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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
