search

statusengine / worker

PHP worker process that writes all event data to a storage backend
https://statusengine.org/worker/#overview
GNU General Public License v3.0
9 stars 8 forks source link

Add primary keys to mysql schema and use DBAL to manage the schema #24

Closed ibering closed 4 years ago

ibering commented 5 years ago

I have added primary keys to the MySQL schema to avoid ordering by internal InnoDB columns like DB_ROW_ID. With these primary keys the records get ordered by hostname and timestamp. This will speed up select queries.

I only made this changes to the mysql schema because CrateDB will work differently i guess.

In addition the schema for MySQL is now managed by DBAL.

$ bin/Console.php database --update --dry-run                                                                                                                                                                                      (DBAL|✚2…)
Start updating your database...
ALTER TABLE statusengine_dbversion CHANGE id id INT AUTO_INCREMENT NOT NULL
ALTER TABLE statusengine_host_acknowledgements CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE acknowledgement_type acknowledgement_type SMALLINT UNSIGNED DEFAULT 0
ALTER TABLE statusengine_host_notifications CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE reason_type reason_type SMALLINT UNSIGNED DEFAULT 0
DROP INDEX hostname_time ON statusengine_host_statehistory
ALTER TABLE statusengine_host_statehistory CHANGE hostname hostname VARCHAR(255) NOT NULL, CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE current_check_attempt current_check_attempt SMALLINT UNSIGNED DEFAULT 0, CHANGE max_check_attempts max_check_attempts SMALLINT UNSIGNED DEFAULT 0, CHANGE last_state last_state SMALLINT UNSIGNED DEFAULT 0, CHANGE last_hard_state last_hard_state SMALLINT UNSIGNED DEFAULT 0, ADD PRIMARY KEY (hostname, state_time)
DROP INDEX hostname ON statusengine_hostchecks
ALTER TABLE statusengine_hostchecks CHANGE hostname hostname VARCHAR(255) NOT NULL, CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE timeout timeout SMALLINT UNSIGNED DEFAULT 0, CHANGE current_check_attempt current_check_attempt SMALLINT UNSIGNED DEFAULT 0, CHANGE max_check_attempts max_check_attempts SMALLINT UNSIGNED DEFAULT 0, ADD PRIMARY KEY (hostname, start_time)
ALTER TABLE statusengine_hoststatus CHANGE current_state current_state SMALLINT UNSIGNED DEFAULT 0, CHANGE current_check_attempt current_check_attempt SMALLINT UNSIGNED DEFAULT 0, CHANGE max_check_attempts max_check_attempts SMALLINT UNSIGNED DEFAULT 0, CHANGE last_hard_state last_hard_state SMALLINT UNSIGNED DEFAULT 0, CHANGE acknowledgement_type acknowledgement_type SMALLINT UNSIGNED DEFAULT 0, CHANGE scheduled_downtime_depth scheduled_downtime_depth SMALLINT UNSIGNED DEFAULT 0
ALTER TABLE statusengine_logentries ADD id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (id)
ALTER TABLE statusengine_service_acknowledgements CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE acknowledgement_type acknowledgement_type SMALLINT UNSIGNED DEFAULT 0
ALTER TABLE statusengine_service_notifications CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE reason_type reason_type SMALLINT UNSIGNED DEFAULT 0
DROP INDEX servicename_time ON statusengine_service_statehistory
DROP INDEX host_servicename_time ON statusengine_service_statehistory
ALTER TABLE statusengine_service_statehistory CHANGE hostname hostname VARCHAR(255) NOT NULL, CHANGE service_description service_description VARCHAR(255) NOT NULL, CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE current_check_attempt current_check_attempt SMALLINT UNSIGNED DEFAULT 0, CHANGE max_check_attempts max_check_attempts SMALLINT UNSIGNED DEFAULT 0, CHANGE last_state last_state SMALLINT UNSIGNED DEFAULT 0, CHANGE last_hard_state last_hard_state SMALLINT UNSIGNED DEFAULT 0, ADD PRIMARY KEY (hostname, service_description, state_time)
DROP INDEX servicename ON statusengine_servicechecks
ALTER TABLE statusengine_servicechecks CHANGE hostname hostname VARCHAR(255) NOT NULL, CHANGE service_description service_description VARCHAR(255) NOT NULL, CHANGE state state SMALLINT UNSIGNED DEFAULT 0, CHANGE timeout timeout SMALLINT UNSIGNED DEFAULT 0, CHANGE current_check_attempt current_check_attempt SMALLINT UNSIGNED DEFAULT 0, CHANGE max_check_attempts max_check_attempts SMALLINT UNSIGNED DEFAULT 0, ADD PRIMARY KEY (hostname, service_description, start_time)
ALTER TABLE statusengine_servicestatus CHANGE current_state current_state SMALLINT UNSIGNED DEFAULT 0, CHANGE current_check_attempt current_check_attempt SMALLINT UNSIGNED DEFAULT 0, CHANGE max_check_attempts max_check_attempts SMALLINT UNSIGNED DEFAULT 0, CHANGE last_hard_state last_hard_state SMALLINT UNSIGNED DEFAULT 0, CHANGE scheduled_downtime_depth scheduled_downtime_depth SMALLINT UNSIGNED DEFAULT 0
No modifications where done to database!!!