3

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large (~5GB), and queries per second is low, just lots of small databases and users. Each account created for our application get's their own database and user (this architecture cannot change unfortunately).

We are seeing very poor performance, especially when trying to stop mysqld (often times it just times-out). Starting mysqld and spawning (ready for connections) takes over 3+ minutes. Running mysqldump brings the entire server to its knees. Load, CPU usage, and iowait, all skyrocket during mysqldump, and cause other queries to timeout and fail.

It was recommend that we transition off of using innodb_file_per_table because of the excessive number of open file descriptors, shear number of directories and files, and i/o operations.

Changing innodb_file_per_table to off does this, but how can we convert all existing databases and tables to utilize this?

Below is our current /etc/my.conf just for reference, in-case there are further optimizations we can make to optimize for lots of small databases.

[mysqld] datadir=/mysql/data socket=/mysql/mysqld.sock symbolic-links=0 default-storage-engine=InnoDB slow_query_log=1 long_query_time=2 slow_query_log_file=/var/log/mysql_slow.log expire_logs_days=30 max_connections=50 bind-address=192.241.X.X port=3306 max_allowed_packet=4M net_retry_count=5 max_connect_errors=100 wait_timeout=14400 connect_timeout=10 open_files_limit=65535 innodb_open_files=65535 key_buffer_size=256M innodb_buffer_pool_size=4096M innodb_log_buffer_size=4M group_concat_max_len=16k max_sort_length=16k max_length_for_sort_data=16k query_cache_type=1 query_cache_limit=1M query_cache_size=64M innodb_thread_concurrency=8 thread_concurrency=8 thread_cache_size=128 thread_stack=1M read_buffer_size=1M join_buffer_size=1M sort_buffer_size=1M read_rnd_buffer_size=1M table_open_cache=32768 table_definition_cache=65535 tmp_table_size=33554432 max_heap_table_size=33554432 innodb_log_file_size=1024M innodb_flush_log_at_trx_commit=2 innodb_file_per_table log-warnings innodb_flush_neighbors=0 user=mysql old_passwords=0 [mysqld_safe] syslog pid-file=/var/run/mysqld/mysqld.pid
Improve this question

2 Answers 2

Reset to default
3

STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld] innodb_file_per_table = 0

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql MYSQL_CONN="-u... -p..." SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')" SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'" mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

Give it a Try !!!

Improve this answer
5
  • Thanks, a little worried about running this on production with over 2,500 databases and 30,000 tables. Not even sure if this is going to help with our root problem. In fact, going to a single ibdata file may degrade performance further instead of improve it. Thoughts? Commented Mar 19, 2014 at 3:54
  • @Justin, so, make a test server, restore mysqldump on that, and try it there before doing same to production? Commented Dec 7, 2014 at 22:53
  • @MatijaNalis please notice that Justin asked this question 9 months ago. He would already have done this. Commented Dec 7, 2014 at 22:55
  • You can also split ConvertInnoDBToInnoDB.sql to many smaller chunks and run them one by one if you like. As for performance, you could alternatively play with open_files_limit, table_open_cache, table_definition_cache, innodb_open_files -- but still preferably on test server Commented Dec 7, 2014 at 22:59
  • @RolandoMySQLDBA my bad, premature "enter" commited comment before I copy/pasted (hopefully more useful) mysql options - not necessarily for the original poster (who hopefully solved his problem by now), but for other people with similar problems which may come upon the question in the future. Commented Dec 7, 2014 at 23:03
0

The above solution was not working on MariaDB 10 for me. The ibd files were not removed. Our server has over a million tables in it so going using INFORMATION_SCHEMA.TABLES is pretty slow. You can use INFORMATION_SCHEMA.INNODB_SYS_DATAFILES to rip out just the ibd table spaces To convert only the InnoDB tables that are actually in separate files use the following:

echo "SET SESSION old_alter_table=1;" > ConvertInnoDBToInnoDB.sql MYSQL_CONN="-uroot -p" SQL="SELECT CONCAT('ALTER TABLE ',REPLACE(REPLACE(REPLACE(PATH,'./',''),'.ibd',''),'/','.'),' FORCE;')" SQL="${SQL} FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH NOT LIKE '%mysql%'" mysql ${MYSQL_CONN} -AN -e"${SQL}" >> ConvertInnoDBToInnoDB.sql mysql> source /root/ConvertInnoDBToInnoDB.sql
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.