Mark Leith

As planetmysql moved towards “team blogs” and “individual authors”, we in MySQL Support wanted to show our team affiliation as well.

Dups took this on and created a new MySQL Support Blog on dev.mysql.com - thanks Dups!

This aggregates all of our individual blogs - so if you click the blog title in planetmysql, you will be taken to our own blogs. Clicking our names takes you to our aggregated blog:

Mark Leith MySQL MySQL, Support Team

The announcement for 6.0.11 went out not along ago. 

Within it:

6.0.11 will be the last release of 6.0. After this we will be
transitioning into a New Release Model for the MySQL Server

http://forge.mysql.com/wiki/Development_Cycle

The goal of this transition is to enable more frequent and timely
releases of the MySQL Server.

The planned milestone for September 2009 will probably include most of
the features from 6.0. More detail on which, will be communicated
before end of June. Features not in this milestone (e.g. Online Backup,
Falcon, Connection thread pooling, and 4 Byte UTF Support) are planned
for future milestones when they stabilize.

We’re still ironing out the details internally, but please do read up on the above wiki about our current plans for the development schedule!

We’ve still got quite a road ahead to get things settled, but to my mind, we’re on the right path.

Thoughts?

Mark Leith MySQL MySQL

After I blogged about the InnoDB Table and Tablespace monitors, the docs guys got in touch with me, and asked if I’d mind the information being integrated in to the official documentation. “Of course not!” I said.

So Paul DuBois picked up the task, and set about integrating what I had in the docs, as well as sending the InnoDB developers and myself some extra questions for more detail, and adding that in too. 

Paul is finished with this for now - and now there are two new sections within the “SHOW ENGINE INNODB STATUS and the InnoDB Monitors” page of the manual:

• InnoDB Tablespace Monitor Output
• InnoDB Table Monitor Output

Thanks for the work Paul!

Mark Leith MySQL fragmentation, InnoDB, MySQL

It occurred to me at the MySQL UC that I hadn’t blogged about either innodb_stats_on_metadata or innodb_adaptive_hash_index - Morgan Tocker convinced me to do a lightning talk at the end of the Percona conference about them - so I thought I’d do a quick write up on them here as well.
 
First - what’s the point in them? Being the guy that wrote the originating patches for both of them, I have a little background:

innodb_stats_on_metadata

InnoDB is fairly light weight when it gathers table and index statistics. In the default compiled in InnoDB for most users it will do 8 random(ish) dives in to the index and samples the page to try to ascertain the cardinality (selectivity) of the index for use by the optimizer. This being the case, not only is this done when you run ANALYZE TABLE on a table, it is also done during various other points:

• When the table is first opened
• When the table size changes by 1/16th overall since the last time statistics were taken
• If more than 2,000,000,000 rows are inserted (if this is before to the 1/16th rule)
• When anybody accesses any of SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS for InnoDB tables (for each and every InnoDB table in the output)

The first is easy to digest.

The second and third both occur for each row change within InnoDB. row_update_statistics_if_needed() is called from row_insert_for_mysql(), row_update_cascade_for_mysql(), and row_update_for_mysql().

The last in the list is interesting. In order to get an accurate(ish) count of the rows in the table, or the cardinality results etc. for the metadata commands, InnoDB runs the same procedure as an ANALYZE TABLE for all of the tables, because it doesn’t maintain accurate statistics in a table or index header somewhere like MyISAM (being MVCC getting an accurate row count is far more difficult for example).

This is great, if you run these commands periodically to check on their output yourself. Once you start trying to read these tables very often in a monitoring solution of any kind though, you will likely hit two distinct problems:

1. God Damn that INFORMATION_SCHEMA access is SLLLOOOWWWWW
2. If you are monitoring your queries, and their EXPLAIN plans, you may start to see “plan instability” - the optimizer randomly choosing different access paths for the queries

Behind door #1 there’s a couple of issues - we traverse the directories and table files in the  data directory for a lot of the information used in the output, as well as the ANALYZE that is done for InnoDB. We’ve done work in Worklog #3732 to optimize certain accesses to these INFORMATION_SCHEMA tables, but this didn’t solve the statistics issue.

The second issue is a little more subtle. You may have benchmarked your application statements in Dev and QA environments - but data sets change when they are in production. If table and index statistics are constantly being recalculated, there is a high probability that the optimizer will in fact choose different access paths for certain queries and tables where the rows that need to be scanned could be satisfied by more than one index.

Unless you are closely monitoring individual statements for their run time, access paths (EXPLAIN) etc. this is far more difficult to catch (often you will be seeing the “good” EXPLAIN plan a lot, and the “bad” one only surfaces every now and then too - so interactive debugging is made harder as well).

So, at the request of a pretty large SaaS customer, who was having both problems, innodb_stats_on_metadata was born - originally in Bug #26598. If you see any of the situations above, give it a try, and see how it works for you, as well.

Originally it was created as a non-dynamic variable, you could only set it on the command line or in an option file. It was altered by the InnoDB guys to be dynamic, and settable by SUPER privileged users. It’s documented in the InnoDB plugin documentation under the “Changes for Flexibility, Ease of Use and Reliability” chapter.

I’m on the fence about allowing this dynamically, as it still allows tools to change the setting as well (which often have the SUPER privilege to get at things like SHOW INNODB STATUS), when the DBA may have wanted this explicitly not allowed for all users.

Be good citizens tool vendors, and respect this setting please.

innodb_adaptive_hash_index

InnoDB has a really unique feature in the database world called “Adaptive Hash Indexes“. It monitors which rows are being accessed via index searches, and dynamically decides to cache those that are used the most often, by taking the BTREE index for the table the rows are from and converting it to a HASH index in memory, for quick row lookups.  Historically, this has performed really well (and still does today in most workloads).

Prior to the fix for the infamous Bug #15815 (the true start of our multi-core scalability jaunt, imho), it just hummed along with most people not noticing it around. Once we had the fix though, we started to see mutex contention in other places, in certain cases leading to InnoDB getting locked up, and stopping the server due to long semaphore waits - this happened within the code for adaptive hash indexes as well, and was reported as Bug #20358.

This has hit a pretty large number of users - many Support customers as well, and has had many engineer hours in trying to reproduce it reliably (without result, the bug is still “Open”, not “Verified”). As this started to hit a number of customers, I wrote a pretty simple patch for innodb_adaptive_hash_index to allow you to switch off the use of adaptive hash indexes altogether within InnoDB.

Now, for some this comes as a performance trade off - if you hit the above bug, it does allow you to work around it, but your application may benefit from Adaptive Hash Indexes too. If you favor reliability over performance, then use it - it fixes the problem (in a somewhat hackish way).

Yasufumi Kinoshita did a lot of great work, in Bug #29560 and Bug #26442, and there have been other issues found and fixed by the InnoDB team - Bug #39483 (closed at the time of writing) - and Mark over at Google - Bug #32149 (still verified at the time of writing). We hardly ever see Bug#20358 any more though, unless it’s on older versions.

This variable also gets a good mention in the InnoDB plugin documentation, under the ”Changes for Flexibility, Ease of Use and Reliability” again, and in it’s own section “Controlling Adaptive Hash Indexing“, because the variable has a nice side effect as well - it can also help to scale, specifically because of the kind of mutex contention that also lead to Bug #20358. 

The InnoDB manual mentions that you can monitor the contention via the SEMAPHORES section of SHOW INNODB STATUS by looking for “many threads waiting on an RW-latch created in btr0sea.c” - quite correctly.

You can also check out how much memory the hash index is using, and how well it’s being utilized, in the INSERT BUFFER AND ADAPTIVE HASH INDEX section:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
16104 inserts, 16104 merged recs, 15379 merges
Hash table size 2212699, used cells 846306, node heap has 1574 buffer(s)
0.03 hash searches/s, 0.63 non-hash searches

The last two rows apply to the Adaptive Hash index. “Hash Table size” and “used cells” are the number of cells (array slots) assigned to the hash table (always a prime number), and the number of those which are used, respectively. “used cells” was removed as part of Bug #36941 because of it having perform issues by scanning the hash table to find the used cells - in 5.1.28 - so you may not see this on your server. 

“node heap has 1574 buffer(s)” is where you can find out how much memory the hash index is using - the number here is the number of pages that are allocated to the hash index (and a page is 16K in InnoDB by default). 

The last row gives the number of searches both in the adaptive hash index, as well as those not satisfied by it - which can give you a hit ratio for hash read efficiency. 

I’ve made a few graphs for MEM to track these, so they can be monitored over time to get a better picture of adaptive hash index usage, to give you a better tool to judge for yourself what the impact may be for disabling it (see below). I’ll push these up today, and after some testing on a number of other servers, you should hopefully see them in a new advisor update soon.

InnoDB Adaptive Hash Index graphs for MEM

Mark Leith MySQL 5.1 InnoDB, patches

I hadn’t gotten around to this as soon as I would have liked, but here it is!

The talk was moderately attended, but all seemed to be knowledgeable users of MEM, and asked some good insightful questions around how they want to use it.

There was also some talk of being able to set up a “Community” around these kinds of customizations from the users, which I think sounds great. What kind of things would you users want from this? Would a forge wiki section suffice, or what about a new launchpad project? Other ideas?

Interested in your thoughts!

Mark Leith MySQL graphs, MEM

As Peter Gulutzan just announced - we’re opening up a “new” worklog that we’ve been working on, Worklog #2360. PERFORMANCE_SCHEMA.

In fact - it’s not “new”, it’s something that has been in the worklog system for a long time, and has had much much much discussion internally between some of the brightest engineers in the group.

The astute among you out there that read my post on the benchmark with Maria for the the tokutek challenge, may have noted this in the configure line that I used:

./configure –prefix=/usr/local/mysql –localstatedir=/data0/mysqldata \
–without-query-cache –with-extra-charsets=complex –with-pic –with-fast-mutexes \
–enable-thread-safe-client –with-readline –with-big-tables –with-zlib-dir=bundled \
–with-plugin-maria –with-plugin-partition –with-plugin-perfschema \
–without-innodb –without-falcon –without-ndbcluster –without-archive-storage-engine \
–without-example-storage-engine –without-federated-storage-engine –without-backhole-storage-engine

I don’t wish to steal Peter’s thunder (he’s worked enormously hard with this project), but I’m going to take this opportunity to post a couple of snippets that I took away from the first benchmark, and let Peter explain them, and the other tables, in his coming posts.

First, let’s check what are the hottest parts of the server where mutexes, IO (there has been more added with Maria in the tree since my tests here, in fact as a result of them I think), conditions or rw locks etc. are concerned whilst I was running the benchmark:

mysql> SELECT *
    ->   FROM events_waits_summary_by_event_name
    ->  WHERE sum_timer_wait != 0
    -> ORDER BY sum_timer_wait DESC;
+----------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                                               | COUNT_WAIT | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+----------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| wait/synch/mutex/maria/PAGECACHE::cache_lock             | 2147483647 | 13466583113436 |            138 |           1678 |      544893048 |
| wait/synch/cond/maria/translog_d::log_flush              |       5769 |  2961643469364 |          48942 |      513372069 |     4294177986 |
| wait/synch/cond/mysys/my_thread_var::suspend             |        776 |   805743293206 |          12936 |     1038328986 |     4283692164 |
| wait/synch/mutex/maria/translog_buffer::mutex            | 2147483647 |   577561613332 |            138 |            234 |     4231638106 |
| wait/synch/mutex/maria/translog_d:file_header            | 2147483647 |   422453242530 |            138 |            171 |       10497066 |
| wait/synch/mutex/maria/MARIA_FILE_BITMAP::bitmap_lock    |  735984803 |   169563172188 |            144 |            230 |     1244470326 |
| wait/synch/rwlock/maria/MARIA_KEYDEF::root_lock          |  248027518 |    65520201900 |            162 |            264 |       10019208 |
| wait/io/file/sql/FRM                                     |        801 |    12940295718 |            150 |       16155175 |      515501940 |
| wait/synch/mutex/maria/MARIA_SHARE::intern_lock          |    3854083 |     4986469182 |            150 |           1293 |     2181619680 |
| wait/synch/mutex/sql/LOCK_global_system_variables        |    5208428 |      863439342 |            144 |            165 |         559086 |
| wait/synch/mutex/maria/translog_d::sent_to_disk          |     735999 |      508699002 |            150 |            691 |        1163784 |
| wait/synch/rwlock/maria/translog_d::open_files           |     491046 |      434394126 |            168 |            884 |         473808 |
| wait/synch/mutex/maria/MARIA_SHARE::key_del_lock         |    1665267 |      429800430 |            150 |            258 |        1077558 |
| wait/io/file/maria/Control                               |         92 |      429294504 |          12552 |        4666244 |       31523556 |
| wait/synch/mutex/mysys/LOCK_alarm                        |     882996 |      350870472 |            150 |            397 |         479982 |
| wait/synch/mutex/sql/LOCK_mdl                            |     758523 |      312468192 |            150 |            411 |         147180 |
| wait/synch/mutex/sql/LOCK_open                           |     736908 |      308840712 |            150 |            419 |         475176 |
| wait/synch/mutex/sql/LOCK_thread_count                   |    1036629 |      244505298 |            144 |            235 |         375186 |
| wait/synch/mutex/maria/translog_d::log_flush             |     565581 |      227091810 |            138 |            401 |         177606 |
| wait/synch/mutex/sql/LOCK_global_read_lock               |     490683 |      198332490 |            144 |            404 |         476472 |
| wait/synch/mutex/sql/LOCK_plugin                         |     588674 |      158860836 |            144 |            269 |          56688 |
| wait/synch/rwlock/sql/LOCK_grant                         |     267378 |      157255554 |            180 |            588 |        2143872 |
| wait/synch/mutex/maria/TRN::state_lock                   |     735985 |      147520044 |            150 |            200 |         154188 |
| wait/synch/mutex/maria/translog_d::dirty_buffer_mask     |     490790 |      110804592 |            162 |            225 |         135702 |
| wait/synch/mutex/maria/LOCK_trn_list                     |     490678 |      106710882 |            156 |            217 |         474672 |
| wait/synch/mutex/mysys/THD_LOCK_mutex                    |     490848 |       97715760 |            162 |            199 |          65340 |
| wait/synch/cond/maria/translog_b::waiting_filling_buffer |          5 |       20083674 |        1284888 |        4016734 |        6963702 |
| wait/synch/mutex/mysys/THR_LOCK_lock                     |      43560 |       19417026 |            162 |            445 |          25980 |
| wait/io/file/maria/DataFile                              |         20 |       14346036 |           4596 |         717301 |       14193714 |
| wait/synch/mutex/mysys/BITMAP_mutex                      |      43538 |       11462268 |            156 |            263 |          16278 |
| wait/synch/mutex/sql/LOCK_status                         |      21718 |       10181124 |            198 |            468 |           7518 |
| wait/synch/mutex/sql/LOCK_active_mi                      |      43440 |        9080496 |            180 |            209 |          17550 |
| wait/synch/mutex/sql/LOCK_event_metadata                 |      10804 |        4669272 |            210 |            432 |           8166 |
| wait/synch/mutex/sql/RLI_data_lock                       |      21718 |        4548996 |            180 |            209 |          17376 |
| wait/synch/rwlock/sql/LOCK_system_variables_hash         |      10866 |        4383222 |            186 |            403 |           7410 |
| wait/synch/mutex/sql/LOCK_prepared_stmt_count            |      10877 |        2389752 |            186 |            219 |          14400 |
| wait/synch/mutex/mysys/THR_LOCK_open                     |       1260 |         451236 |            156 |            358 |           2622 |
| wait/io/file/maria/KeyFile                               |         50 |         317220 |           2676 |           6344 |          30624 |
| wait/io/file/sql/ERRMSG                                  |          3 |         115410 |          13980 |          38470 |          81966 |
| wait/synch/mutex/sql/hash_filo::LOCK                     |        557 |         105720 |            150 |            189 |            654 |
| wait/io/file/mysys/charset                               |          1 |          46644 |          46644 |          46644 |          46644 |
| wait/io/file/sql/DbOpt                                   |          1 |          40734 |          40734 |          40734 |          40734 |
| wait/synch/mutex/mysys/KEY_CACHE::cache_lock             |        158 |          32664 |            150 |            206 |            516 |
| wait/io/file/sql/pid                                     |          1 |          32382 |          32382 |          32382 |          32382 |
| wait/synch/mutex/mysys/my_thread_var::mutex              |        115 |          31194 |            162 |            271 |            834 |
| wait/synch/mutex/mysys/THR_LOCK_charset                  |        157 |          28620 |            150 |            182 |            528 |
| wait/synch/mutex/mysys/THR_LOCK_threads                  |         22 |          25920 |            498 |           1178 |           5838 |
| wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock        |         82 |          20502 |            150 |            250 |            480 |
| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock     |         66 |          19386 |            174 |            293 |            900 |
| wait/synch/mutex/maria/THR_LOCK_maria                    |         42 |          17574 |            204 |            418 |            798 |
| wait/synch/mutex/sql/LOCK_connection_count               |         39 |           9258 |            192 |            237 |            468 |
| wait/synch/mutex/sql/THR_LOCK_DDL_blocker                |         22 |           5664 |            156 |            257 |            534 |
| wait/synch/mutex/sql/LOCK_xid_cache                      |         18 |           5178 |            234 |            287 |            456 |
| wait/synch/mutex/sql/LOCK_delete                         |         18 |           4932 |            192 |            274 |            534 |
| wait/synch/mutex/sql/LOCK_ha_data                        |         12 |           3954 |            222 |            329 |            546 |
| wait/synch/mutex/sql/THR_LOCK_DDL_is_blocked             |         11 |           3294 |            210 |            299 |            528 |
| wait/synch/mutex/maria/MARIA_SHARE::close_lock           |         15 |           3114 |            150 |            207 |            234 |
| wait/synch/mutex/sql/LOCK_lock_db                        |         10 |           3036 |            198 |            303 |            444 |
| wait/synch/mutex/mysys/THR_LOCK_myisam                   |          3 |           1560 |            402 |            520 |            708 |
| wait/synch/mutex/sql/LOCK_user_conn                      |          2 |            696 |            240 |            348 |            456 |
| wait/synch/mutex/sql/LOCK_mysql_create_db                |          2 |            522 |            258 |            261 |            264 |
+----------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
61 rows IN SET (0.00 sec)

The times that are being recorded above are actually CPU cycles, if you know the power of your CPUs you could convert those to microseconds fairly easily - or you can tell the PERFORMANCE_SCHEMA to record in other formats too.

mysql> SELECT * FROM setup_timers;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| Wait | CYCLE      |
+------+------------+
1 row IN SET (0.00 sec)

Next, let’s take a look at what the thread that was inserting the 1 billion rows had been doing. Yes folks, this takes SHOW PROFILES and SHOW ENGINE INNODB MUTEX to a whole different level (it will pretty much make them defunct, imho, if we can get InnoDB using this instrumentation).

mysql> SELECT *
    ->   FROM events_waits_summary_by_thread_by_event_name
    ->  WHERE thread_id = 12
    ->    AND sum_timer_wait != 0
    -> ORDER BY sum_timer_wait DESC;
+-----------+----------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
| THREAD_ID | EVENT_NAME                                               | COUNT_WAIT | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-----------+----------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
|        12 | wait/synch/mutex/maria/PAGECACHE::cache_lock             | 2147483647 | 13461313032030 |            138 |           1684 |      544893048 |
|        12 | wait/synch/cond/maria/translog_d::log_flush              |       5758 |  2961367306584 |          48942 |      514304846 |     4294177986 |
|        12 | wait/synch/cond/mysys/my_thread_var::suspend             |        769 |   805570319284 |          12936 |     1047555681 |     4283692164 |
|        12 | wait/synch/mutex/maria/translog_buffer::mutex            | 2147483647 |   575354215426 |            138 |            234 |     4231638106 |
|        12 | wait/synch/mutex/maria/translog_d:file_header            | 2147483647 |   420424031916 |            138 |            171 |       10497066 |
|        12 | wait/synch/mutex/maria/MARIA_FILE_BITMAP::bitmap_lock    |  732428925 |   168888242736 |            144 |            230 |     1244470326 |
|        12 | wait/synch/rwlock/maria/MARIA_KEYDEF::root_lock          |  246828873 |    65195270814 |            162 |            264 |       10019208 |
|        12 | wait/synch/mutex/maria/MARIA_SHARE::intern_lock          |    3836359 |     4981866294 |            150 |           1298 |     2181619680 |
|        12 | wait/synch/mutex/maria/translog_d::sent_to_disk          |     732420 |      506173524 |            150 |            691 |        1163784 |
|        12 | wait/synch/rwlock/maria/translog_d::open_files           |     488660 |      432319110 |            168 |            884 |         473808 |
|        12 | wait/io/file/maria/Control                               |         92 |      429294504 |          12552 |        4666244 |       31523556 |
|        12 | wait/synch/mutex/maria/MARIA_SHARE::key_del_lock         |    1658211 |      428136198 |            150 |            258 |        1077558 |
|        12 | wait/synch/mutex/mysys/LOCK_alarm                        |     834892 |      311987064 |            150 |            373 |         479982 |
|        12 | wait/synch/mutex/sql/LOCK_open                           |     732430 |      307217022 |            150 |            419 |         475176 |
|        12 | wait/synch/mutex/sql/LOCK_mdl                            |     732432 |      299536968 |            162 |            408 |         147180 |
|        12 | wait/synch/mutex/sql/LOCK_thread_count                   |     976577 |      230754018 |            144 |            236 |         375186 |
|        12 | wait/synch/mutex/maria/translog_d::log_flush             |     563195 |      226177674 |            138 |            401 |         177606 |
|        12 | wait/synch/mutex/sql/LOCK_global_read_lock               |     488287 |      197541498 |            180 |            404 |         476472 |
|        12 | wait/synch/mutex/maria/TRN::state_lock                   |     732430 |      146788518 |            150 |            200 |         154188 |
|        12 | wait/synch/rwlock/sql/LOCK_grant                         |     244146 |      137635878 |            180 |            563 |        2143872 |
|        12 | wait/synch/mutex/sql/LOCK_plugin                         |     488290 |      136693146 |            150 |            279 |          56688 |
|        12 | wait/synch/mutex/maria/translog_d::dirty_buffer_mask     |     488404 |      110289804 |            162 |            225 |         135702 |
|        12 | wait/synch/mutex/maria/LOCK_trn_list                     |     488288 |      106188246 |            156 |            217 |         474672 |
|        12 | wait/synch/mutex/mysys/THD_LOCK_mutex                    |     488287 |       97045002 |            180 |            198 |          65340 |
|        12 | wait/synch/cond/maria/translog_b::waiting_filling_buffer |          5 |       20083674 |        1284888 |        4016734 |        6963702 |
|        12 | wait/synch/mutex/mysys/THR_LOCK_open                     |         92 |         114222 |            414 |           1241 |           2622 |
|        12 | wait/io/file/maria/KeyFile                               |          3 |          20400 |           4374 |           6800 |           9606 |
|        12 | wait/synch/rwlock/sql/LOCK_system_variables_hash         |          3 |           1752 |            288 |            584 |            750 |
|        12 | wait/synch/mutex/mysys/THR_LOCK_threads                  |          1 |            852 |            852 |            852 |            852 |
|        12 | wait/synch/mutex/sql/LOCK_global_system_variables        |          2 |            498 |            234 |            249 |            264 |
|        12 | wait/synch/mutex/sql/LOCK_ha_data                        |          1 |            414 |            414 |            414 |            414 |
|        12 | wait/synch/mutex/sql/hash_filo::LOCK                     |          1 |            228 |            228 |            228 |            228 |
|        12 | wait/synch/mutex/sql/LOCK_connection_count               |          1 |            204 |            204 |            204 |            204 |
+-----------+----------------------------------------------------------+------------+----------------+----------------+----------------+----------------+
33 rows IN SET (0.00 sec)

Bonus points to those that spot the obvious “bug” in the above!

There is so much more to this worklog, there’s a number more tables, there’s a lot more information, and a lot more ways to look at it. The future of monitoring MySQL just got much brighter.

Stay tuned! I’m sure Peter is itching to spread more information!

Mark Leith General Maria, MySQL, performance_schema

A little late to post (I’ve known a while), but I thought I’d plug my talk for any interested readers out there, that are going to the conference, and use MEM!

I’m talking about Extending MySQL Enterprise Monitor with Custom Advisors, Graphs and Data Collections.

I’ve had the pleasure of working with the team that writes the MEM software (the “Enterprise Tools” team, internally and lovingly known as the “Merlin Team“, the codename that has survived various renames of the product!) for a little over 3 years now. I can’t say I was there at it’s conception, but I started working with them before the initial release of the product, and have watched (and I like to think helped shape) the product very closely whilst being the “Support Coordinator” for the Support Team for MEM. It’s a great product already, but we have many ideas, it’s going to be an awesome product of the future.

Along the way I’ve helped to write many of the graphs and rules that are released for the MySQL Enterprise Monitor within the default Advisor bundles (along with Andy Bang, one of the original team with the concept) and hope to give MEM users some insights in to how they can extend MEM to suit their own needs.

For example, many users have asked for us to add disk space monitoring - we’re working towards making it more seamless for the next releases (2.0 has taken an interim step for this) - but little know that you can already extend the Monitor to do this within the new 2.0 release:

MEM Disk Monitoring

Come to the talk to find out how - and more, like collecting your own data points (from various sources), graphing them and/or alerting on them!

Mark Leith General MEM

So I saw the tokutek challenge, and wondered to myself how Maria would get along with it. I duly downloaded a 6.0 tree, and the iiBench code, tinkered with it to make it actually build, and fired things up.

I watched it closely, for about a day, then got bored and forgot about it. I remembered today that I should take a look!

CPU Usage (Quad Core)

Average rows per second inserted

Load Averages

You can see, in just over a day the IO load became too heavy to process efficiently.

MySQL was built like this:

./configure –prefix=/usr/local/mysql –localstatedir=/data0/mysqldata \
–without-query-cache –with-extra-charsets=complex –with-pic –with-fast-mutexes \
–enable-thread-safe-client –with-readline –with-big-tables –with-zlib-dir=bundled \
–with-plugin-maria –with-plugin-partition –with-plugin-perfschema \
–without-innodb –without-falcon –without-ndbcluster –without-archive-storage-engine \
–without-example-storage-engine –without-federated-storage-engine –without-backhole-storage-engine

I tinkered with Maria right from the start though, I wanted to see what a longer checkpoint interval would give, so increased it to every 5 minutes - obviously this doesn’t seem great. I also wanted to use the same page size as InnoDB out of morbid curiosity. Here’s the my.cnf:

[mysqld]
basedir = /usr/local/mysql
datadir = /data0/mysqldata
log-error = /usr/local/mysql/logs/error.log

maria-block-size = 16384
maria_log_dir_path = /usr/local/mysql/logs
maria_page_checksum = 0
maria_pagecache_buffer_size = 12G
maria-checkpoint-interval = 300

The tables were defined as ENGINE = Maria - i.e TRANSACTIONAL = 1 (with crash recovery) by default.

I added a new custom graph for MEM, to track how the Maria Page Cache gets utilized:

Maria Page Cache Usage

I’ll be making a couple more for Maria as well - including the easy read and write physcial/logical requests from SHOW GLOBAL STATUS (to be released with MEM once Maria is ready, let me know if you want the custom graphs before hand).

The server is RHEL5, Quad Xeon, with 16G RAM, and a 4 disk 10krpm RAID 10 array for the /data0 mountpoint (although using ext3, along with the noop scheduler). Taking a look at iostat when I came back to it, it’s clear that this was my barrier (well, the io wait in the CPU graph is a pretty good indicator as well eh!):

avg-cpu: %user %nice %system %iowait %steal %idle
1.25 0.00 0.00 23.75 0.00 75.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 7.00 9.00 813.00 288.00 6592.00 8.37 142.09 177.35 1.22 100.10

avg-cpu: %user %nice %system %iowait %steal %idle
4.00 0.00 0.25 20.75 0.00 75.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 125.00 0.00 8.00 0.00 1064.00 133.00 0.05 6.25 4.38 3.50
cciss/c0d1 0.00 4.00 7.00 869.00 224.00 7008.00 8.26 142.00 163.25 1.14 100.10

avg-cpu: %user %nice %system %iowait %steal %idle
0.25 0.00 0.25 24.50 0.00 75.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 0.00 8.00 880.00 256.00 7040.00 8.22 143.06 162.75 1.13 100.10

Maria does not make use of bulk_insert_buffer_size, unfortunately, when TRANSACTIONAL = 1. It does when TRANSACTIONAL = 0 however. It also doesn’t use something like InnoDB’s Insert Buffer, so it’s clear that there is probably some way to go when it comes to bulk inserts within Maria for the TRANSACTIONAL mode.

Maria does support concurrent inserts with TRANSACTIONAL = 1, however this is disabled when the table has an AUTO_INCREMENT, (or FULLTEXT/GIS indexes) - so that makes this benchmark difficult from that respect too.

I’d like to restart this benchmark in a few ways:

TRANSACTIONAL = 0, single threaded, default checkpoint interval and block size, default auto_inc

The IO overhead for the log files (on cciss/c0d0 above) was not huge, so it will be interesting to see how this affects things (I’ll report back). This should show how just the new page cache works out as well.

TRANSACTIONAL = 1, 4 threads, default checkpoint interval and block size, default auto_inc

There’s only 4 CPUs on the system, so this will certainly max those out - but I’d like to see the performance differences with filling the cache as well, with concurrent INSERT still disabled.

TRANSACTIONAL = 1, 4 threads, default checkpoint interval and block size, synthetic auto_inc

This would really show how quickly concurrent inserts could start filling the cache concurrently. Sadly I think once the cache is full we will continue to see issues at least for now though.

Maybe I’ll get to those in my copious free time, but thought I’d at least post some initial findings on a “benchmark gone wrong” for amusement factor.

Mark Leith General benchmarks, Maria, MySQL, performance_schema

InnoDB allows you to dump information on its internal data dictionary out to stderr, by creating specific InnoDB monitor tables. Specifically the innodb_table_monitor and innodb_tablespace_monitor tables. This is covered to a limited extent in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html, however not to any great detail.

In the following I’ll use output created via these tables based upon the sample “sakila” database, and give details on how to attempt to correlate the information between the different outputs.

The output of SHOW INNODB STATUS is the output of the InnoDB Monitor, which is different than both the InnoDB Tablespace Monitor and the Innodb Table Monitor. You can also cause this information to be output to the MySQL error log every 15 seconds by creating an InnoDB table named innodb_monitor, like this:

CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;

In order to invoke InnoDB to output it’s data dictionary information on the tablespace, you must create an InnoDB table, like this:

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE = InnoDB;

InnoDB will then dump the tablespace information to either the error log, or the console (if started without log-error).

Note: This only works fully when not using the innodb_file_per_table option, as it only dumps information on the shared tablespace, and not individual “tablespaces”.

================================================
060403 14:20:31 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 1152, free limit 832, free extents 3
not full frag extents 4: used pages 202, full frag extents 2
first seg id not used 0 114
SEGMENT id 0 86 space 0; page 209; res 28 used 28; full ext 0
fragm pages 28; free extents 0; not full extents 0: pages 0
SEGMENT id 0 87 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 88 space 0; page 209; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 89 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 90 space 0; page 209; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 91 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
–snip–
SEGMENT id 0 81 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 82 space 0; page 2; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 83 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 84 space 0; page 2; res 96 used 53; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 21
SEGMENT id 0 85 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
NUMBER of file segments: 108
Validating tablespace
Validation ok
—————————————
END OF INNODB TABLESPACE MONITOR OUTPUT
=======================================

Now lets break down what this means for each segment. Right near the end we have SEGMENT id 0 84:

SEGMENT id 0 84 space 0; page 2; res 96 used 53; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 21

Space 0, page 2 means that the inode of the segment is on page 2 of space 0. The system tablespace (ibdata files) always have the id 0.

res means how many pages we have allocated for the segment.

used means how many of these pages are actually used by the segment at the moment.

So for this segment we have 96 pages allocated, but only 53 are being used currently, leaving 43 pages unused.

full ext means that 0 extents are completely used.

fragm pages means that additionally we have used all of the first 32 pages that InnoDB first allocates for the segment.

free extents means the number of those extents that are allocated for the segment, but where none of the pages is used.

not full extents tells you the number of those extents that are allocated for the segment, where some of the pages are used but not all.

pages tells you how many pages are used within those extents.

An extent in InnoDB is always 64 pages at a size of 16kb - 1 MB.

The tablespace is divided into these 64 page extents. When a segment grows it starts at a single page, and InnoDB allocates the first 32 pages for it individually. After that, InnoDB allocates complete 64 page extents.

This is shown with the number of pages reserved - 32 initial pages + 64 pages for the next extent.

InnoDB bases it’s internal tablespace management as a filesystem. The analogy between an OS file system and an InnoDB tablespace is:

File system              <-> InnoDB
----------------------------------------------
disk partition           <-> tablespace
file                     <-> segment
inode                    <-> fsp0fsp.c 'inode'
fs space allocation unit <-> extent
disk block               <-> page (16 kB)

So currently, the table in question (SEGMENT id 0 84) is 96 pages in size, or 1.5Mb, and has 43 pages free, or 688Kb.

Now that we have this information, how do we try to find out which table this is? Let’s move on to the innodb_table_monitor output.

To direct the output of the InnoDB Table Monitor to the erro log create the following table:

create table innodb_table_monitor (a int) engine = innodb;

This produces the following output for the Sakila sample database:

===========================================
060403 14:29:35 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 22
  COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; FOR_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name ID_IND, id 0 11, fields 1/6, type 3
   root page 46, appr.key vals 22, leaf pages 1, size pages 1
   FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
  INDEX: name FOR_IND, id 0 12, fields 1/2, type 0
   root page 47, appr.key vals 11, leaf pages 1, size pages 1
   FIELDS:  FOR_NAME ID
  INDEX: name REF_IND, id 0 13, fields 1/2, type 0
   root page 48, appr.key vals 12, leaf pages 1, size pages 1
   FIELDS:  REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 22
  COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name ID_IND, id 0 14, fields 2/6, type 3
   root page 49, appr.key vals 22, leaf pages 1, size pages 1
   FIELDS:  ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------
TABLE: name sakila/actor, id 0 14, columns 8, indexes 2, appr.rows 200
  COLUMNS: actor_id: DATA_INT len 2 prec 0; first_name: type 12 len 135 prec 0; last_name: type 12 len 135 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name PRIMARY, id 0 16, fields 1/6, type 3
   root page 51, appr.key vals 200, leaf pages 1, size pages 1
   FIELDS:  actor_id DB_TRX_ID DB_ROLL_PTR first_name last_name last_update
  INDEX: name idx_actor_last_name, id 0 17, fields 1/2, type 0
   root page 52, appr.key vals 121, leaf pages 1, size pages 1
   FIELDS:  last_name actor_id
  FOREIGN KEY CONSTRAINT sakila/fk_film_actor_actor: sakila/film_actor ( actor_id )
             REFERENCES sakila/actor ( actor_id )
--------------------------------------
--snip--
--------------------------------------
TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
  COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id: DATA_INT len 3 prec 0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
   root page 207, appr.key vals 16305, leaf pages 53, size pages 97
   FIELDS:  rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update
  INDEX: name rental_date, id 0 45, fields 3/4, type 2
   root page 208, appr.key vals 17655, leaf pages 28, size pages 29
   FIELDS:  rental_date inventory_id customer_id rental_id
  INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0
   root page 210, appr.key vals 4467, leaf pages 16, size pages 17
   FIELDS:  inventory_id rental_id
  INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0
   root page 211, appr.key vals 589, leaf pages 16, size pages 17
   FIELDS:  customer_id rental_id
  INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0
   root page 212, appr.key vals 1, leaf pages 13, size pages 14
   FIELDS:  staff_id rental_id
  FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
             REFERENCES sakila/staff ( staff_id )
  FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental ( inventory_id )
             REFERENCES sakila/inventory ( inventory_id )
  FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental ( customer_id )
             REFERENCES sakila/customer ( customer_id )
  FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment ( rental_id )
             REFERENCES sakila/rental ( rental_id )
--------------------------------------
TABLE: name sakila/staff, id 0 27, columns 15, indexes 3, appr.rows 1
  COLUMNS: staff_id: DATA_INT len 1 prec 0; first_name: type 12 len 135 prec 0; last_name: type 12 len 135 prec 0; address_id: DATA_INT len 2 prec 0; picture: type 5 len 10 prec 0; email: type 12 len 150 prec 0; store_id: DATA_INT len 1 prec 0; active: DATA_INT len 1 prec 0; username: type 12 len 48 prec 0; password: type 12 len 120 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name PRIMARY, id 0 49, fields 1/13, type 3
   root page 213, appr.key vals 1, leaf pages 3, size pages 4
   FIELDS:  staff_id DB_TRX_ID DB_ROLL_PTR first_name last_name address_id picture email store_id active username password last_update
  INDEX: name idx_fk_store_id, id 0 50, fields 1/2, type 0
   root page 214, appr.key vals 2, leaf pages 1, size pages 1
   FIELDS:  store_id staff_id
  INDEX: name idx_fk_address_id, id 0 51, fields 1/2, type 0
   root page 215, appr.key vals 2, leaf pages 1, size pages 1
   FIELDS:  address_id staff_id
  FOREIGN KEY CONSTRAINT sakila/fk_staff_store: sakila/staff ( store_id )
             REFERENCES sakila/store ( store_id )
  FOREIGN KEY CONSTRAINT sakila/fk_staff_address: sakila/staff ( address_id )
             REFERENCES sakila/address ( address_id )
  FOREIGN KEY CONSTRAINT sakila/fk_payment_staff: sakila/payment ( staff_id )
             REFERENCES sakila/staff ( staff_id )
  FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
             REFERENCES sakila/staff ( staff_id )
  FOREIGN KEY CONSTRAINT sakila/fk_store_staff: sakila/store ( manager_staff_id )
             REFERENCES sakila/staff ( staff_id )
--------------------------------------
TABLE: name sakila/store, id 0 28, columns 8, indexes 3, appr.rows 2
  COLUMNS: store_id: DATA_INT len 1 prec 0; manager_staff_id: DATA_INT len 1 prec 0; address_id: DATA_INT len 2 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name PRIMARY, id 0 52, fields 1/6, type 3
   root page 216, appr.key vals 2, leaf pages 1, size pages 1
   FIELDS:  store_id DB_TRX_ID DB_ROLL_PTR manager_staff_id address_id last_update
  INDEX: name idx_unique_manager, id 0 53, fields 1/2, type 2
   root page 217, appr.key vals 2, leaf pages 1, size pages 1
   FIELDS:  manager_staff_id store_id
  INDEX: name idx_fk_address_id, id 0 54, fields 1/2, type 0
   root page 218, appr.key vals 2, leaf pages 1, size pages 1
   FIELDS:  address_id store_id
  FOREIGN KEY CONSTRAINT sakila/fk_store_staff: sakila/store ( manager_staff_id )
             REFERENCES sakila/staff ( staff_id )
  FOREIGN KEY CONSTRAINT sakila/fk_store_address: sakila/store ( address_id )
             REFERENCES sakila/address ( address_id )
  FOREIGN KEY CONSTRAINT sakila/fk_customer_store: sakila/customer ( store_id )
             REFERENCES sakila/store ( store_id )
  FOREIGN KEY CONSTRAINT sakila/fk_inventory_store: sakila/inventory ( store_id )
             REFERENCES sakila/store ( store_id )
  FOREIGN KEY CONSTRAINT sakila/fk_staff_store: sakila/staff ( store_id )
             REFERENCES sakila/store ( store_id )
--------------------------------------
TABLE: name test/t3, id 0 13, columns 6, indexes 1, appr.rows 1
  COLUMNS: i: DATA_INT len 4 prec 0; j: DATA_VARCHAR prtype 1 len 4000 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name GEN_CLUST_INDEX, id 0 15, fields 0/5, type 1
   root page 50, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i j
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

InnoDB uses clustered indexes, so the actual row data is stored along with the PRIMARY KEY (be this user, or synthetically generated if no PRIMARY KEY was specified). We are looking for a PRIMARY KEY in the above output that is around 96 pages long, and that has 53 pages used.

First, here is the actual table that we are looking for, sakila.rental:

TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
  COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id: DATA_INT len 3 prec 0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
   root page 207, appr.key vals 16305, leaf pages 53, size pages 97
   FIELDS:  rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update
  INDEX: name rental_date, id 0 45, fields 3/4, type 2
   root page 208, appr.key vals 17655, leaf pages 28, size pages 29
   FIELDS:  rental_date inventory_id customer_id rental_id
  INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0
   root page 210, appr.key vals 4467, leaf pages 16, size pages 17
   FIELDS:  inventory_id rental_id
  INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0
   root page 211, appr.key vals 589, leaf pages 16, size pages 17
   FIELDS:  customer_id rental_id
  INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0
   root page 212, appr.key vals 1, leaf pages 13, size pages 14
   FIELDS:  staff_id rental_id
  FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
             REFERENCES sakila/staff ( staff_id )
  FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental ( inventory_id )
             REFERENCES sakila/inventory ( inventory_id )
  FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental ( customer_id )
             REFERENCES sakila/customer ( customer_id )
  FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment ( rental_id )
             REFERENCES sakila/rental ( rental_id )

Among the wealth of obvious information here, for INDEX: name PRIMARY you can see:

 INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
  root page 207, appr.key vals 16305, leaf pages 53, size pages 97
  FIELDS:  rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update

leaf pages is the number of used pages used by the clustered index

size pages is the overall number of pages allocated to the clustered index

Note: It is more difficult to correlate the relations between small tables (1 page allocated each for example). It would be nice if InnoDB were to expose unique IDs between the table and tablespace monitors. 

This output also gives information on the internal data dictionary tables used by InnoDB, which are not exposed anywhere else:

--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 22
  COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; FOR_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name ID_IND, id 0 11, fields 1/6, type 3
   root page 46, appr.key vals 22, leaf pages 1, size pages 1
   FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
  INDEX: name FOR_IND, id 0 12, fields 1/2, type 0
   root page 47, appr.key vals 11, leaf pages 1, size pages 1
   FIELDS:  FOR_NAME ID
  INDEX: name REF_IND, id 0 13, fields 1/2, type 0
   root page 48, appr.key vals 12, leaf pages 1, size pages 1
   FIELDS:  REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 22
  COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name ID_IND, id 0 14, fields 2/6, type 3
   root page 49, appr.key vals 22, leaf pages 1, size pages 1
   FIELDS:  ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------

These are the tables that InnoDB uses to track FOREIGN KEY constraints internally.

Wow, with that lengthy post, I hope this gives people a little more insight in to how to find out whether your tables and tablespace are fragmented within InnoDB!

Mark Leith MySQL fragmentation, InnoDB, MySQL

So it’s been a while since I’ve hit my blog, but I feel compelled to respond to Baron’s post, and many of the other (perhaps short sighted) criticisms out there against this new functionality that we’ve been working on for so long.

Everybody seems to be saying that this functionality should be implemented in the server, or that the better way to do this is to use these patches which add functionality to the logging that MySQL already provides. Well guess what people - what does that give you, other than some more details on you queries?

More I/O.

What’s bad on a database server?

More I/O.

Query Analyzer, whilst it does currently use a proxy to collect the statistics, doesn’t hit your disk at all. Everything is collected and aggregated in memory, it doesn’t require you to turn on logging at all to collect this information. Everybody already knows that turning on the logging on MySQL can give some “serious” performance decreases - which is why we initially looked at using the proxy, rather than log monitoring and aggregation.

Yes, we are still looking at feeding information in to the Query Analysis page from different sources, such as the logs, or even DTrace, for people that don’t want to use the proxy, but we think we’ve actually come up with the better solution for overall database performance here in the short term.

And guess what:

Now, we do have some limitations, which are perfectly valid - we can’t monitor the queries that a slave SQL thread executes for example, and we can’t measure the IO, lock waits, etc. for each statement - but we have some plans on server instrumentation without I/O in the works for that, and Query Analysis is sure to snap that up when available.

I’ll also note that the user and table statistics that were created by Google “get this”, and I’d very much like to see those integrated in to the server. We could really make some good use of those within MySQL Enterprise Monitor as well, and I’m hopeful that we will get these integrated at some point.

So Percona - here’s a challenge - how about creating your extra query logging patches in a way that doesn’t give the I/O overhead of using logging, in the way that Google’s User/Table/Index monitoring does? That would make them compelling, for me. The statistics that have been implemented are fantastic, no doubt about that, but don’t add I/O or lock overhead (such as the current mysql.general_log and mysql.slow_log in 5.1).

Mark Leith MySQL dtrace, explain, MySQL, query logging