Merge lp:~hrvojem/percona-server/bug1056603-5.1 into lp:percona-server/5.1

Proposed by Hrvoje Matijakovic
Status: Merged
Approved by: Alexey Kopytov
Approved revision: 496
Merged at revision: 496
Proposed branch: lp:~hrvojem/percona-server/bug1056603-5.1
Merge into: lp:percona-server/5.1
Diff against target: 581 lines (+243/-143)
8 files modified
doc/source/diagnostics/innodb_stats.rst (+46/-29)
doc/source/diagnostics/misc_info_schema_tables.rst (+123/-44)
doc/source/diagnostics/response_time_distribution.rst (+7/-23)
doc/source/diagnostics/user_stats.rst (+25/-29)
doc/source/index.rst (+1/-1)
doc/source/index_info_schema_tables.rst (+31/-17)
doc/source/management/innodb_lru_dump_restore.rst (+9/-0)
doc/source/performance/innodb_opt_lru_count.rst (+1/-0)
To merge this branch: bzr merge lp:~hrvojem/percona-server/bug1056603-5.1
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) Approve
Review via email: mp+134478@code.launchpad.net
To post a comment you must log in.
Alexey Kopytov (akopytov) wrote :

Hrvoje,

Few comments:

- There's a difference between internal InnoDB tables naming and the
  corresponding I_S tables exposing those internal tables. That is, the
  internal InnoDB tables are called SYS_STATS, SYS_TABLES and
  SYS_INDEXES, while their user-visible I_S counterparts have the
  INNODB_ prefix: INNODB_SYS_STATS, INNODB_SYS_TABLES and
  INNODB_SYS_INDEXES.

So it is technically incorrect to say that "If this option is enabled,
|XtraDB| uses the :table:`INNODB_SYS_STATS` system table to store
statistics of table indexes". It actually uses SYS_STATS, as correctly
described now.

- the patch adds INNODB_SYS_TABLES and INNODB_SYS_INDEXES which is good,
  but it only describes their structure without providing any further
  info, which is not that good :)

- s/the index haven't been used/the index has not been used/

review: Needs Fixing
Alexey Kopytov (akopytov) wrote :

Hrvoje,

Please use my descriptions of the SPACE_ID, PAGE_NO, LRU_POSITION, FIX_COUNT and ACCESS_TIME fields that I have provided in https://code.launchpad.net/~hrvojem/percona-server/bug1056603-5.5/+merge/134998 for all tables, including INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES.

review: Needs Fixing
Alexey Kopytov (akopytov) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'doc/source/diagnostics/innodb_stats.rst'
2--- doc/source/diagnostics/innodb_stats.rst 2012-05-29 18:31:23 +0000
3+++ doc/source/diagnostics/innodb_stats.rst 2012-12-10 14:05:32 +0000
4@@ -6,14 +6,6 @@
5
6 This feature provides new startup options (control method and collection of index statistics estimation) and information schema views to confirm the statistics.
7
8-This implements the fix for `MySQL Bug #30423 <http://bugs.mysql.com/bug.php?id=30423>`_.
9-
10-Version Specific Information
11-============================
12-
13- * 5.5.8-20.0:
14- Renamed three fields in table ``INNODB_INDEX_STATS``.
15-
16
17 System Variables
18 ================
19@@ -30,7 +22,7 @@
20 :default: ``nulls_equal``
21 :allowed: ``nulls_equal``, ``nulls_unequal``, ``nulls_ignored``
22
23-The values and meanings are almost same to ``myisam_stats_method`` option of native |MySQL| (``nulls_equal``, ``nulls_unequal``, ``nulls_ignored``). But |InnoDB| doesn't have several patterns of statistics currently. So, though this option able to be changed dynamically, we need re-calculate statistics to change the method for the table.
24+The values and meanings are almost same to ``myisam_stats_method`` option of native |MySQL| (``nulls_equal``, ``nulls_unequal``, ``nulls_ignored``). But |InnoDB| doesn't have several patterns of statistics currently. Even though this option can be changed dynamically, statistics needs to be re-calculated to change the method for the table.
25
26 (reference: `MyISAM Index Statistics Collection <http://dev.mysql.com/doc/refman/5.1/en/myisam-index-statistics.html>`_)
27
28@@ -41,7 +33,7 @@
29 :type: BOOLEAN
30 :default: 1
31
32-|InnoDB| updates the each index statistics automatically (many updates were done, some information_schema is accessed, table monitor, etc.). Setting this option 0 can stop these automatic recalculation of the statistics except for “first open” and “ANALYZE TABLE command”.
33+|InnoDB| updates the each index statistics automatically (many updates were done, some information_schema is accessed, table monitor, etc.). Setting this option 0 can stop these automatic recalculation of the statistics except for "first open" and "ANALYZE TABLE command".
34
35
36 .. variable:: innodb_stats_update_need_lock
37@@ -65,7 +57,41 @@
38 INFORMATION_SCHEMA Tables
39 =========================
40
41-Two new tables were introduced by this feature.
42+.. table:: INFORMATION_SCHEMA.INNODB_SYS_STATS
43+
44+ :column INDEX_ID: Index ID
45+ :column KEY_COLS: Number of Key Columns
46+ :column DIFF_VALS: Number of Different Values.
47+ :column NON_NULL_VALS: Number of Non NULL Values.
48+
49+
50+.. table:: INFORMATION_SCHEMA.INNODB_SYS_TABLES
51+
52+ Shows the information about InnoDB tables
53+
54+ :column SCHEMA: Schema (database) name
55+ :column NAME: Table name
56+ :column ID: Table ID
57+ :column N_COLS: Number of Columns
58+ :column TYPE:
59+ :column MIX_ID: This value is obsolete, value is always 0
60+ :column MIX_LEN: Contains 0 for regular tables and 1 for temporary tables
61+ :column CLUSTER_NAME: This value isn't supported anymore, value is always NULL
62+ :column SPACE: Tablespace ID
63+
64+
65+.. table:: INFORMATION_SCHEMA.INNODB_SYS_INDEXES
66+
67+ Shows the information about InnoDB indexes
68+
69+ :column TABLE_ID: Table ID
70+ :column ID: Index ID
71+ :column NAME: Index Name
72+ :column N_FIELDS: Number of fields
73+ :column TYPE:
74+ :column SPACE: Tablespace ID
75+ :column PAGE_NO: The page offset within its tablespace
76+
77
78 .. table:: INFORMATION_SCHEMA.INNODB_TABLE_STATS
79
80@@ -78,7 +104,7 @@
81 :column other_size: Other index (non primary key) size in number of pages.
82 :column modified: Internal counter to judge whether statistics recalculation should be done.
83
84-If the value of modified column exceeds “rows / 16” or 2000000000, the statistics recalculation is done when ``innodb_stats_auto_update == 1``. We can estimate the oldness of the statistics by this value.
85+If the value of modified column exceeds "rows / 16" or 2000000000, the statistics recalculation is done when ``innodb_stats_auto_update == 1``. We can estimate the oldness of the statistics by this value.
86
87 .. table:: INFORMATION_SCHEMA.INNODB_INDEX_STATS
88
89@@ -88,24 +114,15 @@
90 :column table_name: Table name.
91 :column index_name: Index name.
92 :column fields: How many fields the index key has. (it is internal structure of |InnoDB|, it may be larger than the ``CREATE TABLE``).
93- :column rows_per_key: Estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], ...).
94- :column index_total_pages: Number of index pages.
95- :column index_leaf_pages: Number of leaf pages.
96-
97-In releases before 5.5.8-20.0, these fields had different names:
98-
99- * ``rows_per_key`` was ``row_per_keys``
100-
101- * ``index_total_pages`` was ``index_size``
102-
103- * ``index_leaf_pages`` was ``leaf_pages``
104+ :column rows_per_keys: Estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], ...).
105+ :column index_size: Number of index pages.
106+ :column index_pages: Number of leaf pages.
107+
108
109 Example
110 =======
111
112-This example uses the same data to Bug #30423 of |MySQL|.
113-
114-``[innodb_stats_method = nulls_equal (default behavior of |InnoDB|)]`` ::
115+``[innodb_stats_method = nulls_equal (default behavior of InnoDB)]`` ::
116
117 mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id;
118 +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
119@@ -145,15 +162,15 @@
120 | table_name | index_name | fields | row_per_keys | index_size | leaf_pages |
121 +------------------------+-----------------+--------+--------------+------------+------------+
122 | test/sa_opportunities2 | GEN_CLUST_INDEX | 1 | 1 | 21 | 20 |
123- | test/sa_opportunities2 | sa_opp$org_id | 2 | 338, 1 | 11| 10 |
124+ | test/sa_opportunities2 | sa_opp$org_id | 2 | 338, 1 | 11| 10 |
125 | test/orgs2 | orgs$org_id | 1 | 1 | 1 | 1 |
126 | test/contacts2 | GEN_CLUST_INDEX | 1 | 1 | 97 | 80 |
127- | test/contacts2 | contacts$org_id | 2 | 516, 0 | 97 | 37 |
128+ | test/contacts2 | contacts$org_id | 2 | 516, 0 | 97 | 37 |
129 +------------------------+-----------------+--------+--------------+------------+------------+
130 5 rows in set (0.00 sec)
131
132 Other reading
133 =============
134
135- * `InnoDB Table/Index stats <http://www.mysqlperformanceblog.com/2010/03/20/|InnoDB|-tableindex-stats/>`_
136+ * `InnoDB Table/Index stats <http://www.mysqlperformanceblog.com/2010/03/20/InnoDB-tableindex-stats/>`_
137
138
139=== modified file 'doc/source/diagnostics/misc_info_schema_tables.rst'
140--- doc/source/diagnostics/misc_info_schema_tables.rst 2012-10-11 09:18:35 +0000
141+++ doc/source/diagnostics/misc_info_schema_tables.rst 2012-12-10 14:05:32 +0000
142@@ -2,50 +2,6 @@
143 Misc. INFORMATION_SCHEMA Tables
144 ===============================
145
146-This page lists the ``INFORMATION_SCHEMA`` tables added to standard |MySQL| by |Percona Server| that don``t exist elsewhere in the documentation.
147-
148-|InnoDB| transactions
149-=====================
150-
151-.. table:: INFORMATION_SCHEMA.INNODB_LOCK_WAITS
152-
153- :column REQUESTING_TRX_ID:
154- :column REQUESTED_LOCK_ID:
155- :column BLOCKING_TRX_ID:
156- :column BLOCKING_LOCK_ID:
157-
158-.. table:: INFORMATION_SCHEMA.INNODB_LOCKS
159-
160- :column LOCK_ID: Internal unique lock ID
161- :column LOCK_TRX_ID: ID of the transaction holding the lock
162- :column LOCK_MODE: Mode of the lock (shared, exclusive, …)
163- :column LOCK_TYPE: ``RECORD`` for a record lock and ``TABLE`` for a table lock
164- :column LOCK_TABLE: Name of the table holding the lock
165- :column LOCK_INDEX: If lock type is ``RECORD``, name of the index
166- :column LOCK_SPACE: If lock type is ``RECORD``, tablespace id of the locked record
167- :column LOCK_PAGE: If lock type is ``RECORD``, page number of the locked record
168- :column LOCK_REC: If lock type is ``RECORD``, heap number of the locked record
169- :column LOCK_DATA: If lock type is ``RECORD``, primary key of the locked record
170-
171-This table contains information on each lock that is requested by any transaction and on each lock that is held by any transaction. You can get details about the transactions involved by joining INNODB_LOCKS with INNODB_TRX on TRX_ID.
172-
173-This table was introduced by the |InnoDB| plugin and you can find the full documentation here.
174-
175-.. table:: INFORMATION_SCHEMA.INNODB_TRX
176-
177- :column TRX_ID: |InnoDB| internal unique transaction id
178- :column TRX_STATE: Execution state. Possible values are: ``Running``, ``Lock_wait``, ``Rolling_back``, ``Committing``
179- :column TRX_STARTED: Transaction start time
180- :column TRX_REQUESTED_LOCK_ID: Id of the lock the transaction has requested
181- :column TRX_WAIT_STARTED: Date and time when the transaction started waiting for a lock
182- :column TRX_WEIGHT: Weight of the transaction, ie approximate number of locked and modified rows
183- :column TRX_MYSQL_THREAD_ID: Thread id
184- :column TRX_QUERY: SQL query being executed
185-
186-This table holds information on every transaction running in the |InnoDB| kernel. Contrary to the output of SHOW |InnoDB| STATUS, it doesn``t show information on idle transactions.
187-
188-This table was introduced by the |InnoDB| plugin and you can find the full documentation here.
189-
190 Temporary tables
191 ================
192
193@@ -82,3 +38,126 @@
194 :column UPDATE_TIME: Date and time of the latest update of the temporary table
195
196 This table holds information on the temporary tables existing for the running connection.
197+
198+Buffer Pool Data Structure Tables
199+=================================
200+
201+The following tables provide various information about the contents of the |InnoDB| buffer pool.
202+
203+.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES
204+
205+ :column PAGE_TYPE: Type of the page. Possible values: index, undo_log, inode, ibuf_free_list, allocated, bitmap, sys, trx_sys, fsp_hdr, xdes, blob, zblob, zblob2, unknown
206+ :column SPACE_ID: tablespace ID
207+ :column PAGE_NO: page offset within its tablespace
208+ :column LRU_POSITION: page position in the LRU list
209+ :column FIX_COUNT: reference count of a page. It is incremented every time the page is accessed by InnoDB, it is 0 if and only if the page is not currently being accessed.
210+ :column FLUSH_TYPE: type of the last flush of the page (0:LRU 2:flush_list)
211+
212+Example: ::
213+
214+ mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES LIMIT 20;
215+ +-----------+----------+---------+--------------+-----------+------------+
216+ | page_type | space_id | page_no | lru_position | fix_count | flush_type |
217+ +-----------+----------+---------+--------------+-----------+------------+
218+ | allocated | 0 | 7 | 3 | 0 | 2 |
219+ | allocated | 0 | 1 | 4 | 0 | 0 |
220+ | allocated | 0 | 3 | 5 | 0 | 0 |
221+ | inode | 0 | 2 | 6 | 0 | 2 |
222+ | index | 0 | 4 | 7 | 0 | 2 |
223+ | index | 0 | 11 | 8 | 0 | 0 |
224+ | index | 0 | 12956 | 9 | 0 | 0 |
225+ | allocated | 0 | 5 | 10 | 0 | 2 |
226+ | allocated | 0 | 6 | 11 | 0 | 2 |
227+ | undo_log | 0 | 51 | 12 | 0 | 2 |
228+ | undo_log | 0 | 52 | 13 | 0 | 2 |
229+ | index | 0 | 8 | 14 | 0 | 0 |
230+ | index | 0 | 288 | 15 | 0 | 0 |
231+ | index | 0 | 290 | 16 | 0 | 2 |
232+ | index | 0 | 304 | 17 | 0 | 0 |
233+ | allocated | 0 | 0 | 18 | 0 | 2 |
234+ | index | 0 | 10 | 19 | 0 | 0 |
235+ | index | 0 | 12973 | 20 | 0 | 0 |
236+ | index | 0 | 9 | 21 | 0 | 2 |
237+ | index | 0 | 12 | 22 | 0 | 0 |
238+ +-----------+----------+---------+--------------+-----------+------------+
239+ 20 rows in set (0.81 sec)
240+
241+This table shows the characteristics of the allocated pages in buffer pool and current state of them.
242+
243+.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX
244+
245+ :column index_id: index name
246+ :column space_id: tablespace ID
247+ :column page_no: page offset within its tablespace
248+ :column n_recs: number of user records on page
249+ :column data_size: sum of the sizes of the records in page
250+ :column hashed: the block is in adaptive hash index (1) or not (0)
251+ :column access_time: time of the last access to this page.
252+ :column modified: modified since loaded (1) or not (0)
253+ :column dirty: modified since last flushed (1) or not (0)
254+ :column old: is old blocks in the LRU list (1) or not (0)
255+ :column lru_position: page position in the LRU list
256+ :column fix_count: reference count of a page. It is incremented every time the page is accessed by InnoDB, it is 0 if and only if the page is not currently being accessed.
257+ :column flush_type: type of the last flush of the page (0:LRU 2:flush_list)
258+
259+Example: ::
260+
261+ +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
262+ | index_id | space_id | page_no | n_recs | data_size | hashed | access_time | modified | dirty | old | lru_position | fix_count | flush_type |
263+ +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
264+ | 39 | 0 | 5787 | 468 | 14976 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 |
265+ | 40 | 0 | 5647 | 1300 | 15600 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 |
266+ | 39 | 0 | 5786 | 468 | 14976 | 1 | 2636182516 | 1 | 0 | 1 | 0 | 0 | 2 |
267+ | 40 | 0 | 6938 | 1300 | 15600 | 1 | 2636193968 | 1 | 0 | 1 | 0 | 0 | 2 |
268+ | 39 | 0 | 5785 | 468 | 14976 | 1 | 2636182514 | 1 | 0 | 1 | 0 | 0 | 2 |
269+ | 39 | 0 | 5784 | 468 | 14976 | 1 | 2636182512 | 1 | 0 | 1 | 0 | 0 | 2 |
270+ | 40 | 0 | 5646 | 1300 | 15600 | 1 | 2636182511 | 1 | 0 | 1 | 0 | 0 | 2 |
271+ | 39 | 0 | 7203 | 468 | 14976 | 1 | 2636193967 | 1 | 0 | 1 | 0 | 0 | 2 |
272+ | 39 | 0 | 5783 | 468 | 14976 | 1 | 2636182507 | 1 | 0 | 1 | 0 | 0 | 2 |
273+ | 39 | 0 | 5782 | 468 | 14976 | 1 | 2636182506 | 1 | 0 | 1 | 0 | 0 | 2 |
274+ +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
275+
276+This table shows information about the index pages located in the buffer pool.
277+
278+.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_BLOB
279+
280+ :column space_id: tablespace id
281+ :column page_no: page offset within its tablespace
282+ :column compressed: contains compressed data (1) or not (0)
283+ :column part_len: data length in the page
284+ :column next_page_no: page number of the next data
285+ :column lru_position: page position in the LRU list
286+ :column fix_count: reference count of a page. It is incremented every time the page is accessed by InnoDB, it is 0 if and only if the page is not currently being accessed.
287+ :column flush_type: type of the last flush of the page (0:LRU 2:flush_list)
288+
289+Example: ::
290+
291+ mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES_BLOB LIMIT 20;
292+ +----------+---------+------------+----------+--------------+--------------+-----------+------------+
293+ | space_id | page_no | compressed | part_len | next_page_no | lru_position | fix_count | flush_type |
294+ +----------+---------+------------+----------+--------------+--------------+-----------+------------+
295+ | 1748 | 111 | 0 | 10137 | 0 | 263 | 0 | 2 |
296+ | 1748 | 307 | 0 | 5210 | 0 | 1084 | 0 | 2 |
297+ | 1748 | 1329 | 0 | 6146 | 0 | 4244 | 0 | 2 |
298+ | 1748 | 1330 | 0 | 11475 | 0 | 4245 | 0 | 2 |
299+ | 1748 | 1345 | 0 | 5550 | 0 | 4247 | 0 | 2 |
300+ | 1748 | 1346 | 0 | 7597 | 0 | 4248 | 0 | 2 |
301+ | 1748 | 3105 | 0 | 6716 | 0 | 8919 | 0 | 2 |
302+ | 1748 | 3213 | 0 | 8170 | 0 | 9390 | 0 | 2 |
303+ | 1748 | 6142 | 0 | 5648 | 0 | 19638 | 0 | 2 |
304+ | 1748 | 7387 | 0 | 10634 | 0 | 24191 | 0 | 2 |
305+ | 1748 | 7426 | 0 | 5355 | 0 | 24194 | 0 | 2 |
306+ | 1748 | 7489 | 0 | 16330 | 7489 | 24196 | 0 | 2 |
307+ | 1748 | 7490 | 0 | 7126 | 0 | 24197 | 0 | 2 |
308+ | 1748 | 7657 | 0 | 13571 | 0 | 24681 | 0 | 2 |
309+ | 1748 | 7840 | 0 | 11208 | 0 | 25737 | 0 | 2 |
310+ | 1748 | 9599 | 0 | 11882 | 0 | 31989 | 0 | 2 |
311+ | 1748 | 11719 | 0 | 7367 | 0 | 40466 | 0 | 2 |
312+ | 1748 | 12051 | 0 | 11049 | 0 | 41441 | 0 | 2 |
313+ | 1748 | 12052 | 0 | 16330 | 12052 | 41442 | 0 | 2 |
314+ | 1748 | 12053 | 0 | 2674 | 0 | 41443 | 0 | 2 |
315+ +----------+---------+------------+----------+--------------+--------------+-----------+------------+
316+ 20 rows in set (0.05 sec)
317+
318+This table shows information from blob pages located in buffer pool.
319+
320
321=== modified file 'doc/source/diagnostics/response_time_distribution.rst'
322--- doc/source/diagnostics/response_time_distribution.rst 2011-10-07 00:55:06 +0000
323+++ doc/source/diagnostics/response_time_distribution.rst 2012-12-10 14:05:32 +0000
324@@ -6,7 +6,7 @@
325
326 The slow query log provides exact information about queries that take a long time to execute. However, sometimes there are a large number of queries that each take a very short amount of time to execute. This feature provides a tool for analyzing that information by counting and displaying the number of queries according to the the length of time they took to execute. The user can define time intervals that divide the range 0 to positive infinity into smaller intervals and then collect the number of commands whose execution times fall into each of those intervals.
327
328-Note that in a replication environment, the server will not take into account *any* queries executed by the slave's SQL thread (whether they are slow or not) for the time distribution unless the log_slow_slave_statements variable is set.
329+Note that in a replication environment, the server will not take into account *any* queries executed by the slave's SQL thread (whether they are slow or not) for the time distribution unless the :variable:`log_slow_slave_statements` variable is set.
330
331 The feature isn't implemented in all versions of the server. The variable :variable:`have_response_time_distribution` indicates whether or not it is implemented in the server you are running.
332
333@@ -197,10 +197,7 @@
334 Full functionality available.
335
336 * 5.1.53-12.4:
337- Introduced have_response_time_distribution.
338-
339- * 5.5.8-20.0:
340- Renamed variable :variable:`enable_query_response_time_stats` to :variable:`query_response_time_stats`.
341+ Introduced :variable:`have_response_time_distribution`.
342
343 System Variables
344 ================
345@@ -233,9 +230,9 @@
346
347 FLUSH QUERY_RESPONSE_TIME;
348
349-.. variable:: query_response_time_stats
350+.. variable:: enable_query_response_time_stats
351
352- :version 5.5.8-20.0: Introduced.
353+ :version 5.1.49-12.0: Introduced.
354 :cli: Yes
355 :conf: Yes
356 :scope: Global
357@@ -246,24 +243,11 @@
358
359 This variable enables and disables collection of query times if the feature is available in the server that's running. If the value of variable :variable:`have_response_time_distribution` is YES, then you can enable collection of query times by setting this variable to ON using ``SET GLOBAL``.
360
361- Prior to release 5.5.8-20.0, this variable was named :variable:`enable_query_response_time_stats`.
362-
363-
364 INFORMATION_SCHEMA Tables
365 =========================
366
367 .. table:: INFORMATION_SCHEMA.QUERY_RESPONSE_TIME
368
369- :column VARCHAR TIME:
370- :column INT(11) COUNT:
371- :column VARCHAR TOTAL:
372-
373-Implementation Details
374-======================
375-
376-Implementation details on this feature are provided here.
377-
378-Related Reading
379-===============
380-
381- * `Blueprint about Response Time Distribution <https://blueprints.launchpad.net/percona-server/+spec/response-time-distribution>`_
382+ :column VARCHAR TIME: Interval range in which the query occurred
383+ :column INT(11) COUNT: Number of queries with execution times that fell into that interval
384+ :column VARCHAR TOTAL: Total execution time of the queries
385
386=== modified file 'doc/source/diagnostics/user_stats.rst'
387--- doc/source/diagnostics/user_stats.rst 2011-10-07 00:55:06 +0000
388+++ doc/source/diagnostics/user_stats.rst 2012-12-10 14:05:32 +0000
389@@ -9,12 +9,6 @@
390 The functionality is disabled by default, and must be enabled by setting ``userstat`` to ``ON``. It works by keeping several hash tables in memory. To avoid contention over global mutexes, each connection has its own local statistics, which are occasionally merged into the global statistics, and the local statistics are then reset to 0.
391
392
393-Version Specific Information
394-============================
395-
396- * :rn:`5.5.10-20.1`:
397- Renamed variable :variable:`userstat_running` to :variable:`userstat`.
398-
399 Other Information
400 =================
401
402@@ -26,7 +20,7 @@
403
404 .. variable:: userstat_running
405
406- :version 5.5.10-20.1: Renamed to :variable:`userstat`
407+ :version 5.1.49-rel11.3: variable introduced
408 :cli: Yes
409 :conf: Yes
410 :scope: Global
411@@ -103,7 +97,7 @@
412
413 This table shows statistics on index usage. An older version of the feature contained a single column that had the ``TABLE_SCHEMA``, ``TABLE_NAME`` and ``INDEX_NAME`` columns concatenated together. The |Percona| version of the feature separates these into three columns. Users can see entries only for tables to which they have ``SELECT`` access.
414
415-This table makes it possible to do many things that were difficult or impossible previously. For example, you can use it to find unused indexes and generate DROP commands to remove them.
416+This table makes it possible to do many things that were difficult or impossible previously. For example, you can use it to find unused indexes and generate DROP commands to remove them. If the index has not been used it won't be in this table.
417
418 Example: ::
419
420@@ -140,27 +134,29 @@
421
422 .. table:: INFORMATION_SCHEMA.THREAD_STATISTICS
423
424- :column THREAD_ID: int(21)
425- :column TOTAL_CONNECTIONS: int(21)
426- :column CONCURRENT_CONNECTIONS: int(21)
427- :column CONNECTED_TIME: int(21)
428- :column BUSY_TIME: int(21)
429- :column CPU_TIME: int(21)
430- :column BYTES_RECEIVED: int(21)
431- :column BYTES_SENT: int(21)
432- :column BINLOG_BYTES_WRITTEN: int(21)
433- :column ROWS_FETCHED: int(21)
434- :column ROWS_UPDATED: int(21)
435- :column TABLE_ROWS_READ: int(21)
436- :column SELECT_COMMANDS: int(21)
437- :column UPDATE_COMMANDS: int(21)
438- :column OTHER_COMMANDS: int(21)
439- :column COMMIT_TRANSACTIONS: int(21)
440- :column ROLLBACK_TRANSACTIONS: int(21)
441- :column DENIED_CONNECTIONS: int(21)
442- :column LOST_CONNECTIONS: int(21)
443- :column ACCESS_DENIED: int(21)
444- :column EMPTY_QUERIES: int(21)
445+ :column THREAD_ID: ID of the thread.
446+ :column TOTAL_CONNECTIONS: The number of connections created from this thread.
447+ :column CONCURRENT_CONNECTIONS: The number of concurrent connections from this thread.
448+ :column CONNECTED_TIME: The cumulative number of seconds elapsed while there were connections from this thread.
449+ :column BUSY_TIME: The cumulative number of seconds there was activity from this thread.
450+ :column CPU_TIME: The cumulative CPU time elapsed while servicing this thread.
451+ :column BYTES_RECEIVED: The number of bytes received from this thread.
452+ :column BYTES_SENT: The number of bytes sent to this thread.
453+ :column BINLOG_BYTES_WRITTEN: The number of bytes written to the binary log from this thread.
454+ :column ROWS_FETCHED: The number of rows fetched by this thread.
455+ :column ROWS_UPDATED: The number of rows updated by this thread.
456+ :column TABLE_ROWS_READ: The number of rows read from tables by this tread.
457+ :column SELECT_COMMANDS: The number of ``SELECT`` commands executed from this thread.
458+ :column UPDATE_COMMANDS: The number of ``UPDATE`` commands executed from this thread.
459+ :column OTHER_COMMANDS: The number of other commands executed from this thread.
460+ :column COMMIT_TRANSACTIONS: The number of ``COMMIT`` commands issued by this thread.
461+ :column ROLLBACK_TRANSACTIONS: The number of ``ROLLBACK`` commands issued by this thread.
462+ :column DENIED_CONNECTIONS: The number of connections denied to this thread.
463+ :column LOST_CONNECTIONS: The number of thread connections that were terminated uncleanly.
464+ :column ACCESS_DENIED: The number of times this thread issued commands that were denied.
465+ :column EMPTY_QUERIES: The number of times this thread sent empty queries to the server.
466+
467+In order for this table to be populated with statistics, additional variable :variable:`thread_statistics` should be set to ``ON``.
468
469 .. table:: INFORMATION_SCHEMA.USER_STATISTICS
470
471
472=== modified file 'doc/source/index.rst'
473--- doc/source/index.rst 2012-10-31 15:51:11 +0000
474+++ doc/source/index.rst 2012-12-10 14:05:32 +0000
475@@ -134,7 +134,6 @@
476 :maxdepth: 1
477 :glob:
478
479- diagnostics/index_info_schema_tables
480 diagnostics/innodb_stats
481 diagnostics/user_stats
482 diagnostics/slow_extended
483@@ -165,6 +164,7 @@
484 development
485 trademark-policy
486 upstream-bug-fixes
487+ index_info_schema_tables
488 faq
489 compatibility
490 release-notes/release-notes_index
491
492=== renamed file 'doc/source/diagnostics/index_info_schema_tables.rst' => 'doc/source/index_info_schema_tables.rst'
493--- doc/source/diagnostics/index_info_schema_tables.rst 2012-10-11 09:18:35 +0000
494+++ doc/source/index_info_schema_tables.rst 2012-12-10 14:05:32 +0000
495@@ -6,26 +6,40 @@
496
497 This is a list of the ``INFORMATION_SCHEMA TABLES`` that exist in |Percona Server| with |XtraDB|. The entry for each table points to the page in the documentation where it's described.
498
499+ * :table:`CLIENT_STATISTICS`
500+
501+ * :table:`INDEX_STATISTICS`
502+
503 * :table:`GLOBAL_TEMPORARY_TABLES`
504
505+ * :table:`QUERY_RESPONSE_TIME`
506+
507+ * :table:`TEMPORARY_TABLES`
508+
509+ * :table:`TABLE_STATISTICS`
510+
511+ * :table:`THREAD_STATISTICS`
512+
513+ * :table:`USER_STATISTICS`
514+
515+ * :table:`INNODB_BUFFER_POOL_PAGES`
516+
517+ * :table:`INNODB_BUFFER_POOL_PAGES_BLOB`
518+
519+ * :table:`INNODB_BUFFER_POOL_PAGES_INDEX`
520+
521+ * :table:`INNODB_RSEG`
522+
523+ * :table:`INNODB_CHANGED_PAGES`
524+
525 * :table:`INNODB_INDEX_STATS`
526
527- * :table:`INNODB_LOCK_WAITS`
528-
529- * :table:`INNODB_LOCKS`
530-
531- * :table:`INNODB_RSEG`
532-
533 * :table:`INNODB_TABLE_STATS`
534
535- * :table:`INNODB_TRX`
536-
537- * :table:`INNODB_CHANGED_PAGES`
538-
539- * :table:`PROCESSLIST`
540-
541- * :table:`QUERY_RESPONSE_TIME`
542-
543- * :table:`TEMPORARY_TABLES`
544-
545-
546+ * :table:`INNODB_SYS_TABLES`
547+
548+ * :table:`INNODB_SYS_STATS`
549+
550+ * :table:`INNODB_SYS_INDEXES`
551+
552+ * :table:`XTRADB_ADMIN_COMMAND`
553
554=== modified file 'doc/source/management/innodb_lru_dump_restore.rst'
555--- doc/source/management/innodb_lru_dump_restore.rst 2012-06-13 10:52:23 +0000
556+++ doc/source/management/innodb_lru_dump_restore.rst 2012-12-10 14:05:32 +0000
557@@ -132,6 +132,15 @@
558
559 When this variable is set to ON XtraDB waits until the restore of the dump is completed before reporting successful startup to the server.
560
561+``INFORMATION_SCHEMA`` Tables
562+=============================
563+
564+This feature provides the following table:
565+
566+.. table:: INFORMATION_SCHEMA.XTRADB_ADMIN_COMMAND
567+
568+ :column result_message: result message of the ``XTRADB_ADMIN_COMMAND``
569+
570 Other reading
571 =============
572
573
574=== modified file 'doc/source/performance/innodb_opt_lru_count.rst'
575--- doc/source/performance/innodb_opt_lru_count.rst 2011-10-07 00:55:06 +0000
576+++ doc/source/performance/innodb_opt_lru_count.rst 2012-12-10 14:05:32 +0000
577@@ -5,3 +5,4 @@
578 =====================================
579
580 We removed ``buffer_pool`` mutex operations on counting blocks on LRU list where it is safe to delete. As drawback we may have some inaccurate information of LRU list, but it does not affect storage engine operations. As result we have decreased contention on ``buffer_pool`` mutex.
581+

Subscribers

People subscribed via source and target branches