High CPU

[mmcgrath]

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!

[Thorsten]

Hi,

do you have many users on the FAQ?

bye
Thorsten

[mmcgrath]

Throsten -

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

Max

[Thorsten]

Hi,

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

bye
Thorsten

[mmcgrath]

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]

Hi Max,

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

bye
Thorsten

[mmcgrath]

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]

Hi,

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

bye
Thorsten

[mmcgrath]

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.

[Thorsten]

Hi,

please try this patch:

https://github.com/thorsten/phpMyFAQ/bl ... F/Tags.php

This caches the query results.

bye
Thorsten

[mmcgrath]

Thanks Thorsten!

For now we've seemingly fixed the problem by denying those IPs in our .htaccess file:

Code: Select all

Order Deny,Allow
Deny from 169.48.66.89
Deny from 66.249.75.159
Deny from 66.249.75.157

Would you still recommend we apply the patch? Also, sorry, but I don't know how to apply the patch. What steps do I need to take?

[Thorsten]

Hi,

just copy the contents of the file from Github and paste it into the one on your server.

bye
Thorsten