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

Today, I will talk to you about how to perform mysql performance test library CRASH recovery, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Environment:

OS: Linux als_qa_rhel5.3 2.6.18-128.el5 # 1 SMP Wed Dec 17 11:41:38 EST 2008 x86 "64 GNU/Linux

MYSQL:MySQL-server-enterprise-gpl-5.0.68-0.rhel4.x86_64 [@ more@]

A MYSQL performance test database, in the process of high pressure testing, suddenly CRASH

The following is the LOG log when MYSQL is restarted:

090716 09:31:02 mysqld started

InnoDB: Log scan progressed past the checkpoint lsn 32 2512592438

090716 9:31:03 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

InnoDB: Doing recovery: scanned up to log sequence number 32 2517835264

InnoDB: Doing recovery: scanned up to log sequence number 32 2523078144

InnoDB: Doing recovery: scanned up to log sequence number 32 2528321024

InnoDB: Doing recovery: scanned up to log sequence number 32 2533563904

InnoDB: Doing recovery: scanned up to log sequence number 32 2538806784

InnoDB: Doing recovery: scanned up to log sequence number 32 2544049664

InnoDB: Doing recovery: scanned up to log sequence number 32 2549292544

InnoDB: Doing recovery: scanned up to log sequence number 32 2554535424

InnoDB: Doing recovery: scanned up to log sequence number 32 2559778304

InnoDB: Doing recovery: scanned up to log sequence number 32 2565021184

InnoDB: Doing recovery: scanned up to log sequence number 32 2570264064

InnoDB: Doing recovery: scanned up to log sequence number 32 2575506944

InnoDB: Doing recovery: scanned up to log sequence number 32 2580749824

InnoDB: Doing recovery: scanned up to log sequence number 32 2585992704

InnoDB: Doing recovery: scanned up to log sequence number 32 2591235584

InnoDB: Doing recovery: scanned up to log sequence number 32 2596478464

InnoDB: Doing recovery: scanned up to log sequence number 32 2601721344

InnoDB: Doing recovery: scanned up to log sequence number 32 2606964224

InnoDB: Doing recovery: scanned up to log sequence number 32 2612207104

InnoDB: Doing recovery: scanned up to log sequence number 32 2617449984

InnoDB: Doing recovery: scanned up to log sequence number 32 2622692864

InnoDB: Doing recovery: scanned up to log sequence number 32 2627935744

InnoDB: Doing recovery: scanned up to log sequence number 32 2633088581

090716 9:31:05 InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

InnoDB: Apply batch completed

InnoDB: Last MySQL binlog file position 0 22007701, file name. / mysql-bin.000149

090716 9:31:49 InnoDB: Started; log sequence number 32 2633088581

090716 9:31:49 [Note] Recovering after a crash using mysql-bin

090716 9:31:49 [Note] Starting crash recovery...

090716 9:31:49 [Note] Crash recovery finished.

090716 9:31:49 [Note] / usr/sbin/mysqld: ready for connections.

Version: '5.0.68 socket:' / tmp/mysql.sock' port: 3306 MySQL Enterprise Server (GPL)

090716 9:31:59InnoDB: Assertion failure in thread 1177917760 in file btr0cur.c line 3606

InnoDB: Failing assertion: extern_len-part_len = = 0

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

InnoDB: corruption in the InnoDB tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html

InnoDB: about forcing recovery.

090716 9:31:59-mysqld got signal 11

This could be because you hit a bug. It is also possible that this binary

Or one of the libraries it was linked against is corrupt, improperly built

Or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose

The problem, but since we have already crashed, something is definitely wrong

And this may fail.

Key_buffer_size=335544320

Read_buffer_size=1048576

Max_used_connections=2

Max_connections=1000

Threads_connected=2

It is possible that mysqld could use up to

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = 2375680 K

Bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thd= (nil)

Attempting backtrace. You can use the following information to find out

Where mysqld died. If you see no messages after this, something went

Terribly wrong...

Frame pointer is NULL, did you compile with

-fomit-frame-pointer? Aborting backtrace!

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

Information that should help you find out what is causing the crash.

Number of processes running now: 0

090716 09:31:59 mysqld restarted

In the log, we see that after the success of INNODB recovery, it shows

090716 9:31:49 [Note] / usr/sbin/mysqld: ready for connections.

This was normal, but then it was restarted.

At this point, the client cannot connect.

But look at the MYSQL process, but it is still there.

The library cannot be stopped with SERVICE MYSQL STOP. It can only be killed with KILL-9.

At present, we can only guess that we have encountered BUG. (this version runs normally in other libraries.)

And such BUG cannot be reproduced. It is possible that it will only occur under high pressure.

In such a situation, the most important thing for us is to back up the data.

So open the library as much as possible and back up the data.

A lucky thing. This time the database INNODB tablespace is not corrupted.

Added to the MY.CNF configuration file:

[mysqld]

Innodb_force_recovery = 2

The database was opened smoothly. At this time, quickly use MYSQLDUMP to do a full library export.

Then delete all the libraries and reinstall MYSQL.

Finally, import all the libraries.

Although the library is restored. But I want to find out why. Why can it be opened when innodb_force_recovery = 2?

Get from http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html:

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Do not run transaction rollbacks after recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)

Do not do the log roll-forward in connection with recovery.

The second is that if purge operation causes CRASH, then this setting will take effect.

What kind of operation is the purge operation here? Searching.

After reading the above, do you have any further understanding of how to perform CRASH recovery of the mysql performance test library? 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.