Racker Hacker

Thanks to an e-mail from Joe Calderon, I’ve corrected a MySQLTuner bug where indexes were not being calculated properly when symbolic links are used. It was a quick fix, and the new version is now available for download.

Also, MySQLTuner now has a standardized versioning scheme. I’m starting at 0.8.0 since I’m close to a completely stable release. There’s still a few kinks when working with MySQL cluster nodes, and I’m hoping to work that out soon.

Thanks for downloading!

I’ve just reviewed the stats for MySQLTuner, and it crossed the 5,000 download mark last week! Thanks to all of you who have helped me make it a successful tool for MySQL server optimization.

With that said - how can MySQLTuner be improved to help you? Please let me know, and I’ll get started on those features!

A new version of MySQLTuner was released tonight to correct some bugs found within revision 26. As usual, you can get a new copy from the MySQLTuner site.

Here are the new features:

» Storage engine counts are shown

In addition to the actual size of the tables stored under each storage engine, there is a table count as well. The table count reflects the number of tables that exist on the server which use the specified storage engine.

» Minor changes

• Reduced overall code size
• Optimized subroutines to use fewer system calls and math computations
• Added storage engine disabling recommendations to the bottom of the output

» Bugs fixed

• Fixed an issue that caused incorrect statistics for storage engines with MySQL 3.23
• Corrected a logic bug that displayed odd storage engine statistics calculations

As some subversion users may have noticed, revision 23 of MySQLTuner was released quietly on Sunday. Thanks to Mike Jackson, a few bugs from revision 23 were smashed today and revision 26 was released this morning.

To pick up the new script, visit the MySQLTuner site.

Here’s some of the new features:

» Shows banner of enabled and disabled storage engines

Near the top of the MySQLTuner output, you’ll find a line like this:

[**] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

Of course, this is shown in color within the terminal, but all enabled storage engines are shown in green with a plus sign at the front. Disabled storage engines are shown in red and are prepended with a minus sign.

» Recommends disabling unused storage engines

If MySQLTuner finds storage engines enabled that are not in use, it will recommend that they are disabled to save resources:

[!!] InnoDB is enabled but isn’t being used

» Calculates total data corresponding to storage engines

Any storage engines in use are shown with the total amount of data stored using those engines:

[**] Data in MyISAM tables: 9G
[**] Data in InnoDB tables: 189M
[**] Data in MEMORY tables: 0B

» Displays exact counts in addition to percentages

For some results where percents are shown, exact counts are being displayed as well:

[OK] Slow queries: 0% (1/4M)
[OK] Highest usage of available connections: 56% (17/30)

» Initial InnoDB support

As an initial step towards InnoDB support, the innodb_buffer_pool_size is compared to the total amount of InnoDB data stored on the server:

[OK] InnoDB data size / buffer pool: 189.7M/384.0M

» Other minor changes

• Added additional section headers to further organize the output
• Merged the total buffers lines into one for more compact output
• Added MySQL 5.1 to the supported list to prepare for upcoming GA release
• For filesize amounts less than 1024 bytes, the “B” letter is shown to represent bytes
• InnoDB log file recommendation removed due to bad implementations based on recommendations
• Switches from spaced indents to tabbed indents

I just received a note yesterday that MySQLTuner was added to Debian’s package list for the sid release. I’d like to thank Marcela Tiznado for putting the package together and submitting it!

You can read more about the package.

MySQLTuner revision 22 is available today. Here’s some of the notable fixes and changes:

» Changed how indexes are calculated on MySQL 5

Thanks to Jon Hinds, I found that when running the tuning script against MySQL 5, the following SQL statement caused MySQL to open all of the tables on the server, which of course caused the table cache hit rate to plummet each time the script is run:

SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='MyISAM'

The script now calculates index size using du operations for all MySQL versions.

» Added checks for innodb_log_file_size

I’m working in some InnoDB support, and the script now checks to see whether the innodb_log_file_size is 25% (+/- 5%) of your innodb_buffer_pool_size.

» Added checks for 32-bit and 64-bit architectures

The script now determines if you have a 32-bit system with less than 2GB of RAM. You’ll get a polite suggestion to move to a 64-bit OS so that MySQL can allocate more than 2GB of RAM safely. Also, if your maximum possible memory usage is over 2GB on a 32-bit system, you’ll get a warning about stability issues. (Allocating more than 2GB on a 32-bit system can cause thread thrashing and a system crash.)

» Fixed a bug in the recommendations for temporary tables

I had a pretty ugly math error, and it’s fixed now. You will see recommendations for increasing the size of the max_heap_table and tmp_table_size buffers as long as they are not at 256MB already.

» Fixed thread cache recommendations and warnings

If your thread cache is set to 0, you now receive a warning about a disabled thread cache. Also, a separate recommendation is made in that situation. If your thread cache is set too low, but still enabled, a separate recommendation will appear.

» Internal changes

Some of the commented lines are switched around a bit, and some of the arrays have been consolidated to speed up the script a bit more.

Ready to download the script? Go to http://mysqltuner.com/ and get it for free.

Thanks to Oden Eriksson at Mandriva, MySQLTuner is now packaged into an RPM for easier installations on Mandriva Linux.

More data can be found in the Mandriva Linux Archives.

As an aside, I find it quite interesting that the first package for my script popped up on Mandriva. My first adventure into Linux was with Mandrake many years ago.

I’ve updated MySQLTuner to revision 20 tonight, and it has the following improvements:

* Added NetBSD support (thanks to Dave Burgess)
* Switched username/password prompts to STDERR so they won’t appear in printouts

Downloads are available, or you can do a new subversion checkout.

I’ve revamped a few of the recommendations in MySQLTuner, and revision 19 is now available tonight! Here’s the main changes:

* Adjusted infoprint to use asterisks (cosmetic)
* Per-thread/global buffer counts are now displayed
* Key buffer increases are only recommended if the buffer is smaller than total indexes and hit rate is < 95%
* Dropped max_seeks_for_key checks
* Temporary table size increases are not recommended over 256M
* Aborted connection calculation and recommendation adjustments

You can download the latest copy on the MySQLTuner page, and you can get diffs for the new version as well.

I’ve been hard at work on the boxcheck.com site, and there’s plenty of improvements. The new interface is quicker, easier to use, and it uses ajax for a more Web 2.0 feel (can’t believe I just said Web 2.0).

Some beta testers are already giving it a whirl, and I hope to have it released to the public later next week!