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.

I'm looking to do an infrastructure overhaul.Currently I have a ton of small databases scattered amongst our assets and I wish to consolidate them.

Is consolidating all databases to 1 tuned database a good idea? Will I get greater performance if I only have a single database VM on a cluster?

The tuning options I've been looking into are:

innodb_buffer_pool_size=8000M
innodb_flush_method=O_DIRECT
innodb_file_per_table
log_slow_queries=/var/log/mysql.slow.log
thread_cache_size=4
query_cache_limit=1M
query_cache_size=128M
max_connections = 400
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
innodb_flush_log_at_trx_commit=2 

Thank you for your time.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

I would suggest separate instances for your critical production databases and consolidation to a single instance only for the small and less important MySQL databases.

In a consolidated MySQL environment, a single instance with n databases, any database could potentially impact on the others.

Additionally, it would be better to configure appropriately at least the critical databases and that will be easier when you're using separate instances.

Another important point is backup: different storage engines need different backup methods: if you use only transactional storage engines, you could avoid locking during the backup (xtrabackup, MEB, --single-transaction for mysqldump), non-transactional storage engines will require table lock during the backup. You could manage this by using flexible backup procedures, but if you need to generate a consistent backup for replication slave provisioning, for example, it would be more complicated if you have mixed engines across the databases.

share|improve this answer

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.