January 24, 2018

SHOW ENGINE INNODB STATUS walk through

SHOW ENGINE INNODB STATUS is a very direct and simple SHOW ENGINE statement to display InnoDB Monitor output. The output of this query is quite detailed and exhaustive, It has wealth of information to troubleshoot most common MySQL performance / operations issues real-time. Often MySQL DBAs ask me how to interpret this data so thought I will write a post on this. Please run SHOW ENGINE INNODB STATUS if you are building a pattern of InnoDB operations over a period of time.

Please confirm data is sampled not for 0 or 1 seconds, Anything above 10 seconds helps

  • Status : This shows timestamp, monitor name and the number of seconds, or the elapsed time between the current time and time the InnoDB monitor last displayed. The per-second averages are based upon this time .
  • BACKGROUND THREAD : The srv_master_thread lines in BACKGROUND THREAD shows work performed by the main background thread .
  • SEMAPHORES :  The threads waiting for a semaphore and stats show on how many number of times threads have needed a spin or wait on a mutex or rw-lock semaphore, If this number of threads is large then may be I/O or contention issues . You can reduce innodb_thread_concurrency system variable (this works often not always) to solve contention related to thread scheduling. Spin rounds per wait shows the total number of spinlock rounds per OS wait for a mutex. LATEST FOREIGN KEY ERROR is shown only if there has been a foreign key constraint error. LATEST DETECTED DEADLOCK will be shown only if there has been a deadlock, It displays the transaction involved in the deadlock and statements being executed, held and required locked and the transaction rolled back to.
  • TRANSACTIONS : The output of this section can help to identify all the lock contention and the reasons for deadlocks .
  • FILE I/O : InnoDB thread information as well as pending I/O operations and I/O performance statistics .
  • LOG : InnoDB log information, including current log sequence number, how far the log has been flushed to disk, the position at which InnoDB last took a checkpoint, pending writes and write performance statistics .
  • BUFFER POOL AND MEMORY : These matrices report the information on buffer pool pages read and written, Which allows you to see the number of data file I/O operations performed by your queries .
  • ROW OPERATIONS : Information about the main thread, including the number and performance rate for each type of its own operation .

Example output:

mysql> show engine innodb status\G;

*************************** 1. row ***************************

  Type: InnoDB

  Name: 

Status: 

=====================================

180105 22:39:35 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 17 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 11 1_second, 10 sleeps, 0 10_second, 3 background, 3 flush

srv_master_thread log flush and writes: 10

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 4457, signal count 1072

Mutex spin waits 4670, rounds 140100, OS waits 1494

RW-shared spins 2090, rounds 62850, OS waits 1169

RW-excl spins 1735, rounds 53820, OS waits 948

Spin rounds per wait: 30.00 mutex, 30.07 RW-shared, 31.02 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 8D121

Purge done for trx's n:o < 862C4 undo n:o < 0

History list length 667

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 3, OS thread handle 0x7f965a163700, query id 25591 localhost root

show engine innodb status

---TRANSACTION 8D102, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1855, OS thread handle 0x7f9656b61700, query id 25560 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D10B, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1848, OS thread handle 0x7f9656eae700, query id 25569 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D116, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1806, OS thread handle 0x7f96570f7700, query id 25580 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D108, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1816, OS thread handle 0x7f9659fdd700, query id 25566 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D110, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1807, OS thread handle 0x7f9656ff3700, query id 25574 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D111, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1833, OS thread handle 0x7f9656c65700, query id 25575 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0EB, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1815, OS thread handle 0x7f9656e6d700, query id 25537 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D104, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1852, OS thread handle 0x7f9656f30700, query id 25562 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0FB, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1808, OS thread handle 0x7f9656ce7700, query id 25553 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D11E, not started

MySQL thread id 1846, OS thread handle 0x7f9656a5d700, query id 25588 localhost root

---TRANSACTION 8D0E7, not started

MySQL thread id 1825, OS thread handle 0x7f9656fb2700, query id 25533 localhost root

---TRANSACTION 8D101, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1823, OS thread handle 0x7f9656a9e700, query id 25559 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D106, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1824, OS thread handle 0x7f9657444700, query id 25564 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0F6, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1812, OS thread handle 0x7f9657403700, query id 25548 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0F3, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1810, OS thread handle 0x7f96569db700, query id 25545 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0F1, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1842, OS thread handle 0x7f9657340700, query id 25543 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0F0, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1851, OS thread handle 0x7f96571ba700, query id 25542 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D10F, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1811, OS thread handle 0x7f9656ca6700, query id 25573 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D107, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1818, OS thread handle 0x7f9656d69700, query id 25565 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0F7, not started

MySQL thread id 1817, OS thread handle 0x7f965723c700, query id 25549 localhost root

---TRANSACTION 8D0FC, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1834, OS thread handle 0x7f9656a1c700, query id 25554 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0FA, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1841, OS thread handle 0x7f96573c2700, query id 25552 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0EA, not started

MySQL thread id 1850, OS thread handle 0x7f96572be700, query id 25536 localhost root

---TRANSACTION 8D109, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1822, OS thread handle 0x7f9657075700, query id 25567 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D105, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1827, OS thread handle 0x7f965a01e700, query id 25563 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D11D, not started

MySQL thread id 1809, OS thread handle 0x7f9657179700, query id 25587 localhost root

---TRANSACTION 8D100, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1839, OS thread handle 0x7f9656ba2700, query id 25558 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D10C, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1837, OS thread handle 0x7f96571fb700, query id 25570 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0F4, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1819, OS thread handle 0x7f9657485700, query id 25546 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0FE, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1835, OS thread handle 0x7f9657507700, query id 25556 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0E4, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1826, OS thread handle 0x7f9656f71700, query id 25530 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D120, not started

MySQL thread id 1843, OS thread handle 0x7f965a05f700, query id 25590 localhost root

---TRANSACTION 8D0E6, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1820, OS thread handle 0x7f9656adf700, query id 25532 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D11F, not started

MySQL thread id 1836, OS thread handle 0x7f9656daa700, query id 25589 localhost root

---TRANSACTION 8D0FD, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1844, OS thread handle 0x7f9656d28700, query id 25555 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0FF, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1828, OS thread handle 0x7f965727d700, query id 25557 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D10A, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1831, OS thread handle 0x7f96572ff700, query id 25568 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D11A, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1853, OS thread handle 0x7f9657381700, query id 25584 localhost root query end

INSERT INTO t1 VALUES (1759592334,'3lkoxjtvgLu5xKHSTTtJuGE5F5QqmCcppCTmvFZScRZQgim93gSxwb24gKmIPEzEQStMjQiCu7WapGbkw4ilXch3xRLMhKSzgLDOovSi2qGj6rKvnuYAWDDJgaZDu2')

---TRANSACTION 8D0E9, not started

MySQL thread id 1854, OS thread handle 0x7f9657138700, query id 25535 localhost root

---TRANSACTION 8D0F5, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1813, OS thread handle 0x7f9656b20700, query id 25547 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D0ED, not started

MySQL thread id 1829, OS thread handle 0x7f96574c6700, query id 25539 localhost root

---TRANSACTION 8D10D, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1821, OS thread handle 0x7f96570b6700, query id 25571 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D103, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1832, OS thread handle 0x7f9656eef700, query id 25561 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D11B, not started

MySQL thread id 1830, OS thread handle 0x7f9656deb700, query id 25585 localhost root

---TRANSACTION 8D0F9, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1849, OS thread handle 0x7f965a0a0700, query id 25551 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D11C, not started

MySQL thread id 1838, OS thread handle 0x7f965a0e1700, query id 25586 localhost root

---TRANSACTION 8D0EC, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1847, OS thread handle 0x7f9657034700, query id 25538 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D10E, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1814, OS thread handle 0x7f9656be3700, query id 25572 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D115, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1845, OS thread handle 0x7f9656c24700, query id 25579 localhost root query end

INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm')

---TRANSACTION 8D119, not started flushing log

mysql tables in use 1, locked 1

MySQL thread id 1840, OS thread handle 0x7f9656e2c700, query id 25583 localhost root query end

INSERT INTO t1 VALUES (1759592334,'3lkoxjtvgLu5xKHSTTtJuGE5F5QqmCcppCTmvFZScRZQgim93gSxwb24gKmIPEzEQStMjQiCu7WapGbkw4ilXch3xRLMhKSzgLDOovSi2qGj6rKvnuYAWDDJgaZDu2')

---TRANSACTION 8D080, not started

MySQL thread id 5, OS thread handle 0x7f965a122700, query id 25430 localhost root

--------

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

1491 OS file reads, 4495 OS file writes, 4410 OS fsyncs

0.06 reads/s, 16384 avg bytes/read, 263.98 writes/s, 258.98 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 276671, node heap has 1 buffer(s)

280.57 hash searches/s, 652.61 non-hash searches/s

---

LOG

---

Log sequence number 561353275

Log flushed up to   561352811

Last checkpoint at  556826545

0 pending log writes, 0 pending chkp writes

4396 log i/o's done, 257.98 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 137363456; in additional pool allocated 0

Dictionary memory allocated 75535

Buffer pool size   8191

Free buffers       6699

Database pages     1491

Old database pages 566

Modified db pages  256

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 1480, created 11, written 94

0.06 reads/s, 0.65 creates/s, 5.47 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: 1491, 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

1 read views open inside InnoDB

Main thread process no. 1430, id 140283695105792, state: sleeping

Number of rows inserted 14565, updated 0, deleted 0, read 2595539

856.71 inserts/s, 0.00 updates/s, 0.00 deletes/s, 135022.35 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.00 sec)

ERROR: 

No query specified
About Shiv Iyer 12 Articles
Independent and vendor neutral MySQL, MariaDB and Percona Server consultant with core expertise in performance, scalability and high availability. Shiv is a frequent speaker in Open Source Software conferences worldwide. Shiv currently is the founder and principal of MinervaSQL, An boutique private-label MySQL, MariaDB and Percona Server consulting and remote DBA services company with several customers worldwide. Shiv in the past worked for companies like MySQL AB, SUN, Oracle, AOL, Nike, eBay, PayPal, PalominoDB (acquired by Pythian) and Percona in Sr. MySQL consulting and professional services roles. To engage MinervaSQL for MySQL consulting and professional services, please send email to shiv@minervasql.com

Be the first to comment

Leave a Reply

Your email address will not be published.


*