Fixing large mysql ibdata1 resulting from ranger audits
Table Partitioning in MySQL: (Version 5.1.6 or above)
Note: Before starting backup/restore please stop all running application which usage XA_ACCESS_AUDIT table. this will be help for keeping snapshot of XA_ACCESS_AUDIT for particular timestamp.
Table Partitioning in MySQL:-
Partitioned tables created with MySQL versions prior to 5.1.6 cannot be read by 5.5 or higher version MySQL Server. In addition, the INFORMATION_SCHEMA database tables cannot be used if such tables are present on MySQL 5.1.6 server. Beginning with MySQL 5.1.7, a suitable warning message is generated instead, to alert the user that incompatible partitioned tables have been found by the server. The maximum possible number of partitions for a given table (that does not use the NDB storage engine) is 1024.
Horizontal partitioning — Different rows of a table may be assigned to different physical partitions. MySQL 5.1 does not support Vertical partitioning, in which different columns of a table are assigned to different physical partitions.
Partitioning applies to all data and indexes of a table;
Check whether your MySQL Server supports partitioning by means of a S HOW VARIABLES statement such as this one:
SHOW VARIABLES LIKE '%partition%';
Available Partitioning type in MySQL 5.1 : Range , List, Hash, Key
Range Partitioning : This type of partitioning assigns rows to partitions based on column values falling within a given range. A table that is partitioned by a range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator.
Reference MySQL 5.1:
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html http://dev.mysql.com/doc/refman/5.1/en/alter-table.html http://dev.mysql.com/doc/refman/5.1/en/partitions-table.html
Reference MySQL 5.6:
http://dev.mysql.com/doc/refman/5.6/en/partitioning.html http://dev.mysql.com/doc/refman/5.6/en/alter-table.html http://dev.mysql.com/doc/refman/5.6/en/partitions-table.html
others:
http://mysql.rjweb.org/doc.php/partitionmaint http://www.zabbix.org/wiki/Docs/howto/mysql_partitioning#Creating_partitions_by_day
Table Partitioning in MySQL 5.1 :
1.1) Create xa_access_audit table with partitions: If xa_access_audit table does not exist then you can create partition by adding PARTITION statement in CREATE table script . below given CREATE table statement creates partitions based on day of the month.
CREATE TABLE `xa_access_audit` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, `added_by_id` bigint(20) DEFAULT NULL, `upd_by_id` bigint(20) DEFAULT NULL, `audit_type` int(11) NOT NULL DEFAULT '0', `access_result` int(11) DEFAULT '0', `access_type` varchar(255) DEFAULT NULL, `acl_enforcer` varchar(255) DEFAULT NULL, `agent_id` varchar(255) DEFAULT NULL, `client_ip` varchar(255) DEFAULT NULL, `client_type` varchar(255) DEFAULT NULL, `policy_id` bigint(20) DEFAULT '0', `repo_name` varchar(255) DEFAULT NULL, `repo_type` int(11) DEFAULT '0', `result_reason` varchar(255) DEFAULT NULL, `session_id` varchar(255) DEFAULT NULL, `event_time` datetime DEFAULT NULL, `request_user` varchar(255) DEFAULT NULL, `action` varchar(2000) DEFAULT NULL, `request_data` varchar(2000) DEFAULT NULL, `resource_path` varchar(2000) DEFAULT NULL, `resource_type` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`,`event_time`), KEY `xa_access_audit_added_by_id` (`added_by_id`), KEY `xa_access_audit_upd_by_id` (`upd_by_id`), KEY `xa_access_audit_cr_time` (`create_time`), KEY `xa_access_audit_up_time` (`update_time`), KEY `xa_access_audit_event_time` (`event_time`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE(DAYOFMONTH(`event_time`))( PARTITION P_START VALUES LESS THAN(1), PARTITION P_DAY1 VALUES LESS THAN(2), PARTITION P_DAY2 VALUES LESS THAN(3), PARTITION P_DAY3 VALUES LESS THAN(4), PARTITION P_DAY4 VALUES LESS THAN(5), PARTITION P_DAY5 VALUES LESS THAN(6), PARTITION P_DAY6 VALUES LESS THAN(7), PARTITION P_DAY7 VALUES LESS THAN(8), PARTITION P_DAY8 VALUES LESS THAN(9), PARTITION P_DAY9 VALUES LESS THAN(10), PARTITION P_DAY10 VALUES LESS THAN(11), PARTITION P_DAY11 VALUES LESS THAN(12), PARTITION P_DAY12 VALUES LESS THAN(13), PARTITION P_DAY13 VALUES LESS THAN(14), PARTITION P_DAY14 VALUES LESS THAN(15), PARTITION P_DAY15 VALUES LESS THAN(16), PARTITION P_DAY16 VALUES LESS THAN(17), PARTITION P_DAY17 VALUES LESS THAN(18), PARTITION P_DAY18 VALUES LESS THAN(19), PARTITION P_DAY19 VALUES LESS THAN(20), PARTITION P_DAY20 VALUES LESS THAN(21), PARTITION P_DAY21 VALUES LESS THAN(22), PARTITION P_DAY22 VALUES LESS THAN(23), PARTITION P_DAY23 VALUES LESS THAN(24), PARTITION P_DAY24 VALUES LESS THAN(25), PARTITION P_DAY25 VALUES LESS THAN(26), PARTITION P_DAY26 VALUES LESS THAN(27), PARTITION P_DAY27 VALUES LESS THAN(28), PARTITION P_DAY28 VALUES LESS THAN(29), PARTITION P_DAY29 VALUES LESS THAN(30), PARTITION P_DAY30 VALUES LESS THAN(31), PARTITION P_DAY31 VALUES LESS THAN(32), PARTITION P_FUTURE VALUES LESS THAN MAXVALUE );
1.2) Add Partitions feature in nonpartitioned xa_access_audit table: If xa_access_audit table exist then you can create partition by adding PARTITION statement in ALTER table script . We can add partition feature in multiple ways. You can refer mysql documents and create/add partitions according to your requirement and available mysql version.
Please read Rule-1: You have to make sure that the partition attributes are part of all primary and unique keys of the table. So you might have to execute below given ALTER statement to add event_time in the primary key. Please note that changing index takes longer if there are too many records available in the table.
ALTER TABLE xa_access_audit DROP PRIMARY KEY , ADD PRIMARY KEY ( id, event_time )
Please find few examples of creating partition in xa_access_audit, you can use any one of them to enable partition support in xa_access_audit or you can create one according to your requirement to implement the same.
1.2.1) Day of month wise partition: Create Partition on existing xa_access_audit table based on day(1-31) of month.
ALTER TABLE xa_access_audit PARTITION BY RANGE( DAYOFMONTH( EVENT_TIME))( PARTITION P_START VALUES LESS THAN(1), PARTITION P_DAY1 VALUES LESS THAN(2), PARTITION P_DAY2 VALUES LESS THAN(3), PARTITION P_DAY3 VALUES LESS THAN(4), PARTITION P_DAY4 VALUES LESS THAN(5), PARTITION P_DAY5 VALUES LESS THAN(6), PARTITION P_DAY6 VALUES LESS THAN(7), PARTITION P_DAY7 VALUES LESS THAN(8), PARTITION P_DAY8 VALUES LESS THAN(9), PARTITION P_DAY9 VALUES LESS THAN(10), PARTITION P_DAY10 VALUES LESS THAN(11), PARTITION P_DAY11 VALUES LESS THAN(12), PARTITION P_DAY12 VALUES LESS THAN(13), PARTITION P_DAY13 VALUES LESS THAN(14), PARTITION P_DAY14 VALUES LESS THAN(15), PARTITION P_DAY15 VALUES LESS THAN(16), PARTITION P_DAY16 VALUES LESS THAN(17), PARTITION P_DAY17 VALUES LESS THAN(18), PARTITION P_DAY18 VALUES LESS THAN(19), PARTITION P_DAY19 VALUES LESS THAN(20), PARTITION P_DAY20 VALUES LESS THAN(21), PARTITION P_DAY21 VALUES LESS THAN(22), PARTITION P_DAY22 VALUES LESS THAN(23), PARTITION P_DAY23 VALUES LESS THAN(24), PARTITION P_DAY24 VALUES LESS THAN(25), PARTITION P_DAY25 VALUES LESS THAN(26), PARTITION P_DAY26 VALUES LESS THAN(27), PARTITION P_DAY27 VALUES LESS THAN(28), PARTITION P_DAY28 VALUES LESS THAN(29), PARTITION P_DAY29 VALUES LESS THAN(30), PARTITION P_DAY30 VALUES LESS THAN(31), PARTITION P_DAY31 VALUES LESS THAN(32), PARTITION P_FUTURE VALUES LESS THAN MAXVALUE );
Above given sql statement shall create 33 partitions. Partitions from P_DAY1 to P_DAY31 are actual partitions. P_START and P_FUTURE partitions will handle underflow/overflow/error data. Lower partition number represents early calendar day of any month. i.e partition P_DAY1 shall represent record of 1st Day of the month.
1.2.2) Month Wise Partition: Create Partition on existing xa_access_audit table based on month(1-12) of year.
ALTER TABLE xa_access_audit PARTITION BY RANGE(MONTH (EVENT_TIME)) ( PARTITION P_START VALUES LESS THAN(1), PARTITION P_JAN VALUES LESS THAN(2), PARTITION P_FEB VALUES LESS THAN(3), PARTITION P_MAR VALUES LESS THAN(4), PARTITION P_APR VALUES LESS THAN(5), PARTITION P_MAY VALUES LESS THAN(6), PARTITION P_JUN VALUES LESS THAN(7), PARTITION P_JUL VALUES LESS THAN(8), PARTITION P_AUG VALUES LESS THAN(9), PARTITION P_SEP VALUES LESS THAN(10), PARTITION P_OCT VALUES LESS THAN(11), PARTITION P_NOV VALUES LESS THAN(12), PARTITION P_DEC VALUES LESS THAN(13), PARTITION P_FUTURE VALUES LESS THAN MAXVALUE );
Above given sql statement shall create 14 partitions. Partitions from P_JAN to P_DEC are actual partitions. P_START and P_FUTURE partitions will handle underflow/overflow/error data.
1.2.3) Day of Year Wise Partition: Create Partition on existing xa_access_audit table based on dayofyear(1-366) of any year.
ALTER TABLE xa_access_audit PARTITION BY RANGE( DAYOFYEAR (EVENT_TIME)) ( PARTITION P_START VALUES LESS THAN(1), PARTITION P_JAN1 VALUES LESS THAN(2), PARTITION P_JAN2 VALUES LESS THAN(3), PARTITION P_JAN3 VALUES LESS THAN(4), ----------------- ----------------- ----------------- ----------------- ----------------- PARTITION P_DEC29 VALUES LESS THAN(364), PARTITION P_DEC30 VALUES LESS THAN(365), PARTITION P_DEC31 VALUES LESS THAN(366), PARTITION P_FUTURE VALUES LESS THAN MAXVALUE );
Above given sql statement shall create 367 partitions. Partitions from P_JAN1 to P_DEC31 are actual partitions. P_START and P_FUTURE partitions will handle underflow/overflow/error data.
1.2.4) Day Wise Partition: Create Partition on existing xa_access_audit table based on calendar days.
ALTER TABLE xa_access_audit PARTITION BY RANGE( TO_DAYS (EVENT_TIME)) (
PARTITION `P_START` VALUES LESS THAN (TO_DAYS('2014-01-01 00:00:00')),
PARTITION `P_2014JAN1` VALUES LESS THAN (TO_DAYS('2014-01-02 00:00:00')),
PARTITION `P_2014JAN2` VALUES LESS THAN (TO_DAYS('2014-01-03 00:00:00')),
PARTITION `P_2014JAN3` VALUES LESS THAN (TO_DAYS('2014-01-04 00:00:00')),
.....
....
.....
PARTITION `P_2014JAN31` VALUES LESS THAN (TO_DAYS('2014-02-01 00:00:00')),
PARTITION `P_FUTURE` VALUES LESS THAN MAXVALUE );
Above given sql statement shall create 33 partitions. Partitions from P_2014JAN1 to P_2014JAN31 are actual partitions. P_START and P_FUTURE partitions will handle underflow/overflow/error data.
1.3) Maintenance of Partitions:
1.3.1) Drop Partition: Dropping a partition will delete the partition along with data. You can delete one or more partitions in a single ALTER table statement.
ALTER TABLE xa_access_audit DROP PARTITION <partition_name1, partition_name2...>
Make sure you don’t need the partitions before deleting them as you can’t add them again unless you delete all partitions of the table and recreate the partitions again. Adding a previous partition back is not possible, however you can split any existing partition in two and reorganize the partition using REORGANIZE PARTITION statement. Dropping a partition shall not free disk space if you have not turned on file per tablespace option mentioned in Suggestion-1.
1.3.2) Truncate Partition: In MySQL 5.5 or above, it is possible to delete rows from selected partitions using the T RUNCATE PARTITION option. This statement will keep the partition and delete data of mentioned partition only. You should use TRUNCATE statement rather than DROP statement.
ALTER TABLE xa_access_audit TRUNCATE PARTITION <partition_name1, partition_name2...>
When you use this fast truncation technique with the option Suggestion-1 enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as described in Reclaiming Disk Space with TRUNCATE TABLE . If you do not have the InnoDB Plugin installed, issue the OPTIMIZE TABLE statement to free the disk space for the table.
1.3.3) Reorganise Partition: If you intend to change the partitions of a table without losing data, use ALTER TABLE … REORGANIZE PARTITION. REORGANIZE operations copy only changed partitions and do not touch the unchanged ones.
If you have a created partition like given in 1.2.4 then you can use REORGANIZE clause to create future partitions.
ALTER TABLE xa_access_audit REORGANIZE PARTITION P_FUTURE INTO
(
PARTITION P_2014FEB01 VALUES LESS THAN (TO_DAYS('2014-02-02 00:00:00')),
PARTITION P_FUTURE VALUES LESS THAN MAXVALUE
);
1.3.4) Rebuilding partitions: Rebuilds the partition; this has the same effect as deleting all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.
ALTER TABLE xa_access_audit REBUILD PARTITION <partition_name1, partition_name2...>
1.3.5)Analyzing partitions: This reads and stores the key distributions for partitions.
ALTER TABLE xa_access_audit ANALYZE PARTITION <partition_name1, partition_name2...>
1.3.6) Repairing partitions: This repairs corrupted partitions.
ALTER TABLE xa_access_audit REPAIR PARTITION <partition_name1, partition_name2...>
1.3.7) Checking partitions: You can check partitions for errors in the same way that you use CHECK TABLE statement with nonpartitioned tables. This will tell you if the data or indexes in partition p1 of table t1 are corrupted. If this is the case, use ALTER TABLE … REPAIR PARTITION to repair the partition.
ALTER TABLE xa_access_audit CHECK PARTITION <partition_name1, partition_name2...>
1.3.8) Remove/Disable Partitioning:
ALTER TABLE xa_access_audit REMOVE PARTITIONING
1.4) Reclaiming Disk space:
1.4.1) Reclaim Disk space from partitioned table without dropping a partition:
Below given steps works as expected if innodb_file_per_table is ON.
MySQL version 5.5 or above: You can use truncate statement mentioned in 1.3.2 .
MySQL version 5.1: If you are using a mysql version previous to 5.5 then you can use the below given steps to do some workaround.
a) Take backup of the table data which you are planning to delete. i.e
mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql or SELECT * INTO OUTFILE 'data_path.sql' from table_name where event_time<’2014-10-01’
b) Delete Records : If you have taken the backup successfully and/or you don’t need specific data any more then you can delete those records by using DELETE statement.
| Delete Records : i.e delete records older than date ‘2014-10-15’ |
DELETE FROM xa_access_audit WHERE EVENT_TIME<’2014-10-15’ |
| Delete Records of particular month : i.e delete records of august month |
DELETE FROM xa_access_audit WHERE MONTH(EVENT_TIME)=8 |
| Delete Records of given date range: i.e delete records after 2014-10-15 and before 2014-10-20 |
DELETE FROM xa_access_audit WHERE EVENT_TIME>’2014-10-15’ and EVENT_TIME<’2014-10-20’ |
Note: Deletion of records in INNODB mysql database engine does not free disk space automatically, to reclaim disk space either optimize the partition or truncate/drop a partition/table to reclaim disk space occupied by specific partition/table.
c) Reclaim free space: Use steps 1.3.4 and 1 .3.5 to reclaim disk space.
1.4.2) Reclaim Disk space from nonpartitioned table:
a) If innodb_file_per_table is OFF:
I) Delete unwanted records from using DELETE statement. skip this step if not applicable.
Syntax : DELETE FROM <TABLE_NAME> [WHERE <conditions>]
II) Take Backup of required/available data of Table. skip this step if not applicable.
mysqldump -u <db_username> -h <db_host> -p <db_name> <table_name> > <tablebackupfile_name.sql>
III) DROP and CREATE TABLE.
IV) Import Data : import using below command. skip this step if not applicable.
Syntax : mysql -u root -p password db_name < "backupfile.sql"
b) If innodb_file_per_table is ON :
I) Delete unwanted records from using DELETE statement. skip this step if not applicable.
Syntax : DELETE FROM <TABLE_NAME> [WHERE <conditions>]
II) Take Backup of required/available data of Table. skip this step if not applicable.
mysqldump -u <db_username> -h <db_host> -p <db_name> <table_name> > <tablebackupfile_name.sql>
III) TRUNCATE TABLE.
Syntax : TRUNCATE TABLE <TABLE_NAME>
IV) Import Data : skip this step if not applicable.
Syntax : mysql -u root -p password db_name < "backupfile.sql"
Please note that if you are dropping xa_access_audit and recreating it then privileges given on that table need to given again
GRANT INSERT ON xasecure_audit.xa_access_audit TO ‘xalogger’@'localhost' ; GRANT INSERT ON xasecure_audit.xa_access_audit TO ‘xalogger’@'%' ; FLUSH PRIVILEGES;"
MySQL Partition Rules:
R1: All columns used in the partitioning expression for a partitioned table must be a part of every unique key that the table might have. In other words, every unique key on the table must use every column in the table’s partitioning expression. (This also includes the table’s primary key, since it is by definition a unique key)
R2: DROP PARTITION statement can be used to drop one or more RANGE, LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded.
MySQL Performance Related Suggestions:
S1: Use separate tablespace for each table of database : By enabling file per table (see innodb_file_per_table ) You can store each I nnoDB table and its indexes in its own file. You can reclaim disk space when truncating or dropping a table. Please note that you need to restart mysql server after enabling this option in my.cnf/my.inf file. This will require a backup of database, drop database and restore.
Steps to implement :
- Backup all your existing databases with mysqldump.
- Drop all databases except the mysql database
- Stop the MySQL server
- Change the config file to include the following in the [mysqld] section of the config file(my.cnf/my.inf) : innodb_file_per_table=1
- Restart MySQL server
- Import all databases again
S2: To Prepare MySQL for Production use.
1) MySQLDump all databases(with schema and data) into a SQL text file (i.e SQLData.sql)
2) Drop all databases (except mysql schema)
3) Shutdown mysql server/service
4) Add the following lines to /etc/my.cnf
[mysqld] innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_log_file_size=1G innodb_buffer_pool_size=4G
Sidenote: Whatever you set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
5) Delete ibdata1, ib_logfile0 and ib_logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
6) Restart mysql
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
7) Reload SQLData.sql into mysql
ibdata1 will grow but only contain table metadata
Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table
- mytable.frm (Storage Engine Header)
- mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.
With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.
S3: If your mysql data partition and/or mysql installation partition is having less disk space or facing disk space issue often its possible that your mysql server crashes and you can not connect to server to delete data or repair mysql server. To overcome this situation you should keep a garbage file (i.e donotdeletemedearadmin.help) of minimum 100mb and move the file to some other partition or delete it. This shall help you to restart the mysql server and do maintenance work. Don’t forget to place the garbage file back to mysql partition.
S4: Swap Space: make sure your production server has enough swap space i.e double of RAM size.
S5: To achieve better performance from xa_access_audit table MySQL Administrator can use the below given steps.
a) Create another table(i.e xa_access_audit_history) similar to xa_access_audit table
b) Move record older than N days from xa_access_audit to xa_access_audit_history. (you can automate this by using cron job: (i.e every night move 7 days old records to xa_access_audit_history ). Please note that if you move records or delete any record from xa_access_audit t able then you can not view them in XA Admin UI tool.
c) The above two steps will help you to maintain xa_access_audit table o f almost fixed size for daily audit operation.
d) To reclaim disk space of xa_access_audit_history table you can take backup of the table and truncate it.
S6: use ANALYZE statement to boost indexes.
ANALYZE TABLE <TABLE_NAME>
Source
Hortonworks
