Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Can I safely abandon innodb crash recovery?

g00seg00se EntrantCurrent User Role Beginner
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 (http://bugs.mysql.com/bug.php?id=29847) 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

Comments

  • justin.swanhartjustin.swanhart MySQL Sage Inactive User Role Beginner
    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.
  • g00seg00se Entrant Current User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">justin.swanhart wrote on Wed, 20 October 2010 12:40</td></tr><tr><td class="quote">
    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.

    </td></tr></table>


    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
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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 )
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.