Category: Uncategorized

The top 20 design tips for MySQL Enterprise data architects

Scooped from Hacker News.

For those wondering how to do query caching here is a quick run down.

1) Edit the MySQL conf at the following to /etc/my.cnf for a 26M query cache use:

query_cache_type = 1
query_cache_size = 26214400

2) Restart MySQL

/etc/init.d/mysqld restart

3) Login to MySQL and run the following:

mysql> SET GLOBAL query_cache_size = 26214400;

4) Check if it was loaded:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 26214400 |
+------------------+----------+
1 row in set (0.00 sec)

5) Check the cache “hits”:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1079    |
| Qcache_free_memory      | 4635984 |
| Qcache_hits             | 210991  |
| Qcache_inserts          | 52877   |
| Qcache_lowmem_prunes    | 17821   |
| Qcache_not_cached       | 23497   |
| Qcache_queries_in_cache | 2273    |
| Qcache_total_blocks     | 6990    |
+-------------------------+---------+
8 rows in set (0.00 sec)

6) Compare that to the questions:

mysql> SHOW STATUS LIKE ‘Questions%’;
+—————+——–+
| Variable_name | Value |
+—————+——–+
| Questions | 354695 |
+—————+——–+
1 row in set (0.00 sec)

from this we can determine that at that point roughly 59% 210991/354695 of the database queries are “cached”.