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 1115098...@qq.com <so...@foxmail.com> 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
