Mysql – How to calculate how much hardware resources you need for a database

data-warehousehardwareMySQLscalability

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:

  1. 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.
  2. 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

SHOW VARIABLES

SHOW GLOBAL VARIABLES

SHOW GLOBAL STATUS

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

RAM size (currently 24GB)
SHOW VARIABLES;
SHOW GLOBAL STATUS;   -- STATUS, not VARIABLES.

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.

  • You seem to be running pretty well now.
  • I itemized several things that will help you live for a short while with what you have.
  • A tough one: getting rid of the unnecessary 3rd-party-isms (change_db, etc)
  • A tough one: locating the slow queries and improving them
  • You already have a recent 5.6, so handling more connections on the 8 cores should be easy.
  • There is a lot of InnoDB I/O, but nothing to indicate that the cache is "inefficient". With more ram, the most important thing to change will be innodb_buffer_pool_size (to 70% of available RAM).
  • If you grow by 10%/month, a year or two from now, you will be happy to have that "huge upgrade", and not have to upgrade again.

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

Related Question