Development

MysqlOptimization

You must first sign up to be able to contribute.

Version 2 (modified by todd, 11 years ago)
Added table of contents and references section

MySQL Optimization

Introduction

The propel engine used for the database models is symfony is not only powerful, but can also put a load on the databases behind them. The following tips let you utilize the optimizations already built in to MySQL. The default configuration has very few optimizations to have as much compatibility as possible--the default "out of the box" configuration of MySQL is NOT meant for a production environment. This is mainly intended for MySQL 5.0 but most options should be available in older version. All actions will be performed from the mysql command line. You should be logged in as root and you will need root access to modify some config files. I'm also writing this up from a linux/mac perspective as I've never actually ran MySQL on windows. I'd consider this an intermediate level article. Which is why I'm not giving details on how to send SQL command to a MySQL server.

Gathering runtime information

You can get information about a running server using two commands. The first is SHOW STATUS;. This gives you information about the running server. For example the Com_* lines count how many times each command was run. Connections will show how many connections there have been. Most of them kinda make sense and if you don't understand one, and it's not covered here, just search for it on line and you'll find a ton of information.

The second command is SHOW VARIABLES;. This lists all the variables that are set for the server. You can set a lot of these variables at run time for testing by running SET GLOBAL variable_name=value. So if you wanted to increase the table cache to 128, you can do SET GLOBAL thread_cache=128; If nothing breaks you can add it to your /etc/my.cnf file

/etc/my.cnf

To get things started you should have a /etc/my.cnf. Most of the time this file doesn't exist on new installs, so you will need to create it. Fortunately MySQL comes with several different templates to get you started. The location of these vary, but they almost always have the same name. A couple names would be my-small.cnf, my-medium.cnf, and my-large.cnf. Review the headers in all the my-whatever.cnf files and see which meets your needs. Once you've found one, copy it as /etc/my.cnf and restart MySQL.

Preface

For each section I'm going to explain what status and variable items to look for and how to optimize. It will be rather short and to the point. To adjust any of these settings at runtime use the SET GLOBAL command mentioned above.

Key Buffer Size

One thing MySQL can do is buffer primary keys. This makes access records using primary keys (which most should do) run much faster. Run SHOW STATUS LIKE 'key_read%';.

key_read_requests
This is the total number or requests making use of an index
key_reads
This is the total number of requests that had to be read to disk
key_read_requests / key_reads
When you divide the two, the ratio should be higher than 100 to 1. The higher the better.

To adjust this, increase the key_buffer_size. Typical rule of thumb is take your system memory and multiply by .25. Some example values are 768M on a system with 3gb and 16M on a system with 64M.

Query Cache

This and thread cache should be the two things you take with you if you ignore everything else. Below is pretty much an abbreviated form of http://mirrors.ee.teiath.gr/mysql/tech-resources/articles/mysql-query-cache.html.

You get the first two query_cache_* variables via SHOW VARIABLES LIKE 'query%';. The others you can check with SHOW STATUS LIKE 'qc%';.

query_cache_type
Should be set to ON to enable it
query_cache_size
This is the size of the query cache. if set to 0, then no space is allocated for it, effectively disabling it even if it's on.
qcache_free_blocks
This is the number of free blocks of memory in the cache. A high number is a sign of fragmentation and can be fixed using FLUSH QUERY CACHE;, which will defrag the memory in the cache without removing any of the cached queries.
qcache_free_memory
A low value here compared to the total amount allocated (query_cache_size variable) can indicate a low amount of memory. This can be solved by upping the query_cache_size variable. qcache_hits and qcache_inserts This is how many times a query was serviced from the cache and how many queries have been inserted. query_cache_limit may be too low, since it givers the amount of RAM devoted to each individual query cache entry. Larger query result sets will require a larger number.
qcache_lowmem_prunes
If this value keeps increasing, then mysql had to remove queries to make room for new ones. Other reasons include an undersized cache (query_cache_size) and memory fragmentation (FLUSH QUERY CACHE; to just defragment the memory or RESET QUERY CACHE; to remove all queries)
qcache_not_cached
This is the number of queries not cached since they were non-select statements or banned using the SQL_NO_CACHE hint.

You may want to run FLUSH QUERY CACHE; periodically. It may only be needed to be done after mysql has been running a couple days, but after the initial amount of inserts it may be more optimized.

Thread Cache

First calculate your cache hit rate using the following formula:

100 - ((Threads_created / Connections) * 100)

Should be as close to 100% as possible. You will need to adjust the thread_cache_size variable. To adjust it, increase thread_cache_size until threads_created stops going up. Just making thread_cache_size 2 or 3 can make a big difference.

Table Cache

The two important variables here are open_tables and opened_tables. These should be as close as possible to each other. If 'open_tables' is maxed out (it matches what your table_cache variable is set to) then you should try to increase it. The limit here is how many open files the underlaying OS can handle. Remember that MyISAM tables contain two files. You can verify this by checking the open_files variable. If the difference between the two variables is really high then you should definitely look into adjusting the system file i/o limits if needed to allow more tables to be open. Just search around on google to find out how to do it. Unless you get into the 1000's of opened tables it shouldn't be an issue and the default limits should be fine.

Also keep in mind that the more tables you have open the greater chance of "bad things" to happen in the event of an unexpected power failure where it doesn't close the tables properly. In debian, on startup, it will check all tables for errors. So don't be alarmed if the opened tables is high if you have a lot of different databases stored on the server.

Temporary Data

Created_tmp_disk_tables is the number of tables the server had to create on disk. If this number is big, consider increasing the tmp_table_size variable so temporary tables are created in memory as opposed to on disk.

References