Shulou(Shulou.com)05/31 Report--

What I want to share with you in this article is about how Mysql RELICATION handles the storage. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Yesterday, Hawkeye needs to delete 47026788 data from a large table (27G).

Using stored procedures through primary key deletion; it took 1 hour and 50 minutes

QPS=47026788/ (60,110) = 7125.2709

This speed is so fast that it is all read at random; [@ more@]

At that time, I was wondering if it took nearly two hours for MASTER, but did it take so long for SLAVE?

When I went to SLAVE, there was no SQL running at all.

And the data in the table has been emptied.

Through the test, we find that MASTER is out of date in the application, and what is recorded in BINLOG is the real last DML operation.

For example:

# 101202 13:58:43 server id 2 end_log_pos 15842 Query thread_id=4058 exec_time=0 error_code=0

SET timestamp 1291269523

Delete from test where id = NAME_CONST (238)

/ *! * /

# at 15842

# 101202 13:58:43 server id 2 end_log_pos 15974 Query thread_id=4058 exec_time=0 error_code=0

SET timestamp 1291269523

Delete from test where id = NAME_CONST

/ *! * /

# at 15974

# 101202 13:58:43 server id 2 end_log_pos 16106 Query thread_id=4058 exec_time=0 error_code=0

SET timestamp 1291269523

Delete from test where id = NAME_CONST ('vastly entryful)

/ *! * /

...

So as soon as the MASTER is executed, it will be applied to SLAVE immediately.

Here is an example of a stored procedure that deletes data:

=

Use test

Drop procedure if exists delete_expired

Delimiter / /

CREATE PROCEDURE delete_expired (in in_date date)

BEGIN

Declare done int default 0

Declare rowcnt int default 0

Declare v_entry_id int

Declare cur_del cursor For select id from test

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1

Open cur_del

Start transaction

Cursor_loop:loop

Fetch cur_del into v_entry_id

If done=1 then leave cursor_loop

End if

Delete from test where id = v_entry_id

Set rowcnt=rowcnt+1

If rowcnt=1000 then

Set rowcnt = 0

Commit

Start transaction

End if

End loop cursor_loop

Commit

Close cur_del

END; / /

DELIMITER

Call delete_expired ('2010-01-10')

Drop procedure if exists delete_expired

=

The above is how Mysql RELICATION deals with storage, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.