I am somewhat new to the Cluster based setups but have ran standard mysql for some time now…
I currently have in Dev a 2 node cluster (This is dev so I know it should be 3 or 5 nodes) where I have been playing around with cluster.
What I have noticed is about every 24-48 hours I see extended high CPU usage on the box… once I recycle the mysql process the load decreases quite a bit… generally from a overall system load of around 10 down to a 2 level
The systems are not super beefy but decent… 16 core 16 gig each
Any guesses?
[url]https://snapshots-g504.percona.com/dashboard/snapshot/2CDwrLVt1MGnf2P2z2Gwa04xcg62nHFw[/url]
Average querys are around 1200 querys per second so not major (well indexed and joins are controlled)
wsrep file is
[mysqld]
Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://xxx,xxx
In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
Slave thread to use
wsrep_log_conflicts
This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
Node IP address
wsrep_node_address=xxx
Cluster name
wsrep_cluster_name=dev
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=xxxx
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth=“sstuser:s3cret”
wsrep_slave_threads=64
wsrep_provider_options=“gcache.size=2G”
mysqld.conf is
socket=/var/lib/mysql/mysql.sock
[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
server-id=1
datadir=/app/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=1
userstat=1
binlog_cache_size = 256M
query-cache-type = 1
query-cache-size = 30M
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
innodb_thread_concurrency=0
innodb_read_io_threads=10
innodb_write_io_threads=10
innodb_purge_threads=10
innodb_flush_method=O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1024M
innodb-file-per-table = 1
innodb-buffer-pool-size = 22G
innodb_stats_on_metadata=0
innodb_lock_wait_timeout=19000
innodb_io_capacity_max=60000
innodb-doublewrite=0
innodb_log_buffer_size=4M
innodb_change_buffer_max_size=50
innodb_support_xa = 0
innodb_flush_log_at_trx_commit = 0
max-connections=1000