High CPU

In this board you can talk about general questions about phpMyFAQ

Moderator: Thorsten

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

High CPU

Post by 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!
You do not have the required permissions to view the files attached to this post.
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten »

Hi,

do you have many users on the FAQ?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
mmcgrath
Posts: 38
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by mmcgrath »

Throsten -

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

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

Re: High CPU

Post by Thorsten »

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: 38
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by 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
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by 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
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
mmcgrath
Posts: 38
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by 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
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten »

Hi,

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

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
mmcgrath
Posts: 38
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by 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
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten »

Hi,

please try this patch:

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

This caches the query results.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
mmcgrath
Posts: 38
Joined: Wed Jul 10, 2013 9:31 pm

Re: High CPU

Post by 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
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: High CPU

Post by Thorsten »

Hi,

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

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Post Reply