Performance bottlenecks with thousands of small databases

[Justin Keller]

We have a large number (2,100+) of databases and users in MySQL 5.6 on a single server. All the tables are using InnoDB. The actual data size is not very large (4.6GB), 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.

The server hardware is virtualized (4 cpus, 8GB memory, SSD disks (40GB total)).

Any specific my.cnf configuration options we can tune for a large number of small databases and users? We've done a decent amount of optimizations and tuning already. Not sure what else we can do. I think even upgrading the server to 8 cpus and 16GB would not help.

Here is our current my.cnf:

[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.X.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=4M
query_cache_size=512M
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=1
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



Edited 2 time(s). Last edit at 03/11/2014 10:25PM by Justin Keller.