MySQL再學(xué)習(xí)筆記

數(shù)據(jù)結(jié)構(gòu)

  • 非結(jié)構(gòu)化數(shù)據(jù)已慢，各種文檔、圖片霹购、視頻/音頻等都屬于非結(jié)構(gòu)化數(shù)據(jù)佑惠。對(duì)于這類數(shù)據(jù)，我們一般直接整體進(jìn)行存儲(chǔ)齐疙，而且一般存儲(chǔ)為二進(jìn)制的數(shù)據(jù)格式（如文件膜楷、圖片、視頻贞奋、語(yǔ)音等需存入文件系統(tǒng)中）
  • 結(jié)構(gòu)化數(shù)據(jù)赌厅，結(jié)構(gòu)化的數(shù)據(jù)是指可以使用關(guān)系型數(shù)據(jù)庫(kù)表示和存儲(chǔ)，表現(xiàn)為二維形式的數(shù)據(jù)忆矛。一般特點(diǎn)是：數(shù)據(jù)以行為單位察蹲，一行數(shù)據(jù)表示一個(gè)實(shí)體的信息，每一行數(shù)據(jù)的屬性是相同的（如行數(shù)據(jù)等需存入關(guān)系型數(shù)據(jù)庫(kù)中）
  • 半結(jié)構(gòu)化數(shù)據(jù)催训，半結(jié)構(gòu)化數(shù)據(jù)是結(jié)構(gòu)化數(shù)據(jù)的一種形式洽议，它并不符合關(guān)系型數(shù)據(jù)庫(kù)或其他數(shù)據(jù)表的形式關(guān)聯(lián)起來(lái)的數(shù)據(jù)模型結(jié)構(gòu)，但包含相關(guān)標(biāo)記漫拭，用來(lái)分隔語(yǔ)義元素以及對(duì)記錄和字段進(jìn)行分層亚兄。因此，它也被稱為自描述的結(jié)構(gòu)采驻。（常見(jiàn)的半結(jié)構(gòu)數(shù)據(jù)有XML和JSON审胚，可存入NoSQL數(shù)據(jù)庫(kù)中）

簡(jiǎn)介

  • 關(guān)系型數(shù)據(jù)庫(kù),瑞典基于C++語(yǔ)言開(kāi)發(fā)
  • 小巧、實(shí)用礼旅、性能高
  • 其他數(shù)據(jù)庫(kù)如Oracle(甲骨文)膳叨、SQLServer(微軟)、DB2(IBM)

特點(diǎn)

  • 開(kāi)源
  • 社區(qū)版免費(fèi)
  • 跨平臺(tái)
  • 安全性高
  • 成本低
  • 支持各種開(kāi)發(fā)語(yǔ)言
  • 支持強(qiáng)大的內(nèi)置函數(shù)
  • 數(shù)據(jù)存儲(chǔ)量大

架構(gòu)

20200930-205650-0124.png

安裝方式

  • 可使用xdja_centos7.4裁剪版自帶MySQL5.7安裝包一鍵安裝
  • 可使用以下腳本設(shè)置連接權(quán)限

常用命令

  • 連接數(shù)據(jù)庫(kù)
mysql -uroot -p
  • 顯示數(shù)據(jù)庫(kù)
show databases;
  • 選擇數(shù)據(jù)庫(kù)
use xxx(databasename);
  • 顯示數(shù)據(jù)庫(kù)表
show tables;
  • 查看表描述
DESC xxx(datatable);
  • 顯示數(shù)據(jù)庫(kù)版本痘系、時(shí)間等
SELECT VERSION(),CURRENT_DATE(),CURRENT_TIME();
  • 顯示運(yùn)行的進(jìn)程
show processlist;
  • 查看配置項(xiàng)
show variables like '%tx_isolation%';
  • 查看innodb狀態(tài)
show engine innodb status\G;
DDL
  • DDL(Data Definition Languages):數(shù)據(jù)定義語(yǔ)言菲嘴，定義不同的數(shù)據(jù)段、數(shù)據(jù)庫(kù)汰翠、表龄坪、列、索引等數(shù)據(jù)庫(kù)對(duì)象复唤，常用的關(guān)鍵字包括create健田、drop、alter等
  • 創(chuàng)建數(shù)據(jù)庫(kù)test1
create database test1;
  • 刪除指定數(shù)據(jù)庫(kù)
drop database test1;
  • 修改表字段
alter table emp modify ename varchar(20);
  • 增加佛纫、刪除表字段
alter table emp add column age int(3);
alter table emp drop column age;
DML

  • DML(Data Manipulation Language):數(shù)據(jù)操作語(yǔ)言妓局，用于添加、刪除呈宇、更新和查詢數(shù)據(jù)庫(kù)記錄跟磨，并檢查數(shù)據(jù)完整性，常用的關(guān)鍵字主要包括insert攒盈、delete抵拘、update和select等

  • 插入記錄

insert into emp(ename,sal,deptno) values('zhangsan','2015-08-01','2000',1);
insert into emp(ename,sal,deptno) values('lisi','2015-08-01','3000',1);
create table dept(deptno int(3),deptname varchar(20);
insert into dept values(1,'tech'),(2,'sales'),(3,'fin');
  • 更新、刪除記錄
update emp set sal=4000 where ename='lisi';
delete from emp where ename='lisi';
  • 查詢指定列型豁、查詢不重復(fù)記錄
select ename,hiredate,sal,deptno from emp;
select distinct deptno from emp;
  • 條件查詢與排序
select * from emp where deptno =1 and sal<3000;
select * from emp order by sal;
  • 分組統(tǒng)計(jì)
select count(1) from emp;
select deptno,count(1) as empnum from emp group by deptno
  • 分組統(tǒng)計(jì)+條件過(guò)濾
select deptno,count(1) as empnum from emp group by deptno with rollup;
select deptno,count(1) as empnum from emp group by deptno having count(1)>1;
  • 聚合函數(shù)與多表關(guān)聯(lián)查詢
select sum(sal),max(sal),min(sal) from emp;
select ename,deptname from emp,dept where emp.deptno=dept.deptno;

存儲(chǔ)引擎

  • 存儲(chǔ)引擎就是如何存儲(chǔ)數(shù)據(jù)僵蛛、如何為存儲(chǔ)的數(shù)據(jù)建立縮影和如何更新、查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方法迎变；
  • 在關(guān)系數(shù)據(jù)中數(shù)據(jù)的存儲(chǔ)是以表的形式存儲(chǔ)充尉，所以存儲(chǔ)引擎也可以稱為表類型（即存儲(chǔ)和操作此表的類型）；
  • 類型有MyISAM衣形、InnoDB驼侠、MERGE姿鸿、MEMORY(HEAP)等；
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.03 sec)
  • InnoDB
  1. 數(shù)據(jù)和索引合并存儲(chǔ)為一個(gè)文件倒源，.frm(描述表的結(jié)構(gòu)) .ibd(表數(shù)據(jù)文件)
  2. 支持外鍵苛预，事務(wù)處理
  3. 行鎖定
  4. 具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全
  5. 并行讀寫笋熬，適用于大量的寫操作的表

備份

備份所有數(shù)據(jù)庫(kù)

mysqldump -uroot -p --all-databases > itsca.sql
  • 輸入密碼后即可備份所有庫(kù)
[root@xdja wch]# mysqldump -uroot -p --all-databases > itsca.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

備份指定數(shù)據(jù)庫(kù)

mysqldump -u root -p --databases 數(shù)據(jù)庫(kù)1 數(shù)據(jù)庫(kù)2 > xxx.sql

還原mysql備份內(nèi)容

  • 在系統(tǒng)命令行中热某，輸入如下實(shí)現(xiàn)還原：
mysql -uroot -p123456 < /data/mysqlDump/mydb.sql
  • 在登錄進(jìn)入mysql系統(tǒng)中,通過(guò)source指令找到對(duì)應(yīng)系統(tǒng)中的文件進(jìn)行還原：
mysql> source /data/mysqlDump/mydb.sql

基礎(chǔ)優(yōu)化

存儲(chǔ)引擎的選擇、字段設(shè)計(jì)胳螟、索引昔馋、SQL語(yǔ)句等都是影響MySQL性能的重要因素，本次暫不詳細(xì)討論糖耸。

僅從MySQL數(shù)據(jù)庫(kù)參數(shù)配置入手秘遏，探究設(shè)置合理的參數(shù)值來(lái)提升MySQL數(shù)據(jù)庫(kù)性能。

innodb_page_size

  • 從InnoDB1.2.x版本開(kāi)始嘉竟，可通過(guò)參數(shù)innodb_page_size將頁(yè)的大小設(shè)置為4K垄提、8K、16K周拐。若設(shè)置完成铡俐，則所有表中頁(yè)的大小都為innodb_page_size，不可以對(duì)其再次修改妥粟，否則啟動(dòng)會(huì)報(bào)錯(cuò)审丘，踩過(guò)坑。

  • 默認(rèn)是16k

  • Consider using a page size that matches the internal sector size of the disk. Early-generation SSD devices often have a 4KB sector size. Some newer devices have a 16KB sector size. The default InnoDB page size is 16KB. Keeping the page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.

  • 請(qǐng)考慮使用與磁盤的內(nèi)部扇區(qū)大小匹配的頁(yè)面大小勾给。早期 SSD 器件通常具有 4KB 扇區(qū)大小滩报。某些較新的設(shè)備具有 16KB 扇區(qū)大小。默認(rèn) InnoDB 頁(yè)面大小為 16KB播急。使頁(yè)面大小接近存儲(chǔ)設(shè)備塊大小可最大限度地減少重寫到磁盤的未更改數(shù)據(jù)量脓钾。

  • 參考文檔

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

innodb_buffer_pool_size

  • 從其作用可以看出，當(dāng)系統(tǒng)的IO比較空閑的時(shí)候桩警，可以適當(dāng)將這個(gè)參數(shù)設(shè)大可训，當(dāng)IO吃緊時(shí)，需要適當(dāng)減小捶枢，一般設(shè)置為內(nèi)存總大小的50%-75%

  • Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter the buffer pool size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

  • When you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size variable, which has a default of 128 MB.

  • 參考文章

    https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

sync_binlog

  • sync_binlog=0握截，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤烂叔，而讓Filesystem自行決定什么時(shí)候來(lái)做同步谨胞，或者cache滿了之后才同步到磁盤。

  • 當(dāng)sync_binlog=1時(shí), MySQL在寫1次二進(jìn)制日志binary log時(shí), 會(huì)使用fdatasync()函數(shù)將二進(jìn)制binary log同步到disk中去(安全性最高的配置)蒜鸡。

  • sync_binlog=n胯努，當(dāng)每進(jìn)行n次事務(wù)提交之后牢裳，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來(lái)將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。

  • 參考文檔

    https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog

innodb_flush_log_at_trx_commit

  • 當(dāng)innodb_flush_log_at_trx_commit=1時(shí)(默認(rèn))叶沛，每次事務(wù)提交時(shí), MySQL會(huì)把log buffer的數(shù)據(jù)寫入log file, 并且將log file flush到硬盤中蒲讯。這樣做的好處是數(shù)據(jù)安全性最佳，不足之處在于每次提交事務(wù)恬汁，都要進(jìn)行磁盤寫入的操作。在大并發(fā)的場(chǎng)景下辜伟，過(guò)于頻繁的磁盤讀寫會(huì)導(dǎo)致 CPU 資源浪費(fèi)氓侧，系統(tǒng)效率變低。（效率低导狡，安全性高）

  • 設(shè)置為0 表示每隔 1 秒將數(shù)據(jù)寫入日志约巷，并將日志寫入磁盤；（效率高旱捧，安全性低）

  • 設(shè)置為2 表示每次提交事務(wù)的時(shí)候都將數(shù)據(jù)寫入日志独郎，但是日志每間隔 1 秒寫入磁盤。（效率中枚赡，安全性中）

  • 參考文檔

    https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

  • 雙1就是 sync_binlog=1和 innodb_flush_log_at_trx_commit=1氓癌。這兩個(gè)參數(shù)線上要保證為1，前者保證binlog的安全贫橙，后者保證redo的安全贪婉，它們?cè)跀?shù)據(jù)庫(kù)crash recovery的時(shí)候起到了關(guān)鍵做用，不設(shè)置為雙1可能導(dǎo)致數(shù)據(jù)丟失卢肃。

innodb_lru_scan_depth

innodb_io_capacity

  • The default setting of 200 is generally sufficient for a lower-end non-rotational storage device. For higher-end, bus-attached devices, consider a higher setting such as 1000.

  • 參數(shù) :innodb_io_capacity :數(shù)據(jù)庫(kù)落盤臟頁(yè)個(gè)數(shù) ,配置壓力和磁盤的性能相關(guān)忙芒，如果過(guò)大巩螃，IO能力不足，則出現(xiàn)卡頓匕争。

  • innodb_io_capacity默認(rèn)是200避乏，單位是頁(yè)，該參數(shù)的設(shè)置大小取決于硬盤的IOPS甘桑，即每秒的輸入輸出量(或讀寫次數(shù))

  • 參考文檔

    https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

innodb_io_capacity_max

innodb_max_dirty_pages_pct

  • innodb_max_dirty_pages_pct 是 MySQL InnoDB 存儲(chǔ)引擎非常重要的一個(gè)參數(shù)拍皮，用來(lái)控制buffer pool中臟頁(yè)的百分比歹叮，當(dāng)臟頁(yè)數(shù)量占比超過(guò)這個(gè)參數(shù)設(shè)置的值時(shí)，InnoDB會(huì)啟動(dòng)刷臟頁(yè)的操作铆帽。該參數(shù)只控制臟頁(yè)百分比咆耿，并不會(huì)影響刷臟頁(yè)的速度。
  • 默認(rèn)值：75%
  • 除了臟頁(yè)百分比達(dá)到閾值innodb_max_dirty_pages_pct觸發(fā)刷臟頁(yè)以外爹橱，還有很多條件萨螺，也會(huì)觸發(fā)刷臟頁(yè)，主要包括：
  1. REDO日志快滿的時(shí)候愧驱。
  2. 為了保證MySQL中的空閑頁(yè)面的數(shù)量慰技，會(huì)從LRU鏈表尾部淘汰一部分頁(yè)面作為空閑頁(yè)。如果對(duì)應(yīng)的頁(yè)面是臟頁(yè)的話组砚，就需要先將頁(yè)面刷到磁盤吻商。
  3. MySQL
  4. MySQL實(shí)例正常關(guān)閉時(shí)候。

innodb_flush_neighbors

  • innodb_flush_neighbors 參數(shù)是InnoDB用來(lái)控制buffer pool刷臟頁(yè)時(shí)是否把臟頁(yè)鄰近的其他臟頁(yè)一起刷到磁盤糟红，在傳統(tǒng)的機(jī)械硬盤時(shí)代艾帐，打開(kāi)這個(gè)參數(shù)能夠減少磁盤尋道的開(kāi)銷，顯著提升性能盆偿。

  • 取值范圍：0柒爸，1，2

  • 默認(rèn)值：5.7版本為1事扭， 8.0版本為0

  • 含義：

  1. 設(shè)置為0時(shí)揍鸟，表示刷臟頁(yè)時(shí)不刷其附近的臟頁(yè)。
  2. 設(shè)置為1時(shí)句旱，表示刷臟頁(yè)時(shí)連帶其附近毗連的臟頁(yè)一起刷掉阳藻。
  3. 設(shè)置為2時(shí)，表示刷臟頁(yè)時(shí)連帶其附近區(qū)域的臟頁(yè)一起刷掉谈撒。1與2的區(qū)別是2刷的區(qū)域更大一些腥泥。

innodb_page_cleaners

innodb_log_file_size

優(yōu)化結(jié)果

  • 為我司安裝MySQL默認(rèn)參數(shù)蹬癌，需根據(jù)不同機(jī)器硬件配置進(jìn)行調(diào)整
[mysqld]

########basic settings########

server-id = 11
port = 3306
user = mysql
#bind_address = 10.166.224.32   #根據(jù)實(shí)際情況修改
autocommit = 1   #5.6.X安裝時(shí)权她，需要注釋掉，安裝完成后再打開(kāi)
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /home/mysql/data      #根據(jù)實(shí)際情況修改,建議和程序分離存放
transaction_isolation = READ-COMMITTED
#explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########

log_error = /home/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /home/mysql/logs/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 60
long_query_time = 1
min_examined_row_limit = 100
lower_case_table_names = 1 
########replication settings########

master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = /home/mysql/binlog/bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log = /home/mysql/relaylog/relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors

########innodb settings########

#innodb_page_size = 8192
innodb_buffer_pool_size = 24G    #根據(jù)實(shí)際情況修改
innodb_buffer_pool_instances = 12
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /home/mysql/redolog/  #根據(jù)實(shí)際情況修改
innodb_undo_directory = /home/mysql/undolog/      #根據(jù)實(shí)際情況修改
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 8G               #根據(jù)實(shí)際情況修改
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_flush_log_at_trx_commit = 1

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 12
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
  • MySQL數(shù)據(jù)庫(kù)服務(wù)器安裝fio

http://freshmeat.sourceforge.net/projects/fio/

[root@xdja wch]# tar -zxvf fio-2.1.10.tar.gz 
[root@xdja wch]# cd fio-2.1.10/
[root@xdja fio-2.1.10]# ./configure
[root@xdja fio-2.1.10]# make
[root@xdja fio-2.1.10]# make Install
[root@xdja fio-2.1.10]# cd ..
[root@xdja wch]# fio -filename=test0628 -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

direct=1 測(cè)試過(guò)程繞過(guò)機(jī)器自帶的buffer逝薪，使測(cè)試結(jié)果更真實(shí)
rw=randrw 測(cè)試隨機(jī)混合寫和讀的I/O
ioengine=psync io引擎使用pync方式
bs=16k 單次io的塊文件大小為16k
numjobs=10 本次的測(cè)試線程為10

mytest: (g=0): rw=randrw, bs=16K-16K/16K-16K/16K-16K, ioengine=psync, iodepth=1
...
fio-2.1.10
Starting 10 threads
mytest: Laying out IO file(s) (1 file(s) / 500MB)
Jobs: 10 (f=10): [mmmmmmmmmm] [100.0% done] [1328KB/896KB/0KB /s] [83/56/0 iops] [eta 00m:00s]
mytest: (groupid=0, jobs=10): err= 0: pid=23464: Mon Jun 28 12:52:55 2021
  read : io=11632KB, bw=1156.2KB/s, iops=72, runt= 10061msec
    clat (msec): min=1, max=273, avg=84.36, stdev=55.83
     lat (msec): min=1, max=273, avg=84.36, stdev=55.83
    clat percentiles (msec):
     |  1.00th=[    5],  5.00th=[   12], 10.00th=[   18], 20.00th=[   31],
     | 30.00th=[   48], 40.00th=[   62], 50.00th=[   77], 60.00th=[   94],
     | 70.00th=[  113], 80.00th=[  130], 90.00th=[  159], 95.00th=[  186],
     | 99.00th=[  249], 99.50th=[  258], 99.90th=[  273], 99.95th=[  273],
     | 99.99th=[  273]
    bw (KB  /s): min=   56, max=  191, per=10.04%, avg=116.07, stdev=29.91
  write: io=11424KB, bw=1135.5KB/s, iops=70, runt= 10061msec
    clat (usec): min=277, max=205220, avg=54698.78, stdev=44032.60
     lat (usec): min=277, max=205221, avg=54699.64, stdev=44032.65
    clat percentiles (usec):
     |  1.00th=[  828],  5.00th=[ 1272], 10.00th=[ 1672], 20.00th=[ 3472],
     | 30.00th=[23168], 40.00th=[37632], 50.00th=[53504], 60.00th=[64768],
     | 70.00th=[79360], 80.00th=[92672], 90.00th=[112128], 95.00th=[134144],
     | 99.00th=[171008], 99.50th=[193536], 99.90th=[205824], 99.95th=[205824],
     | 99.99th=[205824]
    bw (KB  /s): min=   29, max=  206, per=10.19%, avg=115.64, stdev=38.23
    lat (usec) : 500=0.21%, 750=0.21%, 1000=0.69%
    lat (msec) : 2=4.93%, 4=5.27%, 10=3.68%, 20=5.55%, 50=18.18%
    lat (msec) : 100=34.35%, 250=26.44%, 500=0.49%
  cpu          : usr=0.01%, sys=0.06%, ctx=1492, majf=0, minf=7
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=727/w=714/d=0, short=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
   READ: io=11632KB, aggrb=1156KB/s, minb=1156KB/s, maxb=1156KB/s, mint=10061msec, maxt=10061msec
  WRITE: io=11424KB, aggrb=1135KB/s, minb=1135KB/s, maxb=1135KB/s, mint=10061msec, maxt=10061msec

Disk stats (read/write):
    dm-2: ios=719/1496, merge=0/0, ticks=60164/81876, in_queue=142913, util=100.00%, aggrios=727/1144, aggrmerge=0/370, aggrticks=61291/63730, aggrin_queue=125075, aggrutil=100.00%
  sda: ios=727/1144, merge=0/370, ticks=61291/63730, in_queue=125075, util=100.00%
  • 可以看到讀寫的iops基本在70左右隅要，根據(jù)以上基本參數(shù)解釋，現(xiàn)有服務(wù)器（Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz 4核8線程翼闽，24G內(nèi)存拾徙，疊瓦式機(jī)械硬盤）進(jìn)行以下參數(shù)設(shè)置洲炊，可以明顯降低甚至消除接口性能測(cè)試過(guò)程中的TPS抖動(dòng)（MySQL數(shù)據(jù)庫(kù)刷盤導(dǎo)致感局，可以從error.log日志看到刷盤信息）
innodb_buffer_pool_size =6G
innodb_buffer_pool_instances = 1
innodb_lru_scan_depth = 200
innodb_io_capacity = 100
innodb_io_capacity_max = 200
innodb_log_file_size = 4G
innodb_page_cleaners= 1
  • 以上參數(shù)只是針對(duì)特定機(jī)器匹配服務(wù)相對(duì)最優(yōu)的參數(shù)

其他一些概念

問(wèn)題匯總

  • 驗(yàn)證一個(gè)項(xiàng)目接口性能測(cè)試時(shí)持續(xù)出現(xiàn)數(shù)據(jù)庫(kù)死鎖，本項(xiàng)目基于arm架構(gòu)麒麟系統(tǒng)暂衡，mysql為源碼編譯询微，transaction_isolation = REPEATABLE-READ
Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  • 嘗試查看項(xiàng)目之前的mysql服務(wù)，默認(rèn)為transaction_isolation = READ-COMMITTED狂巢，調(diào)整之后再次驗(yàn)證未再出現(xiàn)死鎖問(wèn)題
  • 參考文檔

https://cloud.tencent.com/developer/article/1710549

優(yōu)化參考文檔

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末撑毛，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子唧领，更是在濱河造成了極大的恐慌藻雌，老刑警劉巖，帶你破解...
    沈念sama閱讀 216,651評(píng)論 6贊 501
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件斩个，死亡現(xiàn)場(chǎng)離奇詭異胯杭，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)受啥，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,468評(píng)論 3贊 392
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門做个，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人滚局，你說(shuō)我怎么就攤上這事居暖。” “怎么了藤肢？”我有些...
    開(kāi)封第一講書人閱讀 162,931評(píng)論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵太闺，是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我嘁圈，道長(zhǎng)跟束，這世上最難降的妖魔是什么莺奸？ 我笑而不...
    開(kāi)封第一講書人閱讀 58,218評(píng)論 1贊 292
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮冀宴，結(jié)果婚禮上灭贷，老公的妹妹穿的比我還像新娘。我一直安慰自己略贮，他們只是感情好甚疟，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,234評(píng)論 6贊 388
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著逃延，像睡著了一般览妖。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上揽祥，一...
    開(kāi)封第一講書人閱讀 51,198評(píng)論 1贊 299
  • 城市分裂傳說(shuō)
    那天讽膏，我揣著相機(jī)與錄音，去河邊找鬼拄丰。 笑死府树，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的料按。 我是一名探鬼主播奄侠，決...
    沈念sama閱讀 40,084評(píng)論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼载矿！你這毒婦竟也來(lái)了垄潮？” 一聲冷哼從身側(cè)響起，我...
    開(kāi)封第一講書人閱讀 38,926評(píng)論 0贊 274
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤闷盔，失蹤者是張志新（化名）和其女友劉穎弯洗，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體逢勾，經(jīng)...
    沈念sama閱讀 45,341評(píng)論 1贊 311
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡牡整，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,563評(píng)論 2贊 333
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了敏沉。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片果正。...
    茶點(diǎn)故事閱讀 39,731評(píng)論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖盟迟，靈堂內(nèi)的尸體忽然破棺而出秋泳，到底是詐尸還是另有隱情，我是刑警寧澤攒菠，帶...
    沈念sama閱讀 35,430評(píng)論 5贊 343
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布迫皱，位于F島的核電站，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏卓起。R本人自食惡果不足惜和敬，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,036評(píng)論 3贊 326
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望戏阅。 院中可真熱鬧昼弟，春花似錦、人聲如沸奕筐。這莊子的主人今日做“春日...
    開(kāi)封第一講書人閱讀 31,676評(píng)論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)离赫。三九已至芭逝，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間渊胸，已是汗流浹背旬盯。 一陣腳步聲響...
    開(kāi)封第一講書人閱讀 32,829評(píng)論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來(lái)泰國(guó)打工， 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留翎猛，地道東北人胖翰。 一個(gè)月前我還...
    沈念sama閱讀 47,743評(píng)論 2贊 368
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長(zhǎng)得像办成，于是被迫代替她去往敵國(guó)和親泡态。 傳聞我的和親對(duì)象是個(gè)殘疾皇子搂漠，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,629評(píng)論 2贊 354

推薦閱讀更多精彩內(nèi)容