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

Proposed by Hrvoje Matijakovic
Status: Merged
Approved by: Alexey Kopytov
Approved revision: 360
Merged at revision: 360
Proposed branch: lp:~hrvojem/percona-server/bug1056603-5.5
Merge into: lp:percona-server/5.5
Diff against target: 584 lines (+299/-115)
7 files modified
doc/source/diagnostics/innodb_stats.rst (+93/-16)
doc/source/diagnostics/misc_info_schema_tables.rst (+123/-45)
doc/source/diagnostics/response_time_distribution.rst (+5/-14)
doc/source/diagnostics/user_stats.rst (+25/-23)
doc/source/index.rst (+1/-1)
doc/source/index_info_schema_tables.rst (+42/-14)
doc/source/management/innodb_lru_dump_restore.rst (+10/-2)
To merge this branch: bzr merge lp:~hrvojem/percona-server/bug1056603-5.5
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) Approve
Review via email: mp+134998@code.launchpad.net
To post a comment you must log in.
Alexey Kopytov (akopytov) wrote :

Same comments as in the 5.1 MP plus:

- new docs for INNODB_BUFFER_POOL_PAGES, INNODB_BUFFER_POOL_PAGES_INDEX
  and INNODB_BUFFER_POOL_PAGES_BLOB. I wonder if they deserve a separate
  section, like there are sections for "InnoDB Undo Logs", "Temporary
  Tables", etc.?

- s/These tables show the buffer pool data structure/The following
  tables provide various information about the contents of the InnoDB
  buffer pool/

- s/Possible values,/Possible values:/

- SPACE_ID is the tablespace ID, rather than the table ID

- PAGE_NO is the page offset within its tablespace

- LRU_POSITION is the page position in the LRU list

- FIX_COUNT is the reference count of a page. It is incremented every
  time the page is accessed by InnoDB, and is 0 if and only if the page
  is not currently being accessed

- ACCESS_TIME (in INNODB_BUFFER_POOL_PAGES_INDEX) is not a boolean value
  as the description suggests. It is the time of the last access to that
  page.

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, rather than just for INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES. Just for consistency.

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-09-18 05:04:43 +0000
3+++ doc/source/diagnostics/innodb_stats.rst 2012-12-10 15:33:26 +0000
4@@ -6,8 +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@@ -30,18 +28,16 @@
14 :default: ``nulls_equal``
15 :allowed: ``nulls_equal``, ``nulls_unequal``, ``nulls_ignored``
16
17-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.
18-
19-(reference: `MyISAM Index Statistics Collection <http://dev.mysql.com/doc/refman/5.1/en/myisam-index-statistics.html>`_)
20-
21-**Note:** Beginning in release 5.1.56-12.7, a variable with the same and functionality was implemented in the upstream |InnoDB|.
22+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.
23+
24+(reference: `MyISAM Index Statistics Collection <http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html>`_)
25
26 .. variable:: innodb_stats_auto_update
27
28 :type: BOOLEAN
29 :default: 1
30
31-|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”.
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
34
35 .. variable:: innodb_stats_update_need_lock
36@@ -63,7 +59,90 @@
37 INFORMATION_SCHEMA Tables
38 =========================
39
40-Two new tables were introduced by this feature.
41+.. table:: INFORMATION_SCHEMA.INNODB_SYS_STATS
42+
43+ Shows statistics of table indexes.
44+
45+ :column INDEX_ID: Index ID
46+ :column KEY_COLS: Number of key columns
47+ :column DIFF_VALS: Number of Different Values
48+ :column NON_NULL_VALS: Number of Non ``NULL`` Values
49+
50+.. table:: INFORMATION_SCHEMA.INNODB_SYS_TABLES
51+
52+ Shows the information about |InnoDB| tables
53+
54+ :column TABLE_ID: Table ID
55+ :column SCHEMA: Database (schema) name
56+ :column NAME: Table name
57+ :column FLAG: Contains `0` if it is a InnoDB system table or `1` it is a user table
58+ :column N_COLS: Number of columns in the table
59+ :column SPACE: Tablespace ID
60+
61+.. table:: INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
62+
63+ Shows the information about the performance statistics of |InnoDB| tables.
64+
65+ :column TABLE_ID: Table ID
66+ :column SCHEMA: Database (schema) Name
67+ :column NAME: Table Name
68+ :column STATS_INITIALIZED: Contains ``Initialized`` value if the statistics are collected or ``Uninitialized`` if they are not collected.
69+ :column NUM_ROWS: Estimated number of rows in the table.
70+ :column CLUST_INDEX_SIZE: Number of pages on disk that store the clustered index.
71+ :column OTHER_INDEX_SIZE: Number of pages on disk that store all secondary indexes.
72+ :column MODIFIED_COUNTER: Number of rows modified by DML operations.
73+ :column AUTOINC:
74+ :column MYSQL_HANDLES_OPENED:
75+
76+.. table:: INFORMATION_SCHEMA.INNODB_SYS_INDEXES
77+
78+ Shows the information about |InnoDB| indexes
79+
80+ :column INDEX_ID: Index ID
81+ :column NAME: Index Name
82+ :column TABLE_ID: Table ID
83+ :column TYPE: Numeric identifier signifying the index type
84+ :column N_FIELDS: Number of columns in the index
85+ :column PAGE_NO: Page offset within its tablespace
86+ :column SPACE: Tablespace ID
87+
88+.. table:: INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
89+
90+ Shows the information about the |InnoDB| table columns
91+
92+ :column TABLE_ID: Table ID
93+ :column NAME: Column Name
94+ :column POS: Position of the column inside the table.
95+ :column MTYPE: Numeric identifier for the column type.
96+ :column PRTYPE: Binary value with bits representing data type, character set code and nullability.
97+ :column LEN: Column length.
98+
99+.. table:: INFORMATION_SCHEMA.INNODB_SYS_FIELDS
100+
101+ Shows the information about the |InnoDB| index key fields.
102+
103+ :column INDEX_ID: Index ID
104+ :column NAME: Index Name
105+ :column POS: Position of the field inside the index.
106+
107+.. table:: INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
108+
109+ Shows the information about the |InnoDB| foreign keys.
110+
111+ :column ID: Foreign Key ID
112+ :column FOR_NAME: Database/Table which contains the Foreign Key
113+ :column FOR_REF: Database/Table being referenced by the Foreign Key
114+ :column N_COLS: Number of columns in the foreign key.
115+ :column TYPE: Type of foreign key, represented by the bit flags.
116+
117+.. table:: INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
118+
119+ Shows the information about the columns of the |InnoDB| foreign keys.
120+
121+ :column ID: Foreign Key ID
122+ :column FOR_COL_NAME: Foreign Key Column Name
123+ :column FOR_REF: Referenced Column Name
124+ :column POS: Position of the field inside the index.
125
126 .. table:: INFORMATION_SCHEMA.INNODB_TABLE_STATS
127
128@@ -76,7 +155,7 @@
129 :column other_size: Other index (non primary key) size in number of pages.
130 :column modified: Internal counter to judge whether statistics recalculation should be done.
131
132-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.
133+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.
134
135 .. table:: INFORMATION_SCHEMA.INNODB_INDEX_STATS
136
137@@ -101,9 +180,7 @@
138 Example
139 =======
140
141-This example uses the same data to Bug #30423 of |MySQL|.
142-
143-``[innodb_stats_method = nulls_equal (default behavior of |InnoDB|)]`` ::
144+``[innodb_stats_method = nulls_equal (default behavior of InnoDB)]`` ::
145
146 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;
147 +----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
148@@ -143,15 +220,15 @@
149 | table_name | index_name | fields | row_per_keys | index_size | leaf_pages |
150 +------------------------+-----------------+--------+--------------+------------+------------+
151 | test/sa_opportunities2 | GEN_CLUST_INDEX | 1 | 1 | 21 | 20 |
152- | test/sa_opportunities2 | sa_opp$org_id | 2 | 338, 1 | 11| 10 |
153+ | test/sa_opportunities2 | sa_opp$org_id | 2 | 338, 1 | 11| 10 |
154 | test/orgs2 | orgs$org_id | 1 | 1 | 1 | 1 |
155 | test/contacts2 | GEN_CLUST_INDEX | 1 | 1 | 97 | 80 |
156- | test/contacts2 | contacts$org_id | 2 | 516, 0 | 97 | 37 |
157+ | test/contacts2 | contacts$org_id | 2 | 516, 0 | 97 | 37 |
158 +------------------------+-----------------+--------+--------------+------------+------------+
159 5 rows in set (0.00 sec)
160
161 Other reading
162 =============
163
164- * `InnoDB Table/Index stats <http://www.mysqlperformanceblog.com/2010/03/20/|InnoDB|-tableindex-stats/>`_
165+ * `InnoDB Table/Index stats <http://www.mysqlperformanceblog.com/2010/03/20/InnoDB-tableindex-stats/>`_
166
167
168=== modified file 'doc/source/diagnostics/misc_info_schema_tables.rst'
169--- doc/source/diagnostics/misc_info_schema_tables.rst 2012-10-19 12:06:24 +0000
170+++ doc/source/diagnostics/misc_info_schema_tables.rst 2012-12-10 15:33:26 +0000
171@@ -4,54 +4,10 @@
172 Misc. INFORMATION_SCHEMA Tables
173 =================================
174
175-This page lists the ``INFORMATION_SCHEMA`` tables added to standard |MySQL| by |Percona Server| that don't exist elsewhere in the documentation.
176-
177-|InnoDB| transactions
178-=====================
179-
180-.. table:: INFORMATION_SCHEMA.INNODB_LOCK_WAITS
181-
182- :column REQUESTING_TRX_ID:
183- :column REQUESTED_LOCK_ID:
184- :column BLOCKING_TRX_ID:
185- :column BLOCKING_LOCK_ID:
186-
187-.. table:: INFORMATION_SCHEMA.INNODB_LOCKS
188-
189- :column LOCK_ID: Internal unique lock ID
190- :column LOCK_TRX_ID: ID of the transaction holding the lock
191- :column LOCK_MODE: Mode of the lock (shared, exclusive, …)
192- :column LOCK_TYPE: ``RECORD`` for a record lock and ``TABLE`` for a table lock
193- :column LOCK_TABLE: Name of the table holding the lock
194- :column LOCK_INDEX: If lock type is ``RECORD``, name of the index
195- :column LOCK_SPACE: If lock type is ``RECORD``, tablespace id of the locked record
196- :column LOCK_PAGE: If lock type is ``RECORD``, page number of the locked record
197- :column LOCK_REC: If lock type is ``RECORD``, heap number of the locked record
198- :column LOCK_DATA: If lock type is ``RECORD``, primary key of the locked record
199-
200-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.
201-
202-This table was introduced by the |InnoDB| plugin and you can find the full documentation here.
203-
204-.. table:: INFORMATION_SCHEMA.INNODB_TRX
205-
206- :column TRX_ID: |InnoDB| internal unique transaction id
207- :column TRX_STATE: Execution state. Possible values are: ``Running``, ``Lock_wait``, ``Rolling_back``, ``Committing``
208- :column TRX_STARTED: Transaction start time
209- :column TRX_REQUESTED_LOCK_ID: Id of the lock the transaction has requested
210- :column TRX_WAIT_STARTED: Date and time when the transaction started waiting for a lock
211- :column TRX_WEIGHT: Weight of the transaction, ie approximate number of locked and modified rows
212- :column TRX_MYSQL_THREAD_ID: Thread id
213- :column TRX_QUERY: SQL query being executed
214-
215-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.
216-
217-This table was introduced by the |InnoDB| plugin and you can find the full documentation here.
218-
219 Temporary tables
220 ================
221
222- Only the temporary tables that were explicitly created with `CREATE TEMPORARY TABLE` or `ALTER TABLE` are shown, and not the ones created to process complex queries.
223+ Only the temporary tables that were explicitly created with `CREATE TEMPORARY TABLE` or `ALTER TABLE` are shown, and not the ones created to process complex queries.
224
225 .. table:: INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLES
226
227@@ -85,6 +41,128 @@
228
229 This table holds information on the temporary tables existing for the running connection.
230
231+Buffer Pool Data Structure Tables
232+=================================
233+
234+The following tables provide various information about the contents of the |InnoDB| buffer pool.
235+
236+.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES
237+
238+ :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
239+ :column SPACE_ID: Tablespace ID
240+ :column PAGE_NO: Page offset within its tablespace
241+ :column LRU_POSITION: Page position in the LRU list
242+ :column FIX_COUNT: reference count of a page. It is incremented every time the page is accessed by |InnoDB|, and is 0 if and only if the page is not currently being accessed
243+ :column FLUSH_TYPE: type of the last flush of the page (0:LRU 2:flush_list)
244+
245+Example: ::
246+
247+ mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES LIMIT 20;
248+ +-----------+----------+---------+--------------+-----------+------------+
249+ | page_type | space_id | page_no | lru_position | fix_count | flush_type |
250+ +-----------+----------+---------+--------------+-----------+------------+
251+ | allocated | 0 | 7 | 3 | 0 | 2 |
252+ | allocated | 0 | 1 | 4 | 0 | 0 |
253+ | allocated | 0 | 3 | 5 | 0 | 0 |
254+ | inode | 0 | 2 | 6 | 0 | 2 |
255+ | index | 0 | 4 | 7 | 0 | 2 |
256+ | index | 0 | 11 | 8 | 0 | 0 |
257+ | index | 0 | 12956 | 9 | 0 | 0 |
258+ | allocated | 0 | 5 | 10 | 0 | 2 |
259+ | allocated | 0 | 6 | 11 | 0 | 2 |
260+ | undo_log | 0 | 51 | 12 | 0 | 2 |
261+ | undo_log | 0 | 52 | 13 | 0 | 2 |
262+ | index | 0 | 8 | 14 | 0 | 0 |
263+ | index | 0 | 288 | 15 | 0 | 0 |
264+ | index | 0 | 290 | 16 | 0 | 2 |
265+ | index | 0 | 304 | 17 | 0 | 0 |
266+ | allocated | 0 | 0 | 18 | 0 | 2 |
267+ | index | 0 | 10 | 19 | 0 | 0 |
268+ | index | 0 | 12973 | 20 | 0 | 0 |
269+ | index | 0 | 9 | 21 | 0 | 2 |
270+ | index | 0 | 12 | 22 | 0 | 0 |
271+ +-----------+----------+---------+--------------+-----------+------------+
272+ 20 rows in set (0.81 sec)
273+
274+This table shows the characteristics of the allocated pages in buffer pool and current state of them.
275+
276+.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX
277+
278+ :column index_id: index name
279+ :column space_id: Tablespace ID
280+ :column page_no: Page offset within its tablespace
281+ :column n_recs: number of user records on page
282+ :column data_size: sum of the sizes of the records in page
283+ :column hashed: the block is in adaptive hash index (1) or not (0)
284+ :column access_time: time of the last access to that page
285+ :column modified: modified since loaded (1) or not (0)
286+ :column dirty: modified since last flushed (1) or not (0)
287+ :column old: is old blocks in the LRU list (1) or not (0)
288+ :column lru_position: page position in the LRU list
289+ :column fix_count: reference count of a page. It is incremented every time the page is accessed by |InnoDB|, and is 0 if and only if the page is not currently being accessed
290+ :column flush_type: type of the last flush of the page (0:LRU 2:flush_list)
291+
292+Example: ::
293+
294+ +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
295+ | index_id | space_id | page_no | n_recs | data_size | hashed | access_time | modified | dirty | old | lru_position | fix_count | flush_type |
296+ +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
297+ | 39 | 0 | 5787 | 468 | 14976 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 |
298+ | 40 | 0 | 5647 | 1300 | 15600 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 |
299+ | 39 | 0 | 5786 | 468 | 14976 | 1 | 2636182516 | 1 | 0 | 1 | 0 | 0 | 2 |
300+ | 40 | 0 | 6938 | 1300 | 15600 | 1 | 2636193968 | 1 | 0 | 1 | 0 | 0 | 2 |
301+ | 39 | 0 | 5785 | 468 | 14976 | 1 | 2636182514 | 1 | 0 | 1 | 0 | 0 | 2 |
302+ | 39 | 0 | 5784 | 468 | 14976 | 1 | 2636182512 | 1 | 0 | 1 | 0 | 0 | 2 |
303+ | 40 | 0 | 5646 | 1300 | 15600 | 1 | 2636182511 | 1 | 0 | 1 | 0 | 0 | 2 |
304+ | 39 | 0 | 7203 | 468 | 14976 | 1 | 2636193967 | 1 | 0 | 1 | 0 | 0 | 2 |
305+ | 39 | 0 | 5783 | 468 | 14976 | 1 | 2636182507 | 1 | 0 | 1 | 0 | 0 | 2 |
306+ | 39 | 0 | 5782 | 468 | 14976 | 1 | 2636182506 | 1 | 0 | 1 | 0 | 0 | 2 |
307+ +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
308+
309+This table shows information about the index pages located in the buffer pool.
310+
311+.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_BLOB
312+
313+ :column space_id: tablespace ID
314+ :column page_no: page offset within its tablespace
315+ :column compressed: contains compressed data (1) or not (0)
316+ :column part_len: data length in the page
317+ :column next_page_no: page number of the next data
318+ :column lru_position: page position in the LRU list
319+ :column fix_count: reference count of a page. It is incremented every time the page is accessed by InnoDB, and is 0 if and only if the page is not currently being accessed
320+ :column flush_type: type of the last flush of the page (0:LRU 2:flush_list)
321+
322+Example: ::
323+
324+ mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES_BLOB LIMIT 20;
325+ +----------+---------+------------+----------+--------------+--------------+-----------+------------+
326+ | space_id | page_no | compressed | part_len | next_page_no | lru_position | fix_count | flush_type |
327+ +----------+---------+------------+----------+--------------+--------------+-----------+------------+
328+ | 1748 | 111 | 0 | 10137 | 0 | 263 | 0 | 2 |
329+ | 1748 | 307 | 0 | 5210 | 0 | 1084 | 0 | 2 |
330+ | 1748 | 1329 | 0 | 6146 | 0 | 4244 | 0 | 2 |
331+ | 1748 | 1330 | 0 | 11475 | 0 | 4245 | 0 | 2 |
332+ | 1748 | 1345 | 0 | 5550 | 0 | 4247 | 0 | 2 |
333+ | 1748 | 1346 | 0 | 7597 | 0 | 4248 | 0 | 2 |
334+ | 1748 | 3105 | 0 | 6716 | 0 | 8919 | 0 | 2 |
335+ | 1748 | 3213 | 0 | 8170 | 0 | 9390 | 0 | 2 |
336+ | 1748 | 6142 | 0 | 5648 | 0 | 19638 | 0 | 2 |
337+ | 1748 | 7387 | 0 | 10634 | 0 | 24191 | 0 | 2 |
338+ | 1748 | 7426 | 0 | 5355 | 0 | 24194 | 0 | 2 |
339+ | 1748 | 7489 | 0 | 16330 | 7489 | 24196 | 0 | 2 |
340+ | 1748 | 7490 | 0 | 7126 | 0 | 24197 | 0 | 2 |
341+ | 1748 | 7657 | 0 | 13571 | 0 | 24681 | 0 | 2 |
342+ | 1748 | 7840 | 0 | 11208 | 0 | 25737 | 0 | 2 |
343+ | 1748 | 9599 | 0 | 11882 | 0 | 31989 | 0 | 2 |
344+ | 1748 | 11719 | 0 | 7367 | 0 | 40466 | 0 | 2 |
345+ | 1748 | 12051 | 0 | 11049 | 0 | 41441 | 0 | 2 |
346+ | 1748 | 12052 | 0 | 16330 | 12052 | 41442 | 0 | 2 |
347+ | 1748 | 12053 | 0 | 2674 | 0 | 41443 | 0 | 2 |
348+ +----------+---------+------------+----------+--------------+--------------+-----------+------------+
349+ 20 rows in set (0.05 sec)
350+
351+This table shows information from blob pages located in buffer pool.
352+
353 InnoDB Undo Logs
354 ================
355
356
357=== modified file 'doc/source/diagnostics/response_time_distribution.rst'
358--- doc/source/diagnostics/response_time_distribution.rst 2012-09-18 05:04:43 +0000
359+++ doc/source/diagnostics/response_time_distribution.rst 2012-12-10 15:33:26 +0000
360@@ -6,7 +6,7 @@
361
362 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.
363
364-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.
365+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.
366
367 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.
368
369@@ -251,16 +251,7 @@
370
371 .. table:: INFORMATION_SCHEMA.QUERY_RESPONSE_TIME
372
373- :column VARCHAR TIME:
374- :column INT(11) COUNT:
375- :column VARCHAR TOTAL:
376-
377-Implementation Details
378-======================
379-
380-Implementation details on this feature are provided here.
381-
382-Related Reading
383-===============
384-
385- * `Blueprint about Response Time Distribution <https://blueprints.launchpad.net/percona-server/+spec/response-time-distribution>`_
386+ :column VARCHAR TIME: Interval range in which the query occurred
387+ :column INT(11) COUNT: Number of queries with execution times that fell into that interval
388+ :column VARCHAR TOTAL: Total execution time of the queries
389+
390
391=== modified file 'doc/source/diagnostics/user_stats.rst'
392--- doc/source/diagnostics/user_stats.rst 2012-09-18 05:04:43 +0000
393+++ doc/source/diagnostics/user_stats.rst 2012-12-10 15:33:26 +0000
394@@ -108,7 +108,7 @@
395
396 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.
397
398-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.
399+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.
400
401 Example: ::
402
403@@ -145,28 +145,30 @@
404
405 .. table:: INFORMATION_SCHEMA.THREAD_STATISTICS
406
407- :column THREAD_ID: int(21)
408- :column TOTAL_CONNECTIONS: int(21)
409- :column CONCURRENT_CONNECTIONS: int(21)
410- :column CONNECTED_TIME: int(21)
411- :column BUSY_TIME: int(21)
412- :column CPU_TIME: int(21)
413- :column BYTES_RECEIVED: int(21)
414- :column BYTES_SENT: int(21)
415- :column BINLOG_BYTES_WRITTEN: int(21)
416- :column ROWS_FETCHED: int(21)
417- :column ROWS_UPDATED: int(21)
418- :column TABLE_ROWS_READ: int(21)
419- :column SELECT_COMMANDS: int(21)
420- :column UPDATE_COMMANDS: int(21)
421- :column OTHER_COMMANDS: int(21)
422- :column COMMIT_TRANSACTIONS: int(21)
423- :column ROLLBACK_TRANSACTIONS: int(21)
424- :column DENIED_CONNECTIONS: int(21)
425- :column LOST_CONNECTIONS: int(21)
426- :column ACCESS_DENIED: int(21)
427- :column EMPTY_QUERIES: int(21)
428- :column TOTAL_SSL_CONNECTIONS: int(21)
429+ :column THREAD_ID: Thread ID
430+ :column TOTAL_CONNECTIONS: The number of connections created from this thread.
431+ :column CONCURRENT_CONNECTIONS: The number of concurrent connections from this thread.
432+ :column CONNECTED_TIME: The cumulative number of seconds elapsed while there were connections from this thread.
433+ :column BUSY_TIME: The cumulative number of seconds there was activity from this thread.
434+ :column CPU_TIME: The cumulative CPU time elapsed while servicing this thread.
435+ :column BYTES_RECEIVED: The number of bytes received from this thread.
436+ :column BYTES_SENT: The number of bytes sent to this thread.
437+ :column BINLOG_BYTES_WRITTEN: The number of bytes written to the binary log from this thread.
438+ :column ROWS_FETCHED: The number of rows fetched by this thread.
439+ :column ROWS_UPDATED: The number of rows updated by this thread.
440+ :column TABLE_ROWS_READ: The number of rows read from tables by this tread.
441+ :column SELECT_COMMANDS: The number of ``SELECT`` commands executed from this thread.
442+ :column UPDATE_COMMANDS: The number of ``UPDATE`` commands executed from this thread.
443+ :column OTHER_COMMANDS: The number of other commands executed from this thread.
444+ :column COMMIT_TRANSACTIONS: The number of ``COMMIT`` commands issued by this thread.
445+ :column ROLLBACK_TRANSACTIONS: The number of ``ROLLBACK`` commands issued by this thread.
446+ :column DENIED_CONNECTIONS: The number of connections denied to this thread.
447+ :column LOST_CONNECTIONS: The number of thread connections that were terminated uncleanly.
448+ :column ACCESS_DENIED: The number of times this thread issued commands that were denied.
449+ :column EMPTY_QUERIES: The number of times this thread sent empty queries to the server.
450+ :column TOTAL_SSL_CONNECTIONS: The number of thread connections that used SSL.
451+
452+In order for this table to be populated with statistics, additional variable :variable:`thread_statistics` should be set to ``ON``.
453
454 .. table:: INFORMATION_SCHEMA.USER_STATISTICS
455
456
457=== modified file 'doc/source/index.rst'
458--- doc/source/index.rst 2012-10-31 16:45:42 +0000
459+++ doc/source/index.rst 2012-12-10 15:33:26 +0000
460@@ -136,7 +136,6 @@
461 :maxdepth: 1
462 :glob:
463
464- diagnostics/index_info_schema_tables
465 diagnostics/innodb_stats
466 diagnostics/user_stats
467 diagnostics/slow_extended_55
468@@ -169,6 +168,7 @@
469 development
470 trademark-policy
471 upstream-bug-fixes
472+ index_info_schema_tables
473 faq
474 compatibility
475 release-notes/release-notes_index
476
477=== renamed file 'doc/source/diagnostics/index_info_schema_tables.rst' => 'doc/source/index_info_schema_tables.rst'
478--- doc/source/diagnostics/index_info_schema_tables.rst 2012-10-11 09:53:58 +0000
479+++ doc/source/index_info_schema_tables.rst 2012-12-10 15:33:26 +0000
480@@ -6,25 +6,53 @@
481
482 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.
483
484+ * :table:`CLIENT_STATISTICS`
485+
486+ * :table:`INDEX_STATISTICS`
487+
488 * :table:`GLOBAL_TEMPORARY_TABLES`
489
490- * :table:`INNODB_INDEX_STATS`
491-
492- * :table:`INNODB_LOCK_WAITS`
493-
494- * :table:`INNODB_LOCKS`
495-
496- * :table:`INNODB_RSEG`
497-
498- * :table:`INNODB_TABLE_STATS`
499-
500- * :table:`INNODB_TRX`
501-
502- * :table:`PROCESSLIST`
503-
504 * :table:`QUERY_RESPONSE_TIME`
505
506+ * :table:`TABLE_STATISTICS`
507+
508 * :table:`TEMPORARY_TABLES`
509
510+ * :table:`THREAD_STATISTICS`
511+
512+ * :table:`USER_STATISTICS`
513+
514+ * :table:`INNODB_RSEG`
515+
516 * :table:`INNODB_UNDO_LOGS`
517
518+ * :table:`INNODB_SYS_TABLESTATS`
519+
520+ * :table:`INNODB_INDEX_STATS`
521+
522+ * :table:`INNODB_CHANGED_PAGES`
523+
524+ * :table:`INNODB_BUFFER_POOL_PAGES`
525+
526+ * :table:`INNODB_BUFFER_POOL_PAGES_BLOB`
527+
528+ * :table:`INNODB_BUFFER_POOL_PAGES_INDEX`
529+
530+ * :table:`INNODB_SYS_TABLES`
531+
532+ * :table:`INNODB_SYS_FIELDS`
533+
534+ * :table:`INNODB_SYS_COLUMNS`
535+
536+ * :table:`INNODB_SYS_STATS`
537+
538+ * :table:`INNODB_SYS_FOREIGN`
539+
540+ * :table:`INNODB_SYS_INDEXES`
541+
542+ * :table:`XTRADB_ADMIN_COMMAND`
543+
544+ * :table:`INNODB_TABLE_STATS`
545+
546+ * :table:`INNODB_SYS_FOREIGN_COLS`
547+
548
549=== modified file 'doc/source/management/innodb_lru_dump_restore.rst'
550--- doc/source/management/innodb_lru_dump_restore.rst 2012-09-04 23:24:39 +0000
551+++ doc/source/management/innodb_lru_dump_restore.rst 2012-12-10 15:33:26 +0000
552@@ -80,7 +80,7 @@
553 Status Information
554 ==================
555
556-Status information about the dump and restore is written to the server``s error file: ::
557+Status information about the dump and restore is written to the server's error file: ::
558
559 ....
560 091217 11:49:16 InnoDB: administration command ``XTRA_LRU_DUMP`` was detected.
561@@ -145,7 +145,7 @@
562 :default: OFF
563 :range: ON/OFF
564
565-When this variable is set to ON XtraDB waits until the restore of the dump is completed before reporting successful startup to the server.
566+When this variable is set to ON |XtraDB| waits until the restore of the dump is completed before reporting successful startup to the server.
567
568
569 .. variable:: innodb_buffer_pool_restore_at_startup
570@@ -164,6 +164,14 @@
571
572 This variable was added in release 5.5.10-20.1. Prior to that, it was named :variable:`innodb_auto_lru_dump`, which still exists in earlier versions.
573
574+``INFORMATION_SCHEMA`` Tables
575+=============================
576+
577+This feature provides the following table:
578+
579+.. table:: INFORMATION_SCHEMA.XTRADB_ADMIN_COMMAND
580+
581+ :column result_message: result message of the ``XTRADB_ADMIN_COMMAND``
582
583 Other reading
584 =============

Subscribers

People subscribed via source and target branches