# XtraDB Cluster HIgh Load **URL:** \ **Category:** Percona XtraDB Cluster 5.x\ **Created:** [April 30, 2017, 5:04am UTC](https://forums.percona.com/t/xtradb-cluster-high-load/5577 "2017-04-30T05:04:38Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![Mhs](https://avatars.discourse-cdn.com/v4/letter/m/ccd318/32.png) [@Mhs](https://forums.percona.com/u/Mhs)\ **Post date:** [April 30, 2017, 5:04am UTC](https://forums.percona.com/t/xtradb-cluster-high-load/5577/1 "2017-04-30T05:04:38Z")
HI , I want to know is it possible to handle about 3k TPS with XTRADB Cluster and mysql 5.7? I have 5 nodes and each node has 32G Ram with 8 Cores Intel(R) Xeon(R) CPU E5-2660 v4@ 2.00GHz and a haproxy as load balancer . Haproxy handle traffic method is roundrobin .and maxxon is about 8096 . Unfortunately it has poor performance and can only handle about 800 TPS and less than 1000 conenction !!!. I want to know is it possible to handle more than 3K tps and also more than 2k connection with Xtradb and mysql 5.7 . And also i want to know is it better to have 3 node with 64G Ram and 16 Cores CPU or 5 node with 32G ram and 8 Cores CPU. and also my my.cnf : [MYSQLD] user=mysql basedir=/usr/ datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock pid-file=/var/lib/mysql/mysql.pid port=3306 log\_error=/var/log/mysql/mysqld.log log\_warnings=2 # log\_output = FILE ### INNODB OPTIONS innodb\_buffer\_pool\_size=12055M innodb\_flush\_log\_at\_trx\_commit=1 innodb\_file\_per\_table=1 innodb\_data\_file\_path = ibdata1:100M:autoextend ## You may want to tune the below depending on number of cores and disk sub innodb\_read\_io\_threads=4 innodb\_write\_io\_threads=4 innodb\_doublewrite=1 innodb\_log\_file\_size=1024M innodb\_log\_buffer\_size=96M innodb\_buffer\_pool\_instances=-1 innodb\_log\_files\_in\_group=2 innodb\_thread\_concurrency=64 innodb\_io\_capacity=15000 innodb\_io\_capacity\_max=25000 # innodb\_file\_format = barracuda innodb\_flush\_method = O\_DIRECT # innodb\_locks\_unsafe\_for\_binlog = 1 innodb\_autoinc\_lock\_mode=2 ## avoid statistics update when doing e.g show tables innodb\_stats\_on\_metadata=0 default\_storage\_engine=innodb back\_log=1500 # CHARACTER SET # collation\_server = utf8\_unicode\_ci # init\_connect = ‘SET NAMES utf8’ # character\_set\_server = utf8 thread\_handling = pool-of-threads # REPLICATION SPECIFIC server\_id=1 binlog\_format=ROW # log\_bin = binlog # log\_slave\_updates = 1 # gtid\_mode = ON # enforce\_gtid\_consistency = 1 # relay\_log = relay-bin # expire\_logs\_days = 7 # OTHER THINGS, BUFFERS ETC tmp\_table\_size = 64M max\_heap\_table\_size = 64M max\_allowed\_packet = 512M # sort\_buffer\_size = 256K # read\_buffer\_size = 256K # read\_rnd\_buffer\_size = 512K # myisam\_sort\_buffer\_size = 8M skip\_name\_resolve memlock=0 sysdate\_is\_now=1 max\_connections=5000 thread\_cache\_size=512k query\_cache\_type = 0 query\_cache\_size = 0 table\_open\_cache=1024 lower\_case\_table\_names=0 # 5.6 backwards compatibility (FIXME) # explicit\_defaults\_for\_timestamp = 1 ## ## WSREP options ## performance\_schema = ON performance-schema-max-mutex-classes = 0 performance-schema-max-mutex-instances = 0 # Full path to wsrep provider library or ‘none’ wsrep\_provider=/usr/lib/libgalera\_smm.so wsrep\_on=ON wsrep\_node\_address=192.168.100.11 # Provider specific configuration options wsrep\_provider\_options=“base\_port=4567; gcache.size=1024M; gmcast.segment=0” # Logical cluster name. Should be the same for all nodes. wsrep\_cluster\_name=“my\_wsrep\_cluster” # Group communication system handle wsrep\_cluster\_address=gcomm://192.168.100.11,192.168.100.12,192.168.100.13,192.168.100.14,192.168.100.15 # Human\_readable node name (non-unique). Hostname by default. wsrep\_node\_name=192.168.100.11 # Address for incoming client connections. Autodetect by default. #wsrep\_node\_incoming\_address= # How many threads will process writesets from other nodes wsrep\_slave\_threads=4 # DBUG options for wsrep provider #wsrep\_dbug\_option # Generate fake primary keys for non-PK tables (required for multi-master # and parallel applying operation) wsrep\_certify\_nonPK=1 # Location of the directory with data files. Needed for non-mysqldump # state snapshot transfers. Defaults to mysql\_real\_data\_home. #wsrep\_data\_home\_dir= # Maximum number of rows in write set wsrep\_max\_ws\_rows=131072 # Maximum size of write set wsrep\_max\_ws\_size=1073741824 # to enable debug level logging, set this to 1 wsrep\_debug=0 # convert locking sessions into transactions wsrep\_convert\_LOCK\_to\_trx=0 # how many times to retry deadlocked autocommits wsrep\_retry\_autocommit=1 # change auto\_increment\_increment and auto\_increment\_offset automatically wsrep\_auto\_increment\_control=1 # replicate myisam, not supported in PXC 5.7 wsrep\_replicate\_myisam=0 # retry autoinc insert, which failed for duplicate key error wsrep\_drupal\_282555\_workaround=0 # enable “strictly synchronous” semantics for read operations wsrep\_causal\_reads=0 ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [May 17, 2017, 9:51am UTC](https://forums.percona.com/t/xtradb-cluster-high-load/5577/2 "2017-05-17T09:51:54Z")
Hi, That of course depends - what kind of transactions, what network latency between the nodes (remember network latency directly influences commits), what disks, etc, etc. The wsrep\_slave\_threads also looks small in your config as well as innodb\_flush\_log\_at\_trx\_commit=1 is too strict perhaps. You may be interested in this article btw: [url][https://www.percona.com/blog/2017/04/19/performance-improvements-percona-xtradb-cluster-5-7-17/[/url]](https://www.percona.com/blog/2017/04/19/performance-improvements-percona-xtradb-cluster-5-7-17/%5B/url%5D)