Galera configuration tips required for best usage

[DRivard]

Hi,

I finally got the new hardware for my cluster so now we are running a full 3 nodes cluster using HP servers with Intel Xeon CPU X5690@ 3.47GHz, 156GB of RAM and a RAID-5 array of 7.4TB and the file system is XFS.

I set up the cluster using Ubuntu 12.04 and now I am looking for the best configuration of my.cnf and wsrep.cnf.

my my.cnf file looks like :

[mysqld_safe]

socket          = /var/run/mysqld/mysqld.sock

nice            = 0

[mysqld]

skip-external-locking

bind-address            = 0.0.0.0

key_buffer              = 16M

max_allowed_packet      = 16M

thread_stack            = 192K

thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit       = 512M

query_cache_size        = 512M

expire_logs_days        = 10

max_binlog_size         = 100M

event-scheduler         = OFF

max_connections         = 500

default-storage-engine  = InnoDB

log-error               = /var/log/mysql/error.log

innodb_file_per_table

innodb_flush_log_at_trx_commit  = 0

innodb_buffer_pool_size         = 128G

innodb_log_buffer_size          = 256M

innodb_log_file_size            = 512M

innodb_thread_concurrency       = 8

innodb_flush_method             = O_DIRECT

[mysqldump]

quick

quote-names

max_allowed_packet      = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

key_buffer              = 16M

!includedir /etc/mysql/conf.d/

my wsrep.cnf looks like:

[mysqld]

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

query_cache_size=0

query_cache_type=0

bind-address=0.0.0.0

wsrep_provider=/usr/lib/galera/libgalera_smm.so

wsrep_cluster_name="datavalet_cluster"

wsrep_cluster_address="gcomm://*.*.*.*:4567"

wsrep_node_address=*.*.*.*

wsrep_slave_threads=1

wsrep_provider_options="gcache.size=25G"

wsrep_certify_nonPK=1

wsrep_max_ws_rows=131072

wsrep_max_ws_size=1073741824

wsrep_debug=0

wsrep_convert_LOCK_to_trx=0

wsrep_retry_autocommit=1

wsrep_auto_increment_control=1

wsrep_drupal_282555_workaround=0

wsrep_causal_reads=0

wsrep_notify_cmd=

wsrep_sst_method=mysqldump

wsrep_sst_auth=root:********

How can I make this more efficient for production (most of the the activities are insert in DB and some times per month it is about reporting on about 15GB tables)?

Thank you for your help.

Dominick

[Alex Yurchenko]

Hi Dominic,

Performance tuning is largely and interactive thing: it depends on your
workload and status variables readings. To begin with you need to
identify what's wrong with your performance.
But one thing you can try here is setting wsrep_slave_threads=16
(unless you have a table that has no primary key but has unique keys)

Regards,
Alex

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