agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Sergey Klochkov <klochkov@iqbuzz.ru>
To: Jov <amutu@amutu.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
Date: Tue, 01 Oct 2013 14:23:17 +0400
Message-ID: <524AA295.8050209@iqbuzz.ru> (raw)
In-Reply-To: <CADyrUxMiZ6TG48UdpZAkECPMx_hoiGq05dYabip9hwM-Y5_9tg@mail.gmail.com>
References: <524A90D1.5000109@iqbuzz.ru>
<CADyrUxMiZ6TG48UdpZAkECPMx_hoiGq05dYabip9hwM-Y5_9tg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
view thread (11+ messages) latest in thread
Message-ID: <524AA295.8050209@iqbuzz.ru>
Permalink: ../524AA295.8050209@iqbuzz.ru/
Also on: postgresql.org/message-id/524AA295.8050209@iqbuzz.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: klochkov@iqbuzz.ru, amutu@amutu.com, pgsql-admin@postgresql.org
Subject: Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects
In-Reply-To: <524AA295.8050209@iqbuzz.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox