Diagnosing performance issues on the site

[kiska]

Hey there guys, Skillz has given me access to the server so I can see what's causing performance issues.

Since I don't really want to mess with prod, I have made a copy of it and have hosted it here:

https://dev.kiska.pw/

I would like some performance feedback on the dev site. Stats database was taken 2023-02-18_04-01-39

And the forum database was taken on 2023-02-02_04-49-46

NEW:
I have set up another dev env at https://dev2.kiska.pw/

The stats here have taken from the live site at
Stats db 2023-02-26_05-11-04
Forum db 2023-02-26_05-11-34

I have rewritten a few of the queries, please check the marathon stats as those are the ones I have optimised

[UBT - Timbo]

Hi

I just tried the dev website and switching betwen the various "header" items across the top of the page seems to be a bit faster.

But there are still times when the grey box with 3 blue circles appears as the page is being prepped for display (I assume). But said boxes appear for less time than can be observed on the production website.

eg: Clicking on a specific project stats brings the page up quickly.

https://dev.kiska.pw/marathon_project.php?id=25

and then clicking on any Team Name is faster (on the dev site) and same with then clicking on Team Member stats.

If the production and dev websites are hosted on different servers (but same service provider) then the hardware could be the issue, as the (relatively) slower performance on production server seems to indicate.

I doubt that any service provider related "internet bandwidth/performance capping" issues could cause the relatively slower "performance" observed on the production server.

regards
Tim

[Skillz]

We suspect it's the hardware on the main site.

Hardware on the main site is dual X5690 CPUs, 192GB RAM, and 2TB HDD, Debian 10.
Hardware on the dev site is single E3-1275v2, 32GB RAM and unsure on storage, but Ubuntu 20.04.5 LTS.

Main site is hosted in a datacenter in North Carolina w/ 1Gbps up/down
Dev site is hosted at kiska's house somewhere in the land down under (Australia)

For sure not a network limitation.

I am looking into upgrading the server to much faster hardware (even faster than the dev site) but it'll be a few months before I can save up the money to purchase it.

The main, MAIN, reason for the slow issues is the poor coding the development team did on the site. Basically, every time someone clicks on a page to show the stats it calculates those stats from the database in real time. There is no caching, no index, nothing. What Kiska did was force a "cache" update every minute using a cronjob on the dev site to force the data to stay in the cache, otherwise it only lasts a couple minutes before going away.

[UBT - Timbo]

We suspect it's the hardware on the main site.

Hardware on the main site is dual X5690 CPUs, 192GB RAM, and 2TB HDD, Debian 10.
Hardware on the dev site is single E3-1275v2, 32GB RAM and unsure on storage, but Ubuntu 20.04.5 LTS.

Those X5690's are pretty old architechture, but with 2x 6 real cores, and 192Gb RAM on a Linux server they should be OK. And those E3-1275v2's are not that new either, but with fewer cores and less RAM, the dev server should be slower...but it seems faster.

Main site is hosted in a datacenter in North Carolina w/ 1Gbps up/down
Dev site is hosted at kiska's house somewhere in the land down under (Australia)

For sure not a network limitation.

Agreed.

I am looking into upgrading the server to much faster hardware (even faster than the dev site) but it'll be a few months before I can save up the money to purchase it.

The main, MAIN, reason for the slow issues is the poor coding the development team did on the site. Basically, every time someone clicks on a page to show the stats it calculates those stats from the database in real time. There is no caching, no index, nothing. What Kiska did was force a "cache" update every minute using a cronjob on the dev site to force the data to stay in the cache, otherwise it only lasts a couple minutes before going away.

The caching sounds to me like the root cause...and having too many users online at once can also slow down page generation if it's pulling data "on demand" from the stats database (rather than the cache).

And you might also be suffering issues with spidering from search engines, which can slow things down as well.

So, maybe some tweaking to the cache might be better than new hardware, especially if the stats are only updated (locally) hourly...the caches do not need to be "constantly" refreshed so often, as long as they are "persistent" and therefore this does not put an extra load on the CPUs to keep refreshing them, if the data is the same?

regards
Tim

(edited last paragraph for better clarity)

[Skillz]

I've reached out to the dev team about implementing some sort of indexing/caching for the stats. I am sure they'll want to charge me for this since I didn't explicably say this in the original quote, but hell, I thought an optimized web site was well, the base per se. Once I find out if they can implement it on their end and how much it'll cost I'll see what kind of options I have.

Additionally, we think the MariaDB might be using AVX which the X5690s lack and the i3 1275v2 has which can explain the speed difference. Although it's not a lot, it is a noticeable.

We've been looking up hardware on eBay for the past hour or so seeing what kind of newer options we can get and the costs. So far we've been looking at Skylake CPUs with less cores, but higher clock-speeds and better single threaded performance.

[UBT - Timbo]

I've reached out to the dev team about implementing some sort of indexing/caching for the stats. I am sure they'll want to charge me for this since I didn't explicably say this in the original quote, but hell, I thought an optimized web site was well, the base per se. Once I find out if they can implement it on their end and how much it'll cost I'll see what kind of options I have.

I'm sure that if the design brief stated that there would be a significant level of daily (hourly during Sprints?) interest in publishing live credits data, then some degree of indexing should have been built into the design...unless the dev team assumed that stats pages might only be viewed every so often (maybe a few users once or twice a day?).

But tweaking the cache functions should be a one time hit, *IF* they decide it is a new request. Good luck with the negotiations on this.

Additionally, we think the MariaDB might be using AVX which the X5690s lack and the i3 1275v2 has which can explain the speed difference. Although it's not a lot, it is a noticeable.

We've been looking up hardware on eBay for the past hour or so seeing what kind of newer options we can get and the costs. So far we've been looking at Skylake CPUs with less cores, but higher clock-speeds and better single threaded performance.

Some food for thought - check these out:

https://www.plesk.com/blog/various/mari ... ce-tuning/

https://stackoverflow.com/questions/609 ... -cpu-cache

https://serverfault.com/questions/89550 ... malization

https://serverfault.com/questions/90572 ... to-mariadb

regards
Tim

[kiska]

Additionally, we think the MariaDB might be using AVX which the X5690s lack and the i3 1275v2 has which can explain the speed difference. Although it's not a lot, it is a noticeable.

We've been looking up hardware on eBay for the past hour or so seeing what kind of newer options we can get and the costs. So far we've been looking at Skylake CPUs with less cores, but higher clock-speeds and better single threaded performance.

Some food for thought - check these out:

https://www.plesk.com/blog/various/mari ... ce-tuning/

https://stackoverflow.com/questions/609 ... -cpu-cache

https://serverfault.com/questions/89550 ... malization

regards
Tim

Currently I have attached a performance monitor to both sites and I'll aggregate its profile and see whats hampering the speed, given I have replicated what I have done on the dev site to the main site and the speed seems to not have improved.

Here are a couple of performance traces I have

URL: https://dev.kiska.pw/team_details.php?id=1
Loading 100 entries: https://server8.kiska.pw/uploads/5de95f ... image.png
Loading standard 10 entries: https://server8.kiska.pw/uploads/6b441b ... image.png

Marathon user URL: https://dev.kiska.pw/marathon_users.php?id=28
100 entries: https://server8.kiska.pw/uploads/1bf478 ... image.png
10 entries: https://server8.kiska.pw/uploads/cb30a1 ... image.png

From these traces I don't think its mariadb thats the problem, but the dev team's... lack of optimisation.
For example the 10 entries performs 91 SQL calls and the 100 entry page 900 SQL calls...

I can only make the queries so fast before its diminishing returns for what is terrible design/programming

[bluestang]

Yeah, a night and day difference between them. Dev site loads everything so much quicker, especially the project stats for Team and Individuals. Pretty enjoyable.

[SystemViper]

Hey nice job on your in house work, amazing on how unprofessional the supposed professional coders work is.
Just with some tweeks on the dev server your rocking it.
your efforts have always been appreciated!

Excellent
SV/steve

[kiska]

I've updated the dev environment details in my first post please see it for testing