Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

projects:postgres_stuff [2010/05/01 01:39] (Version actuelle)
Ligne 1: Ligne 1:
 +====== My PostgreSQL'​s stuff ======
 +
 +You can clone my branches :
 +
 +  git clone http://​git.postgresql.org/​git/​users/​c2main/​postgres.git
 +
 +===== Track Disk statistics =====
 +
 +Current fun is provided by adding a column for indexes and tables in //​pg_statio* views//. Those column output the **real** read on disk: it let calculate the real hit/miss ratio of your full cache (postgresql shared buffers and OS buffer cache)
 +
 +  git checkout -b track_disk origin/​track_disk
 +
 +Example : 
 +<​code>​
 +cedric=# show track_disk ;
 + ​track_disk ​
 +------------
 + on
 +
 +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 ]-------+-----------------
 +relname ​            | pgbench_accounts
 +heap_blks_hit ​      | 0
 +heap_blks_read ​     | 0
 +heap_blks_real_read | 0
 +idx_blks_hit ​       | 0
 +idx_blks_read ​      | 0
 +idx_blks_real_read ​ | 0
 +
 +./pgbench -S -t 100 -c2
 +tps = 43.698998 (including connections establishing)
 +tps = 43.785598 (excluding connections establishing)
 +
 +relname ​             | pgbench_accounts
 +heap_blks_hit ​       | 0
 +heap_blks_read ​      | 200
 +heap_blks_real_read/​2| 200
 +idx_blks_hit ​        | 342
 +idx_blks_read ​       | 262
 +idx_blks_real_read/​2 | 181
 +
 +--
 +-- After some runs
 +--
 +
 +tps = 99.057743 (including connections establishing)
 +tps = 99.105170 (excluding connections establishing)
 +
 +relname ​             | pgbench_accounts
 +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
 +heap_blks_hit ​      | 442
 +heap_blks_read ​     | 39838
 +heap_blks_real_read | 31023
 +idx_blks_hit ​       | 83635
 +idx_blks_read ​      | 37372
 +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>​
 +
  
 
Haut de page
projects/postgres_stuff.txt · Dernière modification: 2010/05/01 01:39 (modification externe)
 
 
CC Attribution-Share Alike 4.0 International
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0 Google Analytics