Re: pgsql_tmp consuming all inodes

On 11/20/2014 08:20 AM, Nestor A. Diaz wrote:
> Hello People.
>
> I have installed a postgres engine: PostgreSQL 8.4.22 on
> x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.7.2-5) 4.7.2,
> 64-bit from debian packages at postgresql official repository running on
> debian wheezy 7.7 64 bit.
>
> I have a couple of databases running on a cluster, and two of them have
> been experiencing the following behavior since installed yesterday:
>
> They create a lot of files under directory "pgsql_tmp" up to the point
> they consume all the inodes, when I do a 'df -i' I found the partition
> assigned to the tablespace where the database is located is out of inodes.
>
> I don't know what postgres internals trigger this behavior but at least
> we can replicate the behavior with a query.

What query?

How is it executed?

>
> Python database clients got this error message:
>
> "DatabaseError: could not create temporary file
> "pg_tblspc/16575/pgsql_tmp/pgsql_tmp27225.105148": No space left on device"
>
> I work on postgresql 8.4 because of issues we found working with 9.1
> when it comes to partitioning, however we can not switch to 9.3 because
> we use postgis 1.5 which is supported up to 9.1.
>
> On the other side our experience with 8.4 have been good, except that
> today the database goes in recovery mode due to an out of memory error:
>
> Nov 20 09:10:52 feline1 kernel: [125090.855205] Out of memory: Kill
> process 14314 (postgres) score 145 or sacrifice child
> Nov 20 09:10:52 feline1 kernel: [125090.855267] Killed process 14314
> (postgres) total-vm:3579868kB, anon-rss:2708kB, file-rss:2668600kB
> Nov 20 09:10:52 feline1 kernel: [125091.592295] postgres invoked
> oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0
> Nov 20 09:10:52 feline1 kernel: [125091.592367] postgres cpuset=/
> mems_allowed=0
> Nov 20 09:10:52 feline1 kernel: [125091.592402] Pid: 21339, comm:
> postgres Tainted: G O 3.2.0-4-amd64 #1 Debian 3.2.63-2+deb7u1

Well this is coming from the kernel OOM(Out Of Memory) manager.

For how to deal with this see here:

http://www.postgresql.org/docs/8.4/static/kernel-resources.html#AEN24213

>
> Any ideas on how to fix the creation of too much files under 'pgsql_tmp'
> directory ? Any hint on the Out of memory error ?

>
> Slds.
>

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