You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Prasad Bhalerao <pr...@gmail.com> on 2020/01/24 12:23:45 UTC
Distinct Query too slow
Hi,
I am using Ignite 2.6 version. I have around total 6 million entries in my
cache.
Following sql is taking too much time to execute. Some times it takes more
than 180 seconds.
This SQL returns 4.5 million entries for given subscriptionId. I tried to
add query parallelism (4-16 threads) on cache configuration. But it did not
help.
If I remove DISTINCT keyword from sql then it executes quickly. But I need
distinct in this particular case.
Can some please advise?
*SQL:*
select distinct ipStart,ipEnd from IpContainerIpV4Data where subscriptionId
= ?
*2020-01-23 06:49:38,249 264738612 [query-#30600%springDataNode%] WARN
o.a.i.i.p.query.h2.IgniteH2Indexing - Query execution is too long
[time=83159 ms, sql='SELECT DISTINCT __Z0.IPSTART __C0_0, __Z0.IPEND __C0_1
FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 WHERE
__Z0.SUBSCRIPTIONID = ?1', plan= SELECT DISTINCT __Z0.IPSTART AS __C0_0,
__Z0.IPEND AS __C0_1 FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0
/* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX2: SUBSCRIPTIONID = ?1 */
WHERE __Z0.SUBSCRIPTIONID = ?1 , parameters=[1234]]*
*Index is as follows:*
public class IpContainerIpV4Data implements Data<DefaultDataAffinityKey>,
UpdatableData<DefaultDataAffinityKey> {
@QuerySqlField
private long id;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 2)})
private int moduleId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 1),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
private long subscriptionId;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 4, descending = true),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2, descending
= true)})
private int ipEnd;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"ip_container_ipv4_idx1", order = 3),
@QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
private int ipStart;
@QuerySqlField
private int partitionId;
@QuerySqlField
private long updatedDate;
}
*Cache Configuration:*
private CacheConfiguration ipContainerIPV4CacheCfg() {
CacheConfiguration ipContainerIpV4CacheCfg = new
CacheConfiguration<>(CacheName.IP_CONTAINER_IPV4_CACHE.name());
ipContainerIpV4CacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
ipContainerIpV4CacheCfg.setWriteThrough(ENABLE_WRITE_THROUGH);
ipContainerIpV4CacheCfg.setReadThrough(false);
ipContainerIpV4CacheCfg.setRebalanceMode(CacheRebalanceMode.ASYNC);
ipContainerIpV4CacheCfg.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC);
ipContainerIpV4CacheCfg.setBackups(this.backupCount);
Factory<IpContainerIpV4CacheStore> storeFactory =
FactoryBuilder.factoryOf(IpContainerIpV4CacheStore.class);
ipContainerIpV4CacheCfg.setCacheStoreFactory(storeFactory);
ipContainerIpV4CacheCfg.setIndexedTypes(DefaultDataAffinityKey.class,
IpContainerIpV4Data.class);
ipContainerIpV4CacheCfg.setCacheStoreSessionListenerFactories(cacheStoreSessionListenerFactory());
ipContainerIpV4CacheCfg.*setSqlIndexMaxInlineSize(84);*
RendezvousAffinityFunction affinityFunction = new
RendezvousAffinityFunction();
affinityFunction.setExcludeNeighbors(true);
ipContainerIpV4CacheCfg.setAffinity(affinityFunction);
ipContainerIpV4CacheCfg.setStatisticsEnabled(true);
ipContainerIpV4CacheCfg.setPartitionLossPolicy(PartitionLossPolicy.READ_WRITE_SAFE);
ipContainerIpV4CacheCfg.setQueryParallelism(4);
return ipContainerIpV4CacheCfg;
}
Thanks,
Prasad
Re: Distinct Query too slow
Posted by Andrei Aleksandrov <ae...@gmail.com>.
Hi,
Looks like it's known issue:
https://issues.apache.org/jira/browse/IGNITE-10781
According to this issue, indexes can work non-effective for distinct clause.
However, looks like the explain from your log isn't full. It should
contain two parts - reducer and mapper.
Can you please run next from any SQL tool:
explain select distinct ipStart,ipEnd from IpContainerIpV4Data where
subscriptionId = some_value;
BR,
Andrei
1/24/2020 3:23 PM, Prasad Bhalerao пишет:
> Hi,
>
> I am using Ignite 2.6 version. I have around total 6 million entries
> in my cache.
>
> Following sql is taking too much time to execute. Some times it takes
> more than 180 seconds.
>
> This SQL returns 4.5 million entries for given subscriptionId. I tried
> to add query parallelism (4-16 threads) on cache configuration. But it
> did not help.
>
> If I remove DISTINCT keyword from sql then it executes quickly. But I
> need distinct in this particular case.
>
> Can some please advise?
>
> *SQL:*
> select distinct ipStart,ipEnd from IpContainerIpV4Data where
> subscriptionId = ?
>
> /2020-01-23 06:49:38,249 264738612 [query-#30600%springDataNode%] WARN
> o.a.i.i.p.query.h2.IgniteH2Indexing - Query execution is too long
> [time=83159 ms, sql='SELECT DISTINCT __Z0.IPSTART __C0_0, __Z0.IPEND
> __C0_1 FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 WHERE
> __Z0.SUBSCRIPTIONID = ?1', plan= SELECT DISTINCT __Z0.IPSTART AS
> __C0_0, __Z0.IPEND AS __C0_1 FROM
> IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 /*
> IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX2: SUBSCRIPTIONID = ?1 */
> WHERE __Z0.SUBSCRIPTIONID = ?1 , parameters=[1234]]/
>
>
> *Index is as follows:*
> **
> public class IpContainerIpV4Data implements
> Data<DefaultDataAffinityKey>, UpdatableData<DefaultDataAffinityKey> {
>
> @QuerySqlField
> private long id;
>
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 2)})
> private int moduleId;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 1),
> @QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
> private long subscriptionId;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 4, descending = true),
> @QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2,
> descending = true)})
> private int ipEnd;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 3),
> @QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
> private int ipStart;
> @QuerySqlField
> private int partitionId;
> @QuerySqlField
> private long updatedDate;
>
> }
>
>
> *Cache Configuration:*
>
> private CacheConfiguration ipContainerIPV4CacheCfg() {
>
> CacheConfiguration ipContainerIpV4CacheCfg =new CacheConfiguration<>(CacheName.IP_CONTAINER_IPV4_CACHE.name());
> ipContainerIpV4CacheCfg.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
> ipContainerIpV4CacheCfg.setWriteThrough(ENABLE_WRITE_THROUGH);
> ipContainerIpV4CacheCfg.setReadThrough(false);
> ipContainerIpV4CacheCfg.setRebalanceMode(CacheRebalanceMode.ASYNC);
> ipContainerIpV4CacheCfg.setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC);
> ipContainerIpV4CacheCfg.setBackups(this.backupCount);
> Factory<IpContainerIpV4CacheStore> storeFactory = FactoryBuilder.factoryOf(IpContainerIpV4CacheStore.class);
> ipContainerIpV4CacheCfg.setCacheStoreFactory(storeFactory);
> ipContainerIpV4CacheCfg.setIndexedTypes(DefaultDataAffinityKey.class, IpContainerIpV4Data.class);
> ipContainerIpV4CacheCfg.setCacheStoreSessionListenerFactories(cacheStoreSessionListenerFactory());
> ipContainerIpV4CacheCfg.*setSqlIndexMaxInlineSize(84);*
> RendezvousAffinityFunction affinityFunction =new RendezvousAffinityFunction();
> affinityFunction.setExcludeNeighbors(true);
> ipContainerIpV4CacheCfg.setAffinity(affinityFunction);
> ipContainerIpV4CacheCfg.setStatisticsEnabled(true);
> ipContainerIpV4CacheCfg.setPartitionLossPolicy(PartitionLossPolicy.READ_WRITE_SAFE);
> ipContainerIpV4CacheCfg.setQueryParallelism(4);return ipContainerIpV4CacheCfg; }
>
> Thanks,
> Prasad