If it's expected that the SQL query will produce a large data set then consider using the memory quotas feature with the offloading to disk option <https://www.gridgain.com/docs/latest/developers-guide/memory-configuration/memory-quotas>. It's not available in Ignite though. GridGain releases it in its free version - Community Edition.
- Denis On Tue, Aug 25, 2020 at 3:03 AM Andrey Mashenkov <[email protected]> wrote: > Hi, > > Most likely, the query intermediate result doesn't fit to JVM heap memory. > The query may require all table data fetched before applying sorting. > > You can try to create a composite index over "act_id,mer_id,score" columns. > > > > On Tue, Aug 25, 2020 at 8:42 AM [email protected] <[email protected]> > wrote: > >> Hi,an error happened when I run a sql in ignite cluster. Thanks. >> >> Some info as follow: >> -- sql >> -- act_rank has 5,000,000 rows >> select * from act_rank >> order by act_id,mer_id,score >> limit 100 ; >> >> -- sql error info: >> Error: javax.cache.CacheException: Failed to map SQL query to topology on >> data node [dataNodeId=ca448962-9ce9-4321-82a7-2d12e147f34c, msg=Data node >> has left the grid during query execution >> [nodeId=ca448962-9ce9-4321-82a7-2d12e147f34c]] (state=50000,code=1) >> java.sql.SQLException: javax.cache.CacheException: Failed to map SQL >> query to topology on data node >> [dataNodeId=ca448962-9ce9-4321-82a7-2d12e147f34c, msg=Data node has left >> the grid during query execution >> [nodeId=ca448962-9ce9-4321-82a7-2d12e147f34c]] >> at >> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750) >> at >> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212) >> at >> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475) >> at sqlline.Commands.execute(Commands.java:823) >> at sqlline.Commands.sql(Commands.java:733) >> at sqlline.SqlLine.dispatch(SqlLine.java:795) >> at sqlline.SqlLine.begin(SqlLine.java:668) >> at sqlline.SqlLine.start(SqlLine.java:373) >> at sqlline.SqlLine.main(SqlLine.java:265) >> >> -- ignite server error log >> SELECT >> __Z0.ID __C0_0, >> __Z0.ACT_ID __C0_1, >> __Z0.MEM_ID __C0_2, >> __Z0.MER_ID __C0_3, >> __Z0.SHOP_ID __C0_4, >> __Z0.AREA_ID __C0_5, >> __Z0.PHONE_NO __C0_6, >> __Z0.SCORE __C0_7 >> FROM PUBLIC.ACT_RANK __Z0 >> ORDER BY 2, 4, 8 LIMIT 100 [90108-197] >> at >> org.h2.message.DbException.getJdbcSQLException(DbException.java:357) >> at org.h2.message.DbException.get(DbException.java:168) >> at org.h2.message.DbException.convert(DbException.java:301) >> at org.h2.command.Command.executeQuery(Command.java:214) >> at >> org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:114) >> at >> org.apache.ignite.internal.processors.query.h2.PreparedStatementExImpl.executeQuery(PreparedStatementExImpl.java:67) >> at >> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:1421) >> ... 13 more >> Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded >> at >> org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1457) >> at org.h2.result.LazyResult.hasNext(LazyResult.java:79) >> at org.h2.result.LazyResult.next(LazyResult.java:59) >> at org.h2.command.dml.Select.queryFlat(Select.java:527) >> at org.h2.command.dml.Select.queryWithoutCache(Select.java:633) >> at >> org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114) >> at org.h2.command.dml.Query.query(Query.java:352) >> at org.h2.command.dml.Query.query(Query.java:333) >> at >> org.h2.command.CommandContainer.query(CommandContainer.java:114) >> at org.h2.command.Command.executeQuery(Command.java:202) >> ... 16 more >> >> >> -- table struct (total rows:5,000,000) >> CREATE TABLE act_rank( >> id varchar(50) primary key, >> act_id VARCHAR(50), >> mem_id VARCHAR(50), >> mer_id VARCHAR(50), >> shop_id VARCHAR(50), >> area_id VARCHAR(50), >> phone_no VARCHAR(16), >> score INT >> ); >> >> -- visor info >> visor> cache -c=@c4 -a >> Time of the snapshot: 2020-08-24 11:20:50 >> >> +========================================================================================================================================================================+ >> | Name(@) | Mode | Nodes | Total entries (Heap / >> Off-heap) | Primary entries (Heap / Off-heap) | Hits | Misses | >> Reads | Writes | >> >> +========================================================================================================================================================================+ >> | SQL_PUBLIC_ACT_RANK(@c4) | PARTITIONED | 3 | 5000000 (0 / 5000000) >> | min: 1635268 (0 / 1635268) | min: 0 | min: 0 | >> min: 0 | min: 0 | >> | | | | >> | avg: 1666666.67 (0.00 / 1666666.67) | avg: 0.00 | avg: 0.00 | >> avg: 0.00 | avg: 0.00 | >> | | | | >> | max: 1720763 (0 / 1720763) | max: 0 | max: 0 | >> max: 0 | max: 0 | >> >> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >> >> Cache 'SQL_PUBLIC_ACT_RANK(@c4)': >> +------------------------------------------------------------------+ >> | Name(@) | SQL_PUBLIC_ACT_RANK(@c4) | >> | Total entries (Heap / Off-heap) | 5000000 (0 / 5000000) | >> | Nodes | 3 | >> | Total size Min/Avg/Max | 1635268 / 1666666.67 / 1720763 | >> | Heap size Min/Avg/Max | 0 / 0.00 / 0 | >> | Off-heap size Min/Avg/Max | 1635268 / 1666666.67 / 1720763 | >> +------------------------------------------------------------------+ >> >> Nodes for: SQL_PUBLIC_ACT_RANK(@c4) >> >> +==========================================================================================================================+ >> | Node ID8(@), IP | CPUs | Heap Used | CPU Load | Up Time >> | Size (Primary / Backup) | Hi/Mi/Rd/Wr | >> >> +==========================================================================================================================+ >> | CA448962(@n0), 172.17.0.1 | 4 | 55.15 % | 0.07 % | 00:42:56.498 >> | Total: 1643969 (1643969 / 0) | Hi: 0 | >> | | | | | >> | Heap: 0 (0 / <n/a>) | Mi: 0 | >> | | | | | >> | Off-Heap: 1643969 (1643969 / 0) | Rd: 0 | >> | | | | | >> | Off-Heap Memory: <n/a> | Wr: 0 | >> >> +---------------------------+------+-----------+----------+--------------+-----------------------------------+-------------+ >> | B015A32C(@n3), 172.17.0.1 | 4 | 11.76 % | 0.17 % | 00:41:28.094 >> | Total: 1720763 (1720763 / 0) | Hi: 0 | >> | | | | | >> | Heap: 0 (0 / <n/a>) | Mi: 0 | >> | | | | | >> | Off-Heap: 1720763 (1720763 / 0) | Rd: 0 | >> | | | | | >> | Off-Heap Memory: <n/a> | Wr: 0 | >> >> +---------------------------+------+-----------+----------+--------------+-----------------------------------+-------------+ >> | 13714C97(@n2), 172.17.0.1 | 4 | 52.12 % | 0.23 % | 00:42:14.272 >> | Total: 1635268 (1635268 / 0) | Hi: 0 | >> | | | | | >> | Heap: 0 (0 / <n/a>) | Mi: 0 | >> | | | | | >> | Off-Heap: 1635268 (1635268 / 0) | Rd: 0 | >> | | | | | >> | Off-Heap Memory: <n/a> | Wr: 0 | >> >> +--------------------------------------------------------------------------------------------------------------------------+ >> >> -- ignite.xml >> <beans xmlns="http://www.springframework.org/schema/beans"; xmlns:xsi=" >> http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation=" >> http://www.springframework.org/schema/beans >> http://www.springframework.org/schema/beans/spring-beans.xsd";> >> <!-- Alter configuration below as needed. --> >> <bean id="grid.cfg" class=" >> org.apache.ignite.configuration.IgniteConfiguration"> >> <property name="peerClassLoadingEnabled" value="true"/> >> <property name="publicThreadPoolSize" value="128"/> >> <property name="systemThreadPoolSize" value="64"/> >> <property name="discoverySpi"> >> <bean class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi"> >> <property name="ipFinder"> >> <bean class=" >> org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder"> >> <property name="addresses"> >> <list> >> <value>172.30.222.128</value> >> <value>172.30.222.131</value> >> <value>172.30.222.138</value> >> </list> >> </property> >> </bean> >> </property> >> </bean> >> </property> >> <!-- >> Redefining maximum memory size for the cluster node usage. >> --> >> <property name="dataStorageConfiguration"> >> <bean class="org.apache.ignite.configuration.DataStorageConfiguration"> >> <!-- Redefining the default region's settings --> >> <property name="defaultDataRegionConfiguration"> >> <bean class="org.apache.ignite.configuration.DataRegionConfiguration"> >> <property name="name" value="Default_Region"/> >> <!-- Setting the size of the default region to 4GB. --> >> <property name="maxSize" value="#{8L * 1024 * 1024 * 1024}"/> >> </bean> >> </property> >> </bean> >> </property> >> </bean> >> </beans> >> >> > > -- > Best regards, > Andrey V. Mashenkov >
