First touch: Galera clustering for MySQL

hingo's picture

I've been following the Galera project for years now, but this week I finally had some time to spend hands on time on it. I'm sure you noticed Vadim from Percona is also looking at it, it will be interesting to compare results. In this post I will just share the experience of installing and configuring Galera.

Finding the information

First thing is that there is no project page like galera.org, rather you have to visit www.codership.com, Codership being the small Finnish company that created Galera. The next thing is that it is a bit hard to find what I'm looking for. Galera requires patching MySQL/InnoDB so for convenience they provide their own MySQL binaries - but this is based on MySQL 5.1. Since that means poor InnoDB performance, I'm instead interested to try their MariaDB based version that is using XtraDB from Percona. I couldn't find it via the website, but then I remember Vadim links to the launchpad project in his post: lp:coership-maria.

Update: Alex informs in comments that innodb_plugin is supported for 5.1. I got the impression from Vadim only the built-in innodb was supported. I suppose the plugin is good enough that I won't build from source (but otoh if Percona Server could include Galera in their binaries...)

They do link to the README file which provides a good overview of Galera, how to configure and start it up, and even a list of limitations compared to single node MySQL. The only limitation I'm a bit concerned about is that only InnoDB tables are replicated, so your mysql.* system tables are not replicated. This reminds me of one of the favorite limitations of MySQL Cluster, where this would cause a lot of confusion. For instance, if you do GRANT ALL ON *.* TO myuser on one node but not the others, then that user can only access that node. This could of course sometimes be used as a feature, but generally it is just confusing. Anyway, best practice around this is to script all your user management and other actions affecting mysql.* tables, so that you can run the exact same script against all nodes. Update: Alex corrects me that GRANT command is properly replicated, only if you UPDATE mysql.* tables directly will they not be replicated.

I also joined the low volume Codership Google Group where I can see Vadim already sent some questions as part of his efforts.

In the end I found the reference manual on the Galera wiki quite good and will read more of it going forward.

Installing and starting a cluster

One aspect I'm interested in evaluating is ease of use. To test that I decided to first try the demo download, as compiling something from source isn't a relevant test for that! I'm hoping Galera will turn out to be simpler than MySQL replication, as I don't need to fiddle with binary log positions. Not to mention semi-sync replication, which is an additional set of management on top of standard MySQL replication.

The demo edition comes with a QUICK_START text file that makes this easy.

It recommends to setup 3 nodes, mainly so that in case of network problems the nodes can know which part of the cluster has quorum and which one is left as minority and must die. I'll later look into using Galera with only 2 nodes as I'm sure many want to do that too.

Node 3:

./mysql-galera -g gcomm:// start
$ mysql -u root -prootpass -h 127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.53 wsrep_0.8.0
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.53 |
+-----------+
1 row in set (0.00 sec)
 
mysql>

Node 2

./mysql-galera -g gcomm://[node 3] start
Starting mysqld instance with data dir /data/b/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var and listening at port 3306 and socket /data/b/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld.sock...../mysql-galera: line 172: 4524 Aborted (core dumped) nohup $VALGRIND $MYSQLD $DEFAULTS_OPTION --user="$MYSQLD_USER" --basedir="$MYSQL_BASE_DIR" --datadir="$MYSQL_DATA_DIR" --pid-file="$MYSQL_PID" --port=$MYSQL_PORT --socket=$MYSQL_SOCKET --skip-external-locking --log_error=$err_log $MYSQLD_OPTS $INNODB_OPTS $WSREP_OPTS $DEBUG_OPTS $LOGGING_OPTS $RBR_OPTS $PLUGIN_OPTS > /dev/null 2>> $err_log
Failed (PID:4524)

Ok, so I succeed starting it on one node but get core dumped on the 2 others. This can easily happen when you download software as tar files and not rpm, where dependencies are (hopefully) explicitly defined. As it happens, the succeeding node is one where I had just done sudo yum groupinstall "Development Tools". So I install those on the other servers as well, it's a large bundle and also some existing packages are updated, but I don't dig deeper into what particularly it was missing.

Node 2

# ./mysql-galera -g gcomm://[node 3] start
Starting mysqld instance with data dir /data/c/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var and listening at port 3306 and socket /data/c/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld.sock.... Done (PID:3947)
Waiting for wsrep_ready... Done

Node 1

# ./mysql-galera -g gcomm://[node 3] start
Starting mysqld instance with data dir /data/b/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var and listening at port 3306 and socket /data/b/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld.sock.... Done (PID:14574)
Waiting for wsrep_ready... Done
 
# mysql -uroot -prootpass -h 127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.53 wsrep_0.8.0
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql> create table t (k int primary key, v blob);
Query OK, 0 rows affected (0.03 sec)
 
mysql> show create table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`k` int(11) NOT NULL,
`v` blob,
PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Alright! 3 Galera nodes up and running. Even if this is MySQL 5.1, they've set InnoDB as the default storage engine (the only one replicated by Galera) so you don't have to specify it in the create table statement.

It's important to specify an explicit primary key. I always do that, but in simple tests like this you often omit it. Galera apparently needs it for DELETE operations. (I'm surprised UPDATEs apparently work without primary keys.)

I expected ease of use and I'm not disappointed: I don't need to setup server names in all directions, or binlog names and positions. I just tell the 2 nodes to join the cluster referenced by the first node, and they can figure out what to do. (It remains to be tested how this changes when I add nodes to a live system that is receiving updates while the new node is being provisioned...)

So let's see if it actually replicates something:

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`k` int(11) NOT NULL,
`v` blob,
PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from t;
+---+------------+
| k | v |
+---+------------+
| 1 | first row |
| 2 | second row |
+---+------------+
2 rows in set (0.00 sec)
 
mysql>

Node 3

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`k` int(11) NOT NULL,
`v` blob,
PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from t;
+---+------------+
| k | v |
+---+------------+
| 1 | first row |
| 2 | second row |
+---+------------+
2 rows in set (0.00 sec)
 
mysql>

Of course it does.

And as my last "first touch" activity, let's see what new variables and status information Galera brings into MySQL. As the lists are long, I make comments here, and copy paste them below.

First observation is that I'm used to using semicolon with show variables, but some of the galera variables have so long value part that it messes up the output and you have to use \G instead. In fact, there is a bug open in launchpad about the fact that the maximum size of 1024 characters for the value is not enough for them!

From the top you immediately see that Galera has set the auto_increment_offset and auto_increment_increment values so that auto_increment columns shouldn't create problems. At the ende there is the list of wsrep_* variables where you see that wsrep_autoincrement_control=ON.

They also set innodb_flush_log_at_trx_commit=0, the rationale here is that since data is synchronously replicated across the network, then writing it to disk is of secondary importance. (Same philosophy as MySQL Cluster NDB, but for InnoDB.) I expect big performance gain due to this, will have to test later. sync_binlog is of course also 0, as no binlog is being written. (But theoretically you could do that and even add MySQL replication slaves to the mix - not something I ever want to see myself doing!)

innodb_doublewrite is OFF, from the manual I don't understand what this variable does. I would expect it to increase performance, but the manual suggests that OFF increases performance. (Because there is then only one write? Where?)

Query cache is set to off. A TODO would be to check whether Galera properly invalidates query cache on all nodes if I set it to on. (To be honest, I have no particular need to keep it on, just being pedantic here.)

server_id is zero. This is not used by Galera replication. (Apparently I can use a mix of both Galera and classic MySQL replication, but I have absolutely no intent to ever do so!)

Apparently the wsrep_causal_reads variable already exists, I was under the impression this feature doesn't exist yet...

I'm surprised to find wsrep_cluster_address="gcomm://[hostname]" as a variable. This is the hostname of the first node that was started in the cluster, telling other nodes where to connect to the cluster. Turns out I can change this value with SET GLOBAL. If I change it on all nodes, replication still works. I didn't try changes that I suspect could break things, like pointing different nodes at different places. I don't know what the significance of this setting is, but I'm guessing if you wanted to shut down the first node, you should first change this value so the other nodes use someone else as the "first" node, aka cluster address.

Apparently I should learn to set wsrep_cluster_name, but the default value is ok for now. Having wsrep_node_name remain the same as system hostname seems like a good idea though.

Tuning opportunity: for large write transactions, increase value of wsrep_max_* variables. By default you can only do inserts and updates that are smaller than 65k rows and 1Gb size.

wsrep_notify_cmd seems like a useful thing to have in a production setting: "A command to run when cluster membership or state of this node changes. Notification command arguments." Should be easy to integrate with some alarm or HA tool.

wsrep_on: Interesting that I can turn off replication on a per-session basis! (Easy way to shoot yourself in the foot, but could sometimes be used for small fixes or interesting features.)

wsrep_provider_options: Long string concatenating all gcomm options. Seems like a monster to read, but they allow you to set the components individually, you don't need to repeat the long string.

Another tuning opportunity: wsrep_slave_threads. Increase this if you experience slave lag or need more replication throughput. (Seppo said that it helps in disk bound workload. With 16 parallel slave threads he gets 4x improvement.)

Also in the SHOW GLOBAL STATUS output the Galera variables all start with wsrep_*. I don't pretend to understand what all of them means, but it is interesting to note which values are similar and different on each node. For instance the wsrep_local_state_uuid was for me identical on all nodes and also identical to wsrep_cluster_state_uuid. Of course, the name suggest it could sometimes be not-identical too. A bit non-intuitive is that wsrep_cluster_size is not the same value on all nodes, rather it is 1, 2 and 3 in the order that nodes joined the cluster (1 for the first node). Also interesting to note that two nodes are in Primary status, but one is non-Primary.

Just like the configuration options, also status variables are documented on the Galera Wiki.

That's it for today. Next time I will see what happens when you start and stop nodes a lot. For that I will have to learn how to re-provision a node that is out of sync. The I'll also do some performance testing.

mysql> show variables\G
*************************** 1. row ***************************
Variable_name: auto_increment_increment
Value: 3
*************************** 2. row ***************************
Variable_name: auto_increment_offset
Value: 2 [On the two other nodes, this value is 1 and 3]
*************************** 3. row ***************************
Variable_name: autocommit
Value: ON
*************************** 4. row ***************************
Variable_name: automatic_sp_privileges
Value: ON
*************************** 5. row ***************************
Variable_name: back_log
Value: 50
*************************** 6. row ***************************
Variable_name: basedir
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/
*************************** 7. row ***************************
Variable_name: big_tables
Value: OFF
*************************** 8. row ***************************
Variable_name: binlog_cache_size
Value: 32768
*************************** 9. row ***************************
Variable_name: binlog_direct_non_transactional_updates
Value: OFF
*************************** 10. row ***************************
Variable_name: binlog_format
Value: ROW
*************************** 11. row ***************************
Variable_name: bulk_insert_buffer_size
Value: 8388608
*************************** 12. row ***************************
Variable_name: character_set_client
Value: utf8
*************************** 13. row ***************************
Variable_name: character_set_connection
Value: utf8
*************************** 14. row ***************************
Variable_name: character_set_database
Value: latin1
*************************** 15. row ***************************
Variable_name: character_set_filesystem
Value: binary
*************************** 16. row ***************************
Variable_name: character_set_results
Value: utf8
*************************** 17. row ***************************
Variable_name: character_set_server
Value: latin1
*************************** 18. row ***************************
Variable_name: character_set_system
Value: utf8
*************************** 19. row ***************************
Variable_name: character_sets_dir
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/share/mysql/charsets/
*************************** 20. row ***************************
Variable_name: collation_connection
Value: utf8_general_ci
*************************** 21. row ***************************
Variable_name: collation_database
Value: latin1_swedish_ci
*************************** 22. row ***************************
Variable_name: collation_server
Value: latin1_swedish_ci
*************************** 23. row ***************************
Variable_name: completion_type
Value: 0
*************************** 24. row ***************************
Variable_name: concurrent_insert
Value: 1
*************************** 25. row ***************************
Variable_name: connect_timeout
Value: 10
*************************** 26. row ***************************
Variable_name: datadir
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/
*************************** 27. row ***************************
Variable_name: date_format
Value: %Y-%m-%d
*************************** 28. row ***************************
Variable_name: datetime_format
Value: %Y-%m-%d %H:%i:%s
*************************** 29. row ***************************
Variable_name: default_week_format
Value: 0
*************************** 30. row ***************************
Variable_name: delay_key_write
Value: ON
*************************** 31. row ***************************
Variable_name: delayed_insert_limit
Value: 100
*************************** 32. row ***************************
Variable_name: delayed_insert_timeout
Value: 300
*************************** 33. row ***************************
Variable_name: delayed_queue_size
Value: 1000
*************************** 34. row ***************************
Variable_name: div_precision_increment
Value: 4
*************************** 35. row ***************************
Variable_name: engine_condition_pushdown
Value: ON
*************************** 36. row ***************************
Variable_name: error_count
Value: 0
*************************** 37. row ***************************
Variable_name: event_scheduler
Value: OFF
*************************** 38. row ***************************
Variable_name: expire_logs_days
Value: 0
*************************** 39. row ***************************
Variable_name: flush
Value: OFF
*************************** 40. row ***************************
Variable_name: flush_time
Value: 0
*************************** 41. row ***************************
Variable_name: foreign_key_checks
Value: ON
*************************** 42. row ***************************
Variable_name: ft_boolean_syntax
Value: + -><()~*:""&|
*************************** 43. row ***************************
Variable_name: ft_max_word_len
Value: 84
*************************** 44. row ***************************
Variable_name: ft_min_word_len
Value: 4
*************************** 45. row ***************************
Variable_name: ft_query_expansion_limit
Value: 20
*************************** 46. row ***************************
Variable_name: ft_stopword_file
Value: (built-in)
*************************** 47. row ***************************
Variable_name: general_log
Value: OFF
*************************** 48. row ***************************
Variable_name: general_log_file
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld.log
*************************** 49. row ***************************
Variable_name: group_concat_max_len
Value: 1024
*************************** 50. row ***************************
Variable_name: have_community_features
Value: YES
*************************** 51. row ***************************
Variable_name: have_compress
Value: YES
*************************** 52. row ***************************
Variable_name: have_crypt
Value: YES
*************************** 53. row ***************************
Variable_name: have_csv
Value: YES
*************************** 54. row ***************************
Variable_name: have_dynamic_loading
Value: YES
*************************** 55. row ***************************
Variable_name: have_geometry
Value: YES
*************************** 56. row ***************************
Variable_name: have_innodb
Value: YES
*************************** 57. row ***************************
Variable_name: have_ndbcluster
Value: NO
*************************** 58. row ***************************
Variable_name: have_openssl
Value: NO
*************************** 59. row ***************************
Variable_name: have_partitioning
Value: NO
*************************** 60. row ***************************
Variable_name: have_query_cache
Value: YES
*************************** 61. row ***************************
Variable_name: have_rtree_keys
Value: YES
*************************** 62. row ***************************
Variable_name: have_ssl
Value: NO
*************************** 63. row ***************************
Variable_name: have_symlink
Value: YES
*************************** 64. row ***************************
Variable_name: hostname
Value: ...
*************************** 65. row ***************************
Variable_name: identity
Value: 0
*************************** 66. row ***************************
Variable_name: ignore_builtin_innodb
Value: OFF
*************************** 67. row ***************************
Variable_name: init_connect
Value:
*************************** 68. row ***************************
Variable_name: init_file
Value:
*************************** 69. row ***************************
Variable_name: init_slave
Value:
*************************** 70. row ***************************
Variable_name: innodb_adaptive_hash_index
Value: ON
*************************** 71. row ***************************
Variable_name: innodb_additional_mem_pool_size
Value: 1048576
*************************** 72. row ***************************
Variable_name: innodb_autoextend_increment
Value: 8
*************************** 73. row ***************************
Variable_name: innodb_autoinc_lock_mode
Value: 2
*************************** 74. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 440401920
*************************** 75. row ***************************
Variable_name: innodb_checksums
Value: ON
*************************** 76. row ***************************
Variable_name: innodb_commit_concurrency
Value: 0
*************************** 77. row ***************************
Variable_name: innodb_concurrency_tickets
Value: 500
*************************** 78. row ***************************
Variable_name: innodb_data_file_path
Value: ibdata1:10M:autoextend
*************************** 79. row ***************************
Variable_name: innodb_data_home_dir
Value:
*************************** 80. row ***************************
Variable_name: innodb_disallow_writes
Value: OFF
*************************** 81. row ***************************
Variable_name: innodb_doublewrite
Value: OFF
*************************** 82. row ***************************
Variable_name: innodb_fast_shutdown
Value: 1
*************************** 83. row ***************************
Variable_name: innodb_file_io_threads
Value: 4
*************************** 84. row ***************************
Variable_name: innodb_file_per_table
Value: OFF
*************************** 85. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 0
*************************** 86. row ***************************
Variable_name: innodb_flush_method
Value:
*************************** 87. row ***************************
Variable_name: innodb_force_recovery
Value: 0
*************************** 88. row ***************************
Variable_name: innodb_lock_wait_timeout
Value: 50
*************************** 89. row ***************************
Variable_name: innodb_locks_unsafe_for_binlog
Value: ON
*************************** 90. row ***************************
Variable_name: innodb_log_buffer_size
Value: 1048576
*************************** 91. row ***************************
Variable_name: innodb_log_file_size
Value: 104857600
*************************** 92. row ***************************
Variable_name: innodb_log_files_in_group
Value: 2
*************************** 93. row ***************************
Variable_name: innodb_log_group_home_dir
Value: ./
*************************** 94. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 90
*************************** 95. row ***************************
Variable_name: innodb_max_purge_lag
Value: 0
*************************** 96. row ***************************
Variable_name: innodb_mirrored_log_groups
Value: 1
*************************** 97. row ***************************
Variable_name: innodb_open_files
Value: 300
*************************** 98. row ***************************
Variable_name: innodb_rollback_on_timeout
Value: OFF
*************************** 99. row ***************************
Variable_name: innodb_stats_on_metadata
Value: ON
*************************** 100. row ***************************
Variable_name: innodb_support_xa
Value: ON
*************************** 101. row ***************************
Variable_name: innodb_sync_spin_loops
Value: 20
*************************** 102. row ***************************
Variable_name: innodb_table_locks
Value: ON
*************************** 103. row ***************************
Variable_name: innodb_thread_concurrency
Value: 8
*************************** 104. row ***************************
Variable_name: innodb_thread_sleep_delay
Value: 10000
*************************** 105. row ***************************
Variable_name: innodb_use_legacy_cardinality_algorithm
Value: ON
*************************** 106. row ***************************
Variable_name: insert_id
Value: 0
*************************** 107. row ***************************
Variable_name: interactive_timeout
Value: 28800
*************************** 108. row ***************************
Variable_name: join_buffer_size
Value: 131072
*************************** 109. row ***************************
Variable_name: keep_files_on_create
Value: OFF
*************************** 110. row ***************************
Variable_name: key_buffer_size
Value: 8384512
*************************** 111. row ***************************
Variable_name: key_cache_age_threshold
Value: 300
*************************** 112. row ***************************
Variable_name: key_cache_block_size
Value: 1024
*************************** 113. row ***************************
Variable_name: key_cache_division_limit
Value: 100
*************************** 114. row ***************************
Variable_name: language
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/share/mysql/english/
*************************** 115. row ***************************
Variable_name: large_files_support
Value: ON
*************************** 116. row ***************************
Variable_name: large_page_size
Value: 0
*************************** 117. row ***************************
Variable_name: large_pages
Value: OFF
*************************** 118. row ***************************
Variable_name: last_insert_id
Value: 0
*************************** 119. row ***************************
Variable_name: lc_time_names
Value: en_US
*************************** 120. row ***************************
Variable_name: license
Value: GPL
*************************** 121. row ***************************
Variable_name: local_infile
Value: ON
*************************** 122. row ***************************
Variable_name: locked_in_memory
Value: OFF
*************************** 123. row ***************************
Variable_name: log
Value: OFF
*************************** 124. row ***************************
Variable_name: log_bin
Value: ON
*************************** 125. row ***************************
Variable_name: log_bin_trust_function_creators
Value: OFF
*************************** 126. row ***************************
Variable_name: log_bin_trust_routine_creators
Value: OFF
*************************** 127. row ***************************
Variable_name: log_error
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/....err
*************************** 128. row ***************************
Variable_name: log_output
Value: FILE
*************************** 129. row ***************************
Variable_name: log_queries_not_using_indexes
Value: OFF
*************************** 130. row ***************************
Variable_name: log_slave_updates
Value: OFF
*************************** 131. row ***************************
Variable_name: log_slow_queries
Value: OFF
*************************** 132. row ***************************
Variable_name: log_warnings
Value: 1
*************************** 133. row ***************************
Variable_name: long_query_time
Value: 10.000000
*************************** 134. row ***************************
Variable_name: low_priority_updates
Value: OFF
*************************** 135. row ***************************
Variable_name: lower_case_file_system
Value: OFF
*************************** 136. row ***************************
Variable_name: lower_case_table_names
Value: 0
*************************** 137. row ***************************
Variable_name: max_allowed_packet
Value: 1048576
*************************** 138. row ***************************
Variable_name: max_binlog_cache_size
Value: 18446744073709547520
*************************** 139. row ***************************
Variable_name: max_binlog_size
Value: 1073741824
*************************** 140. row ***************************
Variable_name: max_connect_errors
Value: 10
*************************** 141. row ***************************
Variable_name: max_connections
Value: 1024
*************************** 142. row ***************************
Variable_name: max_delayed_threads
Value: 20
*************************** 143. row ***************************
Variable_name: max_error_count
Value: 64
*************************** 144. row ***************************
Variable_name: max_heap_table_size
Value: 16777216
*************************** 145. row ***************************
Variable_name: max_insert_delayed_threads
Value: 20
*************************** 146. row ***************************
Variable_name: max_join_size
Value: 18446744073709551615
*************************** 147. row ***************************
Variable_name: max_length_for_sort_data
Value: 1024
*************************** 148. row ***************************
Variable_name: max_prepared_stmt_count
Value: 16382
*************************** 149. row ***************************
Variable_name: max_relay_log_size
Value: 0
*************************** 150. row ***************************
Variable_name: max_seeks_for_key
Value: 18446744073709551615
*************************** 151. row ***************************
Variable_name: max_sort_length
Value: 1024
*************************** 152. row ***************************
Variable_name: max_sp_recursion_depth
Value: 0
*************************** 153. row ***************************
Variable_name: max_tmp_tables
Value: 32
*************************** 154. row ***************************
Variable_name: max_user_connections
Value: 0
*************************** 155. row ***************************
Variable_name: max_write_lock_count
Value: 18446744073709551615
*************************** 156. row ***************************
Variable_name: min_examined_row_limit
Value: 0
*************************** 157. row ***************************
Variable_name: multi_range_count
Value: 256
*************************** 158. row ***************************
Variable_name: myisam_data_pointer_size
Value: 6
*************************** 159. row ***************************
Variable_name: myisam_max_sort_file_size
Value: 9223372036853727232
*************************** 160. row ***************************
Variable_name: myisam_mmap_size
Value: 18446744073709551615
*************************** 161. row ***************************
Variable_name: myisam_recover_options
Value: OFF
*************************** 162. row ***************************
Variable_name: myisam_repair_threads
Value: 1
*************************** 163. row ***************************
Variable_name: myisam_sort_buffer_size
Value: 8388608
*************************** 164. row ***************************
Variable_name: myisam_stats_method
Value: nulls_unequal
*************************** 165. row ***************************
Variable_name: myisam_use_mmap
Value: OFF
*************************** 166. row ***************************
Variable_name: net_buffer_length
Value: 16384
*************************** 167. row ***************************
Variable_name: net_read_timeout
Value: 30
*************************** 168. row ***************************
Variable_name: net_retry_count
Value: 10
*************************** 169. row ***************************
Variable_name: net_write_timeout
Value: 60
*************************** 170. row ***************************
Variable_name: new
Value: OFF
*************************** 171. row ***************************
Variable_name: old
Value: OFF
*************************** 172. row ***************************
Variable_name: old_alter_table
Value: OFF
*************************** 173. row ***************************
Variable_name: old_passwords
Value: OFF
*************************** 174. row ***************************
Variable_name: open_files_limit
Value: 5120
*************************** 175. row ***************************
Variable_name: optimizer_prune_level
Value: 1
*************************** 176. row ***************************
Variable_name: optimizer_search_depth
Value: 62
*************************** 177. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
*************************** 178. row ***************************
Variable_name: pid_file
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld.pid
*************************** 179. row ***************************
Variable_name: plugin_dir
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/lib/mysql/plugin
*************************** 180. row ***************************
Variable_name: port
Value: 3306
*************************** 181. row ***************************
Variable_name: preload_buffer_size
Value: 32768
*************************** 182. row ***************************
Variable_name: profiling
Value: OFF
*************************** 183. row ***************************
Variable_name: profiling_history_size
Value: 15
*************************** 184. row ***************************
Variable_name: protocol_version
Value: 10
*************************** 185. row ***************************
Variable_name: pseudo_thread_id
Value: 10
*************************** 186. row ***************************
Variable_name: query_alloc_block_size
Value: 8192
*************************** 187. row ***************************
Variable_name: query_cache_limit
Value: 1048576
*************************** 188. row ***************************
Variable_name: query_cache_min_res_unit
Value: 4096
*************************** 189. row ***************************
Variable_name: query_cache_size
Value: 0
*************************** 190. row ***************************
Variable_name: query_cache_type
Value: OFF
*************************** 191. row ***************************
Variable_name: query_cache_wlock_invalidate
Value: OFF
*************************** 192. row ***************************
Variable_name: query_prealloc_size
Value: 8192
*************************** 193. row ***************************
Variable_name: rand_seed1
Value:
*************************** 194. row ***************************
Variable_name: rand_seed2
Value:
*************************** 195. row ***************************
Variable_name: range_alloc_block_size
Value: 4096
*************************** 196. row ***************************
Variable_name: read_buffer_size
Value: 131072
*************************** 197. row ***************************
Variable_name: read_only
Value: OFF
*************************** 198. row ***************************
Variable_name: read_rnd_buffer_size
Value: 262144
*************************** 199. row ***************************
Variable_name: relay_log
Value:
*************************** 200. row ***************************
Variable_name: relay_log_index
Value:
*************************** 201. row ***************************
Variable_name: relay_log_info_file
Value: relay-log.info
*************************** 202. row ***************************
Variable_name: relay_log_purge
Value: ON
*************************** 203. row ***************************
Variable_name: relay_log_space_limit
Value: 0
*************************** 204. row ***************************
Variable_name: report_host
Value:
*************************** 205. row ***************************
Variable_name: report_password
Value:
*************************** 206. row ***************************
Variable_name: report_port
Value: 3306
*************************** 207. row ***************************
Variable_name: report_user
Value:
*************************** 208. row ***************************
Variable_name: rpl_recovery_rank
Value: 0
*************************** 209. row ***************************
Variable_name: secure_auth
Value: OFF
*************************** 210. row ***************************
Variable_name: secure_file_priv
Value:
*************************** 211. row ***************************
Variable_name: server_id
Value: 0
*************************** 212. row ***************************
Variable_name: skip_external_locking
Value: ON
*************************** 213. row ***************************
Variable_name: skip_name_resolve
Value: OFF
*************************** 214. row ***************************
Variable_name: skip_networking
Value: OFF
*************************** 215. row ***************************
Variable_name: skip_show_database
Value: OFF
*************************** 216. row ***************************
Variable_name: slave_compressed_protocol
Value: OFF
*************************** 217. row ***************************
Variable_name: slave_exec_mode
Value: STRICT
*************************** 218. row ***************************
Variable_name: slave_load_tmpdir
Value: /tmp
*************************** 219. row ***************************
Variable_name: slave_net_timeout
Value: 3600
*************************** 220. row ***************************
Variable_name: slave_skip_errors
Value: OFF
*************************** 221. row ***************************
Variable_name: slave_transaction_retries
Value: 10
*************************** 222. row ***************************
Variable_name: slow_launch_time
Value: 2
*************************** 223. row ***************************
Variable_name: slow_query_log
Value: OFF
*************************** 224. row ***************************
Variable_name: slow_query_log_file
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld-slow.log
*************************** 225. row ***************************
Variable_name: socket
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/mysqld.sock
*************************** 226. row ***************************
Variable_name: sort_buffer_size
Value: 2097144
*************************** 227. row ***************************
Variable_name: sql_auto_is_null
Value: ON
*************************** 228. row ***************************
Variable_name: sql_big_selects
Value: ON
*************************** 229. row ***************************
Variable_name: sql_big_tables
Value: OFF
*************************** 230. row ***************************
Variable_name: sql_buffer_result
Value: OFF
*************************** 231. row ***************************
Variable_name: sql_log_bin
Value: ON
*************************** 232. row ***************************
Variable_name: sql_log_off
Value: OFF
*************************** 233. row ***************************
Variable_name: sql_log_update
Value: ON
*************************** 234. row ***************************
Variable_name: sql_low_priority_updates
Value: OFF
*************************** 235. row ***************************
Variable_name: sql_max_join_size
Value: 18446744073709551615
*************************** 236. row ***************************
Variable_name: sql_mode
Value:
*************************** 237. row ***************************
Variable_name: sql_notes
Value: ON
*************************** 238. row ***************************
Variable_name: sql_quote_show_create
Value: ON
*************************** 239. row ***************************
Variable_name: sql_safe_updates
Value: OFF
*************************** 240. row ***************************
Variable_name: sql_select_limit
Value: 18446744073709551615
*************************** 241. row ***************************
Variable_name: sql_slave_skip_counter
Value:
*************************** 242. row ***************************
Variable_name: sql_warnings
Value: OFF
*************************** 243. row ***************************
Variable_name: ssl_ca
Value:
*************************** 244. row ***************************
Variable_name: ssl_capath
Value:
*************************** 245. row ***************************
Variable_name: ssl_cert
Value:
*************************** 246. row ***************************
Variable_name: ssl_cipher
Value:
*************************** 247. row ***************************
Variable_name: ssl_key
Value:
*************************** 248. row ***************************
Variable_name: storage_engine
Value: InnoDB
*************************** 249. row ***************************
Variable_name: sync_binlog
Value: 0
*************************** 250. row ***************************
Variable_name: sync_frm
Value: ON
*************************** 251. row ***************************
Variable_name: system_time_zone
Value: EEST
*************************** 252. row ***************************
Variable_name: table_definition_cache
Value: 256
*************************** 253. row ***************************
Variable_name: table_lock_wait_timeout
Value: 50
*************************** 254. row ***************************
Variable_name: table_open_cache
Value: 64
*************************** 255. row ***************************
Variable_name: table_type
Value: InnoDB
*************************** 256. row ***************************
Variable_name: thread_cache_size
Value: 0
*************************** 257. row ***************************
Variable_name: thread_handling
Value: one-thread-per-connection
*************************** 258. row ***************************
Variable_name: thread_stack
Value: 262144
*************************** 259. row ***************************
Variable_name: time_format
Value: %H:%i:%s
*************************** 260. row ***************************
Variable_name: time_zone
Value: SYSTEM
*************************** 261. row ***************************
Variable_name: timed_mutexes
Value: OFF
*************************** 262. row ***************************
Variable_name: timestamp
Value: 1308815507
*************************** 263. row ***************************
Variable_name: tmp_table_size
Value: 16777216
*************************** 264. row ***************************
Variable_name: tmpdir
Value: /tmp
*************************** 265. row ***************************
Variable_name: transaction_alloc_block_size
Value: 8192
*************************** 266. row ***************************
Variable_name: transaction_prealloc_size
Value: 4096
*************************** 267. row ***************************
Variable_name: tx_isolation
Value: REPEATABLE-READ
*************************** 268. row ***************************
Variable_name: unique_checks
Value: ON
*************************** 269. row ***************************
Variable_name: updatable_views_with_limit
Value: YES
*************************** 270. row ***************************
Variable_name: version
Value: 5.1.53
*************************** 271. row ***************************
Variable_name: version_comment
Value: wsrep_0.8.0
*************************** 272. row ***************************
Variable_name: version_compile_machine
Value: x86_64
*************************** 273. row ***************************
Variable_name: version_compile_os
Value: unknown-linux-gnu
*************************** 274. row ***************************
Variable_name: wait_timeout
Value: 28800
*************************** 275. row ***************************
Variable_name: warning_count
Value: 0
*************************** 276. row ***************************
Variable_name: wsrep_auto_increment_control
Value: ON
*************************** 277. row ***************************
Variable_name: wsrep_causal_reads
Value: OFF
*************************** 278. row ***************************
Variable_name: wsrep_certify_nonPK
Value: OFF
*************************** 279. row ***************************
Variable_name: wsrep_cluster_address
Value: gcomm://
*************************** 280. row ***************************
Variable_name: wsrep_cluster_name
Value: my_wsrep_cluster
*************************** 281. row ***************************
Variable_name: wsrep_convert_LOCK_to_trx
Value: OFF
*************************** 282. row ***************************
Variable_name: wsrep_data_home_dir
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/mysql/var/
*************************** 283. row ***************************
Variable_name: wsrep_dbug_option
Value:
*************************** 284. row ***************************
Variable_name: wsrep_debug
Value: OFF
*************************** 285. row ***************************
Variable_name: wsrep_drupal_282555_workaround
Value: ON
*************************** 286. row ***************************
Variable_name: wsrep_local_cache_size
Value: 20971520
*************************** 287. row ***************************
Variable_name: wsrep_max_ws_rows
Value: 65536
*************************** 288. row ***************************
Variable_name: wsrep_max_ws_size
Value: 1073741824
*************************** 289. row ***************************
Variable_name: wsrep_node_incoming_address
Value: xxx.xx.212.27:3306
*************************** 290. row ***************************
Variable_name: wsrep_node_name
Value: ...
*************************** 291. row ***************************
Variable_name: wsrep_notify_cmd
Value:
*************************** 292. row ***************************
Variable_name: wsrep_on
Value: ON
*************************** 293. row ***************************
Variable_name: wsrep_provider
Value: /data/d/mysql-5.1.53-galera-0.8.0b-x86_64/galera/lib/libgalera_smm.so
*************************** 294. row ***************************
Variable_name: wsrep_provider_options
Value: evs.consensus_timeout = PT30S; evs.debug_log_mask = 0x1; evs.inactive_check_period = PT1S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT15S; evs.join_retrans_period = PT0.3S; evs.keepalive_period = PT1S; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = PT5M; gcs.fc_debug = 0; gcs.fc_factor = 0.5; gcs.fc_limit = 16; gcs.fc_master_slave = NO; gcs.max_packet_size = 32616; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.time_wait = PT5S; gmcast.version = 0; pc.allow_sb = true; pc.checksum = true; pc.linger = PT2S; pc.npvo = false; pc.version = 0; protonet.backend = asio; protonet.version = 0; replicator.commit_order = 3
*************************** 295. row ***************************
Variable_name: wsrep_retry_autocommit
Value: 1
*************************** 296. row ***************************
Variable_name: wsrep_slave_threads
Value: 1
*************************** 297. row ***************************
Variable_name: wsrep_sst_auth
Value: ********
*************************** 298. row ***************************
Variable_name: wsrep_sst_donor
Value:
*************************** 299. row ***************************
Variable_name: wsrep_sst_method
Value: mysqldump
*************************** 300. row ***************************
Variable_name: wsrep_sst_receive_address
Value: AUTO
*************************** 301. row ***************************
Variable_name: wsrep_start_position
Value: 00000000-0000-0000-0000-000000000000:-1
*************************** 302. row ***************************
Variable_name: wsrep_ws_persistency
Value: OFF
302 rows in set (0.00 sec)
mysql> show global status\G
*************************** 1. row ***************************
Variable_name: Aborted_clients
Value: 0
*************************** 2. row ***************************
Variable_name: Aborted_connects
Value: 2
*************************** 3. row ***************************
Variable_name: Binlog_cache_disk_use
Value: 0
*************************** 4. row ***************************
Variable_name: Binlog_cache_use
Value: 0
*************************** 5. row ***************************
Variable_name: Bytes_received
Value: 22594
*************************** 6. row ***************************
Variable_name: Bytes_sent
Value: 1025559
*************************** 7. row ***************************
Variable_name: Com_admin_commands
Value: 0
*************************** 8. row ***************************
Variable_name: Com_assign_to_keycache
Value: 0
*************************** 9. row ***************************
Variable_name: Com_alter_db
Value: 0
*************************** 10. row ***************************
Variable_name: Com_alter_db_upgrade
Value: 0
*************************** 11. row ***************************
Variable_name: Com_alter_event
Value: 0
*************************** 12. row ***************************
Variable_name: Com_alter_function
Value: 0
*************************** 13. row ***************************
Variable_name: Com_alter_procedure
Value: 0
*************************** 14. row ***************************
Variable_name: Com_alter_server
Value: 0
*************************** 15. row ***************************
Variable_name: Com_alter_table
Value: 0
*************************** 16. row ***************************
Variable_name: Com_alter_tablespace
Value: 0
*************************** 17. row ***************************
Variable_name: Com_analyze
Value: 0
*************************** 18. row ***************************
Variable_name: Com_backup_table
Value: 0
*************************** 19. row ***************************
Variable_name: Com_begin
Value: 0
*************************** 20. row ***************************
Variable_name: Com_binlog
Value: 0
*************************** 21. row ***************************
Variable_name: Com_call_procedure
Value: 0
*************************** 22. row ***************************
Variable_name: Com_change_db
Value: 47
*************************** 23. row ***************************
Variable_name: Com_change_master
Value: 0
*************************** 24. row ***************************
Variable_name: Com_check
Value: 0
*************************** 25. row ***************************
Variable_name: Com_checksum
Value: 0
*************************** 26. row ***************************
Variable_name: Com_commit
Value: 3
*************************** 27. row ***************************
Variable_name: Com_create_db
Value: 0
*************************** 28. row ***************************
Variable_name: Com_create_event
Value: 0
*************************** 29. row ***************************
Variable_name: Com_create_function
Value: 0
*************************** 30. row ***************************
Variable_name: Com_create_index
Value: 0
*************************** 31. row ***************************
Variable_name: Com_create_procedure
Value: 0
*************************** 32. row ***************************
Variable_name: Com_create_server
Value: 0
*************************** 33. row ***************************
Variable_name: Com_create_table
Value: 1
*************************** 34. row ***************************
Variable_name: Com_create_trigger
Value: 0
*************************** 35. row ***************************
Variable_name: Com_create_udf
Value: 0
*************************** 36. row ***************************
Variable_name: Com_create_user
Value: 0
*************************** 37. row ***************************
Variable_name: Com_create_view
Value: 0
*************************** 38. row ***************************
Variable_name: Com_dealloc_sql
Value: 0
*************************** 39. row ***************************
Variable_name: Com_delete
Value: 0
*************************** 40. row ***************************
Variable_name: Com_delete_multi
Value: 0
*************************** 41. row ***************************
Variable_name: Com_do
Value: 0
*************************** 42. row ***************************
Variable_name: Com_drop_db
Value: 0
*************************** 43. row ***************************
Variable_name: Com_drop_event
Value: 0
*************************** 44. row ***************************
Variable_name: Com_drop_function
Value: 0
*************************** 45. row ***************************
Variable_name: Com_drop_index
Value: 0
*************************** 46. row ***************************
Variable_name: Com_drop_procedure
Value: 0
*************************** 47. row ***************************
Variable_name: Com_drop_server
Value: 0
*************************** 48. row ***************************
Variable_name: Com_drop_table
Value: 0
*************************** 49. row ***************************
Variable_name: Com_drop_trigger
Value: 0
*************************** 50. row ***************************
Variable_name: Com_drop_user
Value: 0
*************************** 51. row ***************************
Variable_name: Com_drop_view
Value: 0
*************************** 52. row ***************************
Variable_name: Com_empty_query
Value: 0
*************************** 53. row ***************************
Variable_name: Com_execute_sql
Value: 0
*************************** 54. row ***************************
Variable_name: Com_flush
Value: 0
*************************** 55. row ***************************
Variable_name: Com_grant
Value: 0
*************************** 56. row ***************************
Variable_name: Com_ha_close
Value: 0
*************************** 57. row ***************************
Variable_name: Com_ha_open
Value: 0
*************************** 58. row ***************************
Variable_name: Com_ha_read
Value: 0
*************************** 59. row ***************************
Variable_name: Com_help
Value: 0
*************************** 60. row ***************************
Variable_name: Com_insert
Value: 0
*************************** 61. row ***************************
Variable_name: Com_insert_select
Value: 0
*************************** 62. row ***************************
Variable_name: Com_install_plugin
Value: 0
*************************** 63. row ***************************
Variable_name: Com_kill
Value: 0
*************************** 64. row ***************************
Variable_name: Com_load
Value: 0
*************************** 65. row ***************************
Variable_name: Com_load_master_data
Value: 0
*************************** 66. row ***************************
Variable_name: Com_load_master_table
Value: 0
*************************** 67. row ***************************
Variable_name: Com_lock_tables
Value: 0
*************************** 68. row ***************************
Variable_name: Com_optimize
Value: 0
*************************** 69. row ***************************
Variable_name: Com_preload_keys
Value: 0
*************************** 70. row ***************************
Variable_name: Com_prepare_sql
Value: 0
*************************** 71. row ***************************
Variable_name: Com_purge
Value: 0
*************************** 72. row ***************************
Variable_name: Com_purge_before_date
Value: 0
*************************** 73. row ***************************
Variable_name: Com_release_savepoint
Value: 0
*************************** 74. row ***************************
Variable_name: Com_rename_table
Value: 0
*************************** 75. row ***************************
Variable_name: Com_rename_user
Value: 0
*************************** 76. row ***************************
Variable_name: Com_repair
Value: 0
*************************** 77. row ***************************
Variable_name: Com_replace
Value: 0
*************************** 78. row ***************************
Variable_name: Com_replace_select
Value: 0
*************************** 79. row ***************************
Variable_name: Com_reset
Value: 0
*************************** 80. row ***************************
Variable_name: Com_restore_table
Value: 0
*************************** 81. row ***************************
Variable_name: Com_revoke
Value: 0
*************************** 82. row ***************************
Variable_name: Com_revoke_all
Value: 0
*************************** 83. row ***************************
Variable_name: Com_rollback
Value: 0
*************************** 84. row ***************************
Variable_name: Com_rollback_to_savepoint
Value: 0
*************************** 85. row ***************************
Variable_name: Com_savepoint
Value: 0
*************************** 86. row ***************************
Variable_name: Com_select
Value: 94
*************************** 87. row ***************************
Variable_name: Com_set_option
Value: 215
*************************** 88. row ***************************
Variable_name: Com_show_authors
Value: 0
*************************** 89. row ***************************
Variable_name: Com_show_binlog_events
Value: 0
*************************** 90. row ***************************
Variable_name: Com_show_binlogs
Value: 0
*************************** 91. row ***************************
Variable_name: Com_show_charsets
Value: 0
*************************** 92. row ***************************
Variable_name: Com_show_collations
Value: 0
*************************** 93. row ***************************
Variable_name: Com_show_column_types
Value: 0
*************************** 94. row ***************************
Variable_name: Com_show_contributors
Value: 0
*************************** 95. row ***************************
Variable_name: Com_show_create_db
Value: 4
*************************** 96. row ***************************
Variable_name: Com_show_create_event
Value: 0
*************************** 97. row ***************************
Variable_name: Com_show_create_func
Value: 0
*************************** 98. row ***************************
Variable_name: Com_show_create_proc
Value: 0
*************************** 99. row ***************************
Variable_name: Com_show_create_table
Value: 44
*************************** 100. row ***************************
Variable_name: Com_show_create_trigger
Value: 0
*************************** 101. row ***************************
Variable_name: Com_show_databases
Value: 3
*************************** 102. row ***************************
Variable_name: Com_show_engine_logs
Value: 0
*************************** 103. row ***************************
Variable_name: Com_show_engine_mutex
Value: 0
*************************** 104. row ***************************
Variable_name: Com_show_engine_status
Value: 0
*************************** 105. row ***************************
Variable_name: Com_show_events
Value: 0
*************************** 106. row ***************************
Variable_name: Com_show_errors
Value: 0
*************************** 107. row ***************************
Variable_name: Com_show_fields
Value: 43
*************************** 108. row ***************************
Variable_name: Com_show_function_status
Value: 0
*************************** 109. row ***************************
Variable_name: Com_show_grants
Value: 0
*************************** 110. row ***************************
Variable_name: Com_show_keys
Value: 0
*************************** 111. row ***************************
Variable_name: Com_show_master_status
Value: 0
*************************** 112. row ***************************
Variable_name: Com_show_new_master
Value: 0
*************************** 113. row ***************************
Variable_name: Com_show_open_tables
Value: 0
*************************** 114. row ***************************
Variable_name: Com_show_plugins
Value: 0
*************************** 115. row ***************************
Variable_name: Com_show_privileges
Value: 0
*************************** 116. row ***************************
Variable_name: Com_show_procedure_status
Value: 0
*************************** 117. row ***************************
Variable_name: Com_show_processlist
Value: 0
*************************** 118. row ***************************
Variable_name: Com_show_profile
Value: 0
*************************** 119. row ***************************
Variable_name: Com_show_profiles
Value: 0
*************************** 120. row ***************************
Variable_name: Com_show_slave_hosts
Value: 0
*************************** 121. row ***************************
Variable_name: Com_show_slave_status
Value: 0
*************************** 122. row ***************************
Variable_name: Com_show_status
Value: 2
*************************** 123. row ***************************
Variable_name: Com_show_storage_engines
Value: 0
*************************** 124. row ***************************
Variable_name: Com_show_table_status
Value: 42
*************************** 125. row ***************************
Variable_name: Com_show_tables
Value: 6
*************************** 126. row ***************************
Variable_name: Com_show_triggers
Value: 42
*************************** 127. row ***************************
Variable_name: Com_show_variables
Value: 2
*************************** 128. row ***************************
Variable_name: Com_show_warnings
Value: 0
*************************** 129. row ***************************
Variable_name: Com_slave_start
Value: 0
*************************** 130. row ***************************
Variable_name: Com_slave_stop
Value: 0
*************************** 131. row ***************************
Variable_name: Com_stmt_close
Value: 0
*************************** 132. row ***************************
Variable_name: Com_stmt_execute
Value: 0
*************************** 133. row ***************************
Variable_name: Com_stmt_fetch
Value: 0
*************************** 134. row ***************************
Variable_name: Com_stmt_prepare
Value: 0
*************************** 135. row ***************************
Variable_name: Com_stmt_reprepare
Value: 0
*************************** 136. row ***************************
Variable_name: Com_stmt_reset
Value: 0
*************************** 137. row ***************************
Variable_name: Com_stmt_send_long_data
Value: 0
*************************** 138. row ***************************
Variable_name: Com_truncate
Value: 0
*************************** 139. row ***************************
Variable_name: Com_uninstall_plugin
Value: 0
*************************** 140. row ***************************
Variable_name: Com_unlock_tables
Value: 0
*************************** 141. row ***************************
Variable_name: Com_update
Value: 0
*************************** 142. row ***************************
Variable_name: Com_update_multi
Value: 0
*************************** 143. row ***************************
Variable_name: Com_xa_commit
Value: 0
*************************** 144. row ***************************
Variable_name: Com_xa_end
Value: 0
*************************** 145. row ***************************
Variable_name: Com_xa_prepare
Value: 0
*************************** 146. row ***************************
Variable_name: Com_xa_recover
Value: 0
*************************** 147. row ***************************
Variable_name: Com_xa_rollback
Value: 0
*************************** 148. row ***************************
Variable_name: Com_xa_start
Value: 0
*************************** 149. row ***************************
Variable_name: Compression
Value: OFF
*************************** 150. row ***************************
Variable_name: Connections
Value: 11
*************************** 151. row ***************************
Variable_name: Created_tmp_disk_tables
Value: 84
*************************** 152. row ***************************
Variable_name: Created_tmp_files
Value: 5
*************************** 153. row ***************************
Variable_name: Created_tmp_tables
Value: 147
*************************** 154. row ***************************
Variable_name: Delayed_errors
Value: 0
*************************** 155. row ***************************
Variable_name: Delayed_insert_threads
Value: 0
*************************** 156. row ***************************
Variable_name: Delayed_writes
Value: 0
*************************** 157. row ***************************
Variable_name: Flush_commands
Value: 1
*************************** 158. row ***************************
Variable_name: Handler_commit
Value: 6
*************************** 159. row ***************************
Variable_name: Handler_delete
Value: 0
*************************** 160. row ***************************
Variable_name: Handler_discover
Value: 0
*************************** 161. row ***************************
Variable_name: Handler_prepare
Value: 0
*************************** 162. row ***************************
Variable_name: Handler_read_first
Value: 35
*************************** 163. row ***************************
Variable_name: Handler_read_key
Value: 4
*************************** 164. row ***************************
Variable_name: Handler_read_next
Value: 1928
*************************** 165. row ***************************
Variable_name: Handler_read_prev
Value: 0
*************************** 166. row ***************************
Variable_name: Handler_read_rnd
Value: 0
*************************** 167. row ***************************
Variable_name: Handler_read_rnd_next
Value: 3267
*************************** 168. row ***************************
Variable_name: Handler_rollback
Value: 0
*************************** 169. row ***************************
Variable_name: Handler_savepoint
Value: 0
*************************** 170. row ***************************
Variable_name: Handler_savepoint_rollback
Value: 0
*************************** 171. row ***************************
Variable_name: Handler_update
Value: 0
*************************** 172. row ***************************
Variable_name: Handler_write
Value: 1098
*************************** 173. row ***************************
Variable_name: Innodb_buffer_pool_pages_data
Value: 64
*************************** 174. row ***************************
Variable_name: Innodb_buffer_pool_pages_dirty
Value: 0
*************************** 175. row ***************************
Variable_name: Innodb_buffer_pool_pages_flushed
Value: 15
*************************** 176. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 26815
*************************** 177. row ***************************
Variable_name: Innodb_buffer_pool_pages_misc
Value: 1
*************************** 178. row ***************************
Variable_name: Innodb_buffer_pool_pages_total
Value: 26880
*************************** 179. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_rnd
Value: 1
*************************** 180. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_seq
Value: 0
*************************** 181. row ***************************
Variable_name: Innodb_buffer_pool_read_requests
Value: 254
*************************** 182. row ***************************
Variable_name: Innodb_buffer_pool_reads
Value: 13
*************************** 183. row ***************************
Variable_name: Innodb_buffer_pool_wait_free
Value: 0
*************************** 184. row ***************************
Variable_name: Innodb_buffer_pool_write_requests
Value: 37
*************************** 185. row ***************************
Variable_name: Innodb_data_fsyncs
Value: 12
*************************** 186. row ***************************
Variable_name: Innodb_data_pending_fsyncs
Value: 0
*************************** 187. row ***************************
Variable_name: Innodb_data_pending_reads
Value: 0
*************************** 188. row ***************************
Variable_name: Innodb_data_pending_writes
Value: 0
*************************** 189. row ***************************
Variable_name: Innodb_data_read
Value: 3231744
*************************** 190. row ***************************
Variable_name: Innodb_data_reads
Value: 26
*************************** 191. row ***************************
Variable_name: Innodb_data_writes
Value: 19
*************************** 192. row ***************************
Variable_name: Innodb_data_written
Value: 251904
*************************** 193. row ***************************
Variable_name: Innodb_dblwr_pages_written
Value: 0
*************************** 194. row ***************************
Variable_name: Innodb_dblwr_writes
Value: 0
*************************** 195. row ***************************
Variable_name: Innodb_log_waits
Value: 0
*************************** 196. row ***************************
Variable_name: Innodb_log_write_requests
Value: 4
*************************** 197. row ***************************
Variable_name: Innodb_log_writes
Value: 4
*************************** 198. row ***************************
Variable_name: Innodb_os_log_fsyncs
Value: 9
*************************** 199. row ***************************
Variable_name: Innodb_os_log_pending_fsyncs
Value: 0
*************************** 200. row ***************************
Variable_name: Innodb_os_log_pending_writes
Value: 0
*************************** 201. row ***************************
Variable_name: Innodb_os_log_written
Value: 3584
*************************** 202. row ***************************
Variable_name: Innodb_page_size
Value: 16384
*************************** 203. row ***************************
Variable_name: Innodb_pages_created
Value: 0
*************************** 204. row ***************************
Variable_name: Innodb_pages_read
Value: 64
*************************** 205. row ***************************
Variable_name: Innodb_pages_written
Value: 15
*************************** 206. row ***************************
Variable_name: Innodb_row_lock_current_waits
Value: 0
*************************** 207. row ***************************
Variable_name: Innodb_row_lock_time
Value: 0
*************************** 208. row ***************************
Variable_name: Innodb_row_lock_time_avg
Value: 0
*************************** 209. row ***************************
Variable_name: Innodb_row_lock_time_max
Value: 0
*************************** 210. row ***************************
Variable_name: Innodb_row_lock_waits
Value: 0
*************************** 211. row ***************************
Variable_name: Innodb_rows_deleted
Value: 0
*************************** 212. row ***************************
Variable_name: Innodb_rows_inserted
Value: 2
*************************** 213. row ***************************
Variable_name: Innodb_rows_read
Value: 4
*************************** 214. row ***************************
Variable_name: Innodb_rows_updated
Value: 0
*************************** 215. row ***************************
Variable_name: Key_blocks_not_flushed
Value: 0
*************************** 216. row ***************************
Variable_name: Key_blocks_unused
Value: 6678
*************************** 217. row ***************************
Variable_name: Key_blocks_used
Value: 16
*************************** 218. row ***************************
Variable_name: Key_read_requests
Value: 122
*************************** 219. row ***************************
Variable_name: Key_reads
Value: 16
*************************** 220. row ***************************
Variable_name: Key_write_requests
Value: 0
*************************** 221. row ***************************
Variable_name: Key_writes
Value: 0
*************************** 222. row ***************************
Variable_name: Last_query_cost
Value: 0.000000
*************************** 223. row ***************************
Variable_name: Max_used_connections
Value: 3
*************************** 224. row ***************************
Variable_name: Not_flushed_delayed_rows
Value: 0
*************************** 225. row ***************************
Variable_name: Open_files
Value: 42
*************************** 226. row ***************************
Variable_name: Open_streams
Value: 0
*************************** 227. row ***************************
Variable_name: Open_table_definitions
Value: 22
*************************** 228. row ***************************
Variable_name: Open_tables
Value: 22
*************************** 229. row ***************************
Variable_name: Opened_files
Value: 476
*************************** 230. row ***************************
Variable_name: Opened_table_definitions
Value: 23
*************************** 231. row ***************************
Variable_name: Opened_tables
Value: 30
*************************** 232. row ***************************
Variable_name: Prepared_stmt_count
Value: 0
*************************** 233. row ***************************
Variable_name: Qcache_free_blocks
Value: 0
*************************** 234. row ***************************
Variable_name: Qcache_free_memory
Value: 0
*************************** 235. row ***************************
Variable_name: Qcache_hits
Value: 0
*************************** 236. row ***************************
Variable_name: Qcache_inserts
Value: 0
*************************** 237. row ***************************
Variable_name: Qcache_lowmem_prunes
Value: 0
*************************** 238. row ***************************
Variable_name: Qcache_not_cached
Value: 0
*************************** 239. row ***************************
Variable_name: Qcache_queries_in_cache
Value: 0
*************************** 240. row ***************************
Variable_name: Qcache_total_blocks
Value: 0
*************************** 241. row ***************************
Variable_name: Queries
Value: 555
*************************** 242. row ***************************
Variable_name: Questions
Value: 552
*************************** 243. row ***************************
Variable_name: Rpl_status
Value: NULL
*************************** 244. row ***************************
Variable_name: Select_full_join
Value: 0
*************************** 245. row ***************************
Variable_name: Select_full_range_join
Value: 0
*************************** 246. row ***************************
Variable_name: Select_range
Value: 0
*************************** 247. row ***************************
Variable_name: Select_range_check
Value: 0
*************************** 248. row ***************************
Variable_name: Select_scan
Value: 157
*************************** 249. row ***************************
Variable_name: Slave_open_temp_tables
Value: 0
*************************** 250. row ***************************
Variable_name: Slave_retried_transactions
Value: 0
*************************** 251. row ***************************
Variable_name: Slave_running
Value: OFF
*************************** 252. row ***************************
Variable_name: Slow_launch_threads
Value: 0
*************************** 253. row ***************************
Variable_name: Slow_queries
Value: 0
*************************** 254. row ***************************
Variable_name: Sort_merge_passes
Value: 0
*************************** 255. row ***************************
Variable_name: Sort_range
Value: 0
*************************** 256. row ***************************
Variable_name: Sort_rows
Value: 0
*************************** 257. row ***************************
Variable_name: Sort_scan
Value: 4
*************************** 258. row ***************************
Variable_name: Table_locks_immediate
Value: 62
*************************** 259. row ***************************
Variable_name: Table_locks_waited
Value: 0
*************************** 260. row ***************************
Variable_name: Tc_log_max_pages_used
Value: 0
*************************** 261. row ***************************
Variable_name: Tc_log_page_size
Value: 0
*************************** 262. row ***************************
Variable_name: Tc_log_page_waits
Value: 0
*************************** 263. row ***************************
Variable_name: Threads_cached
Value: 0
*************************** 264. row ***************************
Variable_name: Threads_connected
Value: 3
*************************** 265. row ***************************
Variable_name: Threads_created
Value: 10
*************************** 266. row ***************************
Variable_name: Threads_running
Value: 1
*************************** 267. row ***************************
Variable_name: Uptime
Value: 89162
*************************** 268. row ***************************
Variable_name: Uptime_since_flush_status
Value: 89162
*************************** 269. row ***************************
Variable_name: wsrep_local_state_uuid
Value: 9f09de80-9cab-11e0-0800-293d6220d6f8
*************************** 270. row ***************************
Variable_name: wsrep_last_committed
Value: 3
*************************** 271. row ***************************
Variable_name: wsrep_replicated
Value: 0
*************************** 272. row ***************************
Variable_name: wsrep_replicated_bytes
Value: 0
*************************** 273. row ***************************
Variable_name: wsrep_received
Value: 14
*************************** 274. row ***************************
Variable_name: wsrep_received_bytes
Value: 1269
*************************** 275. row ***************************
Variable_name: wsrep_local_commits
Value: 0
*************************** 276. row ***************************
Variable_name: wsrep_local_cert_failures
Value: 0
*************************** 277. row ***************************
Variable_name: wsrep_local_bf_aborts
Value: 0
*************************** 278. row ***************************
Variable_name: wsrep_local_replays
Value: 0
*************************** 279. row ***************************
Variable_name: wsrep_local_send_queue
Value: 0
*************************** 280. row ***************************
Variable_name: wsrep_local_send_queue_avg
Value: 0.000000
*************************** 281. row ***************************
Variable_name: wsrep_local_recv_queue
Value: 0
*************************** 282. row ***************************
Variable_name: wsrep_local_recv_queue_avg
Value: 0.000000
*************************** 283. row ***************************
Variable_name: wsrep_flow_control_paused
Value: 0.000000
*************************** 284. row ***************************
Variable_name: wsrep_flow_control_sent
Value: 0
*************************** 285. row ***************************
Variable_name: wsrep_flow_control_recv
Value: 0
*************************** 286. row ***************************
Variable_name: wsrep_cert_deps_distance
Value: 1.333333
*************************** 287. row ***************************
Variable_name: wsrep_apply_oooe
Value: 0.000000
*************************** 288. row ***************************
Variable_name: wsrep_apply_oool
Value: 0.000000
*************************** 289. row ***************************
Variable_name: wsrep_apply_window
Value: 1.000000
*************************** 290. row ***************************
Variable_name: wsrep_commit_oooe
Value: 0.000000
*************************** 291. row ***************************
Variable_name: wsrep_commit_oool
Value: 0.000000
*************************** 292. row ***************************
Variable_name: wsrep_commit_window
Value: 1.000000
*************************** 293. row ***************************
Variable_name: wsrep_local_state
Value: 4
*************************** 294. row ***************************
Variable_name: wsrep_local_state_comment
Value: Synced (6)
*************************** 295. row ***************************
Variable_name: wsrep_cluster_conf_id
Value: 3
*************************** 296. row ***************************
Variable_name: wsrep_cluster_size
Value: 3
*************************** 297. row ***************************
Variable_name: wsrep_cluster_state_uuid
Value: 9f09de80-9cab-11e0-0800-293d6220d6f8
*************************** 298. row ***************************
Variable_name: wsrep_cluster_status
Value: Primary
*************************** 299. row ***************************
Variable_name: wsrep_connected
Value: ON
*************************** 300. row ***************************
Variable_name: wsrep_local_index
Value: 1
*************************** 301. row ***************************
Variable_name: wsrep_ready
Value: ON
301 rows in set (0.00 sec)

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Danël van Eeden's picture

binlog sync

sync_binlog is of course also 0, as no binlog is being written

I don't think that's true as log_bin is enabled. The binlog won't be synced to disk with sync_binlog=0.

Alex's picture

Actually, Galera uses binlog

Actually, Galera uses binlog events for replication, so binlog is enabled. However by default binlog files are not written to disk. To make binlog write to disk you need to specify a base filename.

This however will log only "master" transactions on this node. To make Galera node write "full" binlog, you'll need to enable log_slave_updates too.

Alex's picture

Hi Henrik, Great to hear that

Hi Henrik,
Great to hear that you're trying Galera out. You've made many notes! I'd like make some comments about your findings, if I may:

1) GRANT command IS replicated, so you need to do it only once. UPDATE mysql.user is not. I guess it is not explained clearly enough in the docs.

2) If by low InnoDB performance in MySQL 5.1 you mean built-in InnoDB, MySQL/Galera supports InnoDB plugin too. MySQL-5.5 support comes soon.

3) The coredump you observed was most likely due to intended abort (in a synchronous multimaster cluster there are situations where it is impossible or simply pointless to do graceful shutdown, for example - failed state transfer, so Galera calls abort()). MySQL tries to handle signal 6 and tries to dump stack and core in this case. This will be fixed.

4) The distorted output of 'wsrep_%' variables is due to wsrep_provider_options. Even if you don't set it, it will display all currently effective wsrep parameters. The reason to have it as a string is to make it generic with respect to different wsrep provider implementations.

5) I have not seen noticeable performance improvement from innodb_doublewrite=0. So perhaps it should be 1. But innodb_flush_log_at_trx_commit is a completely another story.

6) Query cache is not supported, so you can save some time ;)

7) The support for causal reads in Galera is still under development and 0.8 ships without it.

8) Currently, maximum possible trx size is 4G. There is no such strict limit on the number of rows, you can bump it up. 65K is actually an error in wsrep.cnf file. DBT2 dump load requires 128K, so it is the actual default in the code. Of course we're looking to remove those limits in future.

9) The sure way to see the effect of multiple slave threads is DBT2 with database size few times bigger than the buffer pool. Surprisingly, sysbench does not need that many page replacements.

Happy testing!
Alex

VadimTK's picture

innodb_doublewrite

Henrik,

innodb_doublewrite is used as protection from possible partial write into innodb table. Basically InnoDB writes the same page twice - into ibdata1 doblewrite area and into table. If during writing to table there is system failure and only 8K was written instead of 16K, then InnoDB is able to recovery whole page from ibdata1.

Alex,
You may not see significant benefits from disabling innodb_doublewrite
on regular RAIDs, however on fast Flash devices innodb_doublwrite affects performance significantly. You may get much better result with innodb_doublwrite=0 there (but it is not recommended, until Flash supports 16K atomic writes).

hingo's picture

Thanks for explaining that to

Thanks for explaining that to me. I was confusing doublewrite buffer and insert buffer, so the manual entry didn't make any sense. What you say totally makes sense.

Alex's picture

innodb_doublewrite

Vadim,
Thanks for heads up about innodb_doublewrite. How I read it, it helps to recover data in case of a system failure - the data which otherwise would have been lost. In Galera cluster however, no matter how well the node is recovered, it still needs to get a state snapshot from another node, so it is no longer that relevant (rsync or xtrabackup will just rebuild the whole data directory).

At least until we implement incremental state transfer AND find a way to associate recovered database state with Galera sequence number. (the latter must require a major innodb hacking)

VadimTK's picture

Alex, Your are fully correct

Alex,

Your are fully correct there.

But I would encourage you to look into incremental state transfer and how to correspond InnoDB LSN with Galera sequence number. That would make XtraBackup incremental SST possible. In my opinion having this would improve Galera product a lot (and not having it - it is significant downside).

Alex's picture

crash recovery

Well, incremental state transfer is surely coming. And even then innodb_doublewrite probably will be of little use, since what does few lost transactions matter when you can get them from a peer?

Recovering crashed database to a Galera-consistent state is problematic however:
1) As you mentioned, we need to be able to associate LSN with Galera sequence number which we get only after transaction is prepared for commit, so it seems there is no way (at least now) to commit it to the database (as per Henrik's proposal).
2) Even if we have the above, we must make sure we can recover without holes in Galera sequence. And I'm not sure it is possible.

hingo's picture

Alex, my proposal is that you

Alex, my proposal is that you don't need to know the LSN. Just create a system table (in InnoDB format of course) like mysql.wsrep or wsrep.wsrep and then you do something like

UPDATE mysql.wsrep SET global_trx_id=[trx id of current trx] WHERE pk=1;

...as part of each commit. At this point you don't need to know LSN at all. (Updating a single row from each transaction will result in contention, but optimization strategies are left out for simplicity.)

When a new replica wants to connect to a cluster, it will read the value of wsrep.wsrep.global_trx_id, tell the other nodes that it has that state and ask to receive any write sets newer than that.

I need to go to sleep so I may be missing something, but this is the basic idea.

Alex's picture

Henrik, the problem is that

Henrik, the problem is that in "master" node you don't know Galera transaction ID until after replication, and at that point transaction is already prepared for commit. As far as I understand you cannot add anything to this transaction any more. I guess Kristian's proposal below is what could work in this situation.

Also I think Vadim needs GTID-LSN association to get xtrabackup working in incremental mode. I guess recovering node will provide missing interval using GTID, which peer will translate to local LSN and will be able to provide a delta using xtrabackup.

hingo's picture

Ah, ok. I was assuming Galera

Ah, ok. I was assuming Galera knows the id of the transaction it is working on. I thought it was just the LSN you cannot know beforehand. (But I realize now the final ordering of the transaction is only known after replication, makes sense.)

If it was possible to store the GTID as part of its own transaction, then mapping it to the LSN is already achieved. For a given state of the database/tablespace you would just read your LSN from InnoDB/xtrabackup and the value of the GTID from the table where it is stored, and that's your mapping.

If you don't know the GTID beforehand this is indeed more complicated, as you'd have to store that information somehow "atomically" with the transaction itself, but this is of course not possible.

Kristian Nielsen's picture

Re: crash recovery

1) Yes, this is a critical problem that people often overlook.
I think it is better to handle it like how InnoDB/XtraDB now handle
associating binlog position with state after InnoDB crash recovery. The state
is written into the transaction log as part of the commit, not into any
table. A similar method should work for Galera sequence number, though it will
require some work for sure.

2) In the MariaDB group commit, I was careful in the design to allow a plugin
to handle recovery without leaving holes. There is a fixed (but efficient)
ordering between commits in InnoDB and the binlog (or primary redundancy
service plugin). So this together with (1) should allow to leave no holes by
starting from the right Galera sequence number.

Still, it requires that Galera cluster is able to provide a joining node with
the full sequence of Galera replication events from a given sequence number
on. Clearly it must already be able to do so on a non-incremental state
transfer to catch up, but I guess the list of events is received and buffered
by the joining node during the transfer. Otherwise active nodes would have to
either each write the full list of Galera events to some kind of binlog, or
the joining node would need to somehow merge binlogs received from each active
node. So agree it does not look trivial...

Alex's picture

Kristian, thanks for pointing

Kristian,
thanks for pointing up 1). I guess we could simply hijack the binlog position space fro Galera transaction ID, since semantically they are the same (am I right that this is used only for slave recovery?). I wonder if that can be utilized for xtrabackup incremental mode.

As for Galera events "binlog", it is almost done and may see the light in one of the nearest releases.

Kristian Nielsen's picture

yes

Alex,
Yes, it is only used for slave provisioning.

In fact, the only thing InnoDB does is write the information into the log at startup (XtraBackup does the same during restore). Then the DBA can make note of the position and execute the corresponding CHANGE MASTER TO when setting up the new slave.

hingo's picture

Re: crash recovery

Hi Kristian

Re 1), does innodb write binlog position into InnoDB table space or logs at some point? Reading the innobackupex perl code from xtrabackup, the way it gets replication position is simply to lock all tables during backup and do SHOW SLAVE STATUS and SHOW MASTER STATUS. (http://bazaar.launchpad.net/~percona-dev/percona-xtrabackup/trunk/view/h...)

You are saying this is not needed (at least not master status) because the last binlog position is already stored inside the innodb data and xtrabackup could just extract it from there?

Your worklog certainly claims exactly this: http://askmonty.org/worklog/Server-RawIdeaBin/?tid=164

It seems the above worklog nicely explains how Galera could couple their GTID with each InnoDB commit in a similar way.

Kristian Nielsen's picture

Re: crash recovery

InnoDB writes the binlog position as part of every commit.

The detailed explanation is that there is a fixed location in the system
tablespace, and this is overwritten in each commit with the corresponding
binlog position. Since every write to tablespace is logged as redo in the
transaction log, the position is effectively written into the log at commit.

In MySQL, the infamous prepare_commit_mutex is used to ensure that the binlog
position stored matches the committed transaction, essentially by not allowing
two commits to be in progress at the same time. In MariaDB a kind of MVCC is
used to provide the correct binlog position without the need for a global
mutex.

XtraBackup by itself does not lock any tables. It copies just the InnoDB table
spaces and transaction log. After recovering using the redo log, it reads the
binlog position from the fixed location and prints it. So you only get the
correct binlog position if the last event in the binlog is DML against InnoDB,
it does not handle .frm files, MyISAM tables, or non-InnoDB binlog event
positions.

AFAIK, innobackupex on the other hand handles also non-InnoDB stuff, at the
cost of using FLUSH TABLES WITH READ LOCK, locking the tables.

So it is two different ways of taking the backup, and two different ways to
get the binlog position.

hingo's picture

Right, so for taking a full

Right, so for taking a full backup it's actually less useful, like you say, as you would miss any non-InnoDB transactions. Also, since one often takes backups from a slave, it is actually just as important to record the SHOW SLAVE STATUS to get the binlog positions of the master server.

But for Galera this is more useful, since they are InnoDB-only anyway they could use this for their snapshotting - with the exception of occasional DDL or other non-InnoDB commands that they do replicate.

Pavel's picture

interesting

Galera seems to be quite an interesting project. A pity that there's not much documentation, according to your post it should deserve more attention. If mysql had a userfriendl(ier) way to handle clustering, that would be awesome.

Alex's picture

Documentation

Hi Pavel,
So how much of a documentation would you like to have for a "userfriendl(ier) way to handle clustering"? :)
Seriously, we are very eager to hear about what people are missing in Galera docs.

Thanks,
Alex

Pavel's picture

Well IMHO the information on

Well IMHO the information on your site is rather scattered. Some things are explained on the product pages, some on the wiki, some in the README. So, what to do about that:
* I'd introduce products first. Dumb it down, keep the introduction simple. Tell people about the major features current Galera offers, about its licensing and give links to directly download Galera - point out the prebuild binaries as well as the source. Provide a link i.e. to the wiki on how to install Galera. Point people i.e. to launchpad to report bugs, ask questions etc.
* Have a separate page explaining in better detail what Galera does and how. I.e. on http://codership.com/products/galera_replication, you have a paragraph "Synchronous vs. Asynchronous Replication". If a user stumbles upon this page, he won't know if Galera provides sync or async or both, because you don't advertise the features (visibly enough).
* Give people a howto/tutorial. Dumb it down and explain what configuration options they want to tweak (i.e. innodb_doublewrite and others mentioned in the post above). You can do that by linking to the relevant docs page. If you don't have time/patience to do that, give links to blogs about Galera.
* And especially, do something about navigation on your site ;) its a real mess. Products, info, services and downloads could all be in a single category. If you want people to register, advertise the free consulting offer along with information on your products, not with the download link. The register form is so much more dominant then the direct download link that some who don't read the pages too much will believe that they *have* to register to download Galera.
Hope I made my point. I really think that the current presentation/documentation of Galera scares away possible early adopters.

At first, the docs should probably have a dumbed-down introduction of what Galera does. You can do this by some Infographics

Pavel's picture

please ignore the last line

please ignore the last line ... accidentally pasted something there

Alex's picture

Pavel, thanks for your

Pavel, thanks for your comments/sugestions. I pretty much agree with them. Our web-site is a disaster.

 Log Buffer #226, A Carnival of the Vanities for DBAs | The 's picture

Pingback

[...] Henrik Ingo touches the Galera clustering for MySQL first time and is pretty excited about it. [...]

First touch: Galera clustering for MySQL | MySQL | Syngu's picture

Pingback

[...] interesting to compare results. In this post I will just share the experience of installing and... Read more... Categories: MySQL     Share | Related [...]

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Use [fn]...[/fn] (or <fn>...</fn>) to insert automatically numbered footnotes.
  • Allowed HTML tags: <h1> <h2> <h3> <h4> <p> <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <br> <sup> <div> <blockquote> <pre> <img>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically. (Better URL filter.)

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
5 + 8 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.