mysql replication - table locking?

Question

I am currently working for a company that has a website running mysql/php (all tables are also using the MYISAM table type).

We would like to implement replication, but I have read in the mysql docs and elsewhere on the internet that this will lock the tables when doing the writes to the binary log (which the slave dbs will eventually read from).

Will these locks cause a problem on a live site that is fairly write-heavy? Also, is there a way to enable replication without having to lock the tables?

Kealey

Answer 1

If you change your table types to innodb, row level locking is used. Also, your replication will be more stable, as updates will be transactional. MyISAM replication is a long-term pain.

Be sure that your servers are version-matched, and ALWAYS be sure to shut down the master before shutting down the slaves. You can bring the master up again immediately after shutting down the slaves, but you do have to take it down.

Also, make sure you use appropriate autoextend options for InnoDB. And, while you're at it, you'll probably want to migrate away from float and double to 'decimal' (which means mysql 5.1.) That will save you some replication headaches.

That's probably a bit more than you asked for. Enjoy.

P.s., yes the myisam locks can cause problems. Also, innodb is slower than myisam, unless myisam is blocking for a huge select.

Ardeliaardelis

Oh, there may be some SQL you can execute to turn off replication on the master, then poll the state on the slaves until they catch up, then turn them off. – Ardeliaardelis

Answer 2

In my experience DBAing a write-heavy site, writing a binary log adds no perceivable problems with locking or performance on the master. If you want to benchmark it, simply turn binary logging on. I really don't think tables are locked to write queries to the binary log.

Table locking on the slave is quite another thing, however. Replication is serial: each query runs to completion before the slave runs the next one. So long updates will cause replication to fall behind temporarily. If your application is intending to use replication for scale-out, it needs to know how to accomodate this.

Tulle

Answer 3

The solution with the myisam table type is not 'better'. However, you can get by with it.

The best you can do, is make sure your slave and master run on the same hardware (FPU differences can create replication errors), as well as making sure you are running the same version numbers on your MySQL servers.

The following link answers your questions. Specifically, locks in MyISAM tables have less of a chance of blocking writes if there are no deletes going on. So a table that doesn't have delete holes in it will perform faster in a replicated setup.

http://dev.mysql.com/doc/refman/5.1/en/internal-locking.html

You can mitigate the effect of 'holes' by have a DBA export/import periodically during scheduled downtimes (especially after mass deletes.) Also, make sure your slave databases don't go down with the master still running. That will save you many, many issues.

Ardeliaardelis

Actually, the FPU difference replication errors can happen with innodb too, any table column that uses float or double. The exact error you'll see (one in so many thousands of times,) is a column width difference error. – Ardeliaardelis