Tens of thousands of MySQL users have already used this tool to improve their MySQL performance Monitoring MySQL Simply copy and paste the results of the Percona Configuration Wizard for MySQL into your my.cnf file. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.įree online productivity tools for MySQL DBAs, SysAdmins and DevelopersĪpply Percona best practices to achieve better MySQL database performance and avoid the time, complexity, and risk of customizing a my.cnf configuration on your own. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. Mysqlreport: makes a friendly report of important MySQL status values. MySQLTuner: a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Nice and fast, and likely the most up-to-date tool. This tool is very similar to the tuning-primer tool. It is compatible with all versions of MySQL 3.23 and above.

MySQL Tuning Primer Script - tuning-primer.sh - This script takes information from ' SHOW STATUS LIKE.' and ' SHOW VARIABLES LIKE.' then attempts to produce sane recommendations for tuning server variables.

open_files / table_cache - May need to boost your /etc/security/nf and your /etc/nf.

Thread/Connections - How many processes and threads should be running.

tmp tables, tmpdir (use a tmpfs mounted dir).ACID - Do you need full ACID, or can you (likely) make some sacrifices there for speed.The biggest areas to focus in on (IMHO) are: I especially like the Tuning-Primer shell script, and the phpMyAdmin Advisor ( which is fairly new to phpMyAdmin - using 4.1-DEV-BETA). The one thing you need to do before running any of them is make sure your MySQL server has been online for at least a week without restarting, otherwise the results will mostly all be questionable. If you are just learning or doing it yourself, props to you! In that case, you should utilize ALL 4 of these tools. A Linux system admin GURU who can make sure the subtle and not-so-subtle settings and tweaks to the OS is geared for max performance.A DBA who knows MySQL optimization inside and out, percona/mariadb experience = 'the best'.I optimize it first, then I optimize it again after both pros are finished. I personally use 2 professionals to tune clients DBs. The first thing to understand and believe is that there is absolutely no substitute for having a professional tune your DB. I've used these optimization techniques on monster servers with 32GB of ram and many slaves, and also on a machine with 1GB of ram (running arch-linux). In fact I gained some speed in many instances. I was able to save some serious $$$ by optimizing my server, without sacrificing speed. That server used to be 8GB RAM, but now as you may see in the screenshots, it is now only 2GB of RAM. Seriously? Optimize EVERYTHING! The screenshots below are actual from one of my live servers. InnoDB may be surpassed by in-development engines like TokuDB. I personally have spent a couple years using Percona, then I upgraded from Percona to MariaDB (which has a lot of Percona juju built in) and am no longer thinking about which version to go with. The percona flavor of MySQL is the other truly improved version of MySQL to consider. It is based on the excellent work by the Percona project. MariaDB is a greatly enhanced 100% compatible replacement for the stock MySQL Server. I, along with WikiPedia, Arch-Linux, and others, use MariaDB. There are several 'flavors' of MySQL believe it or not. That is the absolute very first thing you should do: Upgrade your MySQL Server and Client libraries and keep them updated. MySQL Server SoftwareĮach new MySQL server release contains ENORMOUS performance enhancements over previous versions. I have spent many hours poring over it's wisdom-filled pages and gaining much practical know-how. I'd like to write a 50 page article but am limiting this to 1 page.įor anyone serious about High Performance MySQL, I would highly highly recommend the fantastic book: ' High Performance MySQL: Optimization, Backups, Replication, and more' - O'Reilly. This post is a 'best-of' compilation of the tricks and scripts I have found to be the most effective over the past decade.

Mysql performance tuning