欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時(shí)間：2021年07月02日 15:49:02   作者：生生不息.連綿不絕  
今天給大家?guī)硪黄坛剃P(guān)于MySQL常用配置和性能壓力測試的知識，文中配置參數(shù)以32內(nèi)存的服務(wù)器為例，通過實(shí)例代碼給大家介紹的非常詳細(xì)，需要的朋友參考下吧

一、MySQL常用配置

以下所有配置參數(shù)以32G內(nèi)存的服務(wù)器為基

1、打開獨(dú)立的表空間

innodb_file_per_table = 1

2、MySQL服務(wù)所允許的同時(shí)會話數(shù)的上限，默認(rèn)為151，經(jīng)常出現(xiàn)Too Many Connections的錯(cuò)誤提示，則需要增大此值

max_connections = 8000

3、操作系統(tǒng)在監(jiān)聽隊(duì)列中所能保持的連接數(shù)

back_log = 300

4、每個(gè)客戶端連接最大的錯(cuò)誤允許數(shù)量，當(dāng)超過該次數(shù)，MYSQL服務(wù)器將禁止此主機(jī)的連接請求，直到MYSQL服務(wù)器重啟或通過flush hosts命令清空此主機(jī)的相關(guān)信息

max_connect_errors = 1000

5、所有線程所打開表的數(shù)量

open_files_limit = 10240

6、每個(gè)連接傳輸數(shù)據(jù)大小，最大1G，須是1024的倍數(shù)，一般設(shè)為最大的BLOB的值

max_allowed_packet = 32M

7、請求的最大連接時(shí)間

wait_timeout = 10

8、排序緩沖被用來處理類似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ù)隔離級別

transaction_isolation = REPEATABLE-READ

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

thread_stack = 512K

14、開啟二進(jìn)制日志功能

log_bin

15、二進(jìn)制日志格式：基于行

binlog_format = row

16、InnoDB使用一個(gè)緩沖池來保存索引和原始數(shù)據(jù), 可設(shè)置這個(gè)變量到服務(wù)器物理內(nèi)存大小的80%

innodb_buffer_pool_size = 6G

17、用來同步IO操作的IO線程的數(shù)量

innodb_file_io_threads = 4

18、在Innodb核心內(nèi)的允許線程數(shù)量，建議的設(shè)置是CPU數(shù)量加上磁盤數(shù)量的兩倍

innodb_thread_concurrency = 16

19、用來緩沖日志數(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語句在被回滾前,InnoDB事務(wù)等待InnoDB行鎖的時(shí)間

innodb_lock_wait_timeout = 120

23、慢查詢記錄的閾值時(shí)長，默認(rèn)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  #內(nèi)存不足6G會報(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的性能壓力測試

常見測試工具：

  • mysqlslap
  • Sysbench
  • tpcc-mysql
  • MySQL Benchmark Suite
  • MySQL super-smack
  • MyBench

mysqlslap工具介紹

​mysqlslap來自于mariadb包，測試的過程默認(rèn)生成一個(gè)mysqlslap的schema,生成測試表t1，查詢和插入測試數(shù)據(jù)，mysqlslap庫自動生成，如果已經(jīng)存在則先刪除。用--only-print來打印實(shí)際的測試過程，整個(gè)測試完成后不會在數(shù)據(jù)庫中留下痕跡。

常用選項(xiàng)：

  • --auto-generate-sql, -a 自動生成測試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來測試并發(fā)壓力
  • --auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環(huán)境是讀操作還是寫操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認(rèn))
  • --auto-generate-sql-add-auto-increment 代表對生成的表自動添加auto_increment列，從5.1.18版本開始支持
  • --number-char-cols=N, -x N 自動生成的測試表中包含多少個(gè)字符類型的列，默認(rèn)1
  • --number-int-cols=N, -y N 自動生成的測試表中包含多少個(gè)數(shù)字類型的列，默認(rèn)1
  • --number-of-queries=N 總的測試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
  • --query=name,-q 使用自定義腳本執(zhí)行測試，例如可以調(diào)用自定義的存儲過程或者sql語句來執(zhí)行測試
  • --create-schema 代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database
  • --commint=N 多少條DML后提交一次
  • --compress, -C 如服務(wù)器和客戶端都支持壓縮，則壓縮信息
  • --concurrency=N, -c N 表示并發(fā)量，即模擬多少個(gè)客戶端同時(shí)執(zhí)行select；可指定多個(gè)值，以逗號或者--delimiter參數(shù)指定值做為分隔符
  • --engine=engine_name, -e engine_name 代表要測試的引擎，可以有多個(gè)，用分隔符隔開
  • --iterations=N, -i N 測試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運(yùn)行測試多少次
  • --only-print 只打印測試語句而不實(shí)際執(zhí)行
  • --detach=N 執(zhí)行N條語句后斷開重連
  • --debug-info, -T 打印內(nèi)存和CPU的相關(guān)信息

測試示例：

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）同時(shí)測試不同的存儲引擎的性能進(jìn)行對比

[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個(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）迭代測試

[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常用配置和性能壓力測試的詳細(xì)內(nèi)容，更多關(guān)于MySQL常用配置和性能壓力測試的資料請關(guān)注腳本之家其它相關(guān)文章！

相關(guān)文章

最新評論