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

How to start to optimize the database, in view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

The tuning of database can be considered from the following aspects, such as hardware configuration, network configuration, database configuration, SQL writing and interpretation, index maintenance and so on. To start, the following is only the database configuration and SQL rewrite related tuning explanation.

Generally speaking, adjusting database performance requires systematic knowledge, for example, you need to know whether the object served by the database system is an online analysis system or an online transaction system, which has different requirements for the IO CPU provided by the hardware system, such as how much CPU you want, how much RAID is set, how much memory, and how to set up the database, and so on. Of course, you can use PGBANCH to test the TPS of the database.

In the second part, after the operation of the database, at the beginning, you need to observe and monitor the system. For example, you can use pgbadger monitoring tools to monitor the system as a whole, or powa and pg_stat_statements can find problems in the database, find bottlenecks and slow queries and other information.

The first point of analysis is the analysis of slow queries. A slower and worse SQL needs to be rewritten. Otherwise, missing indexes should be created, database configurations should be reset, physical structures should be reconstructed, and so on.

Next, we will start debugging the configuration of the database.

The maximum number of connections to the database is very important in the database configuration. The default value of max_ connections setting is 100. if the setting value is low, you can appropriately increase the configuration value of work_mem.

In POSTGRESQL, connection pooling is usually used to improve system performance, reduce memory waste, and reduce the time consumed due to connection killing and rebuilding connection locks.

Two commonly used connection pooling softwar

Pgbouncer

Pgpool-II

Of course, connection pooling can also use connection-pooling or C3P0 like JAVA in the commercial grade.

The following settings are about memory settings

Shared buffer (shared_buffers): the default value of the shared buffer is

32 MB; however, it is recommended that it be set to about 25% of the total memory, but no more than 8 GB on Linux systems and no more than 512 MB on windows systems. Sometimes increasing shared_buffers to a very high value results in improved performance because the database can be fully cached in RAM. However, the disadvantage of increasing this value too much is that memory cannot be allocated for CPU operations such as sorting and hashing.

(I don't quite agree with the content of this paragraph in the book. in fact, this value should be set according to the situation of the application system, such as OLTP OLAP, and the statistics that have been running for a period of time. It is better to make a second adjustment.)

Working Memory (work_mem)

Working memory (work_mem): the default value is 1 MB;, which is important for cpu binding operations. The work_mem setting is associated with the number of connections, so the total number of RAM used is equal to the number of connections times work_mem. Working memory is used for sorting and hashing, so it affects queries that use sequential BY

Different, joint queries, etc.

For example, we can see the memory usage of this statement through explain analyze.

Extend:

The content of this memory in the book is gone. In fact, the memory adjustment of PG also involves

Temp_buffers = 8MB the setting of a temporary BUFFER for each database, where each SESSION occupies 64bytes as a placeholder when not in use

Maintenance_work_mem = 64MB

Specifies the maximum amount of memory used by maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 megabytes (64MB). Because the database session can only perform one of these operations at a time, and the installation usually does not run many operations at the same time, you can set this value to a much higher value than work_mem. Larger settings may improve the performance of emptying and recovering database dumps.

The memory used by autovacuum_work_mem in the most important autovacuum in the database. The default value of-1 means to replace this configuration with the configuration of maintenance_work_mem.

Shared_memory_type = mmap

Dynamic_shared_memory_type = posix

This is the answer to the question on how to start optimizing the database. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

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.