Racker Hacker

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!

I rolled out a new MySQLTuner update tonight and made the following changes:

• Added aborted connection checks
• Added % reads/writes counts
• Adjusted recommendations for slow query logging, max seeks per key, and joins without indexes
• Added licensing data
• Added props for Matthew Montgomery

Coming up soon is InnoDB support and additional per-thread buffer checks.

I’ve been flooded with requests for MySQLTuner and I’ve answered them this weekend. Here’s the changes that were made:

• Specific variable recommendations are made with suggested values as well
• Odd recommendations have been reduced
• Some math errors were corrected
• More configuration items are supported, like table locks, thread caching, table caching and open file limits.

To find out more and to download the script, head on over to mysqltuner.com.

If you’re like me, you’re frustrated with DNSStuff’s actions lately. You only get 4 lookups without making a login, and even when you make a login, you have to log back in each time you access the site. Even a dim-witted web developer would know how to use cookies to automate this process and make things easier on the users.

Also, why should you pay for a web front-end for tools that people have on their servers already?

Check out Boxcheck.com and let me know what you think. I became so fed up, I decided to make my own site that is faster and guaranteed free forever. Period.

If there’s one question I get a lot, it would be “Hey, how can I speed up MySQL?” There’s absolutely no end-all, be-all answer to this question. Instead a combination of many factors contribute to the overall performance of any SQL server. However, here’s a list of my recommendations for great MySQL performance. They’re arranged from the biggest gains to smallest gains:

Query Optimization
I know - you were hoping I’d talk about hardware to start this thing off, but optimizing queries is the #1 way to get a MySQL server in gear. MySQL gives you great tools, like the slow query log, multiple status variables, and the EXPLAIN statement. Put these three things together and your queries will be on their way to a more optimized state. I’ll go into great detail about query optimization in a later post.

Memory / System Architecture
We all know MySQL likes RAM, and the more you give it (to a point) the better the performance will be. If you consider the alternative to memory, which is swapping on disk, it’s obvious to see the gains.

So why did I add system architecture to this section? Well, if you have 32-bit Redhat, you can only allocate 2GB per process with the standard kernel. If you jump up to the SMP or hugemem kernel (in ES 2.1, you need the hugemem kernel for this to work), you can allocate 3GB per process. There is a caveat - MySQL can only use 2GB per buffer in 32-bit land. In a 64-bit OS with an appropriate Redhat kernel, you can allocate much larger buffers, and this can be tremendously helpful to tables which use the InnoDB engine. The memory allocation abilities are a great benefit, but also keep in mind that you will also get a boost in math performance within MySQL due to the 64-bit architecture. It’s a win-win!

Disk Performance
Running a critical database on IDE or SATA drives just doesn’t cut it any more. A SCSI or SAS drive is required for the best performance. Although you hope that MySQL doesn’t touch the disk much, it’s important to remember that you need to make backups often, and you may need to restore data. Also, if your site is write-intensive, the disk performance is much more important than you think. It will reduce the time that tables are locked, and it will also reduce the time for backups and restores.

CPU
Although CPU comes last, don’t forget how important it can be. If you run a high number of complex queries and perform a lot of mathematical operations, you’re going to need a CPU that can handle this load. Dual CPU’s or dual core CPU’s will help out even more, since MySQL can use multiple CPU cores to perform simultaneous operations. Keep in mind that 64-bit will outperform 32-bit in MySQL, and also allow for greater memory allocations (look in the Memory section above).

Final Note:
Keep in mind that these are general suggestions, and these suggestions may not apply to all users. For example, on sites that are heavily read-intensive, you may find that CPU speed is more important than disk speed. Also, if you’re not using all of the available memory on your server, but your performance is still sagging, adding more memory won’t help. Consult with a DBA and find out where your server’s slowdowns are, then make a change with your queries or with your hardware. Remember, throwing more hardware at the problem will not always solve it.

On some servers, you may notice that MySQL is consuming CPU and memory resources when it’s not processing any queries. During these times, running a mysqladmin processlist will show many processes in the ’sleep’ state for many minutes.

These issues occur because of code that uses a persistent connection to the database. In PHP, this is done with mysql_pconnect. This causes PHP to connect to the database, execute queries, remove the authentication for the connection, and then leave the connection open. Any per-thread buffers will be kept in memory until the thread dies (which is 28,800 seconds in MySQL by default). There’s three ways to handle this type of issue:

Fix the code
This is the #1 most effective way to correct the problem. Persistent connections are rarely needed. The only time when they would be even mildly useful is if your MySQL server has a huge latency. For example, if your web server takes > 250ms to make contact with your MySQL server, this setting might save you fractions of a second. Then again, if your web server and MySQL server are so far apart to where latency is even a consideration, you have more problems than I can help you with.

Restrict the connections
If push comes to shove, and you have users on a server who are abusing their MySQL privileges with mysql_pconnect, then you can pull the plug on their shenanigans with GRANT. You can reduce the maximum simultaneous connections for their database user, and they’ll find themselves wanting to make code changes pretty quickly. MySQL doesn’t queue extra connections for users who have passed their maximum, so they get a really nice error stating that they have exceeded their max connections. To set up this grant, just do something like the following:

GRANT ALL PRIVILEGES ON database.* TO 'someuser'@'localhost' WITH MAX_USER_CONNECTIONS = 20;

Reduce the timeouts
If changing the code isn’t an option, and you don’t feel mean enough to restrict your users (however, if they were causing a denial of service on my MySQL server, I’d have no problem restricting them), you can reduce the wait_timeout and interactive_timeout variables. The wait_timeout affects non-interactive connections (like TCP/IP and Unix socket) and interactive_timeout affects interactive connections (if you don’t know what these are, you’re not alone). The defaults of these are fairly high (usually 480 minutes) and you can drop them to something more reasonable, like 30-60 seconds. Web visitors shouldn’t notice the difference - it will just cause the next page load to start a new connection to the database server.

If you need to strip query strings from a URL with mod_rewrite, you can use a rewrite syntax such as the following:

RewriteEngine on
RewriteCond %{QUERY_STRING} "action=register" [NC]
RewriteRule ^/. http://www.domain.com/registerpage.html? [R,L]

To install PayFlowPro, you will need a few things:

• The PHP source code for version of PHP installed (go here)
• The SDK from Verisign/PayPal (this comes from the portal, login required)
• The gcc and automake packages

Take the Verisign SDK and copy the following:

• Copy pfpro.h to /usr/include
• Copy the .so file to /usr/lib

Untar the PHP source code and cd into php-[version]/ext/pfpro. Run phpize and make sure it finishes successfully. Now run:

./configure --prefix=/usr --enable-shared

Then run make and make install. Now, go to the php.ini and add:

extension=pfpro.so

Run php -i | grep pfpro to make sure the module was successfully built. Restart Apache and you’re all set!

The pfpro module is now available via pecl in PHP 5.1+. Thanks to Chris R. for pointing that out.

If you can’t use PHP to force HTTPS, you can use mod_rewrite instead. Toss this in an .htaccess file in the web root of your site:

RewriteEngine On
RewriteCond %{SERVER_PORT} 80
RewriteRule ^(.*)$ https://www.domain.com/$1 [R,L]

Or, if it needs to be forced only for a certain folder:

RewriteEngine On
RewriteCond %{SERVER_PORT} 80
RewriteCond %{REQUEST_URI} somefolder
RewriteRule ^(.*)$ https://www.domain.com/somefolder/$1 [R,L]