在MySQL 5.6中，我們在單個虛擬化伺服器*（4 cpu 和 8GB 記憶體）上擁有大量（2,500 多個）數據庫和使用者。所有表都使用InnoDB*。實際數據量不是很大*（~5GB），每秒查詢量很低，只有很多小型數據庫和使用者。為我們的應用程序創建的每個帳戶都有自己的數據庫和使用者（不幸的是，這種架構不能改變）*。

我們看到性能非常差，尤其是在嘗試停止 mysqld 時*（通常只是超時）。啟動 mysqld 和生成（準備連接）*需要 3 多分鐘。執行 mysqldump 會使整個伺服器癱瘓。負載、CPU 使用率和 iowait，在 mysqldump 期間都會飆升，並導致其他查詢超時和失敗。

innodb_file_per_table由於打開的文件描述符數量過多、目錄和文件的數量過多以及 i/o 操作，建議我們停止使用。

更改innodb_file_per_table為 off 可以做到這一點，但是我們如何轉換所有現有的數據庫和表以利用它呢？

以下是我們目前/etc/my.conf的僅供參考，以防我們可以針對許多小型數據庫進行進一步優化。

[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

第1步

在 /etc/my.cnf 中將 innodb_file_per_table 設置為 0

[mysqld]
innodb_file_per_table = 0

第2步

service mysql restart

第 3 步

再次將每個 InnoDB 表轉換為 InnoDB

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

一旦您查看他的腳本並感到滿意，請登錄到 mysql 並執行它

mysql> source /root/ConvertInnoDBToInnoDB.sql

結語

所有.ibd文件都將消失，所有 InnoDB 表和索引都將存在於 ibdata1 中

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/61116