Re: Sudden drop in DBb performance

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(at)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