i got 28k users and it give me error Allowed memory size of 134217728 bytes exhausted
i got 28k users and it give me error Allowed memory size of 134217728 bytes exhausted it give me intermitent error with 500 503 error if i delete 5000 users it going well anyone can help me? lapiccolaitalia.cl its a restorant page, when i go to the menu, and i change from categories, it give me error 500 or 503 intermitent in the ajax of products
[2022-12-14 00:07:18] local.ERROR: Allowed memory size of 134217728 bytes exhausted (tried to allocate 8119008 bytes) {"exception":"[object] (Symfony\Component\ErrorHandler\Error\FatalError(code: 0): Allowed memory size of 134217728 bytes exhausted (tried to allocate 8119008 bytes) at /home/lapiccolaitalia/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117) [stacktrace] #0 {main} "}
Hi there,
It seems like your PHP memory limit is set to the default 128MB which does not seem to be enough for the operation that you are trying to do.
There are two ways to handle this:
-
Increase your memory limit to 256MB or 512MB depdning on the available memory on your server. You can do that by editing your
php.inifile, finding thememory_limitoption and increase it to the desired value. Note that in some cases once you change the value, you would need to restart PHP-FPM or your webserver depending on your setup. -
Alternatively if you don't want to increase your PHP memory limit, I would recommend deleting the 5000 users in smaller batches like deleting 1000 users 5 times or deleting 500 users 10 times.
Hope that this helps.
Best,
Bobby
1
[2022-12-14 00:07:18] local.ERROR: Allowed memory size of 134217728 bytes exhausted (tried to allocate 8119008 bytes) {"exception":"[object] (Symfony\Component\ErrorHandler\Error\FatalError(code: 0): Allowed memory size of 134217728 bytes exhausted (tried to allocate 8119008 bytes) at /home/lapiccolaitalia/public_html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117) [stacktrace] #0 {main} "}
Hi Bobby,
Thank you for your prompt response. We have recently seen a significant increase in our user base, with 200 simultaneous users over the weekend and 12k more users added each month. This has put an immense strain on our server, as the SQL query for each user has exceeded 128M.
I believe that our user model may be the source of the issue and I would be extremely grateful for your assistance in resolving it. Your help would be greatly appreciated.
Thank you for your time.
Regards Lucciano Vanella
Hi Bobby We can increase memory but increasing users in time will cause the problem to explode again in near future. Is any way to solve the way wave manages user resources?. We saw with this users quantity even with no more than two users connected it still produces error 500. usually we have 200 simultaneous users on weekend days. but the problems seems to be the rows size in users table. we need a solution ASAP may be you can quote the service to fix it. Best Regards Walter
Hi there Lucciano,
It seems like I've misunderstood you initially. So the 500 error does not happen when you try to do a large deletion of 5000 users but just the size data that is causing the problem.
What you could do is to just add some indexes on the users table for the fields that you are doing the filtering on, eg where().
I would recommend the following:
- Install the Laravel debug bar for your local development environment:
composer require barryvdh/laravel-debugbar --dev
- Then visit the problematic page
- Check the queries that are executed on that page
What you need to look into is:
- Make sure that there are no 'N+1 Queries'
- Check the larger queries and make sure to add indexes on them
You can use the EXPLAIN statement on your queries to see which indexes are used or where indexes are not used, eg:
EXPLAIN SELECT * FROM users WHERE users.username = 'some_name' OR user.name = 'some_name';
Note: change the SELECT query with the problematic query that you are seeing in the debug bar
Additinally, you could follow the steps here on how to optimize your MySQL services itself:
How to optimize MySQL to speed up your Laravel application with Releem?
In addition to the above, here are some general tips on how to optimize Laravel:
How To Optimize Laravel for Performance
Note that, this problem is not really Wave specific or related to Wave, but it looks like a standard Laravel optimization problem.
Let me know how it goes.
1
Happy to hear that you've got it all working now! Thank you for sharing the solution here! I will look into patching this too so that other users don't hit the same problem!
1
I've submitted a PR here:
Rather than getting ::all() users, we can check if there is at least 1 user which should be more performant.
Let me know if anything else pops up!