Examples to explain database optimization

Searching for database optimization on the Internet basically optimizes from the SQL level, and rarely mentions the instance optimization of the database itself. Even if there is, it is all based on instance optimization of a specific database. This article covers instance optimization of all mainstream databases currently on the market (Oralce, MySQL, POSTGRES, Dameng). According to the configuration of the article, you can use 80% or more of your database performance. above.

Database Optimization Methodology

This part is theoretical knowledge. Students who are not interested can jump directly to the parameter configuration part.

Database optimization goals

Recommended "mysql video tutorial"

According to different roles, 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 Dimension):

Make full use of the physical resources of the database server

Reduce the CPU usage of the database server

Reduce database server IO usage

Reduce database server memory usage

##Indicators

1. The average SQL response time becomes shorter

a. Before optimization: The average database response time is 500ms

b. Optimization target: The average database response time is 200ms

2. Database server The CPU usage decreases

a. Before optimization: CPU usage is 70% during database peak period

b. Optimization target: CPU usage is 50% during database peak period

3 . Database server IO usage becomes low

a. Before optimization: database IO WAIT is 30%

b. Optimization target: database IO WAIT is less than 10%

Database optimization misunderstandings

There may be the following misunderstandings when optimizing databases:

1. Before optimizing, you must have a deep understanding of the internal principles of the database

Optimization has "routines", and you can also complete database optimization well by following these "routines"

2. Continuously adjusting database parameters can ultimately achieve optimization

Sometimes design No matter how unreasonable the parameters are adjusted, it will not work

3. Continuously adjusting the operating system parameters can ultimately achieve optimization

Same as above

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

On the contrary, it has a lot to do with application development

5. It is necessary to separate reading and writing, and it must be divided into databases and tables

The amount of data Only when the level reaches a certain ratio is it necessary to separate reading and writing, and divide the tables into separate databases. Otherwise, it will only increase the complexity. Generally speaking, the single table size of Oracle can reach 100 million, and that of MySQL can reach 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 system information during the problem and archive it. Develop optimization goals based on current system problem performance and communicate with customers to reach agreement on goals; analyze system problems through a series of tools and formulate optimization plans. After the plan review is completed, each person in charge will implement it. If the optimization goal is achieved, an optimization report will be compiled; otherwise, the optimization plan will need to be re-formulated.

Database instance optimization

Database instance optimization follows three mantras:

The log cannot be small, the cache must be large enough, and the connection must be sufficient.

After the database transaction is submitted, the modifications to the data page by the transaction need to be flushed (fsync) to the disk to ensure the durability of the data. This disk flush is a random write with low performance. If the disk is flushed every time a transaction is submitted, it will greatly affect the performance of the database. The database will adopt the following two optimization techniques in the architecture design:

a. First write the transaction to the log Examples to explain database optimization RedoLog (WAL), and optimize random writing into sequential writing

b. Add one The layer cache structure Buffer optimizes each write into a sequential write

So logs and caches are particularly important for database instances. If there are not enough connections, the database will directly throw an exception and the system will be inaccessible.

Database parameter optimization

Mainstream database architectures all have the following in common:

Data cache

SQL parsing area

Sort memory

REDO and UNDO

Lock, LATCH, MUTEX

Monitoring and connection

File reading and writing performance

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

ORACLE

##Data cacheSGA_TAGET, MEMORY_TARGETPhysical memory 70-80%The bigger the betterData CacheDB_CACHE_SIZEPhysical memory 70-80%The bigger the betterSQL parsingSHARED_POOL_SIZE 4-16GIt is not recommended to set it too largeMonitoring and connectionPROCESSES, SESSIONS, OPEN_CURSORSSet according to business requirementsGenerally 120% of the estimated number of business connectionsOthersSESSION_CACHED_CURSORS is greater than 200 Soft Analysis

Parameter classification	Parameter name	Parameter value	Remarks

MYSQL（INNODB）

Parameters CategoryParameter nameParameter valueRemarksData cache INNODB_BUFFER_POOL_SIZEPhysical memory 50-80%Generally speaking, the larger the better the performanceLog relatedInnodb_log_buffer_size16-32MAdjust according to the operating conditionsLog relatedsync_binlog1, 100, 01 Best securityMonitoring and connectionmax_connectionsAdjust according to business conditionscan be pre-set Leave part of the valueFile reading and writing performanceinnodb_flush_log_at_trx_commit2Security and performance compromise considerationsOtherswait_timeout, interactive_timeout28800Avoid scheduled application connection interruptions

POSTGRES

Parameter classificationParameter nameParameter valueRemarksData cacheSHARED_BUFFERSPhysical memory 10-25%Data cacheCACHE_BUFFER_SIZEPhysical memory 50-60% Log relatedwal_buffer8-64MIt is not recommended to set it too large or too smallMonitoring and connectionmax_connectionsAdjust according to business conditionsGenerally 120% of the estimated number of business connections##OthersOtherOthers

maintenance_work_mem	512M or larger
work_mem	8-16M	The original configuration 1M is too small
checkpoint_segments	32 or larger

Dameng Database

Parameter classificationData cacheData cacheData cache Monitoring and connection

Parameter name	Parameter value	Remarks
MEMROY_TARGET, MEMROY_POOL	Physical memory 90%
BUFFER	Physical memory 60%	Data cache
MAX_BUFFER	Physical memory 70%	Maximum data cache
max_sessions	Set according to business requirements	Generally 120% of the estimated number of connections for the business

Summary

There are too many ways to optimize the database, including replacing disk arrays and upgrading hardware, rewriting SQL scripts to add indexes, adjusting database parameters to optimize performance, and even adjusting the database. architecture. This article optimizes the parameters of the database itself. By adjusting the parameters in the above tables, you can basically achieve 80% of the best performance of the database.

This article comes from the php Chinese website,

mysql tutorial

column, welcome to learn!

The above is the detailed content of Examples to explain database optimization. For more information, please follow other related articles on the PHP Chinese website!