Re: Sudden drop in DBb performance - Mailing list pgsql-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@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