How can we determine which provider is suitable for our huge MYSQL database?

For our online service we need lots of database space. Currently we have about 900MB
of used space. Our current premium (fast!) host only supports up to 1GB. After that it's dedicated; which
will cost about € 400 each month. As this is donationware, we can't afford this. We can afford
about $1200 each year for hosting. Not much more.

So we are looking for a new host. With a few GB of MYSQL space. We don't need huge normal
file storage.

We've found hostgator.com, they say their MYSQL has no limit. Which would be ideal.
But we are afraid that their MYSQL and connections (?) aren't fast enough.

About speed. We can have up to a thousand users working simultaniously on our db. This db
has about 10.000.000 records. We want to be ready for the future: the number of users grows
every day.

So my questions are:
- are our assumptions regarding speed and number of connections right?
- what would you recommend we should do?
- what would be possible bottlenecks for a new provider?
- are there ways to check mysql speed and number of connections up front?
- how can we determine which  provider is suitable for our needs?
- would a cheap host be a bad mistake?
dwaxAsked:
Who is Participating?
 
tsmgeekCommented:
again all depends what you are doing, if all your searches are on indexes then CPU/RAM speed is the biggest factor, if your indexes cant fit into RAM/your queries cant use indexes/or you are doing large sorts which happen on disk somtime  then you will be reading disks a lot instead

check your 'Key Efficiency as that broadly determins if most of your lookups are happening on indexes
have you looked at mysql-primer.sh etc to work out if your current server is tuned correctly and its efficiency

that sort of load seems very low to me so nothing very powerfull
our server at work averages 400qps on weekends and peeks 5000qps, during the week its higher. Ok our server runs 4 core xeon 2.4Ghz and 12G of ram 6x72G10k SAS RAID10, lots of our indexes are running at full speed from ram, we are IO bound as the disks are not fast enough for full table scans etc.. our DB is a total of 50G with about 6Gb of indexes

its been tweeked and prodded and we work out that if we stil some SSD disks or 15k disks then we will be able to extend the servers lifecycle by a fair ammount

mysql tuning can somtimes be a dark art

have you looked at VPS servers, good thing with thoes is that you start with a low spec and with a simple change they give your server more CPU cycles and RAM allocation plus disk space, grow as you require.
i use servint in the US for my VPS units, they have been ubber stable in the last 2 yrs, softlayer are also very competitive
0
 
Lee_YCPCommented:
See the articles about screening, getting the most bang for the buck, trade-offs and chosing a provider toward the bottom of the page at:
http://www.top10hostinglist.com/?gclid=CN-04o_h-6cCFcfsKgodN3rcqw
0
 
tsmgeekCommented:
all depends on DB load, 1G is small by my level
what you need to know is things like, how many concurrent connections, queries per second average and max, total index space, are all queries optimised to use indexes or else you will be doing full table scans which are slow, few graphs of db/cpu/disk/mem usage currently would be usefull
without all the above its hard to know the type of db load you have, could quite easly support all of that on a reasonable vps, you say 1000 users at the same time but are they all issuing 1000 queries every second or is is say 100 per second etc, so many vairables
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
dwaxAuthor Commented:
@tsmgeek

Thanks for your reply.
I've done a bit of research and here are some figures.

server settings:
MySQL version      MySQL 5.1/64bit
Max. concurrent connections      102

mysql data for our site:

no of queries per second
update-insert between 20 and 120 (peaks)
select around 10-30

no of rows per second
fetched rows 200- with peaks to 3500
updated rows around 100

date send recieved from mysql in kb's
recieved 2-7 kb with peaks to 15kb
send 5-10 with peaks to 35 kb
0
 
dwaxAuthor Commented:

One more question:
Does the type of hardware also matter in MYSQL speed? Or is this negligible?
0
 
dwaxAuthor Commented:
Thanks tsmgeek for taking your time to respond.
I've been in contact with servint, googled a bit on experiences, and it seems very promising!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.