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.

Is there any harm like eating up resources etc. Or is mySQL engineered to just work for the used ones and keep others at hand. If so, why is there such thing like taking a db offline? Should I do that?

Thanks

share|improve this question

1 Answer 1

up vote 0 down vote accepted

The only aspects to be concerned about is Diskspace and Fragmentation.

MySQL's two main storage engines are MyISAM and InnoDB.

MyISAM

When accessing a MyISAM table, data are always read from the MyISAM table's .MYD file. When an index is being accessing, the index pages are stored in the MyISAM key cache (sized by key_buffer_size). This being the case, if you never access a MyISAM table, index pages are never loaded into memory.

InnoDB

When accessing an InnoDB table, data and index pages that are read are loaded into the InnoDB Buffer Pool (sized by innodb_buffer_pool_size). In this instance, if you never access an InnoDB table, data and index pages are never loaded into memory.

If you have innodb_file_per_table disabled, then all data and index pages would resides inside the system tablespace file ibdata1. Then, have a large ibdata1 with a lot of unused tables could introduce fragmentation that could potentially slow down access to InnoDB tables. If you are using MySQL 5.5/5.6, this should not be a consideration to worry about.

Summary

There are options and structures that can be used to preload index pages (and data pages for InnoDB). Notwithstanding, as long as you do not access those archive tables, you should be OK in all other aspects. To answer your questions, you cannot take a DB Offline and MySQL is engineered to work with what is accessed.

share|improve this answer
    
Thank you! Your answer clearly helped me understand what's going on under the hood. –  cenk Nov 17 '13 at 15:36

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.