Skip site navigation (1) Skip section navigation (2)

Re: Performance impact of log streaming replication

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Andy <angelflow(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Performance impact of log streaming replication
Date: 2010-04-21 00:28:50
Message-ID: 4BCE46C2.5050009@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-general
Andy wrote:
> What is the expected performance impact of the log streaming replication in 9.0?
> In the past I've used the log shipping replication of MySQL and it caused the performance of the master to drop by almost 50%. Just wondered if Postgresql's replication is expected to behave similarly.
>   

Should only be in the low single digit percentages, except in some 
unusual cases--for example, there's an optimization for creating a new 
table and populating it all in one transaction that has to be disabled 
when replication is turned on, so that particular operation can be much 
slower.

MySQL replication works by shipping a binary log of statements around, 
and that's claimed to have a similar overhead:  about 1%:  
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html  However, its 
performance is sensitive to whether that logging is going to a high 
performance disk or not.  It's common for people to throw those onto 
network shares and the like, which can cripple the master if you do that 
badly.

The built-in replication in PostgreSQL saves log files of disk block 
changes instead, ones that are already being created by the database 
anyway for crash recovery.  The only additional overhead beyond standard 
operation is copying those files somewhere else--you're always paying 
most of the logging overhead all the time in standard, unreplicated 
PostgreSQL.  The whole thing is quite fast and robust, without any weird 
limitations like those listed at 
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

The main downside of the approach taken in PostgreSQL compared to what 
MySQL does is that the slaves are not as decoupled from what the master 
does in Postgres, which makes it harder to get scale-out replication 
going still.  You can certainly do it right now, it's just harder than 
most people would like it to be to setup.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com   www.2ndQuadrant.us


In response to

pgsql-general by date

Next:From: raghavendra tDate: 2010-04-21 01:49:20
Subject: Re: tar error, in pg_start_backup()
Previous:From: Craig RingerDate: 2010-04-21 00:20:23
Subject: Re: Help with tracking!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group