From: | Ivan Bianchi <ivan(at)wikiloc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Update statement results in Out of memory |
Date: | 2016-07-06 09:13:49 |
Message-ID: | CAE9-U41VMsu442WWWp2Am5zcn4DarT2iHr3_ORvYqU0CzpGrNg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I am trying to update a column using a PostGIS ST_Buffer
<http://postgis.net/docs/ST_Buffer.html> function into a table of 4.257.769
rows, but after 6 hours, an *Out of memory* error appears and the kernel
starts killing processes until a *Kernel Panic* shows up.
I have simplified the buffer target geometry and also added a gist index to
that column.
The statement is the following:
> psql -h host -U user -W -d database -c "UPDATE table SET buffer =
> ST_Buffer(simplified_geometry, 0.005);"
>
After reading and tunning the configuration, I still have the same result.
Here's the initial memory stats:
total used free shared buff/cache available
> Mem: 15G 1.5G 12G 503M 1.4G
> 13G
> Swap: 7.8G 0B 7.8G
I'm running out of ideas, as I think the postgresql.conf memory parameters
are quite low for the machine specs. I understand I can split the process
and paginate the rows, but I can't see why I can't deal with this full
statement right now.
Do you think this issue is related with the postgres memory parameters
configuration? Why is not respecting the shared_buffers or
effective_cache_size parameters and keeps growing?
Here's some info:
*Machine specs*
- Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz (8 cores)
- 16 GB of memory
- Fedora release 23 (Twenty Three)
- Kernel - 4.5.7-202.fc23.x86_64
*postgresql.conf*
- effective_cache_size = 5GB
- shared_buffers = 3GB
- work_mem = 10MB
- maintenance_work_mem = 800MB
- wal_buffers = 16MB
*Kernel parameters*
- vm.overcommit_memory=2
- kernel.shmmax = 8340893696
- kernel.shmall = 2036351
*Versions:*
- PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.3.1
20160406 (Red Hat 5.3.1-6), 64-bit
- POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.1,
04 March 2015" GDAL="GDAL 2.0.2, released 2016/01/26" LIBXML="2.9.3"
LIBJSON="0.12" RASTER
Many thanks,
--
Ivan
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2016-07-06 11:16:27 | Re: How sync settings or extensions in streaming replication |
Previous Message | Christian Castelli | 2016-07-06 07:43:03 | Re: Avoid deadlocks on alter table |