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

Pierre.Vriens
  • 1,159
  • 34
  • 15
  • 19
Brett
  • 299
  • 1
  • 3
  • 12
  • 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 09:42
  • Why do you need to repair the database tables every time? What storage engine do you use? – Alex Aug 23 '12 at 09: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 09:13
  • I didn't develop the database schema for one thing, but the only other option would be putting it all into one table and from past experience working with large single tables is a huge pain and can cause slow queries. – Brett Aug 24 '12 at 09:27
  • One table would be an even worse design. The correct answer lies somewhere between the two. – John Gardeniers Aug 24 '12 at 09:57
  • @JohnGardeniers Not sure what other way you could do it other than one table or separated into categories. Thought of separating it into alphabetical tables and then only searching the respective table, but I quickly decided against that as that would obviously only work with limited search capability. If you could point me in the right direction that would be great. – Brett Aug 25 '12 at 11:30
  • @Brett, I recommend you take some time to learn a bit about database design. It will save you both time and headaches. – John Gardeniers Aug 25 '12 at 12:12
  • @JohnGardeniers I have designed many a database in my time, though I just don't see another way this can be done. The data can be broken up into different sections and mapped, but it's still going to have to be able to search all the data no matter what way you do it. I'm not asking you how to do it, I asked you to point me in the right direction so I CAN learn; I'm coming up empty with my google searches. – Brett Aug 26 '12 at 06:39
  • @JohnGardeniers Forget it then. You're full of answers but lacking in information. – Brett Aug 28 '12 at 09:32

1 Answers1

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.

Alex
  • 7,789
  • 4
  • 36
  • 51
  • 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 08:54
  • @Brett You'd probably get much more insightful information on dba.stackexchange.com. – tacotuesday Aug 28 '12 at 14:59
  • @nojak Thanks a lot for that. SE has so many sites you never know that some exist lol – Brett Aug 28 '12 at 18:37
  • @Brett So true. But it's a good thing! – tacotuesday Aug 28 '12 at 20:29