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
[…] 29, 2008 in mysql A lot of people have been buzzing about Maria over the last few […]
I think you had problems crashing MySQL because when insert … select uses the same table both in insert and select clauses, MySQL first executes select into a temporary table, and then insert from it into the destination table. That is, half of the time MySQL is NOT writing into t1, and you have no chance of seeing when it does, you shoot in the dark, pull the plug, and pray.
A better approach would be to create a big table in advance, and use insert t1 select * from big_table; or load data infile.
In Heidelberg we pulled the plug during the large DELETE and MyISAM was corrupted, pretty reliably.
Aha! So thats how you did it in Heidelberg. Giuseppe and I didn’t have enough time to chat with you to see what was going on, and I was in awe when I first saw it in Heidelberg :)
Kudos BTW to all work on Maria – it seems really reliable, and its crash-safe goodness, is simply amazing.
Highly excited I am, so I’ll be playing with it a lot more and providing feedback
Logs are huge by default (1Gb per file) but you can limit how much data write in every log file:
set global maria_log_file_size=
Maria by default remove unneeded logs as soon as it detected (checked on every checkpoint, by default every 30 sec)