Обсуждение: Too many IO?
I have created a 29GB test database by using standard pgbnech -i -s 2000. Then I executed: explain (analyze, buffers) select * from pgbench_accounts where aid in (select cast(random()*200000000 as int) from generate_series(1,500)); Nested Loop (cost=30.00..6075.07 rows=100000000 width=97) (actual time=23.051. .13570.739 rows=500 loops=1) Buffers: shared hit=1255 read=1250 -> HashAggregate (cost=30.00..32.00 rows=200 width=4)(actual time=0.474..0 .723 rows=500 loops=1) -> Function Scan on generate_series (cost=0.00..17.50 rows=1000 width =0) (actual time=0.097..0.264 rows=500 loops=1) -> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.00..3 0.20 rows=1 width=97) (actual time=27.085..27.138 rows=1 loops=500) Index Cond: (aid = (((random() * 200000000::doubleprecision))::integer )) Buffers: shared hit=1255 read=1250Total runtime: 13571.020 ms As you can see, this query generated 1255+1250 = 2505 times block read either from the buffer or the disk. In my understanding the query accesses an index tuple, which will need access to root page and several number of meta pages (I mean index pages they are not either root or leaf pages) and 1 leaf page, then access 1 heap block. So I expected total number of IO would be somewhat: 500 index leaf pages + 500 heap blocks = 1000 However I saw 1505 more accesses in total. My guess is this number mainly comes from index meta page access. So my guess is we need 3 page accesses (to traverse b tree index tree) before reaching the leaf page in average. Am I correct or the number is execessive? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Wed, Mar 14, 2012 at 2:29 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: > However I saw 1505 more accesses in total. My guess is this number > mainly comes from index meta page access. So my guess is we need 3 > page accesses (to traverse b tree index tree) before reaching the leaf > page in average. Am I correct or the number is execessive? Meta page access was optimised away some time ago. Descending the index tree can easily take that long, perhaps longer when the table is larger and the tree is deeper. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Tatsuo Ishii <ishii@postgresql.org> writes:
> I have created a 29GB test database by using standard pgbnech -i -s
> 2000. Then I executed:
That means 200 million accounts rows. With integer keys you could
expect to get 200 to 300 keys per index page. Taking the number as 200
for simplicity, we expect 1 million leaf pages, 5000 level-1 inner
pages, 25 level-2 inner pages, and a level-3 root page. Even if the
tree were packed completely full, it'd still be depth 3.
> As you can see, this query generated 1255+1250 = 2505 times block read
> either from the buffer or the disk. In my understanding the query
> accesses an index tuple, which will need access to root page and
> several number of meta pages (I mean index pages they are not either
> root or leaf pages) and 1 leaf page, then access 1 heap block. So I
> expected total number of IO would be somewhat:
> 500 index leaf pages + 500 heap blocks = 1000
The way I count it, each probe will touch the root page, a level-2 inner
page, a level-1 inner page, a leaf page, and a heap page, so five buffer
touches per cycle, which is almost exactly what you've got. Only the
first two of those are very likely to benefit from caching from previous
searches, so the fact that you got 1255 hits and not only 1000 is
actually a bit better than expected. Probably this query was not done
from a standing start, and so some of the level-1 pages were already in
buffer cache.
regards, tom lane
Excerpts from Tatsuo Ishii's message of mar mar 13 23:29:44 -0300 2012:
> As you can see, this query generated 1255+1250 = 2505 times block read
> either from the buffer or the disk. In my understanding the query
> accesses an index tuple, which will need access to root page and
> several number of meta pages (I mean index pages they are not either
> root or leaf pages)
To clarify terminology a bit -- we call "metapage" of a btree index the
page number zero, which among other things contains the page number for
the root page. That is, since the root page can move around due to
splitting, its number can always be found by reading the metapage.
Pages that aren't root nor leaf pages are typically called "internal
pages", though there are other names for them ("non-leaf").
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>> As you can see, this query generated 1255+1250 = 2505 times block read >> either from the buffer or the disk. In my understanding the query >> accesses an index tuple, which will need access to root page and >> several number of meta pages (I mean index pages they are not either >> root or leaf pages) and 1 leaf page, then access 1 heap block. So I >> expected total number of IO would be somewhat: > >> 500 index leaf pages + 500 heap blocks = 1000 > > The way I count it, each probe will touch the root page, a level-2 inner > page, a level-1 inner page, a leaf page, and a heap page, so five buffer > touches per cycle, which is almost exactly what you've got. Only the > first two of those are very likely to benefit from caching from previous > searches, so the fact that you got 1255 hits and not only 1000 is > actually a bit better than expected. Probably this query was not done > from a standing start, and so some of the level-1 pages were already in > buffer cache. Thanks for the explanation. Now I understand that PostgreSQL works as expected. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp