MySQL Variables / Status / 優化

MySQL connections variables

Apple MySQL 必要修正一些預設值

//show

mysql> show variables like '%conn%'l;

//set

mysql> set global max_connections=10240;

//select

mysql> select @@global.sort_buffer_size;

• max_connections
  最大連線數。
  要比歷史連線數 status 的 Max_used_connections 高。
  預設：100
  建議：看系統
  
  
• max_user_connections
  每個使用者最大連線數
  
  
• max_connection_errors
  最大連線錯誤數，如果連線錯誤超過此數會出錯。
  連線錯誤是指例如帳密錯誤之類。
  這個設定沒有實際作用，
  是讓你看有多少錯誤。
  預設：
  建議：1844674407370954751，越大越好
  
  
• connect_timeout
  連線逾時(秒)
  預設：5
  建議：10-15
  
  

golang 的 sql package connections 設定

golang mysql api

Understanding maxLifeTime on MySQL

• SetMaxIdleConns
  the maximum number of connections in the idle connection pool.
  連接池中的最大閒置連線數。
  
  
• SetMaxOpenConns ¶
  最大同時開啟連線錯數。
  
  
• SetConnMaxLifetime
  連接池中閒置的連線當時間達到設定，就會被關閉。
  使用中的連線不會被關閉，
  
  

MySQL connections status

mysql=> show status like 'conn%';

• Connection_errors_accept
  
• Connection_errors_internal
  
• Connections_errors_max_connections
  
• Connections
  The number of connection attempts (successful or not) to the MySQL server.
  有史以來總連接數
  
  
• Max_used_connections
  最大同時連接數
  
  
• Max_used_connections_time
  最大連線使用時間
  
  
• Thread_connected
  The number of currently open connections.
  當前連接數
  
  
• Locked_connects
  

顯示當前連線及執行緒狀況

mysqladmin -u -p extended-status | grep -wi 'threads_connected\|threads_running' 
| awk '{ print $2,$4}'

//output
Threads_connected 12

Threads_running 1  

Threads_connected: Number of connections

Threads_running: connections currently running some sql

官方文件

MySQL 8 Variables 官方文件

MySQL 5.7 Variables 官方文件

網上說明

MySQL 8.0 預設值

優化

my.cnf優化

Fix poor mySQL default variables

改 variable 及 my.conf

open_files_limit  default 10000, optimize 10000

這是read only variable

需要編輯 /usr/lib/systemd/system/mysqld.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then run systemctl daemon-reload and systemctl restart mysql.service.

innodb_open_files default 4000,  

指定MySQL可同时打开.ibd文件的最大个数,独立于open_files_limit

max_connections  default 151, optimize  5000

max_user_connections default 0,   optimize 0  //沒限制

max_connect_errors  default 100, optimize  1844674407370954751 //越大越好，這個值沒用

connect_timeout default 10, optimize 15   //加5秒

table_open_cache default 4000, optimize 4000

所有线程打开表的数目。它的作用就是缓存表文件描述符，降低打开关闭表的频率，

table_open_cache_instances default 16, optimize 16

表缓存实例数，为通过减小会话间争用提高扩展性，表缓存会分区为table_open_cache/table_open_cache_instances大小的较小的缓存实例。DML语句会话只需要锁定所在缓存实例，这样多个会话访问表缓存时就可提升性能（DDL语句仍会锁定整个缓存）。默认该值为1，当16核以上可设置为8或16