Re: Understanding postgresql logs

On Mon, May 27, 2013 at 6:37 AM, igivanoff <igivanoff(at)yahoo(dot)com> wrote:

> I have a PostgreSQL 9.0/9.2 which from time to time hits some memory
> issues.
> I know the best approach is to monitor the DB performance and activity but
> in the log files I see error messages similar to:
>
>
> TopMemoryContext: 221952 total in 17 blocks; 7440 free (41 chunks); 214512
> used
> TopTransactionContext: 8192 total in 1 blocks; 6384 free (0 chunks); 1808
> used
> CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks);
> 20496 used
>

You can look for the biggest contexts by copying all of these lines to a
file and then sorting it by something like "sort -t: -k2".

The biggest one I see is "CacheMemoryContext: 1359224 total", which is
pretty small, so the culprit is probably hidden behind the ellipsis in your
message.

1 tm:2013-05-18 11:21:58.274 UTC db:VCDB pid:29515 ERROR: out of
> memory
> 2 tm:2013-05-18 11:21:58.274 UTC db:VCDB pid:29515 DETAIL: Failed on
> request of size 40.
>
> I've searched around for some documentation/books/posts on that topic but I
> failed to find detailed info what's TopMemoryContext,
> TopTransactionContext,
> CurTransactionContext etc. and how those values can be translated.
>

I doubt that most of these are documented anywhere except in the source
code. You don't need to understand all of them, you can isolate just the
one which is very large, and grep the source code for it.

Most likely you just set maintenance_work_mem too large.

Cheers,

Jeff