Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 12 down vote accepted

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.

share|improve this answer
    
One thing to remember about innodb_file_per_table is that the methods used to recover disk space are different. With ibdata files you can recover space in the tablespace by deleting off the end of your tables. With individual table files space is not recovered by deleting, one has to rebuild the table with optimize table to return space to the filesystem. Optimize locks the table and so can cause downtime in your application. –  mtinberg Oct 10 '09 at 21:37
add comment

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)

share|improve this answer
add comment

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.