Differences
This shows you the differences between two versions of the page.
|
projects:postgres_stuff [2010/05/01 01:28] Cédric Villemain |
projects:postgres_stuff [2010/05/01 01:39] (current) Cédric Villemain |
||
|---|---|---|---|
| Line 18: | Line 18: | ||
| on | on | ||
| - | cedric=# SELECT relname,heap_blks_read,heap_blks_hit,heap_blks_real_read, idx_blks_read,idx_blks_hit,idx_blks_real_read from pg_statio_user_tables where relname = 'pgbench_accounts'; | + | cedric=# SELECT relname, |
| + | heap_blks_hit,heap_blks_read,heap_blks_real_read/2 as heap_blks_real_read, | ||
| + | idx_blks_hit,idx_blks_read,idx_blks_real_read/2 as idx_blks_real_read | ||
| + | from pg_statio_user_tables where relname = 'pgbench_accounts'; | ||
| -[ RECORD 1 ]-------+----------------- | -[ RECORD 1 ]-------+----------------- | ||
| relname | pgbench_accounts | relname | pgbench_accounts | ||
| + | heap_blks_hit | 0 | ||
| heap_blks_read | 0 | heap_blks_read | 0 | ||
| - | heap_blks_hit | 0 | ||
| heap_blks_real_read | 0 | heap_blks_real_read | 0 | ||
| - | idx_blks_read | 0 | ||
| idx_blks_hit | 0 | idx_blks_hit | 0 | ||
| + | idx_blks_read | 0 | ||
| idx_blks_real_read | 0 | idx_blks_real_read | 0 | ||
| Line 32: | Line 35: | ||
| tps = 43.785598 (excluding connections establishing) | tps = 43.785598 (excluding connections establishing) | ||
| - | cedric=# SELECT relname,heap_blks_hit,heap_blks_read,heap_blks_real_read/2 as heap_blks_real_read, idx_blks_hit,idx_blks_read,idx_blks_real_read/2 as idx_blks_real_read from pg_statio_user_tables where relname = 'pgbench_accounts'; | + | relname | pgbench_accounts |
| - | -[ RECORD 1 ]-------+----------------- | + | heap_blks_hit | 0 |
| - | relname | pgbench_accounts | + | heap_blks_read | 200 |
| - | heap_blks_hit | 0 | + | heap_blks_real_read/2| 200 |
| - | heap_blks_read | 200 | + | idx_blks_hit | 342 |
| - | heap_blks_real_read | 200 | + | idx_blks_read | 262 |
| - | idx_blks_hit | 342 | + | idx_blks_real_read/2 | 181 |
| - | idx_blks_read | 262 | + | |
| - | idx_blks_real_read | 181 | + | |
| -- | -- | ||
| Line 49: | Line 50: | ||
| tps = 99.105170 (excluding connections establishing) | tps = 99.105170 (excluding connections establishing) | ||
| - | cedric=# SELECT relname,heap_blks_hit,heap_blks_read,heap_blks_real_read/2 as heap_blks_real_read, idx_blks_hit,idx_blks_read,idx_blks_real_read/2 as idx_blks_real_read from pg_statio_user_tables where relname = 'pgbench_accounts'; | + | relname | pgbench_accounts |
| - | -[ RECORD 1 ]-------+----------------- | + | heap_blks_hit | 217 |
| + | heap_blks_read | 19610 | ||
| + | heap_blks_real_read/2| 17663 | ||
| + | idx_blks_hit | 41026 | ||
| + | idx_blks_read | 18570 | ||
| + | idx_blks_real_read/2 | 4631 | ||
| + | |||
| + | -- | ||
| + | -- After some more runs | ||
| + | -- | ||
| + | |||
| + | tps = 143.658449 (including connections establishing) | ||
| + | tps = 144.511803 (excluding connections establishing) | ||
| relname | pgbench_accounts | relname | pgbench_accounts | ||
| - | heap_blks_hit | 217 | + | heap_blks_hit | 442 |
| - | heap_blks_read | 19610 | + | heap_blks_read | 39838 |
| - | heap_blks_real_read | 17663 | + | heap_blks_real_read | 31023 |
| - | idx_blks_hit | 41026 | + | idx_blks_hit | 83635 |
| - | idx_blks_read | 18570 | + | idx_blks_read | 37372 |
| - | idx_blks_real_read | 4631 | + | idx_blks_real_read | 7112 |
| + | cedric=# select * from pgmincore('pgbench_accounts'); | ||
| + | relpath | block_size | block_disk | block_mem | group_mem | ||
| + | --------------------+------------+------------+-----------+----------- | ||
| + | base/16384/24598 | 4096 | 262144 | 126140 | 15422 | ||
| + | base/16384/24598.1 | 4096 | 55318 | 26180 | 3228 | ||
| + | (2 rows) | ||
| + | cedric=# select * from pgmincore('pgbench_accounts_pkey'); | ||
| + | relpath | block_size | block_disk | block_mem | group_mem | ||
| + | ------------------+------------+------------+-----------+----------- | ||
| + | base/16384/24603 | 4096 | 43892 | 43825 | 29 | ||
| + | -- | ||
| + | -- snapshot pgbench_accounts and pgbench_accounts_pkey | ||
| + | -- restart postgresql | ||
| + | -- flush OS cache for pgbench_accounts and pgbench_accounts_pkey | ||
| + | -- | ||
| + | cedric=# select * from pgmincore_snapshot('pgbench_accounts'); | ||
| + | cedric=# select * from pgmincore_snapshot('pgbench_accounts_pkey'); | ||
| + | cedric=# select * from pgfadv_dontneed('pgbench_accounts'); | ||
| + | cedric=# select * from pgfadv_dontneed('pgbench_accounts_pkey'); | ||
| + | cedric=# select pg_stat_reset(); | ||
| + | |||
| + | -- run a pgbench | ||
| + | ./pgbench -S -t 100 -c2 | ||
| + | tps = 38.497385 (including connections establishing) | ||
| + | tps = 38.569719 (excluding connections establishing) | ||
| + | |||
| + | -- restore buffer cache | ||
| + | select * from pgfadv_willneed_snapshot('pgbench_accounts'); | ||
| + | select * from pgfadv_willneed_snapshot('pgbench_accounts_pkey'); | ||
| + | |||
| + | -- run a pgbench | ||
| + | ./pgbench -S -t 100 -c2 | ||
| + | tps = 169.629961 (including connections establishing) | ||
| + | tps = 170.889926 (excluding connections establishing) | ||
| </code> | </code> | ||
