Re: Performance impact of log streaming replication

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