Heavy load on mariadb - multi servers environment

crowdsec
1

Hi all,

I have 1x LAPI server with ~15x crowdsec servers. The mariadb server encounter a permanent heavy load due to mariadb process. With 4 vcpu and 12 GB RAM, the load is ~20 and the %cpu is 400%.

Is it normal ? I suppose no of course :pensive:
I did not find tuning guide of mysql/mariadb from crowdsec (indexing some fields ?, etc …)

Here is what i can see : when i run a mysql command : “show full processlist” :
35 crowdsec 127.0.0.1:56116 crowdsec Execute 12953 Storing result in query cache

SELECT DISTINCT decisions.id, decisions.created_at, decisions.updated_at, decisions.until, decisions.scenario, decisions.type, decisions.start_ip, decisions.end_ip, decisions.start_suffix, decisions.end_suffix, decisions.ip_size, decisions.scope, decisions.value, decisions.origin, decisions.simulated, decisions.uuid, decisions.alert_decisions FROM decisions LEFT JOIN decisions AS t1 ON t1.value = decisions.value AND t1.type = decisions.type AND t1.scope = decisions.scope AND t1.until > decisions.until WHERE ((decisions.until > ‘2024-04-03 09:38:47.758721842’ AND t1.until IS NULL) AND NOT decisions.simulated) AND decisions.scope IN (‘Ip’, ‘Range’) ORDER BY decisions.id ASC 0.000

This mysql request is a little bit amazing … in it’s complexity of course but also for it’s processing time (12953 seconds !!!) If i understand clearly, this request is played one for every server connected to the LAPI server.

I specify that our decision table contains 1,000,000 entries (ban time is 168h) for 1,400 alerts.

I tried to activate mysql cache, etc … I didn’t get any results.
What can i do now ?
Thank you,
Stephane.

2

Yeah we use an ORM to generate the sql statements, this query is for the remediation components that can query every 10 seconds so if it takes 12 then you see the problem. MYSQL seems to have poor performance compared to other solutions that we tested with the ORM, I suggest for you to turn on chunking mode which should improve the LAPI performance, however, I dont know if it will improve mariadb

Within the current feature flags you should see chunked decisions stream

3

Hi @iiAmLoz
Thank you for your help.
chunking is turn on now, but the problem is the same. it didn’t bring anything
I added all fields (from join and order clause) as index , but i does nothing better.

1 million entries in the decisions table, is this usual ? Isn’t that too much ?

Is it possible to flush the decision table easily ?

And finally is crowdsec able to support One LAPI server with more than 10 servers/agents base on mysql ? What is the bigger installation known ?

Thanks.
Stephane.

4

Yes, we have enterprise customers that have thousands of endpoints. The main issue is you shouldnt be banning for long period of time, CrowdSec is designed to issue small time scale bans less than 24 hours as by the time 164 hours is up most likely the ip address has changed multiple hands as most residential ips are not static in nature

you can flush alerts more often by tuning the flushing parameters CrowdSec Configuration | CrowdSec or by running cscli alerts flush warning it may impact database alot

5

I was able to flush alerts from 1500 to 500 items with “flush”
But decision table was very little impacted (always beyond 1,000,000).
I am able to flush decision table ?
Thank you again.
Stephane.

6

The decisions have a cascade to delete if the parent alert has been deleted, I dont know if any indexes or alterations to the table may impact this behaviour.

7

Finally i flush alerts, decisions, events & meta tables …
I will carefully observe the evolution time after time.
Stephane.