Mysql 之 参数优化了解

一、优化的配置文件

[client]port=3306socket=/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 = mysqlbasedir = /app/mysqldatadir = /app/mysql/dataport=3307server-id = 1socket=/tmp/mysql.sock#允许创建函数log_bin_trust_function_creators = 1character-set-server = utf8#log-error = /var/log/mysql/error.log#pid-file = /var/log/mysql/mysql.pidgeneral_log = 1skip-name-resolve#skip-networkingback_log = 300max_connections = 1000max_connect_errors = 6000open_files_limit = 65535table_open_cache = 4096 max_allowed_packet = 100Mbinlog_cache_size = 10Mmax_heap_table_size = 32Mtmp_table_size = 64Mread_buffer_size = 8Mread_rnd_buffer_size = 8Msort_buffer_size = 8Mjoin_buffer_size = 128Mkey_buffer_size = 8Mthread_cache_size = 64query_cache_type = 1query_cache_size = 128Mquery_cache_limit = 2Mft_min_word_len = 4log_bin = mysql-binbinlog_format = mixedexpire_logs_days = 30performance_schema = 0explicit_defaults_for_timestamplower_case_table_names = 1myisam_sort_buffer_size = 8Mmyisam_repair_threads = 1interactive_timeout = 28800wait_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 riskssymbolic-links=0# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES[mysqldump]quickmax_allowed_packet = 16M

二、参数解释查看

max_connections = 1000             #客户端连接数max_connect_errors = 6000        #错误连接数-----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)-----查看系统当前连接数mysql> show status like 'Threads%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_cached    | 9     || Threads_connected | 3     |    #连接数| Threads_created   | 12    || Threads_running   | 2     |+-------------------+-------+4 rows in set (0.01 sec)----------------------------------------------------------------------------------------------------table_open_cache=4096     #通常此值需要大于Opened_tables值查看当前Opened_tables值mysql> show status like '%Opened%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| Opened_files             | 2979  || Opened_table_definitions | 1     || Opened_tables            | 1     |         #对比+--------------------------+-------+3 rows in set (0.00 sec)----------------------------------------------------------------------------------------------------max_heap_table_size = 32Mtmp_table_size = 64M#参考文档：https://www.jb51.net/article/85341.htmmysql> show status like '%created_tmp%';      #查看当前情况+-------------------------+-------+| 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 = 8Mread_rnd_buffer_size = 8Msort_buffer_size = 8Mjoin_buffer_size = 128Mkey_buffer_size = 8M参考文档：https://www.jb51.net/article/84170.htm----------------------------------------------------------------------------------------------------innodb_buffer_pool_size = 3G                #最大建议值为内存的75%---------------------------------------------------------------------------------------------------thread_cache_size = 64系统参数：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 = 1query_cache_size = 128Mquery_cache_limit = 2Mmysql> 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)参考文档：https://blog.csdn.net/u014044812/article/details/78924315