mysql優化之query_cache_limit參數說明

Posted on by

query_cache_limit

query_cache_limit指定單個查詢能夠使用的緩沖區大小，缺省為1M。

優化query_cache_size

　　從4.0.1開始，MySQL提供瞭查詢緩沖機制。使用查詢緩沖，MySQL將SELECT語句和查詢結果存放在緩沖區中，今後對於同樣的 SELECT語句（區分大小寫），將直接從緩沖區中讀取結果。根據MySQL用戶手冊，使用查詢緩沖最多可以達到238％的效率。

　　通過檢查狀態值Qcache_*，可以知道query_cache_size設置是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那麼可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

　　與查詢緩沖有關的參數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查詢緩沖，可以設置為0、1、2，該變量是SESSION級的變量。query_cache_limit指定單個查詢能夠使用的緩沖區大小，缺省為1M。query_cache_min_res_unit是在4.1版本以後引入的，它指定分配緩沖區空間的最小單位，缺省為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢結果都比較小，此時需要減小 query_cache_min_res_unit。

因為我們的服務器內容96G所以如下設置

query_cache_size = 128M
query_cache_limit = 8M

比較完整的my.ini文件，大傢可以根據自己的服務器適當調整，以防止mysql無法運行，這個是針對mysql5.6版本的。

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
#設置客戶端的字符編碼
[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
#設置服務器端的字符編碼

#下面三個參數默認12500,1400,2000
performance_schema_max_table_instances = 20000
table_definition_cache = 2000
table_open_cache = 4096

#*** network ***
back_log = 1024
#skip-networking #默認沒有開啟
max_connections = 10000
#max_connect_errors = 3000
table_open_cache = 4096
#external-locking #默認沒有開啟
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 #默認沒有開啟
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 #默認沒有開啟
#log #默認沒有開啟，此處是查詢日志，開啟會影響服務器性能
log_warnings #開啟警告日志

# *** 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 #默認沒有開啟
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

如果想優化mysql可以結合MySQL的Query Cache詳解的文章。

推薦閱讀：