Friday, November 13, 2009

InnoDB Plugin (With some Windows focus) - Part 2

In the last post on this subject, I discussed the benefits of the InnoDB plugin for operational things, such as creating and dropping indexes, and how much faster and non-obtrusive on other operations this is with the plug-in version of InnoDB, compared to the built-in InnoDB.

This time, I will discuss another benefit of the plugin, which is the operational metadata views provided by the plug-in. A way overdue feature of InnoDB is the ability to inspect locks and lock waiters, to be able to effectively manage lock concurrency issues. In any database using even a moderately complex schema, and having a reasonable amount of writes, concurrenctý issues will often happen. And the issue is that if you cannot monitor them properly, then you may not know what is happening. Users have sessions waiting for locks, then tires of waiting and abandons the operations, sometimes leaving locks of their own around!

Another issue is that end-users cannot usually tell the differece between a straight performace problem, such as a too small cache, a slow disk or a bad network, and a concurrency issue, i.e. waiting for a lock. And the difference in how we find and fix these issues is distinctly different!

OK, so now let's have a look in practice on hwo this works. To begin with, we need to configure the InnoDB plugin monitoring INFORMATION_SCHEMA tables, and here I will install all of them, not only the ones related to locking. The setting needed in the [mysql] section in the my.ini file, to support the InnoDB plugin and all the corresponding monitoring tables are then:

Note that the "plugin_load" parameter must be on a single line! Now, restart the MySQL server and we can start to have some fun!

First create an InnoDB table for our testing:
CREATE TABLE locktest (
c2 char(10) DEFAULT NULL,
KEY c2 (c2)

And insert some data:
INSERT INTO locktest VALUES(1, 1);
INSERT INTO locktest VALUES(2, 2);
INSERT INTO locktest VALUES(10, 16);
INSERT INTO locktest VALUES(11, 16);
INSERT INTO locktest VALUES(15, 18);

And we are set to do some testing. First, open two sessions on the database where the testing table was created, and then, in the first session, do a SELECT .. FOR UPDATE in a transaction:

Now, in the other session, lets try to update one of the locked rows:
UPDATE locktest SET c2 = 17 WHERE c1 = 11;

And then, in a third session, lets see what we have in the lock and lock_wait tables:
use information_schema
SELECT lock_mode, lock_type, lock_table, lock_data FROM innodb_locks;
| lock_mode | lock_type | lock_table | lock_data |
| X | RECORD | `plugintest`.`locktest` | 11 |
| X | RECORD | `plugintest`.`locktest` | 11 |
2 rows in set (0.00 sec)
SELECT * FROM innodb_lock_waits;
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
| 6007 | 6007:0:16396:12 | 6001 | 6001:0:16396:12 |
1 row in set (0.00 sec)

As we can see, InnoDB will only show locks that are waited on, but that is cool. Look at the lock_data column in the innodb_locks table, you see the actual data being waied on there. The lock_mode column is X (the two rows are for the table and index respectively). In your case, you may see X,GAP for the index lock. That is because you have index gap locking on, or in other words, you have not set innodb_locks_unsafe_for_binlog.

All in all, tracking down concurrency issues with these tables is A LOT easier than the old "show innodb status" way of dealing with things.



jhon said...

Soon Joe decides to body a account of association who acquire bought accessories from him. He becomes an associate for added accessories accompanying to the internet business field. He keeps autograph articles, but now he is alpha to acquire lots of money, so he can advance his sales by added means of accepting traffic. He builds a big account of balmy and accommodating buyers, from whom he makes alike added sales.
Check Cashing Corona

ariyan arifin said...

On the off alluring that you end up in an bootless arena while the activity is on-going, it is a alert adjustment to academic assay with the case or abundance ability befitting in apperception the end anxious to apprehend of a ablaze affirmation. Cash Advance San-diego

jhon said...

The abode to attending for differences is in annual fees and features. Read the agreement and altitude anxiously and absolutely accept the after-effects of not advantageous the accommodation aback on time. Accomplish abiding you can allow the loan.

Cash Advance Chicago

Robbert said...

. Since no type of security is being given, these credits are somewhat hard to get affirmed. Unsecured auto advances are viewed as more dangerous by the moneylender as they don't have anything to offer in the event that they have to recoup their credit sum if there should arise an occurrence of default. To make up for this additional danger, moneylenders issue unsecured credits at a higher rate of premium. Check Cashing Costa-mesa