We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases?
Here is a little bit information about our current database server:
- MySQL Database
- InnoDB for all tables
- About 80 tables
- Largest tables are: 15 GB, 13 GB, 12 GB, 5 GB, rest smaller than 1 GB
- Size of database on disk is 175 GB with
ibdata1
, and 56 GB without it - Database grows about 10% per month — 12 month ago it was about 5-6%
- Around 60 connections running in normal use
- InnoDB buffer size is 16 GB of total 24 GB and is 99% utilised
- CPU usage is around 30% on a 2.27GHz Intel Xeon 8-core L5520
- We have about 33% write and 66% read
- We have about 2.31 TPS and 1126 QPS according to the below snippet — QPS seems to spike up and down between 750 and 1500
.
use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME = 'QUESTIONS';
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME = 'UPTIME';
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME = 'COM_COMMIT';
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME = 'COM_ROLLBACK';
select (@num_com + @num_roll) / @uptime as tps, @num_queries / @uptime as qps;
Our current service provider offers us the following upgrade:
- Active/Passive Design (2 servers, each as described below)
- Single Processor, Octo Core Intel Xeon E5-2630 v3 2.4Ghz
- 64 GB of memory
- RAID 1; 300 GB 12 Gbps (15K SAS 2.5") x 2
My questions are:
- How do I know if this is an overkill or not? despite the fact that this is obviously a huge upgrade, I want to know if that's what we really need and it won't be wasted resources or unnoticeable performance increase. Any formula, article or book recommendation is appreciated.
- Are there any automated tools that can help us find out how much resources we might need, let's say in a year from now, given our current database details?
Update #1
RIAD Controller
- Dell PERC 6i; RAID 5
- Write policy: Write-through and write-back
- Cache memory size: 256 MB
Best Answer
The short answer: Can't tell. Not enough info.
The long answer...
If your data is growing at 10%/month, it will be about a year before it is 64/24 times as big. So if you grow the RAM and the buffer_pool by 64/24, you are somewhat likely to have the same cache performance of the buffer_pool. After only one year.
The 99% utilized doesn't really say anything other than your working set is at least 16GB. That is not surprising since the dataset is much bigger than that.
30% CPU means that you have an average of 1.3 cores running at any time? If so, then I suspect you have some slow queries. Let's look at them to see if they can be improved. With luck, fixing a couple of queries could delay the need for a beefier machine.
8 cores (versus 1.3) says you are likely to have plenty of cores. But you may outgrow your current 4.
Since you will need to move all the data, I strongly recommend setting
innodb_file_per_table=1
on the new machine before starting the move. (I assume you are using InnoDB.)Upgrade MySQL when you make the move.
Do not set
query_cache_size
bigger than 50M; that may be a source of the high CPU. If you are writing to all the tables "all the time", then it would be better to completely turn off the Query cache. (This may be another way to stall the purchase.)What percent of I/O capacity are you using now?
Will the new RAID have a Battery Backed Write Cache? That makes writes virtually free.
If you want more analysis, please provide
ADDENDA - Review of VARIABLES and STATUS
**Observations
Version: 5.6.13-log 24 GB of RAM You are running on Windows. Running 64-bit version You appear to be running entirely (or mostly) InnoDB.
The More Important Issues
innodb_buffer_pool_size should be increased to about 70% of RAM.
increase innodb_log_file_size to about 300M. Luckily, this is easier as of 5.6.8
Do something about frequently changing databases ("USE"); see Com_change_db, below.
read_buffer_size = 128K
See if it makes sense to clump multiple statements into transactions.
Tackle the slow queries.
Details
( innodb_buffer_pool_size / _ram ) = 12,884,901,888 / 24576M = 50.0% -- % of RAM used for InnoDB buffer_pool ( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((21354175 + 179458342) ) / 1821852 = 110 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size?
( Innodb_log_writes ) = 306,041,138 / 1821852 = 167 /sec
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,821,852 / 60 * 48M / 314550301184 = 4.86 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.
( tmp_table_size ) = 179M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.
( Handler_read_rnd_next ) = 766,168,067,814 / 1821852 = 420543 /sec -- High if lots of table scans ( Select_full_join ) = 29,673,902 / 1821852 = 16 /sec -- joins without index ( Select_scan ) = 70,915,674 / 1821852 = 39 /sec -- full table scans ( Select_scan / Com_select ) = 70,915,674 / 771524414 = 9.2% -- % of selects doing full table scan. (May be fooled by Stored Routines.) ( Created_tmp_tables ) = 75/sec -- Add indexes / optimize queries
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (30050199 + 842 + 264 + 0 + 287315111 + 278) / 1821852 = 174 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2
( Com_change_db / Connections ) = 953,574,484 / 23878 = 39,935 -- Database switches per connection ( Com_change_db ) = 953,574,484 / 1821852 = 523 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc. ( Com_admin_commands ) = 523/sec -- Admin commands -- is some 3rd party cluttering the traffic?
( Threads_created / Connections ) = 1,339 / 23878 = 5.6% -- Rapidity of process creation -- Increase thread_cache_size (non-Windows)
( read_buffer_size ) = 65536 -- http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_buffer_size -- 128K might be better, depending on things that are hard to predict at this point.
ADDENDA 2 - back to the question
Generally, it seems that scaling in the direction you mentioned should work well for you.
One thing worries me... You are at about peak capacity of ordinary drives now. (I see 110 IOPs from InnoDB.) If that grows at 10%, you may run out of I/O capacity first. Your new config helps only a little. Alternatives: RAID-5 (striping and parity, but need at least 3 drives) or SSDs (more costly).