We currently run ~ 900 WordPress blogs on several machines so was decided to move to load balanced environment with clustered db.
I followed instructions from http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/cenots_howto.html, installed on 3 nodes Percona's XtraDB and imported DB dump, total DB's size is around 40 GB.
Now if there are no inserts in DB queries work fast and all is ok but if we start to do some inserts (4 – 10 inserts per second) on nodes all queries begin to slow down very dramatically reads and writes.
Hardware :
Intel(R) Core(TM) i7 CPU 950 3.07GHz with 26 GB RAM
Xeon E5-2670 0 @ 2.60GHz with 64 GB RAM
my.conf
[mysqld]
datadir=/var/lib/mysql
user=mysql
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.0.93,192.168.0.94,192.168.0.95
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# Node #2 address
wsrep_node_address=192.168.0.94
# Cluster name
wsrep_cluster_name=my_centos_cluster
# SST method
wsrep_sst_method=xtrabackup
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
innodb_log_file_size = 50331648
# Bellow begins different testing values
wait_timeout=20
interactive_timeout=30
key_buffer_size=256M
query_cache_limit=256M
sort_buffer_size=128M
read_rnd_buffer_size=128M
tmp_table_size=1G
max_heap_table_size=1G
innodb_file_per_table=1
innodb_data_file_path = ibdata1:512M:autoextend
innodb_fast_shutdown = 0
innodb_buffer_pool_size = 6144M
innodb_buffer_pool_instances = 4
innodb_read_io_threads = 64
innodb_write_io_threads = 64
# CACHES AND LIMITS #
max_connections = 200
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DSYNC
#autocommit=OFF
sync_binlog=0
innodb_thread_concurrency = 0
innodb_io_capacity = 5000
bulk_insert_buffer_size = 256M
innodb_locks_unsafe_for_binlog=1
innodb_stats_on_metadata=0
What can be problem ?
Machines run in same LAN with ~ 0.1 ms latency
and cluster is deployed under OpenVZ virtualisation
Best Answer
Galera/Percona XtraDB Cluster is an solution that provides high availability, read scaling and full consistency. Although in some cases it can provide some write scaling (mainly due to poor SQL writing), it is not focused on it, as it does not provide sharding, so it has to write to all nodes in a (virtually) synchronous way. This is important to decide if Galera is for you.
Having said that, some comments in your setup:
Virtualisation, while not a problem per-se, can add some component of indeterminism, which is critical for Galera as it will perform as bad as the worst performing node. Make sure all nodes have a predictable performance.
you can reduce some durability parameters in the cluster (for example, setting innodb_flush_log_at_trx_commit = 2), as HA will be provided by node redundancy in the case of a crash. This will increase performance.
Certain workloads may need client changes, as they are hurtful for Galera performance. In particular, frequent updates on the same rows can case collision and frequent rollbacks of transactions, reducing the throughout. Identify hotspots with the Percona/XtraDB cluster statistics and profiling and try to avoid them from client side. In some cases, some commercial applications may need to change some code slightly to adapt to a cluster infrastructure.
Network performance is critical. You say that the latency between nodes is 0.1 ms. A transaction will take an overhead at least equal to double the latency of the slowest node.