With Paul McCullagh’s PBXT storage engine getting integrated into MariaDB 5.1, it’s never been easier to it out. So we have, on a slave off one of our own production systems which gets lots of inserts from our Zabbix monitoring system.

That’s possibly an ideal usage profile, since PBXT is a log based engine (simplistically stated, it indexes its transaction logs, rather than rewriting data from log into index and indexing that) so it should require less disk I/O than say InnoDB. And that means it should be particularly suited to for instance logging, which have lots of inserts on a sustained basis. Note that for short insert burst you may not see a difference with InnoDB because of caching, but sustain it and then you can notice.

Because PBXT has such different/distinct architecture there’s a lot of learning involved. Together …

One of the things that makes a traditional transactional database hard to make highly available is a relatively slow shutdown and start-up time. Applications typically delegate most or all writes to the database, which tends to run with a lot of “dirty” data in its (often large) memory. At shutdown time, the dirty memory needs to be written to disk, so the recovery routine doesn’t have to run at startup. And even upon a clean startup, the database probably has to warm up, which can also take a very long time.

Some databases let the operating system handle most of their memory management needs. This has its own challenges, especially if the operating system’s design doesn’t align exactly with the database’s goals. Other databases take matters into their own hands. InnoDB (the de facto transactional MySQL storage engine) falls …

Some time ago I attended the "Optimisation by Design" course from Open Query¹. In it, Arjen teaches how writing better queries and schemas can make your database access much faster (and more reliable). One such way of optimising things is by adding appropriate query hints or flags. These hints are magic strings that control how a server executes a query or how it returns results.

An example of such a hint is SQL_CALC_FOUND_ROWS. You use it in a select query with a LIMIT clause. It instructs the server to select a limited numbers of rows, but also to calculate the total number of rows that would have been returned without the limit clause in place. That total number of rows is stored in a session variable, which can be retrieved via SELECT FOUND_ROWS();  That simply reads the variable and clears it on the server, it doesn't actually have to look at any table or index data, so it's very fast.

…

InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: "The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 500..."

What this means from a practical perspective is that each query is allocated 500 tickets when it begins executing. Each time it enters InnoDB, this number is decremented until it reaches zero ("entering InnoDB" …

While setting up MySQL Enterprise Monitor 2.2 (Merlin) on a system which had been running version 2.1 I thought I’d try and see what difference the change from using normal innodb tables to using the compressed table format available in the innodb plugin.

I’ve been using a separate db backend for merlin because for me it’s easier to manage and also the database backend has been put on a dedicated server. I’ve also been trying the innodb_plugin on another busier server as I had performance problems with the normal 5.1.42 built-in innodb engine which the plugin managed to solve.

So given that I was using a separate db server I upgraded it to 5.1.47, configured the server to use the plugin (1.0.8) rather than to use the built-in innodb engine and then decided to …

One schema optimization we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It turns there are special cases when this is not the case.

PLAIN TEXT SQL:

1. CREATE TABLE `idxitest` (
2.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `a` int(11) NOT NULL,
4.   `b` int(11) NOT NULL,
5.   PRIMARY KEY (`id`),
6.   KEY `a` (`a`)
7. ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1
8.  
9. mysql> SELECT count(*) FROM idxitest WHERE a=5 AND b=5;
10. +----------+
11. | count(*) |
12. +----------+
13. |    60434 |
…

InnoDB Plugin 1.1 doesn’t add any recovery specific improvements on top of what we already have in Plugin 1.0.7. The details on the latter are available in this blog. Yet, when I tried to recover another big recovery dataset I created, I got the following results for total recovery time:

• Plugin 1.0.7: 46min 21s
• Plugin 1.1: 32min 41s

Plugin 1.1 recovery is 1.5 times faster. Why would that happen? The numerous concurrency improvements in Plugin 1.1 and MySQL 5.5 can’t really affect the recovery. The honor goes to Native Asynchronous IO on Linux. Let’s try without it:

• Plugin 1.1 with –innodb-use-native-aio=0: 49min 07s

which is about the same as 1.0.7 time. My numerous other recovery runs showed that the random fluctuations account for 2-3min of a …

Upgrading from MySQL 5.0 to MySQL 5.1 or Percona Server 5.1 you may run into issues with mysql_upgrade - it will identify some tables to be upgraded and will attempt to run REPAIR TABLE for them. This will fail with "The storage engine for the table doesn't support repair" error message. This seems to confuse a lot of people and I've seen people doing failsafe upgrade path of dumping and reloading complete database confused by this error message, which of course works, but can take quite a lot of time.

Another solution is to simply run ALTER TABLE tbl ENGINE=INNODB which will rebuild table with new MySQL version and normally will fix issues identified by mysql_upgrade.
You can use mysqlcheck -A --check-upgrade to identify tables which need to be fixed such a way.

With Oracle intentions to make Innodb default storage engine in next MySQL release I'm hopeful minor annoyances …

We were discussing the recommendations we issue each quarter around MySQL and the question of using InnoDB plugin came up. We usually follow Planet MySQL closely, so we read what the blogs had to say and it was all good, but we decided to provide our users some data of our own. We used our own sysbench tests on to get the information we needed.
A Word About BenchmarksI don't trust most of the benchmarks that are published online because they really apply to the use case of whomever is writing the article. They are usually many factors that can influence them and I find it difficult to apply them as-is to our environment.

I do trust the benchmarks published online as a reference on how to create and run our own benchmarks. So this article is based on this premise. I recommend you to do your own homework to …

The amount of memory Innodb will require for its data dictionary depends on amount of tables you have as well as number of fields and indexes. Innodb allocates this memory once table is accessed and keeps until server is shut down. In XtraDB we have an option to restrict that limit.

So how much memory can it really take ? Here is some production stats from real system:

PLAIN TEXT SQL:

1. mysql> SELECT count(*) FROM INNODB_SYS_TABLES;
2. +----------+
3. | count(*) |
4. +----------+
5. |    48246 |
6. +----------+
7. 1 row IN SET (8.04 sec)
8.  
9. mysql> SELECT count(*) FROM INNODB_SYS_INDEXES;
10. +----------+
11. | count(*) |
…