failure Mariadb/Galera Maximum writeset size exceeded

[carraroj]

Describe the bug

If the icingadb.service is stopped for a longer period of time in large environments while the icingadb-redis.service is still running, the following error occurs after a short time when starting up the icingadb.service, Icingadb tries to delete old entries in icingdb.service_state table from the database, because too much entries > 100.000

Error 1105 (HY000): Maximum writeset size exceeded                                                            
 can't perform "DELETE FROM \"service_state\" WHERE id IN (?)"

The problem can also be recognized on the writing node of the Galera with the following error:

2025-04-11  0:07:24 631972 [ERROR] WSREP: rbr write fail, data_len: 0
2025-04-11  0:07:38 631972 [Warning] WSREP: transaction size limit (2147483647) exceeded: 2147483648

The problem is that the mariadb variable from Galera has this maximum value the could'nt be raised.

Variable_name: wsrep_max_ws_size
        Value: 2147483647

To solve this issue: TRUNCATE TABLE service_state; in icingadb.

To Reproduce

Provide a link to a live example, or an unambiguous set of steps to reproduce this bug. Include configuration, logs, etc. to reproduce, if relevant.

1. Stopp icingadb in HA Icinga Setups with active icingadb-redis.service
2. Start icingadb after few days and error occurs.

Expected behavior

icingadb should be able to UPDATE/DELETE service_state table after shutoff for few days.

Your Environment

Include as many relevant details about the environment you experienced the problem in

• Icinga DB version: Icinga DB version: v1.2.1
• Icinga 2 version: r2.13.10-1
• Operating System and version: Oracle Enterprise Linux 8.10

[oxzi]

Thanks for this report. After having gotten a bit more information about this via watercooler conversations, I wanted to add that the amount of parameters in the query should be restrictable through the max_placeholders_per_statement database option.

[oxzi]

Thanks again for reporting. Since this is another case of "Galera does not exactly behaves as MySQL/MariaDB would", this was included in the new Operations documentation section via #950. You can take a peek here at GitHub or online after the next Icinga DB release.

However, as long as the documentation does not state any concrete values, I would keep this issue open. After some future testing, either the documentation can be updated or we could consider adding a check in the code, self-testing if the current connection "smells" like Galera and adjusting the values automatically.

[tbauriedel]

However, as long as the documentation does not state any concrete values, I would keep this issue open. After some future testing, either the documentation can be updated or we could consider adding a check in the code, self-testing if the current connection "smells" like Galera and adjusting the values automatically.

The wsrep_max_ws_size is unfortunately not a value that can be "tuned". The range is limited to 2GB (which is also the default value).
A concrete value would therefore have to be specified for max_placeholders_per_statement. Unfortunately, I am aware that it will be difficult to find a good "default value" here.

Thanks for tackling this and adding it in the documentation. If you still need input, please let me know.
Side question: What do you think about a "tuning guide" for IcingaDB (+ redis)? Something like this issue be tackled here. You would not need to give some "default values", but could lead in the right direction.

[oxzi]

Thanks for tackling this and adding it in the documentation. If you still need input, please let me know.

Well, if you have any concrete experience with adjusting the config, please feel free to share your max_placeholders_per_statement and max_rows_per_transaction values- or any other insights you have that are not yet covered by the new docs.

Side question: What do you think about a "tuning guide" for IcingaDB (+ redis)? Something like this issue be tackled here. You would not need to give some "default values", but could lead in the right direction.

The mentioned operations section has a small Redis subsection, effectively mentioning memory overcomitting and linking to the Redis admin doc.

What do you do for "tuning"? I am open for all hints.

[tbauriedel]

What do you do for "tuning"? I am open for all hints.

I'll collect the information we have in our team and then pass it to you.

CC @RincewindsHat

[RincewindsHat]

Added a Redis not with #981. Regarding MariaDB/MySQL/Galera I have to look up again what worked.