mysql-status waiting on empty queue extension

Known as one of the most popular open source database, MySQL is widely used in various scenarios, Alibaba Cloud provides high available ApsaraDB RDS for MySQL with enhanced MySQL service reduced enterprise’s database expenses, and helped enterprises utilize technology to fight against coronavirus.

 

MySQL Event Scheduler Sample Demo

As we all know, the MySQL event Scheduler is a special feature in MySQL 5.1 that can be used as a timed Task scheduler to replace some of the timing functions that were previously only available with the operating system Task Scheduler. The following articles will provide you with more comprehensive knowledge.

First. Overview

The event Scheduler is another feature added in MySQL 5.1 that can be used as a timed Task scheduler to replace some of the time > Energy that was previously available only with the operating system Task Scheduler. For example, the Crontabe in Linux can only be executed once per minute, while the MySQL event Scheduler implements one task per second, which is useful in some environments where real-time > High-performance is needed.

The event Scheduler is timed to trigger execution, and can also be called a "temporary trigger" at this point in time. A trigger simply executes some statements for the events produced by a table, while the event scheduler executes some statements at a certain (interval) time. Events are managed by a particular thread, the so-called "event Scheduler". When event Scheduler is enabled, accounts with SUPER privileges can see this thread by executing SHOW processlist. You can dynamically control whether the MySQL event scheduler is enabled by setting the value of the global variable Event_scheduler.

1. (root:localhost:) test> SET GLOBAL event_scheduler = on ;
2. (root:localhost:) test> Show Processlist\g

 Row

1. id:46147
2. User:event_scheduler
3. Host:localhost
4. Db:null
5. Command:daemon
6. Time:1
7. State:waiting on Empty queue
8. Info:null

As above, the owner of the thread is Event_scheduler.

Second, the MySQL event Scheduler application case

In this case, the event scheduler is used to invoke a stored procedure every second to determine if the SLAVE is functioning properly, if SLAVE is turned off, 0 errors are ignored, and SLAVE is restarted.

Create a stored procedure first

1. Delimiter//
2. CREATE PROCEDURE ' Slave_monitor ' ()
3. Begin
4. SELECT Variable_value into @SLAVE_STATUS
5. From INFORMATION_SCHEMA. Global_status
6. WHERE variable_name=' slave_running ';
7. IF (' on '! = @SLAVE_STATUS) Then
8. SET GLOBAL sql_slave_skip_counter=0;
9. SLAVE START;
10. END IF;
11. End //
12. delimiter;

Because statements like SHOW SLAVE STATUS cannot be called in a stored procedure, exact replication error messages and error codes cannot be obtained and further processing of SLAVE stops is not possible.

Next, create the task

1. CREATE EVENT IF not EXISTS ' Slave_monitor '
2. On SCHEDULE every 5 SECOND
3. On completion PRESERVE
4. Do
5. Call Slave_monitor ();

A task is created, executed every 5 seconds, and the task is retained after the task is completed, rather than deleted. Of course, the task in this example will not end unless it is manually disabled.

If you want to temporarily close a task while running, execute the ALTER EVENT statement:

1. (root:localhost:) test> Alter event ' Slave_monitor ' on
2. Completion PRESERVE DISABLE;
3. (root:localhost:) test> Alter event ' Slave_monitor ' on
4. Completion PRESERVE ENABLE;

The above related content is to the MySQL event Scheduler (Scheduler) Introduction, hope you can gain

Original blog:http://www.codesky.net/article/201006/147600.html

mysql-status waiting on empty queue extension

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch