« Cron Job | Main | Redirect Output to Email »
November 04, 2004
Optimizing MySQL
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
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[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)
Category : MySQL
Posted by FreeBSD Newbie at November 4, 2004 09:24 AM