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(*) |
…

I’ve integrated HailDB into our Hudson setup (haildb-trunk on Hudson). I’ve also made sure that Hudson is tracking the compiler warnings. We’ve enabled more compiler warnings than InnoDB has traditionally been compiled with – this means we’ve started off with over 4,300 compiler warnings! Most of those are not going to be anything remotely harmful – however, we often find that it’s 1 in 1000 that is a real bug. I’ve managed to get it down to about 1,700 at the moment (removing a lot of harmless ones).

I’ve also enabled a cppcheck run on it. Cppcheck is a static analysis tool for C/C++. We’ve also enabled it for …

Today I had to decode an innodb index page, so I documented the entire process here:

E:\mysql-enterprise-gpl-5.0.66a-winx64\bin>mysqld-nt --console --skip-grant-tables --skip-name-resolve
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
080919 14:29:00  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
080919 14:29:00  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
080919 14:29:01  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating …

As I mentioned in my talk An Overview of Flash Storage for Databases I see in the near and middle term future a lot of interest for using Flash storage in Level 2 caching level. The price-capacity trade-off makes Flash as the very good fit for a cache layer.
Actually it is not the new idea, and it was implemented in L2ARC ZFS for two years already.
It is also described in ACM article "Flash storage memory" by Adam Leventhal.

So I am exciting to see it is available for Linux now. Paul Saab and Mohan Srinivasan from Facebook released Flashcache, which allows to use Flash as …

I did some drawing exercise and put XtraDB / InnoDB internals in Visio diagram:

The XtraDB differences and main parameters are marked out.

PDF version is there http://www.percona.com/docs/wiki/percona-xtradb:internals:start.

Entry posted by Vadim | 4 comments

Add to: | …

Oracle managed to score a major victory last week at the MySQL Conference by announcing performance gains of 200-360% in the forthcoming version 5.5.  This is a tremendous improvement and comes in part due to closer collaboration between what were historically two distinct (and occasionally competitive) groups: the InnoBase team and the MySQL Server team.  Bringing the InnoBase team under the direction of the MySQL Server team under Tomas Ullin is a great benefit not only to MySQL developers, but also for MySQL users.  No doubt these performance gains are a result of many months of hard work by not only Tomas, but also a good number of folks on both teams including guys like Mikael Ronstrum, Kojstja, Calvin Sun and others.  

Reaction to the new release has been positive in the community from the likes of …

(and before you ask, yes “rotating blades” comes from “become a fan”)

I’m forming the ideas here first and then we can go and implement it. Feedback is much appreciated.

Two tables.

Table one looks like this:

CREATE TABLE fan_of (
user_id BIGINT,
item_id BIGINT,
PRIMARY KEY (user_id, item_id),
INDEX (item_id)
);

That is, two columns, both 64bit integers. The primary key covers both columns (a user cannot be a fan of something more than once) and can be used to look up all things the user is a fan of. There is also an index over item_id so that you can find out which users are a fan of an item.

The second table looks like this:

CREATE TABLE fan_count (
item_id BIGINT PRIMARY KEY,
fans BIGINT
);

Both tables start empty.

You will have 1000, 2000,4000 and 8000 concurrent clients attempting to run the …

The video of my 5-minute Ignite talk on XtraDB is up on YouTube. Ignite talks have exactly 20 slides on auto-advance every 15 seconds. “Enlighten us, and make it fast!” It’s better than a lightning talk. I explained the difference between Percona XtraDB and other technologies such as standard MySQL, OurDelta, and MariaDB.

Related posts:

1. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug
2. Recap of CPOSC 2009, plus slides Yesterday
3. Learn about mk-query-digest …

MySQL-5.5.4 ® is the great release with performance improvements, let's see how it performs in
tpcc-like workload.

The full details are on Wiki page
http://www.percona.com/docs/wiki/benchmark:mysql:554-tpcc:start

I took MySQL-5.5.4 with InnoDB-1.1, tpcc-mysql benchmark with 200W ( about 18GB worth of data),
InnoDB log files are 3.8GB size, and run with different buffer pools from 20GB to 6GB. The storage is FusionIO 320GB MLC card with XFS-nobarrier. .

While the raw results are available on Wiki, there are graphical results.

I intentionally put all line on the same graph to show trends.

…