search

Newcomer1989 / TSN-Ranksystem

A PHP Bot that assigns time based server groups on TeamSpeak3.
https://ts-ranksystem.com
GNU General Public License v3.0
142 stars 60 forks source link

High cpu usage caused by TIMESTAMP SELECT MAX #251

Closed bykidi closed 6 years ago

bykidi commented 7 years ago

SlowMode is set to 1 second, but the TIMESTAMP SELECT MAX is causing very high cpu usage, see the graphics. And it didn't happened before, i think that this is somehow caused by combination of mysql settings and ranksystem recent update (1.2.2)

default 2

Shad86 commented 7 years ago

Dont forget that your ranksystem is growing, so the snapshots will get also bigger as longer it runs because it gets filled with more information. So a higher cpu usage can happen, if your community continuously grows.

mightyBroccoli commented 7 years ago

I have exactly the same issue with the mysql usage. I am using a cached innodb server and even though these spikes occur every 10 to 15 seconds. I noticed that not on all mysql databases a primary key is set to optimize the index usage of a query but my db knowledge is not that great. But if somebody out there has some idea to optimize the db usage that would be nice.

Newcomer1989 commented 7 years ago

This SQL is needed to calculate the total online time for week and month (see stats/ page at top). That are much data, which are calculated by the Ranksystem. For every user it has to build the time difference and sum this with the next step.

Here the full SQL for this calculation. If anyone are able to optimize this SQL, please feel free to share it with us.

Week: SELECT (SELECT SUM(count) FROM user_snapshot WHERE timestamp=(SELECT MAX(s2.timestamp) AS value1 FROM (SELECT DISTINCT(timestamp) FROM user_snapshot ORDER BY timestamp DESC LIMIT 28) AS s2, user_snapshot AS s1 WHERE s1.timestamp=s2.timestamp)) - (SELECT SUM(count) FROM user_snapshot WHERE timestamp=(SELECT MIN(s2.timestamp) AS value2 FROM (SELECT DISTINCT(timestamp) FROM user_snapshot ORDER BY timestamp DESC LIMIT 28) AS s2, user_snapshot AS s1 WHERE s1.timestamp=s2.timestamp) AND uuid IN (SELECT uuid FROM user)) AS count

Month: SELECT (SELECT SUM(count) FROM user_snapshot WHERE timestamp=(SELECT MAX(s2.timestamp) AS value1 FROM (SELECT DISTINCT(timestamp) FROM user_snapshot ORDER BY timestamp DESC LIMIT 120) AS s2, user_snapshot AS s1 WHERE s1.timestamp=s2.timestamp)) - (SELECT SUM(count) FROM user_snapshot WHERE timestamp=(SELECT MIN(s2.timestamp) AS value2 FROM (SELECT DISTINCT(timestamp) FROM user_snapshot ORDER BY timestamp DESC LIMIT 120) AS s2, user_snapshot AS s1 WHERE s1.timestamp=s2.timestamp) AND uuid IN (SELECT uuid FROM user)) AS count

Note: The SQL above hasn't change for a longer time. There is already an index set to the table user_snapshot on the column timestamp. Entries above one month will be deleted by the cleaning process to save storage and reduce the database ressources (CPU,...).

bykidi commented 7 years ago

@Newcomer1989 As for now... Can you make that stat to calculate itself more rare? Once in the 10 minutes would be great. Until this SQL will get optimized

//edit Also slowmode 2seconds would be super great for low-end servers. I tried it on my system and it works great and reducing the cpu usage noticeably.

Newcomer1989 commented 6 years ago

I will have a look on this both SQL commands with one of the next released (not the directly the next one). Perhaps I will change the way to calculate this data. I thought at beginning at the Ranksystem it would be a good idea to give the database the job to handle much data to calculate, cause databases are made for that.

The main problem are a bad database configuration. The default one is on most machines not the best. I saw on different machines it is possible to increase the database performance which saves time/ resources on factor 2-5. But I also understand that it is not easy to configurate the database perfect for your special case (for your machine, your software ...).

So perhaps I will change it in that way, that the calculation will run over PHP and not the database. Perhaps this will saving more resources for the most cases. I have to code and test it.. If this is done, I will give more information.

Newcomer1989 commented 6 years ago

Release 1.2.3 is out. So your CPU load could be reduced, cause in some installations a SQL Index was missing, which should be added in version 1.2.3.

Can you give us a short feedback you can measure it is better or - I don't hope - worse?

bykidi commented 6 years ago

Yes. Better. A lot better. default

bykidi commented 6 years ago

Without delays there is still hella lot of cpu load, but i guess that's the PHP+MySQL and there is nothing can be done to fix that.

Shad86 commented 6 years ago

Or may a serverside lack of performance - I mean, I have about 6 hosted ranksystems on my system and didn‘t have any performance issues.

Newcomer1989 commented 6 years ago

bykidi: You focus your server traffic or you only focus here the cpu usage of the Ranksystem?

bykidi commented 6 years ago

@Newcomer1989 cpu usage, for sure.

Newcomer1989 commented 6 years ago

yes, but is this of the full server or only the Ranksystem?

bykidi commented 6 years ago

it's overall cpu usage for entire server. teamspeak, apache, mariadb, ranksystem, openvpn, squid, csgo, mumble, ejabberd, jts3servermod

Newcomer1989 commented 6 years ago

Then you should only focus the relevant processes. In Linux you can do that with htop.

htop -p $(pgrep -d, 'php|mysql')

Unfortunately, only the comparison values with version 1.2.2 will be missing.