Found some mysql-peformancce tuning parameters. This can help if you have a medium scale website or a blog hosting with mainly reads and very less writes.
mysql> SHOW GLOBAL STATUS LIKE 'Com_select';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_select | 96212863 |
+---------------+----------+
Com_select gives the total number of selects which is almost same as Qcache_not_cached in our cache.
Com_select = Qcache_inserts + Qcache_not_cached + queries with errors found during the column-privileges check
mysql> SHOW GLOBAL STATUS LIKE '%qcache%';
+-------------------------+---
| Variable_name | Value |
+-------------------------+---
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031352 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 96212009 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---
Configuration variables:
mysql> SHOW VARIABLES LIKE '%query_cache%';
+-----------------------------
| Variable_name | Value |
+-----------------------------
| have_query_cache | YES | this value is always YES, even if query caching is disabled.
| query_cache_limit | 1048576 | maximum size of individual query results that can be cached.
| query_cache_min_res_unit | 4096 | block size for query cache to allocate memory.
| query_cache_size | 1048576 | Setting it to 0 disables the query cache.
| query_cache_type | OFF | If the query cache size is greater than 0, the query_cache_type variable influences how it works.
| query_cache_wlock_invalidate | OFF | on WRITE, invalidate any queries in the query cache that refer to the table
+-----------------------------
Additionally this article explains mysql-query cache pretty well
I was able to validate most of the points in the article with the mysql documentation. But one point mentions that query caching "Does not support prepared statements and cursors".
Last thing,
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Select_full_join | 74593 |
+------------------+-------+
'The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.'
No comments:
Post a Comment