2

My host has given me this log and tells me its mySQL overloading my server:

| 266976 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 266978 | db_name | localhost | db_name     | Query   | 29   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 266980 | db_name | localhost | db_name     | Query   | 45   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 266996 | db_name | localhost | db_name     | Query   | 43   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267002 | db_name | localhost | db_name     | Query   | 44   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267008 | db_name | localhost | db_name     | Query   | 10   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267011 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267013 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267016 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267018 | db_name | localhost | db_name     | Query   | 13   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267023 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267033 | db_name | localhost | db_name     | Query   | 44   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267034 | db_name | localhost | db_name     | Query   | 44   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267040 | db_name | localhost | db_name     | Query   | 43   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267041 | db_name | localhost | db_name     | Query   | 10   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267050 | db_name | localhost | db_name     | Query   | 42   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267058 | db_name | localhost | db_name     | Query   | 38   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267060 | db_name | localhost | db_name     | Query   | 29   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267062 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT p.* FROM wp2_posts AS p  INNER JOIN wp2_term_relationships AS tr ON p.ID = tr.object_id INNER |
| 267072 | db_name | localhost | db_name     | Query   | 34   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267075 | db_name | localhost | db_name     | Query   | 34   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267076 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT p.* FROM wp2_posts AS p  INNER JOIN wp2_term_relationships AS tr ON p.ID = tr.object_id INNER |
| 267084 | db_name | localhost | db_name     | Query   | 19   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267087 | shonline     | localhost | shonline_sho2011 | Query   | 15   | update               | INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) VALUES ('????         |
| 267088 | db_name | localhost | db_name     | Query   | 2    | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |
| 267089 | db_name | localhost | db_name     | Query   | 30   | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS  wp2_posts.* FROM wp2_posts  INNER JOIN wp2_term_relationships ON (wp2_po |

The server is a Xeon quadcore, 4GB RAM, 2Mbit up and down bandwidth. Monthly 2 million visitors. Apache set to 500 concurrent and SQL set to 250.

Plugins: Adrotate / Contact Form 7 / Disqus / Events / Jetpack / Really Simple Captcha / Use Google Libraries / WebsiteDefender / Wordpress.com Popular Posts / WP-Page Navi / WP-Print / WP-Minify / WP-Super Cache

How can I fix this?

3
  • because of hardware limitations - what limitations?
    – zerkms
    Jan 11, 2012 at 1:22
  • maybe your theme has bad implementations, which one are you using?
    – Rochester Oliveira
    Jan 11, 2012 at 14:10
  • Hi, my theme is self coded. The homepage uses 160 queries which is alot. But since I am serving cached files, I dont see much CPU usage since it is only queried once every 5 minutes.
    – Nders
    Jan 12, 2012 at 2:04

2 Answers 2

1

When you see Copying to tmp table, that's often a sign that you wrote a bad query that is going to take a long time to run. Your post is showing several that are taking 45 seconds or more to run. Identify what queries are running that are taking a long time.

You'll need to alter those queries to make proper use of indexes. That may require finding out which plugins are kicking them off.

3
  • Hi, thanks for the input. How exactly do we identify what queries that are the culprits? and which plugins? Thank you
    – Nders
    Jan 12, 2012 at 2:55
  • 1
    Any query that is long-running is a culprit. You can determine the details of those queries by executing show full processlist. Determining the plugin may be more challenging. I would start by greping for 'SQL_CALC_FOUND_ROWS' to try and sort out what the query source is. Jan 12, 2012 at 3:03
  • thanks for the tips. Though I don't understand much about server, I can give this to my host to see if it helps
    – Nders
    Jan 13, 2012 at 0:40
0

You are not interested in "current visitor" but how many concurrent visitors you have. There is no such plugin in wordpress that would enable you to count number of concurrent users. There are few things that you should check:

  • are you sure that supercache is working?
  • are you sure that the problem is in mysql database (if supercache is working, problem shouldn't be in mysql database, but in your server and probably network speed to your server)
  • if you are on shared hosting, you should definitely move from there if you are able to get that many users :)
  • if you are delivering too many pictures or some other static content, you should consider CDN.
9
  • Hi,thanks for pointing out, yes it is concurrent visitors.I am on a dedicated server (quadcore xeon and 4gb ram) and running wp-super-cache (i can see it running fine when viewing my page source). I'm in the process of getting a CDN but according to my server log it seems like my mysql and php are taking up alot of resources (both ram and cpu). Not really sure what is wrong here. mysql is taking up 1.3gb of ram...
    – Nders
    Jan 11, 2012 at 4:22
  • 1.3GB is not too much for a database server (depending on cache configuration). It is strange though that mysql takes cpu if wp-super-cache is working...it should just serve files from the hdd, without hitting the database or any kind of php processing...so, it must be that there is something custom-made on your pages (like some plugin) so your pages are not cached, or wp-super-cache just doesn't work. Perhaps yo can try with some other caching plugin, like W3 total cache...
    – user4589
    Jan 11, 2012 at 4:53
  • Hi. thanks for the feedback. Now my server is experiencing occasional cpu surge (up to 35-40.xx load) from time to time. my webhost's engineers can't find out what is actually the culprit. they only know that sql and php are doing it. Also i wanted to try to switch to W3 but after reading the support forum, it seems like W3 have way more problems in wp3.3.1... I've updated my question with my current enabled plugins, any idea is appreciated. Thank you
    – Nders
    Jan 11, 2012 at 6:46
  • So, your processor goes to 40% from time to time, and you are using 1.3GB of RAM (+ some more) of 4GB. I don't think that you have problems with CPU and RAM then, everything is just fine (at least for a website with such a heavy load). How do you experience problems, anyway? Do your pages open slow? Can you give us a link?
    – user4589
    Jan 11, 2012 at 14:45
  • hi, it is www.theborneopost.com, whenever it is peak hour, my site will go offline randomly, and my host tell me my mysql overloaded my cpu (which goes up to 40 or 50, NOT percentage, is LOAD). my host also given me a list of sql queries that is doing it, mostly made up of "| 267008 | db_name | localhost | db_name | Query | 10 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp2_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp2_po |". I searched this string on google and made a few changes but to no avail. the random spikes still coming..
    – Nders
    Jan 12, 2012 at 1:25

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