Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 3B28A1337B61 for ; Thu, 5 May 2011 07:02:51 -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 55313-09 for ; Thu, 5 May 2011 10:02:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.smtpout.orange.fr (smtp01.smtpout.orange.fr [80.12.242.123]) by mail.postgresql.org (Postfix) with ESMTP id C972C1337A5D for ; Thu, 5 May 2011 07:02:18 -0300 (ADT) Received: from [10.100.1.32] ([80.13.5.220]) by mwinf5d02 with ME id fm2G1g0094kpSGo03m2GRC; Thu, 05 May 2011 12:02:17 +0200 Message-ID: <4DC275A8.8000400@hbs-research.com> Date: Thu, 05 May 2011 12:02:16 +0200 From: adrien ducos User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Kevin Grittner CC: "pgsql-admin@postgresql.org" Subject: Re: My server is oddly very slow References: <4DC17AAB.1080206@hbs-research.com> <4DC14AA6020000250003D28B@gw.wicourts.gov> In-Reply-To: <4DC14AA6020000250003D28B@gw.wicourts.gov> 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=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201105/57 X-Sequence-Number: 37494 Kevin Grittner a écrit : > adrien ducos wrote: > > [rearranged somewhat] > > >> The version of both databases is postgres 8.4.1 >> > > [sigh] You really should upgrade. > > http://www.postgresql.org/support/versioning > > http://www.postgresql.org/docs/8.4/static/release.html > I know I should but only my system administrator is allowed to do so, we will soon change to postgres 9.1 anyway. > > >> So I checked the memory on prod during my query execution: >> vmstat >> > > >> procs -----------memory---------- >> r b swpd free buff cache >> 0 1 1280 23320 6356 2975956 >> > > >> The swap is not used but something is a bit odd: the cache is >> using 98% of the RAM >> > > The first line in vmstat (which is all you get if you run it without > a number) is averages since the OS was booted. That's not usually > very helpful. What does this show?: > > free -m > > or run the query while watching the output from: > > vmstat 1 > > So I did that also, and have the same results: free -m total used free shared buffers cached Mem: 3042 3000 41 0 4 2896 -/+ buffers/cache: 99 2942 Swap: 1983 1 1982 same for vmstat 1 while runing the query vmstat 1 procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 0 1280 45752 4496 2963460 0 0 29 45 0 1 2 0 95 3 Almost everything is "cached", I read on some documents that cache means linux uses the free memory to cache disk data (and avoid waisting the free memory). On the other hand, when I do ps -aux while the query is running I see the query is using 17% of the RAM, so I guess it is using the RAM ok. The question could be, why is linux marking that 95% of the memory is cache while in the same time ps -aux shows my query uses 17% of the RAM. >> the copy of files on the SAN is about 3 times faster than on the >> development hard drive. >> > > Copy time doesn't always correlate real well with database > performance. Have you tried running bonnie++ or similar? > Ok, we didn't check on that but we will. I'll see with my system admin next week (he is on hollydays now). On the paper thought it is a SAN composed of several velociraptors. It is supposed to be faster than a normal and rather old hard drive (speed and access time). Thanks, Adrien -- Logo_HBS_mail.jpg Adrien DUCOS Analyste développeur aducos@hbs-research.com www.hbs-research.com +33 (0)9 70 44 64 10 24 rue de l'Est 75020 Paris