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