strange inefficiency in shared memory handling
От | Radoslaw Stachowiak |
---|---|
Тема | strange inefficiency in shared memory handling |
Дата | |
Msg-id | 20020530205101.GC7410@blue.alter.pl обсуждение исходный текст |
Ответы |
Re: strange inefficiency in shared memory handling
|
Список | pgsql-admin |
hello, i have medium sized database ( about 1mln records) served by p2-300 with 64M ram and ide disk. after upgrading to 7.2 version i started to use pg_stat_* tables to optimize performance, and noticed very strange thing. looks like that shared_buffers (which during these tests i tweaked from 256 to 3000) have great influence on tables blocks, but very small on index ones. cached index blocks are purged from memory _very_ fast, and frequent queries which use indexes, results in many disk reads, even if amount of index-blocks which reside in memory were very small. of course this memory removal of cached index blocks is caused by normal queries and their memory (buffer) requirements, but certainly algorithm which chose which buffer to remove is a very ineffective on index blocks. looks to me that it preffer table-block too much (especially compared to index ones) this leads me to conclusion (maybe wrong, i dont know internal buffer management details) that frequently used buffers which hold tables elements are correctly marked as important (dont purged from memory) but buffers which hold index elemens (use at the same ratio as table ones) are purged from memory very soon. removal of offending indexes resulted in dropping load on machine from 2.0 to 0.1!!! below are details (with shared_buffer=3000): mlot=# select * from pg_statio_user_indexes order by idx_blks_read desc limit 5; relid | indexrelid | relname | indexrelname | idx_blks_read | idx_blks_hit -------+------------+---------+------------------+---------------+-------------- 67904 | 617937 | kto | kto_nazwa_ukey | 5332 | 16591 67904 | 617935 | kto | kto_kid_ukey | 3103 | 11506 67904 | 67906 | kto | kto_pkey | 2650 | 27648 with buffers <1000 idx_blks_hit was always smaller than idx_blks_read, even by 10 times; statio_user_tables showed that hits were larger than reads by _great_ amount (factor of 2 and more) - so it shows that shared_buffers are used very well on tables but not on indexes :-( mlot=# select version(); version ------------------------------------------------------------------- PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 table kto has 76 records; mlot=# \d kto Table "kto" Column | Type | Modifiers ----------+--------------------------+------------------------------------------- id | integer | not null default nextval('seq_kto'::text) fk_typ | integer | not null fk_osoba | integer | kid | integer | not null default nextval('seq_kid'::text) nazwa | character varying(64) | not null lid | integer | pid | integer | started | timestamp with time zone | checked | timestamp with time zone | worked | timestamp with time zone | version | character varying(32) | Primary key: kto_pkey Unique keys: kto_fkosoba_ukey kto_kid_ukey kto_nazwa_ukey Triggers: RI_ConstraintTrigger_618144, RI_ConstraintTrigger_618142, RI_ConstraintTrigger_618140, RI_ConstraintTrigger_618138, RI_ConstraintTrigger_618136, RI_ConstraintTrigger_618134 mlot=# \d kto_nazwa_ukey Index "kto_nazwa_ukey" Column | Type --------+----------------------- nazwa | character varying(64) unique btree mlot=# \d kto_kid_ukey Index "kto_kid_ukey" Column | Type --------+----------------------- kid | integer unique btree -- radoslaw.stachowiak.........................................http://alter.pl/
В списке pgsql-admin по дате отправления: