Sudden drop in DBb performance

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.

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

Could you be so kind and give us any advice how to track down the
problem or comment on possible reasons???

Thank you very much in advance!!!

Regards,
heinz + gerhard

name
| current_setting
----------------------------+-------------------------------------------------------------------------------------------------------------
version | PostgreSQL 8.4.8 on x86_64-pc-linux-gnu,
compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
archive_command | /usr/local/sbin/weblyzard-wal-archiver.sh
%p %f
archive_mode | on
checkpoint_segments | 192
effective_cache_size | 25000MB
external_pid_file | /var/run/postgresql/8.4-main.pid
full_page_writes | on
geqo | on
lc_collate | de_AT.UTF-8
lc_ctype | de_AT.UTF-8
listen_addresses | *
log_line_prefix | %t
log_min_duration_statement | 3s
maintenance_work_mem | 500MB
max_connections | 250
max_stack_depth | 2MB
port | 5432
server_encoding | UTF8
shared_buffers | 7000MB
ssl | on
TimeZone | localtime
unix_socket_directory | /var/run/postgresql
work_mem | 256MB

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
1.96,1.96,voyager,1,1314988752,95G,,1400,93,27804,3,16324,2,2925,96,41636,3,374.9,4,16,,,,,141,0,+++++,+++,146,0,157,0,+++++,+++,172,0,7576us,233s,164s,15647us,13120ms,3302ms,1020ms,128us,9148ms,598ms,37us,485ms