MySQL daily Chang and parameter tuning

daily operation and maintenance DBA Operations Work Daily

• Guide data, data modification, table structure change
• Add permissions, problem handling

Other

• Database selection, deployment, design, monitoring, backup, optimization, etc.

Daily operation and maintenance work:

• Guide Data and Precautions
• Data modification and Precautions
• Table structure Changes and precautions
• Additional privileges and Precautions
• Problem handling, such as slow database response

Guide Data and Precautions

1. The final form of the data (csv,sql text, or directly into a library)
2. Guide data method (Mysqldump,select into outfile,)
3. Precautions
   1. File permissions are required for export to CSV format, and only database local
   2. Avoid locking the Library lock table (mysqldump using the --single-transaction option does not lock the table)
   3. Avoid impact on the business, as far as possible in the mirror library

Data modification and Precautions

1. Remember to do a good backup before modifying
2. Open a transaction to do, modified after the check is ready to submit
3. Avoid modifying large amounts of data at once, which can be modified in batches
4. Avoid doing business spikes

Table Structure Change considerations

1. In the low peak period do
2. Is there a lock on the table structure change? (5.6 contains the online DDL feature)
3. Use pt-online-schema-change to complete, table structure changes
   1. Can avoid master-slave delay
   2. Can avoid excessive load, can speed limit

Percona maintains MySQL DBA must see the blog plus permissions and considerations

1. Give only the minimum permissions that meet the requirements
2. Avoid password changes when authorizing
3. Avoid giving app account Super permissions

Problem handling (database slow?) ）

1. Where is the database slow?
   1. Is the query slow or write slow
   2. Is the second level slow or the millisecond level slow
2. Show Processlist view MySQL connection information
3. View system Status (Iostat,top,vmstat)

Summary

1. Routine work is simple, but any action can affect online services
2. Combine different environments, different requirements to choose the most appropriate method to deal with
3. Daily work should be stable and not fast, ensuring online stability is the greatest responsibility of the DBA

Do not seek the fastest, but the most stable; Example 1. Modify the id<5 data in the T1 table

1) BackupSelect *  fromT1whereId<5  intoOutFile'/tmp/t1id5.txt';2) to perform the modificationbegin;UpdateT1SetB= -C= - whereId<5;Select *  fromT1whereId<5;rollback;/Commit;

2. Table structure Changes

1)5. Version 5:Alter TableT55AddC1int;Delete  fromT55whereId< -;(will not execute until it is stuck for a while)2)5. Version 6: Usedb1;Alter TableT55AddC1int;Delete  fromT55whereId< -;(smooth execution)Alter TableT1 Modify C1varchar( -);Delete  fromT55whereId< -;(will not execute until it is stuck for a while)3) PT-Online-Schema-Change tool./Pt-Online-Schema-Change--user=root--password=123456--host=localhost--socket=/mysqldata/node4/mysqld.sock D=db55,t=t55--alter "Add C2 int "--print--dry-run

3. Add Permissions

Grant Select,Insert,Delete  on *.*  to[Email protected]'localhost'Identified by '163'; #mysql-Unetease-p163--Socet=/mysqldata/node3/mysqld.sock--port=4001 (successful landing)Grant Select,Insert,Delete  on *.*  to[Email protected]'localhost'Identified by '123'; #mysql-Unetease-p163--socet=/mysqldata/node3/mysqld.sock--port=4001 (Login failed)

4. Guide data

 Usedb1;Select Count(*) fromT1; (first look at the amount of data)1) mysqldump Export #mysqldump-Uroot-p123456--single-transaction--socket=/mysqldata/node3/mysqld.sock db1 T1 >/tmp/t1.sqlGrant Select  on *.*  to[Email protected]'localhost'Identified by '163'; #mysqldump-Unetease-p163--socket=/mysqldata/node3/mysqld.sock db1 T1 >/tmp/t2.sql (Error, no lock table permission)#mysqldump-Unetease-p163--single-transaction--socket=/mysqldata/node3/mysqld.sock db1 T1 >/tmp/t2.sql (Success)#mysqldump-Uroot-p123456--single-transaction--socket=/mysqldata/node3/mysqld.sock db1 t1-t/tmp Usedb1;2) to export data as file permissions into outfileSelect *  fromT1 intoOutFile'/tmp/t1_2.txt';SelectT1.c,t3,b fromT1.id=T3.id intoOutFile'/tmp/t13.txt';

5. Database Slow problem: /tcpstat--port 4001-t 1-n 0 (tcpstat, view response time for each TCP connection, Percona Company) parameter Tuning Why do I need to adjust parameters

• Configuration between different servers, performance is not the same
• Different business scenarios do not have the same requirements for data
• MySQL default parameter is only a reference value, not suitable for all the application scenarios

What we need to know before we optimize

• Server-related configuration
• Business-related situations
• MySQL-related configuration

Server-related configuration

• Hardware conditions
• Operating system version
• CPU, Nic Power saving mode
• Server NUMA Settings---memory shards, CPU corresponds to memory;
• RAID Card Cache

Disk scheduling policy--write back

• Data is returned to the cache, and data is asynchronously brushed from the cache into the storage media

Disk scheduling policy--write through

• Data is written to cache and storage media at the same time to return write success

Write back performance is higher than write through and write through is more secure. RAID RAID--Inexpensive storage arrays

RAID0

• Simple is the use of multiple disks as a disk, capacity is multi-disk and, performance is also multi-disk;
• The problem is that when one of the disks is damaged, it cannot guarantee the security of the data;

RAID1

• Two disks to mirror each other--to achieve high availability
• Problem, can only use two disk to do, storage space is limited

RAID5

• Use at least three disk, total storage space is only two blocks, because it needs to store the checksum data block ;
• The high-availability implementation is to recover the data by validating the data block;
• Limited, only bad piece of disk, can pass the other two disk storage checksum data block, for data recovery, if the bad two disk will not be able to recover data

RAID10

• Make a RAID1 of two plates, then do RAID0.
• RAID1 ensure data security, RAID0 ensure data extensibility;
• limitation, do RAID1 of two disk at the same time bad, then also can not guarantee data security;

How RAID guarantees Data security

• BBU (Backup Battery Unit)
• • Ensure that data is secured by writing data from the cache to disk, even when the server is powered down or down, in case of battery power

Precautions What are the considerations for MySQL?

• Deployment installation for MySQL
• MySQL Monitoring
• MySQL parameter tuning

Requirements for deploying MySQL

• Recommended MySQL version: >=mysql5.5
• Recommended MySQL storage Engine: InnoDB

The basis of system tuning: monitoring

• Real-time monitoring of MySQL slow log
• Real-time monitoring of data server load conditions
• Real-time monitoring of MySQL internal status values

NetEase Internal monitoring parameters:

• Binlog file Size (MB)
• Bufferpool hit Ratio (%)
• CPU utilization (%)
• Disk read operation delay (MS/OP)
• Disk read bytes (kb/s)
• Number of disk reads (Times/sec)
• Disk storage footprint (MB)
• Disk write operation delay (MS/OP)
• Disk Write bytes (kb/s)
• Number of disk writes (Times/sec)
• Disk IO utilization (%)
• Amount of memory in use (%)
• Memory utilization (%)
• General transaction commit operations (Times/sec)
• Delete operation (Times/sec)
• Insert operation (Times/sec)
• Query Operations (Times/sec)
• Update operation (Times/sec)
• Two-phase transaction commit operations (Times/sec)

Usuallyfocus on which MySQL status

• Com_select/update/delete/insert
• • See if the database is getting more requests
• Bytes_received/bytes_sent
• • See MySQL Total throughput
• Buffer Pool Hit Rate
• • The InnoDB memory hit rate determines the performance
• Threads_connected/threads_created/threads_running
• • The first two more words, you can determine whether the application uses connection pooling, or whether the connection pool use is reasonable
  • A lot of active connections, the database is very busy, may be malicious attacks;

Why to adjust the parameters of MySQL:

• This generic MySQL database needs to be dynamically adjusted according to fields, making it a dedicated database
• Some of the parameters, most likely the older version, may be for current limiting and protection purposes, but as the performance of the machine improves these parameters, it is obviously inappropriate.

Read Optimization

• Rational use of indexes for MySQL query performance-to-pass reuse
• Proper tuning of MySQL parameters can also improve query performance

Innodb_buffer_pool_size: Cache pool Size, InnoDB maintain a memory area to complete the replacement of old and new data innodb_thread_concurrency:innodb internal concurrency control parameters, Set to 0 for no control if there are more concurrent requests, the meal set is smaller and the last request will be queued Write Optimization

• Table structure design uses the self-increment field as the primary key of the table
• Index only appropriate fields, too many indexes affect write performance
• Monitoring Server disk IO situation, if the write delay is large, you need to expand
• Select the correct MySQL version and set the parameters appropriately

What parameters help improve write performance

• Innodb_flush_log_at_trx_commit&&sync_binlog
• • Control Redo Log Refresh
  • Control the refresh of binary logs
• InnoDB log File size
• Innodb_io_capacity
• InnoDB Insert Buffer

innodb_flush_log_at_trx_commit:0,1,2n = 0 (efficient, but not secure – data is lost on server outages or MySQL outages) every second, writes data from the transaction log buffers to the log file, and flush log file data to disk n = 1 (inefficient, very secure-no data is lost) each transaction commits the transaction log from the cache to the log file, and refreshes the log file's data to disk, optimizing the use of this pattern to ensure data security n = 2 (efficient, But not secure--server downtime drops data) when each transaction commits, the transaction log data is written from the buffer to the log file, and every second, the log file is refreshed, but not necessarily flushed to disk, but depends on the operation system scheduling; Sync_binlog

• Controls whether each write Binlog needs to be persisted once

How to ensure transaction security

• Innodb_flush_log_at_trx_commit&&sync_binlog are set to 1
• Transactions are guaranteed to be consistent with binlog---will not cause master-slave inconsistencies

Transaction submission Process

What's wrong with serial

• SAS disks can only have 150--200 a fsync per second
• Conversion to data can only perform 50--60 transactions per second

Community and official improvements

Redo log's role in data recovery after a database crash; redo log problem

• If the write frequently causes the redo log to correspond to the oldest data dirty page has not been flushed to disk, the database will be stuck, forcing the dirty page to be flushed to disk
• MySQL default configuration file is 10M, very easy to write full, in the build environment should increase the size of redo log

Innodb_io_capacity

• InnoDB the number of dirty pages per brush, determines the throughput capacity of the InnoDB storage engine.
• Under high-performance storage media such as SSDs, this parameter should be improved to improve the performance of the database.

Insert Buffer

• Sequential read-write VS random Read and write
• Random request performance is much smaller than sequential requests

Merging as many random requests into sequential requests is the key to improving the performance of the database insert buffer to two-level index, the operation cache to insert buffer, and then merge these random requests into sequential requests; Summary:

• Server configuration to be reasonable (kernel version, disk scheduling policy, RAID card cache)
• Perfect monitoring system, early detection of problems
• Database version to keep up, not too new, and not too old
• Data performance Optimization:
• • Query optimization: Index optimization is the main, parameter optimization is supplemented
  • Write Optimization: Business optimization, parameter optimization supplemented

Summarize

• daily operations work:
  • Guide data,
    • mysqldump,select into outfile,
    • avoid lock list, mysqldump--single-transac tion;
  • Data modification
    • do a backup,
    • open transactions,
    • batch modification,
    • avoid peak times
  • table structure changes
    • low-peak do
    • 5.6 includes online DDL,
    • using pt-online-schema-change: Avoid master-slave delay, speed limit;
    • plus permissions
      • minimum permissions,
      • Avoid password changes when authorizing
    • do not seek the fastest, but the most stable;
• Parameter tuning
  • Raid0,raid1,raid5,radi10,
  • How RAID guarantees data security:
    • BBU, server power down, flush cache content to disk using battery power
  • Parameters to help improve write performance:
    • Innodb_flush_log_at_trx_commit Control Redo Log Refresh
    • Sync_binlog: Controlling the refresh of binary logs
    • InnoDB log file size: Redo log Loop Write, if it is too small, when the new write, the original log file is written and not persisted to the disk, this time will block the write; therefore, increase the transaction log size, may improve write performance;
    • InnoDB Insert Buffer:
      • Insert buffers, which will be read and written randomly, through this buffer, combined into a possible sequential read and write, to improve write performance.
      • Valid only for level two and non-unique indexes;
    • Innodb_io_capacity:
      • InnoDB how many dirty pages each refresh, determines the throughput capacity of the InnoDB storage engine
      • In SSDs, this parameter should be improved to improve the performance of the database;
  • Read optimization:
    • Innodb_buffer_pool_size: Cache Pool Size
    • Innodb_buffer_pool_size: concurrency control;

MySQL daily Chang and parameter tuning