I'm working on a site that I believe gets about 70k visits a day and it makes use of a lot of queries which uses UNION's
over about 45 different tables, these tables generally range from about 15k rows to about 500k rows in each table.
We have optimized the query cache
as good as possible but whenever the one of the tables is changed and the data in the cache has to be removed the server starts to lock up and the mysql tmp dir
fills up and the server load goes sky high and the following has to be done to fix the issue:
- Stop mysql (load goes to normal)
- repair the database tables
- clear cache from mysql
- flush memcache
- repair database tables again
- restart mysql
I know replication is probably the best idea, but likely also the most expensive. So wondering if utilizing Sphinx
from http://sphinxsearch.com/ would help out a lot?
I used it before for another site so I could get full-text search on INNODB tables so have a little knowledge how it works, but not sure how it would handle something like this.
Does it utilize it's own caching methods? As wouldn't want to change to this and still get the same issues with Sphinx.
Edit: Below is a screenshot of the EXPLAIN
(missing first line, had issues with screenshot program)
Update: Site only gets 30/k visitors/day now.