Home / server / Questions / 3952
Accepted
Jacco
Asked: 2009-05-05 04:34:42 +0800 CST

What should I know before going live with an InnoDB database?

  • 772

What things should a server- or DB-admin know and do before making a InnoDB database available.

I'm thinking about things like setting correct values for innodb_buffer_pool_size and innodb_log_file_size

So what simple things could you forget to configure that would cause the application using the database to work less that optimal?

mysql innodb

2 Answers

  1. Best Answer

    There are some configuration options you should think through before you start.

    As long as you're not using myisam at all, you can safely allocate almost all (keep enough to have your OS running comfortably, and enough for your max_connections) your memory to innodb_buffer_pool. The nice thing about InnoDB is that is handles almost all of the memory stuff by itself, no need to separate stuff like query caches, key buffers etc.

    I would recommend that you enable innodb_file_per_table, simply because it makes it a lot easier to just browse the file system and see how much space the different tables and databases need. You will still need a generic ibdata file for InnoDB internal use, but you can just define it as 10M:autoextend. No need to define a lot of different innodb data files with pre-allocated sizes.

    innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones. If you don't (and you shouldn't [1,2]), there is really no need to bother a lot with it. Check MySQL Performance Blog for a detailed report on how to calculate.

    And when you've run your MySQL for a while, check your settings with MySQLTuner or MySQL Tuning Primer.

    For a more detailed report, try mysqlreport, and for live monitoring, check mytop.

    • 14

  2. One slight "surprise" to me was that innodb uses, by default, one file for all databases/tables. As opposed to MyISAM tables that uses a directory per database and files per table/tableindex.

    That might matter if you're used to shrink the physical files on a table basis (optimize table xxx)

    • 2