How to limit Greenplum logging information
Article ID: 295914
Updated On:
Products
Issue/Introduction
After a heavy or large transaction on a database, the database log file might grown very large and difficult to manage.
This article discusses how to use the "log_min_messages" and the "log_statement" parameter to limit the logging level of a system.
Environment
Greenplum (GPDB) all versions
Resolution
Valid values for this parameter are shown below. Each level includes all of the levels below it. For example, if the parameter is set to "warning", then "warning", "error", "log", "fatal" and "panic" will be logged.
Note: The default value is "notice".
#log_min_messages = notice # Values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
Consider that the parameter is set to the level "fatal". The log message will include "fatal" and "panic". It will not include "error", "warning" etc.
Since the value "log" is in the middle of "warning" and "panic", it is not possible to remove log messages when the parameter is set to "warning".
To avoid avoid the "log" message, limit the message by setting the parameter log_statement.
#log_statement = 'all' # none
# mod
# ddl
# all
By default all statements are logged. Set the logging level for this parameter to "none", this will limit the log files, unless an ERROR is noticed.
Change the parameters using the following command:
gpconfig -c log_min_messages -v warning gpconfig -c log_statement -v none gpstop -u
Example: Observe the message on the log file when it set to the default value "notice":
template1=# SHOW log_min_messages ; log_min_messages ------------------ notice (1 row) Time: 0.560 ms template1=# SHOW log_statement ; log_statement --------------- all (1 row) Time: 0.451 ms template1=# SELECT count(*) FROM pg_class ; count ------- 398 (1 row) Time: 2.573 ms template1=# CREATE TABLE backup_pg_class AS SELECT * FROM pg_class ; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. SELECT 401 Time: 1787.577 ms template1=# \q gpadmin:Fullrack@mdw $ tail gpdb-2014-08-27_030041.csv 2014-08-27 03:00:47.166027 PDT,"gpadmin","template1",p31834,th-1305670224,"[local]",,2014-08-27 03:00:33 PDT,1564,con21,cmd3,seg-1,,dx20,x1564,sx1,"LOG","00000","statement: SHOW log_min_messages ;",,,,,,"SHOW log_min_messages ;",0,,"postgres.c",1555, 2014-08-27 03:00:53.993804 PDT,"gpadmin","template1",p31834,th-1305670224,"[local]",,2014-08-27 03:00:33 PDT,1565,con21,cmd4,seg-1,,dx21,x1565,sx1,"LOG","00000","statement: SHOW log_statement ;",,,,,,"SHOW log_statement ;",0,,"postgres.c",1555, 2014-08-27 03:01:12.646394 PDT,"gpadmin","template1",p31834,th-1305670224,"[local]",,2014-08-27 03:00:33 PDT,1566,con21,cmd5,seg-1,,dx22,x1566,sx1,"LOG","00000","statement: SELECT count(*) FROM pg_class ;",,,,,,"SELECT count(*) FROM pg_class ;",0,,"postgres.c",1555, 2014-08-27 03:01:39.724179 PDT,"gpadmin","template1",p31834,th-1305670224,"[local]",,2014-08-27 03:00:33 PDT,1567,con21,cmd7,seg-1,,dx23,x1567,sx1,"LOG","00000","statement: CREATE TABLE backup_pg_class AS SELECT * FROM pg_class ;",,,,,,"CREATE TABLE backup_pg_class AS SELECT * FROM pg_class ;",0,,"postgres.c",1555, 2014-08-27 03:01:39.924907 PDT,"gpadmin","template1",p4264,th-1305670224,"[local]",,2014-08-27 03:01:39 PDT,1567,con21,cmd8,seg-1,slice1,dx23,x1567,sx1,"LOG","00000","statement: CREATE TABLE backup_pg_class AS SELECT * FROM pg_class ;",,,,,,"CREATE TABLE backup_pg_class AS SELECT * FROM pg_class ;",0,,"postgres.c",1095,
After altering the parameters, analyze the logs.
gpadmin:Fullrack@mdw $ gpconfig -c log_min_messages -v warning 20140827:03:10:14:007613 gpconfig:mdw:gpadmin-[INFO]:-completed successfully gpadmin:Fullrack@mdw $ gpconfig -c log_statement -v none 20140827:03:02:39:002334 gpconfig:mdw:gpadmin-[INFO]:-completed successfully gpadmin:Fullrack@mdw $ gpstop -u 20140827:03:02:43:031323 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u 20140827:03:02:43:031323 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment... 20140827:03:02:43:031323 gpstop:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20140827:03:02:43:031323 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20140827:03:02:43:031323 gpstop:mdw:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.2.8.0 build 1' 20140827:03:02:43:031323 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload . gpadmin:Fullrack@mdw $ psql Timing is on. Pager usage is off. psql (8.2.15) Type "help" for help. template1=# SHOW log_min_messages ; log_min_messages ------------------ warning (1 row) Time: 0.423 ms template1=# SHOW log_statement ; log_statement --------------- none (1 row) template1=# SELECT count(*) FROM pg_class ; count ------- 401 (1 row) Time: 2.791 ms template1=# SELECT count(*) FROM pg_class ; count ------- 401 (1 row) Time: 1.302 ms template1=# CREATE TABLE backup_pg_class_1 AS SELECT * FROM pg_class ; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. SELECT 404 Time: 1723.328 ms template1=# \q
After the change, there are no statements logged in the log file .
Reload the configuration file. The following messages are from gpstop -u when reloading the configuration file.
gpadmin:Fullrack@mdw $ tail gpdb-2014-08-27_030041.csv
(SELECT dbid, fs.oid, fselocation
FROM pg_catalog.gp_segment_configuration
JOIN pg_catalog.pg_filespace_entry on (dbid = fsedbid)
JOIN pg_catalog.pg_filespace fs on (fsefsoid = fs.oid)) fsloc
where db.dattablespace = ts.oid
and ts.spcfsoid = fsloc.oid
and fsloc.dbid = 25
",0,,"postgres.c",1555,
2014-08-27 03:02:43.761581 PDT,,,p26116,th-1305670224,,,,0,,,seg-1,,,,,"LOG","00000","received SIGHUP, reloading configuration files",,,,,,,0,,"postmaster.c",4298,
2014-08-27 03:02:43.762145 PDT,,,p26116,th-1305670224,,,,0,,,seg-1,,,,,"LOG","42704","unrecognized configuration parameter ""gp_crash_handler_async""",,,,,,,0,,"guc.c",8313,
gpadmin:Fullrack@mdw $
Refer to this article for more information on how to rotate or manage the logs manually.
Additional Information
Feedback
