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!

One of the questions I receive the most is: “What version of Plesk works with MySQL 5?” The minimum version of Plesk for MySQL 5 is 8.1.0. If you install MySQL 5 on a version prior to 8.1.0, you may be able to access then panel in the other 8.x versions, but your upgrades will fail miserably.

In case you’re curious about a slightly older system, full MySQL 4 support was available in Plesk 7.5.3. However, MySQL 4 is supported on some distributions as far back as 7.1:

Fedora Core 2
Mandrake 10
SuSE 9.0
FreeBSD 5.2.1

Check out SWSoft/Parallel’s site for more information about MySQL 4 and 5 support.

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

After a couple of weeks, my MySQL replication series has come to a close. Here’s links to all of the topics that I covered:

Performance
Redundancy
Backups and Data Integrity
Horizontal Data Partitioning
Delayed Slaves
Breakdown
Replication Across an External Network
Upgrading the MySQL Server
Slave Performance

If there’s any other questions that you have, please let me know and I’ll be happy to add some extra posts on your topic!

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

There’s a few final configuration options that may help the performance of your slave MySQL servers. If you’re not using certain storage engines, like InnoDB or Berkeley, then by all means, remove them from your configuration. For those two specifically, just add the following to your my.cnf on the slave server:

skip-innodb
skip-bdb

To reduce disk I/O on big MyISAM write operations, you can delay the flushing of indexes to the disk:

delay_key_write = ALL

You can also make all of your write queries take a backseat to any reads:

low-priority-updates

Keep in mind, however, that the last two options will increase slave performance, but it may cause them to lag behind the master. Depending on your application, this may not be acceptable.

If you want to make a DBA nervous, just let them know that they need to upgrade MySQL servers that are replicating in a production environment. There’s multiple ways to get the job done, but here is the safest route:

First, make sure you have dumped all of your databases properly. Verify that your backups are correct and intact, and that you have multiple copies of them.

Next, upgrade the slave servers individually to the newest version. After upgrading the first one, make sure the slave server is operating properly. If it is working properly, then you can continue to upgrade the other slaves.

Once all of the slaves have been upgraded, then you can upgrade the master. If a busy web application is sending write queries to the master, you may want to put up a temporary page that tells visitors that maintenance is being performed. Once all of the writes clear out, stop the master and upgrade it.

After the master starts up, be sure that the slaves reconnect, and you might want to perform a test write query. Verify that the write is performed on the slaves as it was done on the master.

While many people might find replicating over an external network to be an odd concept, it does have some uses. For example, if you need to replicate data for local access at certain locations, it may be helpful. Also, if you have a dedicated server, you can replicate to your home to run backups.

First off, you’re going to need security for the connection. This is easily done with SSL. On the master, simply add the following lines to the [mysqld] section and restart the master:

ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

To have the slaves use SSL connections to the master server, simply add on MASTER_SSL=1 to the CHANGE MASTER statement on the slave.

Another aspect to consider is bandwidth usage. This may be a priority if your remote areas have slow downlinks, or if you are charged for your bandwidth usage. You can compress the MySQL traffic very easily. Simply add the following to the MySQL configuration file in the [mysqld] section:

slave_compressed_protocol = 1

With both of these changes, keep in mind that there is a significant CPU overhead required to compress and/or encrypt data. Determine carefully what your application requires and test your configuration thoroughly.

On some occasions, MySQL replication can break down if an statement comes from the master that makes no sense to the slave. For example, if an UPDATE statement arrives from the master server, but the table referenced by the UPDATE no longer exists, then the slave will halt replication and throw an error when SHOW SLAVE STATUS; is run.

The obvious question here is: how can the master and the slave have different data after replication has started? After all, you make a dump file prior to starting replication, so both servers contain the same information. Stray updates can be thrown into the mix from application errors or plain user errors. These kinds of things happen, even though we all try to avoid it.

Don’t worry - this is almost always an easy fix. You have two main options:

Fix the problem yourself. If the master sent a query that the slave can’t run, fix it manually. For example, if the master wants to run an INSERT on a table that doesn’t exist, run a quick SHOW CREATE TABLE; on the master and create the table manually on the slave. When the table is there, run a START SLAVE; on the slave and you should be all set.

Skip an unnecessary query. Let’s say that the master sent over a DROP TABLE query but the table doesn’t exist on the master. It’s safe to say that the master won’t be sending any write queries to that table in the future, so the query can be skipped. To skip it, run the following statement:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

In short, you’re telling MySQL to skip that unnecessary query and keep going with the ones after that. Of course, if you need to skip multiple queries, change the 1 to whatever number you need and then run START SLAVE;.