Practical application of MySQL cache Query

Source: Internet
Author: User
The following articles mainly describe how to query and set globalquery_cache_size for MySQL cache. We all know that when the access volume increases, the pressure on the MySQL database will increase! What if we reduce the pressure on it? Cache first. I have a professional data engineer here to explain. Set the cache globalquery_cache_sizesetglobalqu

The following article describes how to query MySQL cache and set global query_cache_size. We all know that when the access volume increases, the pressure on the MySQL database will increase! What if we reduce the pressure on it? Cache first. I have a professional data engineer here to explain. Set global query_cache_size setglobal qu

The following article describes how to query MySQL cache and set global query_cache_size. We all know that when the access volume increases, the pressure on the MySQL database will increase! What if we reduce the pressure on it? Cache first.

I have a professional data engineer here to explain.

Set global query_cache_size

 
 
  1. set global query_cache_size = 102760448;
  2. set global query_cache_limit = 2097152;
  3. set global query_cache_size = 600000;

The cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly retrieves the results from the cache without parsing and executing the SQL statement. If the table is changed, all buffered queries using this table will no longer be valid, and the items related to the query cache value will be cleared. Changes refer to any data or structure changes in the TABLE, including INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database, this also includes queries for tables mapped to changed tables that use MERGE tables. Obviously, this is not suitable for MySQL cache query cache for frequently updated tables. For tables that do not often change data and have a large number of identical SQL queries, query cache saves a lot of performance.

The query must be exactly the same (in bytes) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

The following SQL query cache is considered different:

 
 
  1. SELECT * FROM tbl_name
  2. Select * from tbl_name

Query cache Parameters

 
 
  1. MySQL> SHOW VARIABLES LIKE '%query_cache%';
    +------------------------------+---------+ | Variable_name | Value |
    +------------------------------+---------+ | have_query_cache | YES |

Query whether the cache is available | query_cache_limit | 1048576 | -- maximum value of the specific query result that can be cached | query_cache_min_res_unit | 4096 | query_cache_size | 599040 | -- query the cache size | query_cache_type | ON | supports MySQL cache query Cache

 
 
  1. | query_cache_wlock_invalidate | OFF | +------------------------------+---------+

The following is a simple example:

 
 
  1. [MySQL@csdba1850 ~]$ MySQL -u root -p
  2. Enter password:
  3. Welcome to the MySQL monitor. Commands end with ; or \g.
  4. Your MySQL connection id is 3
  5. Server version: 5.0.45-community MySQL Community Edition (GPL)
  6. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  7. MySQL> set global query_cache_size = 600000;

Set cache memory

 
 
  1. Query OK, 0 rows affected (0.00 sec)
  2. MySQL> set session query_cache_type = ON;

Enable query Cache

 
 
  1. Query OK, 0 rows affected (0.00 sec)
  2. MySQL> use test Reading table information for completion
    of table and column names You can turn off this feature to
    get a quicker startup with -A Database changed mysql> show tables;
    +----------------+ | Tables_in_test | +----------------+ | animals |
    | person | +----------------+ 5 rows in set (0.00 sec)
    mysql> select count(*) from animals; +----------+ | count(*)
    | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)

Qcache_hits indicates the cumulative number of times that mysql cache queries hit in the cache, which is the cumulative value.

 
 
  1. Mysql> show status like 'qcache _ hits '; + --------------- + ------- +
    | Variable_name | Value | + --------------- + ------- + | Qcache_hits
    | 0 | -- 0 + --------------- + ------- + 8 rows in set (0.00 sec)
    Mysql> select count (*) from animals; + ---------- + | count (*)
    | + ---------- + | 6 | + ---------- + 1 row in set (0.00 sec)
    Mysql> show status like 'qcache % '; + --------------- + ------- +
    | Variable_name | Value | + --------------- + ------- + | Qcache_hits | 1 |

Indicates that the SQL statement is directly obtained in the cache and does not need to be parsed.

 
 
  1. +---------------+-------+ 8 rows in set (0.00 sec)
    mysql> select count(*) from animals; +----------+
    | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)
    mysql> select count(*) from animals; +----------+ | count(*)
    | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)
    mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+
    | Variable_name | Value | +---------------+-------+ | Qcache_hits | 3 |

The preceding SQL statement is used to obtain the result directly from the cache.

 
 
  1. +---------------+-------+ 1 row in set (0.00 sec) mysql> insert into animals select 9,'testsds' ;

After data is inserted, all SQL caches related to the table will be cleared.

 
 
  1. Query OK, 1 row affected (0.00 sec) Records:
    1 Duplicates: 0 Warnings: 0 mysql> select count(*) from animals;
    +----------+ | count(*) | +----------+ | 7 | +----------+
    1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits';
    +---------------+-------+ | Variable_name | Value |
    +---------------+-------+ | Qcache_hits | 3 |

Or equal to 3 indicates that the previous SQL statement is not directly obtained from the cache.

 
 
  1. +---------------+-------+ 1 row in set (0.00 sec)
    mysql> select count(*) from animals; +----------+
    | count(*) | +----------+ | 7 | +----------+
    1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits';
    +---------------+-------+ | Variable_name | Value | +---------------+-------+
    | Qcache_hits | 4 | +---------------+-------+ 1 row in set (0.00 sec)

The above content is an introduction to mysql cache query and settings. I hope you will find some gains.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.