Goal:
How to purge old data if MapR metrics database is running out of disk space.Solution:
1. Identify nodes which are running hoststats service.
1 | maprcli node list -filter '[csvc==hoststats]' -columns service |
2. Identify retention policy of MapR metrics.
Log on one of above nodes, check the content of /opt/mapr/conf/db.conf.Normally the largest table in MapR metrics database is "METRIC_TRANSACTION" where the finest data granularity is kept. It is partitioned by day and has 3 partitions by default.
Parameter "db.partition.finest.count.days" controls how many days' data it will store;
Similarly, table "METRIC_TRANSACTION_SUMMARY_DAILY" by default has 15 partitions which is controlled by parameter "db.partition.fine.count.days";
Table "METRIC_TRANSACTION_SUMMARY_YEARLY" by default has 100 partitions which is controlled by parameter "db.partition.coarse.count.years".
3. Change retention policy.
If metrics database still has enough disk space currently and it is not an urgent requirement to purge old data immediately, we can change the retention policy on the nodes for 2 different services:a. Change the retention policy in /opt/mapr/conf/db.conf on all nodes which are running "hoststats" service, and then restart "hoststats" services using below command:
1 | maprcli node services -name hoststats -action restart -filter '[csvc==hoststats]' |
b. Change the retention policy in /opt/mapr/conf/db.conf on all nodes which are running webserver service, and then restart "webserver" services using below command:
1 | maprcli node services -name webserver -action restart -filter '[csvc==webserver]' |
Below command is used to identify the node of the first webserver:
1 | maprcli node list -columns "configuredservice,hostname" | grep "webserver" | head -n 1 |
If things are urgent, please follow below steps to purge the old data immediately.
4. Identify the largest table.
Normally table "METRIC_TRANSACTION" is the largest table, however it is best to double check.Go to MySQL node, and identify the data directory by checking the "--datadir" option of mysqld process.
For example:
# ps -ef|grep -i mysqld|grep -v grep
root 1270 1 0 15:45 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 1378 1270 0 15:45 ? 00:00:13 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
List files in the metrics database directory under MySQL data directory.For example, the metrics database name is "metrics":
1 | ls -altr /var/lib/mysql/metrics |
The file name includes the table name.
Assume table "METRIC_TRANSACTION" is the largest table in below steps.
5. Identify which partition(s) need to be purged.
Firstly using "show create table <tablename>" to find out how many partitions belong to this table.For example, table "METRIC_TRANSACTION" has 3 partitions -- p0, p1 and p2.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> show create table METRIC_TRANSACTION ; + --------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + --------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | METRIC_TRANSACTION | CREATE TABLE `METRIC_TRANSACTION` ( `PARTITION_ID` tinyint(4) NOT NULL , `EVENT_TIME` bigint (20) NOT NULL , `M_NAME` varchar (64) NOT NULL , `M_VALUE` bigint (20) NOT NULL , `NODE_ID` varchar (64) NOT NULL , `CLUSTER_ID` varchar (32) NOT NULL , `USER_ID` varchar (64) DEFAULT NULL , `PROCESS_ID` varchar (64) DEFAULT NULL , `CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`PARTITION_ID`,`CLUSTER_ID`,`NODE_ID`,`M_NAME`,`EVENT_TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (PARTITION_ID) (PARTITION p2 VALUES IN (2) ENGINE = InnoDB, PARTITION p1 VALUES IN (1) ENGINE = InnoDB, PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */ | + --------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
For example:
1 2 3 4 5 6 7 8 | mysql> select PARTITION_ID, max (EVENT_TIME) from METRIC_TRANSACTION group by PARTITION_ID order by 2 desc ; + --------------+-----------------+ | PARTITION_ID | max (EVENT_TIME) | + --------------+-----------------+ | 0 | 1424817768000 | | 2 | 1424814429000 | + --------------+-----------------+ 2 rows in set (0.10 sec) |
Assume the decision is to purge partition 1 and 2 of "METRIC_TRANSACTION" in below steps.
6. Drop and recreate partitions.
If partition 1 and 2 need to be dropped and recreated:1 2 3 4 5 | ALTER TABLE METRIC_TRANSACTION DROP PARTITION p1; ALTER TABLE METRIC_TRANSACTION ADD PARTITION (PARTITION p1 VALUES IN (1)); ALTER TABLE METRIC_TRANSACTION DROP PARTITION p2; ALTER TABLE METRIC_TRANSACTION ADD PARTITION (PARTITION p2 VALUES IN (2)); |
1 | show create table METRIC_TRANSACTION ; |
No comments:
Post a Comment