2

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.

Kevin Vermaat
  • 123
  • 6

1 Answers1

3

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)

There are too many other aspects for me to just put in the answer. Here are some of my 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

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536