Làm cách nào để kiểm tra trạng thái InnoDB trong MySQL?
|
mysql 5. 5 Hướng dẫn về Trạng thái InnoDB từ Karwin Software Solutions LLC Show InnoDb là một công cụ lưu trữ cho bảng MySQL, nó hỗ trợ khóa giao dịch và cấp hàng. Có các lệnh trong MySQL chỉ hoạt động cho công cụ InnoDB, chẳng hạn như cập nhật và bắt đầu giao dịch. Khi tôi muốn kiểm tra một số lệnh liên quan đến giao dịch trong MySQL, chúng dường như không có tác dụng Nếu bạn gặp vấn đề tương tự, một trong những lý do là bạn có thể đã tắt công cụ InnoDB Có hai phương pháp để kiểm tra xem công cụ InnoDB có bị tắt hay không, cách thứ nhất là lệnh hiển thị công cụ như sau mysql> show engines; +------------+----------+----------------------------------------------------------------+ | Engine | Support | Comment | +------------+----------+----------------------------------------------------------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | //| InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE | NO | Example storage engine | | ARCHIVE | YES | Archive storage engine | | CSV | NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | DISABLED | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | +------------+----------+----------------------------------------------------------------+ 12 rows in set (0.00 sec) Phương pháp thứ hai là hiển thị trạng thái innodb của công cụ mysql> show engine innodb status; ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined Nhưng bạn sẽ không tìm thấy nó khi cố gắng tạo bảng InnoDB khi nó bị vô hiệu hóa, thậm chí bạn sẽ không nhận được cảnh báo. Những gì MySQL sẽ làm là sử dụng công cụ mặc định, trong hầu hết các trường hợp, đó là MyISAM. Trong một số phiên bản phân tán, InnoDB bị tắt theo mặc định, vì vậy bạn nên kiểm tra nó trước khi thực hiện bất kỳ thao tác nào liên quan đến giao dịch, nếu không bạn sẽ bị nhầm lẫn SHOW ENGINE INNODB STATUS là một câu lệnh SHOW ENGINE rất trực tiếp và đơn giản để hiển thị đầu ra Màn hình InnoDB. Đầu ra của truy vấn này khá chi tiết và đầy đủ, Nó có nhiều thông tin để khắc phục hầu hết các vấn đề về hoạt động/hiệu suất phổ biến của MySQL trong thời gian thực. Các DBA MySQL thường hỏi tôi cách diễn giải dữ liệu này nên nghĩ rằng tôi sẽ viết một bài về vấn đề này. Vui lòng chạy SHOW ENGINE INNODB STATUS nếu bạn đang xây dựng một mẫu hoạt động của InnoDB trong một khoảng thời gian Vui lòng xác nhận rằng dữ liệu được lấy mẫu không phải trong 0 hoặc 1 giây, Bất cứ điều gì trên 10 giây đều hữu ích
đầu ra ví dụ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 InnoDB kiểm tra trạng thái động cơ như thế nào?SHOW ENGINE INNODB STATUS là một dạng cụ thể của câu lệnh SHOW ENGINE hiển thị đầu ra của Màn hình InnoDB , đây là thông tin mở rộng của InnoDB có thể .
InnoDB trong MySQL là gì?InnoDB là công cụ lưu trữ đa năng cân bằng giữa độ tin cậy cao và hiệu suất cao . Trong MySQL5. 6, InnoDB là công cụ lưu trữ MySQL mặc định. Trừ khi bạn đã định cấu hình một công cụ lưu trữ mặc định khác, việc đưa ra câu lệnh CREATE TABLE không có mệnh đề ENGINE sẽ tạo một bảng InnoDB.
Làm cách nào để phát hiện bế tắc của MySQL?Để xem bế tắc cuối cùng trong giao dịch người dùng InnoDB, sử dụng SHOW ENGINE INNODB STATUS . Nếu các bế tắc thường xuyên làm nổi bật sự cố với cấu trúc giao dịch hoặc xử lý lỗi ứng dụng, hãy bật innodb_print_all_deadlocks để in thông tin về tất cả các bế tắc vào nhật ký lỗi mysqld.
Bế tắc trong MySQL là gì?Bế tắc là tình huống khi hai hoặc nhiều giao dịch cùng nắm giữ và yêu cầu khóa mà giao dịch kia cần. Do đó, một chu kỳ phụ thuộc được tạo ra và các giao dịch không thể tiếp tục. Theo mặc định, InnoDB tự động phát hiện các bế tắc và khôi phục một giao dịch (nạn nhân) để phá vỡ chu kỳ |
