agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
11+ messages / 7 participants
[nested] [flat]
* PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
@ 2013-10-01 09:07 Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 10:04 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Jov <amutu@amutu.com>
2013-10-01 11:01 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-10-01 12:49 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Magnus Hagander <magnus@hagander.net>
2013-10-11 07:24 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects luckyjackgao <luckyjackgao@gmail.com>
0 siblings, 4 replies; 11+ messages in thread
From: Sergey Klochkov @ 2013-10-01 09:07 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Hello All,
While trying to backup a database of relatively modest size (160 Gb) I
ran into the following issue:
When I run
$ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
File /path/to/mydb.dmp does not appear (yes, I've checked permissions
and so on). pg_dump just begins to consume memory until it eats up all
avaliable RAM (96 Gb total on server, >64 Gb available) and is killed by
the oom killer.
According to pg_stat_activity, pg_dump runs the following query
SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
lomowner) AS rolname, lomacl FROM pg_largeobject_metadata
until it is killed.
strace shows that pg_dump is constantly reading a large amount of data
from a UNIX socket. I suspect that it is the result of the above query.
There are >300000000 large objects in the database. Please don't ask me why.
I tried googling on this, and found mentions of pg_dump being killed by
oom killer, but I failed to find anything related to the huge large
objects number.
Is there any method of working around this issue?
Thanks in advance.
OS: CentOS 6
PostgreSQL version: 9.2.1
96 Gb RAM
PostgreSQL configuration:
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 500 # (change requires restart)
shared_buffers = 16GB # min 128kB
temp_buffers = 64MB # min 800kB
work_mem = 512MB # min 64kB
maintenance_work_mem = 30000MB # min 1MB
checkpoint_segments = 70 # in logfile segments, min 1,
16MB each
effective_cache_size = 50000MB
logging_collector = on # Enable capturing of stderr and
csvlog
log_directory = 'pg_log' # directory where log files are
written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file of the
log_rotation_age = 1d # Automatic rotation of logfiles
will
log_rotation_size = 0 # Automatic rotation of logfiles
will
log_min_duration_statement = 5000
log_line_prefix = '%t' # special values:
autovacuum = on # Enable autovacuum subprocess?
'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all
actions and
autovacuum_max_workers = 5 # max number of autovacuum
subprocesses
autovacuum_naptime = 5s # time between autovacuum runs
autovacuum_vacuum_threshold = 25 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before
vacuum
autovacuum_vacuum_cost_delay = 7ms # default vacuum cost delay for
autovacuum_vacuum_cost_limit = 1500 # default vacuum cost limit for
datestyle = 'iso, dmy'
lc_monetary = 'ru_RU.UTF-8' # locale for monetary
formatting
lc_numeric = 'ru_RU.UTF-8' # locale for number
formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.russian'
--
Sergey Klochkov
klochkov@iqbuzz.ru
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
@ 2013-10-01 10:04 ` Jov <amutu@amutu.com>
2013-10-01 10:23 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
3 siblings, 1 reply; 11+ messages in thread
From: Jov @ 2013-10-01 10:04 UTC (permalink / raw)
To: Sergey Klochkov <klochkov@iqbuzz.ru>; +Cc: pgsql-admin@postgresql.org
Try update to the latest release,I see there is a bug fix about pg_dump out
of memroy in 9.2.2,from the release note
http://www.postgresql.org/docs/devel/static/release-9-2-2.html:
-
Work around unportable behavior of malloc(0) and realloc(NULL, 0) (Tom
Lane)
On platforms where these calls return NULL, some code mistakenly thought
that meant out-of-memory. This is known to have broken pg_dump for
databases containing no user-defined aggregates. There might be other cases
as well.
Jov
blog: http:amutu.com/blog <http://amutu.com/blog;
2013/10/1 Sergey Klochkov <klochkov@iqbuzz.ru>
> Hello All,
>
> While trying to backup a database of relatively modest size (160 Gb) I ran
> into the following issue:
>
> When I run
> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>
> File /path/to/mydb.dmp does not appear (yes, I've checked permissions and
> so on). pg_dump just begins to consume memory until it eats up all
> avaliable RAM (96 Gb total on server, >64 Gb available) and is killed by
> the oom killer.
>
> According to pg_stat_activity, pg_dump runs the following query
>
> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = lomowner)
> AS rolname, lomacl FROM pg_largeobject_metadata
>
> until it is killed.
>
> strace shows that pg_dump is constantly reading a large amount of data
> from a UNIX socket. I suspect that it is the result of the above query.
>
> There are >300000000 large objects in the database. Please don't ask me
> why.
>
> I tried googling on this, and found mentions of pg_dump being killed by
> oom killer, but I failed to find anything related to the huge large objects
> number.
>
> Is there any method of working around this issue?
>
> Thanks in advance.
>
> OS: CentOS 6
> PostgreSQL version: 9.2.1
> 96 Gb RAM
>
> PostgreSQL configuration:
>
> listen_addresses = '*' # what IP address(es) to listen on;
> port = 5432 # (change requires restart)
> max_connections = 500 # (change requires restart)
> shared_buffers = 16GB # min 128kB
> temp_buffers = 64MB # min 800kB
> work_mem = 512MB # min 64kB
> maintenance_work_mem = 30000MB # min 1MB
> checkpoint_segments = 70 # in logfile segments, min 1, 16MB
> each
> effective_cache_size = 50000MB
> logging_collector = on # Enable capturing of stderr and
> csvlog
> log_directory = 'pg_log' # directory where log files are
> written,
> log_filename = 'postgresql-%a.log' # log file name pattern,
> log_truncate_on_rotation = on # If on, an existing log file of
> the
> log_rotation_age = 1d # Automatic rotation of logfiles
> will
> log_rotation_size = 0 # Automatic rotation of logfiles
> will
> log_min_duration_statement = 5000
> log_line_prefix = '%t' # special values:
> autovacuum = on # Enable autovacuum subprocess?
> 'on'
> log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions
> and
> autovacuum_max_workers = 5 # max number of autovacuum
> subprocesses
> autovacuum_naptime = 5s # time between autovacuum runs
> autovacuum_vacuum_threshold = 25 # min number of row updates before
> autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before
> vacuum
> autovacuum_vacuum_cost_delay = 7ms # default vacuum cost delay for
> autovacuum_vacuum_cost_limit = 1500 # default vacuum cost limit for
> datestyle = 'iso, dmy'
> lc_monetary = 'ru_RU.UTF-8' # locale for monetary
> formatting
> lc_numeric = 'ru_RU.UTF-8' # locale for number
> formatting
> lc_time = 'ru_RU.UTF-8' # locale for time
> formatting
> default_text_search_config = 'pg_catalog.russian'
>
> --
> Sergey Klochkov
> klochkov@iqbuzz.ru
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin;
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 10:04 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Jov <amutu@amutu.com>
@ 2013-10-01 10:23 ` Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 10:46 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Sergey Klochkov @ 2013-10-01 10:23 UTC (permalink / raw)
To: Jov <amutu@amutu.com>; +Cc: pgsql-admin@postgresql.org
I've upgraded to 9.2.4. The problem still persists. It consumed 10 Gb of
RAM in 5 minutes and still grows. The dump file did not appear.
On 01.10.2013 14:04, Jov wrote:
> Try update to the latest release,I see there is a bug fix about pg_dump
> out of memroy in 9.2.2,from the release note
> http://www.postgresql.org/docs/devel/static/release-9-2-2.html:
>
> *
>
> Work around unportable behavior of malloc(0) and realloc(NULL,
> 0) (Tom Lane)
>
> On platforms where these calls return NULL, some code mistakenly
> thought that meant out-of-memory. This is known to have broken
> pg_dump for databases containing no user-defined aggregates. There
> might be other cases as well.
>
>
> Jov
> blog: http:amutu.com/blog <http://amutu.com/blog;
>
>
> 2013/10/1 Sergey Klochkov <klochkov@iqbuzz.ru <mailto:klochkov@iqbuzz.ru>>
>
> Hello All,
>
> While trying to backup a database of relatively modest size (160 Gb)
> I ran into the following issue:
>
> When I run
> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>
> File /path/to/mydb.dmp does not appear (yes, I've checked
> permissions and so on). pg_dump just begins to consume memory until
> it eats up all avaliable RAM (96 Gb total on server, >64 Gb
> available) and is killed by the oom killer.
>
> According to pg_stat_activity, pg_dump runs the following query
>
> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
> lomowner) AS rolname, lomacl FROM pg_largeobject_metadata
>
> until it is killed.
>
> strace shows that pg_dump is constantly reading a large amount of
> data from a UNIX socket. I suspect that it is the result of the
> above query.
>
> There are >300000000 large objects in the database. Please don't ask
> me why.
>
> I tried googling on this, and found mentions of pg_dump being killed
> by oom killer, but I failed to find anything related to the huge
> large objects number.
>
> Is there any method of working around this issue?
>
> Thanks in advance.
>
> OS: CentOS 6
> PostgreSQL version: 9.2.1
> 96 Gb RAM
>
> PostgreSQL configuration:
>
> listen_addresses = '*' # what IP address(es) to listen on;
> port = 5432 # (change requires restart)
> max_connections = 500 # (change requires restart)
> shared_buffers = 16GB # min 128kB
> temp_buffers = 64MB # min 800kB
> work_mem = 512MB # min 64kB
> maintenance_work_mem = 30000MB # min 1MB
> checkpoint_segments = 70 # in logfile segments, min
> 1, 16MB each
> effective_cache_size = 50000MB
> logging_collector = on # Enable capturing of stderr
> and csvlog
> log_directory = 'pg_log' # directory where log files
> are written,
> log_filename = 'postgresql-%a.log' # log file name pattern,
> log_truncate_on_rotation = on # If on, an existing log
> file of the
> log_rotation_age = 1d # Automatic rotation of
> logfiles will
> log_rotation_size = 0 # Automatic rotation of
> logfiles will
> log_min_duration_statement = 5000
> log_line_prefix = '%t' # special values:
> autovacuum = on # Enable autovacuum
> subprocess? 'on'
> log_autovacuum_min_duration = 0 # -1 disables, 0 logs all
> actions and
> autovacuum_max_workers = 5 # max number of autovacuum
> subprocesses
> autovacuum_naptime = 5s # time between autovacuum runs
> autovacuum_vacuum_threshold = 25 # min number of row updates
> before
> autovacuum_vacuum_scale_factor = 0.1 # fraction of table size
> before vacuum
> autovacuum_vacuum_cost_delay = 7ms # default vacuum cost delay for
> autovacuum_vacuum_cost_limit = 1500 # default vacuum cost limit for
> datestyle = 'iso, dmy'
> lc_monetary = 'ru_RU.UTF-8' # locale for
> monetary formatting
> lc_numeric = 'ru_RU.UTF-8' # locale for number
> formatting
> lc_time = 'ru_RU.UTF-8' # locale for time
> formatting
> default_text_search_config = 'pg_catalog.russian'
>
> --
> Sergey Klochkov
> klochkov@iqbuzz.ru <mailto:klochkov@iqbuzz.ru>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
> <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/__mailpref/pgsql-admin
> <http://www.postgresql.org/mailpref/pgsql-admin;
>
>
--
Sergey Klochkov
klochkov@iqbuzz.ru
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 10:04 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Jov <amutu@amutu.com>
2013-10-01 10:23 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
@ 2013-10-01 10:46 ` Sergey Klochkov <klochkov@iqbuzz.ru>
0 siblings, 0 replies; 11+ messages in thread
From: Sergey Klochkov @ 2013-10-01 10:46 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Stack trace:
Thread 1 (Thread 0x7ff72c4c97c0 (LWP 13086)):
#0 removeHeapElement (objs=0x1a0c90630, numObjs=<value optimized out>,
preBoundaryId=<value optimized out>, postBoundaryId=<value optimized
out>) at pg_dump_sort.c:502
#1 TopoSort (objs=0x1a0c90630, numObjs=<value optimized out>,
preBoundaryId=<value optimized out>, postBoundaryId=<value optimized
out>) at pg_dump_sort.c:415
#2 sortDumpableObjects (objs=0x1a0c90630, numObjs=<value optimized
out>, preBoundaryId=<value optimized out>, postBoundaryId=<value
optimized out>) at pg_dump_sort.c:280
#3 0x000000000041acd1 in main (argc=<value optimized out>, argv=<value
optimized out>) at pg_dump.c:747
On 01.10.2013 14:23, Sergey Klochkov wrote:
> I've upgraded to 9.2.4. The problem still persists. It consumed 10 Gb of
> RAM in 5 minutes and still grows. The dump file did not appear.
>
> On 01.10.2013 14:04, Jov wrote:
>> Try update to the latest release,I see there is a bug fix about pg_dump
>> out of memroy in 9.2.2,from the release note
>> http://www.postgresql.org/docs/devel/static/release-9-2-2.html:
>>
>> *
>>
>> Work around unportable behavior of malloc(0) and realloc(NULL,
>> 0) (Tom Lane)
>>
>> On platforms where these calls return NULL, some code mistakenly
>> thought that meant out-of-memory. This is known to have broken
>> pg_dump for databases containing no user-defined aggregates. There
>> might be other cases as well.
>>
>>
>> Jov
>> blog: http:amutu.com/blog <http://amutu.com/blog;
>>
>>
>> 2013/10/1 Sergey Klochkov <klochkov@iqbuzz.ru
>> <mailto:klochkov@iqbuzz.ru>>
>>
>> Hello All,
>>
>> While trying to backup a database of relatively modest size (160 Gb)
>> I ran into the following issue:
>>
>> When I run
>> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>>
>> File /path/to/mydb.dmp does not appear (yes, I've checked
>> permissions and so on). pg_dump just begins to consume memory until
>> it eats up all avaliable RAM (96 Gb total on server, >64 Gb
>> available) and is killed by the oom killer.
>>
>> According to pg_stat_activity, pg_dump runs the following query
>>
>> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
>> lomowner) AS rolname, lomacl FROM pg_largeobject_metadata
>>
>> until it is killed.
>>
>> strace shows that pg_dump is constantly reading a large amount of
>> data from a UNIX socket. I suspect that it is the result of the
>> above query.
>>
>> There are >300000000 large objects in the database. Please don't ask
>> me why.
>>
>> I tried googling on this, and found mentions of pg_dump being killed
>> by oom killer, but I failed to find anything related to the huge
>> large objects number.
>>
>> Is there any method of working around this issue?
>>
>> Thanks in advance.
>>
>> OS: CentOS 6
>> PostgreSQL version: 9.2.1
>> 96 Gb RAM
>>
>> PostgreSQL configuration:
>>
>> listen_addresses = '*' # what IP address(es) to listen on;
>> port = 5432 # (change requires restart)
>> max_connections = 500 # (change requires restart)
>> shared_buffers = 16GB # min 128kB
>> temp_buffers = 64MB # min 800kB
>> work_mem = 512MB # min 64kB
>> maintenance_work_mem = 30000MB # min 1MB
>> checkpoint_segments = 70 # in logfile segments, min
>> 1, 16MB each
>> effective_cache_size = 50000MB
>> logging_collector = on # Enable capturing of stderr
>> and csvlog
>> log_directory = 'pg_log' # directory where log files
>> are written,
>> log_filename = 'postgresql-%a.log' # log file name pattern,
>> log_truncate_on_rotation = on # If on, an existing log
>> file of the
>> log_rotation_age = 1d # Automatic rotation of
>> logfiles will
>> log_rotation_size = 0 # Automatic rotation of
>> logfiles will
>> log_min_duration_statement = 5000
>> log_line_prefix = '%t' # special values:
>> autovacuum = on # Enable autovacuum
>> subprocess? 'on'
>> log_autovacuum_min_duration = 0 # -1 disables, 0 logs all
>> actions and
>> autovacuum_max_workers = 5 # max number of autovacuum
>> subprocesses
>> autovacuum_naptime = 5s # time between autovacuum
>> runs
>> autovacuum_vacuum_threshold = 25 # min number of row updates
>> before
>> autovacuum_vacuum_scale_factor = 0.1 # fraction of table size
>> before vacuum
>> autovacuum_vacuum_cost_delay = 7ms # default vacuum cost
>> delay for
>> autovacuum_vacuum_cost_limit = 1500 # default vacuum cost
>> limit for
>> datestyle = 'iso, dmy'
>> lc_monetary = 'ru_RU.UTF-8' # locale for
>> monetary formatting
>> lc_numeric = 'ru_RU.UTF-8' # locale for number
>> formatting
>> lc_time = 'ru_RU.UTF-8' # locale for time
>> formatting
>> default_text_search_config = 'pg_catalog.russian'
>>
>> --
>> Sergey Klochkov
>> klochkov@iqbuzz.ru <mailto:klochkov@iqbuzz.ru>
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
>> <mailto:pgsql-admin@postgresql.org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/__mailpref/pgsql-admin
>> <http://www.postgresql.org/mailpref/pgsql-admin;
>>
>>
>
--
Sergey Klochkov
klochkov@iqbuzz.ru
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
@ 2013-10-01 11:01 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-10-01 11:12 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 12:30 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects bricklen <bricklen@gmail.com>
3 siblings, 2 replies; 11+ messages in thread
From: Giuseppe Broccolo @ 2013-10-01 11:01 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Maybe you can performe your database changing some parameters properly:
>
> PostgreSQL configuration:
>
> listen_addresses = '*' # what IP address(es) to listen on;
> port = 5432 # (change requires restart)
> max_connections = 500 # (change requires restart)
Set it to 100, the highest value supported by PostgreSQL
> shared_buffers = 16GB # min 128kB
This value should not be higher than 8GB
> temp_buffers = 64MB # min 800kB
> work_mem = 512MB # min 64kB
> maintenance_work_mem = 30000MB # min 1MB
Given RAM 96GB, you could set it up to 4800MB
> checkpoint_segments = 70 # in logfile segments, min 1,
> 16MB each
> effective_cache_size = 50000MB
Given RAM 96GB, you could set it up to 80GB
>
Hope it can help.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 11:01 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
@ 2013-10-01 11:12 ` Sergey Klochkov <klochkov@iqbuzz.ru>
1 sibling, 0 replies; 11+ messages in thread
From: Sergey Klochkov @ 2013-10-01 11:12 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; +Cc: giuseppe.broccolo@2ndquadrant.it
No, it did not make any difference. And after looking through pg_dump.c
and pg_dump_sort.c, I cannot tell how it possibly could. See the
stacktrace that I've sent to the list.
Thanks.
On 01.10.2013 15:01, Giuseppe Broccolo wrote:
> Maybe you can performe your database changing some parameters properly:
>>
>> PostgreSQL configuration:
>>
>> listen_addresses = '*' # what IP address(es) to listen on;
>> port = 5432 # (change requires restart)
>> max_connections = 500 # (change requires restart)
> Set it to 100, the highest value supported by PostgreSQL
>> shared_buffers = 16GB # min 128kB
> This value should not be higher than 8GB
>> temp_buffers = 64MB # min 800kB
>> work_mem = 512MB # min 64kB
>> maintenance_work_mem = 30000MB # min 1MB
> Given RAM 96GB, you could set it up to 4800MB
>> checkpoint_segments = 70 # in logfile segments, min 1,
>> 16MB each
>> effective_cache_size = 50000MB
> Given RAM 96GB, you could set it up to 80GB
>>
>
> Hope it can help.
>
> Giuseppe.
>
--
Sergey Klochkov
klochkov@iqbuzz.ru
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 11:01 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
@ 2013-10-01 12:30 ` bricklen <bricklen@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: bricklen @ 2013-10-01 12:30 UTC (permalink / raw)
To: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>; +Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
On Tue, Oct 1, 2013 at 4:01 AM, Giuseppe Broccolo <
giuseppe.broccolo@2ndquadrant.it> wrote:
> Maybe you can performe your database changing some parameters properly:
>
> max_connections = 500 # (change requires restart)
>>
> Set it to 100, the highest value supported by PostgreSQL
>
Surely you mean that max_connections = 100 is the *default* ?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
@ 2013-10-01 12:49 ` Magnus Hagander <magnus@hagander.net>
2013-10-01 19:30 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Alejandro Brust <alejandrob@pasteleros.org.ar>
3 siblings, 1 reply; 11+ messages in thread
From: Magnus Hagander @ 2013-10-01 12:49 UTC (permalink / raw)
To: Sergey Klochkov <klochkov@iqbuzz.ru>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>
On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov <klochkov@iqbuzz.ru> wrote:
> Hello All,
>
> While trying to backup a database of relatively modest size (160 Gb) I ran
> into the following issue:
>
> When I run
> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>
> File /path/to/mydb.dmp does not appear (yes, I've checked permissions and so
> on). pg_dump just begins to consume memory until it eats up all avaliable
> RAM (96 Gb total on server, >64 Gb available) and is killed by the oom
> killer.
>
> According to pg_stat_activity, pg_dump runs the following query
>
> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = lomowner)
> AS rolname, lomacl FROM pg_largeobject_metadata
>
> until it is killed.
>
> strace shows that pg_dump is constantly reading a large amount of data from
> a UNIX socket. I suspect that it is the result of the above query.
>
> There are >300000000 large objects in the database. Please don't ask me why.
>
> I tried googling on this, and found mentions of pg_dump being killed by oom
> killer, but I failed to find anything related to the huge large objects
> number.
>
> Is there any method of working around this issue?
I think this problem comes from the fact that pg_dump treats each
large object as it's own item. See getBlobs() which allocates a
BlobInfo struct for each LO (and a DumpableObject if there are any,
but that's just one).
I assume the query (from that file):
SELECT oid, lomacl FROM pg_largeobject_metadata
returns 300000000 rows, which are then looped over?
I ran into a similar issue a few years ago with a client using a
32-bit version of pg_dump, and got it worked around by moving to
64-bit. Did unfortunately not have time to look at the underlying
issue.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 12:49 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Magnus Hagander <magnus@hagander.net>
@ 2013-10-01 19:30 ` Alejandro Brust <alejandrob@pasteleros.org.ar>
2013-10-02 14:44 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Alejandro Brust @ 2013-10-01 19:30 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Did U perform any vacuumdb / reindexdb before the Pg_dump?
El 01/10/2013 09:49, Magnus Hagander escribió:
> On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov <klochkov@iqbuzz.ru> wrote:
>> Hello All,
>>
>> While trying to backup a database of relatively modest size (160 Gb) I ran
>> into the following issue:
>>
>> When I run
>> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>>
>> File /path/to/mydb.dmp does not appear (yes, I've checked permissions and so
>> on). pg_dump just begins to consume memory until it eats up all avaliable
>> RAM (96 Gb total on server, >64 Gb available) and is killed by the oom
>> killer.
>>
>> According to pg_stat_activity, pg_dump runs the following query
>>
>> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = lomowner)
>> AS rolname, lomacl FROM pg_largeobject_metadata
>>
>> until it is killed.
>>
>> strace shows that pg_dump is constantly reading a large amount of data from
>> a UNIX socket. I suspect that it is the result of the above query.
>>
>> There are >300000000 large objects in the database. Please don't ask me why.
>>
>> I tried googling on this, and found mentions of pg_dump being killed by oom
>> killer, but I failed to find anything related to the huge large objects
>> number.
>>
>> Is there any method of working around this issue?
> I think this problem comes from the fact that pg_dump treats each
> large object as it's own item. See getBlobs() which allocates a
> BlobInfo struct for each LO (and a DumpableObject if there are any,
> but that's just one).
>
> I assume the query (from that file):
> SELECT oid, lomacl FROM pg_largeobject_metadata
>
> returns 300000000 rows, which are then looped over?
>
> I ran into a similar issue a few years ago with a client using a
> 32-bit version of pg_dump, and got it worked around by moving to
> 64-bit. Did unfortunately not have time to look at the underlying
> issue.
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 12:49 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Magnus Hagander <magnus@hagander.net>
2013-10-01 19:30 ` Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Alejandro Brust <alejandrob@pasteleros.org.ar>
@ 2013-10-02 14:44 ` Sergey Klochkov <klochkov@iqbuzz.ru>
0 siblings, 0 replies; 11+ messages in thread
From: Sergey Klochkov @ 2013-10-02 14:44 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; alejandrob@pasteleros.org.ar
I tried it out. It did not make any difference.
On 01.10.2013 23:30, Alejandro Brust wrote:
> Did U perform any vacuumdb / reindexdb before the Pg_dump?
>
>
> El 01/10/2013 09:49, Magnus Hagander escribió:
>> On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov <klochkov@iqbuzz.ru> wrote:
>>> Hello All,
>>>
>>> While trying to backup a database of relatively modest size (160 Gb) I ran
>>> into the following issue:
>>>
>>> When I run
>>> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>>>
>>> File /path/to/mydb.dmp does not appear (yes, I've checked permissions and so
>>> on). pg_dump just begins to consume memory until it eats up all avaliable
>>> RAM (96 Gb total on server, >64 Gb available) and is killed by the oom
>>> killer.
>>>
>>> According to pg_stat_activity, pg_dump runs the following query
>>>
>>> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = lomowner)
>>> AS rolname, lomacl FROM pg_largeobject_metadata
>>>
>>> until it is killed.
>>>
>>> strace shows that pg_dump is constantly reading a large amount of data from
>>> a UNIX socket. I suspect that it is the result of the above query.
>>>
>>> There are >300000000 large objects in the database. Please don't ask me why.
>>>
>>> I tried googling on this, and found mentions of pg_dump being killed by oom
>>> killer, but I failed to find anything related to the huge large objects
>>> number.
>>>
>>> Is there any method of working around this issue?
>> I think this problem comes from the fact that pg_dump treats each
>> large object as it's own item. See getBlobs() which allocates a
>> BlobInfo struct for each LO (and a DumpableObject if there are any,
>> but that's just one).
>>
>> I assume the query (from that file):
>> SELECT oid, lomacl FROM pg_largeobject_metadata
>>
>> returns 300000000 rows, which are then looped over?
>>
>> I ran into a similar issue a few years ago with a client using a
>> 32-bit version of pg_dump, and got it worked around by moving to
>> 64-bit. Did unfortunately not have time to look at the underlying
>> issue.
>>
>>
>
>
--
Sergey Klochkov
klochkov@iqbuzz.ru
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
@ 2013-10-11 07:24 ` luckyjackgao <luckyjackgao@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: luckyjackgao @ 2013-10-11 07:24 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Hello
I have encountered some issues of PG crash when dealing with too much
data.
It seems that PG tries to do its task as quckly as it can be and will use as
much resource as it can.
Later I tried cgroups to limit resource usage to avoid PG consuming too much
memory etc. too quickly.
And PG works fine.
I edited the following files:
/etc/cgconfig.conf
mount {
cpuset = /cgroup/cpuset;
cpu = /cgroup/cpu;
cpuacct = /cgroup/cpuacct;
memory = /cgroup/memory;
devices = /cgroup/devices;
freezer = /cgroup/freezer;
net_cls = /cgroup/net_cls;
blkio = /cgroup/blkio;
}
group test1 {
perm {
task{
uid=postgres;
gid=postgres;
}
admin{
uid=root;
gid=root;
}
} memory {
memory.limit_in_bytes=300M;
}
}
/etc/cgrules.conf
# End of file
postgres memory test1/
#
Then set service on and restart , then login as postgres
chkconfig cgconfig on
chkconfig cgred on
And I can find PG works under 300M memory limit.
Best Regards
jian gao
--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-2-pg-dump-out-of-memory-when-backuping-a-databa...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2013-10-11 07:24 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-01 09:07 PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 10:04 ` Jov <amutu@amutu.com>
2013-10-01 10:23 ` Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 10:46 ` Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 11:01 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-10-01 11:12 ` Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-01 12:30 ` bricklen <bricklen@gmail.com>
2013-10-01 12:49 ` Magnus Hagander <magnus@hagander.net>
2013-10-01 19:30 ` Alejandro Brust <alejandrob@pasteleros.org.ar>
2013-10-02 14:44 ` Sergey Klochkov <klochkov@iqbuzz.ru>
2013-10-11 07:24 ` luckyjackgao <luckyjackgao@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox