Mysql
如何使用目前伺服器資源提高 MySQL 性能？
我有一個 MySQL 數據庫，我的一些查詢變得很慢。查詢時間不穩定。大多數查詢很快，但其中一些（可能是讀取和返回的數據較少）需要很長時間。
我知道最佳實踐是添加索引或重構程式碼，但我已經添加了索引並且我不想重構程式碼（至少在我有其他變體時）。
我有 8 Gb 可用記憶體，並且 CPU 在峰值時僅載入到 25%。所以我想使用我所有的資源。
我嘗試調整 MySQL 配置，但我沒有這種調整的經驗，所以我提高生產力並沒有達到我想要的程度。這是一個例子：
# Query_time: 3.019647 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 504 SET timestamp=1313380874; SELECT COUNT(inboxentities.id) FROM inboxentities WHERE (active=true)AND(deleted=false)AND((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))AND(notification=true);PS
inboxentities有 500.000 條記錄。我有 index1 (idx_to) :to_和 index2 (idx_complex):deleted,notification,active,to_這是解釋選擇的結果：
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE | inboxentities | ref | idx_status,idx_statusToname,idx_to,idx_complex | idx_to | 768 | const | 286 | Using where | +----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
這是來自慢日誌的原始查詢
SELECT COUNT(inboxentities.id) FROM inboxentities WHERE (active=true) AND (deleted=false) AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0')) AND (notification=true);為這樣的查詢建立索引的最有效方法是生成一個索引，該索引盡可能多地覆蓋 WHERE 子句中的欄位。考慮到該概念而產生的索引稱為覆蓋索引。
查看 WHERE 子句
- ‘active’ 為真或假（2 個值）。僅該欄位的索引是不平衡的。
- ‘deleted’ 是真還是假（2 個值）。僅該欄位的索引是不平衡的。
- “通知”是真或假（2 個值）。僅該欄位的索引是不平衡的。
- ’to_’ 是一個值得索引和縮小範圍的值。
儘管您定義了 idx_complex，但查詢中還有一個元素導致不使用 idx_complex 索引：**COUNT(inboxentities.id)**子句。
你正在計算一張桌子上的東西。該索引除了 to_ 之外沒有指向表的引用點。MySQL Query Optmizer 會選擇最簡單的索引 idx_to。要強制 MySQL 查詢優化器選擇您想要的覆蓋索引 (idx_complex)，只需從索引而不是表中進行 COUNT。我的建議是稍微更改查詢：
SELECT COUNT(to_) FROM inboxentities WHERE (active=true) AND (deleted=false) AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0')) AND (notification=true);試一試 ！！！
更新 2011-08-15 15:16 EDT
一些人已經看到通過更改 my.cnf 以滿足儲存引擎性能需求，性能得到了邊際到顯著的提升。
您需要設置 MyISAM Key Cache 和 InnoDB Buffer Pool。
這將為您的給定數據集推薦正確大小的 MyISAM 密鑰記憶體：
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B;這將為您的給定數據集推薦正確大小的 InnoDB 緩衝池
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A, (SELECT 2 PowerOf1024) B;順便提一句
- (SELECT 0 PowerOf1024) 以字節為單位生成答案
- (SELECT 1 PowerOf1024) 以 KB 生成答案
- (SELECT 2 PowerOf1024) 以 MB 為單位生成答案
- (SELECT 3 PowerOf1024) 以 GB 生成答案
如果您超出此範圍，請給我發電子郵件。
完成這些設置後，請確保推薦的 key_buffer_size 和推薦的_innodb_buffer_pool_size 的組合數不超過已安裝 RAM 的 75%。
更新 2011-08-15 15:35 EDT
您可能希望在伺服器上執行 mysqltuner.pl，讓它告訴您如何調整 DB 連接的記憶體使用情況。管理每個 DB 連接的記憶體使用的設置包括
這些通常乘以max_connections。
您可以從 Linux 命令行獲取 mysqltuner.pl 下載，如下所示：
wget mysqltuner.pl它的輸出是這樣的：
$ perl mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: username Please enter your MySQL administrative password: (password hidden) -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.47-community-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 7G (Tables: 6) [--] Data in InnoDB tables: 2G (Tables: 382) [!!] Total fragmented tables: 84 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 12d 20h 24m 6s (54M q [49.462 qps], 555K conn, TX: 287B, RX: 95B) [--] Reads / Writes: 62% / 38% [--] Total buffers: 9.3G global + 48.2M per thread (1250 max threads) [!!] Maximum possible memory usage: 68.2G (291% of installed RAM) [OK] Slow queries: 0% (647/54M) [OK] Highest usage of available connections: 5% (69/1250) [OK] Key buffer size / total MyISAM indexes: 256.0M/19.4M [OK] Key buffer hit rate: 100.0% (51M cached / 10 reads) [OK] Query cache efficiency: 89.3% (47M cached / 52M selects) [!!] Query cache prunes per day: 60670 [OK] Sorts requiring temporary tables: 0% (239 temp sorts / 1M sorts) [!!] Temporary tables created on disk: 47% (1M on disk / 2M total) [OK] Thread cache hit rate: 99% (103 created / 555K connections) [OK] Table cache hit rate: 21% (722 open / 3K opened) [OK] Open file limit used: 0% (70/32K) [OK] Table locks acquired immediately: 99% (12M immediate / 12M locks) [OK] InnoDB data size / buffer pool: 2.4G/8.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Increasing the query_cache size over 128M may reduce performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 1G) [see warning above] tmp_table_size (> 32M) max_heap_table_size (> 32M)