2

I want to convert some (not all) the MyISAM tables to InnoDB.

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

show engines

8

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

  • When I try this I get an error? I've already done the conversion prior to adjusting the my.cnf. – Rocco The Taco Feb 8 '17 at 11:14
  • Does this work with MariaDb as well? – Rocco The Taco Feb 8 '17 at 11:17
  • @RolandoMySQLDBA If the server has just 2G memory what's your recommendation? – Malus Jan Apr 15 at 14:04
  • @MalusJan default values are sufficient when memory is at a premium. The OS needs memory more than the DB when the amount of RAM is low. Personally, I would start with 512M for innodb_buffer_pool_size and tune conservatively from there. – RolandoMySQLDBA Apr 15 at 14:21
  • @RolandoMySQLDBA I start working on a web application which is hosted in linux (ubuntu) with 2G RAM and 2 CPU. but the mysqld process get about 70% of the memory and do not release it. There is a backup process (mysqldump) run each midnight which takes lots of memory. I want o know if there is any way to release the memory taken by mysqldump or optimising the mysql.cnf (MySQL 5.7)? I'll put the config file in next comment. Thanks – Malus Jan Apr 15 at 14:25

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.