X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (unknown [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 149649FAA6E for ; Wed, 30 Aug 2006 08:58:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 61174-04-4 for ; Wed, 30 Aug 2006 11:58:15 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from relay01.is.co.za (relay01.is.co.za [196.35.6.69]) by postgresql.org (Postfix) with ESMTP id AB2209FB264 for ; Wed, 30 Aug 2006 08:55:40 -0300 (ADT) Received: from relay01.is.co.za (localhost [127.0.0.1]) by relay01.is.co.za (Postfix) with ESMTP id ACA9F314AC3 for ; Wed, 30 Aug 2006 13:55:38 +0200 (SAST) Received: from smtp02.isdsl.net (smtp02.isdsl.net [196.26.208.200]) by relay01.is.co.za (Postfix) with ESMTP id 7B97D314AAC for ; Wed, 30 Aug 2006 13:55:38 +0200 (SAST) Received: from server.svo.co.za (c1-52-10.rrba.isadsl.co.za [196.209.57.52]) by smtp02.isdsl.net (Postfix) with ESMTP id 2BCD32E251 for ; Wed, 30 Aug 2006 13:55:38 +0200 (SAST) Received: from [192.168.0.11] ([192.168.0.11]) by server.svo.co.za with Microsoft SMTPSVC(6.0.3790.1830); Wed, 30 Aug 2006 14:00:00 +0200 Message-ID: <44F57CB2.4030808@studentvillage.co.za> Date: Wed, 30 Aug 2006 13:55:30 +0200 From: Willo van der Merwe User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) MIME-Version: 1.0 To: Luke Lonergan Cc: Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: PostgreSQL performance issues References: <3E37B936B592014B978C4415F90D662D04392D2B@MI8NYCMAIL06.Mi8.com> In-Reply-To: <3E37B936B592014B978C4415F90D662D04392D2B@MI8NYCMAIL06.Mi8.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 30 Aug 2006 12:00:00.0421 (UTC) FILETIME=[D1EC1D50:01C6CC2B] X-Virus-Scanned: ClamAV using ClamSMTP at relay01.dial-up.net X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.63 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, ROUND_THE_WORLD X-Spam-Level: * X-Archive-Number: 200608/472 X-Sequence-Number: 20657 That's exactly what I'm experiencing. Everything was fine until yesterday, when we noticed a considerable site slow-down. Graphs showed the server suddenly spiking to a load of 67. At first I thought somebody executed a ran-away query, so I restarted postgres, but after it came back up, it climbed back up to this load. In the meanwhile I've applied some table level optimizations and the postgres.conf optimizatrions ... nothing Here's the vmstat output, since reboot last night [root@srv1 ~]# vmstat -a procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free inact active si so bi bo in cs us sy id wa 27 0 0 595312 248100 2962764 0 0 8 31 105 7 63 2 35 0 [root@srv1 ~]# vmstat -d disk- ------------reads------------ ------------writes----------- -----IO------ total merged sectors ms total merged sectors ms cur sec ram0 0 0 0 0 0 0 0 0 0 0 ram1 0 0 0 0 0 0 0 0 0 0 ram2 0 0 0 0 0 0 0 0 0 0 ram3 0 0 0 0 0 0 0 0 0 0 ram4 0 0 0 0 0 0 0 0 0 0 ram5 0 0 0 0 0 0 0 0 0 0 ram6 0 0 0 0 0 0 0 0 0 0 ram7 0 0 0 0 0 0 0 0 0 0 ram8 0 0 0 0 0 0 0 0 0 0 ram9 0 0 0 0 0 0 0 0 0 0 ram10 0 0 0 0 0 0 0 0 0 0 ram11 0 0 0 0 0 0 0 0 0 0 ram12 0 0 0 0 0 0 0 0 0 0 ram13 0 0 0 0 0 0 0 0 0 0 ram14 0 0 0 0 0 0 0 0 0 0 ram15 0 0 0 0 0 0 0 0 0 0 sda 197959 38959 4129737 952923 777438 1315162 16839981 39809324 0 2791 fd0 0 0 0 0 0 0 0 0 0 0 md0 0 0 0 0 0 0 0 0 0 0 Luke Lonergan wrote: > Interesting - in this quick snapshot there is no I/O happening at all. > What happens when you track the activity for a longer period of time? > > How about just capturing vmstat during a period when the queries are > slow? > > Has the load average been this high forever or are you experiencing a > growth in workload? 463 processes all doing CPU work will take 100x as > long as one query on a 4 CPU box, have you worked through how long you > should expect the queries to take? > > - Luke > > >> -----Original Message----- >> From: Willo van der Merwe [mailto:willo@studentvillage.co.za] >> Sent: Wednesday, August 30, 2006 4:35 AM >> To: Luke Lonergan >> Cc: Merlin Moncure; pgsql-performance@postgresql.org >> Subject: Re: [PERFORM] PostgreSQL performance issues >> >> Luke Lonergan wrote: >> >>>> Currently the load looks like this: >>>> Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, >>>> >> 0.0% hi, >> >>>> 1.0% si >>>> Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, >>>> >> 0.0% hi, >> >>>> 0.3% si >>>> Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, >>>> >> 0.0% hi, >> >>>> 0.3% si >>>> Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, >>>> >> 0.0% hi, >> >>>> 0.3% si >>>> >>>> >>> All four CPUs are hammered busy - check "top" and look for runaway >>> processes. >>> >>> - Luke >>> >>> >>> >>> >> Yes, the first 463 process are all postgres. In the meanwhile >> I've done: >> Dropped max_connections from 500 to 250 and Upped >> shared_buffers = 50000 >> >> Without any apparent effect. >> >> >> > > >