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 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