0

Running a phpkb CMS on a ubuntu lamp server. mysql - 5.5.28-0ubuntu0.12.04.3-log

The tables in this database are not large in fact I have not found one over 10k rows.

Every five min I get a 'copy to temp table' query that hangs out on the server and locks tables. It brings the site to a stand still. Mysql takes 100% of one of the four cores on the server sometimes over I assume taking resources from the other cores. It still hangs.

I have made a ram disk as suggested in other threads and still no performance gains. I'm starting to think it's just going to come down to query optimization for the one that hangs. This seems to be the query that kills it from show full processlist; I also have logged slow queries to the mysql logs and this is the one that shows up over and over.

# Query_time: 152.569759  Lock_time: 0.000075 Rows_sent: 1181 Rows_examined: 383893921
use newkbdb;
SET timestamp=1461243155;

SELECT 
  phpkb_articles.article_id AS article_id, 
  article_title, 
  article_content, 
  article_status, 
  article_date_time, 
  SUM(phpkb_article_visits.article_hits) AS article_hits 
FROM 
  phpkb_articles 
  LEFT OUTER JOIN phpkb_article_visits ON (phpkb_article_visits.article_id 
  =phpkb_articles.article_id), phpkb_relations 
WHERE  
  article_show='yes' 
  AND (phpkb_relations.article_id = phpkb_articles.article_id) 
  AND article_status IN ('featured','approved')   
  AND ( (article_expiry_date = '0000-00-00') OR (article_expiry_date <>    
  '0000-00-00' AND article_expiry_date >= now()) ) 
  AND category_id=221 GROUP BY (phpkb_articles.article_id) 
ORDER BY 
  FIELD(article_status,'featured','approved'), 
  article_title ASC;

Any suggestions or help appreciated I'm not a mysql dba this is just a unicorn site that some dept bought and now its become a huge dependency.

Thanks in advance.

UPDATE: Create table statements:

CREATE TABLE `phpkb_articles` (
`article_id` int(11) NOT NULL AUTO_INCREMENT,
`author_id` int(11) NOT NULL,
`article_title` varchar(250) NOT NULL,
`article_content` longtext NOT NULL,
`article_date_time` datetime NOT NULL,
`article_status`enum('approved','disapproved','draft','featured','pending','pending-deleted','approved-deleted','featured-deleted','disapproved-deleted') NOT NULL DEFAULT 'pending',
`article_show` enum('yes','no') NOT NULL DEFAULT 'no',
`article_last_updation` datetime NOT NULL,
`article_keywords` varchar(250) NOT NULL,
`article_metadesc` varchar(250) NOT NULL,
`article_expiry_date` date NOT NULL,
`article_email_count` int(11) NOT NULL DEFAULT '0',
`article_print_count` int(11) NOT NULL DEFAULT '0',
`article_comments_allow` enum('yes','no') NOT NULL DEFAULT 'yes',
`article_ratings_allow` enum('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`article_id`),
FULLTEXT KEY `article_title` (`article_title`,`article_content`)
) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARSET=utf8 



 CREATE TABLE `phpkb_relations` (
 `relation_id` int(11) NOT NULL AUTO_INCREMENT,
 `article_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`relation_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=21960 DEFAULT CHARSET=latin1 

CREATE TABLE `phpkb_article_visits` (
`visit_id` int(11) NOT NULL AUTO_INCREMENT,
`article_id` int(11) NOT NULL,
`article_hits` int(11) NOT NULL,
`visit_date` date NOT NULL,
PRIMARY KEY (`visit_id`)
5
  • 3
    What is this part of the joining condition:... , phpkb_relations? Also, please provide the show create table for each of these two tables. Note please that if phpkb_relations is a table, then the scanned rows will be huge as there is no join condition. That could explain the big Rows_examined value Apr 22, 2016 at 15:18
  • I think you may have solved it. I missed that part in the join. phpkb_relations is a table. I'll have to fish through their php code and see why that statement is getting truncated. I added some describe statements. I don't have the original create statements this is not my db. Thanks for the help.
    – vikingben
    Apr 22, 2016 at 15:30
  • 2
    @vikingben When you can run describe you can use show create table too. Please add that as text instead of images of describe (the create table statement contains information about indexes which is not available in describe output and is important too). Use EXPLAIN to check the query plan.
    – jkavalik
    Apr 22, 2016 at 16:00
  • Thanks for the tip. I've added those as requested. Also I found the code for the join and added a condition to the phpkb_relations. I'm testing now to see if that makes a difference.
    – vikingben
    Apr 22, 2016 at 16:13
  • @JehadKeriaki The join condition is actually present in the WHERE clause (phpkb_relations.article_id = phpkb_articles.article_id).
    – jkavalik
    Apr 22, 2016 at 17:09

1 Answer 1

1

Indexes !

  1. Use InnoDB, not MyISAM.

  2. Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)...

Something like:

SELECT  a.article_id AS article_id, article_title, article_content,
        article_status, article_date_time, 
      ( SELECT  SUM(article_hits)
            FROM  phpkb_article_visits
            WHERE  article_id = a.article_id 
      ) AS article_hits
    FROM  phpkb_articles AS a JOIN phpkb_relations AS r  ON r.article_id = a.article_id
    WHERE  article_show='yes'
      AND  article_status IN ('featured','approved')
      AND  article_expiry_date >= now()
      AND  category_id=221
    GROUP BY  a.article_id
    ORDER BY  FIELD(article_status,'featured','approved'),
              article_title ASC;
  1. Please qualify each field with the table that it belongs to. (I am depending on all the unqualified article_* fields belonging to article_hits.)

4.

Indexes needed:

phpkb_article_visits:  (article_id, article_hits)
article_hits:  (category_id, article_show, article_expiry_date)
phpkb_relations:  (article_id)
  1. Notice how I simplified the test for article_expiry_date.
4
  • Thank you for your answer I will give this a try in the next couple of days and update the thread with my results.
    – vikingben
    Apr 25, 2016 at 21:24
  • Rick, added indexes seem to have increased performance significantly. I appreciate your time and help in this matter I marked you for the correct answer. I'm still working on switching up the query and altering my tables to innodb. Where this is software I did not build just making sure it wasn't made with MYISAM in mind. Also the code builds this statement in a dynamic mess in PHP so it's cumbersome to switch up all the parts are not even in the same class. Thanks again for your help.
    – vikingben
    Apr 27, 2016 at 2:48
  • MyISAM used to be the default. Now, it is clear that InnoDB is a better choice. More tips on converting.
    – Rick James
    Apr 27, 2016 at 3:41
  • Sounds great, I'll get there just after a few years I've learned to use precaution. Once I have had a chance to identify the effects of the code I'm going to be running. I'll get those tables updated just been a busy week. Thanks for all your help.
    – vikingben
    Apr 27, 2016 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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