Re: SQL deadlock

I don't think you well be happy with MySQL/Maria with an amount of data you
plan to insert.

Quick Math:
**900 Hosts, 5 Entities each, 2 Minutes per Cycle.**

Resulting in:

900 * 5 / 2 = 2,250 Entries per Minute

Or multiplied by 1440 = 3,240,000 Entries per Day (only basic data without
a single alert!)

Story Time: In a project I was working for (**5 years ago!**), we got stuck
at about 180 M Entries on a MySQL, where we did not yet encounter any
problems with PostgreSQL hitting 1 B Entries recently. Our problems have
been at selecting data (from 3 seconds (10 M entries) to 2 hours (200 M
entries)). With PostgreSQL we are at about 2-4 minutes at 1 B rows!

I think this problem also scales with inserts, since all indexes are
updated constantly...

So I'll totally recommend using PostgreSQL if you are planning to store the
data long-term (where long term is kind of < one week for your setup ;-)).

One point five other notes: You use MariaDB Server 5.5 - it's initial
release was Feb 2012. They have had massive improvements over the last
years, so if you would like to try, I can only recommend to check against
the latest MariaDB Server.

Yours

Henning

Am Di., 29. Okt. 2019 um 14:22 Uhr schrieb David Jones <[email protected]>:

> I have about 900 monit hosts reporting to a single M/monit VM running
> CentOS 7.7.  I had to tune out (expand) the network port ranges to handle
> the simultaneous connections.  Now I have to use monit itself to restart
> the mmonit service several times a day when this shows up in
> /var/log/messages.  Is there any documentation or guidelines for setting up
> and tuning the MariaDB 5.5 database server?  I have a local database on the
> VM but I could move the DB to a more powerful, physical MariaDB 10.3
> cluster if needed.
>
>
>
> Oct 29 08:02:08 monit01 mmonit[3956]: SQLException: Deadlock found when
> trying to get lock; try restarting transaction when storing hostid 12020
> statistics
>
> Oct 29 08:02:08 monit01 mmonit: SQLException: Deadlock found when trying
> to get lock; try restarting transaction when storing hostid 12020 statistics
>
> Oct 29 08:02:21 monit01 mmonit[3956]: SQLException: Deadlock found when
> trying to get lock; try restarting transaction when storing hostid 6928
> statistics
>
> Oct 29 08:02:21 monit01 mmonit: SQLException: Deadlock found when trying
> to get lock; try restarting transaction when storing hostid 6928 statistics
>
>
>
> Thanks,
>
> Dave
>