Thread: Re: [pgsql-hackers] Increasing the length of pg_stat_activity.current_query...

Tom,

> Another relevant question is why you are expecting to get this
> information through pgstats and not by looking in the postmaster log.
> I don't know about you, but I don't have any tools that are designed to
> cope nicely with looking at tables that have columns that might be many
> K wide.  Looking in the log seems a much nicer way of examining the full
> text of extremely long queries.  So I think it's actually a good thing
> that pgstats truncates the queries at some reasonable width.

Because pg_stat_activity can be queried dynamically, and the log can't.   I'm
currently dealing with this at a clients site who is having elusive "bad
queries" hammer the CPU.

In order to find a bad query by PID, I have to:
1) turn on log_statement, log_timestamp and log_pid;
2) HUP the postmaster;
3) watch top and record the time and pid of the "bad query";
4) cp the log off to a file;
5) turn back off log_statement and log_pid;
6) grep the log for the time/pid, using a regexp to deal with minor variations
in timestamp.

It's a big PITA to retrieve the text of one bad query.   And that's assuming
that the bad query re-occurs within a reasonable window of time from when I
spotted it so that I don't end up watching top for the rest of the afternoon.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> Tom,
> 
> 
>>Another relevant question is why you are expecting to get this
>>information through pgstats and not by looking in the postmaster log.
>>I don't know about you, but I don't have any tools that are designed to
>>cope nicely with looking at tables that have columns that might be many
>>K wide.  Looking in the log seems a much nicer way of examining the full
>>text of extremely long queries.  So I think it's actually a good thing
>>that pgstats truncates the queries at some reasonable width.
> 
> 
> Because pg_stat_activity can be queried dynamically, and the log can't. 

I've been planning to post a lengthy mail after 8.0 release, but it 
seems a good idea to do it now.

When comparing pgsql to MSSQL in practice, I encounter a similar problem 
as Josh. I got a server hammered by countless queries, some of them not 
too well constructed and thus soaking CPU from all users. On MSSQL, I'd 
be using the Profiler, which lets me tap one or more connections, and 
log whatever I think is important to trace down the problem. This lets 
me filter out those uninteresting 99.9 % of queries which would make my 
log unreadable. Additionally, some performance measures are recorded for 
each query, enabling me to spot the bad guys, analyze and improve them.

On pgsql, all logging goes unstructured into one file, I even can't 
start and stop a new log on demand on my observation period (somebody 
refused to implement a manual log rotation function, "nobody needs 
that"...) On a server addressed by 100 users, with several dozens of 
queries fired every second, it's hard work to locate the offending query.

It appears to me that simple increasing the max query length won't do 
the deal (and 16k would not be enough). What I'd like to see is the 
possibility to tap one or more backends (this is superuser only, of 
course), and put them in a logging mode, which will record the complete 
query including performance counters to some process in a lossless way. 
When I say tapping I mean that the backend configuration switch is *not* 
set by the very same backend, but from a different superuser backend.

Regards,
Andreas