Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9B753B5DDC9 for ; Mon, 5 Sep 2011 12:17:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 05514-07 for ; Mon, 5 Sep 2011 15:17:44 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from marajade.localdomain (97-64-237-59.client.mchsi.com [97.64.237.59]) by mail.postgresql.org (Postfix) with ESMTP id 6E3C5B5DBBE for ; Mon, 5 Sep 2011 12:17:44 -0300 (ADT) Received: from [192.168.1.116] (173-31-201-111.client.mchsi.com [173.31.201.111]) (Authenticated sender: andy) by marajade.localdomain (Postfix) with ESMTPA id 57185407A5; Mon, 5 Sep 2011 10:17:43 -0500 (CDT) Message-ID: <4E64E816.80905@squeakycode.net> Date: Mon, 05 Sep 2011 10:17:42 -0500 From: Andy Colson User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.18) Gecko/20110707 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: Tomas Vondra CC: Gerhard Wohlgenannt , pgsql-performance@postgresql.org, Heinz-Peter Lang , Gerhard Wohlgenannt , "Weichselbraun, Albert" Subject: Re: Sudden drop in DBb performance References: <4E61D6B4.9090804@ai.wu.ac.at> <33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com> <4E64D3D3.8000205@squeakycode.net> <4E64D7EA.3010704@ai.wu.ac.at> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201109/40 X-Sequence-Number: 44816 On 09/05/2011 09:39 AM, Tomas Vondra wrote: > On 5 Září 2011, 16:08, Gerhard Wohlgenannt wrote: >> Below please find the results of vmstat 2 over some periode of time .. >> with normal database / system load. > > What does a "normal load" mean? Does that mean a time when the queries are > slow? > > Are you sure the machine really has 48GB of RAM? Because from the vmstat > output it seems like there's just 32GB. > > procs -----------memory---------- ---swap-- -----io---- -system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us sy > id wa > 0 0 1342168 336936 107636 31353512 0 0 14 183 1911 3426 > 2 1 93 4 > > > 1342168 + 336936 + 107636 + 31353512 = 33140252 ~ 31GB > > BTW there's 1.3GB of swap, although it's not used heavily (according to > the vmstat output). > > Otherwise I don't see anything wrong in the output. What is the size of > the database (use pg_database_size to get it)? Did it grow significantly > recently? > > Tomas > Yeah, its interesting that it swapped in memory, but never out. Looking at this vmstat, it does not look like a hardware problem.(Assuming "normal load" means slow queries) > Did it grow significantly recently? That's a good thought, maybe the stats are old and you have bad plans? It could also be major updates to the data too (as opposed to growth). Gerhard, have you done an 'explain analyze' on any of your slow queries? Have you done an analyze lately? -Andy