Some Tools Needed
- vi (or some other inferior text editor) - This will be used to change configuration files.
- top - This monitors your server in realtime. It will help you see what effects your changes have on your server.
- mytop - This nifty little app is like the UNIX top command, but it shows MySQL server stats in realtime. This is handy to see how often you're being hit per second, how many slow queries you have, etc.
In order to improve your performance, you must gather information about your MySQL server. The following will give you important information that will help you see where you can get a little performance boost. First, SSH to your server and log into the MySQL client.
MYSQL> show variables;
| Variable_name | Value |
| back_log | 50 |
... etc. etc. ...
| wait_timeout | 28800 |
Some of the important variables for optimizing are; key_buffer, max_allowed_packet, table_cache, sort_buffer_size, read_buffer_size, myisam_sort_buffer_size, thread_cache, and
Also useful is the show status command. This will tell you things like how many of each SQL command has been run, how long the server has been up, how your indexes are being used, and other useful information. If you find you don't like scrolling back in your terminal to see all the data, you can also append a like 'Variable_name'.
Both of the previous commands can be run from your shell of choice with the mysqladmin extended-status and mysqladmin variables.
Another way to see what's going on with your MySQL server is to use mytop, which allows you to see information about your database server's performance in realtime. You may have to install this on your system, but it's definitely worth it if you're trying to get the best performance out of your MySQL server.
An example of the output:
prompt> mytop -u user -p password DB
MySQL on localhost (4.0.17-log) up 0+01:19:39 [02:16:43]
Queries: 3.4k qps: 1 Slow: 1.0 Se/In/Up/De(%): 47/00/02/00
qps now: 0 Slow qps: 0.0 Threads: 1 ( 1/ 0) 00/00/00/00
Key Efficiency: 99.4% Bps in/out: 34.3/678.2
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
239 root localhost DB 0 Query show full processlist
Play around and get to know your server. If you're unsure of something, MySQL has a very indepth manual available online that will explain what all of the variables do.
How-to Tweak MySQL Variables:
There are three ways to set the variables in MySQL 4.0.17. First, you can add them to your my.cnf file and restart MySQL. Second, you can shutdown the MySQL server and restart it with command line options. New in the 4.x series of MySQL is the ability to use SET to change variables while the database server is still running.
If you look in one of the mysql directories that were made when MySQL was compiled (mine are in /usr/local/share/mysql/) you will find four sample configuration files names my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf. Now, before you go off firing up MySQL with my-huge.cnf, thinking bigger is always better, please note that if your hardware is too limited, starting MySQL with this conf file can bring your website to its knees very quickly.
Linux Magazine has a really nice breakdown of the four cnf files that are distributed with MySQL 4.x.
- my-huge.cnf: For systems with 1 GB or more RAM where MySQL is the primary service running on the machine.
- my-large.cnf: For any systems with 512 MB of RAM where MySQL is the primary service running on the machine.
- my-medium.cnf: For systems with less memory (32 MB 128 MB) where MySQL is sharing resources with other services.
- my-small.cnf: For systems with less than 64 MB of memory where MySQL is sharing resources with other services and will not be utilized much.
Once you find one that broadly matches your circumstances, copy it to /etc/my.cnf. (Again note, this may be different on your particular system.)
COMMAND LINE OPTIONS:
If you already have a my.cnf file picked out, you can further tweak settigs by shutting down the server and restarting with command line options. Note, though, that once you get a working setup, you'll want to put it into a my.cnf file so that the variables are set if the machine is ever rebooted.
An example would be:
prompt> mysqld_safe --skip-bdb --log-slow-queries &
This would start the MySQL server with no cache for BDB tables (just make sure you're not using this table type ;) and turns the log on that will write slow queries (ones that take longer than the value of long_query_time.)
New to MySQL 4.x is the ability to change variables while the MySQL server is still running. To do this, you use the SET command. The following will set the long_query_time to 10.
MYSQL> set long_query_time=10;
Explain is your friend. Doesn't make sense to you? Let me explain. Running the following will help you find out if queries are using the table's index or not and whether the query might need some tweaking:
MYSQL> EXPLAIN SELECT * FROM table WHERE non_indexed_field = 'value';
table type possible_keys key key_len ref rows Extra
table ALL NULL NULL NULL NULL 4829 Using where
This is not a very good query. One of the first things to look at is the 'type' column. The ALL in this case shows us that MySQL is looking in all the rows for data. The other possible values for type (in order from best to worst) are - system, const, eq_ref, ref, range, and index.
Possible_keys shows what indexes could be used, with key telling us which ones, if any. Rows is the value of the number of rows MySQL must use to get the data. The lower this number is, the better.
Let's see what happens when we make a change to our SQL statement:
MYSQL> EXPLAIN SELECT * FROM table WHERE indexed_field = 'value';
table type possible_keys key key_len ref rows Extra
table const PRIMARY PRIMARY 3 const 1
Note that the above was a best case scenario. The trick, though, is to check all your SELECT statements with explain to ensure that it's using keys correctly and is as optimized as it can be.
Granted, that was a simple SQL query. What about JOINS? Taking an example from the MySQL manual:
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
From the MySQL manual:
The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimizer (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check.
In the above example, MySQL must do a full scan of b because the LEFT JOIN causes that table to be read before d. A simple change will help speed things up:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key
Another thing to check is that you're using the correct field type in your tables. To check this, use the analyze procedure on your tables. The following example shows how to form the SQL statement to get a recommendation on what field types you should use for your table:
SELECT * FROM table_name PROCEDURE ANALYSE()
This will give you information on all your fields, including the Optimal_fieldtype. Run ANALYSE on your tables and make any suggested changes.
Finally, indexes are also a way you can increase performance on MySQL applications. Be warned, though, that too many indexes (especially on the wrong fields) can hurt performance. If you have an index that isn't used, you can suffer a performance hit because the index has to be updated every time the table is updated.
Again, using EXPLAIN on your SQL SELECT statements will help you determine which fields need to be indexed. In general, though, indexes are used if the fields are in your WHERE clause.
- Use OPTIMIZE tablename on tables that are updated a lot.
- If possible, don't use TEXT, BLOB, or VARCHAR as field types because they're variable length fields. If you have to use even one of these field types, though, you lose any benefit you would have gained by by not using any.
- If you are using VARCHAR or TEXT as field types, run OPTIMIZE tablename occasionally.
- After trying the things above, other optimizations include (in order of importance: RAM, the Disks, and finally CPU power.)
As the title indicates, this is a very rough primer to get you started on optimizing your MySQL server and the applications that use it. This, along with Google groups, should give you a good start on making sure your site is as zippy as it can be.