ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Evgenii Zhuravlev <e.zhuravlev...@gmail.com>
Subject Re: Utilization of CPU problem
Date Wed, 01 Aug 2018 12:02:28 GMT
Hi,

I don't see that you have any indexes, so, you definitely should create it:
https://apacheignite-sql.readme.io/docs/schema-and-indexes

Why do you use replicated caches? You need to know, that when in a query
you use only replicated caches, this query will be executed on one node
only. In this case, most possible, that one of the nodes could be
overloaded.

Looks like nodes have only 1gb of the heap, it's a very small amount of
heap memory if you want to run a lot of complex SQL queries. I'd recommend
increasing this number(for example, you can do it in ignite.sh).

Evgenii

2018-08-01 5:19 GMT+03:00 Lijun Cao <641507577@qq.com>:

> The Numeric type should be replaced with INT OR BIGINT
> Part Table:
> CREATE TABLE PART(
> P_PARTKEY INT PRIMARY KEY,
> P_NAME VARCHAR(22) ,
> P_MFGR CHAR(6),
> P_CATEGORY CHAR(7),
> P_BRAND CHAR(9),
> P_COLOR VARCHAR(11),
> P_TYPE VARCHAR(25),
> P_SIZE INT,
> P_CONTAINER CHAR(10)
> ) WITH "TEMPLATE=REPLICATED";
>
> Supplier Table:
> CREATE TABLE supplier(
> s_suppkey INT PRIMARY KEY,
> s_name CHAR(25),
> s_address VARCHAR(25),
> s_city CHAR(10),
> s_nation CHAR(15),
> s_region CHAR(12),
> s_phone CHAR(15)
> ) WITH "TEMPLATE=REPLICATED";
>
> Customer Table:
> CREATE TABLE customer(
> c_custkey INT PRIMARY KEY,
> c_name VARCHAR(25),
> c_address VARCHAR(25),
> c_city CHAR(10),
> c_nation CHAR(15),
> c_region CHAR(12),
> c_phone CHAR(15),
> c_mktsegment CHAR(10)
> ) WITH "TEMPLATE=REPLICATED";
>
> Date Table:
> CREATE TABLE date(
> d_datekey INT PRIMARY KEY,
> d_date CHAR(18),
> d_dayofweek CHAR(8),
> d_month CHAR(9),
> d_year INT,
> d_yearmonthnum INT,
> d_yearmonth CHAR(7),
> d_daynuminweek INT,
> d_daynuminmonth INT,
> d_daynuminyear INT,
> d_monthuminyear INT,
> d_weeknuminyear INT,
> d_sellingseason CHAR(12),
> d_lastdayinweekfl INT,
> d_lastdayinmonthfl INT,
> d_holidayfl INT,
> d_weekdayfl INT
> ) WITH "TEMPLATE=REPLICATED";
>
> Lineorder Table:
> CREATE TABLE lineorder(
> lo_orderkey BIGINT,
> lo_linenumber BIGINT,
> lo_custkey INT,
> lo_partkey INT,
> lo_suppkey INT,
> lo_orderdate INT,
> lo_orderpriority CHAR(15),
> lo_shippriority INT,
> lo_quantity BIGINT,
> lo_extendedprice BIGINT,
> lo_ordtotalprice BIGINT,
> lo_discount BIGINT,
> lo_revenue BIGINT,
> lo_supplycost BIGINT,
> lo_tax BIGINT,
> lo_commitdate INT,
> lo_shipmode CHAR(10),
> PRIMARY KEY(lo_orderkey,lo_linenumber)
> ) WITH "TEMPLATE=PARTITIONED";
>
> 在 2018年7月31日,23:09,Evgenii Zhuravlev <e.zhuravlev.wk@gmail.com> 写道:
>
> can you share the full benchmark and jvm parameters so community could
> reproduce it?
>
> Thanks,
> Evgenii
>
> 2018-07-31 17:28 GMT+03:00 Lijun Cao <641507577@qq.com>:
>
>> Thank you for your reply.
>>
>> This is my SSB benchmark result, the consumption of time is out of my
>> expectation. My expectation is they should have been finished in 1 minute.
>> My query operation is using the sqlline to connect to the 1 node of cluster
>> and then send SQL to excute.
>> <屏幕快照 2018-07-31 22.25.19.png>
>>
>>
>> 在 2018年7月31日,20:05,Evgenii Zhuravlev <e.zhuravlev.wk@gmail.com>
写道:
>>
>> What do you mean by "result is terrible"? Looks like you just can't give
>> enough load to the cluster. What kind of operations do you perform on the
>> cluster?
>>
>> Evgenii
>>
>> 2018-07-31 14:11 GMT+03:00 Lijun Cao <641507577@qq.com>:
>>
>>> Hi,
>>>
>>> I did a SSB benchmark on Ignite database, but the the result is not
>>> optimistic (actually it is terrible). My testing data is shown below.
>>>
>>> Table Name   |     Records
>>> date               |        2556
>>> supplier         |         39,997
>>> customer       |        599,941
>>> part                |       999,901
>>> lineorder        |       119,982,610
>>>
>>> Then I checked the monitor and found that the utilization of CPU is
>>> pretty low, just 20% average usage. But I have 12 core on cluster (the
>>> cluster is set up by 3 nodes, each node has got 4 core).
>>>
>>> So how can I improve the utilization of CPU ?
>>>
>>> Here is my settings:
>>> <!--
>>>        Licensed to the Apache Software Foundation (ASF) under one or more
>>>   contributor license agreements.  See the NOTICE file distributed with
>>>   this work for additional information regarding copyright ownership.
>>>   The ASF licenses this file to You under the Apache License, Version 2.0
>>>   (the "License"); you may not use this file except in compliance with
>>>   the License.  You may obtain a copy of the License at
>>>
>>>        http://www.apache.org/licenses/LICENSE-2.0
>>>
>>>   Unless required by applicable law or agreed to in writing, software
>>>   distributed under the License is distributed on an "AS IS" BASIS,
>>>   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
>>> implied.
>>>   See the License for the specific language governing permissions and
>>>   limitations under the License.
>>> -->
>>>
>>> <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.confi
>>> guration.IgniteConfiguration">
>>>     <!-- performance tune -->
>>>     <!-- Configure internal thread pool. -->
>>>     <property name="publicThreadPoolSize" value="64"/>
>>>     <!-- Configure system thread pool. -->
>>>     <property name="systemThreadPoolSize" value="32"/>
>>>     <!-- Configure query thread pool. -->
>>>     <property name="queryThreadPoolSize" value="64"/>
>>>
>>>       <property name="discoverySpi">
>>>          <bean class="org.apache.ignite.spi.d
>>> iscovery.tcp.TcpDiscoverySpi">
>>>            <property name="ipFinder">
>>>               <bean class="org.apache.ignite.spi.d
>>> iscovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder">
>>>                 <property name="addresses">
>>>                   <list>
>>>                     <!--  Explicitly specifying address of a local node
>>> to let it start and operate normally even if there is no more nodes in the
>>> cluster  You can also optionally specify an individual port or port
>>> range.-->
>>>                     <value>Ignite01</value>
>>>                   </list>
>>>                 </property>
>>>               </bean>
>>>            </property>
>>>          </bean>
>>>       </property>
>>>       <!-- Redefining maximum memory size for the cluster node usage.
>>> -->
>>>       <property name="dataStorageConfiguration">
>>>         <bean class="org.apache.ignite.confi
>>> guration.DataStorageConfiguration">
>>>           <!-- Redefining the default region's settings -->
>>>           <property name="defaultDataRegionConfiguration">
>>>             <bean class="org.apache.ignite.confi
>>> guration.DataRegionConfiguration">
>>>               <property name="name" value="Default_Region"/>
>>>               <!-- Setting the size of the default region to 4GB. -->
>>>               <property name="maxSize" value="#{16L * 1024 * 1024 *
>>> 1024}"/>
>>>               <!-- Enabling Apache Ignite native persistence. -->
>>>               <property name="persistenceEnabled" value="true"/>
>>>             </bean>
>>>           </property>
>>>         </bean>
>>>       </property>
>>>
>>>       <property name="cacheConfiguration">
>>>         <bean class="org.apache.ignite.confi
>>> guration.CacheConfiguration">
>>>             <!-- Set a cache name. -->
>>>             <property name="name" value="cacheName"/>
>>>             <!-- Set cache mode. -->
>>>             <property name="cacheMode" value="PARTITIONED"/>
>>>             <!-- Set number of backups to 0-->
>>>             <property name="backups" value="0"/>
>>>         </bean>
>>>     </property>
>>>    </bean>
>>> </beans>
>>>
>>
>>
>>
>
>

Mime
View raw message