MysqlOptimization (diff)

You must first sign up to be able to contribute.

Changes from Version 1 of MysqlOptimization

todd (IP:
08/19/06 13:00:28 (12 years ago)

Created Mysql Optimization content


  • MysqlOptimization

    v0 v1  
     1= MySQL Optimization = 
     3== Introduction == 
     5The 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.   
     7== Gathering runtime information == 
     9You 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. 
     11The 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 
     13== /etc/my.cnf == 
     15To 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. 
     17== Preface == 
     19For 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. 
     21== key_buffer_size == 
     23One 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%';`. 
     25 key_read_requests:: 
     26   This is the total number or requests making use of an index 
     27 key_reads:: 
     28   This is the total number of requests that had to be read to disk 
     29 key_read_requests / key_reads:: 
     30   When you divide the two, the ratio should be higher than 100 to 1.  The higher the better. 
     32To 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. 
     34== Query Cache == 
     36This 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 []. 
     38You get the first two `query_cache_*` variables via `SHOW VARIABLES LIKE 'query%';`.  The others you can check with `SHOW STATUS LIKE 'qc%';`. 
     40 query_cache_type:: 
     41   Should be set to ON to enable it 
     42 query_cache_size:: 
     43   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. 
     44 qcache_free_blocks:: 
     45   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. 
     46 qcache_free_memory:: 
     47   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. 
     48 qcache_hits and qcache_inserts 
     49   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. 
     50 qcache_lowmem_prunes:: 
     51   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) 
     52 qcache_not_cached:: 
     53   This is the number of queries not cached since they were non-select statements or banned using the SQL_NO_CACHE hint. 
     55You 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. 
     57== Thread Cache == 
     59First calculate your cache hit rate using the following formula: 
     61  100 - ((Threads_created / Connections) * 100) 
     63Should 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. 
     65== Table Cache == 
     67The 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. 
     69Also 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. 
     71== Temporary Data == 
     73`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.