Webhost "Can't effectively serve small DBs and large DBs from the same MySQL server"

[feldon30]

So I've been having increasing performance issues in the past few months as one of the websites on my dedicated server has grown in popularity. The large database is 11GB. There are times when the server is going absolutely bat**** crazy with loads in the 20's, yet running "top" usually shows that the CPU is mostly idle. I fire up the list of MySQL Processes, and it's rare that there is one definitive query that is holding up the show.

I am running a cPanel/WHM CENTOS setup with MySQL 5.0.x (Yes it's ancient, and an upgrade is imminent), PHP 5.3, and Apache 2.2 on a dedicated Xeon quad core box with 4GB of RAM and 250GB hard drive.

So here's the issue. I contacted my host and here's the relevant passages from some of the ticket responses they've been sending me. I indicated that I intend to do full server backups very soon, and then do a MySQL 5.0 -> 5.1 -> 5.5 upgrade, but first wanted their advice on any other problems they saw before I do so...

The database is currently 11G which the server is not optimized for. How did you want MySQL optimized? It appears that the server is optimized for smaller databases.

and later

f you do decide to have your server optimized for a larger database this can cause performance issues with small databases on the server. See if you can check the tables and see if there's anything that you can take out to decrease the size otherwise the server will continue to have issues with it due to the server being optimized to handle smaller databases. Another option is because your site does have a large database and a lot of traffic you could move that domain to it's own environment that will be optimized for a larger database if you wanted.

The hard-drive wouldn't be the reason why the database is currently having issues, it's only because the server is optimized to handle smaller databases. The hard-drive doesn't appear to be an issue. In order to ensure that this issue does not persist you will either need to decrease the size of the database or move your database to it's own separate Dedicated server. Currently, dev.eq2wire.com is using a large database and high amount of bandwidth which is why MySQL is having issues right now. Once the database is smaller or that domain is on it's own server the issue should be resolved. Other than that I'm not seeing any other issues on the server.

Also, to the idea of me upgrading the server to MySQL 5.5 with the automated cPanel scripts, they had this:

We highly recommend having us upgrade MySQL to 5.5 for you to make sure it is upgraded properly and without any errors.

So has anyone heard of this concept that a MySQL can be optimized for LARGE databases or SMALL databases, but cannot effectively serve both?

I could understand if the server I have is inadequate. I cannot understand that I would need two weaker boxes rather than one strong box. Shrinking the database is, of course, not an option (strangest suggestion I've ever heard). What would you do at this point?

 

[D.O.A.]

how big is your search index? mine was in the GB's. more ram and try elasticseach? upgrade mysql. tried memcache? ditch the background panel and try nginx or something if you're comfortable with command lines... postfix/dovecot can do email easily enough, roundcube will read it. Cut some backend bloat on the server first, imo, unless you really need it.