ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Harvey <dhar...@jobcase.com>
Subject SELECT Statement cancellation & memory sizing
Date Tue, 06 Mar 2018 16:58:20 GMT
I have a question about SQL query via JDBC cancellation, as well as a
performance question.

I've connected Ignite from SQL/Workbench using the Thin driver, however via
ssh -L proxy and I issued
"select count(*) from table1 where indexedField = x;" and that took 1:40

"select count(*) from table1;"   seemed to take 1:20:00, based on when the
cluster went idle again.

This is using Ignite Persistence on 2.3.   The cluster is 8 x i3.8xlarge,
with the 4 local SSDs configured as a RAID 0.  These have 244MB memory
each, and 32 CPUs. The table perhaps has 500M rows at an average of 1-2KB
each.    QueryParallelism is at the default of 1.  Each node has about
220GB of SSB space allocated.

Repeating this when everything was idle,    I issue the select statement,
and then cancelled it using the SQL/Workbench button (which works for
MySQL).   Nothing happened, and I can see that the cluster is reading about
50MB/s from each node.   I then killed SQL/Workbench  to break the
connection, and then the ssh proxy, but the connection loss did not
terminate the query either.   What is the expected behavior when a Query is
killed via JDBC.

On the performance side, it appears that there are no optimizations around
"count(*)", and it is reading all of the data on the SSDs (50MB/s gets
close to 220GB in 1:20:00).   I could believe this is just a standard scan
problem when the data size is a bit larger than the cache, and we could
improve this by increasing query parallelism, or adding nodes, if this use
case was important.

However, node stats from visor, if I'm reading them correctly,  indicate it
is not using the memory, but I believe I enabled 160GB of off-heap space.
 How do I reconcile the visor stats with the memory settings?   All of the
nodes have been up since the data was loaded.

 Current CPU load %          | 0.03%

| Average CPU load %          | 0.16%

| Heap memory initialized     | 30gb

| Heap memory used            | 15gb

| Heap memory committed       | 30gb

| Heap memory maximum         | 30gb

| Non-heap memory initialized | 2mb

| Non-heap memory used        | 125mb

| Non-heap memory committed   | 132mb

| Non-heap memory maximum     | 1gb

<bean class="org.apache.ignite.configuration.DataStorageConfiguration">

101                 <!-- Set the page size to 4 KB -->

102                 <property name="pageSize" value="4096"/>


104                 <!--  switched store/wal to understand higher BW
behavior for WAL -->

105                 <property name="storagePath" value="/

106                 <property name="walPath" value="/

107                 <property name="walArchivePath" value="/


109                 <!-- Enable write throttling. -->

110                 <!-- property name="writeThrottlingEnabled"
value="false"/ -->


112                 <property name="defaultDataRegionConfiguration">

113                    <bean class="org.apache.ignite.configuration.

114                    <!-- Enabling persistence. -->

115                    <property name="persistenceEnabled" value="true"/>


117                    <!-- Increasing the buffer size to 1 GB. -->

118                    <property name="checkpointPageBufferSize" value="#{1024L
* 1024 * 1024}"/>

119                    <property name="name" value="Default_Region"/>

120                    <!-- Setting the size of the default region to
160GB. -->

121                    <property name="maxSize" value="#{160L * 1024 * 1024
* 1024}"/>

122                    </bean>

123                  </property>

124              </bean>


The information contained in this communication from the sender is confidential. It is intended
solely for use by the recipient and others authorized to receive it. If you are not the recipient,
you are hereby notified that any disclosure, copying, distribution or taking action in relation
of the contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been automatically archived
by Mimecast Ltd, an innovator in Software as a Service (SaaS) for business. Providing a safer
and more useful place for your human generated data. Specializing in; Security, archiving
and compliance. To find out more visit the Mimecast website.

View raw message