Shulou(Shulou.com)06/01 Report--

problem

Today, I came to the company, opened zabbix as usual, and suddenly found that no data had been collected, so I quickly checked other graphics and found that there was no data, so I located the problem on the server side. I checked the log and found that the zabbix log suddenly reported that the query history and history_uint failed, and the clock could not be found in the partition.

Solution process

The following is only a solution that takes history as an example, not history_unit for the time being

The first cognition is the problem of zoning, which is not enough, so we start to look up the method of creating zoning on the Internet.

Alter table history partition by range (clock) (partition p1 values less than MAXVALUE)

I gave a large value to MAXVALUE here, and I didn't finish the execution for 20 minutes, and found that this method didn't work.

Check to see if automatic partitioning is set

Show create table history

Found that there are a lot of clock partitions, found that clock is the date, just in the early hours of this morning, and no partition was generated all morning, so the data could not be saved. Know the reason, first solve, do not discuss why there is the problem of automatic partitioning.

If you find a problem, solve the problem first and create a partition.

CALL partition_create ("zabbix", "history", "p201706140000", 1497456000)

The prompt does not have this partition_create stored procedure, so it cannot be executed. I wonder, let's not solve this first.

To create this stored procedure, it is recommended to refer to: https://www.zabbix.org/wiki/Docs/howto/mysql_partition#partition_create (there are too many unreliable online)

Enter the database and execute the following statement: (root login, I don't know if it will affect it)

DELIMITER $$CREATE PROCEDURE `partition_ create` (SCHEMANAME varchar (64), TABLENAME varchar (64), PARTITIONNAME varchar (64), CLOCK int) BEGIN / * SCHEMANAME = The DB schema in which to make changes TABLENAME = The table with partitions to potentially delete PARTITIONNAME = The name of the partition to create * / / * Verify that the partition does not already exist * / DECLARE RETROWS INT SELECT COUNT (1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description > = CLOCK; IF RETROWS = 0 THEN / * 1. Print a message indicating that a partition was created. 2. Create the SQL to create the partition. 3. Execute the SQL from # 2. * / SELECT CONCAT ("partition_create (", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ") AS msg; SET @ sql = CONCAT ('ALTER TABLE', SCHEMANAME,'.', TABLENAME, 'ADD PARTITION (PARTITION', PARTITIONNAME, 'VALUES LESS THAN (', CLOCK,');') PREPARE STMT FROM @ sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF;END$$DELIMITER

Then perform the creation

Ok, create all tables that do not have partitions at this time, and then restart zabbix.

Now it's time to find out the cause of the problem.

Because the colleague who dealt with zabbix earlier did the partition and sub-table, and then set up a scheduled task in cron to regularly add the table area mysql-uzabbix-pxxx-e "CALL partition_maintenance_all ('zabbix') for a few days;" because after I took over, I didn't know what it was. After I summed up the zabbix server, the mysql password changed and couldn't be executed. It took me a whole morning, but when I made the zabbix partition process clear, it was rewarding.

Now it's time to summarize the partition tables.

View stored procedures: select specific_name from mysql.proc

View process logic: show create procedure partition_create\ G

Delete stored procedure: drop procedure if exists partition_maintenance_all

View stored procedures: show procedure status like 'partition_maintenance%'\ G

Modify stored procedures: ALTER {PROCEDURE | FUNCTION} sp_name [characteriss] create stored procedures according to official documents, including: create, drop, maintenance, verify, maintenance_all. It is best to modify maintenance_all according to its own conditions to reduce the pressure on the database.

DELIMITER $$CREATE PROCEDURE `partition_maintenance_ all` (SCHEMA_NAME VARCHAR (32)) BEGIN CALL partition_maintenance (SCHEMA_NAME, 'history', 15,24,7); CALL partition_maintenance (SCHEMA_NAME,' history_log', 30,24,7); CALL partition_maintenance (SCHEMA_NAME, 'history_str', 30,24,7); CALL partition_maintenance (SCHEMA_NAME,' history_text', 30,24,7) CALL partition_maintenance (SCHEMA_NAME, 'history_uint', 15,24,7); CALL partition_maintenance (SCHEMA_NAME,' trends', 60,24,7); CALL partition_maintenance (SCHEMA_NAME, 'trends_uint', 60,24,7); END$$DELIMITER

Finally do the finishing touches

In order to prevent us from often performing the process of creating and deleting partitions, we should make full use of the partition_maintenance_all stored procedure, join the cron, and execute the process at eight o'clock every Monday to create and delete the process.

0 8 1 * * mysql-uroot-pxxx zabbix-e "CALL partition_maintenance_all ('zabbix');"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.