BUG #7571: Query high memory usage

Started by Radovan Jablonovskyover 13 years ago6 messagesbugs

radovan.jablonovsky@replicon.com

over 13 years ago

The following bug has been logged on the website:

Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64
Description:

During checking our company database size we used query, which was not the
best to find out the tables/db size but should do the job. The query was
tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
alone without other activity. It consumed almost all RAM forced server to
use swap and after 1hour it was still running. The simplified version of
query used 20% of memory and finished after 1hour 8min.

The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.

query:
SELECT
schema_name,
sum(table_size)
FROM
(SELECT
pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;

top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10, 0.84
Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k buffers
Swap: 55043952k total, 85216k used, 54958736k free, 14036516k cached

Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres:
postgres db 10.0.1.10(49928) SELECT

Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
Simplified version of query:
SELECT
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint
FROM pg_tables
GROUP BY schemaname;

Pavel Stehule

pavel.stehule@gmail.com

over 13 years ago

In reply to: Radovan Jablonovsky (#1)

Re: BUG #7571: Query high memory usage

Hello

this situation is possible, when optimizer use HashAgg where should not use it.

Please, try to disable HashAgg - set enable_hashagg to off;

please, send EXPLAIN result

Regards

Pavel Stehule

2012/9/26 <radovan.jablonovsky@replicon.com>:

Show quoted text

The following bug has been logged on the website:

Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64
Description:

During checking our company database size we used query, which was not the
best to find out the tables/db size but should do the job. The query was
tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
alone without other activity. It consumed almost all RAM forced server to
use swap and after 1hour it was still running. The simplified version of
query used 20% of memory and finished after 1hour 8min.

The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.

query:
SELECT
schema_name,
sum(table_size)
FROM
(SELECT
pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;

top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10, 0.84
Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k buffers
Swap: 55043952k total, 85216k used, 54958736k free, 14036516k cached

Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres:
postgres db 10.0.1.10(49928) SELECT

Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
Simplified version of query:
SELECT
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint
FROM pg_tables
GROUP BY schemaname;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Melese Tesfaye

mtesfaye@gmail.com

over 13 years ago

In reply to: Pavel Stehule (#2)

Re: BUG #7571: Query high memory usage

Thanks Pavel,
Setting enable_hashagg to off didn't resolve the issue.
Please find the explain as well as query results after "set
enable_hashagg=off;"

mtesfaye@[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE  A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;
+-----------------------------------------------------------------------------------------------------------+
|                                                QUERY
PLAN                                                 |
+-----------------------------------------------------------------------------------------------------------+
| Unique  (cost=1354.62..1354.66 rows=4
width=13)                                                           |
|   ->  Sort  (cost=1354.62..1354.63 rows=4
width=13)                                                       |
|         Sort Key: a.pnr_id, a.nam_id,
a.pty_num                                                           |
|         ->  Merge Join  (cost=1084.06..1354.58 rows=4
width=13)                                           |
|               Merge Cond: (table2_t.pnr_id =
a.pnr_id)                                                    |
|               ->  Unique  (cost=1084.06..1198.67 rows=11461
width=16)                                     |
|                     ->  Sort  (cost=1084.06..1112.72 rows=11461
width=16)                                 |
|                           Sort Key: table2_t.pnr_id, table2_t.itn_id,
table2_t.departure_date_time        |
|                           ->  Seq Scan on table2_t  (cost=0.00..311.34
rows=11461 width=16)               |
|                                 Filter: (departure_date_time >=
'2012-09-26'::date)                       |
|               ->  Index Scan using table1_t_pnr_id_idx1 on table1_t a
(cost=0.00..12.60 rows=4 width=13) |
|                     Index Cond: (pnr_id = ANY
('{1801,2056}'::integer[]))                                 |
+-----------------------------------------------------------------------------------------------------------+
(12 rows)

Time: 5.889 ms

mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| on |
+----------------+
(1 row)

Time: 0.136 ms

mtesfaye@[local](test_db)=# set enable_hashagg=off;
SET
Time: 0.203 ms
mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| off |
+----------------+
(1 row)

Time: 0.131 ms

mtesfaye@[local](test_db)=# SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;
+--------+--------+---------+
| pnr_id | nam_id | pty_num |
+--------+--------+---------+
| 1801 | 3359 | 1 |
| 1801 | 3360 | 1 |
| 1801 | 3361 | 1 |
| 1801 | 3362 | 1 |
+--------+--------+---------+
(4 rows)

Time: 8.452 ms

On Thu, Sep 27, 2012 at 3:54 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Show quoted text

Hello

this situation is possible, when optimizer use HashAgg where should not
use it.

Please, try to disable HashAgg - set enable_hashagg to off;

please, send EXPLAIN result

Regards

Pavel Stehule

2012/9/26 <radovan.jablonovsky@replicon.com>:

The following bug has been logged on the website:

Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64
Description:

During checking our company database size we used query, which was not

the

best to find out the tables/db size but should do the job. The query was
tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
alone without other activity. It consumed almost all RAM forced server to
use swap and after 1hour it was still running. The simplified version of
query used 20% of memory and finished after 1hour 8min.

The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.

query:
SELECT
schema_name,
sum(table_size)
FROM
(SELECT
pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as

database_size

FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;

top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10,

0.84

Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k buffers
Swap: 55043952k total, 85216k used, 54958736k free, 14036516k cached

Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres:
postgres db 10.0.1.10(49928) SELECT

Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
Simplified version of query:
SELECT
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint
FROM pg_tables
GROUP BY schemaname;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Pavel Stehule

pavel.stehule@gmail.com

over 13 years ago

In reply to: Melese Tesfaye (#3)

Re: BUG #7571: Query high memory usage

Hello

you should to run this query on real data - and if it works now, then
send EXPLAIN ANALYZE result, please

Pavel

2012/9/27 Melese Tesfaye <mtesfaye@gmail.com>:

Show quoted text

Thanks Pavel,
Setting enable_hashagg to off didn't resolve the issue.
Please find the explain as well as query results after "set
enable_hashagg=off;"
mtesfaye@[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE  A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;
+-----------------------------------------------------------------------------------------------------------+
|                                                QUERY PLAN
|
+-----------------------------------------------------------------------------------------------------------+
| Unique  (cost=1354.62..1354.66 rows=4 width=13)
|
|   ->  Sort  (cost=1354.62..1354.63 rows=4 width=13)
|
|         Sort Key: a.pnr_id, a.nam_id, a.pty_num
|
|         ->  Merge Join  (cost=1084.06..1354.58 rows=4 width=13)
|
|               Merge Cond: (table2_t.pnr_id = a.pnr_id)
|
|               ->  Unique  (cost=1084.06..1198.67 rows=11461 width=16)
|
|                     ->  Sort  (cost=1084.06..1112.72 rows=11461 width=16)
|
|                           Sort Key: table2_t.pnr_id, table2_t.itn_id,
table2_t.departure_date_time        |
|                           ->  Seq Scan on table2_t  (cost=0.00..311.34
rows=11461 width=16)               |
|                                 Filter: (departure_date_time >=
'2012-09-26'::date)                       |
|               ->  Index Scan using table1_t_pnr_id_idx1 on table1_t a
(cost=0.00..12.60 rows=4 width=13) |
|                     Index Cond: (pnr_id = ANY ('{1801,2056}'::integer[]))
|
+-----------------------------------------------------------------------------------------------------------+
(12 rows)
Time: 5.889 ms

mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| on |
+----------------+
(1 row)

Time: 0.136 ms

mtesfaye@[local](test_db)=# set enable_hashagg=off;
SET
Time: 0.203 ms
mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| off |
+----------------+
(1 row)

Time: 0.131 ms

mtesfaye@[local](test_db)=# SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;
+--------+--------+---------+
| pnr_id | nam_id | pty_num |
+--------+--------+---------+
| 1801 | 3359 | 1 |
| 1801 | 3360 | 1 |
| 1801 | 3361 | 1 |
| 1801 | 3362 | 1 |
+--------+--------+---------+
(4 rows)

Time: 8.452 ms

On Thu, Sep 27, 2012 at 3:54 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

this situation is possible, when optimizer use HashAgg where should not
use it.

Please, try to disable HashAgg - set enable_hashagg to off;

please, send EXPLAIN result

Regards

Pavel Stehule

2012/9/26 <radovan.jablonovsky@replicon.com>:

The following bug has been logged on the website:

Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64
Description:

During checking our company database size we used query, which was not
the
best to find out the tables/db size but should do the job. The query was
tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
alone without other activity. It consumed almost all RAM forced server
to
use swap and after 1hour it was still running. The simplified version of
query used 20% of memory and finished after 1hour 8min.

The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.

query:
SELECT
schema_name,
sum(table_size)
FROM
(SELECT
pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as
database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;

top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10,
0.84
Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k buffers
Swap: 55043952k total, 85216k used, 54958736k free, 14036516k cached

Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres:
postgres db 10.0.1.10(49928) SELECT

Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
Simplified version of query:
SELECT
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint
FROM pg_tables
GROUP BY schemaname;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Radovan Jablonovsky

radovan.jablonovsky@replicon.com

over 13 years ago

In reply to: Pavel Stehule (#4)

Re: BUG #7571: Query high memory usage

Hi Pavel,

Here are the test data with set enable_hashagg to off. It does not looks
like improvement. Query was running for 30min without returning result set.

db=> set enable_hashagg=off;
SET
db=> explain
db-> SELECT
db-> schema_name,
db-> sum(table_size)
db-> FROM
db-> (SELECT
db(> pg_catalog.pg_namespace.nspname as schema_name,
db(> pg_relation_size(pg_catalog.pg_class.oid) as table_size,
db(> sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as
database_size
db(> FROM pg_catalog.pg_class
db(> JOIN pg_catalog.pg_namespace
db(> ON relnamespace = pg_catalog.pg_namespace.oid
db(> ) t
db-> GROUP BY schema_name, database_size;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=725540.59..756658.18 rows=40000 width=104)
-> Sort (cost=725540.59..733219.99 rows=3071759 width=104)
Sort Key: pg_namespace.nspname,
(sum(pg_relation_size((pg_class.oid)::regclass, 'main'::text)) OVER (?))
-> WindowAgg (cost=120.98..243838.73 rows=3071759 width=68)
-> Hash Join (cost=120.98..190082.95 rows=3071759 width=68)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
-> Seq Scan on pg_class (cost=0.00..143885.59
rows=3071759 width=8)
-> Hash (cost=90.99..90.99 rows=2399 width=68)
-> Seq Scan on pg_namespace (cost=0.00..90.99
rows=2399 width=68)
(9 rows)

Data from top after 30 min of query run with hashagg set off:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2235 postgres 25 0 27.5g 23g 4.6g R 95.1 75.2 31:39.81
postgres: aspuser aspdata 10.0.2.67(52716) SELECT

Radovan

On Wed, Sep 26, 2012 at 10:15 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Show quoted text

Hello

you should to run this query on real data - and if it works now, then
send EXPLAIN ANALYZE result, please

Pavel

2012/9/27 Melese Tesfaye <mtesfaye@gmail.com>:

Thanks Pavel,
Setting enable_hashagg to off didn't resolve the issue.
Please find the explain as well as query results after "set
enable_hashagg=off;"

mtesfaye@[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;

+-----------------------------------------------------------------------------------------------------------+

| QUERY PLAN
|

+-----------------------------------------------------------------------------------------------------------+

| Unique (cost=1354.62..1354.66 rows=4 width=13)
|
| -> Sort (cost=1354.62..1354.63 rows=4 width=13)
|
| Sort Key: a.pnr_id, a.nam_id, a.pty_num
|
| -> Merge Join (cost=1084.06..1354.58 rows=4 width=13)
|
| Merge Cond: (table2_t.pnr_id = a.pnr_id)
|
| -> Unique (cost=1084.06..1198.67 rows=11461 width=16)
|
| -> Sort (cost=1084.06..1112.72 rows=11461

width=16)

|
| Sort Key: table2_t.pnr_id, table2_t.itn_id,
table2_t.departure_date_time |
| -> Seq Scan on table2_t (cost=0.00..311.34
rows=11461 width=16) |
| Filter: (departure_date_time >=
'2012-09-26'::date) |
| -> Index Scan using table1_t_pnr_id_idx1 on table1_t a
(cost=0.00..12.60 rows=4 width=13) |
| Index Cond: (pnr_id = ANY

('{1801,2056}'::integer[]))

|

+-----------------------------------------------------------------------------------------------------------+

(12 rows)

Time: 5.889 ms

mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| on |
+----------------+
(1 row)

Time: 0.136 ms

mtesfaye@[local](test_db)=# set enable_hashagg=off;
SET
Time: 0.203 ms
mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| off |
+----------------+
(1 row)

Time: 0.131 ms

mtesfaye@[local](test_db)=# SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;
+--------+--------+---------+
| pnr_id | nam_id | pty_num |
+--------+--------+---------+
| 1801 | 3359 | 1 |
| 1801 | 3360 | 1 |
| 1801 | 3361 | 1 |
| 1801 | 3362 | 1 |
+--------+--------+---------+
(4 rows)

Time: 8.452 ms

On Thu, Sep 27, 2012 at 3:54 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

this situation is possible, when optimizer use HashAgg where should not
use it.

Please, try to disable HashAgg - set enable_hashagg to off;

please, send EXPLAIN result

Regards

Pavel Stehule

2012/9/26 <radovan.jablonovsky@replicon.com>:

The following bug has been logged on the website:

Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64
Description:

During checking our company database size we used query, which was not
the
best to find out the tables/db size but should do the job. The query

was

tested on server with 32GB of RAM, 2 CPU with 4 cores and it was

running

alone without other activity. It consumed almost all RAM forced server
to
use swap and after 1hour it was still running. The simplified version

of

query used 20% of memory and finished after 1hour 8min.

The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.

query:
SELECT
schema_name,
sum(table_size)
FROM
(SELECT
pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as
database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;

top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10,
0.84
Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k

buffers

Swap: 55043952k total, 85216k used, 54958736k free, 14036516k

cached

Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres:
postgres db 10.0.1.10(49928) SELECT

Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
Simplified version of query:
SELECT
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint
FROM pg_tables
GROUP BY schemaname;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Pavel Stehule

pavel.stehule@gmail.com

over 13 years ago

In reply to: Radovan Jablonovsky (#5)

Re: BUG #7571: Query high memory usage

2012/9/27 Radovan Jablonovsky <radovan.jablonovsky@replicon.com>:

Hi Pavel,

Here are the test data with set enable_hashagg to off. It does not looks
like improvement. Query was running for 30min without returning result set.

so maybe it is PostgreSQL bug - probably window function doesn't reset
some memory context and then execution is memory expensive

Regards

Pavel

Show quoted text

db=> set enable_hashagg=off;
SET
db=> explain
db-> SELECT
db-> schema_name,
db-> sum(table_size)
db-> FROM
db-> (SELECT
db(> pg_catalog.pg_namespace.nspname as schema_name,
db(> pg_relation_size(pg_catalog.pg_class.oid) as table_size,
db(> sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as
database_size
db(> FROM pg_catalog.pg_class
db(> JOIN pg_catalog.pg_namespace
db(> ON relnamespace = pg_catalog.pg_namespace.oid
db(> ) t
db-> GROUP BY schema_name, database_size;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=725540.59..756658.18 rows=40000 width=104)
-> Sort (cost=725540.59..733219.99 rows=3071759 width=104)
Sort Key: pg_namespace.nspname,
(sum(pg_relation_size((pg_class.oid)::regclass, 'main'::text)) OVER (?))
-> WindowAgg (cost=120.98..243838.73 rows=3071759 width=68)
-> Hash Join (cost=120.98..190082.95 rows=3071759 width=68)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
-> Seq Scan on pg_class (cost=0.00..143885.59
rows=3071759 width=8)
-> Hash (cost=90.99..90.99 rows=2399 width=68)
-> Seq Scan on pg_namespace (cost=0.00..90.99
rows=2399 width=68)
(9 rows)

Data from top after 30 min of query run with hashagg set off:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2235 postgres 25 0 27.5g 23g 4.6g R 95.1 75.2 31:39.81
postgres: aspuser aspdata 10.0.2.67(52716) SELECT

Radovan

On Wed, Sep 26, 2012 at 10:15 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

you should to run this query on real data - and if it works now, then
send EXPLAIN ANALYZE result, please

Pavel

2012/9/27 Melese Tesfaye <mtesfaye@gmail.com>:

Thanks Pavel,
Setting enable_hashagg to off didn't resolve the issue.
Please find the explain as well as query results after "set
enable_hashagg=off;"

mtesfaye@[local](test_db)=# EXPLAIN SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;

+-----------------------------------------------------------------------------------------------------------+
| QUERY PLAN
|

+-----------------------------------------------------------------------------------------------------------+
| Unique (cost=1354.62..1354.66 rows=4 width=13)
|
| -> Sort (cost=1354.62..1354.63 rows=4 width=13)
|
| Sort Key: a.pnr_id, a.nam_id, a.pty_num
|
| -> Merge Join (cost=1084.06..1354.58 rows=4 width=13)
|
| Merge Cond: (table2_t.pnr_id = a.pnr_id)
|
| -> Unique (cost=1084.06..1198.67 rows=11461 width=16)
|
| -> Sort (cost=1084.06..1112.72 rows=11461
width=16)
|
| Sort Key: table2_t.pnr_id, table2_t.itn_id,
table2_t.departure_date_time |
| -> Seq Scan on table2_t (cost=0.00..311.34
rows=11461 width=16) |
| Filter: (departure_date_time >=
'2012-09-26'::date) |
| -> Index Scan using table1_t_pnr_id_idx1 on table1_t a
(cost=0.00..12.60 rows=4 width=13) |
| Index Cond: (pnr_id = ANY
('{1801,2056}'::integer[]))
|

+-----------------------------------------------------------------------------------------------------------+
(12 rows)

Time: 5.889 ms

mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| on |
+----------------+
(1 row)

Time: 0.136 ms

mtesfaye@[local](test_db)=# set enable_hashagg=off;
SET
Time: 0.203 ms
mtesfaye@[local](test_db)=# show enable_hashagg;
+----------------+
| enable_hashagg |
+----------------+
| off |
+----------------+
(1 row)

Time: 0.131 ms

mtesfaye@[local](test_db)=# SELECT DISTINCT(A.*)
test_db-# FROM table1_t A LEFT JOIN table2_v B
test_db-# ON A.pnr_id=B.pnr_id
test_db-# WHERE A.pnr_id IN(1801,2056) AND
B.departure_date_time>=DATE('2012-09-26')
test_db-# ORDER BY pnr_id ASC,nam_id ASC;
+--------+--------+---------+
| pnr_id | nam_id | pty_num |
+--------+--------+---------+
| 1801 | 3359 | 1 |
| 1801 | 3360 | 1 |
| 1801 | 3361 | 1 |
| 1801 | 3362 | 1 |
+--------+--------+---------+
(4 rows)

Time: 8.452 ms

On Thu, Sep 27, 2012 at 3:54 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

this situation is possible, when optimizer use HashAgg where should not
use it.

Please, try to disable HashAgg - set enable_hashagg to off;

please, send EXPLAIN result

Regards

Pavel Stehule

2012/9/26 <radovan.jablonovsky@replicon.com>:

The following bug has been logged on the website:

Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64
Description:

During checking our company database size we used query, which was
not
the
best to find out the tables/db size but should do the job. The query
was
tested on server with 32GB of RAM, 2 CPU with 4 cores and it was
running
alone without other activity. It consumed almost all RAM forced
server
to
use swap and after 1hour it was still running. The simplified version
of
query used 20% of memory and finished after 1hour 8min.

The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.

query:
SELECT
schema_name,
sum(table_size)
FROM
(SELECT
pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as
database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;

top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10,
0.84
Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi,
0.0%si,
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k
buffers
Swap: 55043952k total, 85216k used, 54958736k free, 14036516k
cached

Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01
postgres:
postgres db 10.0.1.10(49928) SELECT

Simplified version of query uses pg_tables. It has 0.5mil
rows/tables.
Simplified version of query:
SELECT
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint
FROM pg_tables
GROUP BY schemaname;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs