On Tue, 14 Jul 2009, Scott Marlowe wrote:
Are you guys doing anything that could be deemed pathological, like
full table updates on big tables over and over? Had an issue last
year where a dev left a where clause off an update to a field in one
of our biggest tables and in a few weeks the database was so bloated
we had to take it offline to fix the problem. After fixing the
query.
I've just audited the source, looking for any updates without where
clauses. None jumped out to bite me.
Almost everything we do happens in transactions which can occasionally
take 10-20 minutes to complete and span thousands or tens of thousands
of rows across multiple tables. Are long-running transactions a
culprit in table bloat?
I've also used contrib/pgstattuple to try to identify which of our
large tables and indices are experiencing bloat. Here are the
pgstattuple results for our largest tables:
table_len: 56639488
tuple_count: 655501
tuple_len: 53573112
tuple_percent: 94.59
dead_tuple_count: 0
dead_tuple_len: 0
dead_tuple_percent: 0
free_space: 251928
free_percent: 0.44
table_name: status
table_len: 94363648
tuple_count: 342363
tuple_len: 61084340
tuple_percent: 64.73
dead_tuple_count: 10514
dead_tuple_len: 1888364
dead_tuple_percent: 2
free_space: 28332256
free_percent: 30.02
table_name: uploads
table_len: 135675904
tuple_count: 1094803
tuple_len: 129821312
tuple_percent: 95.68
dead_tuple_count: 133
dead_tuple_len: 16048
dead_tuple_percent: 0.01
free_space: 991460
free_percent: 0.73
table_name: invoice_details
table_len: 148914176
tuple_count: 1858812
tuple_len: 139661736
tuple_percent: 93.79
dead_tuple_count: 1118
dead_tuple_len: 80704
dead_tuple_percent: 0.05
free_space: 1218040
free_percent: 0.82
table_name: job_status_log
table_len: 173416448
tuple_count: 132974
tuple_len: 117788200
tuple_percent: 67.92
dead_tuple_count: 10670
dead_tuple_len: 7792692
dead_tuple_percent: 4.49
free_space: 46081516
free_percent: 26.57
table_name: mail
table_len: 191299584
tuple_count: 433378
tuple_len: 145551144
tuple_percent: 76.09
dead_tuple_count: 1042
dead_tuple_len: 862952
dead_tuple_percent: 0.45
free_space: 42068276
free_percent: 21.99
table_name: sessions
table_len: 548552704
tuple_count: 5446169
tuple_len: 429602136
tuple_percent: 78.32
dead_tuple_count: 24992
dead_tuple_len: 1929560
dead_tuple_percent: 0.35
free_space: 93157980
free_percent: 16.98
table_name: job_state_log
table_len: 639262720
tuple_count: 556415
tuple_len: 221505548
tuple_percent: 34.65
dead_tuple_count: 66688
dead_tuple_len: 27239728
dead_tuple_percent: 4.26
free_space: 380168112
free_percent: 59.47
table_name: jobs
table_len: 791240704
tuple_count: 8311799
tuple_len: 700000052
tuple_percent: 88.47
dead_tuple_count: 39
dead_tuple_len: 3752
dead_tuple_percent: 0
free_space: 11397548
free_percent: 1.44
table_name: cron_logs
table_len: 1612947456
tuple_count: 10854417
tuple_len: 1513084075
tuple_percent: 93.81
dead_tuple_count: 0
dead_tuple_len: 0
dead_tuple_percent: 0
free_space: 13014040
free_percent: 0.81
table_name: documents_old_addresses
table_len: 1832091648
tuple_count: 13729360
tuple_len: 1600763725
tuple_percent: 87.37
dead_tuple_count: 598525
dead_tuple_len: 80535904
dead_tuple_percent: 4.4
free_space: 38817616
free_percent: 2.12
table_name: statements
table_len: 3544350720
tuple_count: 64289703
tuple_len: 2828746932
tuple_percent: 79.81
dead_tuple_count: 648849
dead_tuple_len: 28549356
dead_tuple_percent: 0.81
free_space: 143528236
free_percent: 4.05
table_name: ps_page
table_len: 4233355264
tuple_count: 22866609
tuple_len: 3285722981
tuple_percent: 77.62
dead_tuple_count: 231624
dead_tuple_len: 31142594
dead_tuple_percent: 0.74
free_space: 706351636
free_percent: 16.69
table_name: injectd_log
table_len: 4927676416
tuple_count: 55919895
tuple_len: 4176606972
tuple_percent: 84.76
dead_tuple_count: 795011
dead_tuple_len: 58409884
dead_tuple_percent: 1.19
free_space: 279870944
free_percent: 5.68
table_name: documents_ps_page
table_len: 4953735168
tuple_count: 44846317
tuple_len: 3346823052
tuple_percent: 67.56
dead_tuple_count: 2485971
dead_tuple_len: 183639396
dead_tuple_percent: 3.71
free_space: 1038200484
free_percent: 20.96
table_name: latest_document_address_links
table_len: 23458062336
tuple_count: 89533157
tuple_len: 19772992448
tuple_percent: 84.29
dead_tuple_count: 2311467
dead_tuple_len: 502940946
dead_tuple_percent: 2.14
free_space: 2332408612
free_percent: 9.94
table_name: document_address
table_len: 28510109696
tuple_count: 44844664
tuple_len: 21711695949
tuple_percent: 76.15
dead_tuple_count: 1134932
dead_tuple_len: 300674467
dead_tuple_percent: 1.05
free_space: 5988985892
free_percent: 21.01
table_name: documents
Here are the pgstatindex results for our largest indices. I assumed
that negative index sizes are a reslt of integer overflow and ordered
the results accordingly.
index_size: 1317961728
version: 2
tree_level: 3
root_block_no: 12439
internal_pages: 13366
leaf_pages: 1182318
empty_pages: 0
deleted_pages: 13775
avg_leaf_density: -157.76
leaf_fragmentation: 37.87
index_name: documents_pkey
index_size: 1346609152
version: 2
tree_level: 3
root_block_no: 10447
internal_pages: 1937
leaf_pages: 162431
empty_pages: 0
deleted_pages: 12
avg_leaf_density: 66.56
leaf_fragmentation: 26.48
index_name: statements_pkey
index_size: 1592713216
version: 2
tree_level: 3
root_block_no: 81517
internal_pages: 723
leaf_pages: 177299
empty_pages: 0
deleted_pages: 16400
avg_leaf_density: 74.15
leaf_fragmentation: 5.58
index_name: latest_document_address2_precedence_key
index_size: 1617821696
version: 2
tree_level: 3
root_block_no: 81517
internal_pages: 720
leaf_pages: 185846
empty_pages: 0
deleted_pages: 10921
avg_leaf_density: 78.8
leaf_fragmentation: 10.96
index_name: documents_ps_page_ps_page_id_idx
index_size: 1629798400
version: 2
tree_level: 3
root_block_no: 81517
internal_pages: 728
leaf_pages: 188325
empty_pages: 0
deleted_pages: 9896
avg_leaf_density: 88.23
leaf_fragmentation: 0.66
index_name: ps_page_pkey
index_size: 1658560512
version: 2
tree_level: 3
root_block_no: 81517
internal_pages: 740
leaf_pages: 191672
empty_pages: 0
deleted_pages: 10048
avg_leaf_density: 86.7
leaf_fragmentation: 1.03
index_name: ps_page_ps_id_key
index_size: -31956992
version: 2
tree_level: 3
root_block_no: 12439
internal_pages: 5510
leaf_pages: 475474
empty_pages: 0
deleted_pages: 39402
avg_leaf_density: 72.19
leaf_fragmentation: 3.02
index_name: latest_document_address2_pkey
index_size: -321863680
version: 2
tree_level: 3
root_block_no: 81517
internal_pages: 1809
leaf_pages: 479805
empty_pages: 0
deleted_pages: 3383
avg_leaf_density: 25.63
leaf_fragmentation: 40.05
index_name: documents_id_idx
index_size: -461504512
version: 2
tree_level: 3
root_block_no: 49813
internal_pages: 3023
leaf_pages: 456246
empty_pages: 0
deleted_pages: 8682
avg_leaf_density: 34.37
leaf_fragmentation: 66.83
index_name: documents_city
index_size: -11818844162
version: 3
tree_level:
root_block_no: 11036
internal_pages: 10003
leaf_pages: 822178
empty_pages: 0
deleted_pages: 72121
avg_leaf_density: 54.52
leaf_fragmentation: 3.37
index_name: document_address_pkey
index_size: -12678348802
version: 3
tree_level:
root_block_no: 32210
internal_pages: 2410
leaf_pages: 359867
empty_pages: 0
deleted_pages: 7245
avg_leaf_density: 53.31
leaf_fragmentation: 52.7
index_name: documents_recipient
index_size: -13276282882
version: 3
tree_level:
root_block_no: 27346
internal_pages: 2183
leaf_pages: 360040
empty_pages: 0
deleted_pages: 0
avg_leaf_density: 58.39
leaf_fragmentation: 50
index_name: documents_magic_id_key
index_size: -14476328962
version: 3
tree_level:
root_block_no: 44129
internal_pages: 1998
leaf_pages: 339111
empty_pages: 0
deleted_pages: 6465
avg_leaf_density: 50.12
leaf_fragmentation: 52.85
index_name: documents_zip10
index_size: -14723809282
version: 3
tree_level:
root_block_no: 81515
internal_pages: 2470
leaf_pages: 326170
empty_pages: 0
deleted_pages: 15913
avg_leaf_density: 38.21
leaf_fragmentation: 77.19
index_name: documents_state
index_size: -14831697922
version: 3
tree_level:
root_block_no: 47536
internal_pages: 1607
leaf_pages: 341421
empty_pages: 0
deleted_pages: 208
avg_leaf_density: 45.28
leaf_fragmentation: 46.48
index_name: documents_account_number
index_size: -17118412802
version: 3
tree_level:
root_block_no: 81517
internal_pages: 1149
leaf_pages: 296146
empty_pages: 0
deleted_pages: 18027
avg_leaf_density: 80.86
leaf_fragmentation: 7.14
index_name: document_address_precedence_key
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
