On 7/16/2004 9:40 PM, Erik G. Burrows wrote:
> I'm using PostgreSQL 7.4.3 and Slony-I 1.0.1 with Intel P4 and Xeon
> machines.
> 
> I have a two-server replication setup, one database, one set, working
> very well, but I have some questions about performance.
> 
> I'm not sure how to get some number like updates per second from
> Slony-I, so to give you an idea of the activity of my system, I turned
> on statement logging on the (otherwise idle) slave server. It's doing
> about 600 statements/min.
> 
> My slon configuration is very standard, I haven't changed the 10 second
> sync interval time, or any other option.
> 
> Since turning on replication, the CPU utilization of my master database
> server has tripled, and doing some analysis on the log file (with
> statement and duration logging turned on) I can see it's the "fetch 100
> from LOG;" statements from Slony-I that are causing the increased load.
> Each fetch takes 5 seconds to complete. At 10 second intervals, that's a
> lot of cycles.

I don't quite understand that. Those FETCH commands should normally 
drain out the result set out of a sort set. That is either reading from 
memory or disk. Unless your application is majorly different from 
anything I have every used in my tests, this doesn't make sense.

What is the size of your database, the size of your shared buffer 
configuration, the work_mem settings and what is the read/write ratio of 
your applications database access pattern? What is the average, min and 
max row size of the replicated data?

> 
> Doing frequent vacuum/vacuum full/analyze of the Slony-I tables has
> little effect.

Every vacuum full on slony tables will only cause the slave to fall 
behind, because it will be locked out from accessing those tables.


> 
> So, my question is: What can I do to reduce load on my master server
> from Slony-I? 

I don't have enough information yet to answer that question. But playing 
with the -s and -g parameters would be worth a try.


Jan

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