Re: [HACKERS] [WIP] Zipfian distribution in pgbench

Wed, 12 Jul 2017 04:29:39 -0700


On 7 Jul 2017, at 21:53, Peter Geoghegan <[email protected]> wrote:

Is it possible for you to instrument the number of B-Tree page
accesses using custom instrumentation for pgbench_accounts_pkey?

If that seems like too much work, then it would still be interesting
to see what the B-Tree keyspace looks like before and after varying
the "nclient" count from, say, 32 to 128. Maybe there is a significant
difference in how balanced or skewed it is in each case. Or, the index
could simply be more bloated.

There is a query that I sometimes use, that itself uses pageinspect,
to summarize the keyspace quickly. It shows you the highkey for every
internal page, starting from the root and working down to the lowest
internal page level (the one just before the leaf level -- level 1),
in logical/keyspace order. You can use it to visualize the
distribution of values. It could easily include the leaf level, too,
but that's less interesting and tends to make the query take ages. I
wonder what the query will show here.

Here is the query:
…

I am attaching results of query that you sent. It shows that there is nothing have changed after executing tests. 
...before 128

          idx          | level | l_item | blkno | btpo_prev | btpo_next | 
btpo_flags | type | live_items | dead_items | avg_item_size | page_size | 
free_size |         highkey         
-—---------------------+-------+--------+-------+-----------+-----------+------------+------+------------+------------+---------------+-----------+-----------+-------------------------
 pgbench_accounts_pkey |     2 |      1 |   290 |         0 |         0 |       
   2 | r    |         10 |          0 |            15 |      8192 |      7956 | 
 pgbench_accounts_pkey |     1 |      1 |     3 |         0 |       289 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
09 96 01 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      2 |   289 |         3 |       575 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
11 2c 03 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      3 |   575 |       289 |       860 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
19 c2 04 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      4 |   860 |       575 |      1145 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
21 58 06 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      5 |  1145 |       860 |      1430 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
29 ee 07 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      6 |  1430 |      1145 |      1715 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
31 84 09 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      7 |  1715 |      1430 |      2000 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
39 1a 0b 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      8 |  2000 |      1715 |      2285 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
41 b0 0c 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      9 |  2285 |      2000 |      2570 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
49 46 0e 00 00 00 00 00
 pgbench_accounts_pkey |     1 |     10 |  2570 |      2285 |         0 |       
   0 | i    |        177 |          0 |            15 |      8192 |      4616 | 
(11 rows)

latency average = 1.375 ms
tps = 93085.250384 (including connections establishing)
tps = 93125.724773 (excluding connections establishing)
SQL script 1: /home/nglukhov/ycsb_read_zipf.sql
 - weight: 1 (targets 50.0% of total)
 - 2782999 transactions (49.8% of total, tps = 46364.447705)
 - latency average = 0.131 ms
 - latency stddev = 0.087 ms
SQL script 2: /home/nglukhov/ycsb_update_zipf.sql
 - weight: 1 (targets 50.0% of total)
 - 2780197 transactions (49.8% of total, tps = 46317.766703)
 - latency average = 2.630 ms
 - latency stddev = 14.092 ms

after 128

          idx          | level | l_item | blkno | btpo_prev | btpo_next | 
btpo_flags | type | live_items | dead_items | avg_item_size | page_size | 
free_size |         highkey         
—----------------------+-------+--------+-------+-----------+-----------+------------+------+------------+------------+---------------+-----------+-----------+-------------------------
 pgbench_accounts_pkey |     2 |      1 |   290 |         0 |         0 |       
   2 | r    |         10 |          0 |            15 |      8192 |      7956 | 
 pgbench_accounts_pkey |     1 |      1 |     3 |         0 |       289 |       
   0 | i    |        353 |          0 |            15 |      8192 |      1096 | 
09 96 01 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      2 |   289 |         3 |       575 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
11 2c 03 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      3 |   575 |       289 |       860 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
19 c2 04 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      4 |   860 |       575 |      1145 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
21 58 06 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      5 |  1145 |       860 |      1430 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
29 ee 07 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      6 |  1430 |      1145 |      1715 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
31 84 09 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      7 |  1715 |      1430 |      2000 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
39 1a 0b 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      8 |  2000 |      1715 |      2285 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
41 b0 0c 00 00 00 00 00
 pgbench_accounts_pkey |     1 |      9 |  2285 |      2000 |      2570 |       
   0 | i    |        285 |          0 |            15 |      8192 |      2456 | 
49 46 0e 00 00 00 00 00
 pgbench_accounts_pkey |     1 |     10 |  2570 |      2285 |         0 |       
   0 | i    |        177 |          0 |            15 |      8192 |      4616 | 
(11 rows)
—
Thanks and Regards,
Alik Khilazhev
Postgres Professional:
http://www.postgrespro.com
The Russian Postgres Company

Reply via email to