Shulou(Shulou.com)06/01 Report--

This article mainly introduces several factors that affect database query speed and MySQL performance. I hope I can supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in industry information.

I. what affects the database query speed 1.1 four factors that affect the database query speed

1.2 risk analysis

QPS:Queries Per Second means "query rate per second". It is the corresponding number of queries per second for a CVM. It is a measure of the traffic handled by a particular query server within a specified period of time.

TPS: is an acronym for TransactionsPerSecond, that is, transactions per second. It is the unit of measurement of software test results. The client starts the timing when sending the request and ends the timing after receiving the response from the server to calculate the time used and the number of transactions completed.

Tips: it is best not to back up the database on the main library and cancel such a plan before a large event.

Inefficient sql: ultra-high QPS and TPS. A lot of concurrency: the number of data connections is full (max_connection defaults to 100, which is generally set to a larger number). Concurrency: ultra-high CPU utilization of the number of requests processed by the database server at the same time: downtime occurs when CPU resources are exhausted. Disk IO: a scheduled task that suddenly degrades disk IO performance and consumes a lot of disk performance. Solution: faster disk devices, adjust scheduled tasks, and do a good job of disk maintenance. 1.3Network card traffic: how to avoid being unable to connect to the database to reduce the number of slave servers (the slave server will copy the logs from the master server) to perform hierarchical caching (to avoid a large number of cache failures in the front end) to avoid using select * to query and separate the problems caused by the business network and server network 1.4 large tables (important) 1.4.1 the characteristics of large tables record rows are huge, single table tens of millions of table data files are huge Hazards of more than 10 G1.4.2 large tables

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

Low differentiation of query words-> to filter out a part of the data in a table with a large amount of data will result in a large number of disks io-> reduce disk efficiency

two。 Impact on DDL:

Indexing takes a long time:

MySQL-vault 5.5 indexing causes master-slave delays (mysql builds the index, which is executed first on the group, then on the library)

Modifying the table structure requires a long time to lock the table: it will cause a long master-slave delay ('480 second delay')

1.4.3 how to deal with large tables on the database

To divide a large table into smaller tables.

Difficulties:

Selection of primary key of sub-table query and statistics of cross-partition data after sub-table (important) 1.5.1 what is a transaction

1.5.2 ACID attribute of the transaction

1. Atomicity: all succeeded and all rollback failed. Deposit and withdraw money from the bank.

2. Consistent: the total amount transferred by the bank remains unchanged.

3. Isolation (isolation):

Isolation level:

Uncommitted read (READ UNCOMMITED) dirty read, the two transactions are visible to each other; committed read (READ COMMITED) conforms to the basic concept of isolation, when a transaction is going on, other committed things are visible to that transaction, that is, you can get the data committed by other transactions. The default isolation level of the REPEATABLE READ InnoDB. When a transaction is in progress, all other transactions are not visible to it, that is, multiple reads are performed, and the result is the same! Serializable (SERIALIZABLE) locks on every row of data read, resulting in 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

Commit a transaction: commit

Modify the isolation level of things: set session tx_isolation='read-committed'

4. DURABILITY: from the point of view of database persistence, disk corruption is not good.

Redo log mechanism ensures the consistency and persistence of transaction updates.

1.5.3 Major transactions

Transactions that take a long time to run and operate more data

Risks: locking too much data, long rollback time, and long execution time.

Locking too much data causes a lot of blocking and lock timeouts; rollback takes a long time, and the data will still be locked; if the execution time is long, it will cause master-slave delay, because only when the master server has finished writing to the log, will the slave server start synchronization, causing a delay.

The solution is as follows:

To avoid processing too much data at once, you can process it in batches; remove unnecessary SELECT operations to ensure that there are only necessary writes in the transaction. Second, what affects MySQL performance (very important) 2.1 several aspects of server hardware that affect performance. Server system (system parameter optimization). Storage engine.

MyISAM: transactions and table-level locks are not supported.

InnoDB: support transactions, support row-level locks, transaction ACID. Database parameter configuration. Database structure design and SQL statement. (focus on optimization) 2.2 MySQL architecture

It is divided into three layers: client-> Service layer-> Storage engine

MySQL is a plug-in storage engine, in which there are many kinds of storage engines. As long as you implement the interface that meets the mysql storage engine, you can develop your own storage engine! All cross-storage engine functions are implemented at the service layer. MySQL's storage engine is for tables, not libraries. This means that different storage engines can be used in a database. But this is not recommended. 2.3 InnoDB Storage engine

The default storage engine for MySQL5.5 and later versions is InnoDB.

2.3.1 InnoDB uses tablespaces for data storage.

Show variables like 'innodb_file_per_table

If innodb_file_per_table is ON, a separate tablespace will be established with the file tablename.ibd

If innodb_file_per_table is OFF to store data to the system's shared tablespace, the file is ibdataX (X is an integer starting at 1)

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

2.3.2 (MySQL5.5 default) system tablespaces and (MySQL5.6 and later default) independent tablespaces

1.1 the system tablespace cannot simply shrink the file size, resulting in a waste of space and a large number of disk fragments.

1.2 stand-alone tablespaces can shrink system files through optimeze table without the need to restart the server and will not affect normal access to the table.

2.1 if multiple tables are refreshed, it is actually done sequentially, resulting in IO bottlenecks.

2.2 independent tablespaces can refresh data to multiple files at the same time.

Strongly build independent tablespaces for Innodb, optimize what's more convenient and controllable.

2.3.3 the method of transferring tables from system tablespaces to independent tablespaces

Using mysqldump to export all database data (stored procedures, triggers, and scheduled tasks are all exported together) can be operated on the slave server.

Stop the MYsql server, modify the parameters (my.cnf joins innodb_file_per_table), and delete Inoodb-related files (you can rebuild the Data directory).

Restart MYSQL and rebuild the Innodb system tablespace.

Re-import data.

Or Alter table can also transfer, but can not reclaim the space occupied in the system tablespace.

2.4 Features of InnoDB storage engine 2.4.1 feature 1: transactional storage engine and two special log types: Redo Log and Undo LogInnodb are a transactional storage engine. The ACID feature of transactions is fully supported. There are two special log types required to support the transaction: Redo Log and Undo Log

Redo Log: enables transaction persistence (committed transactions).

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

The Undo log records the value before the data is modified, which can be used to record the modified value of a data block when the transaction fails, and can be used to recover the data that has been successfully updated by the transaction that has not been written to the data file.

2.4.2 feature 2: support row-level locks InnoDB supports row-level locks. Row-level locks can support concurrency as much as possible. Row-level locking is implemented by the storage engine layer. 2.5 what is a lock 2.5.1 lock

2.5.2 Lock type

2.5.3 granularity of locks

MySQL's transaction support is not tied to the MySQL server itself, but is related to the storage engine

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

The greater the overhead of the lock, the smaller the granularity, and the higher the degree of concurrency. Table-level locks are usually implemented at the server layer. Row-level locks are implemented at the storage engine layer. Innodb lock mechanism, the server layer is not aware of 2.5.4 blocking and deadlock

The main results are as follows: (1) blocking is a queuing phenomenon caused by insufficient resources.

(2) deadlock is due to the fact that two objects apply for another resource when they have one resource, and the other resource happens to be held by these two objects, so that the two objects cannot complete the operation and the held resources cannot be released.

2.6 how to choose the right storage engine

Reference conditions:

Transaction backup (Innobd free online backup) unique features of the crash recovery storage engine

Conclusion: * Innodb is a good method.

Note: * * try not to use a hybrid storage engine, for example, a rollback may cause problems with online hot backup.

2.7 configuration parameters 2.7.1 memory configuration related parameters

Determine the maximum amount of memory you can use.

The upper limit of memory usage 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 the memory used separately for each connection to the MySQL.

Sort_buffer_size # defines the size of the sorting cache for each thread. MySQL allocates memory for each buffer only when there is a query and needs to be sorted (all memory for this parameter is allocated directly) Join_buffer_size # defines the size of the connection buffer used by each thread. If a query is associated with multiple tables, MySQL allocates a join buffer for each table, resulting in multiple join buffers for a query; read_buffer_size # defines the size of the read buffer pool allocated when a full table scan of a MyISAM is performed, and MySQL allocates memory to it when a query is needed, which must be a multiple of 4k The size of the read_rnd_buffer_size # index buffer. MySQL allocates memory to it when it needs a query, and only allocates the required size.

Note: the above four parameters are assigned to a thread, and if there are 100 connections, then × 100 is required.

MySQL database example:

① MySQL is single-process multithreaded (while oracle is multi-process), which means that a MySQL instance behaves as a service process on the system, that is, a process

② MySQL instances are composed of threads and memory, and the instances are really used to manipulate database files.

In general, an instance operates on one or more databases; in a cluster case, multiple instances operate on one or more databases.

How to allocate memory for a cache pool:

Innodb_buffer_pool_size, which defines the size of the cache pool used by Innodb, is very important for its performance and must be large enough, but when it is too large, it takes more time to flush dirty pages from the buffer pool to disk when Innodb is shut down.

Total memory-(memory required per thread * connections)-memory reserved by the system

Key_buffer_size, which defines the size of the cache pool used by MyISAM. Because the data is dependent 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 by the development are Innodb tables, reserve memory 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 the integrity of the data.

Read the above about the database query speed, MySQL performance of several factors, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.