Search Postgresql Archives

Re: Running out of memory on vacuum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> Subject:  Running out of memory on vacuum
> 
> Hi all,
> 
> I have a production database that sometimes runs out of memory=at
> nightly vacuum.
> 
> The application runs typically with around 40 post=res connections but
> there are times when the connections increase because =f some queries
> going on. The reason is that the operations are slow, the t=rminals
> time out and try to reconnect using new connections. Some time ago=I
> started to have problems with too many connections being open so I
> lower=d the limit to
> 300 connections. It was all good until recently when even w=th 180
> connections I am running out of memory on vacuum... So the connecti=ns
> increase to 180 and the system still runs properly for other 2 days
> but=then at nightly vacuum runs out of memory.
> The fix is to restart postgre= ... If I only close the connections the
> problem is still these so I need =o restart postgres.
> If I don't restart postgres then the system will run=out of memory on
> queries at a point...
> Another important thing is that d=ring vacuum at 1am nothing else is
> going on that server so all the connect=ons are idle.
> 
> 2013-05-14 06:53:51.449
> CST,"postgres","abrazo",8=41,"[local]",519233dc.2225,3,"VACUUM",2013-
> 05-14
> 06:53:48 CST,174/67143,0,=RROR,53200,"out of memory","Failed on request
> of size 668141820.",,,,,"vac=um;",,,"psql"
> 
> OS:
> 
> SUSE Linux Enterprise Server 11 (x86_64) VER=ION = 11 PATCHLEVEL = 2
> 
> Suse compiled postgre version :
>  =A0                        =A0                        =A0 version
> ----------------------------------------------=------------------------
> --------------------------------------------------=--
>  PostgreSQL 9.0.3 on x86_64-suse-linux-gnu, compiled by GCC gcc (SU=E
> Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit
> 
> Postgre= configuration parameters:
> 
> max_connections = 300
> shared_buffers =3D 2GB
> maintenance_work_mem = 2GB
> effective_cache_size = 4GB
> wor=_mem = 2MB
> wal_buffers = 1MB
> 
> 
> checkpoint_segments = 16
> au=ovacuum = on
> 
> 
> wal_level = hot_standby
> archive_mode = on
> a=chive_command = 'test -f
> /cbnDBscripts/tmp/PITR_primarydb_stop_backup ||=rsync --timeout=30 -atz
> %p spsnidb1:/data01/wal_files/%f'
> archive_time=ut = 60
> 
> 
> free
> 
>     total  =A0
> used       free
> shared    buffer=     cached
> Mem:
> 7880512    7825136 =A0
> 55376
> 0      72376  =A0 4537644 -/+
> buffers/cache:    3215116    4665396
> Swap:=A0
> 2097148
> 0    2097148
> 
> =etc/sysctl.conf
> 
> kernel.shmmax=2684354560
> kernel.shmall=26843545=0
> vm.swappiness=0
> vm.overcommit_memory=2
> 
.....
> 
> Thanks,
> ioana


1. You have autovacuum running.  Is there specific reason to run "manual" vacuum nightly?
2. Reduce maintenance_work_mem, you may have manual vacuum and autovacuum running at the same time, each requesting 2GB (your current setting).
3. Use connection pooling software (e.g. PgBouncer) and reduce max_connections.

Igor Neyman




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux