MySQL optimization query_cache_limit parameter description

query_cache_limit

query_cache_limit specifies the buffer size that can be used by a single query. The default is 1M.

Optimizing query_cache_size

Starting from 4.0.1, MySQL provides a query buffer mechanism. Using query buffering, MySQL stores the SELECT statement and query results in the buffer. In the future, for the same SELECT statement (case-sensitive), the results will be read directly from the buffer. According to the MySQL User Manual, you can achieve up to 238% efficiency gains using the query buffer.

By checking the status values ​​Qcache_*, you can know whether the query_cache_size setting is reasonable (the above status values ​​can be obtained using SHOW STATUS LIKE 'Qcache%'). If the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient. If the value of Qcache_hits is also very large, it indicates that the query buffer is used very frequently and you need to increase the buffer size. If the value of Qcache_hits is not large, it indicates that your query repetition rate is very low. In this case, using the query buffer will affect efficiency, so you can consider not using the query buffer. In addition, adding SQL_NO_CACHE to the SELECT statement explicitly indicates that the query buffer is not used.

Other parameters related to query buffering include query_cache_type, query_cache_limit, and query_cache_min_res_unit. query_cache_type specifies whether to use query buffering and can be set to 0, 1, or 2. This variable is a SESSION-level variable. query_cache_limit specifies the buffer size that can be used by a single query. The default is 1M. query_cache_min_res_unit was introduced after version 4.1. It specifies the minimum unit of allocated buffer space, and the default is 4K. Check the status value Qcache_free_blocks. If the value is very large, it means that there are a lot of fragments in the buffer, which means that the query results are relatively small. In this case, you need to reduce query_cache_min_res_unit.

Because our server content is 96G, we set it as follows

query_cache_size = 128M
query_cache_limit = 8M

A relatively complete my.ini file. You can adjust it appropriately according to your own server to prevent MySQL from failing to run. This is for MySQL version 5.6.

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
#Set the client character encoding
[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock
basedir="E:/database/mysql/"
datadir="E:/database/mysql/data/"
tmpdir = "E:/database/mysql/tmp/"
#*** char set ***
character-set-server = utf8
#Set the character encoding on the server

#The following three parameters default to 12500, 1400, and 2000
performance_schema_max_table_instances = 20000
table_definition_cache = 2000
table_open_cache = 4096

#*** network ***
back_log = 1024
#skip-networking #Not enabled by default
max_connections = 10000
#max_connect_errors = 3000
table_open_cache = 4096
#external-locking #Not enabled by default
max_allowed_packet = 256M
max_heap_table_size = 128M
secure_file_priv=''
explicit_defaults_for_timestamp=true
concurrent_insert=2

#*** timeout ***
interactive_timeout=1000
wait_timeout=1000

# *** global cache ***
read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 1024M

# *** thread ***
thread_cache_size = 64
# thread_concurrency = 8
thread_stack = 512K

# *** query cache ***
query_cache_size = 128M
query_cache_limit = 8M

# *** index ***
ft_min_word_len = 8

#memlock #Not enabled by default
default-storage-engine=MYISAM
innodb=OFF
default-tmp-storage-engine=MYISAM
transaction_isolation = REPEATABLE-READ

# *** tmp table ***
tmp_table_size = 1024M

# *** bin log ***
#log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
#log_slave_updates #Not enabled by default
#log #Not enabled by default. This is the query log. Enabling it will affect server performance.
log_warnings #Open warning log

# *** slow query log ***
slow_query_log
long_query_time = 10
# *** Replication related settings
#server-id = 1
#server-id = 2
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only
#*** MyISAM Specific options
#myisam_recover
key_buffer_size = 2048M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options=force,backup

# *** INNODB Specific options ***
#skip-innodb #Not enabled by default
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
#loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 10240
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

If you want to optimize MySQL, you can refer to the article on MySQL Query Cache.