Note: Article has been edited as I was confusing MarisDB and the Maria Stoare Engine!

Just for the heck of it, I decided to try a very simple benchmark on the Maria Storage Engine again. This time, I'm using a simple SELECT. The table I use looks like this:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
Which should be simple enough. I fill this with 1.3 millions rows, with c1 having consequitive numbers from 1 and up. My benchmark consists of random SELECTs of the c2 column from this table, using a primary key lookup on c1. I run this on 400 concurrent threads with each thread doing 1000 SELECTs. Ignoring the actual values, MyISAM and InnoDB come out pretty close in performance, with InnoDB slightly behind. Which is reasonable. Maria is at less than half the performance of MyISAM though. This is worrying. And I know what you say, …

I just wrote a large post on reasons for innodb main tablespace excessive growth and I thought it would make sense to explain briefly of why it is so frequently you have purge not being the problem at all and when out of no where you can see purge thread being unable to keep up and undo table space explodes and performance drops down. Here is what happens.

When you have typical OLTP system with small transactions your UNDO space is small and it fits in buffer pool. In fact most of the changes do not need to go to the disk at all - the space for undo space is allocated, used and freed without ever needing to go to the disk.

Now when you have spike in writes or long running transactions which increases your undo space size it may be evicted from buffer pool and stored on disk. This is when problems often starts …

So you're running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from its starting 10MB size.
What could be the reason of this growth and what you can do about it ?

There are few things which are always stored in main tablespace - these are system tables, also known as data dictionary - table and index definitions and related stuff, double write buffer, insert buffer which temporary stores records to be inserted in the index tree and undo segment holding previous versions of changed rows.

The system tables size depends on the number and complexity of the tables you have in the system. If you have hundreds of thousands of tables it may consume gigabytes of space but for most installations we're speaking about tens of megabytes or less. Double Write Buffer is fixed size (2MB = 128 of 16KB pages) and so will not affect growth of …

Quite frequently I see people confused what table locks reported by SHOW INNODB STATUS really mean. Check this out for example:

PLAIN TEXT SQL:

1. ---TRANSACTION 0 4872, ACTIVE 32 sec, process no 7142, OS thread id 1141287232
2. 2 LOCK struct(s), heap size 368
3. MySQL thread id 8, query id 164 localhost root
4. TABLE LOCK TABLE `test/t1` trx id 0 4872 LOCK mode IX

This output gives us an impression Innodb has taken table lock on test/t1 table and many people tend to think Innodb in fact in some circumstances would abandon its row level locking and use table locks instead. I've seen various theories ranging from lock escalation to using table locks in special cases, for example when no indexes are defined on the table. None of this is right.

In fact Innodb uses …

We had a one of our slave servers frequently stop replicating with the “Innodb Lock Wait Timeout” error. The slave IO thread would continue to fetch the binlogs while the slave SQL thread kept stopping with  the above mentioned error. The teams initial inclination was to change the innodb lock wait timeout variable from 50 secs to a higher value. It was a read-only slave. Our expectation was there would be no competing writes. Then we started listing what are the next steps possible and what could be wrong.

1. There could be a user with “super” privilege in the system that was running updates directly on the slave
2. A backup script that could be locking the tables out for backup
3. Increase the “innodb lock wait timeout variable“
4. Enable the innodb lock monitor

While we were working on the system, we noticed that there were few select queries that …

With the exception of Windows InnoDB has used ’simulated AIO’ on all other platforms to perform certain IO operations. The IO requests that have been performed in a ’simulated AIO’ way are the write requests and the readahead requests for the datafile pages. Let us first look at what does ’simulated AIO’ mean in this context.

We call it ’simulated AIO’ because it appears asynchronous from the

To test my assumptions, I used one of my test Linux servers to perform a sysbench on 5.0.91, 5.1.47 built-in and plugin, and 5.5.4. The MySQL servers were all configured with

This blag was originally posted at http://cafuego.net/2010/05/26/fast-paging-real-world

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 …

Let's start with the plugin. The documentation has been updated to cover this new feature. And 27 new tests give me some confidence that it …

So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table!

So, it’s kinda known but nevertheless often ignored that this operation on InnoDB is costly in terms of time; what InnoDB has to do to figure out the exact number of rows is scan the primary key and just tally. Of course it’s faster if it doesn’t have to read a lot of the blocks from disk (i.e. smaller dataset or a large enough buffer pool).

I was curious about PBXT’s performance on this, and behold it appears to be quite a bit faster! For a table with 50 million rows, PBXT took about 20 minutes whereas the same table in InnoDB took 30 minutes. Interesting! …