As soon as we get couple FusionIO cards, there is question how to join them in single space for database. FusionIO does not provide any mirroring/stripping solutions and totally relies on OS tools there.

So for Linux we have software RAID and LVM, I tried to followup on my post
How many fsync / sec FusionIO can handle, and check what overhead we can expect using additional layers over FusionIO card.

The card I used is Fusion-io ioDrive Duo 320GB, physically it is two cards on single board, and visible as two cards to OS.

By some reason I was not able to setup LVM on cards, so I've finished tests only for software RAID0 and RAID1.

I used XFS filesystem mounted with "-o nobarrier" option, and I've the test I used in previous post on next configurations:

• Single …

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB:
It is

• INNODB_TABLE_STATS
• INNODB_INDEX_STATS

These table show statistics about InnoDB tables ( taken from InnoDB data dictionary).

INNODB_TABLE_STATS is

• | table_name | table name in InnoDB internal style ('database/table') |
• | rows | estimated number of all rows |
• | clust_size | cluster index (table/primary key) size in number of pages|
• | other_size | other index (non primary key) size in number of pages|
• | modified | internal counter to judge whether statistics recalculation should be done |

INNODB_INDEX_STATS is

• | table_name | table name in …

Using the Embedded InnoDB plugin I’m working on, you can use the INNODB_STATUS table function in the data_dictionary, you can do pretty neat things.

For example, we can see that each autocommit transaction causes an fsync and if you insert multiple rows ina  single statement, you still only get 1 fsync:

drizzle> SELECT * FROM DATA_DICTIONARY.INNODB_STATUS
    -> WHERE name="fsync_req_done";
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| fsync_req_done |    25 |
+----------------+-------+
1 row in set (0 sec)

drizzle> insert into t1 values (1);
Query OK, 1 row affected (0.05 sec)

drizzle> SELECT * FROM DATA_DICTIONARY.INNODB_STATUS WHERE name="fsync_req_done";
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| fsync_req_done |    26 |
+----------------+-------+
1 row in set (0 sec)

drizzle> insert into t1 values (1),(2),(3),(4);Query OK, 4 rows affected (0 …

I am rather excited about being able to do awesome things such as this to get the current configuration of your server:

drizzle> SELECT NAME,VALUE
    -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION
    ->  WHERE NAME IN ("data_file_path", "data_home_dir");
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| data_file_path | NULL  |
| data_home_dir  | ./    |
+----------------+-------+
2 rows in set (0 sec)

drizzle> SELECT NAME,VALUE
    -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION
    -> WHERE NAME IN ("data_file_path", "data_home_dir");
+----------------+-------+
| NAME           | VALUE |
+----------------+-------+
| data_file_path | NULL  |
| data_home_dir  | ./    |
+----------------+-------+
2 rows in set (0 sec)

drizzle> SELECT NAME,VALUE
    -> FROM DATA_DICTIONARY.INNODB_CONFIGURATION
    -> WHERE NAME = "io_capacity";
+-------------+-------+
| NAME        | VALUE | …

I am very fortunate to be sent to a Percona innodb low level conference - with one of the guys who has written the High Performance MySQL book. One of the key items will be to dive deep into the Innodb kernel and find out what the hell the thing is doing. To that end I'm going post what I know about the 'show innodb status' output - now my aim from this conference is to pick up on some of the areas that I'm green in to identify and resolve more MySQL performance problems.

So here is my 'show innodb status' walkthrough:

mysql> show innodb status\G*************************** 1. row *************************** Type: InnoDB Name:Status:=====================================100308 17:05:14 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 14 secondsThe following information is based on stats gathered in the last 14 seconds - Generally a good 30 seconds should pass …

I’ve been noticing an undeniable trend in my consulting engagements in the last year or so, and when I vocalized this today, heads nodded all around me. Everyone sees a growth in the number of cases where otherwise well-optimized systems are artificially limited by InnoDB contention problems.

A year ago, I simply wasn’t seeing the need for analysis of GDB backtraces en masse. These days, I’m writing custom tools to gather and analyze backtraces. A year ago, I simply looked at the SEMAPHORE section of SHOW INNODB STATUS. These days I’m writing custom tools to aggregate and reformat that data so I can interpret it more easily. And I’m actually seeing cases of this type of problem multiple times every week. I remember the first time I ran into a server that was literally optimized to the limit, but struggling under the load. It was something new for me, not that long ago. Oh, I’d seen it before, plenty, but was always able to point …

I’ve heard about the innodb_plugin but not had time to put it to the test.

Recently though due to some problems I’ve been having with the MySQL Enterprise Monitor (Merlin) I’ve had to try a few changes and had the opportunity to try out the innodb plugin.

I have been using Merlin for some time and like it a lot. It is not perfect but does a good job for me.  However, since upgrading to version 2.1 I have been having some database load problems. I long ago split the merlin server into a front- and back-end server with the backend running a standard MySQL 5.1 Advanced package. That has been working fine.

I have been monitoring more and more mysqld servers and recently the database backend could not cope. Basically the writes of …

Shortly after I posted my last summary of MySQL releases, our son Mats was born and I went on a 2.5-week vacation. Our developers did not rest in the meanwhile and I'd like to give you a quick update of what's new since then:

MySQL Connector/Net 6.3.0

• Visual Studio 2010 RC support
• Nested transaction scope support

MySQL Workbench 5.2.16 Beta 6

• Fixed 67 bugs
• Saving your profile/connection passwords in OSX keychain, gnome-keyring or in an encrypted password-vault-file.
• New rapid development features for generating complete SQL …

I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on:

- durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ?
- do we use binary logs ( I used ROW based replication for 5.1)
- do we have sync_binlog options.

So why would not take these as variable parameters and run simple benchmark.
I took sysbench update_key scenario ( update indexed field on simple table)
and used Dell PowerEdge R900 with 16 cores, FusionIO as storage for table and RAID 10 with BBU as storage for innodb log files, innodb system table space and binary logs. And I used Percon-XtraDB-5.1.43-9.1 for benchmarks. All used partitions are formatted in XFS and mounted with nobarrier …

Oracle recently announcent MySQL-5.5.2-m2 milestone, and I have a predition that MySQL-5.5 will be announced as GA on MySQL UC 2010 in April.

So let's make quick on scalability characteristics we should expect from new release.

I made sysbench oltp benchmarks on 10 mln rows (worth 2.5GB of data), on our Dell R900 system ( 16 cores, 32GB of RAM, FusionIO + RAID10, but disk in this case does not matter).

There is results for various threads (results are in tps, more is better)

I think it is good results, and there couple conclusions we can come with

• InnoDB now can scale up to 16 threads pretty well
• InnoDB can keep load up to 128 threads, and for 256 threads the result is …