Database partitioning
Overview
Some tables in the 'centreon_storage' database are partitioned to:
- Optimize the execution time of several queries,
- Optimize data purges,
- Minimize the reconstruction of the tables with an error during a crash of the DBMS.
One partition per day is created for the following tables:
- data_bin: performance data
- logs: event logs from the monitoring engine collection.
- log_archive_host: availability data concerning hosts.
- log_archive_service: availability data concerning services.
There are some limitations:
- The maximum number of partitions (for a MariaDB table) is 1024
- Foreign keys are not supported
More details about MariaDB partitioning here.
Prerequisites
The following packages are required:
- php-mysql
- Pear-DB
- MariaDB (>= 10.1)
The MariaDB open_files_limit parameter must be set to 32000 in the [server] section:
[server]
open_files_limit = 32000
If you install Centreon on your RedHat Linux version, you will be able to do it manually. Remember to restart the mariadb processes if you change this value in my.cnf.
If you use systemd, you need to create the /etc/systemd/system/mariadb.service.d/mariadb.conf file:
[Service]
LimitNOFILE=32000
Then reload systemd and MariaDB:
systemctl daemon-reload
systemctl restart mariadb
Configuration
The data retention time is programmed in the Administration > Parameters >
Options
menu:
The setting is as follows:
- Retention duration for partitioning: retention time for partitioned tables, by default 365 days.
- Forward provisioning: number of partitions created in advance, by default 10 days.
- Backup directory for partitioning: partition backup directory, by default /var/cache/centreon/backup.
Operation
Partitioning uses XML files in the /usr/share/centreon/config/partition.d/ directory to create the necessary partitions.
Every day, a script launched by a cron creates missing tables or creates new tables in advance:
0 4 * * * centreon /bin/php /usr/share/centreon/cron/centreon-partitioning.php >> /var/log/centreon/centreon-partitioning.log 2>&1
Sample partitioning partitioning-data_bin.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<centreon-partitioning>
<table name="data_bin" schema="centreon_storage">
<activate>1</activate>
<column>ctime</column>
<type>date</type>
<createstmt>
CREATE TABLE IF NOT EXISTS `data_bin` (
`id_metric` int(11) DEFAULT NULL,
`ctime` int(11) DEFAULT NULL,
`value` float DEFAULT NULL,
`status` enum('0','1','2','3','4') DEFAULT NULL,
KEY `index_metric` (`id_metric`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</createstmt>
</table>
</centreon-partitioning>
Migrating unpartitioned tables
The command line performs the following procedure:
- Rename existing table (‘xxx’ will be ‘xxx_old’)
- Create an empty partitioned table
- Migrate data into the partitioned table (with ‘SELECT INSERT’ statement)
You need to check a few things before executing the command:
- Enough space on the MariaDB Server (at least twice the size of the data and indexes)
- No data in the future (time is used for the partitioning)
- Enough memory on the database server
The ‘SELECT INSERT’ statement will lock the table and maybe your production in some points (for example table migration of ‘logs’).
To perform table migration, use the -m option and specify the name of the table to migrate:
/bin/php /usr/share/centreon/bin/centreon-partitioning.php -m data_bin
If the table migration is ok, the old table can be deleted with the following commands:
DROP TABLE centreon_storage.data_bin_old;
Monitoring of partitioning operation
The Centreon Database Monitoring Connector allows you to check that the number of partitions created in advance is sufficient. It is recommended that you install and deploy this Monitoring Connector.
It is also possible to view the partitioned tables and the consumption
associated with each partition via the Administration > Platform Status >
Databases
menu:
More general information on the state of health of the databases is also present: