Re: OOM Killing on Docker while ANALYZE running

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(at)alvh(dot)no-ip(dot)org>
Sent: Thursday, January 25, 2018 4:47 PM
To: Jorge Daniel
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org; pgsql-admin(at)postgresql(dot)org; dyuryeva(at)medallia(dot)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