Crushing MySQL with traffic / queries???? questions!

What can be done if you're overpowering a dual xeon with 4gb of ram by so much traffic it's literally killing mysql? How much can you actually tweak it? We're talking a single database killing an entire server from traffic overload.

What are methods / ways that are semi affordable for solutions ?

 

Spilt your MySQL on another server through private network

 

Yeah that's what I've been told.. I'm looking at around 1k per month for that setup with load balancer but I trust the company... Guess it costs money to make money!

 

You can do without load balancer as you only have 1 web server & 1 db server

 

you should probably take a look at the code first to determine if it is well written, and take a look at the database design/implementation.

 

Are you serving pages with or without gzip compression? If the pages are currently uncompressed, using gzip will reduce in drastic bandwidth reduction. You can check performance gain for any page at http://www.gidnetwork.com/tools/gzip-test.php

To reduce load on Mysql, implement a cache in php to serve pages without mysql lookup.

 

optimize your code where possible first maybe page caching or sql query catching it's very easy and cheap to get some of your processor back this way.

 

Yes Trying php caching first. Also using Gzip yes.. Bandwidth is not an issue. It's the massive queries.

 

As suggested above, you probably don't need a load balancer at this point
Just 2 servers, one for static and the other mysql.

Have you done any optimization of mysql's config.
That would be the first place I would look

 

Well I'm breaking 60,000 uniques today and around 500,000 pageviews as we speak. Not sure but it's only going up.

 

How big is your database? If you can manage to have it smaller in size than the available RAM, either by increasing RAM, or by deleting and optimising the database, you should be able to serve Select queries without having to read the tables from Disk. This should give you a quantum jump in performance.

 

There are a lot of answers to this question.

1) HOW is mysql killing the server? Memory? CPU? DISK?

2) A lot of database problems are caused by disk and "hot spots"

3) You don't need a load balancer for a separate mysql server simply move the database to another dedicated server and change the php conf info to point to this external mysql server

4) Make sure all your mysql databases are indexed

at 60,000 queries per day that isn't that much, I run an auto-dialers that make over 300,000 queries to a database that has over 3million records each day, but the database is highly tuned and the auto-dialer and mysql server sit on the same box..

It all depends on what the DB is serving up etc..