Re: [percona-group] Is it normal ? Percona cluster (with HAProxy) can't balance the load from tpcc-mysql

[Jay Janssen]

On Oct 19, 2012, at 1:19 PM, JOE YU <smar...@gmail.com> wrote:

2. During the execution of  tpcc-mysql(tpcc_load and tpcc_start) in the testing via VIP(192.168.1.200), i can only see one server node (from 1..3) was chosen to take the whole work load and finished the test. Other two nodes are idle (except running SST sync). I have tried HAProxy balance policy from leastconn to roundrobin but it is not likely the reason. The work load from test client seems bypass the HAProxy and randomly select one database node and running all the tpcc testing routines on it.

So is it normal behave of tpcc-load or there are some problem in the above cluster architecture? We want to build a high available Percona XtraDB Cluster with the float IP that transparent to the front application. (our applicaiton are pure java codes and access the database via jdbc)

HAproxy will only balance by connections, so if tpcc-load is only starting one connection, that might be your issue.  What does your HAproxy config look like?

Jay Janssen, Senior MySQL Consultant, Percona Inc.

http://about.me/jay.janssen

Percona Live in London, UK Dec 3-4th: http://www.percona.com/live/london-2012/

[Yves Trudeau]

Hi Jay,

   you could try a small round-robin load balance like "balance": http://www.inlab.de/balance.html

Regards,

Yves

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
 
 

--
You received this message because you are subscribed to the Google Groups "Experts" group.
To post to this group, send email to exp...@percona.com.
Visit this group at http://groups.google.com/a/percona.com/group/experts/?hl=en-US.
 
 

--
Regards,

Yves
skype: y.trudeau
aim: ytrudeaumysql
gtalk/Jabber: trud...@gmail.com
email: yv...@percona.com
Phone: (408) 457-0699

Percona Live in NYC Oct 1-2nd: http://www.percona.com/live/nyc-2012/

[Joe,Yu]

Thanks Jay,

Below are my HAProxy configuration and testing parameter when execute tpcc testing.

========================================================

global

log 127.0.0.1 local0

log 127.0.0.1 local1 notice

maxconn 4096

defaults

log global

mode http

option tcplog

option dontlognull

retries 3

option redispatch

maxconn 4096

contimeout 5000

clitimeout 50000

srvtimeout 50000

 

frontend pxc-front

bind *:3307

mode tcp

default_backend pxc-back

backend pxc-back

mode tcp

balance leastconn

option httpchk

server n1 192.168.1.241:3306 check port 9200 inter 12000 rise 3 fall 3

server n2 192.168.1.242:3306 check port 9200 inter 12000 rise 3 fall 3

server n3 192.168.1.243:3306 check port 9200 inter 12000 rise 3 fall 3

frontend stats-front

bind *:80

mode http

default_backend stats-back

backend stats-back

mode http

balance roundrobin

stats uri /haproxy/stats

stats auth root:hello

========================================

./tpcc_load 192.168.1.200:3307 tpcc root hello 1

./tpcc_start -h 192.168.1.200 -P3307 -d tpcc -u root -p hello -w 1 -c 500 -r 10 -l 1200 -i 1 -f tpcc_report.log -t tpcc_report.trx

Here connection i use 1 because if using connection more than this will cause lots of deadlock problem that suspend the testing.

The error message is "1213, 40001, Deadlock found when trying to get lock; try restarting transaction"

As you mentioned that HAProxy is only monitoring tcp connection in this case but the tpcc-mysql tool can only using single connection to avoid deadlock. I have found that there is a subproject on launchpard named tpcc-mysql-cluster, Is that the right benchmark tool for me in this scenario ?

Thanks, 

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
 
 

--
jOe

[Jay Janssen]

On Oct 19, 2012, at 9:13 PM, "Joe,Yu" <smar...@gmail.com> wrote:

Here connection i use 1 because if using connection more than this will cause lots of deadlock problem that suspend the testing.

The error message is "1213, 40001, Deadlock found when trying to get lock; try restarting transaction"

Yup, sysbench that is allowed to write to multiple nodes in galera will cause this.  I tried to fully explain this problem and give solutions here:

http://www.mysqlperformanceblog.com/2012/08/17/percona-xtradb-cluster-multi-node-writing-and-unexpected-deadlocks/

But, the short of it is this is just a fact of how Galera works.    We typically recommend people migrating to PXC to use two ports in HAproxy configured something like this:

https://github.com/jayjanssen/percona-xtradb-cluster-tutorial/blob/master/modules/haproxy/files/xtradb_cluster.cfg

Send writes to 4306 in that example, and reads across 3306.  Note that this is never safe to do within a single transaction (which is what sysbench tests normally), and any transactions including writes should be sent to the same node or you will have this deadlock problem.  

As you mentioned that HAProxy is only monitoring tcp connection in this case but the tpcc-mysql tool can only using single connection to avoid deadlock.

HAproxy balances connections, plain and simple, so what you are seeing is expected.   It cannot split up the queries you are sending to that single connection dynamically across the mysql protocol.  You could run some read-only sysbench workload through HAproxy onto all nodes to get a feel for all the nodes doing work.  

I have found that there is a subproject on launchpard named tpcc-mysql-cluster, Is that the right benchmark tool for me in this scenario ?

Don't confuse Percona XtraDB cluster (which is Galera cluster) with MySQL cluster (which is NDB).  If you see "MySQL cluster", it almost always means NDB (http://www.mysql.com/products/cluster/).    

[Alex Yurchenko]

Hi Joe,

Basically you can say that tpcc-mysql is an example of an (arguably
broken) application that cannot use XtraDB Cluster in multi-master mode
(because it chooses to freak out on unexpected deadlock instead of
retrying the transaction). However if you have such an application, you
still can use XtraDB Cluster in good old master-slave mode by directing
all writing transactions to a single ("master") node. You still will get
all other benefits of Galera replication: synchronous data safety,
parallel applying, (near) 0 slave lag, automatic node provisioning and
so on.

Or you could fix tpcc-mysql - it should be rather trivial. ;)

Regards,
Alex

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011