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
Import Notes
Reply to msg id not found: 20041109021343.1B4F83A4086@svr1.postgresql.orgReference msg id not found: 20041109021343.1B4F83A4086@svr1.postgresql.org
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