From: Gerhard Wohlgenannt <wohlg@ai.wu.ac.at>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org, Heinz-Peter Lang <heinz@langatium.net>
Cc: Gerhard Wohlgenannt <wohlg@ai.wu-wien.ac.at>
Cc: Weichselbraun, Albert <albert.weichselbraun@wu.ac.at>
Subject: Re: Sudden drop in DBb performance
Date: Mon, 05 Sep 2011 16:15:27 +0200
Message-ID: <4E64D97F.5070701@ai.wu.ac.at> (raw)
In-Reply-To: <33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com>
References: <4E61D6B4.9090804@ai.wu.ac.at>
<33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com>
hi,
thanks a lot for your help!
>> Dear list,
>>
>> we are encountering serious performance problems with our database.
>> Queries which took around 100ms or less last week now take several
>> seconds.
>>
>> The database runs on Ubuntu Server 10.4.3 (kernel: 2.6.32-33) on
>> hardware as follows:
>> 8-core Intel Xeon CPU with 2.83GHz
>> 48 GB RAM
>> RAID 5 with 8 SAS disks
>> PostgreSQL 8.4.8 (installed from the Ubuntu repository).
>>
>> Additionally to the DB the machine also hosts a few virtual machines. In
>> the past everything worked very well and the described problem occurs
>> just out of the blue. We don't know of any postgresql config changes or
>> anything else which might explain the performance reduction.
>> We have a number of DBs running in the cluster, and the problem seems to
>> affect all of them.
> What are the virtual machines doing? Are you sure they are not doing a lot
> of IO?
we also have a ssd-disk in the machine, and the virtual machines do most
of their IO on that. But there sure also is some amount of I/O onto the
systems raid array coming from the virtual machines. maybe we should
consider having a dedicated database server.
>> We checked the performance of the RAID .. which is reasonable for eg.
>> "hdparm -tT". Memory is well used, but not swapping.
>> vmstat shows, that the machine isn't using the swap and the load
>> shouldn't be also to high:
>> 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
>>
>> Bonnie++ results given below, I am no expert at interpreting those :-)
>>
>>
>> Activating log_min_duration shows for instance this query --- there are
>> now constantly queries which take absurdely long.
>>
>> 2011-09-02 22:38:18 CEST LOG: Dauer: 25520.374 ms Anweisung: SELECT
>> keyword_id FROM keywords.table_x WHERE keyword=E'diplomaten'
>>
>> db=# explain analyze SELECT keyword_id FROM keywords.table_x WHERE
>> keyword=E'diplomaten';
>> QUERY
>> PLAN
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Index Scan using idx_table_x_keyword on table_x (cost=0.00..8.29
>> rows=1 width=4) (actual time=0.039..0.041 rows=1 loops=1)
>> Index Cond: ((keyword)::text = 'diplomaten'::text)
>> Total runtime: 0.087 ms
>> (3 Zeilen)
>>
>> db=# \d keywords.table_x
>> Tabelle »keywords.table_x«
>> Spalte | Typ
>> | Attribute
>> ------------+-------------------+------------------------------------------------------------------------------------------------------
>> keyword_id | integer | not null Vorgabewert
>> nextval('keywords.table_x_keyword_id_seq'::regclass)
>> keyword | character varying |
>> so | double precision |
>> Indexe:
>> "table_x_pkey" PRIMARY KEY, btree (keyword_id) CLUSTER
>> "idx_table_x_keyword" btree (keyword)
>> Fremdschlüsselverweise von:
>> TABLE "keywords.table_x_has" CONSTRAINT
>> "table_x_has_keyword_id_fkey" FOREIGN KEY (keyword_id) REFERENCES
>> keywords.table_x(keyword_id) ON UPDATE CASCADE ON DELETE CASCADE
> But in this explain analyze, the query finished in 41 ms. Use auto-explain
> contrib module to see the explain plan of the slow execution.
thanks. we will use auto_explain as soon as some long running updates
are finished (don't want to kill them)
cheers gerhard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wohlg@ai.wu.ac.at, tv@fuzzy.cz, heinz@langatium.net, wohlg@ai.wu-wien.ac.at, albert.weichselbraun@wu.ac.at
Subject: Re: Sudden drop in DBb performance
In-Reply-To: <4E64D97F.5070701@ai.wu.ac.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox