impala-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tim Armstrong (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (IMPALA-5160) Queries with a large number of small joins regress in terms of memory usage due to memory reservation
Date Tue, 27 Jun 2017 22:35:00 GMT

     [ https://issues.apache.org/jira/browse/IMPALA-5160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Tim Armstrong resolved IMPALA-5160.
-----------------------------------
       Resolution: Fixed
    Fix Version/s: Impala 2.10.0



IMPALA-5160: adjust spill buffer size based on planner estimates

Scale down the buffer size in hash joins and hash aggregations if
estimates indicate that the build side of the join is small.
This greatly reduces minimum memory requirements for joins in some
common cases, e.g. small dimension tables.

Currently this is not plumbed through to the backend and only takes
effect in planner tests.

Testing:
Added targeted planner tests for small/mid/large/unknown memory
requirements for aggregations and joins.

Change-Id: I57b5b4c528325d478c8a9b834a6bc5dedab54b5b
Reviewed-on: http://gerrit.cloudera.org:8080/6963
Reviewed-by: Tim Armstrong <tarmstrong@cloudera.com>
Tested-by: Impala Public Jenkins

> Queries with a large number of small joins regress in terms of memory usage due to memory
reservation
> -----------------------------------------------------------------------------------------------------
>
>                 Key: IMPALA-5160
>                 URL: https://issues.apache.org/jira/browse/IMPALA-5160
>             Project: IMPALA
>          Issue Type: Sub-task
>          Components: Backend
>    Affects Versions: Impala 2.9.0
>            Reporter: Mostafa Mokhtar
>            Assignee: Tim Armstrong
>             Fix For: Impala 2.10.0
>
>         Attachments: many_fragments_baseline.txt, many_fragments_memory_manager.txt
>
>
> Queries with a large number of small joins regress in terms of peak memory usage due
to memory reservations. 
> For the example below peak memory usage increased from 470MB to 8GB. 
> Query used
> {code}
> select /* +straight_join */ count(*) from
>   customer2 A join /* +broadcast */  customer2 B on A.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 C on c.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 D on d.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 E on e.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 F on f.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 G on g.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 H on h.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 I on i.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 J on j.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 K on k.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 L on l.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 M on m.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 N on n.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 O on o.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 P on p.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 R on R.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 S on S.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 T on T.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 U on U.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 V on V.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 W on W.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 X on X.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 Y on Y.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 Z on Z.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z1	 on Z1.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z2	 on Z2.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z3	 on Z3.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z4	 on Z4.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z5	 on Z5.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z6	 on Z6.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z7	 on Z7.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z8	 on Z8.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z9	 on Z9.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z10	 on Z10.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z11	 on Z11.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z12	 on Z12.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z13	 on Z13.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z14	 on Z14.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z15	 on Z15.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z16	 on Z16.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z17	 on Z17.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z18	 on Z18.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z19	 on Z19.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z20	 on Z20.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z21	 on Z21.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z22	 on Z22.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z23	 on Z23.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z24	 on Z24.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z25	 on Z25.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z26	 on Z26.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z27	 on Z27.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z28	 on Z28.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z29	 on Z29.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z30	 on Z30.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z31	 on Z31.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z32	 on Z32.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z33	 on Z33.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z34	 on Z34.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z35	 on Z35.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z36	 on Z36.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z37	 on Z37.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z38	 on Z38.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z39	 on Z39.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z40	 on Z40.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z41	 on Z41.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z42	 on Z42.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z43	 on Z43.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z44	 on z44.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z45	 on z45.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z46	 on z46.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z47	 on z47.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z48	 on z48.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z49	 on z49.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z50	 on z50.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z51	 on z51.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z52	 on z52.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z53	 on z53.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z54	 on z54.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z55	 on z55.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z56	 on z56.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z57	 on z57.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z58	 on z58.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z59	 on z59.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z60	 on z60.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z61	 on z61.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z62	 on z62.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z63	 on z63.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z64	 on z64.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z65	 on z65.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z66	 on z66.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z67	 on z67.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z68	 on z68.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z69	 on z69.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z70	 on z70.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z71	 on z71.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z72	 on z72.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z73	 on z73.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z74	 on z74.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z75	 on z75.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z76	 on z76.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z77	 on z77.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z78	 on z78.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z79	 on z79.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z80	 on z80.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z81	 on z81.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z82	 on z82.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z83	 on z83.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z84	 on z84.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z85	 on z85.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z86	 on z86.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z87	 on z87.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z88	 on z88.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z89	 on z89.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z90	 on z90.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z91	 on z91.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z92	 on z92.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z93	 on z93.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z94	 on z94.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z95	 on z95.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z96	 on z96.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z97	 on z97.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z98	 on z98.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z99	 on z99.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z100	 on z100.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z101	 on z101.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z102	 on z102.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z103	 on z103.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z104	 on z104.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z105	 on z105.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z106	 on z106.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z107	 on z107.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z108	 on z108.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z109	 on z109.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z110	 on z110.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z111	 on z111.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z112	 on z112.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z113	 on z113.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z114	 on z114.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z115	 on z115.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z116	 on z116.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z117	 on z117.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z118	 on z118.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z119	 on z119.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z120	 on z120.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z121	 on z121.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z122	 on z122.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z123	 on z123.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z124	 on z124.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z125	 on z125.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z126	 on z126.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z127	 on z127.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z128	 on z128.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z129	 on z129.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z130	 on z130.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z131	 on z131.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z132	 on z132.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z133	 on z133.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z134	 on z134.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z135	 on z135.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z136	 on z136.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z137	 on z137.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z138	 on z138.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z139	 on z139.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z140	 on z140.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z141	 on z141.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z142	 on z142.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z143	 on z143.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z144	 on z144.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z145	 on z145.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z146	 on z146.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z147	 on z147.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z148	 on z148.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z149	 on z149.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z150	 on z150.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z151	 on z151.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z152	 on z152.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z153	 on z153.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z154	 on z154.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z155	 on z155.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z156	 on z156.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z157	 on z157.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z158	 on z158.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z159	 on z159.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z160	 on z160.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z161	 on z161.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z162	 on z162.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z163	 on z163.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z164	 on z164.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z165	 on z165.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z166	 on z166.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z167	 on z167.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z168	 on z168.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z169	 on z169.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z170	 on z170.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z171	 on z171.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z172	 on z172.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z173	 on z173.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z174	 on z174.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z175	 on z175.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z176	 on z176.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z177	 on z177.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z178	 on z178.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z179	 on z179.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z180	 on z180.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z181	 on z181.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z182	 on z182.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z183	 on z183.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z184	 on z184.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z185	 on z185.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z186	 on z186.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z187	 on z187.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z188	 on z188.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z189	 on z189.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z190	 on z190.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z191	 on z191.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z192	 on z192.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z193	 on z193.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z194	 on z194.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z195	 on z195.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z196	 on z196.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z197	 on z197.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z198	 on z198.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z199	 on z199.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z200	 on z200.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z201	 on z201.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z202	 on z202.c_custkey = B.c_custkey
> join /* +broadcast */   customer2 z203	 on z203.c_custkey = B.c_custkey
> where B.c_custkey < 10
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message