High CPU

In this board you can talk about general questions about phpMyFAQ

Moderator: Thorsten

Post Reply
mmcgrath
Posts: 37
Joined: Wed Jul 10, 2013 9:31 pm

High CPU

Post by mmcgrath » Tue Nov 06, 2018 4:52 pm

Hello!

We are running phpMyFAQ 2.9.11 on Ubuntu 16.0.4.5 LTS.

Over the last few days the CPU usage has been rising steadily (see attached), and now my VM is in a constant state of alarm for 'CPU Usage'.

Running top shows that mysql is the culprit:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1057 mysql 20 0 1112608 216160 17580 S 93.7 10.6 1246:12 mysqld
12462 www-data 20 0 494124 27952 12676 S 2.6 1.4 0:16.11 apache2
11757 www-data 20 0 494800 35808 19936 S 2.3 1.7 0:58.51 apache2
870 root 20 0 222124 26012 15452 S 0.3 1.3 0:04.52 snapd
1924 root 20 0 510280 7912 7128 S 0.3 0.4 0:31.04 ManagementAgent
11883 www-data 20 0 494888 29096 13188 S 0.3 1.4 0:38.81 apache2

These are the databases I have:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| faq |
| faq_ssl |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+


And doing a show processlist; results in the following:

+--------+------+-----------------+---------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 120154 | root | localhost | faq | Query | 0 | starting | show processlist |
| 120223 | faq | localhost:38054 | faq_ssl | Query | 0 | Sending data | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
| 120224 | faq | localhost:38056 | faq_ssl | Query | 0 | Sending to client | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
| 120225 | faq | localhost:38058 | faq_ssl | Query | 0 | Sending data | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
+--------+------+-----------------+---------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)




+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 120154 | root | localhost | faq | Query | 0 | starting | show processlist |
| 120282 | faq | localhost:38172 | faq_ssl | Query | 0 | Sending data | SELECT
MIN(id) as id, searchterm, COUNT(searchterm) AS number
FROM
|
| 120283 | faq | localhost:38174 | faq_ssl | Query | 0 | Sending data | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
| 120284 | faq | localhost:38176 | faq_ssl | Query | 0 | Sending data | SELECT
fd.id AS id,
fd.lang AS lang,
fd.thema AS the |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)




+--------+------+-----------------+---------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 120154 | root | localhost | faq | Query | 0 | starting | show processlist |
| 120300 | faq | localhost:38208 | faq_ssl | Query | 0 | Sending data | SELECT
MIN(id) as id, searchterm, COUNT(searchterm) AS number
FROM
|
| 120301 | faq | localhost:38210 | faq_ssl | Query | 0 | Sending to client | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
| 120302 | faq | localhost:38212 | faq_ssl | Query | 0 | Sending data | SELECT
fd.id AS id,
fd.lang AS lang,
fd.thema AS the |
+--------+------+-----------------+---------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)



Should my database be constantly running queries such as the ones above?

Thanks!
You do not have the required permissions to view the files attached to this post.

Thorsten
Posts: 14644
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten » Sat Nov 10, 2018 8:36 am

Hi,

do you have many users on the FAQ?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

mmcgrath
Posts: 37
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by mmcgrath » Sat Nov 10, 2018 5:57 pm

Throsten -

No, right now there is "1 user online" -- and that's me!

Max

Thorsten
Posts: 14644
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten » Sun Nov 11, 2018 9:21 am

Hi,

looks like the query for fetching the tags is hanging somehow... could you try to kill these queries?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

mmcgrath
Posts: 37
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by mmcgrath » Mon Nov 12, 2018 3:15 pm

Thorsten -

Thanks for the reply!

It looks like the queries are constantly changing. If I do show processlist; back-to-back-to-back, it shows multiple different queries with different query IDs -- it's never the same:

mysql> show processlist;
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 805561 | root | localhost | faq | Query | 0 | starting | show processlist |
| 805736 | faq | localhost:55756 | faq_ssl | Query | 0 | Sending data | SELECT
MIN(id) as id, searchterm, COUNT(searchterm) AS number
FROM
|
| 805737 | faq | localhost:55758 | faq_ssl | Query | 0 | Sending data | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql> show processlist;
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 805561 | root | localhost | faq | Query | 0 | starting | show processlist |
| 805761 | faq | localhost:55806 | faq_ssl | Query | 0 | Sending data | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
| 805762 | faq | localhost:55808 | faq_ssl | Query | 0 | Sending data | SELECT
fd.id AS id,
fd.lang AS lang,
fcr.category_id |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql> show processlist;
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 805561 | root | localhost | faq | Query | 0 | starting | show processlist |
| 805777 | faq | localhost:55838 | faq_ssl | Query | 0 | Sending data | SELECT
dt.record_id AS record_id
FROM
faq_faqtags t, faq |
| 805778 | faq | localhost:55840 | faq_ssl | Query | 0 | Sending data | SELECT
fd.id AS id,
fd.lang AS lang,
fcr.category_id |
+--------+------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Also, please note the queries are against the faq_ssl database. What is the reason for that specific database?

Thanks!

Max

Thorsten
Posts: 14644
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten » Tue Nov 13, 2018 5:39 pm

Hi Max,

well, the faq_ssl database is the one you probably using in config/datanbase.php, could you please verify that?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

mmcgrath
Posts: 37
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by mmcgrath » Tue Nov 13, 2018 5:42 pm

Here is the contents of that file:

<?php
$DB['server'] = '127.0.0.1';
//$DB['user'] = 'root';
//$DB['password'] = 'mysecretpassword1';
//$DB['db'] = 'faq';
$DB['user'] = 'faq';
$DB['password'] = 'mysecretpassword2;
$DB['db'] = 'faq_ssl';
$DB['prefix'] = 'faq_';
$DB['type'] = 'mysqli';

Thorsten
Posts: 14644
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten » Tue Nov 13, 2018 5:48 pm

Hi,

looks like you have many FAQs and many tags, right?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

mmcgrath
Posts: 37
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by mmcgrath » Tue Nov 13, 2018 6:01 pm

Based on phpMyFAQ Information I have 400 Articles.

When at index.php it says I have 383 FAQs online.

I'm not sure how to find out how many tags I have, but I can't imagine we have all that many.

Also, based on our apache2 access.log, it seems it may be Googlebot/2.1 causing the issue:

Code: Select all

66.249.75.157 - - [13/Nov/2018:11:00:04 -0600] "GET /faq/?action=search&tagging_id=944,693,64,694,491,459,695,23 HTTP/1.1" 200 5698 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.75.157 - - [13/Nov/2018:11:00:05 -0600] "GET /faq/?action=search&tagging_id=491,61,64,23,695,694,,24 HTTP/1.1" 200 5688 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.75.157 - - [13/Nov/2018:11:00:06 -0600] "GET /faq/?action=search&tagging_id=61,694,696,,459,693,24,23 HTTP/1.1" 200 5691 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.75.159 - - [13/Nov/2018:11:00:07 -0600] "GET /faq/?action=search&tagging_id=491,694,695,,64,23,24,696 HTTP/1.1" 200 9389 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.75.157 - - [13/Nov/2018:11:00:07 -0600] "GET /faq/?action=search&tagging_id=946,23,24,,491,459,64 HTTP/1.1" 200 5694 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
We've just now setup a robots.txt to see if that helps.

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest