Shulou(Shulou.com)05/31 Report--

How to solve MySQL query slow and poor performance problems, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

What affects database query speed?

1.1 Four factors that affect database query speed

1.2 risk analysis

QPS: Queries Per Second means "query rate per second", which is the number of queries per second that a server can respond to. It is a measure of how much traffic a particular query server handles in a given time.

TPS: Short for TransactionsPerSecond, i.e. transactions per second. It is the unit of measurement for software test results. The client counts the elapsed time and number of completed transactions by starting the timer when it sends the request and ending it when it receives the server response.

Tips: It's best not to back up the database on the main repository and cancel such plans before a large event.

Inefficient sql: super-high QPS and TPS.

A lot of concurrency: The number of data connections is full (max_connection defaults to 100, and the number of connections is generally set to be larger).

Concurrent volume: The number of requests processed by the database server at the same time

High CPU utilization: CPU resource depletion occurs with downtime.

Disk IO: A planned task that suddenly degrades disk IO performance and consumes a lot of disk performance. Solution: Faster disk devices, adjust scheduled tasks, do disk maintenance well.

1.3 Network card traffic: how to avoid unable to connect to the database situation

Reduce the number of slaves (slaves copy logs from master)

Cache hierarchically (avoid massive cache failures at the front)

Avoid using select * for queries

Separate service network and server network

1.4 Problems caused by large tables (important)

1.4.1 Features of large tables

Record lines are huge, and a single table exceeds ten million

The table data file is huge, more than 10 G

1.4.2 Harm of large meters

1. Slow query: It is difficult to filter out the required data in a short time

Query word discrimination is low-> to filter out large amounts of data in tables, some of which will generate a large number of disk io -> reduce disk efficiency

2. Impact on DDL:

Indexing takes a long time:

MySQL -v=5.5 Indexing causes master-slave latency (mysql indexes on groups first, then libraries)

Changing the table structure requires a long lock table: causes a long master-slave delay ('480 second delay')

1.4.3 How to handle large tables on a database

To divide a large table into small tables.

Difficulties:

Selection of sub-table primary key

Query and Statistics of Cross-partition Data after Tabulation

1.5 Big issues bring problems (important)

1.5.1 What is Business?

1.5.2 ACID Attributes of Transactions

1. Atomicity: All successful, all rollback failed. Bank deposits and withdrawals.

2. Consistency: The total amount of the bank transfer remains the same.

3. Isolation:

Isolation class:

READ UNCOMMITTED Dirty reads, two transactions visible to each other;

READ COMMITTED conforms to the basic concept of isolation: while a transaction is in progress, other committed things are visible to the transaction, that is, data committed by other transactions can be obtained.

REPEATABLE READ Default isolation level for InnoDB. When a transaction is in progress, all other transactions are invisible to it, that is, multiple reads are performed, and the result is the same!

SERIALIZABLE locks every row of data read, causing a large number of lock timeouts and lock requisitions, strict data consistency and no concurrency are available.

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 thing: set session tx_isolation='read-committed';

4. DURABILITY: Persistence from a database perspective, not disk corruption

redo log mechanism ensures consistency and persistence of transaction updates

1.5.3 Major issues

Transactions with long running time and more operation data;

Risk: too much locked data, long rollback time, long execution time.

Locking too much data, causing massive blocking and lock timeouts;

Rollback takes longer and the data remains locked;

If the execution time is long, it will cause a master-slave delay, because the slave server will not start synchronizing until the master server has finished writing to the log, causing a delay.

Solution:

Avoid processing too much data at once, and can be processed in batches;

Remove unnecessary SELECT operations and ensure that only necessary writes are included in the transaction.

What affects MySQL performance (very important)

2.1 Several aspects affecting performance

Server hardware.

Server system (system parameter optimization).

Storage engine.

MyISAM: No support for transactions, table-level locking.

InnoDB: transaction support, row-level locking support, transaction ACID.

Database parameter configuration.

Database structure design and SQL statements. (Focus on optimization)

2.2 MySQL architecture

Three tiers: Client-> Service Tier-> Storage Engine

MySQL is a plug-in storage engine, and there are many kinds of storage engines. As long as the implementation of mysql storage engine interface, you can develop your own storage engine!

All cross-storage engine functionality is implemented at the service tier.

MySQL's storage engine is for tables, not libraries. This means that different storage engines can be used within a database. However, this is not recommended.

2.3 InnoDB storage engine

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

InnoDB uses tablespaces for data storage.

show variables like 'innodb_file_per_table

If innodb_file_per_table is ON, a separate table space is created, and the file is tablename.ibd;

if innodb_file_per_table is OFF stores data into the system's shared table space, the file is ibdataX(X is an integer starting with 1);

.frm: A file generated at the server level, similar to a data dictionary at the server level, records the table structure.

2.3.2 (MySQL 5.5 default) System Tablespace vs.(MySQL 5.6 default) Independent Tablespace

1.1 System tablespaces cannot simply shrink file sizes, resulting in wasted space and large amounts of disk fragmentation.

1.2 Independent tablespaces can shrink system files through optimeze tables without restarting the server and without affecting normal access to tables.

2.1 If multiple tables are refreshed, in fact sequentially, an IO bottleneck occurs.

2.2 Independent tablespaces can refresh data to multiple files simultaneously.

Strongly established against Innodb using separate tablespaces, optimizing what's more convenient and controllable.

2.3.3 Method of transferring tables from system tablespaces to independent tablespaces

Export all database data (stored procedures, triggers, scheduled tasks all together) using mysqldump can be operated on the slave server.

2. Stop MYsql server, modify parameters (my.cnf to add innodb_file_per_table), and delete Inoodb related files (you can rebuild Data directory).

Restart MYSQL and rebuild the Innodb system table space.

4. Reimport data.

Or Alter tables can also be migrated, but the space occupied in the system table space cannot be reclaimed.

2.4 Features of 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 ACID features of transactions.

Two special log types are required to support transactions: Redo Log and Undo Log.

Redo Log: Enforces transaction persistence (committed transactions).

Undo Log: Uncommitted transactions, independent of tablespaces, require random access, and can be stored on high-performance io devices.

Undo logs record the value of a certain data before it is modified, which can be used to rollback when the transaction fails;Redo logs record the modified value of a certain data block, which can be used to recover the updated data of a successful transaction that has not been written to the data file.

2.4.2 Feature 2: Support row level locking

InnoDB supports row-level locking.

Row-level locking allows concurrency to be maximized.

Row-level locking is implemented by the storage engine layer.

2.5 What's a lock?

2.5.1 Lock

2.5.2 Lock types

2.5.3 Granularity of locks

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

Add a table-level lock to table_name Command: lock table_name write; write lock will block other users from 'reading and writing' to the table until the write lock is released: unlock tables;

The higher the cost of locking, the smaller the granularity, and the higher the concurrency.

Table-level locks are usually implemented at the server level.

Row-level locks are implemented by the storage engine layer. innodb lock mechanism, server layer is unknown

2.5.4 Blocking and deadlock

(1)Congestion is a queuing phenomenon caused by insufficient resources.

(2)A deadlock occurs when two objects request another resource when they own one, and the other resource happens to be held by the two objects, resulting in the two objects not being able to complete the operation, and the resources held cannot be released.

2.6 How to choose the right storage engine

Reference conditions:

Affairs

Backup (Innobd Free Online Backup)

crash recovery

Storage engine specific features

Summary:Innodb is great.

Note: Try not to use hybrid storage engines, such as rollback problems online hot standby problems.

2.7 configuration parameters

2.7.1 Memory Configuration Related Parameters

Determines the maximum amount of memory that can be used.

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

Determine how much memory MySQL uses individually for each connection.

sort_buffer_size #defines the size of the sort buffer for each thread. MySQL allocates memory for each buffer only when there is a query and a sort operation is needed.(Allocate all memory of this parameter directly);join_buffer_size #defines the size of the join buffer used by each thread. If a query is associated with multiple tables, MySQL will allocate a join buffer for each table, resulting in multiple join buffers generated by a query; read_buffer_size #defines the size of the read buffer pool allocated when scanning a MyISAM table. MySQL allocates memory for it when it is needed for queries, which must be a multiple of 4k;read_rnd_buffer_size #index buffer size. MySQL allocates memory for it when it is needed for queries, and only allocates the required size.

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

MySQL database example:

MySQL is a single-process multi-thread (while oracle is a multi-process), that is to say, MySQL instance is a service process on the system, that is, a process;

MySQL instances are composed of threads and memory, and instances are really used to operate database files;

Typically, one instance operates on one or more databases; in the case of clusters, multiple instances operate on one or more databases.

How to allocate memory for cache pools:

Innodb_buffer_pool_size defines the size of the cache pool used by Innodb, which is very important for its performance. It must be large enough, but when 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 per thread * number of connections)-system reserved memory

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

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

Note: Reserve memory for MyISAM even if all tables used by development are Innodb tables, because the tables used by MySQL systems are still MyISAM tables.

max_connections controls the number of *** connections allowed, generally greater than 2000.

Do not use foreign key constraints to guarantee data integrity.

2.8 performance optimization sequence

From top to bottom:

After reading the above, do you know how to solve MySQL query slow and poor performance problems? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.