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

I’ve just released version 1.1.3 of the Cacti templates I wrote for MySQL. This is a bug-fix release only, and affects only ss_get_mysql_stats.php. To upgrade from the previous release, upgrade ss_get_mysql_stats.php. Don’t forget to save and restore your configuration options, if any. (Note that there is a feature to help with this: you can keep configuration options in ss_get_mysql_stats.php.cnf to avoid making them in ss_get_mysql_stats.php.)

Next up: actual template changes! More graphs!

The changelog follows.

2009-10-24: version 1.1.3

        * This is a bug-fix release only, and contains no template changes.
        * To upgrade from the previous release, upgrade ss_get_mysql_stats.php.
        * MySQL 5.1 broke backwards compatibility with table_cache (issue 63).
        * Added a version number to the script (partial fix for issue 79).
        * Added a test suite (issue 76, issue 59).
        * Math operations were done in MySQL instead of PHP (issue 25).
        * SHOW STATUS values didn't override SHOW INNODB STATUS parsing (issue 24).
        * Long error messages were not appearing in the Cacti log.
        * SHOW INNODB STATUS parsing for unpurged_txns was broken.
        * SHOW INNODB STATUS parsing for innodb_lock_structs was broken.
        * SHOW INNODB STATUS parsing for pending_log_flushes was broken (issue 62).
        * SHOW INNODB STATUS parsing for pending_buf_pool_flushes was broken.
        * SHOW INNODB STATUS parsing for pending_ibuf_aio_reads was broken.
        * SHOW INNODB STATUS parsing for pending_aio_log_ios was broken.
        * SHOW INNODB STATUS parsing for pending_aio_sync_ios was broken.
        * Made SHOW INNODB STATUS parsing less sensitive to false positive matches.

Related posts:

1. Version 1.1.2 of improved Cacti templates released I’ve
2. Maatkit version 4334 released Maatkit ve
3. Maatkit version 3329 released Maatkit ve

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

I’ve had plenty of comments on specific products to Monitoring MySQL Options before providing the completed list. Here are the results from my survey to give everybody a more complete list.

NOTE: Some answers included multiple products, these are all counted separately in the above figures.

There are a few products that are not listed at Monitoring MySQL in this list.

If you want to list what you use, please continue to use the MySQL Alert Monitoring Survey. Thanks to all those that replied.

My recent poll What alert monitoring do you use? showed 25% of the 58 respondents to bravely state they had no MySQL monitoring. I see 1 in 3, ~33% in my consulting so this is consistent.

There is no excuse to not have some MySQL Monitoring on your production system. At the worse case, you should be logging important MySQL information for later analysis. I use my own Logging and Analyzing scripts on every client for an immediate assessment regardless of what’s available. I combine that with my modified statpack to give me immediate text based analysis, broken down by hour chunks for quick reference. These help me in troubleshooting, but they are not a complete solution.

The most popular options I see and are also reflected in the results are:

• Cacti with MySQL Cacti Templates – Open Source
• Nagios used in conjunction with Cacti for alert monitoring – Open Source
• MONyog – Commercial with Free trial

There is a good list, including some products I did not know. My goal is to get this information included in the Monitoring-MySQL information site.

I have some additional information on Cacti and MONyog, and I’ll be sharing this information in upcoming posts.

While going about my day, I stumbled upon John Allspaw’s tweet about his experience with MySQL 5.0.51 versus Percona’s 5.0.83 highperf Percona build. For those that don’t remember John from the MySQL Conference & Expo’s, he’s the guy managing operations at Flickr, and he recently even wrote a book about webops.

Click on the photo above, to see when the upgrade happened. Amazed?

Haven’t tested this out myself, though I’m curious to see how this stacks up against MySQL 5.4, which is also aimed at being a high performance release. In another note, it seems like the mysql-cacti-templates could use a bit more verbose documentation.

If you are unfamiliar with what to monitor in MySQL, starting with looking at what popular Monitoring products monitor. For example, the following is the list of MySQL Cacti Plugin measurements.

Innodb Buffer Pool Activity

• Pages Created
• Pages Written
• Pages Read

Innodb Buffer Pool Pages

• Pool Size
• Database Pages
• Free Pages
• Modified Pages

Inoodb File I/O

• File Reads
• Files Writes
• Log Writes
• File Fsyncs

Innodb Pending I/O

• Aio Log Ios
• Aio Sync ios
• Buffer Pool Flushes
• Chkp Writes
• Ibuf Aio Reads
• Log Flushes
• Log Writes
• Normal Aio Reads
• Normal Aio Writes

Innodb Insert Buffer

• Inserts
• Merged
• Merges

Innodb Log

• Log Buffer Size
• Log Bytes Written
• Log Bytes Flushed
• Unflushed Log

Innodb Row Operations

• Rows Read
• Rows Deleted
• Rows Updated
• Rows Inserted

Innodb Semaphores

• Spin Rounds
• Spin Waits
• OS Waits

Innodb Transactions

• Innodb Transactions
• Current Transactions
• History List
• Read Views

MySQL Binary/Relay Logs

• Binlog Cache use
• Binlog Cache Disk Use
• Binary Log Space
• Relay Log Space

MySQL Command Counters

• Questions
• SELECT
• DELETE
• INSERT
• UPDATE
• REPLACE
• LOAD
• DELETE MULTI
• INSERT SELECT
• UPDATE MULTI
• REPLACE SELECT

MySQL Connections

• Max Connections
• Max Used Connections
• Aborted Clients
• Aborted Connects
• Threads Connected
• Connections

MySQL Files and Tables

• Table Cache
• Open Tables
• Open Files
• Opened Tables

MySQL Network Traffic

• Bytes Received
• Bytes Sent

MySQL Processlist

• State Closing Tables
• State Copying to Tmp Table
• State End
• State Freeing Items
• State Init
• State Locked
• State Login
• State Preparing
• State Reading From Net
• State Sending Data
• State Sorting Result
• State Statistics
• State Updating
• State Writing to Net
• State None
• State Other

MySQL Query Cache

• Queries In Cache
• Hits
• Inserts
• Not Cached
• Lowmem Prunes

MySQL Query Cache Memory

• Query Cache Size
• Free Memory
• Total Blocks
• Free Blocks

MySQL Replication

• Slave Running
• Slave Stopped
• Slave Lag
• Slave Open Temp Tables
• Slave Retried Transactions

MySQL Select Types

• Select Full Join
• Select Full Range Join
• Select Range
• Select Range Check
• Select Scan

MySQL Sorts

• Sort Rows
• Sort Range
• Sort Merge Passes
• Sort Scan

MySQL Table Locks

• Table Locks Immediate
• Table Locks Waited
• Slow Queries

MySQL Temporary Objects

• Created Tmp Tables
• Created Tmp Disk Tables
• Created Tmp Files

MySQL Threads

• Thread Cache Size
• Threads Created