Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

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

OS

ubuntu 12.04.2 LTS 64 bit server edition

Mysql

version 5.5.29 , 64 bit

My Application Architecture

https://stackoverflow.com/questions/11272776/using-phusion-passenger-nginx-running-same-rails-app-with-multiple-instance

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 = 6553

There are some options you will need to consider
InnoDB Buffer Pool

The reason 26G was picked is that you have 32GB of RAM and 80% of that is 25.6 G. Since you mentioned that you will have 100 databases and 100 applications making this a multitenant DB Server, you are going to have to get the InnoDB Buffer Pool just right.

Please run this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

This will tell you how much space is currently occupied by your MySQL instance. Whatever the total of InnoDB Data Size and Index Size is, that is what you use. If that total is over the 80% limit then you must you the 80% (leaving innodb_buffer_pool_size at 26G).

Since you have a quad-core server, set innodb_buffer_pool_instances to 4.
InnoDB Transaction Log Files

Since 26G was selected as innodb_buffer_pool_size, you are going to need the biggest possible transaction logs. The value 512M was probably picked for innodb_log_file_size because there is nothing to suggest the amount of transaction data (in bytes) that will actually be processed.

To resize your transaction logs

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Next edit my.cnf, replacing

innodb_log_file_size           = 512M

with this

innodb_log_file_size           = 2047M

Then, replace the transaction logs like this

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
service mysql start

After a few months of peak activity, you could then run this query during a peak:

SET @TimeInterval = 300;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL,@MB_WL,@GB_WL;

Based on what comes back, you should resize the transaction logs again.

Please see my earlier posts on doing this:

• Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

• Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?

Multicore Engagement

When the InnoDB Plugin was introduced in MySQL 5.1.38, it set the world of MySQL on fire. Why? Because InnoDB was single threaded. You had to install the plugin to have new setting that allowed InnoDB to use multiple cores.

Rather than writing something lengthy, please read my earlier posts of tweaking MySQL 5.5 to have InnoDB utilitze multiple cores:

• May 26, 2011 : About single threaded versus multithreaded databases performance

• Sep 12, 2011 : Possible to make MySQL use more than one core?

• Sep 20, 2011 : Multi cores and MySQL Performance

StackExchange Database Administrators Q#36465, answer score: 2