MySQL系列之十五 MySQL常用配置和性能壓力測試

Posted on by

一、MySQL常用配置

以下所有配置參數以32G內存的服務器為基

1、打開獨立的表空間

innodb_file_per_table = 1

2、MySQL服務所允許的同時會話數的上限，默認為151，經常出現Too Many Connections的錯誤提示，則需要增大此值

max_connections = 8000

3、操作系統在監聽隊列中所能保持的連接數

back_log = 300

4、每個客戶端連接最大的錯誤允許數量，當超過該次數，MYSQL服務器將禁止此主機的連接請求，直到MYSQL服務器重啟或通過flush hosts命令清空此主機的相關信息

max_connect_errors = 1000

5、所有線程所打開表的數量

open_files_limit = 10240

6、每個連接傳輸數據大小，最大1G，須是1024的倍數，一般設為最大的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、指定單個查詢能夠使用的緩沖區大小，默認為1M

query_cache_limit = 4M

12、設定默認的事務隔離級別

transaction_isolation = REPEATABLE-READ

13、 線程使用的堆大小，此值限制內存中能處理的存儲過程的遞歸深度和SQL語句復雜性，此容量的內存在每次連接時被預留

thread_stack = 512K

14、開啟二進制日志功能

log_bin

15、二進制日志格式：基於行

binlog_format = row

16、InnoDB使用一個緩沖池來保存索引和原始數據, 可設置這個變量到服務器物理內存大小的80%

innodb_buffer_pool_size = 6G

17、用來同步IO操作的IO線程的數量

innodb_file_io_threads = 4

18、在Innodb核心內的允許線程數量，建議的設置是CPU數量加上磁盤數量的兩倍

innodb_thread_concurrency = 16

19、用來緩沖日志數據的緩沖區的大小

innodb_log_buffer_size = 16M

20、在日志組中每個日志文件的大小

innodb_log_file_size = 512M

21、在日志組中的文件總數

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，查詢和插入測試數據，mysqlslap庫自動生成，如果已經存在則先刪除。用–only-print來打印實際的測試過程，整個測試完成後不會在數據庫中留下痕跡。

常用選項：

  • –auto-generate-sql, -a 自動生成測試表和數據，表示用mysqlslap工具自己生成的SQL腳本來測試並發壓力
  • –auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環境是讀操作還是寫操作還是兩者混合的。取值包括：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 自動生成的測試表中包含多少個數字類型的列，默認1
  • –number-of-queries=N 總的測試查詢次數(並發客戶數×每客戶查詢次數)
  • –query=name,-q 使用自定義腳本執行測試，例如可以調用自定義的存儲過程或者sql語句來執行測試
  • –create-schema 代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database
  • –commint=N 多少條DML後提交一次
  • –compress, -C 如服務器和客戶端都支持壓縮，則壓縮信息
  • –concurrency=N, -c N 表示並發量，即模擬多少個客戶端同時執行select；可指定多個值，以逗號或者–delimiter參數指定值做為分隔符
  • –engine=engine_name, -e engine_name 代表要測試的引擎，可以有多個，用分隔符隔開
  • –iterations=N, -i N 測試執行的迭代次數，代表要在不同並發環境下，各自運行測試多少次
  • –only-print 隻打印測試語句而不實際執行
  • –detach=N 執行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來模擬並發連接

[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）執行一次測試，分別500和1000個並發，執行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常用配置和性能壓力測試的資料請關註WalkonNet其它相關文章！

推薦閱讀：