Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 18F3DB5DE4D for ; Mon, 5 Sep 2011 10:51:23 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 41978-05 for ; Mon, 5 Sep 2011 13:51:17 +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 D4CEFB5DC1A for ; Mon, 5 Sep 2011 10:51:16 -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 ACEE1407A5; Mon, 5 Sep 2011 08:51:16 -0500 (CDT) Message-ID: <4E64D3D3.8000205@squeakycode.net> Date: Mon, 05 Sep 2011 08:51:15 -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> In-Reply-To: <33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com> 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/35 X-Sequence-Number: 44811 On 09/05/2011 02:48 AM, Tomas Vondra wrote: > On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote: >> Dear list, >> >> we are encountering serious performance problems with our database. >> Queries which took around 100ms or less last week now take several >> seconds. >> Results of Bonnie++ >> >> Version 1.96 ------Sequential Output------ --Sequential Input- >> --Random- >> Concurrency 1 -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- >> --Seeks-- >> Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP >> /sec %CP >> voyager 95G 1400 93 27804 3 16324 2 2925 96 41636 3 >> 374.9 4 >> Latency 7576us 233s 164s 15647us 13120ms >> 3302ms >> Version 1.96 ------Sequential Create------ --------Random >> Create-------- >> voyager -Create-- --Read--- -Delete-- -Create-- --Read--- >> -Delete-- >> files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP >> /sec %CP >> 16 141 0 +++++ +++ 146 0 157 0 +++++ +++ >> 172 0 >> Latency 1020ms 128us 9148ms 598ms 37us >> 485ms >> > > That seems a bit slow ... 27MB/s for writes and 41MB/s forreads is ait > slow with 8 drives. > > Tomas > > Agreed, that's really slow. A single SATA drive will get 60 MB/s. Did you run Bonnie while the VM's were up and running? root@host:~# vmstat procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 0 0 308024 884812 40512932 0 0 464 168 353 92 4 2 84 9 Only one line? That does not help much. Can you run it as 'vmstat 2' and let it run while a few slow queries are performed? Then paste all the lines? -Andy