On Aug 26, 2004, at 2:52 PM, Jan Wieck wrote:

> On 8/26/2004 11:36 AM, Thomas F.O'Connell wrote:
>
>> So I was testing slony-1.0.1 against a development version of a 
>> database for which I'd eventually like to use Slony as a production 
>> replication solution and got some strange results.
>> I've mentioned a few characteristics of the database I'm working with 
>> on this list before, but to lay it out again:
>> 1. There are thousands of relations.
>> 2. Thousands of them are inherited from a few dozen superclasses.
>> 3. Thousands of them do not have primary keys or columns with unique 
>> constraints.
>> So just to benchmark Slony's performance, I decided that I would just 
>> try to replicate the sequences and the relations with primary keys 
>> (which still amount to thousands). I used my automated slonik builder 
>> and came up with slonik scripts that seemed to set up Slony correctly 
>> with one master and one slave node. I started up slon and began the 
>> subscription process, and the information in the log files indicated 
>> that Slony was attempting to work.
>> On these same servers, I had successfully verified that Slony was 
>> working with the pg_bench examples with our particular configuration 
>> (which is based on postgresql-7.4.1), so everything looked as if it 
>> were going well.
>> Disk usage began to grow. And grow. And grow. Eventually, Slony 
>> filled up the entire 60GB /var partition where the data directory was 
>> living. The weird part was, once Slony had stopped because /var was 
>> full, I couldn't find evidence that any of my replicated tables had 
>> rows. I had copied the schema over beforehand, and everything with 
>> that looked fine, but all of my row counts were zero. What was even 
>> odder was that looking in pg_class to discover which database objects 
>> were eating up space, but there was nothing significant about any of 
>> the values in relpages.
>
> Not odd at all. The entire initial data copy is done in one single 
> transaction. You can't see uncommitted rows, so if you run out of disk 
> before it finishes, you never see a single of them ever.

Well, the database that is being replicated requires less than 30 GB of 
disk, so is it just the overhead of a replicated, unvacuumed database 
that would cause it to inflate to more than double its original size 
during the initial copy?

With a database of this size, is there any ability to know beforehand 
how much space will actually be needed for the initial data copy? Would 
it be more advisable to break it up into several smaller replicated 
sets?

> Relpages is maintained by vacuum. So without vacuuming, you don't see 
> anything in there either.

Well, that explains that...

>> I'm just wondering if there's anyone who has seen any behavior like 
>> this or knows what might have happened. I'm especially curious about 
>> the status of the data.
>> We're currently in the process of reconfiguring our servers, and I 
>> needed to reclaim the space from the partition that filled up, so I 
>> uninstalled and dropped the slave database, but I'll continue 
>> experimenting in a few weeks.
>
> Which is a very good idea anyway. Unfortunately 7.3.x does not allow 
> TRUNCATE inside of a transaction, and for compatibility with 7.3 slon 
> does a DELETE on every subscribed table before starting to COPY the 
> initial data set. That would get you for sure into space troubles.

Just a note: This is a 7.4.x installation.

Thanks for the info, though!

-tfo