[Bucardo-general] child_q_ relations linger & entries in q table with no updates/inserts/deletes
Bill McGonigle
bill at bfccomputing.com
Mon Jan 10 21:25:23 UTC 2011
Hi, all,
I'm looking into why my 'bucardo' database has grown so large recently, and see two things that look odd, and may or may not related.
First, I'm noticing un-purged q table entries. They have NULL values for 'started' or 'aborted', so the bucardo_purge_q_table function doesn't see them, but they also don't have any 'updates', 'deletes', or 'inserts', which seems odd.
I can write some SQL to take these out regularly so they don't linger, but perhaps it's indicative of something else wrong.
Second, and the main cause of the 'bucardo' database bloat, is old child_q_ relations. I have dozens of them in the 10-15MB range each (8K pages). According to my munin graphs, this is making the database grow at about 75MB per week, recently pushing it up over the limit of how big it can be for acceptable performance on this hardware). But, that's only about 5-ish of these relations per week, so it can't something that's happening all the time.
This would be some harder SQL to write to clean up - what ought to trigger removal of these relations?
Thanks,
-Bill
------
bucardo=# select count(sync) FROM q WHERE (started < now() - '5 minutes ago'::interval OR ended < now() - '5 minutes ago'::interval OR aborted < now() - '5 minutes ago'::interval OR cdate < now() - '5 minutes ago'::interval) AND (ended IS NULL OR aborted IS NULL);
count
-------
30661
(1 row)
sync | sourcedb | targetdb | ppid | pid | synctype | updates | inserts | deletes | started | aborted | whydie |
ended | cdate
----------------+----------------+-----------------+-------+-------+----------+---------+---------+---------+-------------------------------+---------+--------+----------
---------------------+-------------------------------
bigdb_left | bigdb_left | bigdb_right | 14261 | 5152 | swap | | | | 2011-01-05 06:36:00.614219-05 | | |
| 2011-01-05 06:36:00.541966-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 4376 | swap | | | | 2010-12-11 00:20:06.948823-05 | | |
| 2010-12-11 00:20:06.627405-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 18138 | swap | | | | 2010-12-27 16:40:11.40905-05 | | |
| 2010-12-27 16:40:11.015007-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 26999 | swap | | | | 2010-12-28 20:29:00.768213-05 | | |
| 2010-12-28 20:29:00.509604-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 16960 | swap | | | | 2011-01-02 14:23:42.023571-05 | | |
| 2011-01-02 14:23:41.231009-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 11451 | swap | | | | 2010-12-29 06:05:54.568914-05 | | |
| 2010-12-29 06:05:54.384814-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 19342 | swap | | | | 2011-01-08 17:47:33.215728-05 | | |
| 2011-01-08 17:47:33.043056-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 30849 | swap | | | | 2011-01-01 13:01:45.158658-05 | | |
| 2011-01-01 13:01:44.680756-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 10524 | swap | | | | 2011-01-03 20:30:51.070156-05 | | |
| 2011-01-03 20:30:50.737438-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 29104 | swap | | | | 2011-01-06 20:33:38.651666-05 | | |
| 2011-01-06 20:33:38.383344-05
bigdb_left | bigdb_left | bigdb_right | 14261 | 13319 | swap | | | | 2011-01-09 08:27:34.089306-05 | | |
| 2011-01-09 08:27:33.801951-05
...
bucardo=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
-----------------------------------------+----------
child_q_20101217 | 1501
child_q_20101223 | 1490
child_q_20101214 | 1392
child_q_20101231 | 1351
child_q_20101219 | 1321
child_q_20101218 | 1282
child_q_20101207 | 1280
child_q_20101227 | 1274
child_q_20101216 | 1262
child_q_20101221 | 1257
child_q_20101210 | 1257
child_q_20101209 | 1254
child_q_20101220 | 1242
child_q_20110104 | 1231
child_q_20110109 | 1229
child_q_20101230 | 1226
child_q_20101229 | 1219
child_q_20101130 | 1212
child_q_20101222 | 1211
child_q_20110108 | 1205
child_q_20110103 | 1202
child_q_20101226 | 1194
child_q_20101224 | 1172
child_q_20110105 | 1152
child_q_20101202 | 1147
child_q_20110102 | 1142
child_q_20110101 | 1113
child_q_20101123 | 1112
child_q_20110106 | 1104
child_q_20101204 | 1097
child_q_20110107 | 1094
child_q_20101205 | 1092
child_q_20101203 | 1089
child_q_20101114 | 1084
child_q_20101225 | 1084
child_q_20101228 | 1076
child_q_20101102 | 1055
child_q_20101208 | 1049
child_q_20101211 | 1046
child_q_20101103 | 1033
child_q_20101116 | 1032
child_q_20101201 | 1030
child_q_20101113 | 1016
child_q_20101120 | 1014
child_q_20101117 | 983
child_q_20101129 | 971
...
--
Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.603.448.4440
Email, IM, VOIP: bill at bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle
More information about the Bucardo-general
mailing list