Had a question for you all because over the past couple of weeks my sites have been getting quite a bit more traffic (probably around 5000+ hits/day overall) and they are all heavily reliant on a central database/table that just exceeds 1 million entries (MySQL) to pull records from.
In addition to pulling records, the table in this DB has to be updated with new/modified entries a minimum of once a day (but more often than not around 2-5 times/day) since the content always has to be correct.
This was working fine, but now, with the increase in traffic, my dedicated server seems to eat up all of its memory and the entire server freezes and I have to have it re-booted.
I am wondering if there is a better way to go about this. I'm at a point where I am not really sure what avenue to pursue and the server is freezing up and has to be re-booted 1-3 times a week now when the server is getting too many requests...almost to the point of it happening every day, over the past 3 days.
Is it appropriate for me to have all sites pulling from the same table in the DB with that many records? Anyone have any advice as to how I could go about setting up/modifying/correcting my current set up?
I just really am not sure what to do at this point.
Any and all advice is greatly appreciated - thank you in advance.
Summary of Server and DB/Table:
Intel Xeon X3330 @ 2.66GHz
4 GB DDR3 RAM
500 GB SATA2
1,100,000 rows (approx.)