Pivotal Knowledge Base

Follow

How to limit Pivotal Greenplum logging information

Goal

After heavy/large transaction on the database, the database logfile might grow up to be very large which becomes difficult to manage. Here we will look at parameters "log_min_messages" that you can use to limit the logging level at your system.

Solution

Valid values for this parameter are shown below, each level includes all the levels that is below its ladder i.e. if the parameter is set to "WARNING" all the WARNING,ERROR,LOG,FATAL and PANIC will be logged.

By default the values is NOTICE which would contain all the messages from

#log_min_messages = notice              # Values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic

So using the above mentioned logging level if set to FATAL ( For minimal logging ) it will include message related to FATAL , PANIC but it will not include ERROR , WARNING etc as it above the ladder.

Since option LOG is in the middle of WARNING and PANIC, it would not be possible to remove LOG messages when using with the option WARNING to limit the message.

However the workaround to avoid the LOG message is to use / limit the message by setting the parameter log_statement .

#log_statement = 'all'                  # none 
                                        # mod 
                                        # ddl 
                                        # all

By default all the statements are logged . You can set the logging level for this parameter to none and this will limit the user query logging to the logfiles unless an ERROR is noticed.

Command

You can change the parameters using the below command:

gpconfig -c log_min_messages -v warning
gpconfig -c log_statement -v none
gpstop -u

Example

Observe the message on the logfile when set to default,

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,

Altering the parameters and observing the trend.

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 logfile .

NOTE: The below 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 $ 

Do have a look at the document here on how to rotate/manage the logs manually.

For further information please refer Greenplum Admin/Reference guide for parameters related to log tuning ( Parameters that starts with log_* ). 

 

Comments

Powered by Zendesk