Trying to reliably make MyISAM crash; Maria is sturdy as

I’ve been very excited seeing that we announced the Maria Engine Preview. Giuseppe and I were trying to setup a demo, for Maria, at the lightning talks happening later today, towards the end of the MySQL MiniConf at linux.conf.au 2008. It involved MySQL with Maria, and an Asus Eee PC. For the demo, we wanted to show pulling the plug, which can be done either via a kill -9 `pgrep mysqld` or pulling out the battery of the Eee.

However, we failed to get MyISAM to reliably crash! Yes, imagine that, we actually want it to crash – pity that it might have only happened about 1/3rd of the time we tested it. The magic we were looking for:

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: 23998464       Should be: 16000256 |
| test.t1 | check | error    | Recordlink that points outside datafile at 23000368     |
| test.t1 | check | error    | Corrupt                                                 |
+---------+-------+----------+---------------------------------------------------------+
4 rows in set (0.09 sec)

What we got instead was just a warning mentioning a client is using or hasn’t closed the table properly. Clearly, not so good for a demonstration.

When the magic of set storage_engine=maria; was run, and you crashed (either via pulling out the battery or doing a kill operation), Maria would survive the crash. At worst, you’ll see:

check table t1;
+---------+-------+----------+------------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                   |
+---------+-------+----------+------------------------------------------------------------+
| test.t1 | check | warning  | Auto-increment value: 0 is smaller than max used value: 32 |
| test.t1 | check | status   | OK                                                         |
+---------+-------+----------+------------------------------------------------------------+
2 rows in set (0.20 sec)

So, Maria is clearly tough, as Giuseppe puts it. Give it a twirl (binaries, sources), and hop on over to the Maria Forums if you have questions. And if you’re checking out/building from source, you might find the Building MySQL from source guide handy

Alas, only a demo in a VM today . It seems more reliable inside a VM, not quite on the Eee. Besides, the poor SSD has been through quite a number of fsck’s now. BTW, one other thing to note: Maria log files seem to be pretty huge. Running it on a SSD that has about 1.5GB of free space (total, 4GB), is harsh.

For reference, the test (generally, wait for count to reach 128, switch terminals, and kill mysqld/pop battery off):

drop table if exists t1;

create table t1 (id int not null auto_increment primary key, b longblob) ;
select table_schema,table_name,engine
     from information_schema.tables
     where table_schema=schema() and table_name='t1';
insert into t1 values (1, repeat('a',1000000));
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;
insert into t1 select null,b from t1; select count(*) from t1;

Technorati Tags: mysql, maria, myisam, demo, storage engine