Performance Tuning MySQL Database Server

After you have MySQL Server up and running, some time should be set aside for tuning and performance analysis.
You may want to see Getting MySQL up and running if you haven't seen it yet.

Howto performance tune MySQL on Ubuntu or a Debian based machine.

Do these commands as an administrative user. Most of this will be done in command line with your favorite terminal - like gnome terminal or Eterm.

First: Connecting to MySQL

Open a terminal. Let's connect and see what kind of variables we're dealing with:

mysql --user=root --password=YOURMYSQLPASSWORD

You should get:

mysql >

Put in:

SHOW VARIABLES;

Returned will be all of the variables. You may want to remember this last command. In the future when you are looking at someone's server. It's a quick way to get started on troubleshooting or tuning.
Note: If you get stuck by typing in the wrong thing and have just "->" or for some other reason need to clear the buffer, type \c (W/O quotes) and hit enter.
Note2: To get out and return to the terminal, just type "exit" (W/O quotes).
You can also use mysqladmin. These commands are a quick in and out of mysql. They run the command and return to the terminal.

mysqladmin variables -u root -p

OR

mysqladmin extended-status -u root -p

A quick and dirty/simple report using mysqladmin:

mysqladmin extended-status -u root -p > /home/YOURDIRECTORY/mysqlxs.txt

Go to where you put it and have a look. (or just to make sure it's there: cat /home/YOURDIRECTORY/mysqlxs.txt)

Just like anything else, the more memory you throw at something, the better.

MySQL is very efficient and scalable and can actually run on very little memory. That said, it may need more memory allocated as tables start filling up and/or concurrant connection rates rise.
When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_cache. (More detail later on these.) You should first feel confident that you have these set appropriately before trying to change any other variables.
These examples show some typical variable values for different configurations:

• If you have at least 256MB of memory, many tables and want maximum performance with a smaller number of clients, you should use something like:
  terminal> mysqld_safe --key_buffer_size=64M --table_cache=256 \ --sort_buffer_size=4M --read_buffer_size=1M &
• If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:
  terminal> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
• If there are a high number of concurrant connections, swapping problems could occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for every concurrant connection.
• With little memory and lots of connections, use something like:
  terminal> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \ --read_buffer_size=100K & Or maybe:
  terminal> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \ --table_cache=32 --read_buffer_size=8K \ --net_buffer_length=1K &
• If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, you should increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations.
• If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.
• To see the effects of a parameter change, do something like:
  terminal> mysqld --key_buffer_size=32M --verbose --help The variable values are listed near the end of the output. Make sure that the --verbose and --help options are last. Otherwise, the effect of any options listed after them on the command line will not be reflected in the output.

More Coming Soon:

Have Fun!

Time since last reboot: 16 days, 11 hours, 42 minutes , 10 seconds