You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by ght230 <gh...@163.com> on 2016/06/23 09:08:36 UTC
How to accelerate the query speed in Partitioned Mode
I want to do simple SQL query such "select ... from ... where id = ?" on
cache.
But the amount of data to be stored in the cache is quite large, I have to
save it to several nodes with Partitioned Mode.
The result is: the more server nodes, the slower the query.
How to accelerate the query speed in the above condition?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by AndreyVel <an...@gmail.com>.
It is not correct compare internal TPS and distributed networks TPS.
Could you compare TPS for 2 and 4 nodes? What query do you run?
How distributed selected data on cluster, is any affinity function used?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p5899.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by ght230 <gh...@163.com>.
The data to be queried is more about 700,000 records and every record
includes 10 fields.
When I loaded the data to only one node, query TPS is about 66,000.
If I load the data to 4 nodes by Partitioned Mode, query TPS is about
40,000.
The queried field is the key and has be indexed.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p5896.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by AndreyVel <an...@gmail.com>.
Could you tell more details about result size, cache size.
Does cache have index?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p5850.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by ght230 <gh...@163.com>.
Thank you for your reply.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p6159.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by Denis Magda <dm...@gridgain.com>.
Hi,
Affinity collocation [1] should be used in cases when you need to perform JOINs between several tables or need to have data related to one cache to be near some other data from the second cache. AffinityKeyMapper is from this area. It shouldn’t affect the performance of your test. Read [1] to get basic understanding on this topic.
In your case since you’re testing queries over a single cache you can use “long” as a key and there is no need to use AffinityKeyMapper at all.
How big is your result set? Also make sure to warmup the JVM before storing measurements (you need to run the test for a minute or so and only after that start measuring the performance). Also it makes sense to execute the queries from several threads to see the difference since you want see significant difference if a query is executed from a single thread and there are 2, 4 or 5 server nodes.
—
Denis
[1] https://apacheignite.readme.io/docs/affinity-collocation
> On Jul 6, 2016, at 12:24 PM, ght230 <gh...@163.com> wrote:
>
> I had tested again using "AffinityMapper".
>
> According to the result, It seems the effect of AffinityMapper is not very
> obvious.
>
> Can you help me to confirm whether my test method is correct?
>
> And can I observe that AffinityMapper has correctly setted from
> ignitevisorcmd?
>
> Test result as following:
> **********************************************
> using AffinityMapper
> node(2) record number(300000) query times(50000) TPS(10301)
> node(3) record number(300000) query times(50000) TPS(10910)
> node(4) record number(300000) query times(50000) TPS(10670)
> node(5) record number(300000) query times(50000) TPS(11518)
> **********************************************
> Not using AffinityMapper
> node(2) record number(300000) query times(50000) TPS(11908)
> node(3) record number(300000) query times(50000) TPS(11699)
> node(4) record number(300000) query times(50000) TPS(11893)
> node(5) record number(300000) query times(50000) TPS(11011)
>
> The test code as following:
>
> public class Person2 implements Serializable {
>
> public Object ak;
>
> /** Person ID (indexed). */
> @QuerySqlField(index = true)
> public Long id;
>
> /** Organization ID (indexed). */
> @QuerySqlField(index = true)
> public Long orgId;
>
> /** First name (not-indexed). */
> @QuerySqlField
> public String firstName;
>
> /** Last name (not indexed). */
> @QuerySqlField
> public String lastName;
>
> /**
> * Default constructor.
> */
>
> public Person2() {
> // No-op.
> }
>
> public Object getAk() {
> return ak;
> }
>
> public void setAk(Object ak) {
> this.ak = ak;
> }
>
> public Long getId() {
> return id;
> }
>
> public void setId(Long id) {
> this.id = id;
> }
>
> public Long getOrgId() {
> return orgId;
> }
>
> public void setOrgId(Long orgId) {
> this.orgId = orgId;
> }
>
> public String getFirstName() {
> return firstName;
> }
>
> public void setFirstName(String firstName) {
> this.firstName = firstName;
> }
>
> public String getLastName() {
> return lastName;
> }
>
> public void setLastName(String lastName) {
> this.lastName = lastName;
> }
>
> public Person2(Object ak, Long id, Long orgId, String firstName, String
> lastName) {
> super();
> this.ak = ak;
> this.id = id;
> this.orgId = orgId;
> this.firstName = firstName;
> this.lastName = lastName;
> }
>
> @Override
> public String toString() {
> return "Person2 [ak=" + ak + ", id=" + id + ", orgId=" + orgId + ",
> firstName=" + firstName + ", lastName="
> + lastName + "]";
> }
>
> }
>
> public class PersonKey {
>
> private Long id;
> @AffinityKeyMapped
> private Long orgId;
>
> public Long getId() {
> return id;
> }
>
> public void setId(Long id) {
> this.id = id;
> }
>
> public Long getOrgId() {
> return orgId;
> }
>
> public void setOrgId(Long orgId) {
> this.orgId = orgId;
> }
>
> public PersonKey(Long id, Long orgId) {
> super();
> this.id = id;
> this.orgId = orgId;
> }
>
> }
>
> public class Test3 {
>
> public static void main(String[] args) {
> // TODO Auto-generated method stub
>
> String personsCache = "personsCache";
>
> Ignition.setClientMode(true);
> Ignite ignite = Ignition.start();
>
> CacheConfiguration<Object, Person2> cacheCfg = new
> CacheConfiguration<Object, Person2>(personsCache);
> cacheCfg.setCacheMode(CacheMode.PARTITIONED);
> cacheCfg.setIndexedTypes(Object.class, Person2.class);
>
> cacheCfg.setAffinityMapper(new AffinityKeyMapper() {
> @Override
> public Object affinityKey(Object key) {
> if (key instanceof PersonKey) {
> PersonKey det = (PersonKey) key;
>
> return det.getOrgId();
> }
> return key;
> }
>
> @Override
> public void reset() {
> // TODO Auto-generated method stub
>
> }
> });
>
> IgniteCache<Object, Person2> cachePerson =
> ignite.getOrCreateCache(cacheCfg);
>
> cachePerson.clear();
>
> int personId = 0;
> int size = 10000;
> int groupsize = 6;
>
> for (int gi = 0; gi < groupsize; gi++) {
> Long orgId = Long.valueOf(gi);
> for (int pi = gi * size; pi < (gi + 1) * size; pi++) {
> personId = pi;
> Object personKey1 = new PersonKey(Long.valueOf(personId), orgId);
> Person2 p = new Person2(personKey1, Long.valueOf(personId), orgId,
> "FN123", "LN456");
> cachePerson.put(personId, p);
> }
> }
>
> SqlQuery sqlPer = new SqlQuery(Person2.class, "orgId=?");
>
> Long st = System.currentTimeMillis();
> int cnt = 0;
> Person2 rp = null;
> try (QueryCursor<Entry<Object, Person2>> cursor =
> cachePerson.query(sqlPer.setArgs("2"))) {
>
> for (Entry<Object, Person2> e : cursor) {
> rp = e.getValue();
> cnt++;
> }
> System.out.println("return rownum:=" + cnt);
> }
>
> cachePerson.clear();
> cachePerson.destroy();
> ignite.close();
>
> }
>
> }
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p6128.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by ght230 <gh...@163.com>.
I had tested again using "AffinityMapper".
According to the result, It seems the effect of AffinityMapper is not very
obvious.
Can you help me to confirm whether my test method is correct?
And can I observe that AffinityMapper has correctly setted from
ignitevisorcmd?
Test result as following:
**********************************************
using AffinityMapper
node(2) record number(300000) query times(50000) TPS(10301)
node(3) record number(300000) query times(50000) TPS(10910)
node(4) record number(300000) query times(50000) TPS(10670)
node(5) record number(300000) query times(50000) TPS(11518)
**********************************************
Not using AffinityMapper
node(2) record number(300000) query times(50000) TPS(11908)
node(3) record number(300000) query times(50000) TPS(11699)
node(4) record number(300000) query times(50000) TPS(11893)
node(5) record number(300000) query times(50000) TPS(11011)
The test code as following:
public class Person2 implements Serializable {
public Object ak;
/** Person ID (indexed). */
@QuerySqlField(index = true)
public Long id;
/** Organization ID (indexed). */
@QuerySqlField(index = true)
public Long orgId;
/** First name (not-indexed). */
@QuerySqlField
public String firstName;
/** Last name (not indexed). */
@QuerySqlField
public String lastName;
/**
* Default constructor.
*/
public Person2() {
// No-op.
}
public Object getAk() {
return ak;
}
public void setAk(Object ak) {
this.ak = ak;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getOrgId() {
return orgId;
}
public void setOrgId(Long orgId) {
this.orgId = orgId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Person2(Object ak, Long id, Long orgId, String firstName, String
lastName) {
super();
this.ak = ak;
this.id = id;
this.orgId = orgId;
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return "Person2 [ak=" + ak + ", id=" + id + ", orgId=" + orgId + ",
firstName=" + firstName + ", lastName="
+ lastName + "]";
}
}
public class PersonKey {
private Long id;
@AffinityKeyMapped
private Long orgId;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getOrgId() {
return orgId;
}
public void setOrgId(Long orgId) {
this.orgId = orgId;
}
public PersonKey(Long id, Long orgId) {
super();
this.id = id;
this.orgId = orgId;
}
}
public class Test3 {
public static void main(String[] args) {
// TODO Auto-generated method stub
String personsCache = "personsCache";
Ignition.setClientMode(true);
Ignite ignite = Ignition.start();
CacheConfiguration<Object, Person2> cacheCfg = new
CacheConfiguration<Object, Person2>(personsCache);
cacheCfg.setCacheMode(CacheMode.PARTITIONED);
cacheCfg.setIndexedTypes(Object.class, Person2.class);
cacheCfg.setAffinityMapper(new AffinityKeyMapper() {
@Override
public Object affinityKey(Object key) {
if (key instanceof PersonKey) {
PersonKey det = (PersonKey) key;
return det.getOrgId();
}
return key;
}
@Override
public void reset() {
// TODO Auto-generated method stub
}
});
IgniteCache<Object, Person2> cachePerson =
ignite.getOrCreateCache(cacheCfg);
cachePerson.clear();
int personId = 0;
int size = 10000;
int groupsize = 6;
for (int gi = 0; gi < groupsize; gi++) {
Long orgId = Long.valueOf(gi);
for (int pi = gi * size; pi < (gi + 1) * size; pi++) {
personId = pi;
Object personKey1 = new PersonKey(Long.valueOf(personId), orgId);
Person2 p = new Person2(personKey1, Long.valueOf(personId), orgId,
"FN123", "LN456");
cachePerson.put(personId, p);
}
}
SqlQuery sqlPer = new SqlQuery(Person2.class, "orgId=?");
Long st = System.currentTimeMillis();
int cnt = 0;
Person2 rp = null;
try (QueryCursor<Entry<Object, Person2>> cursor =
cachePerson.query(sqlPer.setArgs("2"))) {
for (Entry<Object, Person2> e : cursor) {
rp = e.getValue();
cnt++;
}
System.out.println("return rownum:=" + cnt);
}
cachePerson.clear();
cachePerson.destroy();
ignite.close();
}
}
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p6128.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to accelerate the query speed in Partitioned Mode
Posted by AndreyVel <an...@gmail.com>.
Hello ght230
Do you use setAffinityMapper to put selected rows in one partition?
cacheCfg.setAffinityMapper(new AffinityKeyMapper() {
@Override
public Object affinityKey(Object key) {
if (key instanceof PersonKey){
PersonKey det = (PersonKey)key;
return pk.getGroupId();
}
return key;
}
});
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-accelerate-the-query-speed-in-Partitioned-Mode-tp5826p5837.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.