Merge lp:~hrvojem/percona-xtrabackup/doc-43-2.1 into lp:percona-xtrabackup/2.1

[Hrvoje Matijakovic]

[Sergei Glushchenko (sergei.glushchenko)]

Hrvoje,
Below my notes:

- as it can extended the time needed for backup to take place -- typo extended -> extend ?
- the part about --lock-wait-threshold should belong to "Waiting for queries to finish"
 section, not to "Killing the blocking queries" section. It specifies which query is
 we should wait for, not which query is allowed to be killed.
- when we talking "wait for all queries to finish" we mean "all LONG RUNNING (exec time longer than allowed by --lock-wait-threshold)"

review: Needs Fixing (g2)

[Sergei Glushchenko (sergei.glushchenko)]

Hrvoje,

Few more comments

1.
It is not obvious what happens when --lock-wait-timeout expired (backup actually bail out with error message and non-zero return code)

2.
Can you also add usage examples with explanation of what they do.
Something like

innobackupex --lock-wait-threshold=40 --lock-wait-query-type=all --lock-wait-timeout=180 --kill-long-queries-timeout=20 --kill-long-query-type=all {please add whatever is needed for command to be correct}

this will cause innobackupex to spin no longer than 3 minutes waiting for all queries older than 40 seconds to complete. After FTWRL issued, innobackupex will wait 20 seconds for lock acquired. If lock is still not acquired after 20 seconds, it will kill all queries which arun longer that FTWRL.

3.
Following is incorrect:

+After the threshold has been specified with the :option:`innobackupex --lock-wait-threshold` option, :option:`innobackupex --kill-long-query-type` option can be used to kill the long running queries that have passed the threshold. This options allows |innobackupex| to kill either all or only ``SELECT`` queries that are preventing global lock from being acquired.

--lock-wait-threshold does not have any impact on which query should be killed. Queries which older than FTWRL are killed. Only SELECT's or all whichever is specified by --kill-long-query-type.

review: Needs Fixing (g2)

[Sergei Glushchenko (sergei.glushchenko)]

Sentence

Option, :option:`innobackupex --kill-long-query-type` option can be used to kill the long running queries that are older than ``FLUSH TABLES WITH READ LOCK``.

does not look correct. I think it and next sentences can be combined together. Something like:

:option:`innobackupex --kill-long-query-type` option can be used to specify all or only ``SELECT`` queries that are preventing global lock from being acquired.

[Sergei Glushchenko (sergei.glushchenko)]

review: Needs Fixing (g2)

[Alexey Kopytov (akopytov)]

then the following sentence in the introductory section should also be corrected:

"|innobackupex| can kill selects which are preventing the global lock from being acquired"

I.e. should say "all or only SELECT queries".

review: Needs Fixing

[Alexey Kopytov (akopytov)]

The following also looks incorrect:

"To avoid locking the tables for too long, new option has been implemented: :option:`innobackupex --lock-wait-timeout` option can be used to limit the waiting time."

The option limits the time innobackupex should wait before taking the lock, rather than the lock duration. I'd say something like "To prevent innobackupex from waiting to issue FTWRL for too long, ...".

review: Needs Fixing

[Alexey Kopytov (akopytov)]

review: Approve

[Sergei Glushchenko (sergei.glushchenko)]

Approve

review: Approve (g2)

=== modified file 'doc/source/howtos/setting_up_replication.rst'
--- doc/source/howtos/setting_up_replication.rst	2013-04-29 11:08:57 +0000
+++ doc/source/howtos/setting_up_replication.rst	2013-07-31 10:57:28 +0000
@@ -204,7 +204,7 @@
 
    TheSlave$ innobackupex --apply-log --use-memory=2G /path/to/backupdir/$TIMESTAMP/
 
-Copy the directory from the ``TheSlave`` to ``TheNewSlave`` (**NOTE**: Make sure mysqld is shut down on ``TheNewSlave`` before you copy the contents the snapshot into its :term:`datadir`.): ::
+Copy the directory from the ``TheSlave`` to ``TheNewSlave`` (**NOTE**: Make sure mysqld is shut down on ``TheNewSlave`` before you copy the contents the snapshot into its :term:`datadir`.): 
 
 .. code-block:: console
 

=== added file 'doc/source/innobackupex/improved_ftwrl.rst'
--- doc/source/innobackupex/improved_ftwrl.rst	1970-01-01 00:00:00 +0000
+++ doc/source/innobackupex/improved_ftwrl.rst	2013-07-31 10:57:28 +0000
@@ -0,0 +1,55 @@
+.. _improved_ftwrl:
+
+==================================================
+Improved ``FLUSH TABLES WITH READ LOCK`` handling
+==================================================
+
+When taking backups, ``FLUSH TABLES WITH READ LOCK`` is being used before the non-InnoDB files are being backed up to ensure backup is being consistent. ``FLUSH TABLES WITH READ LOCK`` can be run even though there may be a running query that has been executing for hours. In this case everything will be locked up in ``Waiting for table flush`` or ``Waiting for master to send event`` states. Killing the ``FLUSH TABLES WITH READ LOCK`` does not correct this issue either. In this case the only way to get the server operating normally again is to kill off the long running queries that blocked it to begin with. This means that if there are long running queries ``FLUSH TABLES WITH READ LOCK`` can get stuck, leaving server in read-only mode until waiting for these queries to complete.  
+
+In order to prevent this from happening two things have been implemented:
+
+* |innobackupex| can wait for a good moment to issue the global lock.
+* |innobackupex| can kill all or only SELECT queries which are preventing the global lock from being acquired
+
+Waiting for queries to finish
+-----------------------------
+
+Good moment to issue a global lock is the moment when there are no long queries running. But waiting for a good moment to issue the global lock for extended period of time isn't always good approach, as it can extend the time needed for backup to take place. To prevent |innobackupex| from waiting to issue ``FLUSH TABLES WITH READ LOCK`` for too long, new option has been implemented: :option:`innobackupex --lock-wait-timeout` option can be used to limit the waiting time. If the good moment to issue the lock did not happen during this time, |innobackupex| will give up and exit with an error message and backup will not be taken. Zero value for this option turns off the feature (which is default).
+
+Another possibility is to specify the type of query to wait on. In this case :option:`innobackupex --lock-wait-query-type`. Possible values are ``all`` and ``update``. When ``all`` is used |innobackupex| will wait for all long running queries (execution time longer than allowed by :option:`innobackupex --lock-wait-threshold`) to finish before running the ``FLUSH TABLES WITH READ LOCK``. When ``update`` is used |innobackupex| will wait on ``UPDATE/ALTER/REPLACE/INSERT`` queries to finish.
+
+Although time needed for specific query to complete is hard to predict, we can assume that queries that are running for a long time already will likely not be completed soon, and queries which are running for a short time will likely be completed shortly. |innobackupex| can use the value of :option:`innobackupex --lock-wait-threshold` option to specify which query is long running and will likely block global lock for a while.
+
+Killing the blocking queries
+----------------------------
+
+Second option is to kill all the queries which prevent global lock from being acquired. In this case all the queries which run longer than ``FLUSH TABLES WITH READ LOCK`` are possible blockers. Although all queries can be killed, additional time can be specified for the short running queries to complete. This can be specified by :option:`innobackupex --kill-long-queries-timeout` option. This option specifies the time for queries to complete, after the value is reached, all the running queries will be killed. Default value is zero, which turns this feature off.
+
+:option:`innobackupex --kill-long-query-type` option can be used to specify all or only ``SELECT`` queries that are preventing global lock from being acquired.
+
+Options summary
+---------------
+
+* :option:`--lock-wait-timeout=N` (seconds) - how long to wait for a good moment. Default is 0, not to wait.
+
+* :option:`--lock-wait-query-type={all|update}` - which long queries should be finished before ``FLUSH TABLES WITH READ LOCK`` is run. Default is all.
+
+* :option:`--lock-wait-threshold=N` (seconds) - how long query should be running before we consider it long running and potential blocker of global lock.
+
+* :option:`--kill-long-queries-timeout=N` (seconds) - how many time we give for queries to complete after ``FLUSH TABLES WITH READ LOCK`` is issued before start to kill. Default if ``0``, not to kill.
+
+* :option:`--kill-long-query-type={all|update}` - which queries should be killed once :option:`kill-long-queries-timeout` has expired.
+
+Example
+-------
+
+Running the |innobackupex| with the following options: ::
+
+ $ innobackupex --lock-wait-threshold=40 --lock-wait-query-type=all --lock-wait-timeout=180 --kill-long-queries-timeout=20 --kill-long-query-type=all /data/backups/
+
+will cause |innobackupex| to spend no longer than 3 minutes waiting for all queries older than 40 seconds to complete. After ``FLUSH TABLES WITH READ LOCK`` is issued, |innobackupex| will wait 20 seconds for lock to be acquired. If lock is still not acquired after 20 seconds, it will kill all queries which are running longer that the ``FLUSH TABLES WITH READ LOCK``.
+
+Version Information
+-------------------
+
+This feature has been implemented in |Percona XtraBackup| 2.1.4.

=== modified file 'doc/source/innobackupex/innobackupex_option_reference.rst'
--- doc/source/innobackupex/innobackupex_option_reference.rst	2013-05-10 09:29:17 +0000
+++ doc/source/innobackupex/innobackupex_option_reference.rst	2013-07-31 10:57:28 +0000
@@ -114,6 +114,26 @@
 
    This option accepts a string argument that specifies the log sequence number (:term:`LSN`) to use for the incremental backup. It is used with the :option:`--incremental` option. It is used instead of specifying :option:`--incremental-basedir`. For databases created by *MySQL* and *Percona Server* 5.0-series versions, specify the as two 32-bit integers in high:low format. For databases created in 5.1 and later, specify the LSN as a single 64-bit integer.
 
+.. option:: --kill-long-queries-timeout=SECONDS
+
+   This option specifies the number of seconds innobackupex waits between starting ``FLUSH TABLES WITH READ LOCK`` and killing those queries that block it. Default is 0 seconds, which means innobackupex will not attempt to kill any queries.
+
+.. option:: --kill-long-query-type=all|update
+
+   This option specifies which types of queries should be killed to unblock the global lock. Default is "all".
+
+.. option:: --lock-wait-timeout=SECONDS
+
+   This option specifies time in seconds that innobackupex should wait for queries that would block ``FLUSH TABLES WITH READ LOCK`` before running it. If there are still such queries when the timeout expires, innobackupex terminates with an error. Default is 0, in which case innobackupex does not wait for queries to complete and starts ``FLUSH TABLES WITH READ LOCK`` immediately.
+
+.. option:: --lock-wait-threshold=SECONDS
+
+   This option specifies the query run time threshold which is used by innobackupex to detect long-running queries with a non-zero value of :option:`innobackupex --lock-wait-timeout`. FLUSH TABLES WITH READ LOCK`` is not started until such long-running queries exist. This option has no effect if --lock-wait-timeout is 0. Default value is 60 seconds.
+
+.. option:: --lock-wait-query-type=all|update
+
+   This option specifies which types of queries are allowed to complete before innobackupex will issue the global lock. Default is all.
+
 .. option:: --log-copy-interval
 
    This option specifies time interval between checks done by log copying thread in milliseconds.

=== modified file 'doc/source/innobackupex/innobackupex_script.rst'
--- doc/source/innobackupex/innobackupex_script.rst	2013-04-29 11:08:57 +0000
+++ doc/source/innobackupex/innobackupex_script.rst	2013-07-31 10:57:28 +0000
@@ -50,9 +50,8 @@
    throttling_ibk
    importing_exporting_tables_ibk
    pit_recovery_ibk
-
-
-..    performance_tunning_innobackupex
+   improved_ftwrl
+
 
 Implementation
 ==============

=== modified file 'doc/source/release-notes/2.1/2.1.4.rst'
--- doc/source/release-notes/2.1/2.1.4.rst	2013-07-25 20:00:02 +0000
+++ doc/source/release-notes/2.1/2.1.4.rst	2013-07-31 10:57:28 +0000
@@ -6,7 +6,7 @@
 New Features
 ------------
 
- |Percona XtraBackup| has introduced additional options to handle the locking during the ``FLUSH TABLES WITH READ LOCK``. These options can be used to minimize the amount of the time when |MySQL| operates in the read-only mode.
+ |Percona XtraBackup| has introduced :ref:`additional options <improved_ftwrl>` to handle the locking during the ``FLUSH TABLES WITH READ LOCK``. These options can be used to minimize the amount of the time when |MySQL| operates in the read-only mode.
 
  |Percona XtraBackup| has now been rebased on |MySQL| versions 5.1.70, 5.5.30, 5.6.11 and |Percona Server| versions 5.1.70-rel14.8 and 5.5.31-rel30.3 server versions.
 

=== modified file 'innobackupex'
--- innobackupex	2013-07-30 09:31:12 +0000
+++ innobackupex	2013-07-31 10:57:28 +0000
@@ -3679,7 +3679,7 @@
 
 =item --lock-wait-query-type=all|update
 
-This option specifies which types of queries are allowed to complete before innobackuped will issue the global lock. Default is all.
+This option specifies which types of queries are allowed to complete before innobackupex will issue the global lock. Default is all.
 
 =item --move-back