不知道大家之前對(duì)類似優(yōu)化Mysql參數(shù)的具體步驟的文章有無(wú)了解，今天我在這里給大家再簡(jiǎn)單的講講。感興趣的話就一起來(lái)看看正文部分吧，相信看完優(yōu)化Mysql參數(shù)的具體步驟你一定會(huì)有所收獲的。
[client] port=3306 socket=/tmp/mysql.sock [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. innodb_buffer_pool_size = 3G # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. user = mysql basedir = /app/mysql datadir = /app/mysql/data port=3307 server-id = 1 socket=/tmp/mysql.sock #允許創(chuàng)建函數(shù) log_bin_trust_function_creators = 1 character-set-server = utf8 #log-error = /var/log/mysql/error.log #pid-file = /var/log/mysql/mysql.pid general_log = 1 skip-name-resolve #skip-networking back_log = 300 max_connections = 1000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 4096 max_allowed_packet = 100M binlog_cache_size = 10M max_heap_table_size = 32M tmp_table_size = 64M read_buffer_size = 8M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 128M key_buffer_size = 8M thread_cache_size = 64 query_cache_type = 1 query_cache_size = 128M query_cache_limit = 2M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = mixed expire_logs_days = 30 performance_schema = 0 explicit_defaults_for_timestamp lower_case_table_names = 1 myisam_sort_buffer_size = 8M myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES [mysqldump] quick max_allowed_packet = 16M
max_connections = 1000 #客戶端連接數(shù) max_connect_errors = 6000 #錯(cuò)誤連接數(shù) ----- mysql> show variables like '%conn%'; +-----------------------------------------------+-----------------+ | Variable_name | Value | +-----------------------------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | disconnect_on_expired_password | ON | | init_connect | | | max_connect_errors | 6000 | | max_connections | 1000 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 0 | +-----------------------------------------------+-----------------+ 9 rows in set (0.01 sec) ----- 查看系統(tǒng)當(dāng)前連接數(shù) mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 9 | | Threads_connected | 3 | #連接數(shù) | Threads_created | 12 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.01 sec) ---------------------------------------------------------------------------------------------------- table_open_cache=4096 #通常此值需要大于Opened_tables值 查看當(dāng)前Opened_tables值 mysql> show status like '%Opened%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Opened_files | 2979 | | Opened_table_definitions | 1 | | Opened_tables | 1 | #對(duì)比 +--------------------------+-------+ 3 rows in set (0.00 sec) ---------------------------------------------------------------------------------------------------- max_heap_table_size = 32M tmp_table_size = 64M #參考文檔：https://www.jb51.net/article/85341.htm mysql> show status like '%created_tmp%'; #查看當(dāng)前情況 +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 2921 | | Created_tmp_files | 11 | | Created_tmp_tables | 8476 | +-------------------------+-------+ 3 rows in set (0.00 sec) ---------------------------------------------------------------------------------------------------- read_buffer_size = 8M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 128M key_buffer_size = 8M 參考文檔：https://www.jb51.net/article/84170.htm ---------------------------------------------------------------------------------------------------- innodb_buffer_pool_size = 3G #大建議值為內(nèi)存的75% --------------------------------------------------------------------------------------------------- thread_cache_size = 64 系統(tǒng)參數(shù)： mysql> show global status like 'Threads_%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 9 | #這就是thread_cache_size | Threads_connected | 3 | | Threads_created | 12 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.00 sec) 參考文檔：https://www.jianshu.com/p/47adb747652d --------------------------------------------------------------------------------------------------- query_cache_type = 1 query_cache_size = 128M query_cache_limit = 2M mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134200384 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 6 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
看完優(yōu)化Mysql參數(shù)的具體步驟這篇文章，大家覺(jué)得怎么樣？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
