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

In this issue, the editor will bring you about how to optimize the database, analyze and describe it from a professional point of view. I hope you can get something after reading this article.

1. Database optimization is a wide range, involving many things, and the specific optimization process of each specific database is also different. Because a large part of the optimization will eventually have to deal with the details of the specific database system, it is impossible to elaborate on all the databases here. If so, I am afraid that I will not be able to write several books. We can only have a discussion on some of the more general and frequently used things.

2. in general, database optimization refers to the optimization of query performance (although strictly speaking, it should not be like this), so that the database can respond to queries as quickly as possible.

3. Only for the database system itself, the factors that affect the query performance theoretically include database parameter settings (in fact, parameters control the memory, iUnip o, cache, backup and other administrative things of the database system), indexes, partitions, sql statements. Database parameter setting itself is a very complex thing, partition is mainly aimed at a large amount of data, it disperses the distribution of data files, reduces disk competition, and improves efficiency.

Database optimization goal

Depending on the role, database optimization is divided into the following goals:

Business perspective (key users):

Reduce user page response time

Database perspective (development):

Reduce database SQL response time

Database server perspective (operation and maintenance):

Make full use of database server physical resources

Reduce database server CPU usage

Reduce database server IO usage

Reduce database server memory usage

Index

1. The average response time of SQL becomes shorter

a. Before optimization: average database response time 500ms

b. Optimization goal: average database response time 200ms

two。 Database server CPU occupancy is reduced

a. Before optimization: database peak CPU utilization rate is 70%

b. Optimization goal: database peak CPU utilization 50%

3. Database server IO usage is low

a. Before optimization: database IO WAIT is 30%

b. Optimization goal: database IO WAIT less than 10%

Misunderstanding of database optimization

There may be the following misunderstandings when doing database optimization:

1. Be sure to have an in-depth understanding of the internal principles of the database before optimization

There are "routines" in optimization, and you can also complete database optimization well according to these "tricks".

two。 The optimization can be realized by constantly adjusting the database parameters.

Sometimes the design is unreasonable and no matter how to adjust the parameters.

3. The optimization can be realized by constantly adjusting the operating system parameters.

Same as above

4. Database performance is determined by application and database architecture, and has little to do with application development.

On the contrary, application development is very important.

5. Must do read-write separation, must make sub-library sub-table.

Only when the data reaches a certain proportion, it is necessary to do read-write separation, sub-table and sub-database, otherwise it will only increase the complexity. Generally speaking, the single table order of magnitude of Oracle can reach 100 million, MySQL to 10 million ~ 20 million.

Database optimization process

The complete database optimization process is as follows:

First of all, you need to understand the optimization problem as much as possible, collect the system information during the problem and archive it. Formulate optimization objectives according to the performance of the current system problems and reach agreement with customers; analyze system problems through a series of tools, formulate optimization plans, and implement them by the responsible personnel after the program review is completed. If you achieve the optimization goal, prepare the optimization report, otherwise you need to reformulate the optimization plan.

Database instance optimization

Database instance optimization follows three rules: the log cannot be small, the cache is large enough, and the connection should be sufficient.

After the database transaction is committed, the modification of the data page by the transaction needs to be fsync to the disk to ensure the persistence of the data. This flush disk, is a random write, the performance is low, if every transaction commit has to be brushed disk, it will greatly affect the performance of the database. The following two optimization techniques are adopted in the architecture design of the database:

a. First, write the transaction to the log file RedoLog (WAL), and optimize the random write to write sequentially.

b. Add a layer of cache structure Buffer to optimize each write to write sequentially

So logging and caching are especially important for database instances. If the connection is not enough, the database will directly throw an exception and the system cannot access it.

Database parameter optimization

Mainstream database architectures have the following in common:

Data caching

SQL analysis area

Sort memory

REDO and UNDO

Lock, LATCH, MUTEX

Monitoring and connection

File read and write performance

Next, we adjust the parameters according to different databases to achieve the best performance of the database.

ORACLE

Parameter classification parameter name parameter value remarks data cache SGA_TAGET, MEMORY_TARGET physical memory 70-80% data cache DB_CACHE_SIZE physical memory 70-80% better SQL parsing SHARED_POOL_SIZE4-16G it is not recommended to set excessive monitoring and connections PROCESSES, SESSIONS, OPEN_CURSORS according to business requirements generally 120% of the estimated number of connections other SESSION_CACHED_CURSORS greater than 200 soft parsing

Dameng database

Parameter classification parameter name parameter value remarks data cache MEMROY_TARGET, MEMROY_POOL physical memory 90%

Data cache BUFFER physical memory 60% data cache MAX_BUFFER physical memory 70% most big data cache monitoring and connection max_sessions is generally 120% of the estimated number of business connections according to business requirements

MYSQL (INNODB)

Parameter classification parameter name parameter value remarks data cache INNODB_BUFFER_POOL_SIZE physical memory 50-80% generally the larger the performance, the better log-related Innodb_log_buffer_size16-32m adjust log-related sync_binlog1, 100m, Security best monitoring and connection max_connections adjust according to business conditions can reserve some values file read and write performance innodb_flush_log_at_trx_commit2 security and performance compromise consider other wait_timeout Interactive_timeout28800 avoids timing interruption of application connection

POSTGRES

Parameter classification parameter name parameter value remarks data cache SHARED_BUFFERS physical memory 10-25%

Data cache CACHE_BUFFER_SIZE physical memory 50-60%

Log-related wal_buffer8-64m does not recommend setting too large or too small snooping and connection max_connections to adjust to 120% of the estimated number of business connections based on business conditions, other maintenance_work_mem512M or greater

Other work_mem8-16m original configuration 1m is too small, other checkpoint_segments32 or larger

Summary

There are too many ways to optimize the database, including changing disk arrays to upgrade hardware, rewriting SQL scripts to add indexes, adjusting database parameters to optimize performance, and even adjusting database architecture. In this paper, we tune the parameters of the database itself, and we can basically reach 80% of the best performance of the database according to the parameters in the above tables.

The above is the editor for you to share how to optimize the database, if you also have similar doubts, you might as well refer to the above method to try. If you want to know more about it, please follow the industry information.

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.