Causes and solutions for slow MySQL query speed and poor performance

1. What affects database query speed?

1.1 Four factors affecting database query speed

1.2 Risk Analysis

QPS: QueriesPerSecond means "query rate per second", which is the number of queries a server can respond to per second. It is a measure of the amount of traffic a specific query server handles within a specified time.

TPS: It is the abbreviation of TransactionsPerSecond, which means the number of transactions per second. It is a unit of measurement for software testing results. The client starts timing when it sends a request and ends timing after receiving a response from the server, thereby calculating the elapsed time and the number of completed transactions.

Tips: It is best not to back up the database on the main database and cancel such plans before large-scale events.

Inefficient SQL: Ultra-high QPS and TPS. Large amount of concurrency: The number of data connections is full (the default value of max_connection is 100, and the number of connections is usually set larger). Concurrency: The number of requests that the database server handles at the same time. Extremely high CPU usage: CPU resources are exhausted and the server crashes. Disk IO: Disk IO performance suddenly drops and scheduled tasks consume a lot of disk performance. Solution: Faster disk devices, adjusting scheduled tasks, and performing disk maintenance.

1.3 Network card traffic: How to avoid being unable to connect to the database

Reduce the number of slave servers (slave servers will copy logs from the master server) Perform hierarchical caching (to avoid large-scale cache failure on the front end) Avoid using select* for queries Separate business networks and server networks

1.4 Problems caused by large tables (important)

1.4.1 Characteristics of large tables

The number of records is huge, with more than 10 million rows in a single table. The data file is huge, exceeding 10G.

1.4.2 The dangers of large tables

1. Slow query: It is difficult to filter out the required data in a short time. The query word has low discrimination -> To filter out a part of the data in a large data table, a large amount of disk IO will be generated -> Reduce disk efficiency

2. Impact on DDL:

Building the index takes a long time:

MySQL-v<5.5 Creating an index will lock the table MySQL-v>=5.5 Creating an index will cause master-slave delay (mysql creates an index, first on the group, then on the database)

Modifying the table structure requires a long table lock: This will cause a long master-slave delay ('480 seconds delay')

1.4.3 How to handle large tables in the database

Splitting a large table into multiple small tables

difficulty:

Selection of primary key for sharded tables Query and statistics of cross-partitioned data after sharding

1.5 Problems with large transactions (important*)*

1.5.1 What is a transaction?

1.5.2 ACID Properties of Transactions

1. Atomicity: All succeed, all rollbacks fail. Bank deposits and withdrawals.

2. Consistent: The total amount of the bank transfer remains unchanged.

3. Isolation:

Isolation level:

Uncommitted read (READ UNCOMMITED) is a dirty read. Two transactions are visible to each other. Committed read (READ COMMITED) complies with the basic concept of isolation. When a transaction is in progress, other committed transactions are visible to the transaction, that is, the data committed by other transactions can be obtained. Repeatable Read (REPEATABLE READ) The default isolation level of InnoDB. When a transaction is in progress, all other transactions are invisible to it, that is, the result obtained by multiple reads is the same! SERIALIZABLE locks each row of data read, which can cause a large number of lock timeouts and lock requisitions. It can be used with strict data consistency and no concurrency.

View the transaction isolation level of the system: show variables like'%iso%';

Start a new transaction: begin;

Submit a transaction: commit;

Modify the isolation level of the transaction: setsession tx_isolation='read-committed';

4. DURABILITY: From the database's perspective, the persistence is not possible if the disk is damaged.

The redolog mechanism ensures the consistency and persistence of transaction updates

1.5.3 Big Transactions

Transactions that take a long time to run and require a lot of data.

Risks: Too much locked data, long rollback time, long execution time.

Locking too much data will cause a lot of blocking and lock timeouts; rolling back will take a long time and the data will still be locked; if the execution time is long, it will cause master-slave delays, because the slave server will only start synchronization when the master server has completed all log writes, causing delays.

Solution:

Avoid processing too much data at one time by processing it in batches; remove unnecessary SELECT operations to ensure that only necessary write operations are included in the transaction. 2. What affects MySQL performance (very important)

2.1 Several aspects affecting performance

Server hardware. Server system (system parameter optimization). Storage engine. MyISAM: Does not support transactions or table-level locks. InnoDB: supports transactions, row-level locks, and transaction ACID. Database parameter configuration. Database structure design and SQL statements. (Key optimization)

2.2 MySQL Architecture

Divided into three layers: client->service layer->storage engine

MySQL is a plug-in storage engine, of which there are many types. As long as you implement an interface that conforms to the MySQL storage engine, you can develop your own storage engine! All cross-storage engine functions are implemented in the service layer. MySQL's storage engine is for tables, not for libraries. That is to say, different storage engines can be used in one database. But this is not recommended.

2.3 InnoDB Storage Engine

The default storage engine for MySQL 5.5 and later versions is InnoDB.

2.3.1 InnoDB uses tablespace for data storage.

show variables like 'innodb_file_per_table

If innodbfileper_table is ON, an independent tablespace will be created and the file will be tablename.ibd;

If innodbfileper_table is OFF, data is stored in the system's shared tablespace, and the file is ibdataX (X is an integer starting from 1);

.frm: It is a file generated at the server level, similar to the data dictionary at the server level, recording the table structure.

2.3.2 (MySQL 5.5 default) System tablespace and (MySQL 5.6 and later default) Independent tablespace

1.1 The system tablespace cannot simply shrink the file size, resulting in space waste and a large amount of disk fragmentation. 1.2 Independent tablespaces can shrink system files through the opize table command without restarting the server and without affecting normal access to the table. 2.1 If multiple tables are refreshed, they are actually done sequentially, which will cause an IO bottleneck. 2.2 Independent tablespaces can refresh data to multiple files at the same time.

It is strongly recommended to use a separate tablespace for Innodb, which makes optimization more convenient and controllable.

2.3.3 How to transfer tables in the system tablespace to independent tablespaces

1. Use mysqldump to export all database data (stored procedures, triggers, and scheduled tasks must be exported together) and you can do this on the slave server. 2. Stop the MYSQL server, modify the parameters (add innodbfileper_table to my.cnf), and delete Inoodb related files (you can rebuild the Data directory). 3. Restart MYSQL and rebuild the Innodb system tablespace. 4. Re-import the data.

Or Altertable can also be transferred, but the space occupied by the system tablespace cannot be reclaimed.

2.4 Features of the InnoDB storage engine

2.4.1 Feature 1: Transactional Storage Engine and Two Special Log Types: Redo Log and Undo Log

Innodb is a transactional storage engine. Fully supports the ACID characteristics of transactions. Supports two special log types required for transactions: RedoLog and UndoLog

Redo Log: Realize transaction persistence (committed transactions). Undo Log: Uncommitted transactions, independent of the tablespace, require random access, and can be stored on high-performance IO devices.

The Undo log records the value of a certain data before it is modified, and can be used to rollback when a transaction fails. The Redo log records the value of a certain data block after it is modified, and can be used to recover data updated by successful transactions that have not been written to the data file.

2.4.2 Feature 2: Support row-level locks

InnoDB supports row-level locking. Row-level locks can maximize concurrency support. Row-level locks are implemented by the storage engine layer.

2.5 What is a lock?

2.5.1 Lock

2.5.2 Lock Types

2.5.3 Lock Granularity

MySQL transaction support is not bound to the MySQL server itself, but is related to the storage engine.

Add table-level lock command to table_name: locktable table_name write; The write lock will block other users' 'read and write' operations on the table until the write lock is released: unlock tables;

The greater the lock overhead, the smaller the granularity, and the higher the concurrency. Table-level locks are usually implemented at the server level. Row-level locks are implemented at the storage engine layer. The locking mechanism of InnoDB is unknown to the server layer

2.5.4 Blocking and Deadlock

(1) Blockage is a queuing phenomenon caused by insufficient resources. (2) Deadlock occurs when two objects request a resource while already owning another resource, and the other resource happens to be held by both objects, causing the two objects to be unable to complete the operation and the resources they hold cannot be released.

2.6 How to choose the right storage engine

Reference conditions:

Transaction backup (Innobd free online backup) Crash recovery Storage engine specific features

Summary: Innodb is great.

Note: Try not to use mixed storage engines, for example, rollback will cause problems with online hot standby.

2.7 Configuration Parameters

2.7.1 Memory configuration related parameters

Determines the upper limit of memory that can be used.

The upper limit of memory usage cannot exceed the physical memory, otherwise it is easy to cause memory overflow; (For 32-bit operating systems, MySQL can only try memory below 3G.)

Determines the memory used by each MySQL connection individually.

sort_buffer_size #Defines the size of the sort buffer for each thread. MySQL will allocate memory for each buffer only when there is a query and a sort operation needs to be performed (directly allocate all the memory of this parameter) join_buffer_size #Defines the size of the connection buffer used by each thread. If a query is associated with multiple tables, MySQL will allocate a connection buffer for each table, resulting in multiple connection buffers for one query read_buffer_size #Defines the size of the read buffer pool allocated when a full table scan is performed on a MyISAM. MySQL will allocate memory for it when a query requires it, and it must be a multiple of 4k; read_rnd_buffer_size #Index buffer size. MySQL will allocate memory for it when a query requires it, and will only allocate the required size.

Note: The above four parameters are allocated for one thread. If there are 100 connections, then ×100 is required.

MySQL database instance:

①MySQL is a single-process multi-threaded (while Oracle is a multi-process), which means that the MySQL instance is a service process on the system, that is, a process;

②MySQL instance is composed of threads and memory, and the instance is actually used to operate the database file;

Generally, one instance operates one or more databases; in a cluster, multiple instances operate one or more databases.

How to allocate memory for the cache pool:

Innodb_buffer_pool_size defines the size of the buffer pool used by Innodb. It is very important for its performance and must be large enough. However, if it is too large, it will take more time to flush dirty pages from the buffer pool to disk when Innodb is closed.

Total memory - (memory required by each thread * number of connections) - system reserved memory

key_buffer_size defines the size of the cache pool used by MyISAM. Since data depends on the storage operating system cache, more memory space should be reserved for the operating system.

select sum(index_length) from information_schema.talbes where engine='myisam'

Note: Even if all the tables used for development are Innodb tables, memory must be reserved for MyISAM, because the tables used by the MySQL system are still MyISAM tables.

max_connections controls the maximum number of connections allowed, usually 2000 or more.

Do not use foreign key constraints to ensure data integrity.

2.8 Performance Optimization Order

From top to bottom: