0

We created two new mysql servers running 5.5. Our old server were 5.0.

The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues.

We have some long running select queries (40-50 sec), and when they are run replication breaks - we have to skip a couple of records to get replication restarted. This is consistent and repeatable. I suspect that some tables are getting updated on each server, but replication is paused trying to update a locked table - and when it is done, each server is trying to send data to the other and replication is broken.

I am seeing a lot of "waiting for table level lock" on select statements when this long query is running.

Is there a setting I've missed to prevent selects from locking tables? Is there some other cause, or any way to fix this?

  • Is any or all of your data MyISAM ??? – RolandoMySQLDBA Jun 26 '14 at 20:34
  • Yes, it's all MyISAM. – chris Jun 26 '14 at 20:41
1

Since all the data is MyISAM, you should expect table locks rather frequently since INSERT , UPDATE, and DELETE command issues a full table lock.

If you have a lot of tables that use auto increment keys, you should add the following

Server1

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 1

Server2

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 2

This will prevent PRIMARY KEY collisions with autoincrement columns

I have other suggestions in my post I have been tasked with Mysql Master-Master replication?

  • That seems to have helped - I wondering why with a 5.0.x system it wasn't need, but we started seeing errors on the 5.5 system? – chris Jun 27 '14 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.