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

[UPDATE 2011/01/11] : New poll added, vote for the best GUI client tool ! (And continue to vote for other polls)
And thanks again for your involvement. It’s time to vote again… 

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL+ Community Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.
And, please, share these polls !

Happy 2012 !
Cédric

This article is obviously not sponsored !
(MySQL is a trademark of Oracle Corporation and/or its affiliates)

Sources :

• Oracle products, tools and services : http://www.mysql.com
• SkySQL products, tools and services : http://www.skysql.com
• Percona products, tools and services : http://www.percona.com
• FromDual products, tools and services : http://www.fromdual.com
• PalominoDB products, tools and services : http://palominodb.com/
• Pythian products, tools and services : http://www.pythian.com/
• Blue Gecko products, tools and services : http://www.bluegecko.net/
• Zabbix : http://www.zabbix.com/
• MySQL Cacti templates : http://code.google.com/p/mysql-cacti-templates/
• Nagios : http://www.nagios.org/
• r1soft CDP for MySQL : http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/ 
• mylvmbackup tool : http://www.lenzg.net/mylvmbackup/
• mydumper tool : http://www.mydumper.org/
• ZRM tools : http://www.zmanda.com
• Galera replication : http://codership.com/products/galera_replication
• Tungsten replicator : http://code.google.com/p/tungsten-replicator/
• DRBD : http://www.drbd.org/
• ShoonerSQL : http://www.schoonerinfotech.com/products/schoonersql
• ScaleBase : http://www.scalebase.com/
• common-schema : http://code.google.com/p/common-schema/
• innotop : http://code.google.com/p/innotop/
• MySQL Tuner : https://launchpad.net/mysqltuner/2.0/mysqltuner-2.1-rev17
• mycheckpoint : http://code.openark.org/forge/mycheckpoint
• MariaDB : http://mariadb.org/
• TokuDB : http://www.tokutek.com/
• InfiniDB : http://infinidb.org/
• Drizzle : https://launchpad.net/drizzle
• Amazon SimpleDB : http://aws.amazon.com/fr/simpledb//180-2635771-0721067/
• mongoDB : http://www.mongodb.org/
• Cassandra : http://cassandra.apache.org/
• CouchDB : http://couchdb.apache.org/
• Tokyo Cabinet : http://fallabs.com/
• Riak : http://basho.com/
• Hadoop : http://hadoop.apache.org/
• Tarantool : http://tarantool.org/
• memcached : http://memcached.org/
• MySQL Sandbox : http://mysqlsandbox.net/
• openark kit : http://code.openark.org/forge/openark-kit
• Redis : http://redis.io/
• Ganglia : http://ganglia.sourceforge.net/
• HandlerSocket : https://github.com/ahiguti/HandlerSocket-Plugin-for-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.

I was trying to setup cacti on my ubuntu desktop to gather metrics from few new database servers we were planning to replace. Installed cacti using the following command

apt-get install cacti

Then the next step was to download and the install the cacti mysql templates from http://code.google.com/p/mysql-cacti-templates/. Thanks to Baron, was able to get this up and running fast using the templates. But had one problem though, while importing the mysql template “cacti_host_template_x_mysql_server_ht_0.8.6i-sver1.1.7.xml” cacti bombed with the below error

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 41 bytes) in /usr/share/cacti/site/lib/xml.php on line 32

The fix was to edit “/etc/php5/apache2/php.ini” change the parameter “memory_limit” from 16M to 32M and restart apache using the command “/etc/init.d/apache2 restart“. Now cacti was able to import the template without any issues.

While configuring cacti, by mistake selected apache instead of apache2 during the configuration and was not able to access the cacti ui. Ran the following command to reconfigure cacti.

dpkg-reconfigure cacti

The traditional way to build Cacti templates is through the Cacti web interface. This is an enormous amount of work, and the result is generally not very consistent or good quality. The process is too error-prone. You can export the templates as XML, but they tend to have problems such as version incompatibilities with other Cacti installations, and it’s hard to adapt them for user preferences such as different graph image sizes and polling intervals.

The way I build Cacti templates is exactly the opposite. I create a data structure in a file, which looks like many configuration file syntaxes you’ve probably worked with. It represents the graphs, templates, scripts, and so on. From this, a tool generates the XML template file, which is a universal template definition, and is a breeze to import into Cacti. It is completely consistent and has zero cruft in it. This process prevents errors, and the results are perfect every time. (There’s a test suite, by the way.)

All the tools, documentation, how-tos, examples, and pre-fabricated scripts and templates you need are at the Better Cacti Templates open-source project. If you want to build your own templates, pay special attention to the documentation on creating graphs.

Related posts:

1. Version 1.1.4 of improved Cacti templates released I’ve
2. Version 1.1.5 of improved Cacti templates released I’ve
3. Version 1.1.7 of Better Cacti Templates released I’ve

Related posts brought to you by Yet Another Related Posts Plugin.

I’ve released version 1.1.7 of the Better Cacti Templates project. This release includes a bunch of bug fixes, some new graphs for MySQL, and two new sets of graphs, for Redis graphing and for JMX graphing.

There are upgrade instructions on the project wiki for this and all releases. There is also a comprehensive tutorial on how to create your own graphs and templates with this project. Use the project issue tracker to view and report issues, and use the project mailing list to discuss the templates and scripts.

The full changelog follows.

  * Added graphs for Innodb_row_lock_waits and Innodb_row_lock_time (issue 118).
  * Added graphs for the MyISAM key buffer memory consumption (issue 127).
  * The loadavg/users graphs failed when only 1 user was logged in (issue 131).
  * The Apache and Nginx graphs couldn't use the --port2 option (issue 129).
  * The InnoDB Log graph's unflushed_log should be GAUGE not COUNTER (issue 10).
  * Added graphs for InnoDB semaphore waits (issue 124).
  * Added Redis graphs (issue 90).
  * Added --openvz_cmd configuration and command-line option (issue 130).
  * Added --mysql_ssl configuration and command-line option (issue 103).
  * Added JMX graphs (--type jmx) (issue 139).
  * Big-integer math printf spec was missing a % character (issue 137).
  * Partially accepted patches from Artur Kaszuba (more remaining in issue 120).

Related posts:

1. Version 1.1.2 of improved Cacti templates released I’ve
2. Version 1.1.6 of Better Cacti Templates released I’ve
3. Version 1.1.5 of improved Cacti templates released I’ve

Related posts brought to you by Yet Another Related Posts Plugin.

I’m speaking at the O’Reilly MySQL Conference 2010. I hope I don’t lose my voice, because I have four sessions!

• Diagnosing and Fixing MySQL Performance Problems
• EXPLAIN Demystified
• Read-Write Splitting: Techniques, Challenges, and Solutions
• MySQL Graphing and Trending with Cacti

You can click through on the links above to learn more about each session. I’m also looking forward to the other sessions. Here’s a sample of a few that I have my eye on:

• Linux performance tuning and stabilization tips by Yoshinori Matsunobu
• MySQL Proxy meets: Memcache by Jan Kneschke
• PHP Object-Relational Mapping Libraries In Action by Fernando Ipar

The schedule is far from complete, because the conference committee is still working on it, but the proposals to choose from are impressive. Stay tuned as more talks are approved and the schedule fills out, and don’t wait too long to register and book your flight! This is going to be a banner year.

Related posts:

1. Submit your proposals for MySQL conference 2010 The MySQL
2. There will be an O’Reilly MySQL Conference in April 2010 O’Re
3. Speaking at EdUI Conference 2009 I’m

Related posts brought to you by Yet Another Related Posts Plugin.

I’ve released version 1.1.6 of the Better Cacti Templates project. This release includes a bunch of bug fixes (but not all of them!) and two new sets of graphs. One set is for disk I/O on GNU/Linux, and the other is a new set of templates for OpenVZ. I’m looking for feedback on both of those. This release also has a bunch of code-level features: much better test coverage (hooray!), and a refactored ss_get_by_ssh.php that makes it much easier to create new graphs and templates. The SSH-based templates also take advantage of the same caching as the MySQL templates, which makes them a lot more efficient.

There are upgrade instructions on the project wiki for this and all releases. There is also a comprehensive tutorial on how to create your own graphs and templates with this project. Use the project issue tracker to view and report issues, and use the project mailing list to discuss the templates and scripts.

The full changelog follows.

2010-01-10: version 1.1.6

  * Added OpenVZ graphs (--type openvz) (issue 95).
  * Added IO usage graphs (--type diskstats) (issue 97).
  * Added extra error-reporting (issue 110).
  * The $debug $debug_log options couldn't be set in the .cnf file (issue 110).
  * Added a --use-ssh option to ss_get_by_ssh.php (issue 66).
  * Added a debugging log to ss_get_by_ssh.php (issue 54).
  * Enabled caching of results in ss_get_by_ssh.php (issue 46).
  * Added a test suite for ss_get_by_ssh.php (issue 110).
  * The 'free' stats suffered from PHP's issues with big numbers (issue 102).
  * There was ambiguity (but no error) in SHOW STATUS overrides (issue 106).
  * It was hard to debug failures caused by missing ext/mysql (issue 105).
  * Code to make ss_get_mysql_stats.php testable was broken (issue 108).

Related posts:

1. Version 1.1.4 of improved Cacti templates released I’ve
2. Version 1.1.5 of improved Cacti templates released I’ve
3. Version 1.1.2 of improved Cacti templates released I’ve

Related posts brought to you by Yet Another Related Posts Plugin.

If you’d like some improvements to the Cacti templates (MySQL, Apache, Memcached, and more) I’ve been maintaining, please make your voice heard — either write to the mailing list, or post a new issue (or comment on an existing one) on the issue list. I’ve got a bunch of work underway, including a test suite for ss_get_by_ssh.php.

Related posts:

1. Version 1.1.2 of improved Cacti templates released I’ve
2. News on MySQL Cacti Templates It’s
3. Version 1.1.5 of improved Cacti templates released I’ve

Related posts brought to you by Yet Another Related Posts Plugin.

I’ve released version 1.1.4 of my improved Cacti templates. Unlike the prior release, which was solely bug fixes, this one includes new graphs in the MySQL template. Some of the graphs are of data that’s exposed in standard MySQL versions, but some of it is available only in Percona’s high-performance builds of the MySQL database server. If you don’t have a Percona build, those graphs will just contain nothing, but there is no detrimental effect.

This template release is fully backwards compatible with the previous release. The upgrade process is to copy the new PHP file into place and import the new template file. You can then add the new graphs to your hosts.

I’ve also taken the opportunity to address many of the other open issues. Most of these were minor, such as a debug log to help troubleshoot. Some are big improvements, such as no longer using GET_LOCK() in MySQL to arbitrate access to the cache file. A list of open issues can always be found at http://code.google.com/p/mysql-cacti-templates/issues/list.

To ease the process of future upgrades, I’ve also added in a gratuitous GPRINT definition, which is unused and merely records the installed version of the template (both the template version, and the version of the PHP file against which it was generated).

The full changelog follows.

2009-10-25: version 1.1.4

        * Changed SSH options so host keys are accepted automatically (issue 68).
        * Parsing of the pending_ibuf_aio_reads property was broken.
        * Parsing of the pending_aio_log_ios property was broken.
        * Parsing of the pending_aio_sync_ios property was broken.
        * Added a debugging log for ss_get_mysql_stats.php (issue 54).
        * Added the --lint_check option to make-template.pl (issue 80).
        * Removed the use of GET_LOCK() and changed to flock() instead (issue 78).
        * The template and script version is now recorded in a GPRINT (issue 79).
        * Restored unflushed_log, which was accidentally deleted in 1.1.3.
        * Added the InnoDB Internal Hash Memory Usage graph (issue 75).
        * Added the InnoDB Checkpoint Age graph (issue 73).
        * Added the InnoDB Insert Buffer Usage graph (issue 74).
        * Added the InnoDB Active/Locked Transactions graph.
        * Added the InnoDB Memory Allocation graph.
        * Added the InnoDB Adaptive Hash Index graph.
        * Added the InnoDB Tables In Use graph (issue 32).
        * Added the InnoDB Current Lock Waits graph.
        * Added the InnoDB Lock Structures graph (issue 32).

Related posts:

1. Version 1.1.2 of improved Cacti templates released I’ve
2. Version 1.1.3 of improved Cacti graphs for MySQL released I’ve
3. A tip when upgrading mysql-cacti-templates A client r

Related posts brought to you by Yet Another Related Posts Plugin.