Merge lp:~hrvojem/percona-server/bug1056603-5.1 into lp:percona-server/5.1
- bug1056603-5.1
- Merge into 5.1
Proposed by
Hrvoje Matijakovic
Status: | Merged |
---|---|
Approved by: | Alexey Kopytov |
Approved revision: | no longer in the source branch. |
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 |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Alexey Kopytov (community) | Approve | ||
Review via email: mp+134478@code.launchpad.net |
Commit message
Description of the change
To post a comment you must log in.
Revision history for this message
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:/
review:
Needs Fixing
Revision history for this message
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 | + |
Hrvoje,
Few comments:
- There's a difference between internal InnoDB tables naming and the SYS_INDEXES.
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_
So it is technically incorrect to say that "If this option is enabled, `INNODB_ SYS_STATS` system table to store
|XtraDB| uses the :table:
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/