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
(m:ss)
"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"/>
103
104 <!-- switched store/wal to understand higher BW
behavior for WAL -->
105 <property name="storagePath" value="/
IgnitePersistenceStorage/wal"/>
106 <property name="walPath" value="/
IgnitePersistenceStorage/store"/>
107 <property name="walArchivePath" value="/
IgnitePersistenceStorage/wal/archive"/>
108
109 <!-- Enable write throttling. -->
110 <!-- property name="writeThrottlingEnabled"
value="false"/ -->
111
112 <property name="defaultDataRegionConfiguration">
113 <bean class="org.apache.ignite.configuration.
DataRegionConfiguration">
114 <!-- Enabling persistence. -->
115 <property name="persistenceEnabled" value="true"/>
116
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>
Disclaimer
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.
