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:

  1. Stop mysql (load goes to normal)
  2. repair the database tables
  3. clear cache from mysql
  4. flush memcache
  5. repair database tables again
  6. 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.

enter image description here

  • Sphinx has it's own index that must be rebuilt periodically, so you won't have ideal data consistency at all time (but I see that you already use caching via memcache so this is not a problem). Sphinx does store the information in it's own database so your mysql server would catch a breath after you implement it. – wojciechz Aug 23 '12 at 9:42
  • Why do you need to repair the database tables every time? What storage engine do you use? – Alex Aug 23 '12 at 9:44
  • @Alex I'm not sure, those are the steps given to me from my client from his host I believe. – Brett Aug 23 '12 at 11:08
  • @Alex Sorry, forgot to say, storage engine is MyISAM. – Brett Aug 23 '12 at 15:07
  • 1
    You're tackling this completely wrong. You need to do some serious database schema and query optimisation. Unions across 45 tables is a sure sign of a very poorly designed database. – John Gardeniers Aug 24 '12 at 9:13

You should identify and solve the database problems, 70k visits per day is not that much and MySQL is capable of handling that amount of work. You should download and use mysqlreport or similar tool to analyze and tune MySQL MyISAM and InnoDB buffers usage (I would also recommend to switch to InnoDB if you are still using MyISAM). The next step is to enable the slow queries logging in MySQL and to analyze the slow query log daily using pt-query-digest tool and EXPLAIN MySQL command. Probably you database lacks of proper indexes.

  • Yes, site is using MyISAM; can't use InnoDB as we need full-text search, so would have to switch to Sphinx anyway if we wanted to change to InnoDB and still have full-text search capability. Already checked slow query log and many of the queries are there, but they are indexed as well as I can see. – Brett Aug 23 '12 at 15:13
  • Are you absolutely sure these queries use indexes properly? You can check this using the EXPLAIN command in mysql console. If they do you should probably extend max MySQL key buffer size in the config. – Alex Aug 23 '12 at 15:17
  • I'll have a look at EXPLAIN again and let you know. – Brett Aug 23 '12 at 15:44
  • Ok, added a screenshot of the explain for an example query. Also found out the site is only getting about 30k/ day now. – Brett Aug 24 '12 at 8:54
  • @Brett You'd probably get much more insightful information on dba.stackexchange.com. – tacotuesday Aug 28 '12 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.