My little web site over the past 5 years has grown from 1 static page into a monster. I now need some advice on how to proceed.
The web site helps create stats for BOINC projects. For those who dont know BOINC is a Berkeley University distributed computing platform helping world research projects get CPU time for the mathematical modeling.
Each BOINC project exports XML files daily with host, user and team data. My site grabs those XMLs, parses into MYSQL databases, adds a few fields (like rankings) so not to have to calculate them... then displays dynamic PHP pages for anyone who is interested.
Problem is as time goes on (BOINC been going about 2 years now) the files get bigger and bigger, MYSQL database bigger and bigger etc etc. Right now the largest XML file is nearly 200Mb gzipped which creates approx 1.5Million rows in one particular DB.
There are 40 projects, each project has 8 tables in the database... then there are "combined" tables as one user may be in many projects (and people like "combined stats").
The MYSQL database is approx 3.6Gb, over 320 tables, 65 million rows.
The updates to these tables happen twice per day as cron jobs.
Apart form this there is not much else on the box. The php stats pages run off the above DB, a Joomla site relating to BOINC and BOINC info, one PHPBB board (10 active users at any one time) and maybe 3-4 other tiny static web pages. The box typically gets 15000 uniques per day, 200K page hits and approx 2.4Gb traffic per day.
Right now we have been managing on a 784Mb split share Xenon VPS (just) but the update scripts have been going slower and slower. Now were at a point where the script that updates the combined user table (1.5 Million rows, 10 fields including calculated ranking fileds) takes about 3 hours! Need something bigger.
Anyway... I am by no means a low level expert in networking issues... I fumble along like most people. I will probably end up getting someone to do the security troubleshooting for me... though most simple things I can handle through some application like WHM/Cpanel. I dont plan to resell or do anything else with this box.
So comments would be wonderful. Unfortunately price will always be an issue for me. I started running this site to see if I could make it years ago... and it just kept growing. I make minimal money from Google ads and donations, and all things I provide on the site are free for anyone in BOINC projects (and always will be free). So high quailty solutions without an unlimited budget (as I will be financing it all for the love of it). If the site goes down for a few hours here or there... geez even a day or two its NOT the end of the world... like I said... I run this as a hobby/passion... but people understand I do actually have a real day job and this is something I do in my spare time.
I wouldn't go with something like cpanel/WHM if your issue is speed. I would go with something lighter like direct admin. I would try optimizing your mysql configuration and maybe add more memory to your system? If you want to move out of your vps, which looks like it would be a good idea, layeredtech has nice deals on servers. Like someone else mentioned, whats your budget for this do you think?
I am a realist in that respect... I know the more you pay (if you shop around) the better the hardware and performance (that said there are quite a few over priced systems out there... so shopping around is the key).
I suppose I would go uptowards $150-200US... and depending on the sytem pay up towards $250 (maybe). Much higher than that and I am really starting to strech my personal household budget just to keep this site going for the wider BOINC community. Like I said earlier... if I can get away with less without compramising things I would love that option.
I hear you on cpanel/WHM. Its the only panel I have known (over the last 5 years) which is why I got it on the current VPS. Though I am not "attached to it" and would be happy to use any system and change system if it is going to mean a performance gain.
Right now the MYSQL is as optimized as I can get it and that pretty much has been the focus of my brain power over the past 2 years. But even the simple INSERT INTO SELECT statements are starting to be painfully slow (with appropriate indexes etc)... as some are on 18M row tables.
Well with your current system requirements I would suggest somthing like a single processor Pentium 4, Pentium D, or the new Core 2 Duo. As they will provide enough cpu power to handle the xml and database queries. You may also want to consult the idea of getting a single Xeon or OPteron system where you can add a second cpu at a later date without transferring to a new system.
That will allow you to save money of front, but expand if you need to in the furture without mobing everything to another server. You may wish to go with more than a gig of ram, but 1gig shopuld be suffient for time being and can always be upgraded in the future.
Im not sure you disck space requirements, but a SATA based system will be good, but if you require extremely fast response time from you hard drive you may want to consider upgrading to SCSI drives as they run at a much higher soindle rate and data rate.