I have about 36 end points being monitored using webseer.
I started to notice the DELETE FROM plugin_webseer_servers_log WHERE UNIX_TIMESTAMP(lastcheck) < 1504645687 taking minutes to complete in the mysql show processlist;
I made a small change to the query on line 268 of the webseer_process.php. I changed
db_execute('DELETE FROM plugin_webseer_servers_log
WHERE UNIX_TIMESTAMP(lastcheck) < ' . (time() - (86400 * 90)));
to
db_execute('DELETE FROM plugin_webseer_servers_log
WHERE lastcheck < FROM_UNIXTIME(' . (time() - (86400 * 90)) . ')');
By shifting the unix timestamp to the variable instead of the column mysql can now utilize the indexing on the column.
root@localhost [cacti]> explain DELETE FROM plugin_webseer_servers_log WHERE UNIX_TIMESTAMP(lastcheck) < 1504645687\G
1. row
id: 1
select_type: DELETE
table: plugin_webseer_servers_log
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1025030
filtered: 100.00
Extra: Using where
1 row in set (0.01 sec)
Now after making the change
root@localhost [cacti]> explain DELETE FROM plugin_webseer_servers_log WHERE lastcheck < FROM_UNIXTIME(1504645687)\G
1. row
id: 1
select_type: DELETE
table: plugin_webseer_servers_log
partitions: NULL
type: range
possible_keys: lastcheck
key: lastcheck
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set (0.01 sec)
The process now finishes in under a second on my system. I thought I would share this with you in case you wanted to test or implement it in your next release.
I have about 36 end points being monitored using webseer.
I started to notice the DELETE FROM plugin_webseer_servers_log WHERE UNIX_TIMESTAMP(lastcheck) < 1504645687 taking minutes to complete in the mysql show processlist;
I made a small change to the query on line 268 of the webseer_process.php. I changed
to
By shifting the unix timestamp to the variable instead of the column mysql can now utilize the indexing on the column.
root@localhost [cacti]> explain DELETE FROM plugin_webseer_servers_log WHERE UNIX_TIMESTAMP(lastcheck) < 1504645687\G 1. row id: 1 select_type: DELETE table: plugin_webseer_servers_log partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1025030 filtered: 100.00 Extra: Using where 1 row in set (0.01 sec)
Now after making the change
root@localhost [cacti]> explain DELETE FROM plugin_webseer_servers_log WHERE lastcheck < FROM_UNIXTIME(1504645687)\G 1. row id: 1 select_type: DELETE table: plugin_webseer_servers_log partitions: NULL type: range possible_keys: lastcheck key: lastcheck key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using where 1 row in set (0.01 sec)
The process now finishes in under a second on my system. I thought I would share this with you in case you wanted to test or implement it in your next release.
Thanks