Database is too large

[richman1000000]

We will be very grateful, if your problem was described as completely as possible,
enclosing excerpts from logs (if possible within DEBUG mode, if no errors evident
within INFO mode), and configuration in particular of effected relevant settings
(e.g., with fail2ban-client -d | grep 'affected-jail-name' for a particular
jail troubleshooting).
Thank you in advance for the details, because such issues like "It does not work"
alone could not help to resolve anything!
Thanks! (remove this paragraph and other comments upon reading)

Environment:

Fill out and check ([x]) the boxes which apply. If your Fail2Ban version is outdated,
and you can't verify that the issue persists in the recent release, better seek support
from the distribution you obtained Fail2Ban from

• Fail2Ban version (including any possible distribution suffixes): Fail2Ban v0.10.2
• OS, including release name/version: debian 10.7
• [x ] Fail2Ban installed via OS/distribution mechanisms
• [ x] You have not applied any additional foreign patches to the codebase
• [ x] Some customizations were done to the configuration (provide details below is so)

The issue:

database is keeps growing too big, fail2ban consumes a lot of CPU and memory with size of database

Steps to reproduce

install in WHITE IP interface.
set up ban time to 1 year

Expected behavior

Most of the time we don't need to know why some IP was banned. especially for SSH bans.
line in configuration which will allow us to choose whether we need or not a recording of "data" column in table "bans"

Observed behavior

database grows really large cause column "data" have too much information

Any additional information

as a workaround I used sqlight3 command and purged column manually

update bans set data=NULL

Configuration, dump and another helpful excerpts

Any customizations done to /etc/fail2ban/ configuration

Relevant parts of /var/log/fail2ban.log file:

preferably obtained while running fail2ban with loglevel = 4

Relevant lines from monitored log files in question:

[sebres]

It is depending on the maxretry value specified for the jail, but this can be solved with another option dbmaxmatches (implemented in #2402), which can help to hold database slimmer. In newer version it is 5 by default (so saves maximal 5 last noticed failures per ticket).
Also note there was an issue (#1267) with purge (resolved in 0.11), so the database did not clean periodically (therefore grows).
The "fix" for that would be either to switch to 0.11 or as workaround periodically purge it manually (in cron or timer), see #1316 (comment).

[richman1000000]

Well, would prefer to not record in 1st place
This is just a work around.

[sebres]

Well, would prefer to not record in 1st place

Well, in mentioned newer versions following entries would do exactly that (not record at all):
fail2ban.local:

[DEFAULT]
dbmaxmatches = 0

jail.local:

[DEFAULT]
maxmatches = 0

I don't know what you're calling a workaround.

[richman1000000]

maxmatches = 0 doesn't affect content of "DATA" column in table "bans".
I actually don't want to limit maxmatches

By reducing DATABASE size reduces memory consumption and CPU consumption without limiting speed of fail2ban actual performance during "restoring bans" process

in practice I need only "ip" and "timeofban" to be filled.

I don't know what you're calling a workaround.
This is why I think maxmatches and direct sqlite3 cleaning are workarounds.

The true solution of this behavior is possibility to switch recording into "DATA" and "JAIL" column on/off only in case necessary.

[sebres]

maxmatches = 0 doesn't affect content of "DATA" column in table "bans".

This is false, maxmatches = 0 would avoid that fail2ban accumulates logged failure messages in the ticket in memory (but so it would restrict saving it to db too), dbmaxmatches = 0 is affecting database only (but it is a jail independent setting).
Both would cause that the DATA column contains only few info about the ticket.

I actually don't want to limit maxmatches

Then specify dbmaxmatches = 0 only.

The true solution of this behavior is possibility to switch recording into "DATA" and "JAIL" column on/off only in case necessary.

You can completely disable database (just it is not advisable at least for 0.11 if incremental banning is enabled, because it is used for searching for bad ips). As for jail column it is expected to distinguish the tickets of different jails (if you have only one jail, this doesn't mean other people using it also with single jail only). By the way, newest version of fail2ban would get completely different schema (where jail is only an ID) and failures (if not disabled) are stored in different table also.

[MrPeteH]

Something is completely undocumented AFAIK:

• ASSUME: I want to use Incremental banning; my formula allows up to 15 bans which produces an 8 month ban time.
• I don't need a super huge database. In particular, the "data" field is only needed for most-recent bans

QUESTIONS:

• Does setting dbmaxmatches = 0 impact my formula?
• Does setting maxmatches = 0 impact my formula?
• Does purging the sqlite3 database impact my formula?
  ASSUME I will have a number of reboots in the 8 month max-ban timeframe.

[sebres]

Does setting dbmaxmatches = 0 impact my formula?
Does setting maxmatches = 0 impact my formula?

Answer is "No" for 2 first of your questions.

• dbmaxmatches holds small the database (no matches stored there)
• maxmatches holds small the fail manager list (the memory) - no matched lines are stored in memory, but one cannot use <matches> tag in actions.

Does purging the sqlite3 database impact my formula?

Conditionally - fail2ban would not know the "bad" intruders anymore and those last bancount (how many times they were already banned), so it'd start to count from 1 for all IPs. All that regardless a reboots (known as bad IPs are searching in database only, not in memory).

Note that fail2ban has also an automatic purge function, which would remove non-persistent bans those starttime of ban exceeds purgeage and last bantime 3 times exceeds purgearge.