Loadbalancers taking too long after upgrade to1.64 commit #11527

after upgrade to 1.64 i found that commit #11527 made intense sql query which is in my case:
SQL[select * from component where disabled = ? and ignore = ? and device_id = ? [0,0,160] 36.78ms]
update:
the sql query is not the problem, the problem is in the setComponentPrefs function inside Component.php
its just take too long to update the DB, before this commit the update is taking way faster

36.78ms is intense? Could you dig in a little further and see what is taking longer? That commit fixed a lot of bugs in the “Component” code so it could have been that nothing was being updated before. It also could have introduced new bugs. I would need a lot more detailed information about what you are seeing if that is the case.

1 Like

its not query i updated my replay :slight_smile:
amm, i can only tell that its the function setComponentPrefs
i dont know what exactly taking very long. im pretty sure that block of code is the slowest one:
\App\Models\Component::whereIn(‘id’, array_keys($updated))
->with(‘prefs’)
->get()
honestly, im a python guy and its my first time dealing with laravel, i can guess this line is making the query to the DB to get the desired rows.
maybe we can make it more efficient somehow
tell me what kind of info do you need, maybe we can insert logs in the code to investigate further.

so yes, this is an Eloquent (active record) db query. It gets all the component entries with given ids and loads all the preferences from component_prefs table related to those components.

You can run that line by hand by doing php artisan tinker as the librenms user. Then just paste it in and hit enter.

Edit: you will need to specify the ids to fetch. select id from component where disabled = 0 and ignore = 0 and device_id = 160 would give you those, I’m guessing. (or \App\Models\Component::where('device_id', 160)->get() in tinker should get you close enough.

many thanks!
i found out that:
unset($updated[$component->id][‘type’]); // can’t change type
is the problem, i dont know what its doing but its making it slow.
why this variable is unsetted? why he cannot be written to the DB again?

Odd. So setComponentPrefs() receives an array of components with updated fields and preferences.
“type” is not allowed to be updated after creation so it removes it from that array.
after that it fills in the fields that can be updated on the component itself and updates them in the db.
Then it filters out all the reserved fields and updates/creates/deletes the rest as component_prefs entries.

the unset in this huge array is very intense, im guessing that is the reason why my servers cpu is allways over 80% after the upgrade…
what about unsetting this value only for updated components, i mean unsetting this value before the $component->save() (inside the if statement)
update:
removing unset($updated[$component->id][‘type’]);
and now it is looking something like this:
unset($component[‘type’]);
$component->save();
dramatically improves the execution time. i dont know if its fault tolerance but im leaving it like that for now, my cpu util dropped by 60% as well.

That is kind of shocking that unset would take so much cpu.

how many items are in $updated[$component->id]?

Maybe if we did:

$update = $updated[$component->id];
unset($update['type']);

shit, actually PHP is copy on write. So unset causes the $updated array to be copied for each loop.
The above code would fix that. Let me know if it helps and I can create a PR, unless you would like to, you did a lot of the heavy lifting here.

1 Like

we have over 9000 pool members so its pretty big.
you mean?
\App\Models\Component::whereIn(‘id’, array_keys($updated))
->with(‘prefs’)
->get()
->each(function (\App\Models\Component $component) use ($updated) {
// update component attributes
$update = $updated[$component->id];
unset($update[‘type’]);
$component->fill($update);
if ($component->isDirty()) {
its working good that way… ill do the PR i wanna be in the contributors in the next release :slight_smile:

1 Like

Great!

There could probably be some additional optimizations made to the sql queries. I had assumed that would be unnecessary because I had only seen usage with 1-2 components and 0-3 prefs.