Tuning logging-related parameters

In this recipe, we will talk about tuning logging-related parameters.

How to do it...

The following are the logging-related parameters that usually require tuning:

• log_line_prefix
• log_statement
• log_min_duration_statement

How it works...

log_line_prefix: Usually the default value related to this parameter is empty and it is not desirable. A good way to put this in context would be to use the following format:

log_line_prefix='%t:%r:%u@%d:[%p]: '

Once this format is used, every line in the log will follow this format. The following is a description of the entries:

• %t: This is the timestamp
• %u: This is the database username
• %r: This is where the remote host connection is from
• %d: This is where the database connection is to
• %p: This is the process ID of the connection

Not all of these values will be required initially; however, at a later stage as you drill down further, they will be useful. For instance, the significance of processID becomes apparent when you are troubleshooting performance issues.

log_statement: This parameter defines which statements are logged. Statement logging is a powerful technique to find performance issues. The options for the log_statement values are as follows:

• None: At this setting, no statement-level logging is captured.
• DDL: When this setting is enabled, DDL statements such as CREATE, ALTER, and DROP statements are captured. This is a handy setting to find out whether there were any major changes introduced accidentally by developers or administrators alike.
• MOD: When this setting is enabled, all the statements that modify a value will be logged. From the point of view of auditing changes, this is a handy feature. This setting is not required if your workload includes running SELECT statements or for reporting purposes.
• ALL: When this setting is enabled, all the statements are logged. This should generally be avoided to counter the logging overhead on the server when all statements are being logged.

log_min_duration_statement: This parameter setting causes the duration of each statement to be logged if a given statement ran for at least a certain number of milliseconds. It is basically like setting a threshold and if any statement exceeds the defined thresholds, the duration of that statement is logged.