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 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(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 won't to kill them)

cheers gerhard