Ci-dessous, les différences entre deux révisions de la page.
— | projects:postgres_stuff [2010/04/30 23:39] (Version actuelle) – créée - modification externe 127.0.0.1 | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ====== My PostgreSQL' | ||
+ | |||
+ | You can clone my branches : | ||
+ | |||
+ | git clone http:// | ||
+ | |||
+ | ===== Track Disk statistics ===== | ||
+ | |||
+ | Current fun is provided by adding a column for indexes and tables in // | ||
+ | |||
+ | git checkout -b track_disk origin/ | ||
+ | |||
+ | Example : | ||
+ | < | ||
+ | cedric=# show track_disk ; | ||
+ | | ||
+ | ------------ | ||
+ | on | ||
+ | |||
+ | cedric=# SELECT relname, | ||
+ | heap_blks_hit, | ||
+ | idx_blks_hit, | ||
+ | from pg_statio_user_tables | ||
+ | -[ RECORD 1 ]-------+----------------- | ||
+ | relname | ||
+ | heap_blks_hit | ||
+ | heap_blks_read | ||
+ | heap_blks_real_read | 0 | ||
+ | idx_blks_hit | ||
+ | idx_blks_read | ||
+ | idx_blks_real_read | ||
+ | |||
+ | ./pgbench -S -t 100 -c2 | ||
+ | tps = 43.698998 (including connections establishing) | ||
+ | tps = 43.785598 (excluding connections establishing) | ||
+ | |||
+ | relname | ||
+ | heap_blks_hit | ||
+ | heap_blks_read | ||
+ | heap_blks_real_read/ | ||
+ | idx_blks_hit | ||
+ | idx_blks_read | ||
+ | idx_blks_real_read/ | ||
+ | |||
+ | -- | ||
+ | -- After some runs | ||
+ | -- | ||
+ | |||
+ | tps = 99.057743 (including connections establishing) | ||
+ | tps = 99.105170 (excluding connections establishing) | ||
+ | |||
+ | relname | ||
+ | heap_blks_hit | ||
+ | heap_blks_read | ||
+ | heap_blks_real_read/ | ||
+ | idx_blks_hit | ||
+ | idx_blks_read | ||
+ | idx_blks_real_read/ | ||
+ | |||
+ | -- | ||
+ | -- After some more runs | ||
+ | -- | ||
+ | |||
+ | tps = 143.658449 (including connections establishing) | ||
+ | tps = 144.511803 (excluding connections establishing) | ||
+ | |||
+ | relname | ||
+ | heap_blks_hit | ||
+ | heap_blks_read | ||
+ | heap_blks_real_read | 31023 | ||
+ | idx_blks_hit | ||
+ | idx_blks_read | ||
+ | idx_blks_real_read | ||
+ | |||
+ | cedric=# select * from pgmincore(' | ||
+ | relpath | ||
+ | --------------------+------------+------------+-----------+----------- | ||
+ | | ||
+ | | ||
+ | (2 rows) | ||
+ | |||
+ | cedric=# select * from pgmincore(' | ||
+ | | ||
+ | ------------------+------------+------------+-----------+----------- | ||
+ | | ||
+ | |||
+ | -- | ||
+ | -- snapshot pgbench_accounts and pgbench_accounts_pkey | ||
+ | -- restart postgresql | ||
+ | -- flush OS cache for pgbench_accounts and pgbench_accounts_pkey | ||
+ | -- | ||
+ | |||
+ | cedric=# select * from pgmincore_snapshot(' | ||
+ | cedric=# select * from pgmincore_snapshot(' | ||
+ | |||
+ | cedric=# select * from pgfadv_dontneed(' | ||
+ | cedric=# select * from pgfadv_dontneed(' | ||
+ | |||
+ | 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(' | ||
+ | select * from pgfadv_willneed_snapshot(' | ||
+ | |||
+ | -- run a pgbench | ||
+ | ./pgbench -S -t 100 -c2 | ||
+ | tps = 169.629961 (including connections establishing) | ||
+ | tps = 170.889926 (excluding connections establishing) | ||
+ | </ | ||
+ | |||