Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having big troubles on a database pool I've setup recently.

There is a master, and 4 slaves replicating one DB. All using MyISAM engine.

Thing is, during the replication period (when the slave is not up to date), the IO% showing in iotop is equal to 99% for mysqld.

Besides, I'm having a big latency when connecting a web application to any of these databases (master included).

All these servers are virtual machines running Ubuntu 12.04, having 4vcpu each and 12GB RAM.

If you need more information about the config (system/mysql), please tell me, I'll update my post.

Thing is, I need some hint about where to search, because for now I was not able to identify the problem.

EDIT:

I'm adding the conf files for the master and for a replica :

master configuration file

replica configuration file

share|improve this question
    
Looking at the situation, I feel the problem is at the hardware level and not related to Engine or MySQL. What is the server load when slave tries to replicate queries? Are you able to perform other OS operations efficiently (like copy, move, seek, etc.)? –  Abhijit Buchake Sep 16 '13 at 12:27

1 Answer 1

2 questions: - Where is the IO problem ? On the master or on the replicas ? - If the IO contention is on a replica, can you create a TEST replica, replicating from the same master, but having innoDb engine ?

The thing with MyISAM storage is that an UPDATE / INSERT query will lock the whole table. And the replication thread on each replica is also a serial process, running just one query at a time (event) from the master binlog.

My suggestion would be change the storage engine for all tables, from MyISAM to InnoDb, then re-check the replication parameters.

share|improve this answer
    
The IO problem is on the replicas. I need to stay with the MyISAM engine because where are using full text indexes, so for now, InnoDB is not the solution. –  Tony Apr 18 '13 at 9:46
    
can you post the config files for master and one of the replicas ? –  MTIhai Apr 18 '13 at 17:36
    
Just added the configuration files, thanks for the help. –  Tony Apr 19 '13 at 8:16
    
I get "Unknown Paste ID!" for both links. –  MTIhai Apr 30 '13 at 7:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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