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.
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.
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.
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?
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
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?
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)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.
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.
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?
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.
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
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.
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.
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.
it does not working! Is there other changes that be welcome?
What isn’t working with those changes?
Please post the updated compose file so I can check you’ve applied them correctly.
The formatting of that file isn’t correct.
YAML (the language being used there) requires very exact whitespace. You need to ensure that the file is exactly the same as we ship, but with that extra command line added to it.