slow performance and 500 errors with large data

I've looked over older questions and just want to clarify if this is something to be optimized in v4?

In my loadtest I have 20,000 users, and it returns a 500 error. And if I downset to around 15,000 users, the page will load but runs extremely slow.

Is there anything available to optimize that I may be missing?

Jamie CeeOP•11/8/24, 9:04 AM

Example of my table

message.txt5.44KB

toeknee•11/8/24, 9:10 AM

What is this doing?

canManageAnyOrganisations

I'\d also look at:

Because you are calling
createActionGroup

and we can't see what that is.

But I have 1,000,000 users and my performance is good.

toeknee•11/8/24, 9:10 AM

providing of course you are loading only 25-50 at a time

toeknee•11/8/24, 9:10 AM

Ensure you have indexed the users correctly too.

Dennis Koch•11/8/24, 9:15 AM

I downset to around 15,000 users, the page will load but runs extremely slow.

Just to make sure. Do you have 15k users in DB and issues, or are you trying to load 15k users at once?

DDennis Koch > I downset to around 15,000 users, the page will load but runs extremely slow. ...

Jamie CeeOP•11/8/24, 9:27 AM

So default pagination to the page is set to 25, not loading at once.

Ttoeknee Ensure you have indexed the users correctly too.

Jamie CeeOP•11/8/24, 9:27 AM

How am I checking this?

Ttoeknee What is this doing? canManageAnyOrganisations I'\d also look at: ```php ...

Jamie CeeOP•11/8/24, 9:27 AM

The function is this:

Jamie CeeOP•11/8/24, 9:28 AM

Also, the canManageAnyOrganisations function is just checking that data exists.

So yeah, dont believe this is lazy loaded, could be the reason?

toeknee•11/8/24, 9:34 AM

So what you need to do is install Telescope, then check the request to that page and debuig the queries, you'll like be looping somewhere or a really poor indexed relation etc. Nothing should take more than a second. I've jsut seeded my DB with 10,000,000 records and the page still loads in 2seconds. Technically it shouldn't really slow it down if an index is there.

Jamie CeeOP•11/8/24, 9:35 AM

Yeah mines like 10 seconds with 18k users, so something must be with my code.

Ill install telescope now

Ttoeknee So what you need to do is install Telescope, then check the request to that page...

Jamie CeeOP•11/8/24, 9:50 AM

Yeah. I think its slow

JJamie Cee Yeah. I think its slow 🤣

toeknee•11/8/24, 9:50 AM

Just a little

Ttoeknee Just a little

Jamie CeeOP•11/8/24, 9:51 AM

Maybe to do with this I think then

JJamie Cee Maybe to do with this I think then

toeknee•11/8/24, 9:54 AM

Yep...

toeknee•11/8/24, 9:54 AM

So click into it, you wil be loading a user in again Imsuspect on each query

toeknee•11/8/24, 9:55 AM

First remove the actions to see if that solves, it then add them back one at a time if it does.

toeknee•11/8/24, 9:55 AM

failing that, remove the modify query and see if that helps

Ttoeknee failing that, remove the modify query and see if that helps

Jamie CeeOP•11/8/24, 10:18 AM

Im down to 700ms (still bad ik), when I take out this

So, I think its the relation to roles...

JJamie Cee Im down to 700ms (still bad ik), when I take out this ```php ->check...

toeknee•11/8/24, 10:38 AM

Good work so far though!

Ttoeknee Good work so far though!

Jamie CeeOP•11/8/24, 10:38 AM

Whenever I try to load the relationship, thats causing the delay.

Was this a fix along the lines of indexing like you mentioned?

toeknee•11/8/24, 10:39 AM

edger load the relationship

toeknee•11/8/24, 10:39 AM

But also look at the realtionship, are you using a good package with it?

Jamie CeeOP•11/8/24, 10:39 AM

I've tried with this..

THis jumps back up tyo 3 seconds

Ttoeknee But also look at the realtionship, are you using a good package with it?

Jamie CeeOP•11/8/24, 10:40 AM

Using the althinect spatie roles package

toeknee•11/8/24, 10:40 AM

So that should be well indexed as it's spatie, I use it too.

You loaded with roles so that's good. The user managedOransiations is like the issue....

toeknee•11/8/24, 10:40 AM

you are plucking constantly

Jamie CeeOP•11/8/24, 10:41 AM

even if I just return $query->with('roles'); none of the extra stuff, it still jumps to 3 seconds

Jamie CeeOP•11/8/24, 10:42 AM

TYhen it has a lot of uuids. THis is the main delay

toeknee•11/8/24, 10:44 AM

that's an additional query for each. You want to with the managedOrganisations on the user. I would put that outside. so

toeknee•11/8/24, 10:44 AM

The roles makes sense, double check the index on there, but it looks normal to me

Ttoeknee that's an additional query for each. You want to with the managedOrganisations o...

Jamie CeeOP•11/8/24, 10:46 AM

So tried this, still 3 seconds.

THe select roles query specifically is 160.00ms

toeknee•11/8/24, 10:49 AM

So 3 seconds isn't terrible. But feel free to look and optimise each bit as much as you can

Jamie CeeOP•11/8/24, 10:49 AM

toeknee•11/8/24, 10:50 AM

ChatGPT/Claude is actually quite good is you pass it your whole query and ask for optimisation ideas if you are nto a DB expert and it can make suggestions etc.

Ttoeknee ChatGPT/Claude is actually quite good is you pass it your whole query and ask fo...

Jamie CeeOP•11/8/24, 10:52 AM

Sometimes takes a bit of probing with ChatGPT, but already on it with it

Ttoeknee So 3 seconds isn't terrible. But feel free to look and optimise each bit as much...

Jamie CeeOP•11/8/24, 1:24 PM

So managed to get it to 800ms, still a little slow on page load for what I'd like, but hey ho.

These are the changes

toeknee•11/8/24, 1:26 PM

Nice

Ttoeknee So what you need to do is install Telescope, then check the request to that page...

Jamie CeeOP•11/12/24, 11:48 AM

Just a quick question with this. Im still only on 170k users as seeidng is taking ages). But do you use default pagination or simple?
I've noticed default takes longer due to the count query

JJamie Cee Just a quick question with this. Im still only on 170k users as seeidng is takin...

toeknee•11/12/24, 11:49 AM

Seeding is taking ages? Are you on a mac?

I use default, but default does count so it is slower as it needs to work on the pages. Simple doesn't

Ttoeknee Seeding is taking ages? Are you on a mac? I use default, but default does coun...

Jamie CeeOP•11/12/24, 11:50 AM

Yeah thats understandable.

Nah, on linux.

I keep getting hit by memory limit when trying to import large, so im currently on a smaller chunk and just taking ages to seed

toeknee•11/12/24, 11:50 AM

Linux should be realtivel fast, what is your use seeding looking like? Inserting right?

Ttoeknee Linux should be realtivel fast, what is your use seeding looking like? Inserting...

Jamie CeeOP•11/12/24, 11:51 AM

So currently using this

I've tried using make() instead of create to then use $users->chunk, but that was complaining about timestamp format.

This is my factory

Jamie CeeOP•11/12/24, 11:52 AM

Just keep getting hit with memory limit randomly during it all

toeknee•11/12/24, 11:52 AM

Ahh that's why your factories are slow in comparison with big data. I would tend to run a DB insert

Jamie CeeOP•11/12/24, 11:53 AM

Ah, so typically avoid factories for large inserts. Ill change and give that a go

toeknee•11/12/24, 11:53 AM

Exactly, factories are great for complex relational data. But they use the model and model events which means it slows everything down with big data.

Jamie CeeOP•11/12/24, 11:54 AM

Ill change that over and give it a run.

Ttoeknee Exactly, factories are great for complex relational data. But they use the model...

Jamie CeeOP•11/12/24, 11:56 AM

Would you still run it in a for loop typically?