Performance issue when reports > 7000

[geeknik]

Thank you for providing this amazing software to the community.

After I reached 7000 reports (ID is over 9700 though only have 7443 live reports) in my ezXSS install, I began to notice that loading https://domain/manage/dashboard after logging in was taking an inordinate amount of time to load (38 seconds to be exact). I'm curious if there are some optimizations that could be made here because when I instead load https://domain/manage/payload immediately upon logging in, the page loads instantly, however when I navigate back to https://domain/manage/dashboard, it takes longer than expected to load (38 seconds).

PHP 7.2.24-0ubuntu0.19.04.2
mysql-server 5.7.28-0ubuntu0.19.04.2

Nothing really stands out when I run mysqltuner however CPU usage appears to 100% of a single CPU core whilst this endpoint is loading. Thank you for investigating this issue.

[ssl]

Could you try updating line 97 in Component.php on your side:

from:
$last = $this->database->fetch('SELECT * FROM reports ORDER BY id DESC LIMIT 1');

to:
$last = $this->database->fetch('SELECT id,time FROM reports ORDER BY id DESC LIMIT 1');

If that improves the load time i will add this to a new release.

[geeknik]

It improved the time from 38 seconds to 23 seconds.

[geeknik]

If this helps debug further, my stats right now:

7443
total reports

31
reports last week

1d
last report

1011
total domains

18
domains last week

[ssl]

Have you tried adding an INDEX? I think it might help adding one to id and origin.

[geeknik]

Great minds think alike, I was just in the middle of doing that when I saw your notification. Adding one to id and one to origin increased the time from 23 seconds to 25 seconds.

[ssl]

I would't know any more ways to speed up this process. My load time is about 1 second with 4k reports. Could it be something to do with your database server?

[geeknik]

I got it down to less than 10 seconds by bumping up some query cache limits in MySQL. Good enough for now until 4.0 comes out and gives me a good excuse to start a new server from scratch. (; Thank you.

[ssl]

Hi @geeknik,

I've just made an performance improvement in a3ff011 for the dashboard statistics.

Could you confirm this improvement? In my tests the dashboard loads within a second instead of a few seconds..

[geeknik]

hi @ssl, I haven't updated to the latest release yet (still on a branch from 25 August) and just cherry picking that particular change causes immense MySQL load and every portion of the site now loads slower than before.

[ssl]

Hi @geeknik,

Since that release I've made multiple improvements to the SQL queries.

What causes the immense MySQL load you've got? Do you mean the a3ff011 change? It should improve it..

[ssl]

hi @geeknik,

I fully fixed this issue. At my side, with 10k reports, the old method takes about 14 seconds to load.

With the a3ff011 fix, it takes about 4 seconds.

In this new commit 6ee8c1b I've now added a small loading indicator at the dashboard statistics, and loading in the stats will now work via javascript. That way the dashboard loads within 300 ms, and the stats will be added within a few seconds, depending on how much data you have.

I also checked all SQL queries that are made by ezXSS and improved the speed and caching on all of them. The overall experience within the manage panel should be beter with these fixes.

Would love for you to check these out and get some feedback :). I will release 3.9 if you agree this fixes it (no pressure!)

[ssl]

GIF:

[geeknik]

I concur. I just upgraded from 3.5 to 3.9 and it is like night and day when logging in now. Go ahead and release 3.9.

[ssl]

Cool! I will probably release it later tonight or tomorrow. Thanks for your time and thanks for the sponsor 👍:)!