OurSQL Episode 166: Top Hat Options

This week we discuss fine-tuning Galera Cluster. In this week's ear candy we talk about recent changes to mysqldump and locking; At the Movies presents "Using Amazon Web Services for MySQL at Scale".

Events
DB Hangops - every other Wednesay at noon Pacific time
FOSDEM 2014 - Sat February 1 - Sun February 2 in Brussels, Belgium.
Upcoming MySQL events

Training
SkySQL Trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings

Tuning Galera Cluster
Episode 165, where we talked about how to set up Galera Cluster.

MariaDB's Galera Cluster Configuration Variables page, which is missing a lot of information.

Galera Parameters from Codership, including gcs.recv_q_hard_limit, gcs.recv_q_soft_limit, gcs.max_throttle

Galera Cluster Configuration from MariaDB
Percona's documentation on wsrep-system-index
Percona's documentation on wsrep-status-index

Codership's documentation of status and monitoring
We talk about setting and configuring:
binlog_format
wsrep_forced_binlog_format
innodb_autoinc_lock_mode - Details on autoincrement lock methods
innodb_locks_unsafe_for_binlog
innodb_doublewrite
innodb_flush_log_at_trx_commit - also refer to Episode 37, where we described innodb_flush_log_at_trx_commit

Codership's Galera Configuration page
wsrep_provider_options: gcs.recv_q_hard_limit, gcs.recv_q_soft_limit, gcs.max_throttle, gcache.size

To set up a queue hard limit of 16 Gb, start throttling when the receive queue is 20% full, but only throttle down to 10% of full replication rate (do not stop completely, do not throttle down to 9% or lower), you would put the following in your configuration file:
wsrep_provider_options="gcs.recv_q_hard_limit=16G,gcs.recv_q_soft_limit=0.20,gcs.max_throttle=0.1"

Codership's page on frequently used wsrep settings
We also discussed the following system variables:
wsrep_cluster_address
wsrep_cluster_name
wsrep_node_address
wsrep_node_name
wsrep_sst_donor
wsrep_slave_threads and the wsrep_cert_deps_distance status variables

Configuration tips from Codership

SHOW GLOBAL STATUS LIKE 'wsrep%';
Codership documentation of Galera status variables
We discussed in particular:
wsrep_local_state_uuid
wsrep_replicated and wsrep_replicated_bytes
wsrep_received and wsrep_received_bytes
wsrep_local_commits
wsrep_local_cert_failures
wsrep_local_bf_aborts
wsrep_local_replays
wsrep_local_send_queue and wsrep_local_send_queue_avg
wsrep_local_recv_queue and wsrep_local_recv_queue_avg
wsrep_flow_control_paused, wsrep_flow_control_sent and wsrep_flow_control_recv
wsrep_cert_deps_distance
wsrep_apply_oooe and wsrep_apply_oool
wsrep_apply_window
wsrep_commit_oooe and wsrep_commit_oool
wsrep_commit_window
wsrep_local_state and wsrep_local_state_comment
wsrep_incoming_addresses
wsrep_cluster_conf_id, wsrep_cluster_size and wsrep_cluster_state_uuid
wsrep_cluster_status
wsrep_local_index

If wsrep_ready is OFF, almost all of the queries fill fail with:
ERROR 1047 (08S01) Unknown Command unless wsrep_on session variable is set to 0.

Ear Candy
Domas Mituzas' article about recent changes to mysqldump and locking; bug reported by Eric Bergen.

This week we present Laine Campbell of PalominoDB's "Using Amazon Web Services for MySQL at Scale" presentation from Velocity. Video part 1 and part 2, and an accompanying blog post

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1272