Why crowdsec update last_push in CreateAlert()

[P0lskay]

What would you like to be added?

Remove locks on the machines table when calling the CreateAlert() method

Why is this needed?

Performance Degradation after CreateAlert() Method Change

Description

With the commit 91fbc63, the CreateAlert() method was modified to update the last_push field in the machines table every time it is called. This change introduces a row lock on the agent entry in the machines table. When there is a high volume of new alerts, the RPS (Requests Per Second) of a single agent is significantly limited due to database locks (see Screenshot 1).

Before the update, an agent could make up to 400 requests per second to LAPI for creating alerts. Now, it is limited to only 40 requests per second.

Proposed Solution

I suggest adding a parameter that determines whether to use the last_push field or not. This parameter would allow us to control the behavior of the CreateAlert() method and mitigate the performance issues caused by the database locks.

Screenshots

Additional Information

• Affected Commit: 91fbc63
• Affected Method: CreateAlert()
• Affected Table: machines
• Performance Impact: RPS reduced from 400 to 40

Steps to Reproduce

1. Call the CreateAlert() method multiple times.
2. Observe the last_push field updates in the machines table.
3. Measure the RPS and compare it with the performance before the commit 91fbc63.

Expected Behavior

The CreateAlert() method should not significantly impact the RPS due to database locks.

Actual Behavior

The RPS is significantly reduced due to the row locks on the machines table.

Possible Fix

Introduce a parameter to control the usage of the last_push field in the CreateAlert() method.

[github-actions]

@P0lskay: Thanks for opening an issue, it is currently awaiting triage.

In the meantime, you can:

1. Check Crowdsec Documentation to see if your issue can be self resolved.
2. You can also join our Discord.
3. Check Releases to make sure your agent is on the latest version.

Details

I am a bot created to help the crowdsecurity developers manage community feedback and contributions. You can check out my manifest file to understand my behavior and what I can do. If you want to use this for your project, you can check out the BirthdayResearch/oss-governance-bot repository.

[github-actions]

@P0lskay: There are no 'kind' label on this issue. You need a 'kind' label to start the triage process.

• /kind feature
• /kind enhancement
• /kind refactoring
• /kind bug
• /kind packaging

Details

I am a bot created to help the crowdsecurity developers manage community feedback and contributions. You can check out my manifest file to understand my behavior and what I can do. If you want to use this for your project, you can check out the BirthdayResearch/oss-governance-bot repository.