4

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

Improve this question

2 Answers 2

Reset to default
2

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:

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:

Improve this answer
2
  • Thanks for you suggestion, I planned to give equal priority to ruby on rails and mysql, on the above scenario i have only less ram for running rails , so can i reduce innodb_buffer_pool_size to 18G
    – Sam
    Commented Mar 12, 2013 at 16:11
  • Can you please check my updated post
    – Sam
    Commented Mar 13, 2013 at 7:03
1 
# BINARY LOGGING #
log_bin                        = /var/lib/mysql/data/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# 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

You don't want your logs in you data directory. This will show a "mysql-bin" database when you start mysql. You'll probably get some unneeded errors in the error log on startup. Best to keep that as clean as possible so you can pay attention when something does show up there.

It's general convention to put logs in /var/log anyway. Further, when possible it's nice to have the logging file system on a separate set of spindles than the data directory. While it doesn't sound like that would be possible there could still be some gains in keeping the data directory and logging on different file systems, even if still on the same underlying disks.

Be aware that expire_logs_days will purge binary logs regardless of weather slaves are up to date. Granted if you have a broken slave that's been off for more than 14 days you have larger problems. In general you'll want to keep enough binlog buffer around so that you can easily spin up a new slave based on your backup schedule.

You might consider having an archiving script that offloads the binlogs to a more permanent archive server and manages the purging.

Improve this answer
1
  • Can you please check my updated post.
    – Sam
    Commented Mar 13, 2013 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.