Author: Song Shaohua
Optimization is very important to improve the performance of the database itself in the case of hardware and non system failures. Each type of database has its own different ways to track and optimize the database. These methods include not only the optimization of the parameters of the database system itself, but also the optimization of SQL statements. Among them, the optimization of SQL statements is the work that DBA s often need to contact. Therefore, slow SQL statements need to be turned off frequently to track and optimize them.
2, Database parametersSlow SQL tracking in PostgreSQL is analyzed and tracked by logging. Therefore, it is necessary to optimize SQL and start the log collection function. The database log collection function installed in RPM mode is on by default, and the database log collection function installed in source code compilation mode is off.
Start the log collection function
postgres=# show logging_collector ; logging_collector ------------------- off (1 row) postgres=# alter system set logging_collector = on; ALTER SYSTEM
Restart the database
[postgres@developer ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile waiting for server to shut down.... done server stopped waiting for server to start.... done server started
Review the parameters again
postgres=# show logging_collector ; logging_collector ------------------- on (1 row)
2.1 log_min_duration_statement
This parameter is set to the minimum execution time for a single SQL. Statements with execution time greater than this value will be recorded. This parameter records all SQL statements by default. If no unit is specified, the unit is ms by default.
If the recorded slow SQL statement is set to 2s, only the SQL statements executed for more than 2s are recorded in the log.
Examples are as follows:
postgres=# select count(*),p_date from tab_random_date group by p_date; count | p_date ---------+------------ 896 | 1993-01-12 384 | 2014-12-21 1152 | 1994-07-01 256 | 2015-08-07 512 | 2011-10-31 512 | 2004-10-16 512 | 2000-06-25 640 | 2001-07-30 384 | 2017-06-13 256 | 2000-10-19 640 | 2004-04-01 768 | 2006-01-13 768 | 2003-10-05 512 | 2003-06-22 896 | 2000-06-10 384 | 1997-06-23 512 | 2004-05-14 768 | 2012-03-10 384 | 2015-05-06 256 | 2018-10-14 128 | 2015-06-15 128 | 2017-02-18 1024 | 1993-05-05 256 | 2018-07-20 128 | 1998-05-08 640 | 1996-08-02 512 | 2014-11-19 256 | 2019-05-05 1536 | 1991-05-16 128 | 2007-08-05 640 | 1994-05-22 256 | 2013-01-06 1280 | 1996-05-12 512 | 1998-09-10 640 | 1999-02-08 384 | 2008-01-31 256 | 2016-08-28 512 | 2002-10-03 1792 | 1994-12-11 Time: 5006.112 ms (00:05.006)
If the execution time of the above SQL statement is 5s, the SQL statement will be recorded in the log. View log content
[21777] LOG: duration: 5005.937 ms statement: select count(*),p_date from tab_random_date group by p_date;
2.2 log_statement
This parameter is used to set the type of record statement. The allowed values are none, ddl, mod and all. The default value of this parameter is none. If it is changed to all, the database log format will change. If the query view is not specially configured, the slow log may not be parsed. Typically, if log is enabled_ min _ duration _ Statement parameter, then log min duration_ The statement parameter needs to be combined with log_statement parameter.
mod: records all DDL and database modification statements, such as INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM, etc.
DDL: only DDL statements are recorded. Such as CREATE, ALTER and DROP.
Examples are as follows:
LOG: duration: 7204.812 ms statement: insert into tab_random_date select * from tab_random_date; LOG: duration: 15749.567 ms statement: insert into tab_random_date select * from tab_random_date; LOG: duration: 2044.327 ms statement: select count(*) from tab_random_date;