FREE THOUGHT · FREE SOFTWARE · FREE WORLD

Home  »  MySQL  »  MySQL Performance Tuning Scripts and Know-How

by 6 comments

Unless you are a MySQL performance tuning expert, or a total linux-freak-guru like myself, and even if you are, it can be enormously challenging and somewhat overwhelming to locate and eliminate MySQL bottlenecks. While many DBAs focus on improving the performance of the queries themselves, this post will focus on the highest-impact items: MySQL Server Performance and OS Performance for MySQL.

This post is a "best-of" compilation of the tricks and scripts I have found to be the most effective over the past decade. I'd like to write a 50 page article but am limiting this to 1 page.

For anyone serious about High Performance MySQL, I would highly highly recommend the fantastic book: "High Performance MySQL: Optimization, Backups, Replication, and more" - O'Reilly. I have spent many hours poring over it's wisdom-filled pages and gaining much practical know-how.


MySQL Server Software

Each new MySQL server release contains ENORMOUS performance enhancements over previous versions. That is the absolute very first thing you should do: Upgrade your MySQL Server and Client libraries and keep them updated.

There are several "flavors" of MySQL believe it or not.. Most people use the stock MySQL Server. I, along with WikiPedia, Arch-Linux, and others, use MariaDB. MariaDB is a greatly enhanced 100% compatible replacement for the stock MySQL Server. It is based on the excellent work by the Percona project. The percona flavor of MySQL is the other truly improved version of MySQL to consider. 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. MariaDB is the bomb-diggity.

MySQL Engine

InnoDB not MyISAM. InnoDB may be surpassed by in-development engines like TokuDB. I ONLY use InnoDB, for everything.

Types of MySQL Servers to optimize

Seriously? Optimize EVERYTHING! The screenshots below are actual from one of my live servers. That server used to be 8GB RAM, but now as you may see in the screenshots, it is now only 2GB of RAM. I was able to save some serious $$$ by optimizing my server, without sacrificing speed... In fact I gained some speed in many instances.

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).

MySQL Performance Tuning Scripts

The first thing to understand and believe is that there is absolutely no substitute for having a professional tune your DB. I personally use 2 professionals to tune clients DBs... I optimize it first, then I optimize it again after both pros are finished.

  1. A DBA who knows MySQL optimization inside and out, percona/mariadb experience = "the best".
  2. 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.

If you are just learning or doing it yourself, props to you! In that case, you should utilize ALL 4 of these tools. 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. I especially like the Tuning-Primer shell script, and the phpMyAdmin Advisor (which is fairly new to phpMyAdmin - using 4.1-DEV-BETA).

The biggest areas to focus in on (IMHO) are:

  1. MEMORY/RAM, specifically the buffers
  2. SWAP
  3. ACID - Do you need full ACID, or can you (likely) make some sacrifices there for speed
  4. tmp tables, tmpdir (use a tmpfs mounted dir)
  5. Thread/Connections - How many processes and threads should be running
  6. open_files / table_cache - May need to boost your /etc/security/limits.conf and your /etc/sysctl.conf

Tuning-Primer

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. It is compatible with all versions of MySQL 3.23 and above.

MySQL Performance Tuning Script: Tuning-Primer

phpMyAdmin Advisor

This tool is very similar to the tuning-primer tool. Nice and fast, and likely the most up-to-date tool.

MySQL Performance Tuning Script: phpmyadmin-advisor

MySQLTuner

MySQLTuner: a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQL Performance Tuning Script: mysqltuner

mysqlreport

mysqlreport: makes a friendly report of important MySQL status values. 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 is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.

MySQL Performance Tuning Script: mysqlreport

Percona Tools for MySQL

Free online productivity tools for MySQL DBAs, SysAdmins and Developers

MySQL Performance Tuning Scripts and Know-How

Percona Configuration Wizard for MySQL

Apply 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. Simply copy and paste the results of the Percona Configuration Wizard for MySQL into your my.cnf file.

Tens of thousands of MySQL users have already used this tool to improve their MySQL performance

Monitoring MySQL

The mysqladmin command is great and all, but these 3 tools are much more useful for the specialized task of monitoring MySQL. The most powerful is innotop, then mytop, and finally the phpMyAdmin Monitor is great for general big-picture monitoring. Also, make sure you understand and use slow query logging and mysqldumpslow as well.

mytop

mytop - a top clone for MySQL.

MySQL Performance Tuning Scripts and Know-How

innotop

innotop

MySQL Performance Tuning Scripts and Know-How

phpMyAdmin Monitor

MySQL Performance Tuning Scripts and Know-How

More Resources

Tags

July 11th, 2013

Comments Welcome

My Online Tools
WordPress Sites

My Picks

Related Articles
Newest Posts
Twitter

  • @askapache · Apr 22
    Within our small sphere of the universe we can look at our past, but only our future is within our control
  • @askapache · Apr 16
    RT @commandlinefu:  t.co/htRbYF8SFf  - Print ASCII Character Chart (AskApache) #echo #column #ascii #for #octal #askapache #chart
  • @askapache · Apr 11
    Angel headed hipsters burning for the ancient heavenly connection to the starry dynamo in the machinery of the night - Ginsberg
  • @askapache · Apr 11
    If you haven't experienced a hacker war on a free shell network like SDF, you haven't lived!
  • @askapache · Apr 10
    @kovshenin @wpekadotcom @CloudFlare Maybe it's an April fools joke that finally propagated
  • @askapache · Apr 10
    @kovshenin @wpekadotcom @CloudFlare wait this is a joke right?
  • ZERO DAY - read before Trojan horse  t.co/pPMLGDJv8P 
  • Trojan Horse, a novel!  t.co/Hf8EtYaZVa 
  • The Hacker Playbook - very nice high level overview of attacks  t.co/lHwNVWi61u 
  • Clean Code - A Handbook of Agile Software Craftsmanship  t.co/hnJX0x1qIc 
  • Secrets of the JavaScript Ninja - By my absolute favorite JS hacker John Resig!  t.co/tZ42ljmcCl 
  • Hacking Exposed 7: Network Security Secrets & SolutionsMy all time favorite, basic but thorough and accurate.  t.co/jycW0RDVtZ 
  • Empty words will be no surrogate for cold resolve. Pain is nothing.  t.co/qXjpRxbjCw 
  • REVERSING: Secrets of Reverse Engineering  t.co/GaWo29lWWG 
  • NEUROMANCER  t.co/3OoknUcb5Z 
  • "The Shockwave Rider", by John Brunner (1975 hacker sci-fi)  t.co/ZW56HVUefW 
  • The Rootkit ARSENAL - Escape and Evasion in the Dark Corners of the System  t.co/1FzX6bHgsQ 
  • "We Are Anonymous - Inside the Hacker World of LulzSec, Anonymous, and the Global Cyber Insurgency" better be good!  t.co/GL0cFNiUOq 
  • THE IDEA FACTORY Bell Labs  t.co/FyVhgNwwT5 
  • The Datacenter as a Computer -- Urs Holzle  t.co/M5WIYs1OVg 

Friends and Recommends
Hacking and Hackers

The use of "hacker" to mean "security breaker" is a confusion on the part of the mass media. We hackers refuse to recognize that meaning, and continue using the word to mean someone who loves to program, someone who enjoys playful cleverness, or the combination of the two. See my article, On Hacking.
-- Richard M. Stallman






[hide]

It's very simple - you read the protocol and write the code. -Bill Joy

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution 3.0 License, just credit with a link.
This site is not supported or endorsed by The Apache Software Foundation (ASF). All software and documentation produced by The ASF is licensed. "Apache" is a trademark of The ASF. NCSA HTTPd.
UNIX ® is a registered Trademark of The Open Group. POSIX ® is a registered Trademark of The IEEE.

| Google+ | askapache

Site Map | Contact Webmaster | License and Disclaimer | Terms of Service

↑ TOPMain