POSTGRESQL DATABASE PERFORMANCE – 1<\/a>”<\/p>\nIn the postgresql database, we can make the following settings to log long DML and DDL operations.<\/p>\n
In the postgresql.conf file, we’ll activate the following parameters and restart the database, then we’ll observe how long the update takes.<\/p>\n
logging_collector = on\r\n\r\nlog_directory = 'log'\r\n\r\nlog_statement = 'all' - this parameter is all for logging both DDM and DML operations\r\n\r\nlog_min_duration_statement = 1000 - this parameter is in milliseconds. We will log queries that longer than 1000 ms.\r\n\r\nlog_line_prefix = '%m'<\/pre>\n <\/p>\n
After changing these parameters,\u00a0the database must be restarted using the following commands. (If you are using PostgreSQL 10 :))<\/p>\n
systemctl stop postgresql-10.service\r\n\r\nsystemctl start postgresql-10.service<\/pre>\n <\/p>\n
After that;<\/p>\n
We will connect to the database and run the update command.<\/p>\n
su - postgres<\/pre>\npsql<\/pre>\nupdate experiment set surname = 'ylmz';\r\nUPDATE 4194394<\/pre>\n <\/p>\n
After this process, we can reach the following log when we open the log file in \/var\/lib\/pgsql\/10\/data\/log<\/p>\n
LOG: statement: update trial set surname = 'ylmz';\r\n\r\nLOG: duration: 11767.525 ms<\/pre>\n <\/p>\n
As a result, a database process that longer than 1000 ms was captured in the database log.<\/p>\n\n
<\/div>\n