Différences

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

@@ 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/04/30 23:39 de 127.0.0.1

Plan du site Derniers changements

Administrer