ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ilya Kasnacheev <ilya.kasnach...@gmail.com>
Subject Re: Query Execution very slow
Date Fri, 28 Dec 2018 16:06:10 GMT
Hello!

There's a docs page at
https://apacheignite.readme.io/docs/configuration-parameters
This is because Ignite's query planner sometimes plans joins unoptimally
and you have to help it.

Regarding second sql, additional investigation is needed. Can you show
EXPLAIN after you apply enforceJoinOrder?

Regards,
-- 
Ilya Kasnacheev


пт, 28 дек. 2018 г. в 18:59, Prasad Bhalerao <prasadbhalerao1983@gmail.com>:

> Hi,
>
> After setting enforceJoinOrder to true it worked. Can you please explain
> how did it work... pushing the data to temp table first and then
> enforeOrder?
>
> Is it documented in ignite docs?
>
> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
> getAffectedIPRange_3 :: *TimeTakenToComplete=10 ::* Size=1295
>
>
> *But second sql is still taking time. The only difference is it has
> ipStart and ipEnd filter in where clause.*
>
> getAffectedIPRange_2 :: SQL_2=SELECT ipv4agd.id, ipv4agd.assetGroupId,
> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
> WHERE subscriptionId = ? AND (ipStart <= ? AND ipEnd >= ?) ORDER BY
> ipv4agd.assetGroupId
> getAffectedIPRange_2 :: *TimeTakenToComplete=25436* :: Size=1260
>
> Thanks,
> Prasad
>
> On Fri, Dec 28, 2018 at 9:02 PM Ilya Kasnacheev <ilya.kasnacheev@gmail.com>
> wrote:
>
>> Hello!
>>
>> Did you set enforceJoinOrder to true?
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пт, 28 дек. 2018 г. в 18:19, Prasad Bhalerao <
>> prasadbhalerao1983@gmail.com>:
>>
>>> Hi,
>>>
>>> I tried your suggestion but it did not work. It is taking 22.8 seconds.
>>>
>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>
>>> getAffectedIPRange_3 :: TimeTakenToComplete=22891 :: Size=1295
>>> Thanks,
>>> Prasad
>>>
>>> On Fri, Dec 28, 2018 at 8:32 PM Ilya Kasnacheev <
>>> ilya.kasnacheev@gmail.com> wrote:
>>>
>>>> Hello!
>>>>
>>>> I have created a PR for you:
>>>> https://github.com/prasadbhalerao1983/IgniteTestPrj/pull/1
>>>> With it, I can see:
>>>>
>>>> getAffectedIPRange_3 :: SQL_3=SELECT ipv4agd.id, ipv4agd.assetGroupId,
>>>> ipv4agd.ipStart, ipv4agd.ipEnd FROM TABLE (assetGroupId bigint = ? ) temp
>>>> JOIN IpV4AssetGroupData ipv4agd ON ipv4agd.assetGroupId = temp.assetGroupId
>>>> WHERE subscriptionId = ? ORDER BY ipv4agd.assetGroupId
>>>> getAffectedIPRange_3 :: TimeTakenToComplete=11 :: Size=1295
>>>>
>>>> Regards,
>>>> --
>>>> Ilya Kasnacheev
>>>>
>>>>
>>>> пт, 28 дек. 2018 г. в 17:46, Prasad Bhalerao <
>>>> prasadbhalerao1983@gmail.com>:
>>>>
>>>>> Can someone from community help me with t
>>>>>
>>>>> I have created a reproducer and uploaded it to GitHub. I have created
>>>>> 3 cases to test the sql execution time.
>>>>>
>>>>> Please run *IgniteQueryTester_4* class to check the issue.
>>>>> GitHub project: https://github
>>>>> .com/prasadbhalerao1983/IgniteTestPrj.git
>>>>>
>>>>> Thanks,
>>>>> Prasad
>>>>>
>>>>>
>>>>> On Wed, Dec 26, 2018 at 11:18 PM Prasad Bhalerao <
>>>>> prasadbhalerao1983@gmail.com> wrote:
>>>>>
>>>>>> How to push the ids to temp table, can you please give any example?
>>>>>> Is it a in memory temp table created by ignite?
>>>>>>
>>>>>> Can you please explain how enforceJoinOrder will help in this case?
>>>>>> Thanks ,
>>>>>> Prasad
>>>>>>
>>>>>> On Wed 26 Dec, 2018, 9:37 PM Ilya Kasnacheev <
>>>>>> ilya.kasnacheev@gmail.com wrote:
>>>>>>
>>>>>>> Hello!
>>>>>>>
>>>>>>> Can you try pushing temp table to 1st position and setting
>>>>>>> enforceJoinOrder=true?
>>>>>>>
>>>>>>> SELECT ipv4agd.id,
>>>>>>>   ipv4agd.assetGroupId,
>>>>>>>   ipv4agd.ipStart,
>>>>>>>   ipv4agd.ipEnd
>>>>>>> FROM TABLE (assetGroupId bigint = ? ) temp
>>>>>>> JOIN IpV4AssetGroupData ipv4agd
>>>>>>> ON ipv4agd.assetGroupId         = temp.assetGroupId
>>>>>>> WHERE subscriptionId            = ?
>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>
>>>>>>> See https://apacheignite.readme.io/docs/configuration-parameters
>>>>>>> for enforceJoinOrder.
>>>>>>>
>>>>>>> Regards,
>>>>>>> --
>>>>>>> Ilya Kasnacheev
>>>>>>>
>>>>>>>
>>>>>>> ср, 26 дек. 2018 г. в 19:01, Prasad Bhalerao <
>>>>>>> prasadbhalerao1983@gmail.com>:
>>>>>>>
>>>>>>>> I am executing following SQL on ignite cache. This cache
has 37
>>>>>>>> million records and this data is distributed across 4 nodes.
>>>>>>>> *SQL:*
>>>>>>>> SELECT ipv4agd.id,
>>>>>>>>   ipv4agd.assetGroupId,
>>>>>>>>   ipv4agd.ipStart,
>>>>>>>>   ipv4agd.ipEnd
>>>>>>>> FROM IpV4AssetGroupData ipv4agd
>>>>>>>> JOIN TABLE (assetGroupId bigint = ? ) temp
>>>>>>>> ON ipv4agd.assetGroupId         = temp.assetGroupId
>>>>>>>> WHERE subscriptionId            = ?
>>>>>>>> AND (ipStart <= ? AND ipEnd >= ?)
>>>>>>>> ORDER BY ipv4agd.assetGroupId
>>>>>>>>
>>>>>>>>
>>>>>>>> As per the execution plan show below, ignite is using index
>>>>>>>> "IPV4_ASSET_GROUP_DATA_IDX2 " and execution plan attached
below.
>>>>>>>>
>>>>>>>> This sql is taking around 23 seconds. I have set the max
index
>>>>>>>> inline size as 65 bytes.
>>>>>>>> The number of assetGroupIds set in join clause are 50.
>>>>>>>>
>>>>>>>> *Is there anything I can do to improve the performance of
this SQL?*
>>>>>>>>
>>>>>>>> I have checked the memory and cpu utilization and it it is
very
>>>>>>>> low. I also tried to profile it using jprofiler to find out
the issue, but
>>>>>>>> could not find solution. I have also attached profiler snapshot
at the end.
>>>>>>>> Please check.
>>>>>>>>
>>>>>>>>
>>>>>>>> .
>>>>>>>>
>>>>>>>> *Indexes:*
>>>>>>>>
>>>>>>>> public class IpV4AssetGroupData implements UpdatableData<DefaultDataAffinityKey>
{
>>>>>>>>
>>>>>>>>   @QuerySqlField
>>>>>>>>   private long id;
>>>>>>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name
= "ipv4_asset_group_data_idx2", order = 2)})
>>>>>>>>   private long assetGroupId;
>>>>>>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name
= "ipv4_asset_group_data_idx1", order = 1),
>>>>>>>>       @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2",
order = 1)})
>>>>>>>>   private long subscriptionId;
>>>>>>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name
= "ipv4_asset_group_data_idx1", order = 2),
>>>>>>>>       @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2",
order = 3)})
>>>>>>>>   private int ipStart;
>>>>>>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name
= "ipv4_asset_group_data_idx1", order = 3),
>>>>>>>>       @QuerySqlField.Group(name = "ipv4_asset_group_data_idx2",
order = 4)})
>>>>>>>>   private int ipEnd;
>>>>>>>>
>>>>>>>> }
>>>>>>>>
>>>>>>>> *Execution plan:*
>>>>>>>>
>>>>>>>> Query execution is too long [time=15788 ms, sql='SELECT
>>>>>>>> IPV4AGD__Z0.ID __C0_0,
>>>>>>>> IPV4AGD__Z0.ASSETGROUPID __C0_1,
>>>>>>>> IPV4AGD__Z0.IPSTART __C0_2,
>>>>>>>> IPV4AGD__Z0.IPEND __C0_3
>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>>  INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>>  ON TRUE
>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
AND
>>>>>>>> ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2) AND ((IPV4AGD__Z0.IPSTART
<= ?3) AND
>>>>>>>> (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>> ORDER BY 2',
>>>>>>>>
>>>>>>>> plan=
>>>>>>>>
>>>>>>>> SELECT
>>>>>>>>     IPV4AGD__Z0.ID AS __C0_0,
>>>>>>>>     IPV4AGD__Z0.ASSETGROUPID AS __C0_1,
>>>>>>>>     IPV4AGD__Z0.IPSTART AS __C0_2,
>>>>>>>>     IPV4AGD__Z0.IPEND AS __C0_3
>>>>>>>> FROM IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4ASSETGROUPDATA IPV4AGD__Z0
>>>>>>>>     /* IPV4_ASSET_GROUP_DETAIL_CACHE.IPV4_ASSET_GROUP_DATA_IDX2:
>>>>>>>> SUBSCRIPTIONID = ?2
>>>>>>>>         AND IPSTART <= ?3
>>>>>>>>         AND IPEND >= ?4
>>>>>>>>      */
>>>>>>>>     /* WHERE (IPV4AGD__Z0.IPEND >= ?4)
>>>>>>>>         AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>>         AND (IPV4AGD__Z0.IPSTART <= ?3))
>>>>>>>>     */
>>>>>>>> INNER JOIN TABLE(ASSETGROUPID BIGINT=?1) TEMP__Z1
>>>>>>>>     /* function: ASSETGROUPID = IPV4AGD__Z0.ASSETGROUPID
*/
>>>>>>>>     ON 1=1
>>>>>>>> WHERE (IPV4AGD__Z0.ASSETGROUPID = TEMP__Z1.ASSETGROUPID)
>>>>>>>>     AND ((IPV4AGD__Z0.SUBSCRIPTIONID = ?2)
>>>>>>>>     AND ((IPV4AGD__Z0.IPSTART <= ?3)
>>>>>>>>     AND (IPV4AGD__Z0.IPEND >= ?4)))
>>>>>>>> ORDER BY 2
>>>>>>>> , parameters=[[3483555, 3180458, 3250090, 3483563, 3182509,
>>>>>>>> 3213230, 3245998, 3487661, 3215281, 3444657, 3182515, 3372974,
3483573,
>>>>>>>> 3372981, 3200951, 3485624, 3295161, 3485626, 3379125, 3211196,
3213242,
>>>>>>>> 3381181, 3194805, 3213247, 3258299, 3379123, 3377070, 3315637,
3352502,
>>>>>>>> 3295174, 3485618, 3438530, 3483592, 3352516, 3155914, 3424204,
3192775,
>>>>>>>> 3485643, 3317711, 3246026, 3209159, 3485584, 3485645, 3483594,
3248085,
>>>>>>>> 3321799, 3248086, 3190744, 3211222, 3379162], 164307, 1084754675,
>>>>>>>> -2094919442]]
>>>>>>>>
>>>>>>>>
>>>>>>>> Profiler snapshot:
>>>>>>>>
>>>>>>>> [image: image.png]
>>>>>>>>
>>>>>>>

Mime
View raw message