BUG #11904: out of memory when scanning large number of partitions - Mailing list pgsql-bugs
| From | koposov@ast.cam.ac.uk |
|---|---|
| Subject | BUG #11904: out of memory when scanning large number of partitions |
| Date | |
| Msg-id | 20141107185824.2513.53433@wrigleys.postgresql.org Whole thread Raw |
| Responses |
Re: BUG #11904: out of memory when scanning large number of partitions
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 11904
Logged by: Sergey Koposov
Email address: koposov@ast.cam.ac.uk
PostgreSQL version: 9.3.5
Operating system: RHEL 6.6
Description:
Hi,
I have a large table (few Tb) partitioned in ~ 12000 smaller tables, using
inheritance from the parent one.
I noticed that when the user tried to query from the parent table
(not something we plan to do often), PG run out of memory (128 Gb on our
machine). It is particularly surprising that even after I repeated the
query simplifying it to
select 1 from photometry_parent where flux1>1e20;
and ensuring that NO rows satisfy the condition.
PG still ran out of memory.
Surely this looks like a bug ?
Below I put the memory context dump that PG put in the log.
I also put the schema of the table below.
Something that could be relevant -- I'm doing this on the hot standby
machine ( I didn't try this on our master ).
PG memory settings are:
shared_buffers = 20GB
temp_buffers = 1GB
work_mem = 2GB
maintenance_work_mem = 2GB
The machine was pretty idle during this test
Thanks,
Sergey
TopMemoryContext: 13585016 total in 1522 blocks; 100584 free (1495 chunks);
13484432 used
TopTransactionContext: 153663037488 total in 18328 blocks; 4854696 free
(17279 chunks); 153658182792 used
TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks);
20496 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
MessageContext: 394461344 total in 59 blocks; 195720 free (8 chunks);
394265624 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
smgr relation table: 4186112 total in 9 blocks; 1569520 free (31 chunks);
2616592 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
ExecutorState: 75587648 total in 19 blocks; 5775864 free (6 chunks);
69811784 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
............... repeated ~ 10000 times ...........
Relcache by OID: 1040384 total in 7 blocks; 34720 free (13 chunks);
1005664 used
CacheMemoryContext: 189971952 total in 12323 blocks; 5860144 free (4
chunks); 184111808 used
photometry12287_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
.... repeated for different photometryXXXX_pkey............................
pg_index_indrelid_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_inherits_parent_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_db_role_setting_databaseid_rol_index: 1024 total in 1 blocks; 64 free
(0 chunks); 960 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1736 free (2
chunks); 1336 used
pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_enum_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks);
1008 used
pg_foreign_server_oid_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1736 free (2
chunks); 1336 used
pg_cast_source_target_index: 1024 total in 1 blocks; 16 free (0 chunks);
1008 used
pg_language_name_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_collation_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1736 free (2 chunks);
1336 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1784 free (2 chunks);
1288 used
pg_opclass_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_event_trigger_evtname_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_event_trigger_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
824 used
pg_conversion_default_index: 3072 total in 2 blocks; 1784 free (2
chunks); 1288 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1736 free (2
chunks); 1336 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 16 free (0
chunks); 1008 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 64 free (0 chunks);
960 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_user_mapping_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
824 used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1784 free (2
chunks); 1288 used
pg_foreign_table_relid_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_type_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_constraint_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
824 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 64 free (0 chunks);
960 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 64 free (0 chunks);
960 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_operator_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
824 used
pg_amop_opr_fam_index: 3072 total in 2 blocks; 1736 free (2 chunks);
1336 used
pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1784 free (2
chunks); 1288 used
pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1784 free (2
chunks); 1288 used
pg_range_rngtypid_index: 1024 total in 1 blocks; 200 free (0 chunks);
824 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 64 free (0 chunks);
960 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks);
1008 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_class_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1736 free (2
chunks); 1336 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 16 free (0
chunks); 1008 used
pg_proc_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_language_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
used
pg_namespace_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1736 free (2 chunks);
1336 used
pg_foreign_server_name_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_conversion_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
824 used
pg_user_mapping_user_server_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_authid_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_tablespace_oid_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_database_datname_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 64 free (0
chunks); 960 used
pg_database_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_authid_rolname_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
MdSmgr: 1040384 total in 7 blocks; 238208 free (0 chunks); 802176 used
ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used
hba parser context: 15360 total in 4 blocks; 7760 free (3 chunks); 7600
used
LOCALLOCK hash: 4186112 total in 9 blocks; 1575680 free (32 chunks);
2610432 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
ErrorContext: 8192 total in 1 blocks; 8160 free (2 chunks); 32 used
postgres:c0:2014-11-07 18:18:46 GMT:16898:0 ERROR: out of memory
postgres:c0:2014-11-07 18:18:46 GMT:16898:0 DETAIL: Failed on request of
size 568.
postgres:c0:2014-11-07 18:18:46 GMT:16898:0 STATEMENT: select 1
from photometry_parent where flux1>1e20;
##################################################################
Table "public.photometry_parent"
Column | Type | Modifiers
----------------+-----------------------------+-----------
transitid | bigint |
sourceid | bigint |
healpixid | bigint |
flagsxm | smallint |
reftime | timestamp without time zone |
alpha | double precision |
dalpha | real |
delta | double precision |
ddelta | real |
flux0 | real |
flux1 | real |
flux2 | real |
flux3 | real |
flux4 | real |
flux5 | real |
flux6 | real |
flux7 | real |
flux8 | real |
flux9 | real |
fluxerror0 | real |
fluxerror1 | real |
fluxerror2 | real |
fluxerror3 | real |
fluxerror4 | real |
fluxerror5 | real |
fluxerror6 | real |
fluxerror7 | real |
fluxerror8 | real |
fluxerror9 | real |
ac0 | real |
ac1 | real |
ac2 | real |
ac3 | real |
ac4 | real |
ac5 | real |
ac6 | real |
ac7 | real |
ac8 | real |
ac9 | real |
acq0 | smallint |
acq1 | smallint |
acq2 | smallint |
acq3 | smallint |
acq4 | smallint |
acq5 | smallint |
acq6 | smallint |
acq7 | smallint |
acq8 | smallint |
acq9 | smallint |
gate0 | smallint |
gate1 | smallint |
gate2 | smallint |
gate3 | smallint |
gate4 | smallint |
gate5 | smallint |
gate6 | smallint |
gate7 | smallint |
gate8 | smallint |
gate9 | smallint |
windowclass | smallint |
objecttype | smallint |
fluxbparr | bytea |
fluxrparr | bytea |
flagspo | smallint |
fluxbpint | real |
fluxbpinterror | real |
fluxrpint | real |
fluxrpinterror | real |
flagspe | smallint |
provenance | bigint |
run | integer |
Foreign-key constraints:
"photometry_parent_provenance_fkey" FOREIGN KEY (provenance) REFERENCES
provenance(id)
Number of child tables: 12289 (Use \d+ to list them.)
pgsql-bugs by date: