MySQL study notes again

The data structure

• Unstructured data, all kinds of documents, pictures, video/audio, etc. For this kind of data, we generally directly store the whole, and generally store in binary data format (such as files, pictures, videos, voice, etc., which need to be stored in the file system)
• Structured data. Structured data refers to the data that can be represented and stored in a relational database, and is represented as a two-dimensional form. The general characteristics are: data in behavioral units, a row of data represents the information of an entity, and the attributes of each row of data are the same (for example, row data should be stored in a relational database).
• Semi-structured data. Semi-structured data is a form of structured data that does not conform to the structure of the data model associated with the form of a relational database or other data table, but contains related markup to separate semantic elements and layer records and fields. Therefore, it is also called a self-describing structure. (Common semi-structured data include XML and JSON, which can be stored in a NoSQL database.)

Introduction to the

• Relational database, Sweden based on C++ language development
• Small, practical, high performance
• Other databases such as Oracle(Oracle), SQLServer(Microsoft), DB2(IBM)

The characteristics of

• Open source
• Community Edition free
• cross-platform
• High safety
• The cost is low
• Support for various development languages
• Support for powerful built-in functions
• Large data storage capacity

architecture

installation

• You can use the MySQL5.7 installation package of xdJA_centos7.4 tailored version to install the server
• You can use the following script to set connection permissions

Common commands

• Connecting to the database

mysql -uroot -p

• Display database

show databases;

• Select database

use xxx(databasename);

• Display database table

show tables;

• View table description

DESC xxx(datatable);

• Displays the database version and time

SELECT VERSION(),CURRENT_DATE(),CURRENT_TIME();

• Display running processes

show processlist;

• Viewing Configuration Items

show variables like '%tx_isolation%';

• Check innoDB status

show engine innodb status\G;

DDL

• Data Definition Languages (DDL): Defines database objects such as Data segments, databases, tables, columns, and indexes. Common keywords include CREATE, DROP, and ALTER
• Create database test1

create database test1;

• Deleting a specified database

drop database test1;

• Modify table fields

alter table emp modify ename varchar(20);

• Add and delete table fields

alter table emp add column age int(3);
alter table emp drop column age;

DML

• Data Manipulation Language (DML): a Data Manipulation Language used to add, delete, update, query database records, and check Data integrity. Common keywords include INSERT, DELETE, Update, and SELECT
• Insert records

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 and delete records

update emp set sal=4000 where ename='lisi';
delete from emp where ename='lisi';

• Query the specified column and the unique record

select ename,hiredate,sal,deptno from emp;
select distinct deptno from emp;

• Conditional query and sort

select * from emp where deptno =1 and sal<3000;
select * from emp order by sal;

• Grouping statistics

select count(1) from emp;
select deptno,count(1) as empnum from emp group by deptno

• Group statistics + conditional filtering

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;

• Aggregate functions are associated with multiple tables

select sum(sal),max(sal),min(sal) from emp;
select ename,deptname from emp,dept where emp.deptno=dept.deptno;

The storage engine

• Storage engine is how to store data, how to build microcosm for stored data and how to update, query data and other technology implementation methods;
• In relational data, the storage of the data is in the form of a table, so the storage engine can also be called a table type (that is, the type of the table that is stored and manipulated);
• Types include MyISAM, InnoDB, MERGE, MEMORY(HEAP), etc.

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. Data and indexes are merged into a single file,.frm(describes the structure of the table).ibd(table data file)
2. Support for foreign keys, transaction processing
3. Row locking
4. Transaction security with commit, rollback, and crash recovery capabilities
5. Parallel read and write, suitable for a large number of write operations of the table

The backup

Back up all databases

mysqldump -uroot -p --all-databases > itsca.sql

• You can back up all libraries after entering the password

[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.

Backing up a specified database

Mysqldump -u root -p --databases 1 database 2 > xxx.sql

Example Restore the mysql backup content

• In the system command line, enter the following to restore:

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

• Log in to the mysql system, and use the source command to find the file in the corresponding system to restore:

mysql> source /data/mysqlDump/mydb.sql

Based optimization

The selection of storage engine, field design, index, and SQL statement are all important factors that affect MySQL performance. We will not discuss them in detail this time.

Starting with the parameter configuration of the MySQL database, explore how to set reasonable parameter values to improve the performance of the MySQL database.

innodb_page_size

• Starting with innodb1.2.x, the page size can be set to 4K, 8K, or 16K with the innodb_page_size parameter. Innodb_page_size = innodb_page_size = innodb_page_size = innodb_page_size = innodb_page_size
• The default is 16 k
• 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.
• Consider using a page size that matches the internal sector size of your disk. Early SSD devices typically had a 4KB sector size. Some newer devices have a sector size of 16KB. The default InnoDB page size is 16KB. Keeping the page size close to the storage device block size minimizes the amount of unchanged data overwritten to disk.
• Reference documentation

https://dev.mysql.com/doc/ref…

innodb_buffer_pool_size

• It can be seen from its role, when the SYSTEM I/O is relatively idle, you can appropriately set this parameter large, when I/O is tight, you need to appropriately reduce, generally set to 50%-75% of the total memory size
• 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.

• Refer to the article

  https://dev.mysql.com/doc/ref…

sync_binlog

• Sync_binlog =0, after a transaction is committed, MySQL does not flush the information in the binlog_cache to the disk with the fsync command. Instead, the Filesystem decides when to do the synchronization, or when the cache is full.
• When sync_binlog=1, MySQL uses the fdatasync() function to synchronize the binary log to disk after writing the binary log once (the most secure configuration).
• Sync_binlog =n, after each n commit, MySQL will perform a disk synchronization command such as fsync to force the data in binlog_cache to disk.

• Reference documentation

  https://dev.mysql.com/doc/ref…

innodb_flush_log_at_trx_commit

• When innodb_flush_LOG_AT_trx_COMMIT =1 (default), MySQL writes the log buffer to the log file and flushs the log file to hard disk each time a transaction is committed. This has the advantage of optimal data security and the disadvantage of requiring a disk write every time a transaction is committed. In a large number of concurrent scenarios, excessive disk read and write operations waste CPU resources and reduce system efficiency. (Low efficiency, high safety)
• If the value is set to 0, data is written to the log every second and the log is written to the disk. (High efficiency, low security)
• A value of 2 means that data is written to the log each time the transaction is committed, but the log is written to disk at intervals of 1 second. (Efficiency, safety)

• Reference documentation

  https://dev.mysql.com/doc/ref…

• Double 1 is sync_binlog=1 and innodb_flush_log_at_trx_commit=1. These two parameters should be set to 1, the former to ensure the security of binlog, the latter to ensure the security of redo, they play a key role in the database crash recovery, not set to double 1 May cause data loss.

innodb_lru_scan_depth

• Innodb_lru_scan_depth is a new parameter added in 5.6. According to the official document, innodb_lru_scan_depth affects the number of dirty pages per brush on the Page Cleaner thread, which is a thread that loops every 1 second. Inside Innodb, this parameter corresponds to the variable srv_LRU_scan_depth
• The Page Cleaner thread brushes the length of the dirty page, starting at srv_LRU_scan_depth
• The default value is 1024. When I/OS are overloaded, lower the value

• Reference documentation

  https://www.cnblogs.com/zengk…
  
  
  https://dev.mysql.com/doc/ref…

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.
• Parameter :innodb_io_capacity: indicates the number of dirty pages in the database disk. The configuration pressure is related to the disk performance. If the configuration pressure is too large, the I/O capacity is insufficient, and there will be a lag.
• Innodb_io_capacity defaults to 200 pages. The size of this parameter depends on the IOPS of the disk, which is the number of inputs and outputs (or reads and writes) per second.

• Reference documentation

  https://dev.mysql.com/doc/ref…

innodb_io_capacity_max

• If you specify an innodb_io_capacity setting at startup but do not specify a value for innodb_io_capacity_max, innodb_io_capacity_max defaults to twice the value of innodb_io_capacity or 2000, whichever value is greater.

• Reference documentation

  https://dev.mysql.com/doc/ref…

  https://dev.mysql.com/doc/ref…

innodb_max_dirty_pages_pct

• Innodb_max_dirty_pages_pct is an important parameter used by the MySQL InnoDB storage engine to control the percentage of dirty pages in the buffer pool. If the number of dirty pages exceeds this value, InnoDB will start to brush dirty pages. This parameter only controls the percentage of dirty pages and does not affect the rate at which dirty pages are brushed.
• Default value: 75%
• In addition to innodb_max_dirty_pages_pct triggering a dirty page brush, there are a number of conditions that trigger a dirty page brush, including:

1. When the REDO log is almost full.
2. To ensure the number of free pages in MySQL, some pages are eliminated from the end of the LRU list as free pages. If the corresponding page is dirty, you need to flush the page to disk first.
3. MySQL
4. When the MySQL instance is closed normally.

• Reference documentation

  https://blog.csdn.net/w892824…

innodb_flush_neighbors

• Innodb_flush_neighbors parameter is the InnoDB is used to control buffer pool brushes the dirty dirty pages in the adjacent other dirty pages together brush to disk, in the era of traditional mechanical hard disk, open the parameters to reduce disk seek overhead, performance significantly.
• Value range: 0,1,2
• Default value: 1 for 5.7 and 0 for 8.0
• Meaning:

1. If the value is set to 0, dirty pages near dirty pages are not brushed.
2. If the value is set to 1, the dirty page is brushed away along with adjacent dirty pages.
3. If the value is set to 2, the dirty pages in the adjacent area will be wiped away when the dirty pages are brushed. The difference between 1 and 2 is that 2 brushes a larger area.

• If the MySQL server disk is a traditional HDD storage device, enabling this parameter can reduce the I/O disk seek cost and improve the performance. However, for SSD devices, the seek time has little impact on the performance. Disabling this parameter can spread write operations and improve the database performance. Due to the popularity of SSD devices, MySQL 8.0 changed the default value of this parameter from 1 to 0.

• Reference documentation

  https://www.mytecdb.com/blogD…
  
  
  https://dev.mysql.com/doc/ref…

innodb_page_cleaners

• show global status like ‘%Innodb_buffer_pool_wait_free%’; If the value is large, you’ll need to increase the innodb_page_Cleaners value along with the writer thread.

• Reference documentation

  https://www.jianshu.com/p/699…
  
  https://www.jianshu.com/p/ddb…

  https://dev.mysql.com/doc/ref…

innodb_log_file_size

• The space of the Redo log passedinnodb_log_file_sizeandinnodb_log_files_in_groupTo obtain the total available Redo log space, multiply the two parameters.
• You can use the MySQL Monitor PMM for detailed analysis, as shown in the following article

• Reference documentation

  https://blog.csdn.net/kai404/…

  https://dev.mysql.com/doc/ref…

The optimization results

• To install MySQL default parameters for our company, they need to be adjusted according to the hardware configuration of different machines

[mysqld] ########basic Settings ######## server-id = 11 port = 3306 user = mysql #bind_address = 10.166.224.32 # Autocommit = 1 # 5.6.x After the installation, open character_set_Server = UTF8MB4 skip_name_resolve = 1 MAX_connections = 800 max_CONNECt_errors = 1000 datadir = 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 # Modify innodb_buffer_pool_instances = 12 as required 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/ Innodb_undo_logs = 128 innoDB_undo_TABLespaces = 3 innodb_undo_TABLespaces = 3 Innodb_flush_neighbors = 1 Innodb_log_file_size = 8G # 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

• The FIO is installed on the MySQL database server

http://freshmeat.sourceforge….

[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= 1 Rw = randRw Tests random write and read I/O ioEngine =psync I/O engine uses PyNC bs= 16K Block file size for a single I/O is 16K numJobs =10 This time the test thread is 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): Lat (msec): min=1, Max =273, avg=84.36, stdev=55.83 in percentiles (msec): Th | th = [5], 1.00 5.00 = [12], th = [18], 10.00 20.00 th = [31], | th = [48], 30.00 40.00 th = [62], 50.00 th = [77], Th = [94], 60.00 70.00 th | = [113], th = [130], 80.00 90.00 th = [159], th = [186], 95.00 99.00 th | = [249], 99.50 th = [258], Th = [273], 99.90 99.95 th = [273], 99.99 th | = [273] bw (KB/s) : Write: 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 Min =277, Max =205221, avg=54699.64, stdev=44032.65 | th = [828], 1.00 5.00 th = [1272], th = [1672], 10.00 20.00 th = [3472], 30.00 th | = [23168], th = [37632], 40.00 50.00 th = [53504], Th = [64768], 60.00 70.00 th | = [79360], th = [92672], 80.00 90.00 th = [112128], th = [134144], 95.00 99.00 th | = [171008], Th = [193536], 99.50 99.90 th = [205824], th = [205824], 99.95 99.99 th | = [205824] bw (KB/s) : Lat (USEC) : 500=0.21%, 750=0.21%, 1000=0.69% lAT (MSEC) : 2 = 4.93%, 4 = 5.27%, 3.68%, 10 = 20 = 5.55%, 50 = 18.18% lat (msec) : 34.35% = 100, 250 = 26.44%, 500 = 0.49% CPU: Usr =0.01%, sys=0.06%, CTX =1492, majf=0, minf=7 IO unforge: 1 = 100.0%, 2 = 0.0%, 4 = 0.0%, 8 = 0.0%, 16 = 0.0%, 32 = 0.0% and > = 64 = 0.0% submit: 8 = 0 = 0.0%, 4 = 100.0%, 0.0%, 16 = 0.0%, 32 = 0.0%, 64 = 0.0% and > = 64 = 0.0% complete: 8 = 0 = 0.0%, 4 = 100.0%, 0.0%, 16 = 0.0%, 32 = 0.0%, 64 = 0.0% and > = 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= 6016/81876, in_queue=142913, util=100.00%, aggrios=727/1144, aggrmerge=0/370, Aggrticks = 61291/63730, aggrin_queue = 125075, aggrutil = 100.00% sda: Merge =0, ticks=61291/63730, in_queue=125075, util=100.00%

• You can see that the read and write IOPS is about 70. Set the following parameters for the existing server (Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz 4-core 8-thread, 24G memory, and imbrated mechanical hard disk) : TPS jitter can be significantly reduced or even eliminated during the interface performance test (caused by the MySQL database disk flushing, you can see the disk flushing information in the 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

• The above parameters are only the parameters that are relatively optimal for a particular machine to match the service

Some other concepts

• Brush dirty page mechanism https://www.ywnds.com/?p=1103… https://www.cnblogs.com/Jiang…
• The difference between the redolog and binlog https://blog.csdn.net/wanbin6…
• InnoDB dirty page refresh mechanism: https://www.cnblogs.com/olinu… https://www.jianshu.com/p/0b1… https://blog.csdn.net/qq_1831…

The problem summary

• Database deadlock continues to occur when verifying interface performance test of a project. This project is based on ARM architecture Kirin system, mysql is compiled for source code, 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

• Try to view mysql services before the project. The default value is transaction_ISOLATION = read-COMMITTED. After adjusting, verify that no deadlock problems occur again
• Reference documentation

https://cloud.tencent.com/dev…

Optimizing reference documents

• https://mp.weixin.qq.com/s/l-…
• https://www.cnblogs.com/wangd…
• https://blog.csdn.net/yimengl…
• https://cloud.tencent.com/dev…
• http://blog.itpub.net/2891601…