Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9Pj7-0007vm-6i for pgsql-admin@arkaria.postgresql.org; Thu, 28 Mar 2019 07:45:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h9Pj5-0004AS-P8 for pgsql-admin@arkaria.postgresql.org; Thu, 28 Mar 2019 07:45:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9Pj5-0004AK-BD for pgsql-admin@lists.postgresql.org; Thu, 28 Mar 2019 07:45:27 +0000 Received: from smtp33.i.mail.ru ([94.100.177.93]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9Pis-0007A8-Jl for pgsql-admin@lists.postgresql.org; Thu, 28 Mar 2019 07:45:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail2; h=Content-Type:In-Reply-To:MIME-Version:Date:Message-ID:From:References:To:Subject; bh=0wCGxZSXmEbly9o0MNPGO8k9Kp8czc/8fk+nZKOscs0=; b=RNZlPe1CQlORiC9tlv9foM7n4xb7nJL7veQXlsg4AEvHttElH/mRI1VQBUADQAZrqla9ipCIJ6fvCufvI8hLCcZi7L/94PqWeRvQWmE7LvWtl53bmbVjcO4mgaF8PJfUL5QrQdF3O/9JpugPKguNilrxit89DmaWk/YLFTID3zI=; Received: by smtp33.i.mail.ru with esmtpa (envelope-from ) id 1h9Piq-0004dS-Ar for pgsql-admin@lists.postgresql.org; Thu, 28 Mar 2019 10:45:12 +0300 Subject: Re: Long running query in new production, not so long in old To: pgsql-admin@lists.postgresql.org References: From: Alexander Shaburov Message-ID: <3a10c247-e1ae-a5c4-ecc1-a46f63eb448f@mail.ru> Date: Thu, 28 Mar 2019 10:45:07 +0300 User-Agent: Mozilla/5.0 (Windows NT 6.2; WOW64; rv:60.0) Gecko/20100101 Thunderbird/60.5.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A5E3C1E9F5C07A11EFA8B070" Content-Language: ru Authentication-Results: smtp33.i.mail.ru; auth=pass smtp.auth=shab2@mail.ru smtp.mailfrom=shab2@mail.ru X-77F55803: E14BCC6235C710295A78504BD2AC2941201699BFDE8D84EE8E7D44862D366A361828EFC5C2204806FAC2A6AE5E11396D X-7FA49CB5: 0D63561A33F958A51EA16E88877D8AF8C945902975B9AC5C21E7134AE9CED6808941B15DA834481FA18204E546F3947CA9FF340AA05FB58CF6B57BC7E64490618DEB871D839B7333395957E7521B51C2545D4CF71C94A83E9FA2833FD35BB23D27C277FBC8AE2E8BAE9A1BBD95851C5BA471835C12D1D977C4224003CC8364767815B9869FA544D8D32BA5DBAC0009BE9E8FC8737B5C22492020B0FB4D3A46B03AA81AA40904B5D9CF19DD082D7633A0E7DDDDC251EA7DABD81D268191BDAD3D78DA827A17800CE72A79E8FE808DD2C2CD04E86FAF290E2D40A5AABA2AD3711975ECD9A6C639B01B78DA827A17800CE7D69D9F3FB08CE8FA05FCABDF50C1329275ECD9A6C639B01B4E70A05D1297E1BBC6867C52282FAC8519DC0BE04022C72727F269C8F02392CD5571747095F342E88FB05168BE4CE3AF X-Mailru-Sender: 583F1D7ACE8F49BDF7CAF4A3911D342C3717ECF340576A079D4AD7D346763DA97770C868B3F15C275098F39066C885933DDE9B364B0DF2897422163B89D1DC5C592DC799C9E688FC0DA7A0AF5A3A8387 X-Mras: OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A5E3C1E9F5C07A11EFA8B070 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi! Make the same shared buffer value for example. Disable any activity except you client query. Make query twice. Second one should be during about < 1 min. If not, so it isnt problem shared buffer. My be processor count. If query will be faster, so in real situation you have any activity on db, that take away buffers from using query. Alex 28.03.2019 1:27, Mark Steben пишет: > Good evening > > We are moving to a new VM environment (expedient) and have one > query  that typically runs in 22 - 25 seconds in our old environment, > but is running in about 1 hour, 20 minutes in our new.  I'd like some > insight as to why the explain is showing shared buffer hits numbering > over 113 milliion > in the new environment and only 445 thousand in the old. I have sent > the explains along with the table descriptions, row counts, the one > function that I know causes the bottleneck,  the query, some relevant > configuration settings in postgresql conf (identical in both environments) > and a listing from top in both environments, showing memory, shared > memory, and cpu. > > Everything seems to be identical or close, except for the shared > buffer count in the explain. > Any insight would be appreciated. > > Thank you, > > > -- > *Mark Steben* >  Database Administrator > @utoRevenue | Autobase > > CRM division of Dominion Dealer Solutions > 95D Ashley Ave. > West Springfield, MA 01089 > t: 413.327-3045 > f: 413.383-9567 > > www.fb.com/DominionDealerSolutions > > www.twitter.com/DominionDealer > www.drivedominion.com > > > > > --------------A5E3C1E9F5C07A11EFA8B070 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Hi!

Make the same shared buffer value for example. Disable any activity except you client query. Make query twice. Second one should be during about < 1 min.

If not, so it isnt problem shared buffer. My be processor count. If query will be faster, so in real situation you have any activity on db, that take away buffers from using query.

Alex
28.03.2019 1:27, Mark Steben пишет:
Good evening

We are moving to a new VM environment (expedient) and have one query  that typically runs in 22 - 25 seconds in our old environment, but is running in about 1 hour, 20 minutes in our new.  I'd like some insight as to why the explain is showing shared buffer hits numbering over 113 milliion
in the new environment and only 445 thousand in the old.  I have sent the explains along with the table descriptions, row counts, the one function that I know causes the bottleneck,  the query, some relevant configuration settings in postgresql conf (identical in both environments)
and a listing from top in both environments, showing memory, shared memory, and cpu.

Everything seems to be identical or close, except for the shared buffer count in the explain.  
Any insight would be appreciated.

Thank you,


--
Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com





--------------A5E3C1E9F5C07A11EFA8B070--