On Fri, 2004-07-16 at 19:54, Jan Wieck wrote:
> 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.

I don't understand it either. It's not majorly different. It's a web
storefront app. Mostly reads, a few writes. 

The box has enough ram that disk reads are almost nil, and writes out to
disk are at 100kB/sec.

Also, it wasn't always this way (Slony's load that is). It seemed to
build steadily from the point of installation. Initially, the added load
was very low.

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

DB size is about 3GB. System RAM is 2GB. Shared buffers are at 16384
(130MB). Read/write ratio is probably over 99% read oriented. Row sizes
are fairly average. No huge strings, and column counts are sane, 5-20
columns per table, about 60 tables.

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

I'll give it a shot, see if I can find a sweet spot.

> 
> Jan
-- 
Erik G. Burrows - KG6HEA                          www.erikburrows.com
PGP Key: http://www.erikburrows.com/files/erik.erikburrows.com.pgpkey