You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by David Harvey <dh...@jobcase.com> on 2018/03/06 16:58:20 UTC
SELECT Statement cancellation & memory sizing
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.
Re: SELECT Statement cancellation & memory sizing
Posted by Dave Harvey <dh...@jobcase.com>.
Just saw 2.4 release notes: Improved COUNT(*) performance
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SELECT Statement cancellation & memory sizing
Posted by lawrencefinn <la...@gmail.com>.
Also AFAIK, visor is very inaccurate on offheap memory measurements. at
least it used to be
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SELECT Statement cancellation & memory sizing
Posted by lawrencefinn <la...@gmail.com>.
ignite is not optimized for count queries. it visits each object to do the
count and doesn't do the count off of an index (or some cached store).
seems kind of silly, especially if you have a count on indexed fields.
i think query cancellation only works within the API. I think you can
either set a timeout for the query or you can cancel it with the handle to
the cursor. i doubt cancelling through JDBC works.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/