Diagnosing performance issues on the site

Grab a beer and let's chit chat.
kiska
Posts: 10
Joined: Mon Aug 30, 2021 1:04 am

Diagnosing performance issues on the site

Post by 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
Last edited by kiska on Sat Feb 25, 2023 9:25 pm, edited 1 time in total.
User avatar
UBT - Timbo
Posts: 19
Joined: Sat Jan 28, 2023 5:54 pm
Location: NW Midlands, England
Contact:

Re: Diagnosing performance issues on the site

Post by 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
Last edited by UBT - Timbo on Sun Feb 19, 2023 3:07 am, edited 1 time in total.
Image
Image
Skillz
Posts: 150
Joined: Sat Aug 28, 2021 5:22 pm
Location: High Point, NC
Contact:

Re: Diagnosing performance issues on the site

Post by 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.
Do not send me PMs. The PM system here doesn't notify like it's supposed. Contact me through Discord (link at top) or post in the forums here. I check those regularly. At least until I figure out the PM notification problem.
User avatar
UBT - Timbo
Posts: 19
Joined: Sat Jan 28, 2023 5:54 pm
Location: NW Midlands, England
Contact:

Re: Diagnosing performance issues on the site

Post by UBT - Timbo »

Skillz wrote: Sat Feb 18, 2023 6:04 pm 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.
Skillz wrote: Sat Feb 18, 2023 6:04 pm 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.
Skillz wrote: Sat Feb 18, 2023 6:04 pm 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)
Last edited by UBT - Timbo on Sat Feb 18, 2023 8:34 pm, edited 1 time in total.
Image
Image
Skillz
Posts: 150
Joined: Sat Aug 28, 2021 5:22 pm
Location: High Point, NC
Contact:

Re: Diagnosing performance issues on the site

Post by 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.
Do not send me PMs. The PM system here doesn't notify like it's supposed. Contact me through Discord (link at top) or post in the forums here. I check those regularly. At least until I figure out the PM notification problem.
User avatar
UBT - Timbo
Posts: 19
Joined: Sat Jan 28, 2023 5:54 pm
Location: NW Midlands, England
Contact:

Re: Diagnosing performance issues on the site

Post by UBT - Timbo »

Skillz wrote: Sat Feb 18, 2023 8:14 pm 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.
Skillz wrote: Sat Feb 18, 2023 8:14 pm 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
Image
Image
kiska
Posts: 10
Joined: Mon Aug 30, 2021 1:04 am

Re: Diagnosing performance issues on the site

Post by kiska »

UBT - Timbo wrote: Sat Feb 18, 2023 8:48 pm
Skillz wrote: Sat Feb 18, 2023 8:14 pm 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
Posts: 44
Joined: Fri Jun 03, 2022 4:13 am
Contact:

Re: Diagnosing performance issues on the site

Post by 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.
User avatar
SystemViper
Posts: 62
Joined: Thu Jun 02, 2022 5:04 pm
Location: Earth
Contact:

Re: Diagnosing performance issues on the site

Post by 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
Posts: 10
Joined: Mon Aug 30, 2021 1:04 am

Re: Diagnosing performance issues on the site

Post by kiska »

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