MySQL Replication and table locks

Question

I have the following problem.

I have a MMORPG game based on PHP and MySQL with now around 600 queries per second averagely. I happen to have problems with tables which are locked too long and are holding up other queries. (Update queries for example need to wait on Select queries) and these have to wait so long that the memory is full.

Those queries are needed and I don't think I can optimize them anymore.

Is it a smart idea to use replication for Master-Slave? Master for the writes and slave for the selects? Does the slave has less problems in performance with write statements (from the log-bin) than the master? And will it really help getting less table lock problems?

Thanks in advance, Martin

Ah okay, thanks for answering. So if I use InnoDB for those tables. Is it smart to mix them up with MyISAM?

Because a test table which was 70MB with MyISAM became 200MB with InnoDB. I'd rather had some tables which have problems with tablelocks InnoDB and the others just MyISAM.

Thanks in advance, Martin.

I've merged your two unregistered accounts. You can now edit your question and leave comments under individual answers. — Tim Post

Johan Johan · Accepted Answer · 2011-06-12T20:04:32

Before you think about replication and stuff, try this:

I'm assuming your tables are using MyISAM.
MyISAM locks the whole table on select and makes the update wait until the select is done.

A few idea to consider before thinking about replication

Use InnoDB
Switch your tables to InnoDB.
InnoDB does row locking instead of table locking. Only those rows that are updated and selected get locked.

Consider using aggregate tables
If you do a lot of aggregate queries like:

select sum(score) from score where player_id = 1

Consider making a memory table like this:

CREATE TABLE `test`.`totals` (
  `user_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `score` INTEGER UNSIGNED NOT NULL,
  other_fields .....
  PRIMARY KEY  USING HASH(`user_id`)
)
ENGINE = MEMORY;

Put a trigger after update and after insert like so:

DELIMITER $$

CREATE TRIGGER au_score_each AFTER UPDATE ON score FRO EACH ROW
BEGIN
  UPDATE totals SET score = score + (new.score - old.score) 
  WHERE totals.user_id = new.user_id;
END $$

DELIMITER ;

Now you can get your aggregate select queries from the totals table and you don't need to bump the database on the full tables any more.

A few remarks about master-slave
Note that master slave in MySQL is asynchronous. This means if you select from the slave and update on the master your slave data will lag behind.
This means you are trading your locking problems for time-lag problems.
In a game with scores this might be a problem.
Also, you are complicating your setup.

I'm not an expert on replication so I'll stop here but I think you are jumping into master-slave before exploring the alternatives.

MySQL Replication and table locks

2 Answers