FewBar.com - Make it good

One of my favorite bloggers, Peter over at Percona/MySQL Performance Blog, has thrown down the gauntlet. Basically saying that it would be fairly trivial to write an incremental InnoDB backup tool.

If you see me, and I haven’t run up to you and told you that I am writing/have written an amazing InnoDB incremental backup tool, I give you permission to make fun of me. This sounds like a fun, interesting project that will challenge me and sort of scratches an itch I have, which is, faster MySQL backups.

There are quite a few articles out there that talk about how to give your application some context and send reads to one server, and writes to another. There are even some mentions of marking your connection “dirty” and then sending all reads to the write server.

As a first try at scaling things, I recently made a change to our web application’s data access layer where reads went to a group of readonly slaves. However, if a write was made to a database, a value was put into the user’s session, saying that the database was dirty, and causing all subsequent reads to go to the master server.

This was good as users would use the readonly slaves as long as they hadn’t changed anything in the database. The real problem though, was that as soon as the user logged in, their account was updated to say that they had logged in, marking that database dirty.

Rather than try to cleverly change this one problem, we changed the “dirty” value from a boolean to a timestamp. Whenever the user writes to the database, it records the current time in their session. Then a global timeout is applied to that. This gives the replication slaves time to catch up and get the record that was just changed, then the user will have a consistent view fo their data.

This is great, but I think a further step is to have something publish the actual maximum lag of the slaves into a memcache key, and simply double that value as the timeout. This would allow maximum usage of the readonly slaves and keep the master server busy doing mostly writes.

This week was an ugly one for my monster database servers. It should have been triumphant, but oddly enough, I think it shows how prone to mistuning InnoDB on MySQL 5.0 is with multiple cores.

This server is a multi-core, high concurrency server. The application has been designed a little bit naively in that it just throws almost all queries at the main db server. Several bits have been designed to scale by not doing that, but unfortunately, huge amounts of functionality were built around those apps to prevent them from scaling.

As a result, we’ve had to scale up the central database server and its redundant systems significantly. We started with the Proliant DL380 G4 with two Xeon 3.4Ghz CPU’s and 12GB of RAM, and plenty of disks in an external RAID. As more traffic was added, we moved up to the DL580 servers with 4 Xeon 3.4Ghz and 64GB of RAM. This worked well, but still more traffic, and more data, was coming and the app wasn’t ready to change significantly. We finally landed on the latest DL580 server, with 1GB of total battery backed write cache, 14 SAS disks, 128GB of RAM, and two quad core Xeon CPU’s.

Some things got better. Writes were now incredibly fast. The server was churning out 1000 queries per second easily. Sometimes during peak times, query response time would suffer, but ultimately, the box was keeping up and performing well. Especially after we turned of query caching. After this week though, I wonder how much of the problem was query caching… more later.

Anyway, whenever the server would need to have maintenance, some high traffic applications would suffer needlessly for their need of rarely changing data (memcached was out of the question for the complexity and “realtime” nature of this data). So we setup a selective replication fanout onto multiple boxes and pointed these apps at that cluster for these queries.

Well the next day, without all of these tiny queries pounding on it, the database server had horrible problems. 400 threads stacked up inside InnoDB “Waiting for InnoDB queue”. System resources were fine, but it was clear, InnoDB was having trouble. Queries that normally take 0.75 seconds were taking 300+ seconds, or just never completing. I knew there was real trouble, when killing the thread would result in it just changing state to “Killed”, but never dying. Based on what I’d read in High Performance MySQL, and articles like this one, I tried twiddling with innodb_thread_concurrency, innodb_concurrency_tickets, and innodb_thread_sleep_delay. None of them seemed to help, though innodb_thread_concurrency set to a value of about half the CPU cores seemed to delay the problems.

I noticed that we were running MySQL v5.0.51a still. We had planned an upgrade to 5.0.67, which was just recently released, but hadn’t gotten there yet. I went ahead and upgraded one of the boxes to it, and failed over to it. Instantly things were more healthy, and the health seemed to stay for hours, without any more InnoDB freakouts.

After some research, it would seem that between 5.0.51a and 5.0.67, a lot of really big fixes were made to InnoDB to help it scale up on multi-core machines. The box has been healthy for a couple of days, though there’s still a lot of work to do removing query load from the server.

But why would a _reduction_ in queries cause concurrency problems? I have a theory, but no real ideas on how to test it.

Before, we were doing 1000 queries per second. Things were healthy. We removed about 400 queries per second from that. These 400 queries were basically instantaneous.. often times returning no results at all and reading from tables and indexes completely stored in the innodb_buffer_pool. But, with query cache turned off, they were still being processed fully by InnoDB. When we removed these tiny queries from the queue imposed by innodb_thread_concurrency, I think we removed the equivalent of spin waits from the queue. These tiny, easy queries were just hard enough to process, to prevent a lot of bigger queries from hitting the queue at the same time. Thats why reducing innodb_thread_concurrency to 4 helped a bit.. with only 4 threads vying for mutexes and CPU resources constantly, InnoDB was able to (sort of) keep up.

My final bit of evidence for this is that we actually, I think, had this problem before with the aforementioned article. Turning off the query cache moved these tiny queries out of the query cache, and into the InnoDB queue, providing the needed pseudo-spin-waits to prevent it from locking in on itself.

I have to wonder if raising innodb_sync_spin_loops to something ridiculously high, like 50000, would have the same effect. Unfortunately, its very hard to test this without dedicating a lot of time to it.

So, in this case, it would seem that more work can, in fact, make the server healthier.

So a few days ago, my big mean MySQL server started having problems that were very hard to explain. It was slowing down, taking a minute to run queries that usually take a few seconds, and Linux load averages were in the teens, despite having quiet disks (less than 0.1% cpu IO wait time) and plenty of RAM (128G for about 200G of data total…).

The developers were stumped. The other systems guys were stumped. So was I. But it still seemed ok. We found all sorts of things to point fingers at, but nothing made sense.

Then this Monday, everything came to a screeching halt. 3 second queries were taking 15 minutes. 30 second queries were never completing. The CPU’s were only a little busy. What gives?! This box has 8 CPU cores and 128G of RAM.. nothing can take it down, right?!

We threw our hands in the air and failed over to the active standby (the other side of our master<->master replication pair). Suddenly all was well. But something smelled wrong. We blamed some kind of bug in MySQL.

I spent all day trying to make Memcached more efficient, and trying to explain why suddenly this beast was felled by such tiny arrows as instantaneous queries that should have been cached anyway.

Oh wait, did somebody say cached? As in the MySQL query cache? I mentioned this in the #mysql channel on Freenode, and Mr. Eric Bergen (ebergen) from Proven Scaling immediately said something like “well duh, turn off the cache, moron”. I was dumbfounded. Shouldn’t it be helping us with all those tiny queries?

Well apparently not. This recent thread on the MySQL internals list talks about mutex contention in the query cache while it is *searched*, not just while it is updated. This is disasterous for an environment where thousands and thousands of tiny queries are being run constantly. Even with query_cache_type set to 2, or “cache on demand” mode, every query in the system must run through this mutex.

So, this morning when the standby box again cried for mercy, hitting max_connections and spinning all queries around in circles, I ran ‘SET GLOBAL query_cache_type=2′. Instantly the server became more healthy. I half expected to trade one problem for another.. with the server being consumed by tiny queries. But instead, these tiny queries did as expected, and took very little time to complete. And large queries against tables that change every second or 2 didn’t have to contend for the query cache, they just ran through like nothing.

So, it would appear that for any sort of multi-core installations of MySQL, the query cache is not only a waste, but a hazard!

Thanks again to Mr. Bergen. I would not have thought about that until he said it.

Just a few weeks ago, I found out that thread_concurrency’s purported magical effects at correcting MySQL’s concurrency limitations (especially in 4.1) were something of a myth. It was a post on mysql’s lists that alerted me to this. Apparently it only works on Solaris, Linux’s threading library ignores this parameter completely. This is not to be confused with innodb_thread_concurrency, which is quite useful in controlling the flow of transactions through InnoDB. I think the problem really lies in the fact that the default my.cnf example configs tell us to set thread_concurrency to the number of CPU’s*2. They fail to mention that this only matters on Solaris, though the manual is quite clear.

I think I set this parameter to 1, 4, and 8 trying to see if it would affect things positively or negatively on quite a few 4.1 boxes. I always just sort of assumed it was going to help prevent any sort of snowballing of server load if it ever got hit hard.

Yet another example where its important to RTFM!