Templates down?
- Thread starter Dimlos
- Start date
Thank you very much.
I am certainly fine now.
Endercraft
Moderator (& bug finder :D)
I think MacroDroidDev is having a lot of issues with the servers right now, recently he had to deal with a server meltdown...
Endercraft
Moderator (& bug finder :D)
Recently I think the server are experiencing a lot of issues, probably a lot of traffic coming in.
My server has recently hit the perfect storm of having tons of data and a sudden influx of users over a few days all hammering the template store. This lead to my server CPU being maxed out and everything timing out and generally failing. The bulk of the server code was written a long time ago when I had very little data and query efficiency wasn't really of any concern. Over time the data has grown and grown and suddenly my terrible queries have come back to bite me. The SQL involved is quite complex as it has to join data from various tables with various criteria.
Over the weekend I got a book about SQL efficiency and most importantly how to measure and identify the bottlenecks/problems. I discovered that my query for getting the list of users was utterly shambolic in terms of efficiency (I put a lot of effort into making good queries for the macro lists and they are working quite efficiently.). I've spent today reworking and measuring and have deployed a fix that made the user list query about 20x faster. This has been a big help and my CPU usage is now way down. The fun thing is it looks like I can still make this considerably more efficient, but for now this fix is a massive help.
I hope this will stop any more CPU saturation issues. I've learnt a lot in the last few weeks and I may have to have some very short outages to tweak my server settings in the coming weeks, but these should hopefully be short and controlled for only a few minutes (with any luck).
Over the weekend I got a book about SQL efficiency and most importantly how to measure and identify the bottlenecks/problems. I discovered that my query for getting the list of users was utterly shambolic in terms of efficiency (I put a lot of effort into making good queries for the macro lists and they are working quite efficiently.). I've spent today reworking and measuring and have deployed a fix that made the user list query about 20x faster. This has been a big help and my CPU usage is now way down. The fun thing is it looks like I can still make this considerably more efficient, but for now this fix is a massive help.
I hope this will stop any more CPU saturation issues. I've learnt a lot in the last few weeks and I may have to have some very short outages to tweak my server settings in the coming weeks, but these should hopefully be short and controlled for only a few minutes (with any luck).
Jacob L
Moderator (Lawsonator)
According to this site: https://dnschecker.org/website-server-software.php the Apache on the server is 2.4.18, which from this change log: https://www.apachelounge.com/Changelog-2.4.html is 11th December 2015. There are huge changes since then that could help if my findings are correct.
Jacob L
Moderator (Lawsonator)
I'm interested so I'm having a read, this looks lime it may help: https://httpd.apache.org/docs/2.4/misc/perf-tuning.html and this: https://bobcares.com/blog/speed-up-apache
Thanks @Jacob L
All the serious bottlenecks in the server are in my own queries so I should be able to squash any issues just by fixing my stuff. I'm hoping to get better and more knowledgeable about the server side later in the year when I have a bit more time, so this will be an interesting read to help me get up to date.
All the serious bottlenecks in the server are in my own queries so I should be able to squash any issues just by fixing my stuff. I'm hoping to get better and more knowledgeable about the server side later in the year when I have a bit more time, so this will be an interesting read to help me get up to date.