0

I have a few queries regarding columns in Postgres CSV Log.

Query 1 Following is a sample Logline

2012-03-28 19:25:47.968 IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,6,"SET",2012-03-28 19:25:47 IST,2/0,0,LOG,00000,"QUERY STATISTICS","! system usage stats: ! 0.047000 elapsed 0.000000 user 0.000000 system sec ! [0.078125 user 0.031250 sys total]",,,,,"Select * from stock_apals" ,,"ShowUsage, .\src\backend\tcop\postgres.c:4305",""

I am aware of all the data segments except the following

"! system usage stats: ! 0.047000 elapsed 0.000000 user 0.000000 system sec ! [0.078125 user 0.031250 sys total]",

What do the number mean, it seems to appear only with a Logline for SQL statements

Query 2

my CSV Log has lot of occurances of a certain Log select statement. It is exactly the same and all over the Log.

2012-03-28 19:25:48.015 IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,7,"idle",2012-03-28 19:25:47 IST,2/98,0,LOG,00000,"statement: SELECT typname, oid FROM pg_type WHERE typname IN ('oidvector', '_oidvector', 'unknown', '_unknown', 'refcursor', '_refcursor', 'char', '_char', 'bpchar', '_bpchar', 'varchar', '_varchar', 'text', '_text', 'name', '_name', 'bytea', '_bytea', 'bit', '_bit', 'bool', '_bool', 'int2', '_int2', 'int4', '_int4', 'int8', '_int8', 'oid', '_oid', 'float4', '_float4', 'float8', '_float8', 'numeric', '_numeric', 'inet', '_inet', 'money', '_money', 'point', '_point', 'lseg', '_lseg', 'path', '_path', 'box', '_box', 'circle', '_circle', 'polygon', '_polygon', 'uuid', '_uuid', 'xml', '_xml', 'interval', '_interval', 'date', '_date', 'time', '_time', 'timetz', '_timetz', 'timestamp', '_timestamp', 'abstime', '_abstime', 'timestamptz', '_timestamptz')",,,,,,,,"exec_simple_query, .\src\backend\tcop\postgres.c:900",""

Is this a performance concern. ? is there anything that i can do to keep this statement from recurring.

thank you arvind

arvind
  • 1,155
  • 1
  • 12
  • 21

2 Answers2

1

Another SO question might give the answer to your first question:

What do 'real', 'user' and 'sys' mean in the output of time(1)?

You can use pretty fine grained controlls of what to log. Be sure to set the correct settings in your .conf file for this. I'm not a performance expert, but I would say that this has only very little effect on the performance and can be neglected if the data is viable to you.

Community
  • 1
  • 1
DrColossos
  • 11,696
  • 2
  • 40
  • 64
  • thanks , our log definition is for all statements with 0 processing duration i.e. we are logging all statements. it is for postgres 9.0.5 running on windows, with application developed in c sharp – arvind Mar 29 '12 at 14:11
0

I have found the answer regarding numbers on Postgresql.org Form

Query 1 These lines are emitted if you set log_statement_stats to "on".

They contain execution statistics for the query. The "user" and "sys" times are acquired by the getrusage(2) or times(2) system call, depending on your operating system (on Windows, GetProcessTimes is used). You can look at the man page for your system for details.

The values you see are: wall time, CPU user time and kernel CPU time it took to execute the query. The values in brackets are the values accumulated for this database session.

Answered by - Laurenz Albe

Query 2 It is because of too many repititive sql sessions being opened and closed

I hope it helps other users understand, how much depth the Log data shows.

regards

arvind
  • 1,155
  • 1
  • 12
  • 21