FreeBSD Blog

Optimizing MySQL

November 4, 2004 Posted by KP

A ServInt forum member posted the following tutorials for MySQL optimization:
MySQL’s Query Cache
Optimizing MySQL: Queries and Indexes
Optimizing MySQL: Hardware and the Mysqld Variables

The first one is directly related to administration, I followed the article and inserted two lines into the file “/etc/my.cnf”.

query-cache-type = 1
query-cache-size = 20M

But this doesn’t work, this can be checked with the following MySQL command.
mysql> SHOW STATUS LIKE ‘%qcache%’;

I hate it when you followed an article carefully but it didn’t work. Tanfwc on ServInt forum shared his my.cnf, it works great after I merged it into mine. If you use this file, make sure your my.cnf doesn’t have duplicated entries and insert the code into the right sections.

[mysqld]
max_connections = 500
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Here is the new output with query cache enabled:
mysql> SHOW STATUS LIKE ‘%qcache%’;

+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_queries_in_cache | 671 |
| Qcache_inserts | 17227 |
| Qcache_hits | 46232 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 152 |
| Qcache_free_memory | 31787024 |
| Qcache_free_blocks | 238 |
| Qcache_total_blocks | 1687 |
+————————-+———-+
8 rows in set (0.01 sec)

• MySQL Optimization
• MySQL Backup and my.cnf
• MySQL Log File
• Monitor MySQL Log File
• Using Perl and MySQL
• MySQL Start Script
• Create MySQL User