Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you using MySQL 5.5 or 5.6 ??? –  RolandoMySQLDBA Jun 10 '13 at 22:12
1  
If you are unsure about whether or not a question like your belongs here, you can post it in meta.dba.stackexchange.com and see if moderators and seasoned power players are OK with it. They won't answer the actual for you there, just render subject/content opinions in line with Question Content Policies. If it's favorable, then post in dba.stackexchange.com –  RolandoMySQLDBA Jun 10 '13 at 22:36
1  
@KevinVermaat check Swiftway. Negotiate with them, do not just sign. –  TomTom Jun 11 '13 at 8:00
3  
You want to go live this weekend and don't have the hardware? You must be joking... –  dezso Jun 12 '13 at 12:00
1  
(Don't take it wrong - this is an important question and how Rolando answered it shows that this is also answerable (not really fitting the sites Q&A standards though) - I'm just wondering if you are/were perfectly calm about the hardware issue when you posted your question. I would never go your way, I think :) –  dezso Jun 12 '13 at 12:42

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
+1 for now,thanks a lot so far. I did a small update regarding the selects I would use. So that you have a better understanding about the selects vs dml I use. –  Kevin Vermaat Jun 10 '13 at 22:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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