Search Postgresql Archives

Re: pg_restore out of memory

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

 





On Tue, 12 Jul 2016, 7:25 p.m. Miguel Ramos, <org.postgresql@xxxxxxxxxxxxxxxxx> wrote:

Hi,

We have backed up a database and now when trying to restore it to the
same server we get this:

 > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump
 > pg_restore: [custom archiver] out of memory
 > 12:09:56.58  9446.593u+1218.508s 24.3%  167+2589k  6+0io  0pf+0sw
6968822cs


Some information about the application:

- We have sensor data, including pictures, and number crunshing output,
then so the large tables on this database have 319, 279, 111 and 26GB.
Mostly on TOAST pages, but the 279GB one divides it evenly. This
database is 765GB. We try to keep them under 4TB.
- Transactions are large, some 100 MB at a time.
- We also use PostGIS.

About the server (dedicated):

- FreeBSD 9.1-RELEASE #0 on AMD64
- 16 GB of RAM
- 8x3GB hardware RAID 10
- 10TB slice for pgdata UFS-formatted and 32kB block
- PostgreSQL 9.1.8 custom compiled to get 32kB blocks
- Installed in 2013 with ~10 people working with it, 145 days uptime today.

I found two relevant threads on the mailing-lists.
The most recent one sugested that postgresql was being configured to use
more memory than what's available.
The older one sugested that the system limits on the size of the data or
stack segments were lower than required.

So here are some server parameters (relevant or otherwise):

 > max_connections = 100
 > shared_buffers = 4GB  -- 25% of RAM
 > temp_buffers = 32MB  -- irrelevant?
 > work_mem = 64MB
 > maintenance_work_mem = was 1G lowered to 256M then 64M


Why did you lower it? I think increasing it should help better. But 1GB seems like fine.


 > wal_buffers = -1  -- should mean 1/32 of shared_buffers = 128MB

Increase this during the restore, may be 512MB

 > checkpoint_segments = 64  -- WAL segments are 16MB
 > effective_cache_size = 8GB  -- irrelevant?


I suspect that the restore fails when constructing the indices. After
the process is aborted, the data appears to be all or most there, but no
indices.

What is logged in database log files? Have you checked that?

So, all I did so far, was lowering maintenance_work_mem and it didn't work.

System limits, as you can see, are at defaults (32GB for data and 512MB
for stack):

 > # limit
 > cputime      unlimited
 > filesize     unlimited
 > datasize     33554432 kbytes
 > stacksize    524288 kbytes
 > coredumpsize unlimited
 > memoryuse    unlimited
 > vmemoryuse   unlimited
 > descriptors  11095
 > memorylocked unlimited
 > maxproc      5547
 > sbsize       unlimited
 > swapsize     unlimited

Shared memory is configured to allow for the single shared memory
segment postgresql appears to use, plus a bit of extra (8GB):

 > # ipcs -M
 > shminfo:
 >         shmmax:   8589934592    (max shared memory segment size)
 >         shmmin:            1    (min shared memory segment size)
 >         shmmni:          192    (max number of shared memory identifiers)
 >         shmseg:          128    (max shared memory segments per process)
 >         shmall:      2097152    (max amount of shared memory in pages)

And semaphores (irrelevant?)...

 > # ipcs -S
 > seminfo:
 >         semmni:          256    (# of semaphore identifiers)
 >         semmns:          512    (# of semaphores in system)
 >         semmnu:          256    (# of undo structures in system)
 >         semmsl:          340    (max # of semaphores per id)
 >         semopm:          100    (max # of operations per semop call)
 >         semume:           50    (max # of undo entries per process)
 >         semusz:          632    (size in bytes of undo structure)
 >         semvmx:        32767    (semaphore maximum value)
 >         semaem:        16384    (adjust on exit max value)

What are your vm.dirty_ratio and vm.dirty_background_ratio? I think reducing them may help. But can not really say what exactly would help unless you are able to get the error source in db logs




I don't know what else to try.
I lowered maintenance_work_mem without restarting the server.
In some of the attempts, but not all, the restore was done while people
were working.

Each attempt takes 12 hours...
We couldn't use the directory -Fd dump because it's postgresql 9.1.
The original database is still on the server, this is a test restore.

We have about one or two months of slack before we really need to remove
them from the server to recover space.


--
Miguel Ramos


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Best Regards
Sameer Kumar | DB Solution Architect 
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com


[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