I'm running an advertising server and I need to upgrade to a new VPS/Dedicated Server as the database needs to repaired approximately once/week.
When running during peak hours the adserver has to handle around 50 visits per minute (and all visits would result in a MySQL row being created). The current database I have is approximately 200MB, and would probably be 1GB when on a new VPS/Dedicated server for a couple days.
I plan on using horizontal partitioning to help increase speed/performance, but how powerful of a VPS/Dedicated server would I need to handle this amount of server load?
Personally I'd go for a mid-range VPS or even a powerful shared account. 50 visits per minute isn't a huge amount of load, although your SQL overheads would create a bit more load. The thing with a VPS is you can always upgrade or downgrade without any downtime should your needs change. This can't be said for a dedicated server.
What sort of package are you serving the content from now? Are you on a VPS or shared? Is it an off-the-shelf package that's serving the ads or is it something custom?
Quality VPS Plans - Instant Setup - True 24/7 Support - Friendly Service
Including Trixbox - host your own VoIP PBX!
Looking at your usage, you might want to go for at least for a gigabyte of RAM, if not more, as it will definitely improve your database access speeds.
Personally, I'll go for a dedicated server. One of the least controlled aspects is disk IO, which is really important for a database server. I certainly won't want others to potentially affect my database operations because of heavy read/writes by another user.
A VPS with 1000GHz CPU, 1024MB + Ram will be fine.
If you can opt for more RAM it will be better.
Softaculous - Auto Installer for cPanel, Direct Admin, InterWorx, Plesk, H-Sphere
The only Auto Installer that installs 260+ scripts. Install in just ONE STEP! Virtualizor - VPS Control Panel supporting OpenVZ, Xen, KVM and has 60+ OS Templates
Webuzo - Softaculous for the Cloud i.e. Softaculous Standalone