Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to convert some (not all) the MyISAM tables to InnoDB. Here is my current my.cnf: pastebin.com/FW8YXtLQ

What would be the best configuration? (need to add InnoDB settings, byecause there are no InnoDB settings in my my.cnf)

share|improve this question
    
Please post output of SHOW ENGINES; in your question. –  RolandoMySQLDBA May 9 '13 at 20:54
    
pastebin.com/tzgMFbmA –  Tarik Čelik May 9 '13 at 20:56
    
Is there a reason why you do not want to convert every table to InnoDB ? –  RolandoMySQLDBA May 9 '13 at 20:57
    
How many CPUs/Cores in the DB Server? –  RolandoMySQLDBA May 9 '13 at 20:58
    
It has 2 6core Xeon processors (2 threads per core == 24 threads) –  Tarik Čelik May 9 '13 at 21:01

1 Answer 1

up vote 2 down vote accepted

You mentioned before you are running

  • MySQL 5.5.29
  • 16 GB RAM

Here are some good settings to start with

innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Don't do the conversion yet !!!

Put there settings in my.cnf, then do the following

service mysql stop
cd /var/lib/mysql
rm -f ibdata1 ib_logfile*
service mysql start

Now, you can do the conversion.

Give it a Try !!!

UPDATE 2013-05-10 10:44 EDT

Here are my past posts on tuning InnoDB for better CPU usage

Here are my past posts on tuning InnoDB

share|improve this answer
    
Now i get an "start: Job failed to start" –  Tarik Čelik May 9 '13 at 21:12
    
Look in your mysql.err. It should give you more details exactly. Rolando's rm mentioned a ib_logfile, that likely should have been two files ib_logfile0 and ib_logfile1. –  atxdba May 9 '13 at 22:39
    
I missed an asterisk on ib_logfile. It should be ib_logfile* –  RolandoMySQLDBA May 10 '13 at 1:19
    
Did my answer help ? –  RolandoMySQLDBA May 10 '13 at 16:35
    
Everything works like a charm now. :) The table doesn't get corrupted anymore. Everything is in RAM. :) –  Tarik Čelik May 12 '13 at 19:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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