MySQL系列之十五 MySQL常用配置和性能壓力測(cè)試
一、MySQL常用配置
以下所有配置參數(shù)以32G內(nèi)存的服務(wù)器為基
1、打開(kāi)獨(dú)立的表空間
innodb_file_per_table = 1
2、MySQL服務(wù)所允許的同時(shí)會(huì)話數(shù)的上限，默認(rèn)為151，經(jīng)常出現(xiàn)Too Many Connections的錯(cuò)誤提示，則需要增大此值
max_connections = 8000
3、操作系統(tǒng)在監(jiān)聽(tīng)隊(duì)列中所能保持的連接數(shù)
back_log = 300
4、每個(gè)客戶端連接最大的錯(cuò)誤允許數(shù)量，當(dāng)超過(guò)該次數(shù)，MYSQL服務(wù)器將禁止此主機(jī)的連接請(qǐng)求，直到MYSQL服務(wù)器重啟或通過(guò)flush hosts命令清空此主機(jī)的相關(guān)信息
max_connect_errors = 1000
5、所有線程所打開(kāi)表的數(shù)量
open_files_limit = 10240
6、每個(gè)連接傳輸數(shù)據(jù)大小，最大1G，須是1024的倍數(shù)，一般設(shè)為最大的BLOB的值
max_allowed_packet = 32M
7、請(qǐng)求的最大連接時(shí)間
wait_timeout = 10
8、排序緩沖被用來(lái)處理類似ORDER BY以及GROUP BY隊(duì)列所引起的排序
sort_buffer_size = 16M
9、不帶索引的全表掃描，使用的buffer的最小值
join_buffer_size = 16M
10、查詢緩沖大小
query_cache_size = 128M
11、指定單個(gè)查詢能夠使用的緩沖區(qū)大小，默認(rèn)為1M
query_cache_limit = 4M
12、設(shè)定默認(rèn)的事務(wù)隔離級(jí)別
transaction_isolation = REPEATABLE-READ
13、 線程使用的堆大小，此值限制內(nèi)存中能處理的存儲(chǔ)過(guò)程的遞歸深度和SQL語(yǔ)句復(fù)雜性，此容量的內(nèi)存在每次連接時(shí)被預(yù)留
thread_stack = 512K
14、開(kāi)啟二進(jìn)制日志功能
log_bin
15、二進(jìn)制日志格式：基于行
binlog_format = row
16、InnoDB使用一個(gè)緩沖池來(lái)保存索引和原始數(shù)據(jù), 可設(shè)置這個(gè)變量到服務(wù)器物理內(nèi)存大小的80%
innodb_buffer_pool_size = 6G
17、用來(lái)同步IO操作的IO線程的數(shù)量
innodb_file_io_threads = 4
18、在Innodb核心內(nèi)的允許線程數(shù)量，建議的設(shè)置是CPU數(shù)量加上磁盤(pán)數(shù)量的兩倍
innodb_thread_concurrency = 16
19、用來(lái)緩沖日志數(shù)據(jù)的緩沖區(qū)的大小
innodb_log_buffer_size = 16M
20、在日志組中每個(gè)日志文件的大小
innodb_log_file_size = 512M
21、在日志組中的文件總數(shù)
innodb_log_files_in_group = 3
22、SQL語(yǔ)句在被回滾前,InnoDB事務(wù)等待InnoDB行鎖的時(shí)間
innodb_lock_wait_timeout = 120
23、慢查詢記錄的閾值時(shí)長(zhǎng)，默認(rèn)10秒
long_query_time = 2
24、將沒(méi)有使用索引的查詢也記錄下來(lái)
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 #內(nèi)存不足6G會(huì)報(bào)錯(cuò) 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的性能壓力測(cè)試
常見(jiàn)測(cè)試工具：
- mysqlslap
- Sysbench
- tpcc-mysql
- MySQL Benchmark Suite
- MySQL super-smack
- MyBench
mysqlslap工具介紹
mysqlslap來(lái)自于mariadb包，測(cè)試的過(guò)程默認(rèn)生成一個(gè)mysqlslap的schema,生成測(cè)試表t1，查詢和插入測(cè)試數(shù)據(jù)，mysqlslap庫(kù)自動(dòng)生成，如果已經(jīng)存在則先刪除。用--only-print來(lái)打印實(shí)際的測(cè)試過(guò)程，整個(gè)測(cè)試完成后不會(huì)在數(shù)據(jù)庫(kù)中留下痕跡。
常用選項(xiàng)：
- --auto-generate-sql, -a 自動(dòng)生成測(cè)試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來(lái)測(cè)試并發(fā)壓力
- --auto-generate-sql-load-type=type 測(cè)試語(yǔ)句的類型。代表要測(cè)試的環(huán)境是讀操作還是寫(xiě)操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認(rèn))
- --auto-generate-sql-add-auto-increment 代表對(duì)生成的表自動(dòng)添加auto_increment列，從5.1.18版本開(kāi)始支持
- --number-char-cols=N, -x N 自動(dòng)生成的測(cè)試表中包含多少個(gè)字符類型的列，默認(rèn)1
- --number-int-cols=N, -y N 自動(dòng)生成的測(cè)試表中包含多少個(gè)數(shù)字類型的列，默認(rèn)1
- --number-of-queries=N 總的測(cè)試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
- --query=name,-q 使用自定義腳本執(zhí)行測(cè)試，例如可以調(diào)用自定義的存儲(chǔ)過(guò)程或者sql語(yǔ)句來(lái)執(zhí)行測(cè)試
- --create-schema 代表自定義的測(cè)試庫(kù)名稱，測(cè)試的schema，MySQL中schema也就是database
- --commint=N 多少條DML后提交一次
- --compress, -C 如服務(wù)器和客戶端都支持壓縮，則壓縮信息
- --concurrency=N, -c N 表示并發(fā)量，即模擬多少個(gè)客戶端同時(shí)執(zhí)行select；可指定多個(gè)值，以逗號(hào)或者--delimiter參數(shù)指定值做為分隔符
- --engine=engine_name, -e engine_name 代表要測(cè)試的引擎，可以有多個(gè)，用分隔符隔開(kāi)
- --iterations=N, -i N 測(cè)試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運(yùn)行測(cè)試多少次
- --only-print 只打印測(cè)試語(yǔ)句而不實(shí)際執(zhí)行
- --detach=N 執(zhí)行N條語(yǔ)句后斷開(kāi)重連
- --debug-info, -T 打印內(nèi)存和CPU的相關(guān)信息
測(cè)試示例：
1）單線程測(cè)試
[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）多線程測(cè)試，使用–concurrency來(lái)模擬并發(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）同時(shí)測(cè)試不同的存儲(chǔ)引擎的性能進(jìn)行對(duì)比
[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í)行一次測(cè)試，分別500和1000個(gè)并發(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）迭代測(cè)試
[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常用配置和性能壓力測(cè)試的詳細(xì)內(nèi)容，更多關(guān)于MySQL常用配置和性能壓力測(cè)試的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信