Merge lp:~hrvojem/percona-server/rn-5.6.13-60.6 into lp:percona-server/5.6

[Hrvoje Matijakovic]

[Laurynas Biveinis (laurynas-biveinis)]

   Not commenting on the missing stuff as this is a draft.

   - Line 44: remove whitespace before "=", and "CREATE,DROP,LOCK
   TABLES"
   - Line 64: s/CSV/comma-separated, missing space before EXECUTE.
   - Line 74: release date.
   - "The unnecessary overhead from persistent InnoDB adaptive hash
   index latching has been removed, potentially improving the
   stability of (AHI partitions link) feature as well. Bug fixed ... "
   - Move bug 1216804/62018 up to the "feature has been ported",
     assign its explaining the text to the feature instead.
   - Line 107: s/hash index memory/hash index memory size
   - "An unncessary buffer pool mutex acquisition has been removed,
     potentially improving performance".
   - 70228 goes to "other fixes".
   - "Adaptive hash indexing partitioning code has been simplified,
     potentially improving performance".

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

- 62018 reference is now lost?
- For AHI, s/decreases the contention/decreases AHI latch contention
- Diff line 102: s/the new variable/a new variable

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

- "... AHI latch contention, fixing upstream bug <bug ref> (<our bug ref>).
- diff line 146 s/for in/in
- "It can be used to specify an additional execution time threshold for the slow query log, that, when exceeded, will cause a query to be logged unconditionally, that is, ref log_slow_rate_limit will not apply to it."
- Even though AHI changes descriptions look +- acceptable to me, feel free to ping KAlexey if he's available for any suggestions.

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

review: Approve

=== modified file 'doc/source/conf.py'
--- doc/source/conf.py	2013-08-28 09:39:11 +0000
+++ doc/source/conf.py	2013-09-20 13:14:11 +0000
@@ -54,7 +54,7 @@
 # The short X.Y version.
 version = '5.6'
 # The full version, including alpha/beta/rc tags.
-release = '5.6.13-60.5'
+release = '5.6.13-60.6'
 
 # The language for content autogenerated by Sphinx. Refer to documentation
 # for a list of supported languages.

=== modified file 'doc/source/diagnostics/slow_extended.rst'
--- doc/source/diagnostics/slow_extended.rst	2013-06-03 10:01:08 +0000
+++ doc/source/diagnostics/slow_extended.rst	2013-09-20 13:14:11 +0000
@@ -16,6 +16,10 @@
 
      * Feature ported from |Percona Server| 5.5.
 
+  * :rn:`5.6.13-60.4`:
+
+      * New :variable:`slow_query_log_always_write_time` variable introduced
+
 Other Information
 =================
 
@@ -96,7 +100,9 @@
 
 This allows flexible setup logging behavior.
 
-For example, if you set the value to 100, then one percent of ``sessions/queries`` will be logged.
+For example, if you set the value to 100, then one percent of ``sessions/queries`` will be logged. In |Percona Server| :rn:`5.6.13-60.6` information about the :variable:`log_slow_rate_limit` has been added to the slow query log. This means that if the :variable:`log_slow_rate_limit` is effective it will be reflected in the slow query log for each written query. Example of the output looks like this: ::
+ 
+  Log_slow_rate_type: query  Log_slow_rate_limit: 10
 
 .. variable:: log_slow_sp_statements
 
@@ -212,6 +218,15 @@
   * ``all``
     Global variables has effect (instead of local)
 
+.. variable:: slow_query_log_always_write_time
+
+     :cli: Yes
+     :conf: Yes
+     :scope: Global
+     :dyn: Yes
+     :default: 10
+
+This variable can be used to specify the query execution time after which the query will be written to the slow query log. It can be used to specify an additional execution time threshold for the slow query log, that, when exceeded, will cause a query to be logged unconditionally, that is, :variable:`log_slow_rate_limit` will not apply to it.
 
 Other Information
 =================

=== modified file 'doc/source/index.rst'
--- doc/source/index.rst	2013-08-28 09:39:11 +0000
+++ doc/source/index.rst	2013-09-20 13:14:11 +0000
@@ -58,7 +58,9 @@
    :maxdepth: 1
    :glob:
 
+   scalability/innodb_split_buf_pool_mutex
    scalability/innodb_io
+   scalability/innodb_adaptive_hash_index_partitions
 
 Performance Improvements
 ========================

=== modified file 'doc/source/management/utility_user.rst'
--- doc/source/management/utility_user.rst	2013-06-03 10:01:08 +0000
+++ doc/source/management/utility_user.rst	2013-09-20 13:14:11 +0000
@@ -10,7 +10,7 @@
 
   * Utility user will not appear in the mysql.user table and can not be modified by any other user, including root.
 
-  * Utility user will not appear in USER_STATISTICS, CLIENT_STATISTICS or THREAD_STATISTICS tables.
+  * Utility user will not appear in :table:`USER_STATISTICS`, :table:`CLIENT_STATISTICS` or :table:`THREAD_STATISTICS` tables.
 
   * Utility user's queries may appear in the general and slow logs.
 
@@ -46,6 +46,10 @@
 
  Example: :option:`--utility_user_schema_access` =schema1,schema2,schema3 ;
 
+Option :variable:`utility_user_privileges` allows a comma-separated list of extra access privileges to grant to the utility user.
+
+ Example: :option:`--utility-user-privileges` ="CREATE, DROP, LOCK TABLES"
+
 System Variables
 ================
 
@@ -84,3 +88,15 @@
      :default: NULL
 
 Specifies the schemas that the utility user has access to in a comma delimited list.
+
+.. variable:: utility_user_privileges
+
+   :version 5.6.13-60.6: Implemented
+   :cli: Yes
+   :conf: utility_user_privileges=<privilege1>,<privilege2>,<privilege3>
+   :scope: Global
+   :dyn: No
+   :vartype: String
+   :default: NULL
+
+This variable can be used to specify a comma-separated list of extra access privileges to grant to the utility user. Supported values for the privileges list are: ``SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,FILE, GRANT, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES,EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE``

=== modified file 'doc/source/release-notes/Percona-Server-5.6.13-60.5.rst'
--- doc/source/release-notes/Percona-Server-5.6.13-60.5.rst	2013-08-28 09:39:11 +0000
+++ doc/source/release-notes/Percona-Server-5.6.13-60.5.rst	2013-09-20 13:14:11 +0000
@@ -4,7 +4,7 @@
  |Percona Server| 5.6.13-60.5
 ==============================
 
-Percona is glad to announce the third Release Candidate release of |Percona Server| 5.6.13-60.5 on August 28th, 2013 (Downloads are available `here <http://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.12-60.4/>`_ and from the :doc:`Percona Software Repositories </installation>`.
+Percona is glad to announce the third Release Candidate release of |Percona Server| 5.6.13-60.5 on August 28th, 2013 (Downloads are available `here <http://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.13-60.5/>`_ and from the :doc:`Percona Software Repositories </installation>`.
 
 Based on `MySQL 5.6.13 <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-13.html>`_, including all the bug fixes in it, |Percona Server| 5.6.13-60.5 is the third RC release in the |Percona Server| 5.6 series. All of |Percona|'s software is open-source and free, all the details of the release can be found in the `5.6.13-60.5 milestone at Launchpad <https://launchpad.net/percona-server/+milestone/5.6.13-60.5>`_.
 

=== added file 'doc/source/release-notes/Percona-Server-5.6.13-60.6.rst'
--- doc/source/release-notes/Percona-Server-5.6.13-60.6.rst	1970-01-01 00:00:00 +0000
+++ doc/source/release-notes/Percona-Server-5.6.13-60.6.rst	2013-09-20 13:14:11 +0000
@@ -0,0 +1,37 @@
+.. rn:: 5.6.13-60.6
+
+==============================
+ |Percona Server| 5.6.13-60.6
+==============================
+
+Percona is glad to announce the fourth Release Candidate release of |Percona Server| 5.6.13-60.6 on September 20th, 2013 (Downloads are available `here <http://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.13-60.6/>`_ and from the :doc:`Percona Software Repositories </installation>`.
+
+Based on `MySQL 5.6.13 <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-13.html>`_, including all the bug fixes in it, |Percona Server| 5.6.13-60.6 is the fourth RC release in the |Percona Server| 5.6 series. All of |Percona|'s software is open-source and free, all the details of the release can be found in the `5.6.13-60.6 milestone at Launchpad <https://launchpad.net/percona-server/+milestone/5.6.13-60.6>`_.
+
+New Features
+============
+ 
+ :ref:`innodb_split_buf_pool_mutex` feature has been ported from |Percona Server| 5.5. This feature splits the single global InnoDB buffer pool mutex into several mutexes. The goal of this change is to reduce mutex contention, which can be very impacting when the working set does not fit in memory.
+
+ :ref:`innodb_adaptive_hash_index_partitions_page` feature has been ported from |Percona Server| 5.5. This feature splits the adaptive hash index across several partitions and decreases the AHI latch contention. This feature fixes the upstream bug :mysqlbug:`62018` (:bug:`1216804`).
+ 
+ :ref:`psaas_utility_user` feature has been extended by adding a new :variable:`utility_user_privileges` that allows a comma separated value list of extra access privileges that can be granted to the utility user.
+
+ |Percona Server| now provides additional information in the slow query log when :variable:`log_slow_rate_limit` variable is enabled.
+
+ A new variable :variable:`slow_query_log_always_write_time` has been introduced. It can be used to specify an additional execution time threshold for the slow query log, that, when exceeded, will cause a query to be logged unconditionally, that is, :variable:`log_slow_rate_limit` will not apply to it.
+
+Bugs Fixed
+==========
+
+ The unnecessary overhead from persistent InnoDB adaptive hash index latching has been removed, potentially improving stability of the :ref:`innodb_adaptive_hash_index_partitions_page` feature as well. Upstream bug fixed :mysqlbug:`70216`, bug fixed :bug:`1218347`.
+
+ Adaptive hash index memory size was incorrectly calculated in ``SHOW ENGINE INNODB STATUS`` and :table:`XTRADB_INTERNAL_HASH_TABLES`. Bug fixed :bug:`1218330`.
+
+ An unnecessary buffer pool mutex acquisition has been removed, potentially improving performance. Upstream bug fixed :mysqlbug:`69258`, bug fixed :bug:`1219842`.
+
+ Fixed the build warnings caused by :ref:`user_stats` code on non-Linux platforms. Bug fixed :bug:`711817`.
+
+ Adaptive hash indexing partitioning code has been simplified, potentially improving performance. Bug fixed :bug:`1218321`.
+
+Other bugs fixed: upstream bug fixed :mysqlbug:`69617` bug fixed :bug:`1216815`, upstream bug fixed :mysqlbug:`70228` bug fixed :bug:`1220544`. 

=== modified file 'doc/source/release-notes/release-notes_index.rst'
--- doc/source/release-notes/release-notes_index.rst	2013-08-28 09:39:11 +0000
+++ doc/source/release-notes/release-notes_index.rst	2013-09-20 13:14:11 +0000
@@ -6,6 +6,7 @@
    :maxdepth: 1
    :glob:
 
+   Percona-Server-5.6.13-60.6
    Percona-Server-5.6.13-60.5
    Percona-Server-5.6.12-60.4
    Percona-Server-5.6.11-60.3

=== added file 'doc/source/scalability/innodb_adaptive_hash_index_partitions.rst'
--- doc/source/scalability/innodb_adaptive_hash_index_partitions.rst	1970-01-01 00:00:00 +0000
+++ doc/source/scalability/innodb_adaptive_hash_index_partitions.rst	2013-09-20 13:14:11 +0000
@@ -0,0 +1,39 @@
+.. _innodb_adaptive_hash_index_partitions_page:
+
+==========================================
+ Multiple Adaptive Hash Search Partitions
+==========================================
+
+The |InnoDB| adaptive hash index can have contention issues on multi-core systems when you run a mix of read and write queries that need to scan secondary indexes. This feature splits the adaptive hash index across several partitions to avoid such problems.
+
+The number of adaptive hash partitions specified by the variable :variable:`innodb_adaptive_hash_index_partitions` are created, and hash indexes are assigned to each one based on ``index_id``. This should help to solve contention problems in the adaptive hash search process when they occur.
+
+
+Version Specific Information
+============================
+ * :rn:`5.6.13-60.6` - Feature ported from |Percona Server| 5.5
+
+System Variables
+----------------
+
+.. variable:: innodb_adaptive_hash_index_partitions
+
+   :version 5.6.13-60.6: Ported from |Percona Server| 5.6
+   :cli: Yes
+   :conf: Yes
+   :scope: Global
+   :dyn: No
+   :vartype: Numeric
+   :def: 1
+   :range: 1-64, (on 32-bit platform 1-32)
+
+Specifies the number of partitions to use in the adaptive hash search process.
+
+When set to one, no extra partitions are created and the normal process is in effect. When greater than one, the specified number of partitions are created across which to perform the adaptive search.
+
+Other reading
+-------------
+
+  * `Index lock and adaptive search <http://www.mysqlperformanceblog.com/2010/02/25/index-lock-and-adaptive-search-next-two-biggest-innodb-problems/>`_
+
+  * |MySQL| bug :mysqlbug:`62018`

=== added file 'doc/source/scalability/innodb_split_buf_pool_mutex.rst'
--- doc/source/scalability/innodb_split_buf_pool_mutex.rst	1970-01-01 00:00:00 +0000
+++ doc/source/scalability/innodb_split_buf_pool_mutex.rst	2013-09-20 13:14:11 +0000
@@ -0,0 +1,80 @@
+.. _innodb_split_buf_pool_mutex:
+
+==================================
+ Improved Buffer Pool Scalability
+==================================
+
+The |InnoDB| buffer pool is a well known point of contention when many queries are executed concurrently. In |XtraDB|, the global mutex protecting the buffer pool has been split into several mutexes to decrease contention.
+
+This feature splits the single global InnoDB buffer pool mutex into several mutexes:
+
+.. list-table::
+   :widths: 20 40
+   :header-rows: 1
+
+   * - Name
+     - Protects
+   * - flush_state_mutex
+     - flushing state of dirty blocks
+   * - LRU_list_mutex
+     - LRU lists of blocks in buffer pool
+   * - flush_list_mutex
+     - flush list of dirty blocks to flush
+   * - free_list_mutex	 
+     - list of free blocks in buffer pool
+   * - zip_free_mutex	 
+     - lists of free area to treat compressed pages
+   * - zip_hash_mutex	 
+     - hash table to search compressed pages
+
+The goal of this change is to reduce mutex contention, which can be very impacting when the working set does not fit in memory.
+
+Version Specific Information
+============================
+ * :rn:`5.6.13-60.6` - Feature ported from |Percona Server| 5.5
+
+Other Information
+=================
+
+Detecting Mutex Contention
+--------------------------
+
+You can detect when you suffer from mutex contention in the buffer pool by reading the information provided in the SEMAPHORES section of the output of SHOW ENGINE INNODB STATUS:
+
+Under normal circumstances this section should look like this:
+
+.. code-block:: guess
+
+   SEMAPHORES
+   ----------
+   OS WAIT ARRAY INFO: reservation count 50238, signal count 17465
+   Mutex spin waits 0, rounds 628280, OS waits 31338
+   RW-shared spins 38074, OS waits 18900; RW-excl spins 0, OS waits 0
+
+If you have a high-concurrency workload this section may look like this:
+
+.. code-block:: guess
+
+   1 ----------
+   2 SEMAPHORES
+   3 ----------
+   4 OS WAIT ARRAY INFO: reservation count 36255, signal count 12675
+   5 --Thread 10607472 has waited at buf/buf0rea.c line 420 for 0.00 seconds the semaphore:
+   6 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0
+   7 waiters flag 0
+   8 --Thread 3488624 has waited at buf/buf0buf.c line 1177 for 0.00 seconds the semaphore:
+   9 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0
+   10 waiters flag 0
+   11 --Thread 6896496 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore:
+   12 S-lock on RW-latch at 0x8800244 created in file buf/buf0buf.c line 547
+   13 a writer (thread id 14879600) has reserved it in mode  exclusive
+   14 number of readers 0, waiters flag 1
+   15 Last time read locked in file btr/btr0cur.c line 442
+   16 Last time write locked in file buf/buf0buf.c line 1797
+   [...]
+   17 Mutex spin waits 0, rounds 452650, OS waits 22573
+   18 RW-shared spins 27550, OS waits 13682; RW-excl spins 0, OS waits 0
+
+
+Note that in the second case you will see indications that threads are waiting for a mutex created in the file buf/buf0buf.c (lines 5 to 7 or 8 to 10). Such an indication is a sign of buffer pool contention.
+