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:

                self::createActionGroup(
                    label: 'More Actions',
                    actions: [
                        Tables\Actions\Action::make('unblock')
                            ->label('Unblock')
                            ->tooltip('Unblock This User')
                            ->icon('heroicon-s-shield-check')
                            ->color(Color::Cyan)
                            ->visible(fn($record) => $record->is_blocked)
                            ->action(function (User $record) {
                                $record->update(['is_blocked' => false]);
                            }),
                        Tables\Actions\EditAction::make()
                            ->tooltip('Edit User')
                            ->color('warning')
                            ->hidden(fn(Model $record) => $record->trashed())
                            ->label('Edit User'),
                        Tables\Actions\DeleteAction::make()
                            ->tooltip('Delete User')
                            ->hidden(function (Model $record): bool {
                                if ($record->getKey() === auth()->user()->getKey()) {
                                    return true;
                                }

                                if ($record->trashed()) {
                                    return true;
                                }

                                return false;
                            })
                            ->label('Delete User'),
                    ]
                ),

                self::createActionGroup(
                    label: 'More Actions',
                    actions: [
                        Tables\Actions\Action::make('unblock')
                            ->label('Unblock')
                            ->tooltip('Unblock This User')
                            ->icon('heroicon-s-shield-check')
                            ->color(Color::Cyan)
                            ->visible(fn($record) => $record->is_blocked)
                            ->action(function (User $record) {
                                $record->update(['is_blocked' => false]);
                            }),
                        Tables\Actions\EditAction::make()
                            ->tooltip('Edit User')
                            ->color('warning')
                            ->hidden(fn(Model $record) => $record->trashed())
                            ->label('Edit User'),
                        Tables\Actions\DeleteAction::make()
                            ->tooltip('Delete User')
                            ->hidden(function (Model $record): bool {
                                if ($record->getKey() === auth()->user()->getKey()) {
                                    return true;
                                }

                                if ($record->trashed()) {
                                    return true;
                                }

                                return false;
                            })
                            ->label('Delete User'),
                    ]
                ),

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:

    protected static function createActionGroup(string $label, array $actions): ActionGroup
    {
        return ActionGroup::make($actions)
            ->icon('heroicon-s-chevron-down')
            ->tooltip($label)
            ->button()
            ->label(false)
            ->color(Color::Neutral);
    }

    protected static function createActionGroup(string $label, array $actions): ActionGroup
    {
        return ActionGroup::make($actions)
            ->icon('heroicon-s-chevron-down')
            ->tooltip($label)
            ->button()
            ->label(false)
            ->color(Color::Neutral);
    }

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

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

    public function canManageAnyOrganisations(): bool
    {
        return $this->managedOrganisations()->exists();
    }

    public function canManageAnyOrganisations(): bool
    {
        return $this->managedOrganisations()->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

            ->checkIfRecordIsSelectableUsing(function (Model $record) {
                // if ($record->hasRole(RoleConstants::SUPER_ADMIN)) {
                //     return false;
                // }

                // if ($record->getKey() === auth()->user()->getKey()) {
                //     return false;
                // }

                return true;
            });

            ->checkIfRecordIsSelectableUsing(function (Model $record) {
                // if ($record->hasRole(RoleConstants::SUPER_ADMIN)) {
                //     return false;
                // }

                // if ($record->getKey() === auth()->user()->getKey()) {
                //     return false;
                // }

                return true;
            });

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

            ->modifyQueryUsing(function ($query) {
                $user = auth()->user();

                $query->with('roles');

                if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
                    return $query;
                }

                if ($user->checkPermissionTo('User.view-any')) {
                    return $query;
                }

                if ($user->canManageAnyOrganisations()) {
                    return $query->whereIn('id', $user->managedOrganisations()->pluck('user_id'));
                }
            })

            ->modifyQueryUsing(function ($query) {
                $user = auth()->user();

                $query->with('roles');

                if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
                    return $query;
                }

                if ($user->checkPermissionTo('User.view-any')) {
                    return $query;
                }

                if ($user->canManageAnyOrganisations()) {
                    return $query->whereIn('id', $user->managedOrganisations()->pluck('user_id'));
                }
            })

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

select
  `roles`.*,
  `model_has_roles`.`model_uuid` as `pivot_model_uuid`,
  `model_has_roles`.`role_id` as `pivot_role_id`,
  `model_has_roles`.`model_type` as `pivot_model_type`
from
  `roles`
  inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id`
where
  `model_has_roles`.`model_uuid` in (

select
  `roles`.*,
  `model_has_roles`.`model_uuid` as `pivot_model_uuid`,
  `model_has_roles`.`role_id` as `pivot_role_id`,
  `model_has_roles`.`model_type` as `pivot_model_type`
from
  `roles`
  inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id`
where
  `model_has_roles`.`model_uuid` in (

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

$user = auth()->user();
$org_user_id = $user->managedOrganisations()->pluck('user_id')

  ->modifyQueryUsing(function ($query) use ($user, $org_user_id) {
      $user = auth()->user();

      $query->with('roles');

      if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
          return $query;
      }

      if ($user->checkPermissionTo('User.view-any')) {
          return $query;
      }

      if ($user->canManageAnyOrganisations()) {
          return $query->whereIn('id', $org_user_id);
      }
  })

$user = auth()->user();
$org_user_id = $user->managedOrganisations()->pluck('user_id')

  ->modifyQueryUsing(function ($query) use ($user, $org_user_id) {
      $user = auth()->user();

      $query->with('roles');

      if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
          return $query;
      }

      if ($user->checkPermissionTo('User.view-any')) {
          return $query;
      }

      if ($user->canManageAnyOrganisations()) {
          return $query->whereIn('id', $org_user_id);
      }
  })

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.

    public static function table(Table $table): Table
    {
        $user = auth()->user();

        $org_user_id = Cache::remember("user_{$user->id}_can_manage_organisations", now()->addMinutes(10), function () use ($user) {
            return $user->managedOrganisations()->pluck('user_id');
        });

        // Get roles and permissions once
        // Cache::forget("user_{$user->id}_permissions");
        $canManageOrganisations = Cache::remember("user_{$user->id}_permissions", now()->addMinutes(10), function () use ($user) {
            if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
                return true;
            }

            if ($user->checkPermissionTo('User.view-any')) {
                return true;
            }

            return $user->canManageAnyOrganisations();
        });

        return $table
            ->modifyQueryUsing(function ($query) use ($user, $org_user_id) {
                if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
                    return $query;
                }

                if ($user->checkPermissionTo('User.view-any')) {
                    return $query;
                }

                if ($user->canManageAnyOrganisations()) {
                    return $query->whereIn('id', $org_user_id);
                }
            })

    public static function table(Table $table): Table
    {
        $user = auth()->user();

        $org_user_id = Cache::remember("user_{$user->id}_can_manage_organisations", now()->addMinutes(10), function () use ($user) {
            return $user->managedOrganisations()->pluck('user_id');
        });

        // Get roles and permissions once
        // Cache::forget("user_{$user->id}_permissions");
        $canManageOrganisations = Cache::remember("user_{$user->id}_permissions", now()->addMinutes(10), function () use ($user) {
            if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
                return true;
            }

            if ($user->checkPermissionTo('User.view-any')) {
                return true;
            }

            return $user->canManageAnyOrganisations();
        });

        return $table
            ->modifyQueryUsing(function ($query) use ($user, $org_user_id) {
                if ($user->hasRole(RoleConstants::SUPER_ADMIN)) {
                    return $query;
                }

                if ($user->checkPermissionTo('User.view-any')) {
                    return $query;
                }

                if ($user->canManageAnyOrganisations()) {
                    return $query->whereIn('id', $org_user_id);
                }
            })

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

    public function run(): void
    {
        \Illuminate\Support\Facades\Log::info('Running seeder');

        for ($i = 0; $i < 50000; $i++) {
            \Illuminate\Support\Facades\Log::info('Creating 250 users: ' . $i);
            User::factory()->count(250)->create();
        }

        \Illuminate\Support\Facades\Log::info('End of user seeding');
    }

    public function run(): void
    {
        \Illuminate\Support\Facades\Log::info('Running seeder');

        for ($i = 0; $i < 50000; $i++) {
            \Illuminate\Support\Facades\Log::info('Creating 250 users: ' . $i);
            User::factory()->count(250)->create();
        }

        \Illuminate\Support\Facades\Log::info('End of user seeding');
    }

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

This is my factory

    public function definition(): array
    {
        return [
            'id' => Str::uuid(),
            'name' => fake()->name(),
            'email' => fake()->unique()->userName() . \Illuminate\Support\Str::random(12) . '@example.com',
            'email_verified_at' => null,
            'password' => Hash::make('password'),
            'remember_token' => Str::random(10),
        ];
    }

    public function definition(): array
    {
        return [
            'id' => Str::uuid(),
            'name' => fake()->name(),
            'email' => fake()->unique()->userName() . \Illuminate\Support\Str::random(12) . '@example.com',
            'email_verified_at' => null,
            'password' => Hash::make('password'),
            'remember_token' => Str::random(10),
        ];
    }

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

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 16384 bytes) in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/MySqlConnection.php on line 47
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/html/vendor/laravel/framework/src/Illuminate/Cache/RateLimiting/GlobalLimit.php on line 5

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 16384 bytes) in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/MySqlConnection.php on line 47
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/html/vendor/laravel/framework/src/Illuminate/Cache/RateLimiting/GlobalLimit.php on line 5

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?

self::createActionGroup( label: 'More Actions', actions: [ Tables\Actions\Action::make('unblock') ->label('Unblock') ->tooltip('Unblock This User') ->icon('heroicon-s-shield-check') ->color(Color::Cyan) ->visible(fn($record) => $record->is_blocked) ->action(function (User $record) { $record->update(['is_blocked' => false]); }), Tables\Actions\EditAction::make() ->tooltip('Edit User') ->color('warning') ->hidden(fn(Model $record) => $record->trashed()) ->label('Edit User'), Tables\Actions\DeleteAction::make() ->tooltip('Delete User') ->hidden(function (Model $record): bool { if ($record->getKey() === auth()->user()->getKey()) { return true; } if ($record->trashed()) { return true; } return false; }) ->label('Delete User'), ] ),

protected static function createActionGroup(string $label, array $actions): ActionGroup { return ActionGroup::make($actions) ->icon('heroicon-s-chevron-down') ->tooltip($label) ->button() ->label(false) ->color(Color::Neutral); }

->checkIfRecordIsSelectableUsing(function (Model $record) { // if ($record->hasRole(RoleConstants::SUPER_ADMIN)) { // return false; // } // if ($record->getKey() === auth()->user()->getKey()) { // return false; // } return true; });

->modifyQueryUsing(function ($query) { $user = auth()->user(); $query->with('roles'); if ($user->hasRole(RoleConstants::SUPER_ADMIN)) { return $query; } if ($user->checkPermissionTo('User.view-any')) { return $query; } if ($user->canManageAnyOrganisations()) { return $query->whereIn('id', $user->managedOrganisations()->pluck('user_id')); } })

select `roles`.*, `model_has_roles`.`model_uuid` as `pivot_model_uuid`, `model_has_roles`.`role_id` as `pivot_role_id`, `model_has_roles`.`model_type` as `pivot_model_type` from `roles` inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id` where `model_has_roles`.`model_uuid` in (

$user = auth()->user(); $org_user_id = $user->managedOrganisations()->pluck('user_id') ->modifyQueryUsing(function ($query) use ($user, $org_user_id) { $user = auth()->user(); $query->with('roles'); if ($user->hasRole(RoleConstants::SUPER_ADMIN)) { return $query; } if ($user->checkPermissionTo('User.view-any')) { return $query; } if ($user->canManageAnyOrganisations()) { return $query->whereIn('id', $org_user_id); } })

public static function table(Table $table): Table { $user = auth()->user(); $org_user_id = Cache::remember("user_{$user->id}_can_manage_organisations", now()->addMinutes(10), function () use ($user) { return $user->managedOrganisations()->pluck('user_id'); }); // Get roles and permissions once // Cache::forget("user_{$user->id}_permissions"); $canManageOrganisations = Cache::remember("user_{$user->id}_permissions", now()->addMinutes(10), function () use ($user) { if ($user->hasRole(RoleConstants::SUPER_ADMIN)) { return true; } if ($user->checkPermissionTo('User.view-any')) { return true; } return $user->canManageAnyOrganisations(); }); return $table ->modifyQueryUsing(function ($query) use ($user, $org_user_id) { if ($user->hasRole(RoleConstants::SUPER_ADMIN)) { return $query; } if ($user->checkPermissionTo('User.view-any')) { return $query; } if ($user->canManageAnyOrganisations()) { return $query->whereIn('id', $org_user_id); } })

public function run(): void { \Illuminate\Support\Facades\Log::info('Running seeder'); for ($i = 0; $i < 50000; $i++) { \Illuminate\Support\Facades\Log::info('Creating 250 users: ' . $i); User::factory()->count(250)->create(); } \Illuminate\Support\Facades\Log::info('End of user seeding'); }

public function definition(): array { return [ 'id' => Str::uuid(), 'name' => fake()->name(), 'email' => fake()->unique()->userName() . \Illuminate\Support\Str::random(12) . '@example.com', 'email_verified_at' => null, 'password' => Hash::make('password'), 'remember_token' => Str::random(10), ]; }

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 16384 bytes) in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/MySqlConnection.php on line 47 PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/html/vendor/laravel/framework/src/Illuminate/Cache/RateLimiting/GlobalLimit.php on line 5