Merge lp:~hrvojem/percona-xtradb-cluster/bug1133638 into lp:percona-xtradb-cluster/percona-xtradb-cluster-5.5

[Hrvoje Matijakovic]

[Raghavendra D Prabhu (raghavendra-prabhu)]

Regarding wsrep_retry_autocommit, can you add this part

"if it is 0 it won't be retried and if it is 1 it will be retried once." to disambiguate?

[Raghavendra D Prabhu (raghavendra-prabhu)]

Bug #1155897: Cluster does not follow MDL semantics -- this one is good.

[Raghavendra D Prabhu (raghavendra-prabhu)]

> Bug #1155897: Cluster does not follow MDL semantics -- this one is good.

I meant " Limitations page in the doc is out of date " here

[Raghavendra D Prabhu (raghavendra-prabhu)]

Regarding

"Bug #1155897: Cluster does not follow MDL semantics"

has
"With regards to DDL under RSU method, is MDL honoured or is it not?"

been tested?

[Raghavendra D Prabhu (raghavendra-prabhu)]

Regarding

"+This variable controls how many replication events will be grouped together. Replication events are grouped in SQL slave thread by skipping events which may cause commit. This way the wsrep node acting in |MySQL| slave role and all other wsrep nodes in provider replication group, will see same (huge) transactions. This implementation is still experimental. This may help with the bottleneck of having only one |MySQL| slave facing commit time delay of synchronous provider."

for Bug #1170066

Has this been sourced from any codership docs?

[Raghavendra D Prabhu (raghavendra-prabhu)]

Everything looks good here except Bug #1155897.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Approved.

review: Approve

=== modified file 'doc-pxc/source/faq.rst'
--- doc-pxc/source/faq.rst	2013-01-25 07:37:41 +0000
+++ doc-pxc/source/faq.rst	2013-06-13 13:58:35 +0000
@@ -47,7 +47,7 @@
 
 Q: How would it handle split brain? 
 ====================================
-A: It would not handle it. The |split brain| is hard stop, |XtraDB Cluster| can't resolve it.
+A: It would not handle it. The |split brain| is hard stop, XtraDB Cluster can't resolve it.
 That's why the minimal recommendation is to have 3 nodes. 
 However there is possibility to allow a node to handle the traffic, option is: ::
   
@@ -58,11 +58,12 @@
 A: It is possible in two ways:
 
 1. By default Galera reads starting position from a text file <datadir>/grastate.dat. Just make this file identical on all nodes, and there will be no state transfer upon start.
+
 2. With :variable:`wsrep_start_position` variable - start the nodes with the same *UUID:seqno* value and there you are.
 
 Q: I have a two nodes setup. When node1 fails, node2 does not accept commands, why?
 ====================================================================================
-A: This is expected behaviour, to prevent |split brain|. See previous question.
+A: This is expected behavior, to prevent |split brain|. See previous question.
 
 Q: What tcp ports are used by Percona XtraDB Cluster?
 ======================================================
 
=== modified file 'doc-pxc/source/howtos/cenots_howto.rst'
--- doc-pxc/source/howtos/cenots_howto.rst	2013-03-12 09:32:01 +0000
+++ doc-pxc/source/howtos/cenots_howto.rst	2013-06-13 13:58:35 +0000
@@ -161,6 +161,9 @@
   # Node #2 address
   wsrep_node_address=192.168.70.72
 
+  # Cluster name
+  wsrep_cluster_name=my_centos_cluster
+
   # SST method
   wsrep_sst_method=xtrabackup
 
@@ -222,6 +225,9 @@
   # Node #3 address
   wsrep_node_address=192.168.70.73
 
+  # Cluster name
+  wsrep_cluster_name=my_centos_cluster
+
   # SST method
   wsrep_sst_method=xtrabackup
 
 
=== added file 'doc-pxc/source/howtos/ubuntu_howto.rst'
--- doc-pxc/source/howtos/ubuntu_howto.rst	1970-01-01 00:00:00 +0000
+++ doc-pxc/source/howtos/ubuntu_howto.rst	2013-06-13 13:58:35 +0000
@@ -0,0 +1,302 @@
+.. _ubuntu_howto:
+
+Installing Percona XtraDB Cluster on *Ubuntu*
+=============================================
+
+This tutorial will show how to install the |Percona XtraDB Cluster| on three *Ubuntu* 12.04.2 LTS servers, using the packages from Percona repositories.
+
+This cluster will be assembled of three servers/nodes: ::
+ 
+  node #1
+  hostname: pxc1
+  IP: 192.168.70.61
+
+  node #2
+  hostname: pxc2
+  IP: 192.168.70.62
+
+  node #3
+  hostname: pxc3
+  IP: 192.168.70.63
+
+Prerequisites 
+-------------
+
+ * All three nodes have a *Ubuntu* 12.04.2 LTS installation. 
+ 
+ * Firewall has been set up to allow connecting to ports 3306, 4444, 4567 and 4568
+
+ * AppArmor profile for |MySQL| is `disabled <http://www.mysqlperformanceblog.com/2012/12/20/percona-xtradb-cluster-selinux-is-not-always-the-culprit/>`_ 
+
+Installation
+------------
+
+Percona repository should be set up as described in the :ref:`apt-repo` guide. Following command will install |Percona XtraDB Cluster| packages: :: 
+
+  $ apt-get install percona-xtradb-cluster-server-5.5 percona-xtradb-cluster-client-5.5
+
+When these two commands have been executed successfully on all three nodes |Percona XtraDB Cluster| is installed.
+
+.. note:: 
+
+ Debian/Ubuntu installation prompts for root password, this was set to: ``Passw0rd``. After the packages have been installed, ``mysqld`` will be started automatically. In this example mysqld is stopped on all three nodes after successful installation with: ``/etc/init.d/mysql stop``.
+
+Configuring the nodes
+---------------------
+
+Individual nodes should be configured to be able to bootstrap the cluster. More details about bootstrapping the cluster can be found in the :ref:`bootstrap` guide.
+
+Configuration file :file:`/etc/mysql/my.cnf` for the first node should look like: ::
+
+  [mysqld]
+
+  datadir=/var/lib/mysql
+  user=mysql
+
+  # Path to Galera library
+  wsrep_provider=/usr/lib/libgalera_smm.so
+
+  # Empty gcomm address is being used when cluster is getting bootstrapped
+  wsrep_cluster_address=gcomm://
+
+  # Cluster connection URL contains the IPs of node#1, node#2 and node#3
+  #wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
+
+  # In order for Galera to work correctly binlog format should be ROW
+  binlog_format=ROW
+
+  # MyISAM storage engine has only experimental support
+  default_storage_engine=InnoDB
+
+  # This is a recommended tuning variable for performance
+  innodb_locks_unsafe_for_binlog=1
+
+  # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
+  innodb_autoinc_lock_mode=2
+
+  # Node #1 address
+  wsrep_node_address=192.168.70.61
+
+  # SST method
+  wsrep_sst_method=xtrabackup
+
+  # Cluster name
+  wsrep_cluster_name=my_ubuntu_cluster
+
+  # Authentication for SST method
+  wsrep_sst_auth="sstuser:s3cretPass"
+
+.. note:: For the first member of the cluster variable :variable:`wsrep_cluster_address` should contain empty ``gcomm://`` when the cluster is being bootstrapped. But as soon as we have bootstrapped the cluster and have at least one more node joined that line can be removed from the :file:`my.cnf` configuration file and the one where :variable:`wsrep_cluster_address` contains all three node addresses. In case the node gets restarted and without making this change it will make bootstrap new cluster instead of joining the existing one.
+
+After this, first node can be started with the following command: ::
+
+  [root@pxc1 ~]# /etc/init.d/mysql start
+ 
+This command will start the first node and bootstrap the cluster (more information about bootstrapping cluster can be found in :ref:`bootstrap` manual).
+
+After the first node has been started, cluster status can be checked by: 
+
+.. code-block:: mysql 
+
+  mysql> show status like 'wsrep%';
+  +----------------------------+--------------------------------------+
+  | Variable_name              | Value                                |
+  +----------------------------+--------------------------------------+
+  | wsrep_local_state_uuid     | b598af3e-ace3-11e2-0800-3e90eb9cd5d3 |
+  ...
+  | wsrep_local_state          | 4                                    |
+  | wsrep_local_state_comment  | Synced                               |
+  ...
+  | wsrep_cluster_size         | 1                                    |
+  | wsrep_cluster_status       | Primary                              |
+  | wsrep_connected            | ON                                   |
+  ...
+  | wsrep_ready                | ON                                   |
+  +----------------------------+--------------------------------------+
+  40 rows in set (0.01 sec)
+
+This output shows that the cluster has been successfully bootstrapped. 
+
+In order to perform successful :ref:`state_snapshot_transfer` using |XtraBackup| new user needs to be set up with proper `privileges <http://www.percona.com/doc/percona-xtrabackup/innobackupex/privileges.html#permissions-and-privileges-needed>`_: 
+
+.. code-block:: mysql
+
+  mysql@pxc1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
+  mysql@pxc1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
+  mysql@pxc1> FLUSH PRIVILEGES;
+
+
+.. note:: 
+
+ MySQL root account can also be used for setting up the :ref:`state_snapshot_transfer` with Percona XtraBackup, but it's recommended to use a different (non-root) user for this.
+
+Configuration file :file:`/etc/mysql/my.cnf` on the second node (``pxc2``) should look like this: ::
+
+  [mysqld]
+
+  datadir=/var/lib/mysql
+  user=mysql
+
+  # Path to Galera library
+  wsrep_provider=/usr/lib/libgalera_smm.so
+
+  # Cluster connection URL contains IPs of node#1, node#2 and node#3
+  wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
+
+  # In order for Galera to work correctly binlog format should be ROW
+  binlog_format=ROW
+
+  # MyISAM storage engine has only experimental support
+  default_storage_engine=InnoDB
+
+  # This is a recommended tuning variable for performance
+  innodb_locks_unsafe_for_binlog=1
+
+  # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
+  innodb_autoinc_lock_mode=2
+
+  # Node #2 address
+  wsrep_node_address=192.168.70.62
+
+  # Cluster name
+  wsrep_cluster_name=my_ubuntu_cluster
+
+  # SST method
+  wsrep_sst_method=xtrabackup
+
+  #Authentication for SST method
+  wsrep_sst_auth="sstuser:s3cretPass"
+ 
+Second node can be started with the following command: ::
+
+  [root@pxc2 ~]# /etc/init.d/mysql start
+
+After the server has been started it should receive the state snapshot transfer automatically. Cluster status can now be checked on both nodes. This is the example from the second node (``pxc2``): 
+
+.. code-block:: mysql 
+
+  mysql> show status like 'wsrep%';
+  +----------------------------+--------------------------------------+
+  | Variable_name              | Value                                |
+  +----------------------------+--------------------------------------+
+  | wsrep_local_state_uuid     | b598af3e-ace3-11e2-0800-3e90eb9cd5d3 |
+  ...
+  | wsrep_local_state          | 4                                    |
+  | wsrep_local_state_comment  | Synced                               |
+  ...
+  | wsrep_cluster_size         | 2                                    |
+  | wsrep_cluster_status       | Primary                              |
+  | wsrep_connected            | ON                                   |
+  ...
+  | wsrep_ready                | ON                                   |
+  +----------------------------+--------------------------------------+
+  40 rows in set (0.01 sec)
+
+This output shows that the new node has been successfully added to the cluster. 
+
+MySQL configuration file :file:`/etc/mysql/my.cnf` on the third node (``pxc3``) should look like this: ::
+
+  [mysqld]
+
+  datadir=/var/lib/mysql
+  user=mysql
+
+  # Path to Galera library
+  wsrep_provider=/usr/lib/libgalera_smm.so
+
+  # Cluster connection URL contains IPs of node#1, node#2 and node#3
+  wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
+
+  # In order for Galera to work correctly binlog format should be ROW
+  binlog_format=ROW
+
+  # MyISAM storage engine has only experimental support
+  default_storage_engine=InnoDB
+
+  # This is a recommended tuning variable for performance
+  innodb_locks_unsafe_for_binlog=1
+
+  # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
+  innodb_autoinc_lock_mode=2
+
+  # Node #3 address
+  wsrep_node_address=192.168.70.63
+
+  # Cluster name
+  wsrep_cluster_name=my_ubuntu_cluster
+
+  # SST method
+  wsrep_sst_method=xtrabackup
+
+  #Authentication for SST method
+  wsrep_sst_auth="sstuser:s3cretPass"
+
+Third node can now be started with the following command: :: 
+
+  [root@pxc3 ~]# /etc/init.d/mysql start
+
+After the server has been started it should receive the SST same as the second node. Cluster status can now be checked on both nodes. This is the example from the third node (``pxc3``): 
+
+.. code-block:: mysql 
+
+  mysql> show status like 'wsrep%';
+  +----------------------------+--------------------------------------+
+  | Variable_name              | Value                                |
+  +----------------------------+--------------------------------------+
+  | wsrep_local_state_uuid     | b598af3e-ace3-11e2-0800-3e90eb9cd5d3 |
+  ...
+  | wsrep_local_state          | 4                                    |
+  | wsrep_local_state_comment  | Synced                               |
+  ...
+  | wsrep_cluster_size         | 3                                    |
+  | wsrep_cluster_status       | Primary                              |
+  | wsrep_connected            | ON                                   |
+  ...
+  | wsrep_ready                | ON                                   |
+  +----------------------------+--------------------------------------+
+  40 rows in set (0.01 sec)
+
+This output confirms that the third node has joined the cluster.
+
+Testing the replication
+-----------------------
+
+Although the password change from the first node has replicated successfully, this example will show that writing on any node will replicate to the whole cluster. In order to check this, new database will be created on second node and table for that database will be created on the third node.
+
+Creating the new database on the second node: 
+
+.. code-block:: mysql 
+
+  mysql@pxc2> CREATE DATABASE percona;
+  Query OK, 1 row affected (0.01 sec)
+
+Creating the ``example`` table on the third node: 
+  
+.. code-block:: mysql 
+
+  mysql@pxc3> USE percona;
+  Database changed
+
+  mysql@pxc3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
+  Query OK, 0 rows affected (0.05 sec)
+
+Inserting records on the first node: 
+
+.. code-block:: mysql 
+
+  mysql@pxc1> INSERT INTO percona.example VALUES (1, 'percona1');
+  Query OK, 1 row affected (0.02 sec)
+
+Retrieving all the rows from that table on the second node: 
+
+.. code-block:: mysql 
+
+  mysql@pxc2> SELECT * FROM percona.example;
+  +---------+-----------+
+  | node_id | node_name |
+  +---------+-----------+
+  |       1 | percona1  |
+  +---------+-----------+
+  1 row in set (0.00 sec)
+
+This small example shows that all nodes in the cluster are synchronized and working as intended.
 
=== modified file 'doc-pxc/source/index.rst'
--- doc-pxc/source/index.rst	2013-03-12 09:32:01 +0000
+++ doc-pxc/source/index.rst	2013-06-13 13:58:35 +0000
@@ -79,6 +79,7 @@
    :glob:
   
    howtos/cenots_howto
+   howtos/ubuntu_howto
    howtos/singlebox
    howtos/3nodesec2
    howtos/haproxy
 
=== modified file 'doc-pxc/source/installation.rst'
--- doc-pxc/source/installation.rst	2013-03-12 09:32:01 +0000
+++ doc-pxc/source/installation.rst	2013-06-13 13:58:35 +0000
@@ -44,6 +44,8 @@
 
   $ sudo apt-get install percona-xtradb-cluster-server-5.5 percona-xtradb-cluster-client-5.5
 
+More detailed example of the |Percona XtraDB Cluster| installation and configuration can be seen in :ref:`ubuntu_howto` tutorial.
+
 Prerequisites
 =============
 
 
=== modified file 'doc-pxc/source/limitation.rst'
--- doc-pxc/source/limitation.rst	2012-06-01 04:28:35 +0000
+++ doc-pxc/source/limitation.rst	2013-06-13 13:58:35 +0000
@@ -1,3 +1,5 @@
+.. _limitations:
+
 ====================================
  Percona XtraDB Cluster Limitations
 ====================================
@@ -6,8 +8,6 @@
 
  - Currently replication works only with |InnoDB| storage engine. Any writes to tables of other types, including system (mysql.*) tables, are not replicated. However, DDL statements are replicated in statement level, and changes to mysql.* tables will get replicated that way. So, you can safely issue: CREATE USER..., but issuing: INSERT INTO mysql.user..., will not be replicated.
 
- - DELETE operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. As a result SELECT...LIMIT... may return slightly different sets.
-
  - Unsupported queries:
     * LOCK/UNLOCK TABLES cannot be supported in multi-master setups.
     * lock functions (GET_LOCK(), RELEASE_LOCK()... )
@@ -21,10 +21,7 @@
 
  - XA transactions can not be supported due to possible rollback on commit.
 
- - The write throughput of the whole cluster is limited by weakest node. If one node becomes slow, whole cluster is slow. If you have requirements for stable high performance, then it should be supported by corresponding hardware (10Gb network, SSD).
+ - The write throughput of the whole cluster is limited by weakest node. If one node becomes slow, whole cluster is slow. If you have requirements for stable high performance, then it should be supported by corresponding hardware.
 
  - The minimal recommended size of cluster is 3 nodes.
 
- - DDL statements are problematic and may stall cluster. Later, the support of DDL will be improved, but will always require special treatment. 
-
-
 
=== modified file 'doc-pxc/source/wsrep-system-index.rst'
--- doc-pxc/source/wsrep-system-index.rst	2013-03-12 09:32:01 +0000
+++ doc-pxc/source/wsrep-system-index.rst	2013-06-13 13:58:35 +0000
@@ -13,8 +13,8 @@
    :default: TOI
 
 This variable can be used to select schema upgrade method. Available values are:
-  * TOI - Total Order Isolation - When this method is selected ``DDL`` is processed in the same order with regards to other transactions in each cluster node. This guarantees data consistency. In case of ``DDL`` statements cluster will have parts of database locked and it will behave like a single server. In some cases (like big ``ALTER TABLE``) this could have impact on cluster's performance and high availability, but it could be fine for quick changes that happen almost instantly (like fast index changes).
-  * RSU - Rolling Schema Upgrade - When this method is selected ``DDL`` statements won't be replicated across the cluster, instead it's up to the user to run them on each node separately. The node applying the changes will desynchronize from the cluster briefly, while normal work happens on all the other nodes. When the ``DDL`` statement is processed node will apply delayed replication events. The schema changes **must** be backwards compatible for this method to work, otherwise the node that receives the change will likely break Galera replication. If the replication breaks the SST will be triggered when the node tries to join again but the change will be undone.
+  * TOI - Total Order Isolation - When this method is selected ``DDL`` is processed in the same order with regards to other transactions in each cluster node. This guarantees data consistency. In case of ``DDL`` statements cluster will have parts of database locked and it will behave like a single server. In some cases (like big ``ALTER TABLE``) this could have impact on cluster's performance and high availability, but it could be fine for quick changes that happen almost instantly (like fast index changes). When ``DDL`` is processed under total order isolation (TOI) the ``DDL`` statement will be replicated up front to the cluster. i.e. cluster will assign global transaction ID for the ``DDL`` statement before the ``DDL`` processing begins. Then every node in the cluster has the responsibility to execute the ``DDL`` in the given slot in the sequence of incoming transactions, and this ``DDL`` execution has to happen with high priority. 
+  * RSU - Rolling Schema Upgrade - When this method is selected ``DDL`` statements won't be replicated across the cluster, instead it's up to the user to run them on each node separately. The node applying the changes will desynchronize from the cluster briefly, while normal work happens on all the other nodes. When the ``DDL`` statement is processed node will apply delayed replication events. The schema changes **must** be backwards compatible for this method to work, otherwise the node that receives the change will likely break Galera replication. If the replication breaks the SST will be triggered when the node tries to join again but the change will be undone. 
 
 .. variable:: wsrep_auto_increment_control
 
@@ -172,7 +172,7 @@
    :default: 0 (no grouping)
    :range: 0-1000
 
-This variable controls how many replication events will be grouped together. This implementation is still experimental.
+This variable controls how many replication events will be grouped together. Replication events are grouped in SQL slave thread by skipping events which may cause commit. This way the wsrep node acting in |MySQL| slave role and all other wsrep nodes in provider replication group, will see same (huge) transactions. This implementation is still experimental. This may help with the bottleneck of having only one |MySQL| slave facing commit time delay of synchronous provider.
 
 .. variable:: wsrep_node_address
 
@@ -271,8 +271,7 @@
    :dyn: No
    :default: 1
 
-This variable sets the number of times autocommitted transactions will be tried in the cluster if it encounters certification errors. In case there is a conflict, it should be safe for the cluster node to simply retry the statement without the client's knowledge with the hopes that it will pass the next time. This can be useful to help an application using autocommit to avoid the deadlock errors that can be triggered by replication conflicts. Note that the default 1 is not a retry, but the first try. Retries start when this variable is set to 2 or higher.  
-
+This variable sets the number of times autocommitted transactions will be tried in the cluster if it encounters certification errors. In case there is a conflict, it should be safe for the cluster node to simply retry the statement without the client's knowledge with the hopes that it will pass the next time. This can be useful to help an application using autocommit to avoid the deadlock errors that can be triggered by replication conflicts. If this variable is set to ``0`` transaction won't be retried and if it is set to ``1`` it will be retried once.
 
 .. variable:: wsrep_slave_threads