Lasitha Alawatta wrote:
>
> Hello Everybody,
>
> We have 6 multi-master MySql instances within a LAN , that are
> replicating is a sequential manner.
>
> Server Environment : Six identical Linux Enterprise version 4 running,
> servers with having MySQL version 6.
>
> we are using “MySQL multi-master replication” method for database
> replication.
>
> There is a delay (5-7 minutes) of that data replication process.
>
> We notice that it’s because of MySQL table locking.
>
I have come across the locking problem before. It depends upon your
application and how you want to segregate your data. I tried switching
to InnoDB for my application but I did not have enough time or resources
to find as performant a configuration, so I just had to switch back.
For context, I'm running over 1M hits a day, and I have four servers
with three mysqld instances each, running mostly MyISAM tables. I was
having a lot of locking pressure on some of my main document tables, so
I wrote a cron job to create some pre-sorted shadow tables and changed
my site to do the bulk of it's queries to the shadow tables. The
trouble-queries used to take about 30s and now they take closer to 1-3s.
My technique here: a) create table Bcold like A; b) insert into B select
* from A where ... order by...; c) rename table Bhot to Bexpired, Bcold
to Bhot; d) drop table Bexpired. This added some replication lag spikes
but improved my performance amazingly, since I never write to the B
shadow tables. I added some replication-ignore-table statements to my
config file, so these big table copies got ignored in replication, and
just ran the cron job on each of the masters and slaves.
The next replication lag issue I solved was with collection of
statistics on my documents. I used to have all my statistics and
documents in the same database instance. The flood of statistics update
statements was serialized with all other updates. I moved my statistics
tables into a different mysql instance. Now I'm only seeing occasional
replication lag.
It's been a lot of work. The icing on the cake has been incorporating
memcached into my application, which has reduced a lot of the query
pressure as well.
Good luck.
Jed
