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

This article introduces the relevant knowledge of "SHOW ENGINE INNODB STATUS example Analysis in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, let's take a look at the basics of SHOW ENGINE INNODB STATUS output, which prints a lot of counter, statistical, transaction information about InnoDB's internal performance. In MySQL 5, the performance statistics of InnoDB are also shown in the SHOW STATUS results. Most of the information is the same as the other information in SHOW ENGINE INNODB STATUS, which was not available in the old version.

Many of the statistics in SHOW ENGINE INNODB STATUS are updated every second, and if you plan to use them, it's best to count the results over a period of time. InnoDB first outputs the following information:

1. INNODB MONITOR OUTPUT3.==4.Per second averages calculated from the last 2.060717 3:07:56 44 seconds

First of all, make sure that this is the sample data that has been counted for at least 20-30 seconds. If the average statistical interval is 0 or 1 second, then the result is meaningless.

The average provided by InnoDB, it is difficult to obtain reasonable average interval statistics, if you are writing the script to obtain SHOW ENGINE INNODB STATUS results, then it is best to get the overall statistical results, and then get the average. Of course, it is also useful to directly view the output result information.

The next section shows information about the signal (Semaphores):

1.-2.SEMAPHORES3.-4.OS WAIT ARRAY INFO: reservation count 13569, signal count 114215.--Thread 1152170336 has waited at. /.. / include/buf0buf.ic line 630 for 0.00 seconds the semaphore:6.Mutex at 0x2a957858b8 created file buf0buf.c line 517 Lock var 07.waiters flag 08.wait is ending9.--Thread 1147709792 has waited at. /.. / include/buf0buf.ic line 630 for 0.00 seconds the semaphore:10.Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 011.waiters flag 012.wait is ending13.Mutex spin waits 5672442, rounds 3899888, OS waits 471914.RW-shared spins 5920, OS waits 2918 RW-excl spins 3463, OS waits 3163

This paragraph can be divided into two parts. One part is the current wait, and this part only contains all the records in a highly concurrent environment, so InnoDB will frequently fall back to the system to wait. If the wait is solved through a spin lock, then this information will not be displayed.

Through this part of the information, you will know where the hot spot of the system load is. However, you need to know something about the source code-you can see which lines in which source file from the above information (different versions may have different results), but you can't see any information from here. Still, you can guess something from the file name-for example, in this case, the file name "buf0buf.ic" indicates something to do with some buffer pool contention. If you want to know more, look at the source code.

There are some more details about waiting. "lock var" represents the value of the current mutex object (locked = 1 / release = 0), and "waiters flag" represents the current number of waits. In addition, the wait status message "wait is ending" can be seen in this example, which means that the mutex has been released, but the system scheduling thread is still processing.

The second block is event statistics-"reservation count" and "signal count" show how active innodb is using internal synchronization arrays-time slice (slot) allocation and how frequently thread signals use synchronization arrays. These statistics can be used to indicate how often the innodb falls back to the system waiting. There is also directly related information about the system waiting, you can see the mutex semaphore (mutexes) of "OS Waits", as well as read-write locks. Mutex and shared locks are shown in this information. System waits are not exactly the same as "reservation". Before falling back to the complex wait mode with sync_array, innodb tries to "yield" to the system, hoping that the named thread has been released in the next scheduled time object. System waits are relatively slow, and there may be problems if tens of thousands of system waits occur per second. Another way to observe is to look at the frequency of context (context) exchanges in the system state.

Another important piece of information is the number of "spin waits" and "spin rounds". A spin lock is a low-cost wait compared to a system wait; however, it is an active wait that wastes some cpu resources. So if you see a lot of spin waiting and spin rotation, it's obviously wasting a lot of cpu resources. Innodb_sync_spin_loops can be used to strike a balance between wasting cpu time and unnecessary context switching.

The next paragraph shows the deadlock status:

1.----2.LATEST DETECTED DEADLOCK3.----4.060717 4 ACTIVE 16 sec 485. Lock struct * (1) TRANSACTION:6.TRANSACTION 0 42313619, ACTIVE 49 sec, process no 10099, OS thread id 3771312 starting index read7.mysql tables in use 1, locked 18.LOCK WAIT 3 lock struct (s), heap size 3209.MySQL thread id 30898 Query id 100626 localhost root Updating10.update iz set pad='a' where i211. Trx id * (1) WAITING FOR THIS LOCK TO BE GRANTED:12.RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/ iz` trx id 0 42313619 lock_mode X locks rec but not gap waiting13.Record lock, heap no 5 PHYSICAL RECORD: n_fields 4 Compact format; info bits 014. 0: len 4; hex 80000002; asc; 1: len 6; hex 00000285a78f; asc; 2: len 7; hex 00000040150110; asc @; 3: len 10; hex 6120202020202020202020; asc a 15. 16 TRANSACTION:17.TRANSACTION * (2) TRANSACTION:17.TRANSACTION 0 42313620, ACTIVE 24 sec, process no 10099, OS thread id 4078512 starting index read, thread declared inside InnoDB 50018.mysql tables in use 1, locked 119.3 lock struct (s), heap size 32020.MySQL thread id 30899 Query id 100627 localhost root Updating21.update iz set pad='a' where iSuppli 122.test/ * (2) HOLDS THE LOCK (S): 23.RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/ iz` trx id 0 42313620 lock_mode X locks rec but not gap24.Record lock, heap no 5 PHYSICAL RECORD: n_fields 4 Compact format; info bits 025. 0: len 4; hex 80000002; asc; 1: len 6; hex 00000285a78f; asc; 2: len 7; hex 00000040150110; asc @; 3: len 10; hex 6120202020202020202020; 26. 27. Benchmark * (2) WAITING FOR THIS LOCK TO BE GRANTED:28.RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/ iz` trx id 0 42313620 lock_mode X locks rec but not gap waiting29.Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 030. 0: len 4; hex 80000001; asc; 1: len 6; hex 00000285a78e; asc; 2: len 7; hex 0000003411d9; asc 4; 3: len 10; hex 6120202020202020202020; 31. 32. Thank you * WE ROLL BACK TRANSACTION (2)

This shows that Innodb last detected the deadlock raised by the transaction, including the state when the deadlock occurred, what lock was added, what lock was waiting for release, and Innodb decided which transaction would be rolled back. Note that innodb shows only simple information about the transaction holding the lock. And only the last statement executed by each transaction is displayed, and the record of deadlock is caused by these statements. To view complex deadlock information, you also need to look at the log file to find the statement that really caused the conflict. In most cases, the information displayed by SHOW INNODB STATUS is basically sufficient.

The following is information about deadlocks caused by foreign key constraints:

1.----2.LATEST FOREIGN KEY ERROR3.----4.060717 4:29:00 Transaction:5.TRANSACTION 0 336342767, ACTIVE 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 5006.mysql tables in use 1, locked 17.3 lock struct (s), heap size 368, undo log entries 18.MySQL thread id 9697561 Query id 188161264 localhost root update9.insert into child values (2 in index 2) 10.Foreign key constraint fails for table `test/ child`: 11.13. CONSTRAINT `child_ibfk_ 1` FOREIGN KEY (`parent_ id`) REFERENCES `parent` (`id`) ON DELETE CASCADE13.Trying to add in child table, in index `par_ ind` tuple:14.DATA TUPLE: 2 fields 15. 0: len 4; hex 80000002; asc;; 1: len 6; hex 0000000401; asc; 16. 17.But in parent table `test/ parent`, in index `PRIMARY`, 18.the closest match we can find is record:19.PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 020. 0: len 4; hex 80000001; asc;; 1: len 6; hex 0000140c2d8f; asc -; 2: len 7; hex 80009c40050084; asc @

Innodb displays the statement that caused the error. Failed to define foreign key constraints and define the most closely related parent table. A lot of embedded information is expressed in hexadecimal, but it is not too important for problem diagnosis, and it is mainly used for Innodb developers to view or for debugging purposes.

The next step is to show the currently active transactions in Innodb:

1.-2.TRANSACTIONS3.-4.Trx id counter 0 801576015.Purge done for trx's n:o

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.