Ljubljana, Slovenia

MySQL & replication "oh, right" moments

We have been running a replicated MySQL setup for many years now. It has been working great for us, but it does make you think of some things in a different way. Not because you're working with a specific function set, but also because of the way the database daemon is constructed, from the ground up, and these little things that add up over time.

A recent "oh" moment was regarding MySQL replication lag. Replication lag is what occurs in certain cases ... loss of network connectivity is one. And looking at our database server, that didn't seem to be the case. The replication was working, the data was being replicated but we had an ever increasing replication lag.

Our first thought was ... ok, this is weird. How can we be 10 minutes behind the master. Replication is working, the master server has almost no load ...

Well, while we explored the option of invalid times between servers, and discarded that because we run NTP on the whole cluster of machines and the differences were minimal, we came to an idea.

The way MySQL replication works is like this (simplified):

  1. The slave opens up a connection (1) to the master server
  2. The master server keeps sending SQL updates to the slave
  3. The slave executes the INSERT/UPDATE statements

Well, if you think about this for a second, you will realize the following. Each connection in MySQL takes up one "thread". One thread is basically a process, which can run on a single CPU core. And the next thought is. "Oh, right". The slave server uses only one thread / CPU core to insert the data going to the master server. The master server uses all cores, because the inserts are coming over multiple connections and in effect - multiple threads.

So, the culprit was an innocent looking INSERT / ON DUPLICATE / UPDATE query, which took about 0,1 seconds. But, when you get hundreds of them, you quickly build up replication lag, since they can't be processed quickly on one cpu core, compared to the four or more cores the master database server is using.

Solution? Well, there you are in luck. Solutions are abound.

  1. Can you optimize the query? Sometimes rewriting it helps, especially when you consider you can use indexes with UPDATE queries.
  2. Consider if the data needs to be replicated. You can turn off replication of a single query in MySQL. This way, the query doesn't get sent to the slaves.
  3. Can you solve the problem outside of the database? Redis seems to be a good option, especially if you're doing some kind of statistics processing.
  4. Maybe the database is wrong for what you're doing? You can still choose any other database (commercial and non-commercial). Favorites include PostgreSQL, MSSQL and other more enthusiastically developed key/value databases like CouchDB and Tokyo Cabinet, some of which give a good performance boost, but be prepared to learn something else than SQL.
  5. Well, if you need the data, can't migrate to another database or fix the query so it runs faster, then eventually your only solution to solve this will be to shard your database over many master servers, to divide the replication work load over many slave servers. This is usually a waste of time and money, especially when you only have "one" problematic query, and virtually no actual load on these servers.
  6. Also to consider would be to get better servers if you can. The CPU speed will continue to be multiplied with many CPU cores, so buying a more powerful CPU will only help you so much when you're using one core. You can however do a little research into proper storage arrays, or if you're doing your work on a smaller scale, just research RAID options, and the use of SSD's (solid state disks). SSD's outperform classical spin-head disks by a wide margin, and in our experience can help to transform a choking system to an almost idle one.

Just as a footnote, I would like to mention that MySQL snapshotting is not much different than the MySQL replication, as the implementation goes. If you're creating your consistent snapshots with mysqldump, the process itself uses only one core, accounting for longer snapshot time (along with lower CPU usage). Since the master (from which you're creating the snapshot) needs to be locked during this procedure, you're blocking all writes on the database during this time.

If you can't afford the long locking snapshot time, consider doing your own multi-threading of mysqldump (dump each table individually and concurrently). The system will be loaded on CPU during the snapshot time, but the time needed to create a consistent snapshot will be much much shorter (about 70% shorter in fact, if you're running a 4 core setup and have many database tables).

- Tit Petric

While I have you here...

It would be great if you buy one of my books:

I promise you'll learn a lot more if you buy one. Buying a copy supports me writing more about similar topics. Say thank you and buy my books.

Feel free to send me an email if you want to book my time for consultancy/freelance services. I'm great at APIs, Go, Docker, VueJS and scaling services, among many other things.