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

Today, I will talk to you about the deadlock failure and solutions caused by full disk space in the production database. Many people may not know much about it. In order to make you understand better, the editor summarized the following contents for you. I hope you can get something according to this article.

Today, we mainly introduce an accident caused by full disk space and its solution.

1. View deadlock

Recently, business feedback can not log on to the system. I need to cooperate with me to check. The reason for looking at the deadlock here is that this is usually the reason why there is something wrong with the system.

Select * from information_schema.INNODB_LOCKS;show processlist

two。 View blocking locks

Take a look at what statements are blocked in this.

To see which thread is blocked by which, waiting_thread_id stands for waiting thread Blocking_thread_id stands for thread blocking SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread_id,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread_id,b.trx_query blocking_query Now ()-r.TRX_STARTED blocking_time FROMinformation_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_idINNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id -- check the source sqlSELECTa.sql_text,c.id,d.trx_started,b.processlist_user,b.processlist_host FROMPERFORMANCE_SCHEMA.events_statements_current aJOIN PERFORMANCE_SCHEMA.threads b ON a.thread_id = b.thread_idJOIN information_schema.PROCESSLIST c ON b.processlist_id = c.idJOIN information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id WHEREc.id = 73921 ORDER BYd.trx_started

3. View deadlock log

Because I added parameters to my configuration file:

# print deadlock log innodb _ print_all_deadlocks=1

So it directly depends on the mysqld.log situation.

You can find that the log indicates that the disk is full.

4. Check disk space

After checking, it was found that it was full. Afterwards, it was found that the backup strategy was backed up once a day and cleaned up for 14 days, but the growing data led to insufficient space. After cleaning, you can adjust the backup strategy.

5. Solution

(1) adjust backup strategy

Modified to complete every two days and clean up automatically for 14 days

(2) clean up the database

Clean up database useless tables and useless objects

(3) cleanup of database log tables

Set up the archive database, remove the relevant log table regularly, and drop the drop after 7 days.

(4) establish a monitoring and alarm system.

Zabbix coverage monitoring, nail warning.

After reading the above, do you have any further understanding of the deadlock failure caused by full disk space in the production database and its solution? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.