I am pleased to announce that the MySQL Database 5.6.4 development milestone release ("DMR") is now available for download (select the Development Release tab). MySQL 5.6.4 includes all 5.5 production-ready features and provides an aggreation of all of the new features that have been released in earlier 5.6 DMRs.  5.6.4 adds many bug fixes and more new "early and often" enhancements that are development and system QA complete and ready for Community evaluation and feedback.  You can get the complete rundown of all the new 5.6.4 specific features here.

For those following the progression of the 5.6 DMRs as the trains leave the station, you should bookmark these MySQL …

I am pleased to announce that the MySQL Database 5.6.4 development milestone release ("DMR") is now available for download (select the Development Release tab). MySQL 5.6.4 includes all 5.5 production-ready features and provides an aggreation of all of the new features that have been released in earlier 5.6 DMRs.  5.6.4 adds many bug fixes and more new "early and often" enhancements that are development and system QA complete and ready for Community evaluation and feedback.  You can get the complete rundown of all the new 5.6.4 specific features here.

For those following the progression of the 5.6 DMRs as the trains leave the station, you should bookmark these MySQL …

The slides of my talk on B+Tree Indexes and InnoDB are now available for download. This slide was presented during Percona Live London 2011. You can download the slides from here.
There are many other interesting and informative talks that were presented during Percona Live London 2011, and I think you should definitely check them out, if you haven't. They are available here.

The post Slides of my talk on B+Tree Indexes and InnoDB appeared first on ovais.tariq.

I recently wrote an article on dealing with an assertion failure in log/log0recv.c, specifically !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table).

I mention it because this occurred after a system outage, and I just encountered another system outage (either HDD power outage or some other serious HDD event), with a completely different assertion failure and error message. Similar to the one above, it’s also kind of obscure, so I wanted to post this so people searching for it will find this.

For reference, the first outage assertion failure was this:

InnoDB: Assertion failure in thread 139838283589376 in file
log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) ==
dict_table_is_comp(index->table)

Here is the new assertion failure:

111201 16:45:00 InnoDB: Assertion failure in …

I’m often wondering what version of the InnoDB Plugin is included with which version of MySQL (or MariaDB). The MySQL changelogs used to denote which version of the InnoDB plugin was included with that particular release of MySQL, but sadly this is no longer the case.

Therefore I’ve compiled a comprehensive list which contains all of this info, and then some (and note all InnoDB Plugin changelog links are provided at the bottom).

Hope you find it helpful

MySQL 5.6:

At ideeli, we use a variety of techniques to eliminate single points of failure and contention. These involve load balancing multiple application servers, caching layers, and calls to asynchronous services. However, at the end of the day we have a single master MySQL database. It doesn’t matter how many dozens of application servers are handling requests if the database is stalled. Without the right tools, it can be difficult to determine exactly what the database is doing at any given time. In this two part post, I will discuss some techniques for finding those problematic queries.

FINDING THE SLOW STUFF

The MySQL slow query log should be the first stop when looking for queries to optimize. Even with tools like mysqldumpslow, the format of the …

As a storage engine developer, I am excited for MySQL 5.6. Looking at http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html, there has been plenty of work done to improve the performance of reads in MySQL for all storage engines (provided they take advantage of the new APIs).

What would be great to add is API improvements to increase the performance of writes, and more specifically, updates. For many applications that perform updates, such as applications that do click counting or impression counting, there are significant opportunities for improving write performance.

Take the following example of click counting (or impression counting). You have a website and want to save the number of times links on your website have been clicked. Your table may look something like:


create table …

Some of you may be aware that MySQL is increasingly using an Oracle-internal bug tracker. You can see these large bug numbers mentioned alongside smaller public bug numbers in recent MySQL release notes. If you’re particularly unlucky, you  just get a big Oracle-internal bug number. For a recently fixed bug, I dug further, posted up on the Percona blog: http://www.mysqlperformanceblog.com/2011/11/20/bug12704861/

Possibly interesting reading for those of you who interested in InnoDB, MySQL, BLOBs and crash recovery.

When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.

In fact, it’s the first thing I check when InnoDB tables are involved.

However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.

Two recent cases I’ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,
OS thread id 3069426592, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
...
INSERT INTO test (id, parent, text) VALUES (180370, 70122, 'test table')

At least that’s what it seemed per the INNODB STATUS, but unfortunately, there wasn’t any further information to go on.

The next time it occurred, SHOW FULL PROCESSLIST was captured at the time.

…

I’m sure most of you have seen the following error, and in many cases it’s quite easy to diagnose, as the error message is clear:

111111 11:11:11 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table test/t does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

From the client side, you may see something like:

ERROR 1146 (42S02): Table 'test.t' doesn't exist

However, if you know you (or some backup/archive script) did not delete the file, nor did you run ALTER TABLE table DISCARD TABLESPACE, then what else could cause this?

Well, I recently found a case where it could occur when a truncate table (on an InnoDB table using the plugin with no foreign keys – so fast truncate method is used, and …