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

This article mainly gives you a brief description of the main factors that affect the performance of MySQL database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring some practical help to you what are the main factors that affect the performance of MySQL database.

Factors that affect database performance:

A lot of sql performance is caused by slow queries

In the current version of mysql, multi-cpu concurrent operations are not supported, which means that only one cpu can be used in a sql. Commonly used qps and tps to measure cpu processing efficiency

Qps/tps: number of queries processed per second

Max_connections can be set larger according to the situation. If this number is actually exceeded, the CVM to be used will not be able to connect to the database, and a 500 error will occur.

The problems caused by the big watch:

(generally, tables that meet the above two items will have a performance impact)

Large tables will produce slow queries, making it difficult for the database to filter out the required data within a certain period of time.

However, sub-database and sub-table not only consume a lot of manpower and material resources, but also take the risk of affecting the back-end business, which may not be suitable for all companies. You can also deal with large tables in another way:

Archiving of large table historical data (minimizing the impact on front and back end business) (such as historical orders)

Difficulties:

Selection of archiving time points

How to perform archiving operations (for large tables, the master-slave delay is light, or a large number of blocking affects business access)

The problems caused by big business:

Characteristics of the transaction:

Atomicity: the smallest indivisible unit that either succeeds or fails.

Consistency: transition from one consistency state to another, where the integrity of the data in the data is not compromised before and after the transaction begins (for example, the total amount before and after the transfer is the same)

Isolation: requires one transaction to modify the data in the database and is not visible to other transactions until the commit is completed

Persistence: once a transaction commits, all its changes are permanently saved to the database. Even if the system crashes, the modified data that has been submitted will not be lost.

What is the big deal:

Definition: transactions that take a long time to run and operate more data

Risk:

Locking too much data, causing a lot of blocking and lock timeouts

Rollback takes a long time (it will still be locked during the rollback)

If the execution time is long, it is easy to cause master-slave delay (if the delay is 1 hour, this will.)

How to handle big business:

1. Avoid dealing with too much data at once

two。 Remove unnecessary select operations in a transaction

Centos parameter optimization:

(the above configuration can be adjusted accordingly)

(the above will determine the recycling speed of tcp connections)

(the above will determine the default value and maximum value of the tcp connection, receive, and send buffer size. Should be adjusted larger accordingly)

(the above parameters are used to reduce the amount of tcp system resources occupied by failed connections and speed up the efficiency of resource recovery. It should be smaller)

Mysql parameters:

.

Benchmark:

The benchmark test is different from the stress test. The stress test is usually real data. The benchmark data is generated by the test tool, which is just a simplified stress test.

Shell script to write test program

Run and save test results

Icon display

Test tools:

Ab 、 mysqlslap 、 sysbench.

Appropriate anti-normal form and appropriate increase of redundancy to improve query efficiency

(those that can belong to the same index in the same table cannot be put into the same index in different tables.)

(anti-normalization requires multiple changes when modifying data.)

Selection of data types:

Note: length is characters, not bytes

(in order to optimize the query more effectively, mysql uses a fixed width for strings in memory, especially when using an implicit memory zero-time table, if the varchar width is defined longer, more memory will be consumed.)

(date is reserved only to the date section, not minutes and seconds. Such as storing birthdays)

(mysql provides enough time storage types, do not use string types to store date and time data.)

Mysql replication:

But the database is different!

The replication of mysql is asynchronous. At the same time, the data of the standby database and the primary database are different, and the delay between the primary database and the standby database cannot be eliminated.

(replication is not a substitute for backup. Replication cannot recover the changed data deleted by the erroneous operation)

All the records in the binlog log have been executed successfully, and the rollback and unsuccessful transactions due to errors will not be recorded.

The copy of the table, to be continued.

Index optimization:

Overriding an index can improve query performance because the size of the index is much smaller than the size of the row. If the index itself is large, there is no need to overwrite the index, especially for select * queries, it is impossible to have an index covering all columns. So you can't use too many columns in the query)

Override index example:

(it can be obtained through the index, using an overlay index)

(using where states that the requested data cannot be obtained from the index, that is, the overlay index cannot be used. Instead, the index data must be put in memory first, and then filtered under where conditions.)

(with using index, override query is used)

Examples of index sort optimization:

(the above is sorted by index. The table is the innodb engine. For the same data structure, if it is a myisam engine, extra will display using filesort)

(neither myisam nor innodb uses using filesort.)

(using filesort is used here! The rules of index ascending and descending order must be consistent with the order by ascending and descending order rules, otherwise index column sorting cannot be used)

(increased concurrency, improved performance)

(duplicate indexes are not necessary at all. Redundant indexes are sometimes available)

Sql Optimization:

Get the sql with performance problems:

Set slow query:

(long_query_time unit second)

However, slow queries are unlikely to look at them one by one, with the help of tools:

Get sql with performance problems in real time:

To query a sql:

How to determine the time spent at each stage of query processing:

Optimization of a specific sql:

(if there is a lot of data in the table, the count () query will be slow, so you can create another table for summary:)

You can count count (*) at some time in the early morning of every day and write it to the summary table. If you want to query today, union all the data in the summary table and today's data.

What are the main factors affecting the performance of MySQL database to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.