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:14:50 +0200
Message-ID: <4E64D95A.2000009@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 IO onto the
systems raid array. 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 won't 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: <4E64D95A.2000009@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