PlanetMysql.ru — информация о СУБД MySQL

I was recently struck by a problem which is unusual. In order to keep an eye on the database server I use nagios, cacti, merlin and some local scripts to monitor the database instance and ensure that it is working properly.  That normally works fine.  The different monitor processes do various things, one of which is to monitor the replication status of a slave, and warn me if the replication is not working or if it’s behind. This is done with the command SHOW SLAVE STATUS.

The server I was looking at runs some large local batch jobs aggregating data. Unfortunately, I was experiencing that replication was interfering with these batch jobs so decided to see if things would perform better if I stopped replication and let the batch jobs complete, restarting replication afterwards.

So the command STOP SLAVE was sent to the server, and this took some time. In the meantime SHOW SLAVE STATUS hangs. The STOP SLAVE command waits for the SQL replication thread to finish it’s task but that thread was waiting on the tables being used by the batch process, which was already running. The consequence of this was that STOP SLAVE waited, all calls to SHOW SLAVE STATUS blocked and with nagios, cacti, merlin and the local scripts all doing periodic SHOW SLAVE STATUS commands which would hang I ended up with the mysqld running out of user connections.  Nasty!

While I see if MySQL can do something about the behaviour of stopping the slave and showing the slave status I’m going to implement grants which limit the monitor users so they are only allowed to have a user concurrent connections open.  This is done using the syntax

GRANT whatever ON whereever TO some_user WITH MAX_USER_CONNECTIONS 5;

5 looks like a good number for nagios, merlin and the local scripts and 10 is probably sufficient as I collect a lot of graph data for the mysql server and the cacti requests are often done in parallel.

So if you monitor MySQL it may be worth you applying some sort of connection limit on your monitoring scripts, if not on other database users.

I was recently struck by a problem which is unusual. In order to keep an eye on the database server I use nagios, cacti, merlin and some local scripts to monitor the database instance and ensure that it is working properly.  That normally works fine.  The different monitor processes do various things, one of which is to monitor the replication status of a slave, and warn me if the replication is not working or if it’s behind. This is done with the command SHOW SLAVE STATUS.

The server I was looking at runs some large local batch jobs aggregating data. Unfortunately, I was experiencing that replication was interfering with these batch jobs so decided to see if things would perform better if I stopped replication and let the batch jobs complete, restarting replication afterwards.

So the command STOP SLAVE was sent to the server, and this took some time. In the meantime SHOW SLAVE STATUS hangs. The STOP SLAVE command waits for the SQL replication thread to finish it’s task but that thread was waiting on the tables being used by the batch process, which was already running. The consequence of this was that STOP SLAVE waited, all calls to SHOW SLAVE STATUS blocked and with nagios, cacti, merlin and the local scripts all doing periodic SHOW SLAVE STATUS commands which would hang I ended up with the mysqld running out of user connections.  Nasty!

While I see if MySQL can do something about the behaviour of stopping the slave and showing the slave status I’m going to implement grants which limit the monitor users so they are only allowed to have a user concurrent connections open.  This is done using the syntax

GRANT whatever ON whereever TO some_user WITH MAX_USER_CONNECTIONS 5;

5 looks like a good number for nagios, merlin and the local scripts and 10 is probably sufficient as I collect a lot of graph data for the mysql server and the cacti requests are often done in parallel.

So if you monitor MySQL it may be worth you applying some sort of connection limit on your monitoring scripts, if not on other database users.

While setting up MySQL Enterprise Monitor 2.2 (Merlin) on a system which had been running version 2.1 I thought I’d try and see what difference the change from using normal innodb tables to using the compressed table format available in the innodb plugin.

I’ve been using a separate db backend for merlin because for me it’s easier to manage and also the database backend has been put on a dedicated server. I’ve also been trying the innodb_plugin on another busier server as I had performance problems with the normal 5.1.42 built-in innodb engine which the plugin managed to solve.

So given that I was using a separate db server I upgraded it to 5.1.47, configured the server to use the plugin (1.0.8) rather than to use the built-in innodb engine and then decided to alter the data tables (dc_p_long, dc_p_string and dc_p_double) to use the new innodb compressed table format. These tables are designed for storing a large number of rows of a specific type but there was no harm in trying.

Here are the results by doing the following:

SET GLOBAL innodb_file_format = "Barracuda";
ALTER TABLE dc_p_xxxx ROW_FORMAT=compressed;


Using the older Antelope storage format:

dc_p_string 178 MB
dc_p_double 514 MB
dc_p_long 15.3 GB

Using Barracuda COMPRESSED:
dc_p_string 35 MB
dc_p_double 223 MB
dc_p_long 6.8 GB

The compressed format is using the standard block size. I need to do further tests to see how much difference using the smaller 1 kb, 2 kb or 4 kb blocks will make.

So from the point of view of Merlin only it does seem to make sense to use this format, assuming performance is not significantly affected. After all it means I can store twice the amount of data on disk, and one of the problems I have had in the past is that I could only keep a week’s worth of data because of storage limitations. Note: the 2.1 to 2.2 update will save a lot of space as the string table will drop in size significantly. However gaining an extra 50% by using innodb compression seems worth doing if it comes for free.

That said I have been told that there are still a few issues with this new table format so for anyone looking to use it in production it may be best to wait for 5.1.48 which should remove a few of these edge cases. If you only want to see how much difference the storage usage is then 5.1.47 should be ok. YMMV.

In the meantime I’m going to leave this server running for a while. Merlin does hammer the db quite heavily, so I’ll be able to see if it survives in a few days.

I also have a few servers which currently use MyISAM tables because of the smaller disk footprint compared to innodb. These servers do suffer from some of MyISAM’s weaknesses such as concurrent reading and writing on the same table is not possible, so now it looks like we might have a good reason to try this compressed table format out and with that gain a lot using innodb. Recovery after a crash has always been a problem on this type of server and innodb recovery should be both quicker and less intrusive.

I’m looking forward to further experimentation but so far this new compressed format does look promising. So thanks to the innodb folk who have made this possible.

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

Since the time that I’ve been using MySQL I have filed quite a few bug reports. Some of these have been fixed and many of the bug reports are actually new feature requests. While working with MySQL Enterprise Monitor I’ve probably filed more feature requests than bug reports.

That’s fine of course and my opinion of what is needed in MySQL or Merlin is one thing,  yours or the MySQL developers is something else. We all have our own needs and find things missing which would solve our specific problems.

If I have ten feature requests open and only one could be added to the software I’d also like to be able to say: this feature is the most important one for me.

However, it seems to me that there is no easy way in the mysql bug tracker at the moment to group together different types of new feature requests into groups of related features and then see the different types of requested features. I imagine many feature requests may be quite similar, but as I do not have a lot of time to look at all bugs it is easy to lose track of the things that people are asking for. It’s also likely that others who might be interested in my feature request are not aware of the request or able to say “I’d like this too”.

Having a clearer list of requested new features, especially if you have a clearer idea of how many people are interested in these new features (whether paying customers or not) would surely be a good way of guiding the product’s development in the way which would be useful to a wider audience. Is there any way this can be done with MySQL, and how is this done with other products which also are complex and have “insufficient resources” to be able to satisfy everyone’s wish?

Currently I do not feel that I can see where MySQL is going or work out if features that I need might actually be implemented in a reasonable time span (or at all) and that is rather frustrating. Some of the “Enterprise” type features that I think are important such as better partition management (variables such as innodb_file_per_table really suck, but the alternatives of X ibdata files which you can’t manage properly are even worse), better replication (taking out the replication process and putting into a separate daemon which would allow you to do N:1 replication, currently impossible in the current MySQL implementation but actually very useful if you want to have multiple sets of replicated databases each handling their own dataset, but with one or more central servers which see the whole combined dataset) are just larger more complex examples but many simpler changes are also important and some I get told will happen after MySQL 7. For me that’s never never land….

So is there a way that this can all be done more transparentlly?

I’ve heard about the innodb_plugin but not had time to put it to the test.

Recently though due to some problems I’ve been having with the MySQL Enterprise Monitor (Merlin) I’ve had to try a few changes and had the opportunity to try out the innodb plugin.

I have been using Merlin for some time and like it a lot. It is not perfect but does a good job for me.  However, since upgrading to version 2.1 I have been having some database load problems. I long ago split the merlin server into a front- and back-end server with the backend running a standard MySQL 5.1 Advanced package. That has been working fine.

I have been monitoring more and more mysqld servers and recently the database backend could not cope. Basically the writes of data collected from the agents and the deletes of old date (purging) caused too much I/O and that is on a box with 6 disks in RAID-10 with a battery backed write-cache.

So I upgraded the db server to a new box with lots of memory and a 300 GB Fusion IO card. I expected all problems to go away. Well not quite. In spite of the solid state drive which was not I/O bound, and the CPU which was not CPU bound, mysqld could not keep up with the load. This was running the MySQL 5.1.44 Advanced rpm. Looking more deeply it seems that mysqld itself was the bottleneck and there was too much contention on the PK by the different INSERTing and DELETing threads.

The Merlin team suggested trying the innodb_plugin (1.0.6) and all of a sudden the bottleneck seems to have gone away.

This is the iostat output taken before switching to the innodb plugin:

Device:    rrqm/s wrqm/s   r/s     w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
fioa         0.00   0.00  4.21 4049.50   33.67 32337.07    16.83 16168.54     7.99   192.42    6.31   0.25 100.22
fioa         0.00   0.00  9.78 4379.44  153.29 34942.12    76.65 17471.06     8.00     0.00    6.17   0.00   0.00
fioa         0.00   0.00 20.80 4455.40  408.00 35552.60   204.00 17776.30     8.03     0.00    5.96   0.00   0.00
fioa         0.00   0.00 21.80 4685.00  267.20 37391.20   133.60 18695.60     8.00    24.00    5.87   0.21 100.02
fioa         0.00   0.00 23.60 5200.40  320.00 41490.40   160.00 20745.20     8.00     0.00    6.04   0.00   0.00
fioa         0.00   0.00 15.17 5020.36  202.79 40055.09   101.40 20027.54     7.99     0.00    5.65   0.00   0.00

This is the iostat output taken after switching to the innodb plugin:

Device:    rrqm/s wrqm/s   r/s     w/s  rsec/s   wsec/s   avgrq-sz avgqu-sz   await  svctm  %util
fioa         0.00   0.00  4.40 2344.80  160.00 201520.00     85.85     0.00    0.41   0.00   0.00
fioa         0.00   0.00  2.00 2188.20   64.00 193500.80     88.38     0.00    0.41   0.00   0.00
fioa         0.00   0.00  1.80 2113.60   57.60 200889.20     94.99     0.00    0.44   0.00   0.00
fioa         0.00   0.00  0.40 1961.60   12.80 194291.20     99.03     0.00    0.43   0.00   0.00
fioa         0.00   0.00  0.00 2118.00    0.00 202496.40     95.61     0.00    0.47   0.00   0.00
fioa         0.00   0.00  0.00 2030.00    0.00 191482.40     94.33     0.00    0.46   0.00   0.00
fioa         0.00   0.00  0.00 2152.60    0.00 208485.20     96.85     0.00    0.44   0.00   0.00
fioa         0.00   0.00  0.00 1936.20    0.00 178732.40     92.31     0.00    0.42   0.00   0.00
fioa         0.00   0.00  4.79 1249.70  153.29 115475.45     92.17     0.00    0.40   0.00   0.00

Note: the first set of figures was taken using CentOS 4, and the second using CentOS 5. The IO statistics aren’t exactly identical and on CentOS 5 for some reason the driver appears not to be providing all the stats. However the wsec/s value clearly shows a significant performance improvement and the original problem mysqld was having of not being able to purge as fast as it was inserting data seems to have been solved. At least initial signs seem to indicate this. The only configuration change made to the server was the following:

ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

innodb_adaptive_flushing = 1
innodb_io_capacity = 1000

Conclusion, if you are having performance problems on your MySQL server and perhaps the hardware is not the bottleneck then try using the plugin. It may make a big difference.

Also a big thanks to the Merlin team for helping me out with this problem and getting things up and running.