Optimize MySQL Performance

Author: Collin VanDyck

The default install of MySQL is not optimized for InnoDB tables; all Cascade Server tables are of type InnoDB so that they will support transactions.

You can quickly check to see what level of caching the database is currently performing.

mysql> show status like '% qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33545608 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 353983 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.00 sec) mysql>

After tweaking and optimizing your configuration parameters, you should see something more like this:

mysql> show status like '%qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 14345816 | | Qcache_hits | 6497 | | Qcache_inserts | 7546 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1142 | | Qcache_queries_in_cache | 7545 | | Qcache_total_blocks | 15149 | +-------------------------+----------+ 8 rows in set (0.00 sec)

You can see here that the query cache itself is now being used. We recommend incorporating the following variables into your [mysqld] settings:

[mysqld] #Don't cache results that are bigger than this. query_cache_limit=1M #minimal size of unit in wich space for results is allocated (last unit will be trimed after writing all result data #Note that this variable is for MySQL 4.1 or later only query_cache_min_res_unit=1024 #The memory allocated to store results from old queries. query_cache_size=32M #Query cache type to use. query_cache_type=1 #The size of the buffer used for index blocks. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common. key_buffer_size=32M #Each thread that needs to do a sort allocates a buffer of this size. sort_buffer_size=2M #The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. innodb_buffer_pool_size=128M #Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will need to allocate here. If InnoDB runs out of memory in this pool, it will start to allocate memory from the operating system, and write warning messages to the MySQL error log. innodb_additional_mem_pool_size=8M

Once you've made the changes, please restart your MySQL server and the CMS server.

Last modified on Tue, 19 Dec 2006 15:51:17 -0500

Top / Next / Up / Table of Contents

MySQL 4.1

NEWS & TIPS

Optimize MySQL Performance

Topic Feedback Form

Content Rating:
Email:
Feedback: