[Bucardo-general] size of q table

[Bill McGonigle]

Thanks, Josh, this solved my problem.

On 10/27/2010 11:31 AM, Kaveh Mousavi Zamani wrote:
> Can you please give an example or point to a document if available.

OK, just working through this, we're calling stored procedures - _q is in the bucardo database, the _delta in each of the databases being replicated.

For now, I'm using a hackish approach of clearing this out with a cron job.  Here are my files:

====
/usr/local/share/bucardo/bucardo_purge.sql:
====
\c bucardo                                                                                                                                                                
SELECT bucardo_purge_q_table('1 day');
\c bigdatabase
SELECT bucardo_purge_delta('1 day');
\c littledatabase
SELECT bucardo_purge_delta('1 day');
\q
====

====
/etc/cron.hourly/bucardo_purge:
====
#!/bin/bash

PGPASSWORD=GOSHTHISISAHACK /usr/bin/psql -U bucardo < /usr/local/share/bucardo/bucardo_purge.sql >> /var/log/bucardo-purge 2>&1 
====

'1 day' is sorta arbitrary.  Josh mentions that these functions won't clear out stuff that's still needed anyway, so I don't think the interval can hurt to be shorter.  I set '1 day' because I can make sure to check on it once a day, and I'm paranoid.  I'm getting sub-3-second replication with 1 day's worth of log, which is good enough (lower than the webserver failover interval).  Looks like the default is '2 hours'; maybe I should just leave it be.

Next week, I'll add a couple verbs to bucardo_ctl - I'm thinking that finding out how stale the syncs are and using a number greater than that would be a prudent default.  Again, probably overly paranoid.  I'd rather have a cron job that looks like:

  bucardo_ctl purge_q [nouns*]
  bucardo_ctl purge_delta [nouns*]

I'll write back when that's working (with a patch).  And please tell me if this is a bad idea!

Here are the results of my hand purge:

bucardo=# SELECT bucardo_purge_q_table('1 day');
NOTICE:  Rows left in q table: 183
CONTEXT:  SQL function "bucardo_purge_q_table" statement 1
 bucardo_purge_q_table 
-----------------------
                112699
(1 row)


bigdatabase=# SELECT bucardo_purge_delta('1 day');
                                bucardo_purge_delta                                
-----------------------------------------------------------------------------------
 Rows deleted from bucardo_delta: 8983026 Rows deleted from bucardo_track: 7687716
(1 row)


littledatabase=# SELECT bucardo_purge_delta('1 day');
                              bucardo_purge_delta                              
-------------------------------------------------------------------------------
 Rows deleted from bucardo_delta: 45497 Rows deleted from bucardo_track: 24772
(1 row)

I/O and CPU are back well under control.  16-million-ish rows to scan apparently was gumming up the works.  For reference, this postgresql instance is running dedicated on a CentOS 5.5 x86_64 instance under Xen with four Xeon X5650 at 2.67GHz cores(real) allocated, 8GB of RAM.  pgsql/data is on a dedicated RAID-1 2.5" disk pair, and pg_xlog on another.  The bucardo->bigdatabase postgresql process is keeping one core busy most of the time now and the other cores are handling the database work as expected.

-Bill

-- 
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