1

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

Improve this question
11
  • 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, 2012 at 9:42
  • Why do you need to repair the database tables every time? What storage engine do you use?
    – Alex
    Aug 23, 2012 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, 2012 at 11:08
  • @Alex Sorry, forgot to say, storage engine is MyISAM.
    – Brett
    Aug 23, 2012 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, 2012 at 9:13

1 Answer 1

Reset to default
3

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.

Improve this answer
7
  • 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, 2012 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, 2012 at 15:17
  • I'll have a look at EXPLAIN again and let you know.
    – Brett
    Aug 23, 2012 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, 2012 at 8:54
  • @Brett You'd probably get much more insightful information on dba.stackexchange.com.
    – tacotuesday
    Aug 28, 2012 at 14:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .