High mysql cpu load

Get Help
1

Hello,

I’m experiencing very high cpu loads from xibo docker.

Tasks: 189 total,   2 running, 186 sleeping,   0 stopped,   1 zombie
%Cpu(s): 95.2 us,  4.5 sy,  0.0 ni,  0.2 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem:   4047996 total,  3771732 used,   276264 free,   224296 buffers
KiB Swap:  4191228 total,   148304 used,  4042924 free.   705956 cached Mem

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                
21774 999       20   0 1784960 639584   8788 S 174.6 15.8 117:47.01 mysqld

When I login to mysql and do a full processlist I get following results:

 1206 | cms  | 172.19.0.3:44552 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '676'   |
| 1237 | cms  | 172.19.0.3:44619 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '10259' |
| 1287 | cms  | 172.19.0.3:44708 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '290'   |
| 1309 | cms  | 172.19.0.3:44839 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '5955'  |
| 1346 | cms  | 172.19.0.3:44915 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '584'   |
| 1380 | cms  | 172.19.0.3:44972 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '3162'  |
| 1398 | cms  | 172.19.0.3:45094 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '6864'  |
| 1468 | cms  | 172.19.0.3:45202 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '7256'  |
| 1563 | cms  | 172.19.0.3:45338 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '1179'  |
| 1649 | cms  | localhost        | cms  | Query   |    0 | init         | show full processlist                                                                                                                                               |
| 1666 | cms  | 172.19.0.3:45540 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '8493'  |
| 2048 | cms  | 172.19.0.3:45967 | cms  | Query   |    0 | Sending data | SELECT * 
                FROM `requiredfile` 
               WHERE `displayId` = '10'
                  AND `type` = 'M' 
                  AND `itemId` = '5978'

My stats archive task is disabled:
4 Stats Archive 2018-07-11 13:19 0`

Counting the stats table results into no rows:
mysql> select count() from stat;
±---------+
| count() |
±---------+
| 0 |
±---------+
1 row in set (0.00 sec)

What could possibly go wrong here? Is there something I can do to investigate further?
Many thanks for your help.

Best regards,

Michel

2

What do you have set the collection interval in the display profile set to?

the queries that you mentioned seem to be about requiredFiles, which is called on each collection interval.

Also which CMS version are you using please?
Which docker version is it and on what server/VPS is it installed on?

3

I’m using 1.8.7.
Collection interval was set to 1 minute but now it’s set to 15 minutes.
When I turn of the display with id 10 the problem disapears.
We’re now trying a reboot of this system is this helps.
It’s running on ubuntu. How can I get the docker version?

I’ll get back with the results after the reboot.

Thanks for your reply!

4

The player is now back online. CPU load again rises to around 95%.
What could couse this player to rise the CPU load this extreme?
Currenty about 10 players are online. None of those rise the CPU this high.
If we turn of this one player CPU drops from 95 to 5 procent.
Content in all players is roughly the same.

5

1.8.7 has some serious widget caching bugs. Please upgrade to release-1.8.10 (or latest) tag.

Once upgraded, it will take some time for the background tasks to delete the left over media items from the database and for load to return to normal.

6

Player version btw is 1.8.8 131
We scheduled other content on this display the same as another display that’s not causing problems.
The problem remains.

7

The Player version isn’t important in this case. It’s the CMS version.

You need to upgrade, and then allow time for the background tasks to clear up the excess media items.

8

I have the same issue. My version is 1.8.10 and I had this same issue in 1.8.5, 1.8.7 and now 1.8.10.
xibo_cms-web_1 around 85%/90% of cpu
xibo_cms-db_1 around 70%/80% of cpu

The server has 8 CPU’s, 8G RAM and 100G SSD.

9

It depends on how many Players you have, how they are configured and what tuning you’ve done on MySQL and PHP. That could be completely normal.

What is MySQL doing to cause that loading, and what’s the size of the network?

10

We have right now around 150 players, all android.
The mysql server (docker) and webserver (docker) is using the default config from XIBO.
Our cms version is 1.8.10 and our android version is R104 and R105.

Also sometime we need restart the cms docker because the webserver got stuck.

Connectivity: 1minute

mysql> select count() from media;
±---------+
| count() |
±---------+
| 4461 |
±---------+
1 row in set (0.00 sec)

mysql> mysql> select count() from log;
±---------+
| count() |
±---------+
| 4161 |
±---------+
1 row in set (0.00 sec)

docker stats command
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
302e1f58f6ea xibo_cms-web_1 104.00% 658.6MiB / 1GiB 64.31% 1.15GB / 3.71GB 2.16MB / 77.2MB 63
8ef11126db56 xibo_cms-xmr_1 0.00% 13.22MiB / 256MiB 5.17% 27.5MB / 26.3MB 11.8MB / 4.1kB 4
7f10a76b0cf1 xibo_cms-db_1 55.91% 679.7MiB / 1GiB 66.37% 41GB / 90.4GB

11

1 minute collection is there for test and debug purposes only. It’s NOT designed for production use.

XMR will notify the Player when there’s anything new to collect. If you have a 1 minute collection as well, then your server is doing alot more work than necessary!

What’s the MySQL process list look like?

12 
mysql> mysql> show full processlist;
+---------+------+------------------+------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User | Host             | db   | Command | Time | State        | Info                                                                                                                                                                 |
+---------+------+------------------+------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2572546 | cms  | localhost        | cms  | Query   |    0 | init         | show full processlist                                                                                                                                                |
| 2572981 | cms  | 172.18.0.3:41652 | cms  | Query   |    0 | Sending data | SELECT *
                FROM `requiredfile`
               WHERE `displayId` = '117'
                  AND `type` = 'W'
                  AND `itemId` = '3666'  |
| 2572984 | cms  | 172.18.0.3:41658 | cms  | Query   |    0 | Sending data | SELECT *
                FROM `requiredfile`
               WHERE `displayId` = '68'
                  AND `type` = 'M'
                  AND `itemId` = '303460' |
| 2572990 | cms  | 172.18.0.3:41670 | cms  | Sleep   |    0 |              | NULL                                                                                                                                                                 |
+---------+------+------------------+------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)




mysql> show full processlist;
+---------+------+------------------+------+---------+------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User | Host             | db   | Command | Time | State     | Info                                                                                                                                                                      |
+---------+------+------------------+------+---------+------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2572546 | cms  | localhost        | cms  | Query   |    0 | init      | show full processlist                                                                                                                                                     |
| 2573401 | cms  | 172.18.0.3:42540 | cms  | Sleep   |    1 |           | NULL                                                                                                                                                                      |
| 2573413 | cms  | 172.18.0.3:42564 | cms  | Sleep   |    0 |           | NULL                                                                                                                                                                      |
| 2573416 | cms  | 172.18.0.3:42570 | cms  | Query   |    0 | query end | INSERT INTO `bandwidth` (Month, Type, DisplayID, Size)
              VALUES ('1530500400', '5', '126', '19986')
            ON DUPLICATE KEY UPDATE Size = Size + '19986' |
| 2573417 | cms  | 172.18.0.3:42572 | cms  | Sleep   |    0 |           | NULL                                                                                                                                                                      |
+---------+------+------------------+------+---------+------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)



mysql> show full processlist;
+---------+------+------------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User | Host             | db   | Command | Time | State        | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+---------+------+------------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2572546 | cms  | localhost        | cms  | Query   |    0 | init         | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 2573593 | cms  | 172.18.0.3:42932 | cms  | Query   |    0 | System lock  | SELECT `permissionId`, `groupId`, `view`, `edit`, `delete`, permissionentity.entityId
              FROM `permission`
                INNER JOIN `permissionentity`
                ON `permissionentity`.entityId = permission.entityId
             WHERE entity = 'Xibo\\Entity\\Widget'
                AND objectId = '3706'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| 2573598 | cms  | 172.18.0.3:42942 | cms  | Query   |    0 | Sending data | SELECT  media.mediaId,
               media.name,
               media.type AS mediaType,
               media.duration,
               media.userId AS ownerId,
               media.fileSize,
               media.storedAs,
               media.valid,
               media.moduleSystemFile,
               media.expires,
               media.md5,
               media.retired,
               media.isEdited,
               IFNULL(parentmedia.mediaId, 0) AS parentId,

               `media`.released,
               `media`.apiRef,

               `media`.createdDt,
               `media`.modifiedDt,
             (SELECT GROUP_CONCAT(DISTINCT tag) FROM tag INNER JOIN lktagmedia ON lktagmedia.tagId = tag.tagId WHERE lktagmedia.mediaId = media.mediaID GROUP BY lktagmedia.mediaId) AS tags,         `user`.UserName AS owner,      (SELECT GROUP_CONCAT(DISTINCT `group`.group)
                              FROM `permission`
                                INNER JOIN `permissionentity`
                                ON `permissionentity`.entityId = permission.entityId
                                INNER JOIN `group`
                                ON `group`.groupId = `permission`.groupId
                             WHERE entity = 'Xibo\\Entity\\Media'
                                AND objectId = media.mediaId
                                AND view = 1
                            ) AS groupsWithPermissions,    media.originalFileName AS fileName  FROM media    LEFT OUTER JOIN media parentmedia    ON parentmedia.editedMediaId = media.mediaId    LEFT OUTER JOIN `user` ON `user`.userId = `media`.userId  WHERE 1 = 1  AND media.name = 'ticker_5e564f913cf7e68cea5903500de01eff'  AND media.isEdited = 0 AND media.type = 'module' ORDER BY name |
| 2573599 | cms  | 172.18.0.3:42944 | cms  | Query   |    0 | Sending data | SELECT  media.mediaId,
               media.name,
               media.type AS mediaType,
               media.duration,
               media.userId AS ownerId,
               media.fileSize,
               media.storedAs,
               media.valid,
               media.moduleSystemFile,
               media.expires,
               media.md5,
               media.retired,
               media.isEdited,
               IFNULL(parentmedia.mediaId, 0) AS parentId,

               `media`.released,
               `media`.apiRef,

               `media`.createdDt,
               `media`.modifiedDt,
             (SELECT GROUP_CONCAT(DISTINCT tag) FROM tag INNER JOIN lktagmedia ON lktagmedia.tagId = tag.tagId WHERE lktagmedia.mediaId = media.mediaID GROUP BY lktagmedia.mediaId) AS tags,         `user`.UserName AS owner,      (SELECT GROUP_CONCAT(DISTINCT `group`.group)
                              FROM `permission`
                                INNER JOIN `permissionentity`
                                ON `permissionentity`.entityId = permission.entityId
                                INNER JOIN `group`
                                ON `group`.groupId = `permission`.groupId
                             WHERE entity = 'Xibo\\Entity\\Media'
                                AND objectId = media.mediaId
                                AND view = 1
                            ) AS groupsWithPermissions,    media.originalFileName AS fileName  FROM media    LEFT OUTER JOIN media parentmedia    ON parentmedia.editedMediaId = media.mediaId    LEFT OUTER JOIN `user` ON `user`.userId = `media`.userId  WHERE 1 = 1  AND media.name = 'ticker_43de6a779a483e32b7cc5b7d5ce33e13'  AND media.isEdited = 0 AND media.type = 'module' ORDER BY name |
+---------+------+------------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
13

Alex,

After changes from 1 min to 30 min the server load decrease. Excelent!
Do you have other tip that we need changes to run in production environment? Like feeds, other times, values, etc?

Thank you.

14

I can’t really give you guideance without knowing what you’re doing, but for feeds, set the cache timeout at the longest you can stand.

So if you have a news feed, then an hour might be a reasonable cache time to have on that, since news changes every few hours realistically.

Similarly, if you have a calendar feed that you update once a week, then setting the cache on that to 1 day might be reasonable.

Avoid anywhere setting no caching, or very short cache times unless you have a valid reason to do so.

15

Ok Alex, thanks for sharing your knowledge.
Also I have other issue. Sometimes the httpd proccess get stuck and the CMS goes to offline. So I need restart the docker cms and all back to normal. I am thinking what’s going on, because when this happened I can see a lot of httpd stuck proccess using top command.

Your thoughts?

16

You will get lots of http processes. It’s possible though that for the number of Players you have you’re running out of workers (they’re all busy).

You need to tune Apache and MySQL to accept more concurrent connections. The container has all the tools to do that.

You’ll need to increase the memory limits for the cms-web and cms-db containers. You can do so by editing the mem_limit lines in the compose file.

Once the containers are allowed to consume more memory, you can have MySQL use more memory by adding the following to the compose file in the cms-db section:

        command: mysqld --innodb-buffer-pool-size=1000M --max-connections=300
        mem_limit: 5G

That will increase the memory usage to somewhere around 3-4GB for MySQL, and increase the maximum connection count too.

Increasing the memory limit on the cms-web container alone may be sufficient, so I’d try that first.

17

Hi Alex,

I did the changes in apache and in docker-compose.yml in cms-db section. After that I did restart both.
How can I check if the mysql is accepting more connections?
Is there any other changes to do?

Thanks.
Thiago

18

If you want to confirm it, you can run the following SQL:

SHOW VARIABLES LIKE "max_connections";

To run SQL on a Docker container, follow these notes:

I would see how you go with those changes before doing anything further. Tuning is about making small changes, and then observing the result. If you make lots of changes at the same time, and then performance drops, you won’t know which caused the issue.

19

mysql> SHOW VARIABLES LIKE “max_connections”;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| max_connections | 151 |
±----------------±------+

By the way I am trying to apply it using the mysql console and I got this message:

mysql> SET GLOBAL max_connections = 5000;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

20

You can’t apply it dynamically, you must apply it to the docker-compose file as I described, and then down/up the containers. 5000 connections is insane. You’ll kill the server trying to do that. You need to set reasonable limits based on the hardware you have.