MySQL Replication Over Slow Links/High Latency

MySQL replication is considered to be efficient and usually changes in the master server are performed in the slaves within a single second. 
However, if you suffer from a replication that fails to close the gap, there are two main reasons for it: 

1. Slave Disk Issue: as replication is single threaded per database, usually the slave lags behind due to disk latency when implementing the changes. In this case you should consider using SSD to accelerate the process.
2. Low Bandwidth/High Latency Networking: in case where the two servers are located on remote locations (high latency case) or there is a low bandwidth between the servers, we should focus on minimizing the traffic between the servers using one (or both) of the following methods:
1. Using statement based replication: Row based replication creates a SQL statement per each changed row in the database. Statement based replication is records the actual SQL statement sent by the application. Usually statement based replication is much more efficient from log size aspect. However, you should be aware that it may not work correctly if you use UPDATE ... LIMIT 1 for example.
2. Compressing the traffic: MySQL supports log replication compression using the slave_compressed_protocol parameter. This method will reduce the traffic between the servers by up to 80%. However, compression is compute intensive, so you should be aware of some extra CPU utilization (that is usually not an issue in databases). This parameter should be enabled on both servers:
1. Dynamically from the MySQL command line:SET GLOBAL slave_compressed_protocol = 1;
2. In the MySQL configuration file:#compress master-slave communication
   slave_compressed_protocol = 1

Bottom Line

Understand why your replication lags behind and use the right method to solve it. Yes, it is that easy.

Keep Performing,

Moshe Kaplan

How to Disable MySQL Binlog

@ MySQL, clean the the old bin log files 

SQL> SHOW MASTER STATUS\G

Take the File name

SQL> PURGE BINARY LOGS TO 'file';

@ The /etc/my.cnf

Comment out log-bin

> #log-bin = ....

Restart the mysql

> sudo service mysql restart

Keep Performing,

Moshe Kaplan

Changing MySQL Binary Log Files Location to Another Directory

What is the Default?
Usually in most installations, binary log files are located in the MySQL default directory (/var/lib/mysql) just next to the data files.

Why Should I Move the Binary Logs to Another Directory?
Each data modification (INSERT, UPDATE, DELETE...) and data definition (ALTER, ADD, DROP...) statement that you perform in your server are recorded in the Log files.
Therefore, each time you make any of these statements, you actually update both your data files and your log files. The result is high IO utilization that is focused on a specific disk area.
A common recommendation in the database field is to separate these files to two different disks in order to get a better performance.

How to Perform it?

1. Change the log-bin variable in the my.cnf to log-bin=/path/to/new/directory/mysql-bin
2. Purge as many files as you can (PURGE BINLOG...) in order to minimize the number of moved files (see stop 4).
3. Stop the master (service mysql stop).
4. Move the files to the new directory: mv /var/lib/mysql/mysql-bin.* /path/to/new/directory
5. Start the master again (service mysql start).

Bottom Line
Few steps and your server is ready for more traffic and data.

Keep Performing,
Moshe Kaplan

How to Setup MySQL Replication in 11 Easy Steps

I've provided a link few weeks ago to a great complete guide for MySQL replication. Yet, since then I got many requests to provide a short and east guide for a new MySQL replication setup.

Well, Fasten Your Seat-belts

1. Install a MySQL instance that will serve as a Master
2. Install a MySQL instance that will serve as a Slave
3. Configure the Master my.cnf file (located at /etc/ at CentOS) with the server id and the log file name: 
1. [mysqld]
2. server­-id = 1
3. log­-bin   = master­-bin.log
4. Configure the Slave my.cnf with the server id, reply logs and key configuration databases;
1. server­-id = 2 
2. relay­-log-­index = slave-­relay-­bin.index
3. relay­-log = slave­-relay­-bin
4. replicate-wild-ignore-table=mysql.%
5. replicate-wild-ignore-table=information_schema.%
6. replicate-wild-ignore-table=performance_schema.%
5. For MySQL 5.6: Delete the auto.cnf from your MySQL directory slave> rm -rf /var/lib/mysql/auto.cnf
6. Restart the MySQL daemons on both servers to apply the my.cnf changes.
7. If both servers were just installed there is no need to sync their data files (since they  should have the same data files). O/w you should either stop the slave and master and copy the data files using SCP or perform just perform a mysqldump. Notice! Before copying the files, get the Master location describe below.
8. Get the master location, so we can sync the slave to it:
1. master> FLUSH TABLES WITH READ LOCK;
2. master> SHOW MASTER STATUS;
3. +-------------------+----------+--------------+------------------+
4. | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
5. +-------------------+----------+--------------+------------------+
6. | master-bin.000013 |  1233453 |              |                  |
7. +-------------------+----------+--------------+------------------+
9. master> UNLOCK TABLES;
9. Provide the slave with replication permissions:
1. master> GRANT REPLICATION SLAVE ON *.* to `repl`@`%` IDENTIFIED BY 'slavepass';
2. master> FLUSH PRIVILEGES;
10. Setup the slave using the chosen user/password and the master location we found before:
1. slave> CHANGE MASTER TO
2.     ->     MASTER_HOST='10.17.16.253',
3.     ->     MASTER_PORT=3306,
4.     ->     MASTER_USER='repl',
5.     ->     MASTER_PASSWORD='slavepass',
6.     ->     MASTER_LOG_FILE='master-bin.000013',
7.     ->     MASTER_LOG_POS=1233453;
11. Now, start the slave and verify it's running: 
1. slave> start slave;show slave status\G
12. If everything is Okay, verify the replication really works by:
1. Creating a table and inserting values to it in the master:
1. master> CREATE DATABASE a;
2. master> CREATE TABLE a.b (c int);
3. master> INSERT INTO a.b (c) VALUES (1);
2. Verifying that SLAVE/MASTER> SELECT * FROM a.b; return the same values in the master and slave;
3. Dropping the database in the master: master> DROP DATABASE IF EXISTS a;

P.S If you are interested in implementing the replication using SSL, follow these instructions.

Bottom Line

Only few minutes and you got a running MySQL replication. Even these kind of tasks can be so simple and smooth...  and leaving you time to drink a cup of coffee!

Keep Performing,

Moshe Kaplan 

MySQL Replication Complete Guide

This is probably my shortest post

Mats Kindahl MySQL Replication guide is short, concise and includes (almost) everything you need.


Keep Perfomring,
Moshe Kaplan

MySQL Replication Deadlock Detection and Recovery (The code inside)

The best practice to handle deadlocks (after eliminating them from the code) is recovering from them. Unfortunately, if you ever came across the "SHOW SLAVE STATUS\G" Error 'Deadlock found when trying to get lock: try restarting transaction', you probably know that MySQL Replication does not recover automatically from deadlocks.


Therefore, I implemented a Nagios plugin. This Nagios plugin detects the MySQL Replication deadlock and recover from it by restart the slave ("STOP SLAVE; START SLAVE;"). 


Technical
Brief: This a shell script (/bin/sh) that connects to a specified MySQL slave database, check the replication status for Deadlock errors + Automatically recover from the state by performing "SLAVE STOP; SLAVE START;"
Required parameters: host, user, password.
If no Deadlock is detected, "OK" is returned
O/w, a message is printed, slave restarted and Warning state is returned.


Bottom Line
Fast, Simple and very effective and now available from Nagios Exchange.


Download the MySQL Replication Deadlock Recovery Nagios Plugin.

Suggestions are welcome,


Keep Performing,
Moshe Kaplan

Adding One More MySQL Slave to Cloud Based System (Amazon AWS)

MySQL Replication tends to be complex.

Creating a new slave to support your system expansion is not easier: coping large data files over the LAN, recovering replication, etc.

Fortunately, virtualization and SAN help us do it easier and faster.

Lucky us, Cloud Computing make it even easier.

Adding a New Slave is Easier than Ever

1. Select an existing slave that can be reboot w/o affecting the service.
1. Take the slave out of the production environment.
2. Stop the MySQL slave and perform a read lock: SLAVE STOP;FLUSH TABLES WITH READ LOCK;
3. Create an AMI based on this instance using AWS console or API.
4. Recover the instance upon AMI creation completion and get the slave back to production: UNLOCK TABLES;SLAVE START;SHOW SLAVE STATUS\G
2. Create a new slave based on the AMI
1. Launch a new slave using the AWS console or API.
2. Change the server's /etc/my.cnf file to fit a new slave instance:
1. Change the server-id=N value to a new numeric value.
2. Set relay-log='server-relay-bin' to the original slave server relay log name. (if you want to make it even easier, configure in the first slave's my.cnf relay-log=mysqld-relay-bin, and you'll be able to avoid this step).
3. Restart the MySQL instance: sudo /etc/init.d/mysql restart
4. Recover the MySQL: UNLOCK TABLES;SLAVE START;SHOW SLAVE STATUS\G
5. Add this new slave to the production environment.

Bottom Line

Expanding your business today is easier w/o the need for file copy or any other tedious action.

Keep Performing,

Moshe Kaplan 

Monitoring and Taking Care of MySQL Replication

If you get your slave into production soon, your should make sure that you know how take care of it when something goes wrong. 

Knowing that the slave is stuck can monitored using open source tools like Nagios and its MySQL plugins. In general it is wise to have such a tool to monitor all your system and business aspect including the web server and login rates.

How do I know that my slave is stuck or not?

1. Connect to MySQL on the slave using permitted user.
2. Run the show slave status\G command.
3. The result should include the following lines with a Yes value. If any of them is No, there is a problem:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

It is stuck, What should I do?

1. Connect to the file system with a permitted user.
2. Check the values of the replication error log and take care according to it (tail -n 50 /var/lib/mysql/responder3.spd.co.il.err).

Bottom Line
Get into production!


Keep Performing,

Moshe Kaplan 

Cloud Computing Design and Best Practices

Today I presented "Cloud Computing Design and Best Practices" in CloudCon, one of the largest cloud events in Israel.

It was a great lecture and I would like to share with you some of the lecture's insights:

1. Assumptions: Don't refer cloud computing as a brave new world. It is, but when you design your cloud system, don’t forget to keep care of the basic rules you had before.
2. Create A Road Map: You probably will be able to turn your system into a 1 Billion users system in day one. Therefore, design your road map and understand how you will reach your target. In order to get there, you should define the various system parts and you should understand how each of them will be scaled out/removed/replaced in the future to meet your road map.
3. Start Fast: People love success. Your investors love it, Your Marketing guys Love it, Your Customers love it and even Your Development guys do. Therefore, start safe and fast. If your development is great in C#, start with it. If you have an existing software product, start with it as well. It is always better to start with a model based on technologies and products that you are good at, then getting into an adventure that you cannot control its risks.
4. Minimize Costs: After you decided to take the fast track, you should control your costs, and most important: your growth costs. You should go over your business plan and turn it into technical requirements plan and find your bottlenecks. Based on these bottlenecks, define solution for each of them: if you are in the online ads market, take care of your impressions module; if you are in the video business, take care of your video processing module. Why? In the viral and online business the growth is exponential. Therefore, cost growth is exponential, and you have to take care of it before your budget will run away.
5. Best Strategies:
1. Scale out: think of sharing nothing, understand how to take each server and split it to infinite number of servers. If a larger server procurement is your best solution, you probably in the wrong direction.
2. Sharding: Data is usually the largest obstacle for scaling out, as conservative designs concentrate the data in a single place. If you have a similar case, take the path the giants already taken: Shard your database either if it is MySQL or SQL Server (read my Best Seller Sharding Post) 
3. In Memory Database: In Memory is X5-X10 faster than on disk. Therefore, analyze your system, and understand what you can do without going to disk. In case you may be able avoid neglected number of transactions, use this technique to cut your costs.
6. Refactor on the Run: as a player in a growing business, you don't have the option to rest. A 100 users system is different than a 100M users system, and as the system grows, smaller modules that were neglected in the first phase will become more important in terms of bottleneck, cost or business sensitivity. Your way to handle it should be refactor the system, step after step to meet the business goals.
7. Define the Exit Strategy: You should always remember that your cloud operator is still a vendor and your best partner in the early days may become an obstacle when you become a giant. Therefore, choose carefully your cloud provider tools. I would recommend you to think twice before you choose propriety data stores like SimpleDB, and if you do: Create your own interface and have an exit strategy when needed.
8. Everybody is using Open Source. What if your organization is not an expert in this field? As written before, you should start fast. There are plenty of cloud providers that support Windows and .Net, and you can get a head start if you will use the technology you are familiar with. When you grow, you may refactor your product and add technologies to remove your bottlenecks such as Erlang for Push or LAMP to handle your most common processes that result in 90% of your costs.
9. Looking for more strategies?
1. CDN: extract your static and streaming content to CDN provider. This move will cut your server and network utilization and will improve your end user experience.
2. Smart Clients: turn your end user client to be sensitive to network failures. If you are using Gmail and seen the loading... label instead of 404 page, you probably understand what I'm talking about (otherwise Google for JQuery).
3. Elastic Growth: if your system pattern usage is not uniform, consider turning on and off some of your instances to keep costs down and meet the spikes.
4. Replication: don't forget to keep your data safe and meet failures. Make sure you do it using commodity hardware and software.
5. Prepare for downtime & upgrades: make sure that you can always go on. Downtimes will come, since in large scale everything happens, even what should have not. Make sure you never really shut down you whole system even when you upgrade it.
6. NoSQL and SQL: choose SQL as a start if you are great at it. But don't neglect NoSQL when you will get larger.
10. Risk Management: You are going to do a bold move ahead, and you should prepare yourself as I mentioned before:
1. Choose your vendors carefully and know your exit strategy. Your Cloud Operator is a provider as well.
2. Hedge costs and take care of your bottlenecks.
3. Stress your system all the way to guarantee you can get to the next level.
4. Think one move ahead and keep aligned to your strategy.
5. Listen to your users feedback. Your business depends on your clients; take care of them.

Cloud Computing Design Best Practices

View more presentations from Moshe Kaplan.

Bottom Line
Working according to these rules can help you reach the 1 Bill users system you were looking for


Keep Performing,
Moshe Kaplan

Lessons from Facebook:

FB maybe is not the best example for quality design (probably each of us is likely to have at least a single error message a day).

However, since it's the 2nd largest site in the world with contsant exponential growth, it's a good lesson to have. Most interesting that their tech is team is only about 250 people and 30K servers, pretty amazing.


Not long ago a Jeff Rothschild, the Vice President of Technology at Facebook gave a presentation in UC San Diego. You can find a detailed summary by Prof. Amin Vahdat, but I'll make several top comments that I found useful.

1. FB Software development: mostly PHP (complied), but other languages are used as well.
   
2. Common interface between services using an internal development that was turned to open source: Thirft. This interface enables easy connections between the different languages.
   
3. Logging Framework that is not dependent on a central repository or its availability. FB is using Hadoop and well as Hive (that was developed there as well). The log size is growing at a 25TB a day.
   
4. Operational Monitoring: Separated from the logging mechanism 
5. LAN can be a bottleneck as well: expect for packet loss and packet drops in the LAN if you stress it too much.
   
6. CDN: Facebook is using external CDN to images distribution.
   
7. Dedicated file system named Haystack that combines simple storage along with cache directory: file system is accessed only once to get images from it, while directory structure is retrieved from the cache.
   
8. Most data is served from Memcached. Database is used mostly for Persistency and data replication between sites (Memcached is being heated by the MySQL itself):
1. Top challenge: keeping data consistent since Memcached can be messed easily (No search for keys is available).
2. Mixing information including sizes and types is better - making sure that load on CPU, Memory and etc is distributed equally.
   
9. Shared nothing - Keep your system independent - avoid a single bottleneck. Therefore, data is saved in a sharded MySQL from day 1. However, MySQL is used mostly for data persistency and not for conservative database usage pattern:
1. No Joins in the MySQL
2. Chosen due to good data consistency + Management Software
3. 4K servers
   
4. Data replication between sites is based on MySQL replication
5. Memcached is being heated based on the MySQL Replication using a custom API

One last thing, if you are interested in Facebook financials, as well the storage machines (NetApp low end 3070), sizing, traffic, servers, storage procurement and data center costs, take a look at TechCrunch's Michael Arrington post.

Keep Performing,
Moshe Kaplan. Performance Expert.