Which hardware you suggest?

Question

I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be prepared as good as possible so I will write some statistics of the database usage I think to see every 24 hours.

- 20 000 000 inserts into a table which contains 1 bigint, 7 ints and 1 date.
-  5 000 000 updates on above table
- 10 000 000 inserts into mapping table 1 bigint 1 int
-  2 000 000 inserts into table with 3 ints
-  5 000 000 select queries with WHERE pk reference of first table described above

I will use the MySQL 5.5 database with all InnoDB tables. At the moment I am thinking about buying a dedicated server with these stats:

Intel Xeon E3-1220LV2 2.3GHz
100 Mbps Uplink
4 GB DDR3 Memory
500 GB RAID-1 Drives
10 TB Bandwidth
Up to 5 Dedicated IPs

For a price of $140 dollar a month. What do you think, will this be enough? Would it be overkill? You know a better hosting? At the moment I am on a shared webhost ($12 a month) and I get a fifth of the numbers above, but the shared database is really not optimized for innoDB.

Answer 1

If you are honestly expecting to have the numbers you say for an all-InnoDB database, you are going to be needing a lot more memory. Here is why:

InnoDB caches data and index pages in the InnoDB Buffer Pool. If you will be doing heavy reads, a large buffer pool is your best friend. Since most people recommend 75-80% of RAM for a dedicated MySQL DB Server, that would about about 3200M (3.2G).

I would go the following (32GB or RAM)

• innodb_buffer_pool_size = 24G
• innodb_buffer_pool_instances = 4
• innodb_read_io_threads = 16
• innodb_write_io_threads = 16
• innodb_file_per_table = 1

There are too many other aspects for me to just put in the answer. Here are some of my InnoDB posts:

• Possible to make MySQL use more than one core?
• How large should be mysql innodb_buffer_pool_size?
• How do you tune MySQL for a heavy InnoDB workload?
• Clarification on MySQL innodb_flush_method variable
• Is it safe to use innodb_flush_log_at_trx_commit = 2
• My Other InnoDB Posts

Don't forget to tune the per-connection settings and multiply the sum of them by max_connections : See my post Understanding mysqltuner Recomendations w/ Unused Catalogs