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.
Von: Mechain Marc [mailto:Marc.Mechain@stripped]
Gesendet: Donnerstag, 10. Februar 2005 12:04
An: Hannes Rohde; Marc Slemko
Betreff: RE: Slow Replication
I think he is talking about the "innodb_flush_log_at_trx_commit" parameter.
Try to put it at a value of 0.
innodb_flush_log_at_trx_commit = 0
If you have an IO bottleneck, this may help.
De : Hannes Rohde [mailto:hr@stripped]
Envoyé : jeudi 10 février 2005 11:46
À : 'Marc Slemko'
Cc : mysql@stripped
Objet : AW: Slow Replication
I don't quite get what you mean with the second paragraph. Do you
mean increasing the thread concurrency to 6 or something like that? I have
already put it on 4 because we do have HT active on the cpu. On the other it
is just a single processor P IV system. On other hand I think it wouldn't
speed up the SQL thread on the slave a whole lot. It would be excellent if
you could run two or more SQL threads on the slave you priories them
Anyway I will try your last paragraph's suggestion.
Von: Marc Slemko [mailto:identd@stripped]
Gesendet: Donnerstag, 10. Februar 2005 11:24
An: Hannes Rohde
Betreff: Re: Slow Replication
On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <hr@stripped> wrote:
> Hi all,
> We use MySQL as a database backend on a portal site. We have a two
> database server setup (one master, one slave). The master is a PIV 3,2
> 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram
> a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even
> though the slave is a bigger system and is quite fast with selects, it
> always falls behind in replication (Seconds behind the server keeps
> at high-load times).
> Is there any way to speed up the replication a little more? I have already
> tried a whole lot of things but have never been successful, yet :-(
That can be problematic since innodb allows much higher concurrency
than myisam, although you can still have this issue with myisam.
What you have to realize is that due to how mysql replication works,
every transaction needs to be serialized. The slave is only running a
single statement at once. So if you have multiple CPUs on the server,
or multiple disks that can't be saturated by a single concurrent
operation ... then multiple simultaneous operations can get better
performance on the server than you can get in replication to the
If most of your stuff is innodb, then setting the innodb option to not
sync to disk on every transaction may speed things up a lot ... if you
don't care about your data. But, then again, I don't think mysql
replication is actually fully transactional yet anyway.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql