Performance issue when reports > 7000 about ezxss HOT 14 CLOSED

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!)

from ezxss.

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

from ezxss.

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.

from ezxss.

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.

from ezxss.

It improved the time from 38 seconds to 23 seconds.

from ezxss.

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

from ezxss.

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

from ezxss.

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.

from ezxss.

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?

from ezxss.

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.

from ezxss.

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..

from ezxss.

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.

from ezxss.

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..

from ezxss.

GIF:

from ezxss.