We did get the problem kind of solved up to now. We basically use as advised the option innodb flush_log_at_trx_commit=2. This did get the timelage solved.
I still do not really understand why we had to speed the slave up like this because it should have been faster due to the hardware already. I guess it is just as Alec said that the fact of using a single-threaded Slave-SQL thread slows the slove down a whole lot. Does anyone have any solution or suggestion how to "work" around that? I don't really feel good using the flush_log_at_trx_commit option. P.S.: The old Show Master or slave status information basically showed that the I/O Thread has been up to date and the SQL Thread of the slave lagged. Thank you, Hannes Rohde -----Ursprüngliche Nachricht----- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Februar 2005 13:15 An: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Betreff: Re: AW: Slow Replication "Hannes Rohde" <[EMAIL PROTECTED]> wrote on 10/02/2005 11:44:13: > I don't think we are dealing with an IO bottleneck here because the > slave server should quite faster with writings to the disc at least since we > are using Raid 0 here. Or is there any way which could explain an IO > bottleneck even though the slave is not running as many selects as the > master is? In this case we are talking about one replicated database on a > dedicated slave system. As I understand the previous posts, the problem is that the replication process is single-threaded while the updates on the original master are multi-threaded. On the original server, if Update 1 stalls because it has to fetch data of disk, Update 2 can proceed. If Update 2 stalls, Update 3 can proceed - and so on. This means firstly that Updates which can take advantage of the cache take no effective time - they come in, do their job, and exit while peer updates are stalled in Disk wait. This also means that lower-level software can optimise disk performance by re-ordering IO operations to minimise head movements. In my experience, having up to 4 parallel streams of disk operations, and allowing the disk to pick its preferred order of execution, usually adds about 50% to disk performance and can double it. However, when they are replicated to the slave server, the updates are put into a strictly First In, First out queue. If Update 1 stalls, Update 2 cannot be started - and nor can Update 3. When Update 3 does finally start, it cannot overlap the others, so that the time it takes, albeit small because it does not access disk, is added on to the other times rather than included within them. And since you are performing strictly one operation at a time (on the Updates side at least) Raid 0 does not help you, because there are no overlapping reads to get from alternate disks. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
