一、MySQL常用配置
以下所有配置參數(shù)以32G內存的服務器為基
1、打開獨立的表空間
innodb_file_per_table = 1
2、MySQL服務所允許的同時會話數(shù)的上限，默認為151，經常出現(xiàn)Too Many Connections的錯誤提示，則需要增大此值
max_connections = 8000
3、操作系統(tǒng)在監(jiān)聽隊列中所能保持的連接數(shù)
back_log = 300
4、每個客戶端連接最大的錯誤允許數(shù)量，當超過該次數(shù)，MYSQL服務器將禁止此主機的連接請求，直到MYSQL服務器重啟或通過flush hosts命令清空此主機的相關信息
max_connect_errors = 1000
5、所有線程所打開表的數(shù)量
open_files_limit = 10240
6、每個連接傳輸數(shù)據(jù)大小，最大1G，須是1024的倍數(shù)，一般設為最大的BLOB的值
max_allowed_packet = 32M
7、請求的最大連接時間
wait_timeout = 10
8、排序緩沖被用來處理類似ORDER BY以及GROUP BY隊列所引起的排序
sort_buffer_size = 16M
9、不帶索引的全表掃描，使用的buffer的最小值
join_buffer_size = 16M
10、查詢緩沖大小
query_cache_size = 128M
11、指定單個查詢能夠使用的緩沖區(qū)大小，默認為1M
query_cache_limit = 4M
12、設定默認的事務隔離級別
transaction_isolation = REPEATABLE-READ
13、 線程使用的堆大小，此值限制內存中能處理的存儲過程的遞歸深度和SQL語句復雜性，此容量的內存在每次連接時被預留
thread_stack = 512K
14、開啟二進制日志功能
log_bin
15、二進制日志格式：基于行
binlog_format = row
16、InnoDB使用一個緩沖池來保存索引和原始數(shù)據(jù), 可設置這個變量到服務器物理內存大小的80%
innodb_buffer_pool_size = 6G
17、用來同步IO操作的IO線程的數(shù)量
innodb_file_io_threads = 4
18、在Innodb核心內的允許線程數(shù)量，建議的設置是CPU數(shù)量加上磁盤數(shù)量的兩倍
innodb_thread_concurrency = 16
19、用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小
innodb_log_buffer_size = 16M
20、在日志組中每個日志文件的大小
innodb_log_file_size = 512M
21、在日志組中的文件總數(shù)
innodb_log_files_in_group = 3
22、SQL語句在被回滾前,InnoDB事務等待InnoDB行鎖的時間
innodb_lock_wait_timeout = 120
23、慢查詢記錄的閾值時長，默認10秒
long_query_time = 2
24、將沒有使用索引的查詢也記錄下來
log-queries-not-using-indexes
my.cnf示例：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table = 1
innodb_buffer_pool_size = 6442450944 #內存不足6G會報錯
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 120
log_bin = /var/lib/mysql/mariadb-bin
binlog_format = row
slow_query_log
long_query_time = 2
log-queries-not-using-indexes
transaction_isolation = REPEATABLE-READ
query_cache_size = 128M
query_cache_limit = 4M
max_connections = 8000
back_log = 300
max_connect_errors = 1000
open_files_limit = 10240
max_allowed_packet = 32M
wait_timeout = 10
sort_buffer_size = 16M
join_buffer_size = 16M
thread_stack = 512K
二、MySQL的性能壓力測試
常見測試工具：
- mysqlslap
- Sysbench
- tpcc-mysql
- MySQL Benchmark Suite
- MySQL super-smack
- MyBench
mysqlslap工具介紹
mysqlslap來自于mariadb包，測試的過程默認生成一個mysqlslap的schema,生成測試表t1，查詢和插入測試數(shù)據(jù)，mysqlslap庫自動生成，如果已經存在則先刪除。用--only-print來打印實際的測試過程，整個測試完成后不會在數(shù)據(jù)庫中留下痕跡。
常用選項：
- --auto-generate-sql, -a 自動生成測試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來測試并發(fā)壓力
- --auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環(huán)境是讀操作還是寫操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認)
- --auto-generate-sql-add-auto-increment 代表對生成的表自動添加auto_increment列，從5.1.18版本開始支持
- --number-char-cols=N, -x N 自動生成的測試表中包含多少個字符類型的列，默認1
- --number-int-cols=N, -y N 自動生成的測試表中包含多少個數(shù)字類型的列，默認1
- --number-of-queries=N 總的測試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
- --query=name,-q 使用自定義腳本執(zhí)行測試，例如可以調用自定義的存儲過程或者sql語句來執(zhí)行測試
- --create-schema 代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database
- --commint=N 多少條DML后提交一次
- --compress, -C 如服務器和客戶端都支持壓縮，則壓縮信息
- --concurrency=N, -c N 表示并發(fā)量，即模擬多少個客戶端同時執(zhí)行select；可指定多個值，以逗號或者--delimiter參數(shù)指定值做為分隔符
- --engine=engine_name, -e engine_name 代表要測試的引擎，可以有多個，用分隔符隔開
- --iterations=N, -i N 測試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運行測試多少次
- --only-print 只打印測試語句而不實際執(zhí)行
- --detach=N 執(zhí)行N條語句后斷開重連
- --debug-info, -T 打印內存和CPU的相關信息
測試示例：
1）單線程測試
[root@centos7 ~]# mysqlslap -a -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.004 seconds
Minimum number of seconds to run all queries: 0.004 seconds
Maximum number of seconds to run all queries: 0.004 seconds
Number of clients running queries: 1
Average number of queries per client: 0
2）多線程測試，使用–concurrency來模擬并發(fā)連接
[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter password:
Benchmark
Average number of seconds to run all queries: 3.384 seconds
Minimum number of seconds to run all queries: 3.384 seconds
Maximum number of seconds to run all queries: 3.384 seconds
Number of clients running queries: 500
Average number of queries per client: 0
3）同時測試不同的存儲引擎的性能進行對比
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.187 seconds
Maximum number of seconds to run all queries: 0.202 seconds
Number of clients running queries: 500
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.355 seconds
Minimum number of seconds to run all queries: 0.350 seconds
Maximum number of seconds to run all queries: 0.364 seconds
Number of clients running queries: 500
Average number of queries per client: 2
User time 0.33, System time 0.58
Maximum resident set size 22892, Integral resident set size 0
Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31896, Involuntary context switches 0
4）執(zhí)行一次測試，分別500和1000個并發(fā)，執(zhí)行5000次總查詢
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.378 seconds
Minimum number of seconds to run all queries: 3.378 seconds
Maximum number of seconds to run all queries: 3.378 seconds
Number of clients running queries: 500
Average number of queries per client: 10
Benchmark
Average number of seconds to run all queries: 3.101 seconds
Minimum number of seconds to run all queries: 3.101 seconds
Maximum number of seconds to run all queries: 3.101 seconds
Number of clients running queries: 1000
Average number of queries per client: 5
User time 0.84, System time 0.64
Maximum resident set size 83068, Integral resident set size 0
Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31524, Involuntary context switches 3
5）迭代測試
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.307 seconds
Minimum number of seconds to run all queries: 3.184 seconds
Maximum number of seconds to run all queries: 3.421 seconds
Number of clients running queries: 500
Average number of queries per client: 10
User time 2.18, System time 1.58
Maximum resident set size 74872, Integral resident set size 0
Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 73904, Involuntary context switches 3
以上就是MySQL系列之十五 MySQL常用配置和性能壓力測試的詳細內容，更多關于MySQL常用配置和性能壓力測試的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- mysql 5.7.20常用下載、安裝和配置方法及簡單操作技巧(解壓版免安裝)
- win10下mysql 8.0.23 安裝配置方法圖文教程
- MySQL性能壓力基準測試工具sysbench的使用簡介
- sysbench對mysql壓力測試的詳細教程
- MySQL壓力測試方法 如何使用mysqlslap測試MySQL的壓力?
- mysql壓力測試腳本實例
- Mysqlslap MySQL壓力測試工具 簡單教程
