Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 611C19FB335 for ; Fri, 3 Nov 2006 10:08:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 46903-08 for ; Fri, 3 Nov 2006 10:08:38 -0400 (AST) X-Greylist: delayed 01:01:48.323692 by SQLgrey- Received: from ebrsrv01.ebravo.it (host53-205-static.34-85-b.business.telecomitalia.it [85.34.205.53]) by postgresql.org (Postfix) with ESMTP id 0CC429FB32F for ; Fri, 3 Nov 2006 10:08:34 -0400 (AST) Received: from [192.168.11.103] ([192.168.11.103]) by ebrsrv01.ebravo.it (8.11.6/8.12.5) with ESMTP id kA3D6d303239; Fri, 3 Nov 2006 14:06:40 +0100 Message-ID: <454B3EDC.3090502@streppone.it> Date: Fri, 03 Nov 2006 14:06:36 +0100 From: Cosimo Streppone User-Agent: Mozilla Thunderbird 1.5 (X11/20051201) MIME-Version: 1.0 To: Richard Huxton CC: Postgresql Performance list Subject: Re: Context switch storm References: <454B3B75.6@archonet.com> In-Reply-To: <454B3B75.6@archonet.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/46 X-Sequence-Number: 21816 Richard Huxton wrote: > creimer@brturbo.com.br wrote: >> Hi, >> >> We've migrated one of our servers from pg 7.4 to 8.1 and from times >> to times (4 hours) the server start doing a lot of context switching >> and all transactions become very slow. >> >> The average context switching for this server as vmstat shows is 1 >> but when the problem occurs it goes to 250000. > > You'll tend to see it when you have multiple clients and most queries > can use RAM rather than disk I/O. My understanding of what happens is > that PG requests data from RAM - it's not in cache so the process gets > suspended to wait. The next process does the same, with the same result. > You end up with lots of processes all fighting over what data is in > the cache and no-one gets much work done. Does this happen also with 8.0, or is specific to 8.1 ? I seem to have the same exact behaviour for an OLTP-loaded 8.0.1 server when I raise `shared_buffers' from 8192 to 40000. I would expect an increase in tps/concurrent clients, but I see an average performance below a certain threshold of users, and when concurrent users get above that level, performance starts to drop, no matter what I do. Server logs and io/vm statistics seem to indicate that there is little or no disk activity but machine loads increases to 7.0/8.0. After some minutes, the problem goes away, and performance returns to acceptable levels. When the load increases, *random* database queries show this "slowness", even if they are perfectly planned and indexed. Is there anything we can do? -- Cosimo