pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOOM Killing on Docker while ANALYZE running
6+ messages / 5 participants
[nested] [flat]
* OOM Killing on Docker while ANALYZE running
@ 2018-01-25 19:16 Jorge Daniel <elgaita@hotmail.com>
2018-01-25 19:47 ` Re: OOM Killing on Docker while ANALYZE running Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 6+ messages in thread
From: Jorge Daniel @ 2018-01-25 19:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-admin; dyuryeva@medallia.com <dyuryeva@medallia.com>
Hi guys , I'm dealing with OOM killing on Postgresql 9.4.8 running on docker ,
Docker : Docker version 17.05.0,
"resources_template = Resources(cpu = 1,ram = 1 * GB, disk = 1 * GB)"
Using Ceph volumes as storages
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 5079992
max total shared memory (kbytes) = 5079992
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 32000
max semaphores per array = 32000
max semaphores system wide = 1024000000
max ops per semop call = 500
semaphore max value = 32767
------ Messages Limits --------
max queues system wide = 32000
max size of message (bytes) = 8192
default max size of queue (bytes) = 16384
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x0052e2c1 0 postgres 600 56 5
0x6b8b4567 32769 postgres 600 2028 5
postgres@d90b66757bb1:~$ ipcs -a
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x0052e2c1 0 postgres 600 56 5
0x6b8b4567 32769 postgres 600 2028 5
------ Semaphore Arrays --------
key semid owner perms nsems
0x0052e2c1 0 postgres 600 17
0x0052e2c2 32769 postgres 600 17
0x0052e2c3 65538 postgres 600 17
0x0052e2c4 98307 postgres 600 17
0x0052e2c5 131076 postgres 600 17
0x0052e2c6 163845 postgres 600 17
0x0052e2c7 196614 postgres 600 17
------ Message Queues --------
key msqid owner perms used-bytes messages
The postgres installation is :
PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: OOM Killing on Docker while ANALYZE running
2018-01-25 19:16 OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
@ 2018-01-25 19:47 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-01-25 20:48 ` Re: OOM Killing on Docker while ANALYZE running Tom Lane <tgl@sss.pgh.pa.us>
2018-01-25 20:58 ` Re: OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Alvaro Herrera @ 2018-01-25 19:47 UTC (permalink / raw)
To: Jorge Daniel <elgaita@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-admin; dyuryeva@medallia.com <dyuryeva@medallia.com>
Jorge Daniel wrote:
> Hi guys , I'm dealing with OOM killing on Postgresql 9.4.8 running on docker ,
What is your statistics target? analyze is supposed to acquire samples
of the data, not the whole table ...
> pgbench=# analyze verbose pgbench_accounts;
> INFO: analyzing "public.pgbench_accounts"
> INFO: "pgbench_accounts": scanned 1639345 of 1639345 pages, containing 100000000 live rows and 0 dead rows; 3000000 rows in sample, 100000000 estimated total rows
Here it seems to be saying that it reads all 1.6 million pages ...
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: OOM Killing on Docker while ANALYZE running
2018-01-25 19:16 OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
2018-01-25 19:47 ` Re: OOM Killing on Docker while ANALYZE running Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2018-01-25 20:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-01-26 02:51 ` Re: OOM Killing on Docker while ANALYZE running Ray Stell <stellr@vt.edu>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2018-01-25 20:48 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Jorge Daniel <elgaita@hotmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-admin; dyuryeva@medallia.com <dyuryeva@medallia.com>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Jorge Daniel wrote:
>> Hi guys , I'm dealing with OOM killing on Postgresql 9.4.8 running on docker ,
> What is your statistics target? analyze is supposed to acquire samples
> of the data, not the whole table ...
>> pgbench=# analyze verbose pgbench_accounts;
>> INFO: analyzing "public.pgbench_accounts"
>> INFO: "pgbench_accounts": scanned 1639345 of 1639345 pages, containing 100000000 live rows and 0 dead rows; 3000000 rows in sample, 100000000 estimated total rows
The standard setting of default_statistics_target would only result in
trying to acquire a 30000-row sample, so the problem here is having
cranked that up beyond what the machine can sustain. AFAIR we do not
consider maintenance_work_mem as imposing a limit on the sample size.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: OOM Killing on Docker while ANALYZE running
2018-01-25 19:16 OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
2018-01-25 19:47 ` Re: OOM Killing on Docker while ANALYZE running Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-01-25 20:48 ` Re: OOM Killing on Docker while ANALYZE running Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-01-26 02:51 ` Ray Stell <stellr@vt.edu>
2018-01-26 04:15 ` Re: OOM Killing on Docker while ANALYZE running Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 6+ messages in thread
From: Ray Stell @ 2018-01-26 02:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/25/18 3:48 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> Jorge Daniel wrote:
>>> Hi guys , I'm dealing with OOM killing on Postgresql 9.4.8 running on docker
Mike Coleman's talk at DockerCon17 is here:
http://training.play-with-docker.com/ops-stage1/
At about the 38 minute mark, on the topic of transitioning enterprise
apps to docker:
"define your guardrails, leave the db out in the VM." Mike is a docker
evangelist.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: OOM Killing on Docker while ANALYZE running
2018-01-25 19:16 OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
2018-01-25 19:47 ` Re: OOM Killing on Docker while ANALYZE running Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-01-25 20:48 ` Re: OOM Killing on Docker while ANALYZE running Tom Lane <tgl@sss.pgh.pa.us>
2018-01-26 02:51 ` Re: OOM Killing on Docker while ANALYZE running Ray Stell <stellr@vt.edu>
@ 2018-01-26 04:15 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Kirkwood @ 2018-01-26 04:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 26/01/18 15:51, Ray Stell wrote:
> On 1/25/18 3:48 PM, Tom Lane wrote:
>
>> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>>> Jorge Daniel wrote:
>>>> Hi guys , I'm dealing with OOM killing on Postgresql 9.4.8 running
>>>> on docker
>
> Mike Coleman's talk at DockerCon17 is here:
> http://training.play-with-docker.com/ops-stage1/
> At about the 38 minute mark, on the topic of transitioning enterprise
> apps to docker:
> "define your guardrails, leave the db out in the VM." Mike is a
> docker evangelist.
>
>
Hmm - I don't think docker per se is the problem here - that
default_statistics_target of 10000 is the more likely culprit. A minimal
allowance of resources (e.g memory in this case) combined with hugely
over spec'ed memory use (the default_statistics_target setting) is gonna
trigger OOM for any environment.
regards
Mark
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: OOM Killing on Docker while ANALYZE running
2018-01-25 19:16 OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
2018-01-25 19:47 ` Re: OOM Killing on Docker while ANALYZE running Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2018-01-25 20:58 ` Jorge Daniel <elgaita@hotmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jorge Daniel @ 2018-01-25 20:58 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-admin; dyuryeva@medallia.com <dyuryeva@medallia.com>
HI Alvaro:
These are our values
default_statistics_target
---------------------------
10000
relpages | reltuples
----------+-----------
1639345 | 1e+08
Table "public.pgbench_accounts"
Column | Type | Modifiers | Storage | Stats target | Description
----------+---------------+-----------+----------+--------------+-------------
aid | integer | not null | plain | |
bid | integer | | plain | |
abalance | integer | | plain | |
filler | character(84) | | extended | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Options: fillfactor=100
I guess you hit the knotch:
pgbench=# set default_statistics_target =10;
SET
pgbench=# analyze verbose pgbench_accounts ;
INFO: analyzing "public.pgbench_accounts"
INFO: "pgbench_accounts": scanned 3000 of 1639345 pages, containing 183000 live rows and 0 dead rows; 3000 rows in sample, 100000000 estimated total rows
ANALYZE
pgbench=# set default_statistics_target =100;
SET
pgbench=# analyze verbose pgbench_accounts ;
INFO: analyzing "public.pgbench_accounts"
INFO: "pgbench_accounts": scanned 30000 of 1639345 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 100000001 estimated total rows
ANALYZE
pgbench=# set default_statistics_target =1000;
SET
pgbench=# analyze verbose pgbench_accounts ;
INFO: analyzing "public.pgbench_accounts"
INFO: "pgbench_accounts": scanned 300000 of 1639345 pages, containing 18300000 live rows and 0 dead rows; 300000 rows in sample, 100000008 estimated total rows
ANALYZE
pgbench=# set default_statistics_target =10000;
SET
pgbench=# analyze verbose pgbench_accounts ;
INFO: analyzing "public.pgbench_accounts"
INFO: "pgbench_accounts": scanned 1639345 of 1639345 pages, containing 100000000 live rows and 0 dead rows; 3000000 rows in sample, 100000000 estimated total rows
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Kind regards
Jorge Daniel Fernandez
________________________________
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Thursday, January 25, 2018 4:47 PM
To: Jorge Daniel
Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org; dyuryeva@medallia.com
Subject: Re: OOM Killing on Docker while ANALYZE running
Jorge Daniel wrote:
> Hi guys , I'm dealing with OOM killing on Postgresql 9.4.8 running on docker ,
What is your statistics target? analyze is supposed to acquire samples
of the data, not the whole table ...
> pgbench=# analyze verbose pgbench_accounts;
> INFO: analyzing "public.pgbench_accounts"
> INFO: "pgbench_accounts": scanned 1639345 of 1639345 pages, containing 100000000 live rows and 0 dead rows; 3000000 rows in sample, 100000000 estimated total rows
Here it seems to be saying that it reads all 1.6 million pages ...
--
Álvaro Herrera https://www.2ndQuadrant.com/
Professional PostgreSQL | 2ndQuadrant<https://www.2ndquadrant.com/;
www.2ndquadrant.com
Stay in touch with us. Subscribe to our monthly newsletter to hear the latest developments from 2ndQuadrant and related technologies. We’ll also send you any ...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-01-26 04:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-25 19:16 OOM Killing on Docker while ANALYZE running Jorge Daniel <elgaita@hotmail.com>
2018-01-25 19:47 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-01-25 20:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-01-26 02:51 ` Ray Stell <stellr@vt.edu>
2018-01-26 04:15 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-25 20:58 ` Jorge Daniel <elgaita@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox