How to Log Long-running Queries in PostgreSQL and MySQL Databases
You are experiencing slow performance navigating the repository or opening ad hoc views or domains. You enable audit logging but do not see any signifcant long running queries. The problem may be hibernate queries but they do not appear in the audit reports. How do you log the query times for these queries?
Resolution
Here’s the procedure to configure long-running query logging for MySQL and Postgres databases.
MySQL
How to start and stop the database
Macintosh OS X:
rons-mbp:apps rmiller$ sudo /usr/local/mysql/support-files/mysql.server stop rons-mbp:apps rmiller$ sudo /usr/local/mysql/support-files/mysql.server start rons-mbp:apps rmiller$ sudo /usr/local/mysql/support-files/mysql.server restart
Most versions of Linux:
[engineer@fed1764-csauto ~]$ sudo service mysql stop [engineer@fed1764-csauto ~]$ sudo service mysql start [engineer@fed1764-csauto ~]$ sudo service mysql restart
Modifying the configuration file
Open in a text editor /etc/my.cnf and add the following lines.
log-slow-queries slow_query_log = 1 # 1 enables the slow query log, 0 disables it slow_query_log_file = <path to log filename> long_query_time = 1000 # minimum query time in milliseconds
Save the file and restart the database. In this example queries running 1 second or longer will now be logged to the slow query file.
Postgres
How to start and stop the database
Macitosh OS X
rons-mbp:apps rmiller$ su postgres rons-mbp:apps rmiller$ pg_ctl -D /Library/PostgreSQL/9.1/data stop -m fast rons-mbp:apps rmiller$ pg_ctl -D /Library/PostgreSQL/9.1/data start rons-mbp:apps rmiller$ pg_ctl -D /Library/PostgreSQL/9.1/data restart
Fedora/SUSE
[engineer@fed1764-csauto ~]$ su postgres [engineer@fed1764-csauto ~]$ pg_ctl stop -D /var/lib/pgsql/data [engineer@fed1764-csauto ~]$ pg_ctl start -D /var/lib/pgsql/data [engineer@fed1764-csauto ~]$ pg_ctl restart -D /var/lib/pgsql/data
Red Hat/Ubuntu
engineer@ub1164:~$ su postgres engineer@ub1164:~$ /etc/init.d/postgresql stop engineer@ub1164:~$ /etc/init.d/postgresql start engineer@ub1164:~$ /etc/init.d/postgresql restart
Modifying the configuration file
Open the configuration file in a text editor. Uncomment the following line and set the minimun duration.
log_min_duration_statement = 1000 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
To log milliseconds set log_file_prefix = ‘%m’
log_line_prefix = '%m' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
Save the file and restart the database.
If You Appreciate What We Do Here On Mimastech, You Should Consider:
- Stay Connected to: Facebook | Twitter | Google+
- Support us via PayPal Donation
- Subscribe to our email newsletters.
- Tell other sysadmins / friends about Us - Share and Like our posts and services
We are thankful for your never ending support.
