Logging with MySQL

Wed, Mar 24, 2010 01:20 AM
I was reading a post by Cassandra is my NoSQL solution but..". In the post, Dathan explains that he uses Cassandra to store clicks because it can write a lot faster than MySQL. However, he runs into problems with the read speed when he needs to get a range of data back from Cassandra. This is the number one problem I have with NoSQL solutions.

SQL is really good at retrieving a set of data based on a key or range of keys. Whereas NoSQL products are really good at writing things and retrieving one item from storage. When looking at redoing our architecture a few years ago to be more scalable, I had to consider these two issues. For what it is worth, the NoSQL market was not nearly as mature as it is now. So, my choices were much more limited. In the end, we decided to stick with MySQL. It turns out that a primary or unique key lookup on a MySQL/InnoDB table is really fast. It is sort of like having a key/value storage system. And, I can still do range based queries against it.

But, back to Dathan's problem: clicks. We store clicks at dealnews. Lots of clicks. We also store views. We store more views than we do clicks. So, lots of views and lots of clicks. (Sorry for the vague numbers, company secrets and all. We are a top 1,000 Compete.com site during peak shopping season.) And we do it all in MySQL. And we do it all with one server. I should disclose we are deploying a second server, but it is more for high availability than processing power. Like Dathan, we only use about the last 24 hours of data at any given time. There are three keys for us doing logging like this in MySQL.


MyISAM supports concurrent inserts. Concurrent inserts means that inserts can add rows to the end of a table while selects are being performed on other parts of the data set. This is exactly the use case for our logging. There are caveats with range queries as pointed out by the MySQL Performance Blog.

Rotating tables

MySQL (and InnoDB in particular) really sucks at deleting rows. Like, really sucks. Deleting causes locks. Bleh. So, we never delete rows from our logging tables. Instead, nightly we rotate the tables. RENAME TABLE is an (near) atomic process in MySQL. So, we just create a new table.
create table clicks_new like clicks;
rename table clicks to clicks_2010032500001, clicks_new to clicks;

Tada! We now have an empty table for today's clicks. We now drop any table with a date stamp that is longer than x days old. Drops are fast, we like drops.

For querying these tables, we use UNION. It works really well. We just issue a SHOW TABLES LIKE 'clicks%' and union the query across all the tables. Works like a charm.


So, I get a lot of flack at work for my outright lust for Gearman. It is my new duct tape. When you have a scalability problem, there is a good chance you can solve it with Gearman. So, how does this help with logging to MySQL? Well, sometimes, MySQL can become backed up with inserts. It happens to the best of us. So, instead of letting that pile up in our web requests, we let it pile up in Gearman. Instead of having our web scripts write to MySQL directly, we have them fire Gearman background jobs with the logging data in them. The Gearman workers can then write to the MySQL server when it is available. Under normal operating procedure, that is in near real time. But, if the MySQL server does get backed up, the jobs just queue up in Gearman and are processed when the MySQL server is available.

BONUS! Insert Delayed

This is our old trick before we used Gearman. MySQL (MyISAM) has a neat feature where you can have inserts delayed until the table is available. The query is sent to the MySQL server and it answers with success immediately to the client. This means your web script can continue on and not get blocked waiting for the insert. But, MySQL will only queue up so many before it starts erroring out. So, it is not as fool proof as a job processing system like Gearman.


To log with MySQL:
  • Use MyISAM with concurrent inserts
  • Rotate tables daily and use UNION to query
  • Use delayed inserts with MySQL or a job processing agent like Gearman
Happy logging!

PS: You may be asking, "Brian, what about Partitioned Tables?" I asked myself that before deploying this solution. More importantly, in IRC I asked Brian Aker about MySQL partitioned tables. I am paraphrasing, but he said that if I ever think I might alter that table, I would not trust it with the partitions in MySQL. So, that kind of turned me off of them.
Gravatar for till

till Says:

Ha, data warehousing! :D

I remember trying the same a few years back.

My first attempt in absence of rotation of any kind was writing into a single table. It failed miserably after a single week due to the volume. I also couldn't run any useful query on the set.

I also tried using merge tables, etc.. But that didn't exactly suck less. You get smaller tables, but the queries across all of them are still slow. I guess the smallest "unit" in your reports is how you rotate your tables (e.g. daily, hourly, weekly, ...).

I haven't really found a solution to this problem.

On a recent project we decided to use Google Analytic's ajax APIs.

Gravatar for till

till Says:

I totally forgot what I wanted to ask -- I recently read great reports about TokuDB (a storage engine for MySQL) and I was wondering if you had evaluated it.

And then, there's also Percona's xtraDB.

It sure sounds like you have a lot of data to process.

Gravatar for Brian Moon

Brian Moon Says:

I have not tried TokuDB as it is not Open Source. I am pretty big on OSS. Although I have not tried xtraDB, it is still InnoDB guts so it has the slower insert times that comes with transactional storage engines when compared to MyISAM with concurrent inserts.

Gravatar for till

till Says:

I tend to agree on the OSS point of view -- but the TokuDB engine is free up until 50 GBs (or so) of data.

So far I opted for innodb mostly because of its robustness. Especially after a crash. And read speed can be drastically improved with a little reading. I must admit I never benched INSERT.

Gravatar for Mark Robson

Mark Robson Says:

Yes, this is pretty much what we do. Except we don't use INSERT DELAYED or Gearman, we have a custom audit data queueing system which stores the data in local files.

And we probably process more rows than you, and keep them longer :)

There are a lot of problems with MyISAM, I dislike it, but I do agree that it is possible to do useful work with it. Of course concurrent insert it vital (you cannot work without).

The main thing about MyISAM is that recovering large tables is very time-consuming.

As we have heaps and heaps of indexes, inserting can be (relatively) slow. To mitigate this, we use DELAY_KEY_WRITES which is ok until you have a crash, then the table needs repair (which it probably would anyway).

Replication almost always breaks following a crash, as there are some rows which end up in the slave but not the master - this causes duplicate key errors. We have procedures for fixing this. Crashes don't happen that often.

Gravatar for Brian Moon

Brian Moon Says:

This post just covers our real time "what is happening now" data for article popularity. We also files for long term, permanent logged data. I don't know how many you process, but we have about 15x more permanently logged rows than these real time logs. But, that is all done offline and therefore not as sensitive to the insert speed as something that is happening in "real time".

Gravatar for Oliver

Oliver Says:

Nice idea with gearman in the middle. I used to use a self written FIFO queue based on memcache, which did basically the same.
So are you stopping gearman somehow from inserting while you rename the tables, just in case?

The issue we had with partitioning a huge myisam table was that it didn't do concurrent inserts anymore. We weren't able to figure out if it was partitioning, but we never deleted, and never got concurrent inserts to work, although we did try for quite some time.

Gravatar for Brian Moon

Brian Moon Says:

MySQL locks the tables while it renames them. That is why we do it in one operation like that instead of two. So, queries just backup for a microsecond while the rename happens.

Gravatar for codemedic

codemedic Says:

I quite liked the solution; and I couldn't agree more on partitions. I looked at it recently and it does look not spot on for this kind of solutions.

I have one question on the long term viability of this approach.
How do you intend to deal with the mysqld running out of file handles, if the number of tables are to grow at a factor of X per day?

Gravatar for Brian Moon

Brian Moon Says:

@codemedic You could add more servers and start sharding in addition to partitioning.

Comments are disabled for this post.