Correlating OS Thread IDs from SEMAPHORES Section to TRANSACTIONS Section

I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

Here is an example semaphore wait:

--Thread 1079654736 has waited at ibuf0ibuf.c line 3549 
for 943.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
a writer (thread id 1079654736) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.c line 1318

So how do you match them up?

SELECT LOWER(HEX(1079654736));

This returns:


Now, examine the TRANSACTIONS section, and look at the value for “OS thread handle”.

Here is the corresponding entry in the TRANSACTIONS section (note “OS thread handle 0x405a3950”) for the semaphore wait:

---TRANSACTION 99FAB3092, ACTIVE 283 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x405a3950, query id 3546555576 update
REPLACE INTO `t1` (id,`date`,`col3`,`col4`,`col5`,`col6`,`col7`,col8)
VALUES (352937385,'2012-08-27 06:16:58','1056665','3755','0','0','0',101)
TABLE LOCK table `db1`.`t1` trx id 99FAB3092 lock mode IX
RECORD LOCKS space id 3680 page no 156295 n bits 456 index `PRIMARY` of table `db1`.`t1` 
trx id 99FAB3092 lock_mode X locks rec but not gap

Since we see “0x405a3950”, we know this is the transaction waiting for the semaphore.

Hope this helps.


Getting Started with the Spider Storage Engine

If you’re like me, you’ve probably heard of the Spider storage engine, but not used it yet.

While it has been available for some years now, I just simply haven’t used it before until now.

I suspect that has to do with ease of installation. Previously, one had to compile it with MySQL in order to use it, which excludes a lot of people. However, in MariaDB 10.0 (as of 10.0.4), it is very easy to add and use.

And with MariaDB 10.0.8 being declared RC, combined with Spider’s sheer usefulness, I only suspect its usage will become more and more widespread.

What is the Spider storage engine, and why will it be useful?

“The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance. It refers to one possible implementation of ISO/IEC 9075-9:2008 SQL/MED. When a table is created with the Spider storage engine, the table links to the table on a remote server. The remote table can be of any storage engine. The table link is concretely achieved by the establishment of the connection from a local MariaDB server to a remote MariaDB server. When more than one Spider storage engine table can be used by the same connection, the connection is shared.”

This is quite flexible, and given that, you can achieve a lot with spider, including federated, sharding, high availability, and background search (the last expected to be fully implemented by the time MariaDB 10.0 is declared GA).

How to install spider?

This is quite simple as the files needed are now included with the MariaDB distribution. In the /share directory, you will see a file named: install_spider.sql

You just need to import this, and there you go:

mysql -uroot -p -P3315 < install_spider.sql

For those interested in the behind-the-scenes, this creates the following 6 system tables (in 'mysql' schema):


And it automatically loads the .so or .dll plugin (depending if you're running on Linux or Windows), thus afterward, SHOW ENGINES should report the following:

mysql> show engines;
| Engine | Support | Comment    | Transactions | XA   | Savepoints |
| SPIDER | YES     | Spider ... | YES          | YES  | NO         |

The most simple example is accessing a table on another instance.

Let's say you have this remote table you want to access:


Then on the local server, where Spider is enabled, issue:

COMMENT 'host "", user "chris", password "xxxxx", port "3314"';

Now you can SELECT from this table, or INSERT into it (which will write to the remote instance), etc.

INSERT INTO s(code) VALUES ('a');

This is very straight-forward, and really it's just the tip of the iceberg. The MariaDB KB article on the Spider storage engine has quite a bit of info and examples, so if you're interested, please see:

Hope this helps.


MariaDB 10.0.8 Overview and Highlights

MariaDB 10.0.8 was recently released as RC (“Release Candidate”), and is available for download here:

This is the first RC release of MariaDB 10.0. It is primarily a bug-fix and polishing release, and all features planned for MariaDB 10.0 GA are included in this release.

There were 6 notable changes in MariaDB 10.0.8:

  1. InnoDB upgraded to version 5.6.14
  2. FLUSH … FOR EXPORT works
  3. Added a new server variable, old_mode, to allow selectively restoring old behavior, in contrast to the old “all-or-nothing” approach of the –old command-line option. See OLD MODE for more.
  4. Added a new read-only server variable malloc_library
  5. Bundled PCRE library upgraded to version 8.34
  6. The CREATE OR REPLACE TABLE statement was added. It is used internally for replicating CREATE … SELECT statements (MDEV-5491), but it is also can be used by clients as any other SQL statement.

You can read more about the 10.0.8 release here:

And if interested, you can review the full list of changes in 10.0.8 (changelogs) here:

Hope this helps.


MySQL 5.6.16 Overview and Highlights

MySQL 5.6.16 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:

As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.

There were 2 minor functionality changes:

  • Previously, ALTER TABLE in MySQL 5.6 could alter a table such that the result had temporal columns in both 5.5 and 5.6 format. Now ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm, so specifying ALGORITHM=INPLACE in these cases results in an error. (Bug #17246318)
  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

I counted 80 bug fixes, 32 of which were InnoDB-related (and some of thoese also memcached-related), 2 partitioning-related, 8 replication-related, and the remaining covered a variety of areas.

I won’t go into all 80 bugs, but if you’re running memcached and/or InnoDB with MySQL 5.6, you should definitely consider upgrading to the latest 5.6.16. And if not, but you’re still running 5.6, I’d recommend reviewing the full changelogs, to see whether you should upgrade asap or not.

Lastly, there was one “known limitation” added, which is:

  • Building MySQL from source on Windows using Visual Studio 2008 fails with an identifier not found error. Later versions of Visual Studio are unaffected. The workaround is to set the CMake build option, INNODB_PAGE_ATOMIC_REF_COUNT, to OFF. This option is ON by default.

So this is very minor, and only related to those building MySQL on Windows using VS 2008. VS 2008 is quite old anyway, but there is the work-around if you’re still using t.

The full changelogs can be viewed here:

And in case you missed it, there is a great post from Morgan Tocker giving thanks to each community member involved in each bug reported/fixed in 5.6.16! What a nice combination of both old and new names and folks from all different companies! That sure shows how active and thriving the MySQL ecosystem really is! 🙂


MySQL 5.5.36 Overview and Highlights

MySQL 5.5.36 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

I was reading through the changelogs to review the changes and fixes, and to summarize, I must say this release is mostly uneventful.

There was one new feature added (for building, so not really applicable to everyone), and only 17 bugs fixed.

The new feature is this:

  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

Out of the 17 bugs, there was only 1 I thought worth mentioning (because it is a wrong results bug):

  • COUNT(DISTINCT) sometimes produced an incorrect result when the last read row contained a NULL value. (Bug #68749, Bug #16539979, Bug #71028, Bug #17867117)

Therefore, if you run COUNT(DISTINCT) and you allow NULL values, then you should consider upgrading so you’re not affected by this.

For reference, the full 5.5.36 changelog can be viewed here:

Hope this helps.


MariaDB 5.5.35 Overview and Highlights

MariaDB 5.5.35 was recently released (it is the latest MariaDB 5.5), and is available for download here:

This is a maintenance release, and so there are not too many big changes of note. However, there are a couple items worth mentioning:

  1. Includes all bugfixes and updates from: MySQL 5.5.35
  2. Includes all bugfixes and updates from: XtraDB from Percona-Server-5.5.35-rel33.0
  3. New SQL_MODE: OLD_MODE – to emulate behavior from old MySQL/MariaDB versions

You can read more about OLD_MODE here, if interested:

Also, there is more about the 5.5.35 release here:

And the full list of fixed bugs and changes in MariaDB 5.5.35 can be found here:

Hope this helps.


MaxScale – We’d Love to Know Your Thoughts

As you’ve heard, MaxScale was recently released, and we’d love for you to try it out and let us know your thoughts.

Anders Karlsson and Ivan Zoratti have written some excellent posts on downloading and setting it up, so if you’re intersted in that, please see their respective posts for quick, detailed instructions.

I just wanted to take a moment and encourage you to try it out if you’re looking for load balancing out of the box, and if you do, we’d love to get your feedback.

If you have any feature requests you’d like to see, or run into any bugs while testing, please report them here:

Or, if you want, feel free to leave any comments here. 🙂

Lastly, if you’re interested in MaxScale (and/or MariaDB Enterprise), please don’t forget to attend Ivan’s webinar this Thursday, 2/6/2014, at noon EST (9am PST):

We look forward to hearing from you. 🙂


MariaDB 10.0.7 Overview and Highlights

MariaDB 10.0.7 was recently released (it is the latest MariaDB 10.0), is currently “beta”, and is available for download here:

I just wanted to note a couple of the main highlights, which are:

  1. XtraDB storage engine was upgraded to the 5.6 version. Now one can use XtraDB with MariaDB 10.0. Unlike MariaDB 5.5, in 10.0 XtraDB is not the default engine, the default is InnoDB, and XtraDB is available as a dynamic plugin.
  2. OQGraph storage engine was upgraded to the version 3. Unlike OQGraph v2, that stores all the data in memory, new OQGraph v3 stores them in a table of another storage engine, on disk. Which makes your graphs persistent and also can support much larger graphs. See the OQGraph documentation for details.
  3. A new plugin metadata_lock_info was added. It implements a new table INFORMATION_SCHEMA.METADATA_LOCK_INFO that shows active metadata locks.

And for reference, the full 10.0.7 changelog can be viewed here:

Hope this helps.