Return to Question

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', article_show enum('yes','no') NOT NULL DEFAUL 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 article_print_count int(11) NOT NULL DEFAULT article_comments_allow enum('yes','no') NOT N article_ratings_allow enum('yes','no') NOT NU PRIMARY KEY (article_id), FULLTEXT KEY article_title (article_title,`) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARS

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) ) ENGINE=MyISAM AUTO_INCREMENT=342553 DEFAULT CHARSET=latin1

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`)

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', article_show enum('yes','no') NOT NULL DEFAUL 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 article_print_count int(11) NOT NULL DEFAULT article_comments_allow enum('yes','no') NOT N article_ratings_allow enum('yes','no') NOT NU PRIMARY KEY (article_id), FULLTEXT KEY article_title (article_title,`) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARS

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_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', article_show enum('yes','no') NOT NULL DEFAUL 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 article_print_count int(11) NOT NULL DEFAULT article_comments_allow enum('yes','no') NOT N article_ratings_allow enum('yes','no') NOT NU PRIMARY KEY (article_id), FULLTEXT KEY article_title (article_title,`) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARS

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) ) ENGINE=MyISAM AUTO_INCREMENT=342553 DEFAULT CHARSET=latin1

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:

UPDATE:

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', article_show enum('yes','no') NOT NULL DEFAUL 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 article_print_count int(11) NOT NULL DEFAULT article_comments_allow enum('yes','no') NOT N article_ratings_allow enum('yes','no') NOT NU PRIMARY KEY (article_id), FULLTEXT KEY article_title (article_title,`) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARS

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