Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article is the second article of the performance series. You can find the first articles link
“POSTGRESQL DATABASE PERFORMANCE – 1”
In the postgresql database, we can make the following settings to log long DML and DDL operations.
In the postgresql.conf file, we’ll activate the following parameters and restart the database, then we’ll observe how long the update takes.
1 2 3 4 5 6 7 8 9 | logging_collector = on log_directory = 'log' log_statement = 'all' - this parameter is all for logging both DDM and DML operations log_min_duration_statement = 1000 - this parameter is in milliseconds. We will log queries that longer than 1000 ms. log_line_prefix = '%m' |
After changing these parameters, the database must be restarted using the following commands. (If you are using PostgreSQL 10 :))
1 2 3 | systemctl stop postgresql-10.service systemctl start postgresql-10.service |
After that;
We will connect to the database and run the update command.
1 | su - postgres |
1 | psql |
1 2 | update experiment set surname = 'ylmz'; UPDATE 4194394 |
After this process, we can reach the following log when we open the log file in /var/lib/pgsql/10/data/log
1 2 3 | LOG: statement: update trial set surname = 'ylmz'; LOG: duration: 11767.525 ms |
As a result, a database process that longer than 1000 ms was captured in the database log.
