0

We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB might show because we want to fully maintain integrity of our data.

Below is .cnf file.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table

server-id=9508071
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=datacv


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
  • if you have PK, what is the var type? – jcho360 May 17 '12 at 13:25
  • yes I have pk's a lot of them too. I use int. What else should I look out will there be any index errors like myisam? – newbie14 May 17 '12 at 17:33
1

MyISAM tables sometimes get corrupted in a power failure. REPAIR TABLE is the remedy.

InnoDB checks and repairs all its tables after any crash.

If a crash occurs in the middle of, say, a multi-row UPDATE in MyISAM, some of the rows will be updated, some won't. In InnoDB, the entire "transaction" will be automatically rolled back. That is, it's all or none.

Check your indexes when moving: http://mysql.rjweb.org/doc.php/myisam2innodb

  • for this new installation is a fresh one with purely only innodb tables so what errors must I be on look out? Is there any special settings I should do for innodb? – newbie14 May 18 '12 at 16:16
  • Tuning: mysql.rjweb.org/doc.php/memory . Errors: not likely to be any. Inefficient indexes: see my other link. – Rick James May 23 '12 at 19:30
  • is there any other link on what best to tune for innodb I saw more talking bout innodb buffer some say set it to 80% of ram and some say 10% more then you data size. Your link say 70%? I have edited my question with the details of .cnf? What is your opinion on this? What else I should add or remove? – newbie14 May 24 '12 at 13:32
  • Swapping is very bad for mysql. 80% leads to more risk of swapping. If your data is small, then data+index+10% (approx) is all that is needed. After editing my.cnf, you need to restart mysqld. Other things?--see my tuning link (/memory). – Rick James May 29 '12 at 21:16
  • so I will go with 70% that should be the best based on your experiences rite. – newbie14 Jun 1 '12 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.