Re: How to log 'user time' in postgres logs

On 8/28/19 5:36 AM, francis picabia wrote:
>
> Recently had a problem where autovacuum was accidentally left off
> and the database took 6 seconds for every task from PHP.
> I had no clue the database was the issue because I
> had the minimal
>
> log_duration = on
> log_line_prefix = '<%t>'
>
> With those settings all queries seen were roughly 1ms
>
> I need this log to show the true time it takes to get a result back.
>
> In the Linux world we have the time command which shows the user
> time reflecting all overhead added up.  I'd like postgres to show
> times like that and then if there are problems I can look further,
> change logging details, etc..
>
> I checked docs, googled,  and didn't see anything obvious.

I'm having a hard time believing autovacuum was involved in this, given
you say the queries took only 1ms on average. That would have been the
part that would have been impacted by bloated tables/out-of-date
statistics.

Some questions:

1) How did you arrive at the 6 second figure?

2) Is the PHP application on the same server as the database?

>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com