Windows-Server-2008
OsTicket + IIS + mySQL + 極慢的查詢
- 背景：我是.net開發人員，php編碼知識有限
- 問這個問題是因為我認為這是一個 php/mysql/version/implementation 問題，並且沒有從 OSticket 開發端獲得真實資訊……
- 我的編碼/數據庫知識告訴我，這不一定與伺服器設置相關，並且如上所述，沒有從任何 OSticket 資源中獲得太多資訊。
- IIS7、mysql 5.6、php 5.5.13
我們正在執行兩台伺服器。兩者都執行 Server 2008（1 = Web，1 = 標準）。
在兩台伺服器上，我們在 IIS 環境中執行 OSticket，並配置了 php。（兩種不同的 osTicket 實現和數據庫）
在一台伺服器上，一切正常，有數千張票。在另一台伺服器上，查詢最多需要 90 秒……其中大多數是 30-40 秒……這些看起來很簡單的查詢……這個數據庫中大約有 16K 票……其中一些票的大小合適附件…
兩台伺服器都執行相同版本的 OSticket。
這個問題讓我想起了我曾經做過的一個 mySQL 程式碼實現，它也遭受了基本查詢的這些類型的延遲……我不得不做一些簡單的查詢更改，並且它加速了一個因素。這個問題聞起來非常相似。
我在 mySQL 中嘗試了以下內容
- 綁定到靜態 IP，不使用 lcoalhost
- 跳過 dns 解析
- Mysql InnoDB 刷新 trx 日誌到 0 和 2
- 修復了所有表優化表
伺服器真的很閒。它有 16GB RAM 和 8 個核心。它也執行其他服務，IIS、MSSQL 等，但如前所述，它是閒置的。我看不出這是伺服器配置/資源問題。
MySQL 消耗大約 1gb 的 Ram，有時大約 5-15% cpu….（CPU 對我來說似乎很高……）
慢查詢的例子有：
SET timestamp=1411987784; SELECT thread.*, info.email_mid, info.headers ,count(DISTINCT attach.attach_id) as attachments FROM ost_ticket_thread thread LEFT JOIN ost_ticket_email_info info ON (thread.id=info.thread_id) LEFT JOIN ost_ticket_attachment attach ON (thread.ticket_id=attach.ticket_id AND thread.id=attach.ref_id) WHERE thread.id=108610 AND thread.thread_type='N' GROUP BY thread.id; # Time: 140929 12:49:44 # User@Host: osticket[osticket] @ PLM-S01 [192.168.10.11] Id: 154 # Query_time: 28.177612 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 108472InnoDB 狀態查詢返回：
' ===================================== 2014-09-29 14:25:16 25b0 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 57 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1972 srv_active, 0 srv_shutdown, 5354 srv_idle srv_master_thread log flush and writes: 7325 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1366361 OS WAIT ARRAY INFO: signal count 1108283 Mutex spin waits 2150949, rounds 3167588, OS waits 18721 RW-shared spins 1435812, rounds 40962905, OS waits 1344494 RW-excl spins 2597, rounds 57911, OS waits 1364 Spin rounds per wait: 1.47 mutex, 28.53 RW-shared, 22.30 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 6366171 Purge done for trx''s n:o < 6366156 undo n:o < 0 state: running but idle History list length 325 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 14, OS thread handle 0x25b0, query id 61326 localhost 127.0.0.1 osticket init SHOW ENGINE INNODB STATUS ---TRANSACTION 0, not started MySQL thread id 13, OS thread handle 0xa94, query id 57911 localhost 127.0.0.1 osticket cleaning up ---TRANSACTION 0, not started MySQL thread id 6, OS thread handle 0x1cd4, query id 404 PLM-S01 192.168.10.11 osticket cleaning up ---TRANSACTION 6366167, ACTIVE 49 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 318, OS thread handle 0x20b8, query id 60114 PLM-S01 192.168.10.11 osticket updating UPDATE ost_ticket SET isoverdue=1, updated=NOW() WHERE ticket_id=21086 Trx read view will not see trx with id >= 6366168, sees < 6366153 ------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 136 page no 142 n bits 216 index `PRIMARY` of table `plm_osticket`.`ost_ticket` trx id 6366167 lock_mode X locks rec but not gap waiting Record lock, heap no 30 PHYSICAL RECORD: n_fields 24; compact format; info bits 0 0: len 4; hex 0000525e; asc R^;; 1: len 6; hex 0000006123c9; asc a# ;; 2: len 7; hex 6f000002e201ca; asc o ;; 3: len 6; hex 373939353332; asc 799532;; 4: len 4; hex 000003a0; asc ;; 5: len 4; hex 00000000; asc ;; 6: len 4; hex 00000001; asc ;; 7: len 4; hex 00000002; asc ;; 8: len 4; hex 00000002; asc ;; 9: len 4; hex 00000000; asc ;; 10: len 4; hex 0000000a; asc ;; 11: len 4; hex 00000000; asc ;; 12: len 4; hex 00000005; asc ;; 13: len 13; hex 3139322e3136382e31302e3836; asc 192.168.10.86;; 14: len 1; hex 02; asc ;; 15: len 1; hex 01; asc ;; 16: len 1; hex 00; asc ;; 17: SQL NULL; 18: len 5; hex 9993f541b1; asc A ;; 19: SQL NULL; 20: len 5; hex 9993f541b1; asc A ;; 21: len 5; hex 9993ecb9a6; asc ;; 22: len 5; hex 9993eca222; asc ";; 23: len 5; hex 9993fae4da; asc ;; ------------------ ---TRANSACTION 6366153, ACTIVE 351 sec fetching rows, thread declared inside InnoDB 1070 mysql tables in use 2, locked 0 33 lock struct(s), heap size 6544, 33 row lock(s), undo log entries 208 MySQL thread id 314, OS thread handle 0x1864, query id 61324 PLM-S01 192.168.10.11 osticket Sending data SELECT thread.*, info.email_mid, info.headers ,count(DISTINCT attach.attach_id) as attachments FROM ost_ticket_thread thread LEFT JOIN ost_ticket_email_info info ON (thread.id=info.thread_id) LEFT JOIN ost_ticket_attachment attach ON (thread.ticket_id=attach.ticket_id AND thread.id=attach.ref_id) WHERE thread.id=109028 AND thread.thread_type=''M'' GROUP BY thread.id Trx read view will not see trx with id >= 6366154, sees < 6366114 -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (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: 2 Pending flushes (fsync) log: 0; buffer pool: 0 7329448 OS file reads, 28218 OS file writes, 9771 OS fsyncs 1264.75 reads/s, 16384 avg bytes/read, 4.51 writes/s, 1.70 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 17, seg size 19, 3190 merges merged operations: insert 3265, delete mark 252, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 722273, node heap has 1 buffer(s) 566.90 hash searches/s, 68.35 non-hash searches/s --- LOG --- Log sequence number 5444405216 Log flushed up to 5444396184 Pages flushed up to 5444396184 Last checkpoint at 5444396184 1 pending log writes, 0 pending chkp writes 3744 log i/o''s done, 0.67 log i/o''s/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 373637120; in additional pool allocated 0 Dictionary memory allocated 399883 Buffer pool size 22272 Free buffers 1010 Database pages 21261 Old database pages 7828 Modified db pages 12 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 16540365, not young 61439991 2726.25 youngs/s, 8876.23 non-youngs/s Pages read 7329387, created 989, written 23206 1264.80 reads/s, 0.21 creates/s, 3.63 writes/s Buffer pool hit rate 950 / 1000, young-making rate 107 / 1000 not 351 / 1000 Pages read ahead 61.74/s, evicted without access 7.81/s, Random read ahead 0.00/s LRU len: 21261, unzip_LRU len: 0 I/O sum[59357]:cur[18], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread id 8924, state: flushing log Number of rows inserted 3212, updated 1123, deleted 8, read 132865050 1.58 inserts/s, 0.25 updates/s, 0.00 deletes/s, 19276.61 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ '我會很感激一些關於我可以從哪裡開始擺弄的想法……
我最終通過導出數據庫、創建新數據庫和導入舊數據來解決這個問題。
不要問我為什麼它有效，但它確實有效。