innodbの状態を確認する

SQL文

show engine innodb status\G

1	show engine innodb status\G

用途

重いsqlがどこまで行っているかを確認するなど。（出力例の「TRANSACTION」部分参照）

出力例

*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2014-05-27 06:35:07 2b9629f27700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 58 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 486050 srv_active, 0 srv_shutdown, 5904931 srv_idle srv_master_thread log flush and writes: 6390969 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 41621 OS WAIT ARRAY INFO: signal count 41624 Mutex spin waits 162, rounds 4825, OS waits 125 RW-shared spins 41739, rounds 1252770, OS waits 41446 RW-excl spins 5, rounds 1540, OS waits 49 Spin rounds per wait: 29.78 mutex, 30.01 RW-shared, 308.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2508259 Purge done for trx's n:o < 2508171 undo n:o < 0 state: running but idle History list length 1521 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 93532, OS thread handle 0x2b9629f27700, query id 16852600 10.1.10.10 Cherubim init show engine innodb status ---TRANSACTION 2508258, not started MySQL thread id 27857, OS thread handle 0x2b9629644700, query id 16852599 localhost 127.0.0.1 rdsadmin cleaning up -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 260 OS file reads, 447106 OS file writes, 381164 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.17 writes/s, 0.74 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 5530309, node heap has 11 buffer(s) 113.95 hash searches/s, 15.17 non-hash searches/s --- LOG --- Log sequence number 43098372 Log flushed up to 43098372 Pages flushed up to 43098372 Last checkpoint at 43098372 0 pending log writes, 0 pending chkp writes 119585 log i/o's done, 0.29 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2859991040; in additional pool allocated 0 Dictionary memory allocated 315915 Buffer pool size 170556 Free buffers 169584 Database pages 961 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 241, created 720, written 203532 0.00 reads/s, 0.00 creates/s, 0.67 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 961, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 21319 Free buffers 21139 Database pages 179 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 34, created 145, written 48215 0.00 reads/s, 0.00 creates/s, 0.10 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 179, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 21320 Free buffers 21277 Database pages 43 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 0, created 43, written 945 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 43, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 21319 Free buffers 21266 Database pages 52 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 2, created 50, written 409 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 52, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 21320 Free buffers 21211 Database pages 109 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 68, created 41, written 30912 0.00 reads/s, 0.00 creates/s, 0.07 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 109, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 21319 Free buffers 21208 Database pages 108 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 67, created 41, written 25538 0.00 reads/s, 0.00 creates/s, 0.10 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 108, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 21320 Free buffers 21143 Database pages 176 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 60, created 116, written 31743 0.00 reads/s, 0.00 creates/s, 0.09 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 176, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 21319 Free buffers 21165 Database pages 151 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 5, created 146, written 36841 0.00 reads/s, 0.00 creates/s, 0.16 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 21320 Free buffers 21175 Database pages 143 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 5, created 138, written 28929 0.00 reads/s, 0.00 creates/s, 0.16 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 143, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 15691, id 47923922183936, state: sleeping Number of rows inserted 14711, updated 53075, deleted 2927, read 34170465 0.00 inserts/s, 0.24 updates/s, 0.00 deletes/s, 156.03 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 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 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235

*************************** 1. row ***************************   Type: InnoDB   Name: Status: ===================================== 2014-05-27 06:35:07 2b9629f27700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 58 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 486050 srv_active, 0 srv_shutdown, 5904931 srv_idle srv_master_thread log flush and writes: 6390969 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 41621 OS WAIT ARRAY INFO: signal count 41624 Mutex spin waits 162, rounds 4825, OS waits 125 RW-shared spins 41739, rounds 1252770, OS waits 41446 RW-excl spins 5, rounds 1540, OS waits 49 Spin rounds per wait: 29.78 mutex, 30.01 RW-shared, 308.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2508259 Purge done for trx's n:o < 2508171 undo n:o < 0 state: running but idle History list length 1521 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 93532, OS thread handle 0x2b9629f27700, query id 16852600 10.1.10.10 Cherubim init show engine innodb status ---TRANSACTION 2508258, not started MySQL thread id 27857, OS thread handle 0x2b9629644700, query id 16852599 localhost 127.0.0.1 rdsadmin cleaning up -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 260 OS file reads, 447106 OS file writes, 381164 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.17 writes/s, 0.74 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 5530309, node heap has 11 buffer(s) 113.95 hash searches/s, 15.17 non-hash searches/s --- LOG --- Log sequence number 43098372 Log flushed up to   43098372 Pages flushed up to 43098372 Last checkpoint at  43098372 0 pending log writes, 0 pending chkp writes 119585 log i/o's done, 0.29 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2859991040; in additional pool allocated 0 Dictionary memory allocated 315915 Buffer pool size   170556 Free buffers       169584 Database pages     961 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 241, created 720, written 203532 0.00 reads/s, 0.00 creates/s, 0.67 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 961, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size   21319 Free buffers       21139 Database pages     179 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 34, created 145, written 48215 0.00 reads/s, 0.00 creates/s, 0.10 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 179, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size   21320 Free buffers       21277 Database pages     43 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 0, created 43, written 945 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 43, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size   21319 Free buffers       21266 Database pages     52 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 2, created 50, written 409 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 52, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size   21320 Free buffers       21211 Database pages     109 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 68, created 41, written 30912 0.00 reads/s, 0.00 creates/s, 0.07 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 109, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size   21319 Free buffers       21208 Database pages     108 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 67, created 41, written 25538 0.00 reads/s, 0.00 creates/s, 0.10 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 108, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size   21320 Free buffers       21143 Database pages     176 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 60, created 116, written 31743 0.00 reads/s, 0.00 creates/s, 0.09 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 176, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size   21319 Free buffers       21165 Database pages     151 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 5, created 146, written 36841 0.00 reads/s, 0.00 creates/s, 0.16 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 151, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size   21320 Free buffers       21175 Database pages     143 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 5, created 138, written 28929 0.00 reads/s, 0.00 creates/s, 0.16 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 143, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 15691, id 47923922183936, state: sleeping Number of rows inserted 14711, updated 53075, deleted 2927, read 34170465 0.00 inserts/s, 0.24 updates/s, 0.00 deletes/s, 156.03 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================   1 row in set (0.00 sec)

プロセスリストを確認

SQL文

show processlist;

1	show processlist;

出力例

mysql> show processlist; +-------+------------+------------------+------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------------+------------------+------------+---------+------+-------+------------------+ | 27857 | rdsadmin | localhost:52269 | NULL | Sleep | 11 | | NULL | | 93532 | Cherubim | 10.1.10.10:34972 | NULL | Query | 0 | init | show processlist | +-------+------------+------------------+------------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)

1 2 3 4 5 6 7 8

mysql> show processlist; +-------+------------+------------------+------------+---------+------+-------+------------------+ | Id    | User       | Host             | db         | Command | Time | State | Info             | +-------+------------+------------------+------------+---------+------+-------+------------------+ | 27857 | rdsadmin   | localhost:52269  | NULL       | Sleep   |   11 |       | NULL             | | 93532 | Cherubim   | 10.1.10.10:34972 | NULL       | Query   |    0 | init  | show processlist | +-------+------------+------------------+------------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)

プロセスリストを確認

SQL文

mysql> SET profiling = 1; mysql> SELECT ... mysql> SHOW PROFILE;

1 2 3

mysql> SET profiling = 1; mysql> SELECT ... mysql> SHOW PROFILE;

用途

実行にかかった時間を出力してくるから、どの部分が一番時間かかってるを確認するのに向いている。

出力例

mysql> SHOW PROFILE; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000108 | | checking permissions | 0.000031 | | Opening tables | 0.000048 | | init | 0.000065 | | System lock | 0.000033 | | optimizing | 0.000025 | | statistics | 0.000047 | | preparing | 0.000038 | | executing | 0.000021 | | Sending data | 0.000137 | | end | 0.000023 | | query end | 0.000027 | | closing tables | 0.000029 | | freeing items | 0.000093 | | cleaning up | 0.000024 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> SHOW PROFILE; +----------------------+----------+ | Status               | Duration | +----------------------+----------+ | starting             | 0.000108 | | checking permissions | 0.000031 | | Opening tables       | 0.000048 | | init                 | 0.000065 | | System lock          | 0.000033 | | optimizing           | 0.000025 | | statistics           | 0.000047 | | preparing            | 0.000038 | | executing            | 0.000021 | | Sending data         | 0.000137 | | end                  | 0.000023 | | query end            | 0.000027 | | closing tables       | 0.000029 | | freeing items        | 0.000093 | | cleaning up          | 0.000024 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)