Some days ago I got a call from our support engineer on duty that MySQL on one of our database servers was lagging more than 1000 seconds behind in replication and the server got kicked out of the pool because of the delay. He was unable to find out why and there was absolutely nothing in the mysql log files. When I got the call it was still lagging behind but the lag was slowly decreasing again.
After a quick peek in all our monitoring systems I isolated it to this message:
Cache Battery 0 in controller 0 is Charging (Ready) [probably harmless]
Apparently not that harmless!
Obviously we did encountered this situation a couple of times before but apparently there was no detection on this machine.
The relearn cycle happens every 90 days and gets first scheduled when the machine gets powered on. Now imagine this happening in a master-master setup where both machines were powered on at the same time. Lucky enough you can use omconfig to reschedule the cycle up to 7 days, but then you obviously need to have detection in place.
Why did nobody come up with the idea to have a dual battery backed up cache with alternating relearn cycles? That way you can have your battery relearn without the controller going back into write-through mode.
It took me a while to figure out why our new instance wasn’t graphing: the response time query was performing and the script was picking up its values. Also the rrd files were created but for some bizarre reason all values were set to “NaN”, in rrd/cacti terms: Not a Number. If you search on that subject you will come across a lot of (forum) postings stating you need to change your graph type to “GAUGE” or change the MIN/MAX values for your data templates. Strange as this was already set to a minimum of 0 and a maximum of an unsigned 64 bit integer.
After running the ss_get_mysql_stats.php script manually for these graphs I got the error stating that a -1 value was not allowed. Indeed the output of the script contained a -1 value for the last measurement and I quickly found the culprit: an uninitialized array key inside the script will cause it to return a -1 value. Now why was this array key not initialized? Simply because the query filling the array was capped to 13 rows instead of the expected 14 rows.
This left me with three options:
1. Change cacti templates to allow -1 value
2. Change cacti templates to only contain 13 data points instead of 14
3. Change the query in the ss_get_mysql_stats.php script
Naturally I patched the script and after 10 minutes the graphs started to get colorful again!
So if you have the same problem as we do, you can find the patch attached to my bugreport:
Query reponse time and Query time histogram not graphing
I know, I haven’t been posting much lately. 5.5 upgrades got postponed due to the new storage platform needing my immediate attention and being a speaker at the Percona Live conference in April also needs a lot of attention.
One of the things I want to try out is running multiple MySQL instances on the same machine. The concept remained in the back of my mind ever since I attended Ryan Thiessen’s presentation on the MySQL conference 2011 but we never actually got a proper usecase for it. Well, with the new storage platform it would be really beneficial so an excellent use case to try it out! So what have I been busy with in the past week? That’s right: running multiple instances MySQL on one single server.
Even though it is not well documented and nobody describes the process in depth it is not that complicated to get multiple instances running next to each other. However it does involve a lot of changes in the surrounding tools, scripts and monitoring. For example, this is what I changed so far:
1. MySQL startup script
Yes, you really want this baby to support multiple instances. I’ve learned my lesson with the wildgrowth of copies of the various MMM init scripts.
2. Templating of configs
If you want to maintain the instances well you should definitely start using a fixed template which includes a defaults file. In our case I created one defaults file for all instances and each and every instance will override the settings of the defaults file. Also some tuning parts are now separated from the main config.
3. Automation of adding new instances to a host
Apart from a bunch of config files, data directory you really want to have some intelligence when adding another instance. For example only the innodb_buffer_pool_size needs to be adjusted for each new instance you add.
4. Automation of removing instances from a host
Part of the step above: if you can add instances, they you need to be able to remove an instance. This should be done with care as it will be destructive.
After this there is still a long extensive list of things to be taken care of:
1. Automation of replication setup
The plan is to keep things simple and have two hosts replicate all instances to each other. So the instance 3307 on host 1 will replicate to instance 3307 on host 2 (and back), instance 3308 on host 1 will replicate to instance 3308 on host 2 (and back), etc.
2. HA Monitoring needs thinking/replacement
I haven’t found a HA Monitoring tool that can handle multiple instances on one host.
Why is this a problem?
If only one of the MySQL instances needs maintenance you can’t use the current tools unless you are willing to make all other instances unavailable as well. Also what will you do when the connection pool of one instance gets exhausted? Or if one instance on both servers die?
3. Backup scripts needs some changes
Obviously our backup tools (wrapper scripts around xtrabackup) need some alteration. We are now running multiple instances, so we need to backup more than one database.
4. Cloning scripts need some changes
We have a script that can clone a live database (utilizing xtrabackup) to a new host. Apart from the fact that it assumes it needs to clone only one single database we might also go for full cloning of all instances
5. Monitoring needs to understand multiple instances
Our current (performance) monitoring tools, like Nagios/Cacti/etc, only assume one MySQL instance per host. At best I can implement the templates multiple times, but that also increases the number of other checks with the same factor.
And there obviously a lot more things I haven’t thought of yet. As you can see I’ll be quite occupied in the upcoming period…
Shortly after the MySQL 5.5 upgrade the whole cluster was upgraded with extra ram. This was a nice test to see how differently 5.1 and 5.5 behave when they A) innodb bufferpool is too small and B) when the innodb bufferpool has enough room to fit everything in memory.
The MySQL 5.5 had just the same pattern in terms of disk utilization as the other nodes before (around 30% to 40%) and after the upgrade (4% to 5%), so not much difference at all. However the number of free pages within the bufferpool is significantly lower (about 10%) than on the other nodes. This definitely needs some further investigation.
Apart from that the machine is stable and it seems we will proceed with the upgrade on the whole cluster soon.
A sidenote: I’m happy to announce that I was selected as a speaker at the Percona Live MySQL Conference & Expo in San Francisco, April 2012. I’ll be talking about Spil Games (the company I work for) and how our new architecture will solve or ease up the majority of our database issues.
At the company I work for we are still running Percona Server 5.1 in production and are slowly heading towards a Percona Server 5.5 rollout. It did take a lot of preparation in the past few months (write a my.cnf conversion script for example) and a lot of testing. A couple of machines already have been upgraded this week to 5.5 to compare performance and stability. So far the machines proved to be stable enough to keep them on 5.5 and even better: we already see a couple of benefits! However, the title wouldn’t have been blues if everything would have been a breeze, right?
First problem we ran into was that our Cacti templates broke due to the changed InnoDB status output. So I headed towards the Cacti templates and looked in the issue tracker if the issue was already known. Apparently it was already known, but unfortunately not fixed yet. Lucky enough writing the fixes myself wasn’t much of a problem.
Secondly we ran into the issue that the history list was growing from a “steady” 200 to 4000 after upgrade. Searching on this topic revealed a problem with the purge operations but it was not clear to me what exactly was the problem. According to the MySQL documentation the default should suffice. Uhm, right?
Now I knew some things have changed in 5.5 regarding purging: a separate purge thread was already introduced in 5.1 but could it have been so different then? So I tried to find out what each and every purge variable would do:
At first I assumed that increasing the batch size would make the purging more efficient: the larger the batch the more it could handle, right?
Wrong: the larger you set it, the later it will purge! I found this on the MySQL documentation about it:
The granularity of changes, expressed in units of redo log records, that trigger a purge operation, flushing the changed buffer pool blocks to disk.
So the name is actually confusing! In our case it went from 20 to 40 making things worse and then from 40 to 10 making the history list go from 4000 to 1800.
Then I decided to see what the purge lag would do. Changing the purge lag as described by Peter did indeed lower the history list for a short while, but MMM also kicked the 5.5 server out of its pool because it started lagging behind in replication! So this is definitely something to keep in the back of your mind!
I did not change the purge threads to 0 since it is a machine that runs in our production environment. Also confusing is the deprecated innodb_use_purge_thread that could be set to different values than 0 and 1 but is marked as experimental.
I’m positive a part two will come shortly, so stay tuned.
At first I thought it would be an easy problem of the master and slaves being out of sync and the row-based replication failing on not finding the row, but then I noticed all machines were actually still running statement-based replication. As far as I could recall we did that to circumvent another issue that has already been solved months ago but for some reason we never put it back to row-based replication.
A simple SHOW SLAVE STATUS revealed something similar to this:
Last_Error: Error 'Duplicate entry '272369' for key 'PRIMARY'' on query. Default database: 'userdata'. Query: 'DELETE u, uf1, uf2, up FROM `users` u LEFT JOIN `friends` uf1 ON u.id = uf1.id LEFT JOIN `friends` uf2 ON u.id = uf2.friend_id LEFT JOIN `prefs` up ON u.id = up.id WHERE u.name = 'testuser';
Due to one of the machines being configured a little bit too close to the edge we had to restart it to downsize its innodb_buffer_pool_size a little bit. We tested the innodb_lru_dump_restore directive with this machine. Results were very promising: it wrote the dump every 5 minutes (we set the variable to 300 seconds) and after MySQL had restarted it reloaded the dump.
However: since MySQL is already started fully before it even starts to load the LRU dump it means MySQL is already available to the outside world. This means in a HA environment it would already be going to perform poorly due to the torrent of queries coming in. This means either the loading of the LRU dump needs to be done up front by a change in Percona Server or we need to alter MMM not to do anything with the server untill it has loaded the LRU dump.
Yesterday one of my colleagues pointed me to the innodb_lru_dump_restore variable available in the Percona MySQL Server distribution. See more about it here:
LRU meaning that the list is in Least Recently Used order to make it easier to remove old items off the pages. Reading from the description it can be a very nice option for already running servers to maintain this list after startup, but it did not solve my immediate problem: I had to warm up two new freshly cloned read slaves on one of our busiest databases.
Normally I use mk-query-digest with the –processlist option on the (active) master, but in our setup this is seriously flawed because Continue reading
Why MySQL Quicksand? Well, that’s easy enough: from experience I’ve learned that the margin between a fully functional MySQL database server and one that’s going into a grinding halt can be a very thin hairline. Getting it back to work may be a hell but it will, most of the time, be very possible to save your server.
Quicksand has the same trouble: it may appear to be quite sold, but a very small disturbance can cause a sudden decrease in viscosity and change it from solid to a liquid like substance. You will sink into it but, generally, not drown.
What will be posted here? Well not everything will be exclusively MySQL but mostly be related to it. You can expect warstories, brainstorms, reposts, links, etc.
My main goal is that whatever I post here will be useful for someone else.
Just started MySQL Quicksand and hopefully it will turn out to be something fruitful.
Well, I feel the urge to share some experience and willing to learn from the feedback.
What to expect here?
Well I do have a lot of warstories to share and tons of stuff that I try to find out when reading other peoples blogs… More stuff to come soon!