CODEWITHSUNDEEP
mysqlphpmyadmin
karto
karto

Reputation: 3658

MYSQL change storage engine of table from INNODB to MYISAM

I have about 10million records of data in a table(innodb as storage). I normally get max locks exceeded when i perform mass edits, inserts and additions to it. Upon reading some help tips i want to convert the table to MyISAM and see if there will be some improvements.

but when i try to change the storage type from innodb to myisam in phpmyadmin, i get 'Number of locks exceeded error'

  1. Is it good to change to myIsam?
  2. What proceedure do i use to avoid getting 'max no. of locks exceeded error?'

thanks

Upvotes: 1

Views: 2382

Answers (1)

tadman
tadman

Reputation: 211560

It's probably a very bad idea to change to MyISAM. You'll lose transaction support and the database won't be journaled meaning a crash could easily corrupt it beyond recoverability.

You can adjust the lock time-out parameter for the server to give operations a longer time to complete. For batch operations this is essential. This is the innodb_lock_wait_timeout configuration parameter you can adjust manually or set in my.cnf. This is normally very low, typically 50 seconds, so many big operations will fail unless this is changed.

You should also make a point of removing any indexes that aren't strictly required as this may be the source of a lot of the delays. SHOW TABLE STATUS will give you an idea of how much index space is associated with your tables. Larger indexes are more expensive to maintain, especially when doing mass-inserts.

Upvotes: 2

Related Questions