Mysql – Is the.cnf for thesql 5.5 is suitable and well tuned for below hardware spec

database-tuninginnodbmy.cnfMySQLmysql-5.5

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

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

Update

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.

Best Answer

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

Related Solutions

Thesqld – cannot connect to socket after changing datadir location

The problem is the mysql client program configuration. You can specify the --socket=/NEW_DIR/mysql/mysql.sock command-line option or add it under the [mysql] section of your my.cnf file.

Please look at the documentation for specifying options through option files: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

Since changing the socket path affects all the client programs, then it will make sense to update the global my.cnf and set the socket option under the [client] section.

MySQL – Master Binlog Corruption

Surprisingly, that's not gibberish.

That indeed appears at the top of binlogs whenever you do mysqlbinlog to a binary log generated using MySQL 5.1 and MySQL 5.5. You will not see that gibberish in binary logs for MySQL 5.0 and back.

This is why the start point for replication from an empty binary log is

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

This is good to remember if you do MySQL Replication where the Master if MySQL 5.1 and the slave is MySQL 5.0. This could present a really big headache.

Replication from Master using 5.0 and Slave using 5.1 works fine, not the other way around.(According to MySQL Documentation, it is generally not supported for 3 reasons: 1) Binary Log Format, 2) Row-based Replication, 3) SQL Incompatibility).

Anyway, do a mysqlbinlog on the offending binary log on the master. If the resulting dump produces gibberish in the middle of the dump (which I have seen a couple of times in my DBA career) you may have to skip to position 98 (MySQL 5.0) or 106 (MySQL 5.1) or 107 (MySQL 5.5) of the master's next binary log and start replicating from there (SOB :( you may need to use MAATKIT tools mk-table-checksum and mk-table-sync to reload master changes not on the slave [if you want to be a hero]; even worse, mysqldump the master and reload the slave and start replication totally over [if you don't want to be a hero])

If the mysqlbinlog of the master is completely readable after the top gibberish you saw, it is possible the master's binary log is fine but the relay log on the slave is corrupt (due to transmission/CRC errors). If that's the case, just reload the relay logs by issuing the CHANGE MASTER TO command as follows:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='< master-host ip or DNS >',
MASTER_PORT=3306,
MASTER_USER='< usernmae >',
MASTER_PASSWORD='< password >',
MASTER_LOG_FILE='< MMMM >',
MASTER_LOG_POS=< PPPP >;
START SLAVE;

Where

MMMM is the last file used from the Master that was last processed on the Slave
PPPP is the last position used from the Master that was last processed on the Slave

You can get MMMM and PPPP by doing SHOW SLAVE STATUS\G and using

Relay_Master_Log_File for MMMM
Exec_Master_Log_Pos for PPPP

Try it out and let me know !!!

BTW running CHANGE MASTER TO command erases the slave's current relay logs and starts fresh.