Unique constraint for notification is too strong

[immae]

Notification tables (statusengine_host_statehistory, statusengine_service_notifications, statusengine_host_notifications) have a unique constraint (primary key) on (hostname, start_time, start_time_usec) or (hostname, service_description, start_time, start_time_usec) , but this unicity constraint is too strong: if we have more than one contact_name (multiple contacts) then we will have two columns with the same primary key (which provokes a constraint violation), and same if one contact_name has more than one command_name/command_args (multiple notification channels for a single contact)

Maybe the primary key constraint should be relaxed to simply an index if it was introduced for performance reasons?

[nook24]

Interesting, the value of start_time_usec should be unique (enough) for this. Are you maybe using Nagios Core instead of Naemon?

The idea behind this primary key is the fact, that InnoDB orders the data in the table by the primary key. When an auto increment ID column is used, the data gets ordered by time, but not by host or service. This could be bad for read performance. Also only a single thread could insert data into the table because the auto increment is using a mutex in MySQL (at least as far as I know) So this could become a potential bottleneck.

The hostname, start_time primary key will order the data by hosts and time. This should be good for read and write performance, but consumes a bit more memory.

I did not come across the issue you are facing so far. But I'm only using Naemon that's why I was asking if your are using Nagios Core, maybe this has a different behavior for the start_time_usec value than Naemon has.

[immae]

Thanks for your answer!

I’m using Naemon. In my database all the start_time_usec have value "0" (it seems to be the "default" value in mysql table definition). Do you know what this value corresponds to? I might have some specificities on my system.

Note that this column didn’t exist before, so I ran the migration ./bin/Console.php database --update. It showed me a bunch of mysql commands, some of them failed (for the reason given above, duplicate conflicts)

So I reran those mysql commands manually without the primary key constraint part, and it successfully created the columns. I left the primary keys out at the moment (when I rerun with --dry-run it shows me that it still has to do them)

Note that it’s a few days old now, my memory may not be "exact".

Also only a single thread could insert data into the table because the auto increment is using a mutex in MySQL (at least as far as I know) So this could become a potential bottleneck.

From what I see there is no auto-increment column in the *notification tables

[immae]

I just saw the instructions for Updating from a version < 3.7. Maybe that’s what I’m missing, I’ll check that

[immae]

I confirm it was an error on my side, everything is in order now.