Can I safely abandon innodb crash recovery?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’m running a MySQL innodb database for a SugarCRM installation - and currently suffering due to hardware spec.

We implemented a custom email-capture system and now we have some large tables. Three are 1 GB each with about 4 million row each - and one table is 26GB with 1 million rows. This is the majority of the whole db (which is about 32GB).

The database occasionally suffers when executing multi-join queries against these tables. We believe it is down to lots of disk IO and fragmented indices which is compounding the ‘analyze’ of these tables into making inappropriate query plans.

To accommodate this, we plan to upgrade the db server with lots of RAM so we can ramp up the innodb_buffer_pool_size to handle much more processing in memory (32GB?). However this bug ([URL=“http://MySQL Bugs: #29847: Large CPU usage of InnoDB crash recovery with a big buf pool”]MySQL Bugs: #29847: Large CPU usage of InnoDB crash recovery with a big buf pool) makes it look like any crash recovery on this db would take forever.

For the new server, we would use Ubuntu 10.04 LTS - which runs with MySQL 5.1.41 - so we can’t use the innodb plugin 1.0.7 which may have a fix for this.

If I’m manually restarting the mysql daemon, I can temporarily edit the mysql config file to set the innodb_buffer_pool_size low just for the recovery process. However, if the server were to crash, rebooting the box would automatically restart the database daemon with the large buffer pool size value and who knows how long it would take to recover?

So the short of it (?!) is: Can I safely “kill -9” the mysql daemon when it’s in the middle of a recovery? Then edit the config to reduce the buffer pool size - and then restart it again to recover more quickly?

Many thanks,
G

2

Hi,

Regardless as to the advisability of the operation, InnoDB will properly recover if you kill it during crash recovery. Otherwise it would not be able to withstand a power failure during crash recovery, as an example.

Why don’t you consider using Percona Server? We provide up-to-date packages for debian based operating systems like Ubuntu.

3
[B]justin.swanhart wrote on Wed, 20 October 2010 12:40[/B]
Hi,

Regardless as to the advisability of the operation, InnoDB will properly recover if you kill it during crash recovery. Otherwise it would not be able to withstand a power failure during crash recovery, as an example.

Why don’t you consider using Percona Server? We provide up-to-date packages for debian based operating systems like Ubuntu.

Because I only found out about Percona and XtraDB via the performance blog in searching about that particular bug )

I am looking into though - and also XtraBackup even if we stay with the vanilla version.

I know it’s the impossible question, but with regards to MySQL 5.1.41 and the Percona/XtraDB equivalent - what would the main areas of performance improvement be?

Thanks,
G

4

If you use ubuntu are you sure it is the innodb recovery a real issue here ? Do you have “check_for_crashed_tables;” enabled in your /etc/mysql/debian-start ? If so, try to restart mysql service after commenting this out )
Ignore me if you have this ‘feature’ disabled though )