Re: SQL deadlock

[Henning Bopp (boppy)]

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 <address@hidden>:

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