Thread: strange inefficiency in shared memory handling
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/
Radoslaw Stachowiak <radek@alter.pl> writes:
> 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)
The buffer management code has absolutely no clue which blocks belong
to indexes and which to tables --- it handles all of them on a uniform
LRU basis.
> 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 :-(
This seems odd, but I wonder whether it is an artifact of some unusual
property of your query workload. You haven't offered enough detail to
let someone else try to reproduce it...
regards, tom lane
*** Tom Lane <tgl@sss.pgh.pa.us> [Thursday, 30.May.2002, 18:04 -0400]:
> > 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)
> The buffer management code has absolutely no clue which blocks belong
> to indexes and which to tables --- it handles all of them on a uniform
> LRU basis.
thats strange. i do regular vacuum analyze on this database (daily)
although not vacuum full. i dont recreate indexes since upgrade to 7.2
about month ago.
> > 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 :-(
> This seems odd, but I wonder whether it is an artifact of some unusual
> property of your query workload. You haven't offered enough detail to
> let someone else try to reproduce it...
there is rather few queries (10.. 60 per minute) but 6..10
queries (mostly updates) are on this table.
most of those 'kto' table selects were optimized to use indexes .
now when i dropped two of them, pgsql may read all tuples but
since table is small (76 rows) it shouldnt have impact (in fact as i
wrote before, load dropped from about 2 to almost 0).
queries on 'kto' are:
UPDATE kto SET checked=now() WHERE id=1 -- avg 3/minute
UPDATE kto SET checked=now(),worked=now(),lid=460733 WHERE id=4 -- avg 2/minute
UPDATE kto SET lid=460730,worked=now() WHERE id=1 -- avg 1/minute
select nazwa,date_part('epoch',now()-worked),date_part('epoch',now()-checked) from kto where lid is not null -- avg
1/minute
numerical values in the queries are not constant.
--
radoslaw.stachowiak.........................................http://alter.pl/