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

Paul White
  • 83,961
  • 28
  • 402
  • 634
tschelik
  • 55
  • 1
  • 2
  • 8

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • When I try this I get an error? I've already done the conversion prior to adjusting the my.cnf. – Rocco The Taco Feb 08 '17 at 11:14
  • Does this work with MariaDb as well? – Rocco The Taco Feb 08 '17 at 11:17
  • @RolandoMySQLDBA If the server has just 2G memory what's your recommendation? – Malus Jan Apr 15 '19 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 '19 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 '19 at 14:25
  • @RolandoMySQLDBA Config file: `[mysql]

    [mysqld] tmp_table_size=2G max_heap_table_size=2G innodb_data_file_path = ibdata1:10M:autoextend:max:512M

    key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M

    [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M

    [mysqlhotcopy] interactive-timeout

    `

     – Malus Jan Apr 15 '19 at 14:26