國內最大的酷站演示中心!
innodb 中文參考手冊 --- 犬犬(心帆)翻譯 9 性能調整技巧(performance tuning tips)
1. 如果 unix top 或 windows 任務管理器(task manager) 顯示服務的 cpu 占用率小于 70%，(shows that the cpu usage percentage with your workload is less than 70 %,）你的系統(tǒng)瓶頸可能在磁盤讀寫上。或許你提交了大量的事務，或者是緩沖池(buffer pool)太小了。將緩沖池設大點會有所幫助，但一定要注意不能大于物理內存的 80%。
2. 在一個事務中包含幾個修改。如果事務對數(shù)據(jù)庫進行了修改，那么在這個事務提交時 innodb 必須刷新日志到磁盤上。因為硬盤的旋轉速度通常至多為 167 轉/秒，那么只要磁盤不欺騙操作系統(tǒng)，提交的事務數(shù)目限止也同樣為 167 次/秒·用戶。
3. 如果掉失最近的幾個事務無所謂的話，可以在 my.cnf 文件中將參數(shù) innodb_flush_log_at_trx_commit 設置為 0。innodb 無論如何總是嘗試一秒刷新(flush)一次日志，盡管刷新并不能得到保證。
4. 將日志文件(log files)設大一點，使日志文件的總和正好與緩沖池(buffer pool)一樣大。當 innodb 用光日志文件的空間時，它不得不在一個時間點上將緩沖池內修改過的內容寫到磁盤上。 小的日志文件可能引起不必要的磁盤寫操作。但是大的日志文件的缺點就是在數(shù)據(jù)恢復時將占用較長的時間。
5. 同樣 log buffer 盡量設大點，比如說 8 mb。
6. 如果要存儲變長的字符串或字段可能會包含大量的 nulls，請使用 varchar 型字段代替 char 。一個 char(n) 字段總是使用 n bytes 來存儲數(shù)據(jù)，即使這個字符串很短或是一個 null 值。較小的表更加適合緩沖池同時能夠減少磁盤 i/o 。
7. (適合從 3.23.41 以上版本) 在某些版本的 linux 和 unixes 中，使用 unix fsync 或其它類似的方法將文件刷新到磁盤是異常地慢的。innodb 默認的方法就是 fsync 。如果你對數(shù)據(jù)庫系統(tǒng)的磁盤寫性能不能感到滿意，你可以嘗試在 my.cnf 中將 innodb_flush_method 設置為 o_dsync，盡管 o_dsync 選項在多數(shù)的系統(tǒng)上看起來比較慢。
8. 在向 innodb 導入數(shù)據(jù)時，請確認 mysql 沒有打開 autocommit=1 。否則每個插入語句都要將 log 刷新到磁盤。在你的 sql 導入文件的第一行加入
set autocommit=0;
并在最后一行加入
commit;
如果使用 mysqldump 選項 --opt，你將會得到一個快速導入 innodb 表的轉儲(dump)文件，甚至可以不再使用上面所提的 set autocommit=0; ... commit; 。
9. 小心 insert 集全的大回滾(roolback)：在插入時 innodb 使用插入緩沖來減少磁盤 i/o，但在相應的回滾中卻沒有使用這樣的機制。一個 disk-bound rollback 可能會花費相應插入時間的 30 倍。如果發(fā)生一個失控的回滾，你可以查看第 6.1 章節(jié)的技巧來停止它。
10. 同樣也要小心一個大的 disk-bound 的操作。使用 drop table 或 truncate (從 mysql-4.0 以上) 來清空一個表，而不要使用 delete from yourtable。
11. 如果需要插入大量記錄行可以使用多行(multi-line)的 insert 來減少客戶端與服務器端的通信開銷：
insert into yourtable values (1, 2), (5, 5);
這個技巧對插入任何表均有效，而不僅僅是 innodb。
12. 如果在輔鍵上有 unique 約束，從 3.23.52 和 4.0.3 開始，可以通過在一個導入會話中將唯一鍵檢查(uniqueness check)關閉來提高數(shù)據(jù)導入速度：
set unique_checks=0;
一個大的表導入這將減少大量的磁盤 i/o，因為這時 innodb 可能使用自身的插入緩沖來分批地記錄輔助索引。
13. 如果在表中有一個子 foreign key 約束，從 3.23.52 和 4.0.3 開始，可以通過在一個導入會話中將外鍵檢查(foreign key check)關閉來提高數(shù)據(jù)導入速度：
set foreign_key_checks=0;
對一個大的表導入這將減少大量的磁盤 i/o。
9.1 innodb 監(jiān)視器(monitors)
從版本 3.23.42 開始，innodb 中就包含了 innodb monitors，它可以顯示出 innodb 的內部狀態(tài)。從版本 3.23.52 和 4.0.3 開始，你可以使用一個新的 sql 命令
show innodb status
來讀取標準 innodb monitor 給 sql client 的輸出信息。這些信息對性能調整有益。
另外一個使用 innodb monitors 方法就是讓它在服務程序 mysqld 的標準輸出上持續(xù)地寫出信息。當開關打開時，innodb monitors 大約每 15 秒顯示一次數(shù)據(jù)(注意：mysql 的客戶端并不會顯示任何東西)。一個簡單地使用它的方法就是以一個命令行方式執(zhí)行 mysqld 。否則輸出將會定向到 mysql 服務錯誤日志(error log file)中 'yourhostname'.err (在 windows 下為 mysql.err)，在 windows 系統(tǒng)中必須在 ms-dos 使用提示符下以 --console 選項運行 mysqld-max 來指令信息輸出在命令提示符窗口上。
顯示的信息包含下列信息： 每一個活動的事務(active transaction)保持的表和記錄鎖定 事務的鎖等待 (lock waits of a transactions) 線程的信號量等待 (semaphore waits of threads) 文件 i/o 的等待請求 (pending file i/o requests) 緩沖池(buffer pool)的統(tǒng)計信息 innodb 主線程的 purge buffer 和 insert buffer 歸并活動(merge activity)
通過下列的 sql 命令，可以使標準的 innodb monitor 記錄到標準的 mysqld 的輸出上：
create table innodb_monitor(a int) type = innodb;
通過它來停止：
drop table innodb_monitor;
create table 句法只不過是為了通過 mysql sql 語法分析而提供給 innodb 引擎命令的一種方式：那個被創(chuàng)建的表根本與 innodb monitor 無任何關系。如果你在監(jiān)視器運行著的狀態(tài)下關閉數(shù)據(jù)庫，并且你需要再次啟動監(jiān)視器， 那么你不得不在發(fā)出一個新的 create table 來啟動監(jiān)視器之前先移除(drop)這個表。
與之相類似的，你可以啟動 innodb_lock_monitor ，它在某些方面與 innodb_monitor 一致，但是它會顯示更多的鎖定信息。一個單獨的 innodb_tablespace_monitor 將顯示在現(xiàn)有表空間內所建立的文件段列表以及可以分配數(shù)據(jù)結構的有效表空間。從 3.23.44 開始，提供了 innodb_table_monitor ，通過它可以獲得 innodb 內部數(shù)據(jù)字典的信息。
3.23.52 中 innodb 輸出的示例：
===================================== 020805 22:07:41 innodb monitor output ===================================== per second averages calculated from the last 3 seconds ---------- semaphores ---------- os wait array info: reservation count 194, signal count 193 --thread 7176 has waited at ../include/btr0btr.ic line 28 for 0.00 seconds the s emaphore: x-lock on rw-latch at 44d980bc created in file buf0buf.c line 354 a writer (thread id 7176) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 last time read locked in file ../include/btr0btr.ic line 28 last time write locked in file ../include/btr0btr.ic line 28 mutex spin waits 0, rounds 0, os waits 0 rw-shared spins 77, os waits 33; rw-excl spins 188, os waits 161 ------------ transactions ------------ trx id counter 0 657853517 purge done for trx's n:o < 0 657853429 undo n:o < 0 80 total number of lock structs in row lock hash table 22 020805 22:07:36 latest detected deadlock: *** (1) transaction: transaction 0 657853503, active 0 sec, os thread id 15373 inserting lock wait 3 lock struct(s), heap size 336 mysql thread id 6, query id 3741 localhost heikki update insert into ibtest11b (d, b, c) values (5, 'khdkkkk' ,'khdkkkk') *** (1) waiting for this lock to be granted: record locks space id 0 page no 104865 n bits 208 table test/ibtest11b index pri mary trx id 0 657853503 lock_mode x waiting record lock, heap no 1 record: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) transaction: transaction 0 657853500, active 0 sec, os thread id 11275 setting auto-inc lock 19 lock struct(s), heap size 2672, undo log entries 5 mysql thread id 2, query id 3750 localhost heikki update insert into ibtest11b (d, b, c) values (5, 'khd' ,'khd') *** (2) holds the lock(s): record locks space id 0 page no 104865 n bits 200 table test/ibtest11b index pri mary trx id 0 657853500 lock_mode x record lock, heap no 1 record: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) waiting for this lock to be granted: table lock table test/ibtest11b trx id 0 657853500 lock_mode auto-inc waiting *** we roll back transaction (2) list of transactions for each session: ---transaction 0 657853516, active 5 sec, os thread id 15373 setting auto-inc lo ck lock wait 1 lock struct(s), heap size 336 mysql thread id 6, query id 3895 localhost heikki update insert into ibtest11b (d, b, c) values (5, 'khdkkkk' ,'khdkkkk') ------- trx has been waiting 5 sec for this lock to be granted: table lock table test/ibtest11b trx id 0 657853516 lock_mode auto-inc waiting ------------------ ---transaction 0 657853514, active 5 sec, os thread id 11275 inserting lock wait 13 lock struct(s), heap size 2672, undo log entries 2 mysql thread id 2, query id 3898 localhost heikki update insert into ibtest11d (d, b, c) values (5, 'khdkkkk' ,'khdkkkk') ------- trx has been waiting 5 sec for this lock to be granted: record locks space id 0 page no 104879 n bits 384 table test/ibtest11d index b t rx id 0 657853514 lock_mode x gap type lock waiting record lock, heap no 130 record: info bits 32 0: len 9; hex 6b48646b6b6b6b6b6b; asc khdkkkkkk;; 1: ------------------ ---transaction 0 657853512, active 5 sec, os thread id 14348 updating or deletin g 20 lock struct(s), heap size 2672, undo log entries 175 mysql thread id 5, query id 3874 localhost heikki updating delete from ibtest11a where a = 215 -------- file i/o -------- i/o thread 0 state: waiting for i/o request i/o thread 1 state: waiting for i/o request i/o thread 2 state: waiting for i/o request i/o thread 3 state: waiting for i/o request pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 pending flushes (fsync) log: 0; buffer pool: 0 272 os file reads, 56 os file writes, 29 os fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- insert buffer and adaptive hash index ------------------------------------- ibuf for space 0: size 1, free list len 5, seg size 7, 0 inserts, 0 merged recs, 0 merges hash table size 124633, used cells 1530, node heap has 4 buffer(s) 2895.70 hash searches/s, 126.62 non-hash searches/s --- log --- log sequence number 19 3267291494 log flushed up to 19 3267283711 last checkpoint at 19 3266545677 0 pending log writes, 0 pending chkp writes 30 log i/o's done, 0.00 log i/o's/second ---------------------- buffer pool and memory ---------------------- total memory allocated 82593970; in additional pool allocated 1406336 buffer pool size 1920 free buffers 1711 database pages 205 modified db pages 39 pending reads 0 pending writes: lru 0, flush list 0, single page 0 pages read 178, created 27, written 50 0.00 reads/s, 0.00 creates/s, 0.00 writes/s buffer pool hit rate 1000 / 1000 -------------- row operations -------------- 1 queries inside innodb, 0 queries in queue; main thread: purging number of rows inserted 2008, updated 264, deleted 162, read 9 0.00 inserts/s, 0.00 updates/s, 14.66 deletes/s, 0.00 reads/s ---------------------------- end of innodb monitor output ============================
輸出信息的某些注意點： 如果 transactions 部分報告鎖定等待(lock waits)，那么你的應用程序可能有鎖爭用(lock contention)。輸出信息可以幫助跟蹤事務死鎖的原因。 semaphores 部分報告線程等待信號量以及統(tǒng)計出線程需要旋轉(spin)或等待(wait)一個互斥(mutex)或 rw-lock 信號量的次數(shù)。一個較大的線程等待信號量的次數(shù)可能是由于磁盤 i/o 引起，或 innodb 內部的爭用問題(contention problems)。爭用(contention)可能是由于比較繁重的并發(fā)性查詢，或操作系統(tǒng)的線程調度的問題。 在這種情形下，可將 innodb_thread_concurrency 設置地小于默認的 8 。 file i/o 部分列出了文件 i/o 的等待請求。過大的值就意味著磁盤 i/o 瓶頸。 buffer pool and memory 部分給出了頁面讀寫的統(tǒng)計。通過這些值可以計算出你的查詢通常所需的數(shù)據(jù)文件 i/o 量。
