Over the weekend, MySQL made available a new storage engine, codenamed
Maria.
It is an improvement of MyISAM, the flagship engine, adding crash recovery to the already appreciated features of MyISAM. This release is a preview, based on MySQL 5.1 code base. The actual final implementation has not been announced yet, although it is safe to assume that it will happen in the 6.x series.
The
source code is available as a bitkeeper tree.
There is some
documentation, and more will follow.
What can we do with the new engine? The immediate answer is "use it as we use MyISAM, with crash recovery features".
Let's show an example, using MyISAM: (this demonstration was given live at the Linux Conference Australia during MySQL Mini-conf)
mysql [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (1.14 sec)
mysql [localhost] {msandbox} (test) > create table t1 (id int, b longblob) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > insert into t1 values (1, repeat('a',1000000));
Query OK, 1 row affected (0.02 sec)
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 8 rows affected (0.26 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 16 rows affected (1.17 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 32 rows affected (2.13 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 64 rows affected (4.77 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
At this point, while the server is inserting data, from a different shell, we enter a vicious command:
$ kill -9 `pgrep mysqld`
which is as close as you can get to
pulling your server plug. The result is disastrous.
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > exit
Bye
Let's restart the server and see what has happened.
[sandbox@dsl093-167-171 maria_5_1_23]$ ./start
sandbox server started
[sandbox@dsl093-167-171 maria_5_1_23]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-maria-alpha Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql [localhost] {msandbox} (test) > check table t1;
+---------+-------+----------+-----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+-----------------------------------------------------------+
| test.t1 | check | warning | 1 client is using or hasn't closed the table properly |
| test.t1 | check | warning | Size of datafile is: 256004096 Should be: 128002048 |
| test.t1 | check | error | Record-count is not ok; is 256 Should be: 128 |
| test.t1 | check | warning | Found 256 parts Should be: 128 parts |
| test.t1 | check | error | Corrupt |
+---------+-------+----------+-----------------------------------------------------------+
5 rows in set (0.62 sec)
As you can see, the table got corrupted, because we pulled the plug during an insertion.
Let's do the same operation with a Maria table.
mysql [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (0.15 sec)
mysql [localhost] {msandbox} (test) > create table t1 (id int, b longblob) engine=maria;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {msandbox} (test) > insert into t1 values (1, repeat('a',1000000));
Query OK, 1 row affected (0.03 sec)
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 4 rows affected (0.29 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 8 rows affected (0.82 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 16 rows affected (1.61 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 32 rows affected (3.16 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 64 rows affected (6.54 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
#
# kill -9 from another terminal
#
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > exit
Bye
Here we pull the plug as in the MyISAM case, and the server dies.
On restart, the situation looks much different:
[sandbox@dsl093-167-171 maria_5_1_23]$ ./start
sandbox server started
[sandbox@dsl093-167-171 maria_5_1_23]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-maria-alpha Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql [localhost] {msandbox} (test) > check table t1;
+---------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status | OK |
+---------+-------+----------+----------+
1 row in set (0.73 sec)
So, Maria survived the crash, while MyISAM was corrupted. There is still much to do to make this engine ready for production, but it looks like an excellent start.
Thus, when I saw a skilled craftsman creating customized hats and scarfs, I couldn't resist.
. We are going to have MySQL company meeting in this beautiful place. For now, I am just nursing my jet lag and preparing to meet a few hundred colleagues.
