My Hardware Spec
Technology: Ivy Bridge CPU: Intel Xeon E3 1245v2 Intel Smart Cache: 8MB Cores / Threads: 4 / 8 Frequency: 3.4GHz+ / 3.8GHz Turbo Boost RAM: 32 GB DDR3 Hard disk: 2x 2TB SATA3 Raid 1 by software Bandwidth: 100 Mbps guaranteed
ubuntu 12.04.2 LTS 64 bit server edition
version 5.5.29 , 64 bit
My Application Architecture
So my server contains 100 rails apps with 100 mysql databases, each database having around 100 tables
My Traffic To The Server
10 to 200 (max) request per second
3000 to 10,000 (max) request per day
Connectivity Between Mysql and Rails
I use sockets
socket = /var/lib/mysql/data/mysql.sock
my.cnf generated by https://tools.percona.com/wizard for the above hardware spec
[mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/data/mysql.sock [mysqld] # GENERAL # user = mysql default_storage_engine = InnoDB socket = /var/lib/mysql/data/mysql.sock pid_file = /var/lib/mysql/data/mysql.pid # MyISAM # key_buffer_size = 32M myisam_recover = FORCE,BACKUP # SAFETY # max_allowed_packet = 16M max_connect_errors = 1000000 skip_name_resolve innodb = FORCE innodb_strict_mode = 1 # DATA STORAGE # datadir = /var/lib/mysql/data/ # BINARY LOGGING # log_bin = /var/lib/mysql/data/mysql-bin expire_logs_days = 14 sync_binlog = 1 # CACHES AND LIMITS # tmp_table_size = 32M max_heap_table_size = 32M query_cache_type = 0 query_cache_size = 0 max_connections = 500 thread_cache_size = 50 open_files_limit = 65535 table_definition_cache = 4096 table_open_cache = 10240 # INNODB # innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 26G # LOGGING # log_error = /var/lib/mysql/data/mysql-error.log log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /var/lib/mysql/data/mysql-slow.log
I have seen that innodb_buffer_pool_size = 26G, so in 32 GB RAM at least 27 to 28 GB will used by mysql, so there is only less space for running 100 rails apps, so please somebody will edit and optimize my mysql config my.cnf posted in pastebin, to get enough space and speed for running both mysql and rails application.
I planned to use 100 databases connecting with 100 rails application in a single machine, so i use mysql sockets for connectivity, whether will have performance improvement compared to connecting using mysql with ports.
More info about application
My application is not an mission critical application, it's just an student information system for schools. Mysql backup to remote ftp scheduled through webmin at 2 times per day , so i planned to put 100 schools in single server, so 100 rails apps + corresponding 100 mysql databases
Currently the application is not running , now i am at production set-up stage, so i need to have some suggestions for my.cnf considering equal resource handling for rails and mysql.