Filament•2mo ago

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?

35 Replies

Jamie CeeOP•2mo ago

Example of my table

message.txt

toeknee•2mo ago

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. providing of course you are loading only 25-50 at a time Ensure you have indexed the users correctly too.

Dennis Koch•2mo ago

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?

Jamie CeeOP•2mo ago

So default pagination to the page is set to 25, not loading at once. How am I checking this? 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);
    }

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•2mo ago

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•2mo ago

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

Jamie CeeOP•2mo ago

Yeah. I think its slow 🤣

toeknee•2mo ago

Just a little

Jamie CeeOP•2mo ago

Maybe to do with this I think then

toeknee•2mo ago

Yep... So click into it, you wil be loading a user in again Imsuspect on each query First remove the actions to see if that solves, it then add them back one at a time if it does. failing that, remove the modify query and see if that helps

Jamie CeeOP•2mo ago

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

toeknee•2mo ago

Good work so far though!

Jamie CeeOP•2mo ago

Whenever I try to load the relationship, thats causing the delay. Was this a fix along the lines of indexing like you mentioned?

toeknee•2mo ago

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

Jamie CeeOP•2mo ago

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 Using the althinect spatie roles package

toeknee•2mo ago

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.... you are plucking constantly

Jamie CeeOP•2mo ago

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

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•2mo ago

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);
      }
  })

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

Jamie CeeOP•2mo ago

So tried this, still 3 seconds. THe select roles query specifically is 160.00ms

toeknee•2mo ago

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

Jamie CeeOP•2mo ago

❤️

toeknee•2mo ago

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.

Jamie CeeOP•2mo ago

Sometimes takes a bit of probing with ChatGPT, but already on it with it 🤣 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•2mo ago

Nice

Jamie CeeOP•2mo ago

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

toeknee•2mo ago

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 😉

Jamie CeeOP•2mo ago

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•2mo ago

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

Jamie CeeOP•2mo ago

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),
        ];
    }

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•2mo ago

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

Jamie CeeOP•2mo ago

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

toeknee•2mo ago

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•2mo ago

Ill change that over and give it a run. Would you still run it in a for loop typically?

toeknee•2mo ago

Not as an insert no, bulk insert so loop throught he code to build the inserts in say 1000 at a time, and insert the 1000

Jamie CeeOP•2mo ago

Got to 21k inserts, then memory limit exhausted again 🤣 Ill just re-run several times still Oh wait, I've read it wrong still... However, next thing I've found is actions. Specifically edit and delete which have some model condition checks. These seem to add 200ms onto the call, is there anyway to optimise these, or is this the best way?

                        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) use ($user): bool {
                                if ($record->getKey() === $user->getKey() || $record->trashed()) {
                                    return true;
                                }

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

                        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) use ($user): bool {
                                if ($record->getKey() === $user->getKey() || $record->trashed()) {
                                    return true;
                                }

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

Gaming

Programming

slow performance and 500 errors with large data

Did you find this page helpful?