On 8/26/2004 4:00 PM, Thomas F.O'Connell wrote:

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

Er ... the copy should not require more than the vacuumed original. I 
don't understand this. Maybe some WAL misconfiguration? Slony does the 
data load a little unfortunate. The DB has to build all the indexes 
during the load, not via bulk index create after. But that can't account 
for such a bloat.

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

Which you should have on 7.4.5 as soon as you can take advantage of 
Slony's switchover.

> 
> Thanks for the info, though!
> 
> -tfo
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #