Mysql

mySQL優化建議

  • March 12, 2016

我正在嘗試為我們的大型 Magento 網站優化我們的 mySQL 配置。我認為 mySQL 需要進一步配置的原因是因為 New Relic 已經表明我們的 SELECT 查詢在某些類別中需要很長時間（20,000+ ms）。

我執行 MySQLTuner 1.3.0 並得到以下結果……（免責聲明：我在調整一些設置後早些時候重新啟動了 mySQL，因此這裡的結果可能不是 100% 準確）：

>>  MySQLTuner 1.3.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
[OK] Currently running supported MySQL version 5.5.37-35.0
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 7G (Tables: 332)
[--] Data in InnoDB tables: 213G (Tables: 8714)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 353)
[!!] Total fragmented tables: 5492

-------- Security Recommendations  -------------------------------------------
[!!] User '@host5.server1.autopartsnetwork.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User 'root@%' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 3m 4s (5M q [317.443 qps], 42K conn, TX: 18B, RX: 2B)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 35.5G global + 184.5M per thread (1024 max threads)
[!!] Maximum possible memory usage: 220.0G (174% of installed RAM)
[OK] Slow queries: 0% (6K/5M)
[OK] Highest usage of available connections: 5% (61/1024)
[OK] Key buffer size / total MyISAM indexes: 512.0M/3.1G
[OK] Key buffer hit rate: 100.0% (102M cached / 45K reads)
[OK] Query cache efficiency: 66.9% (3M cached / 5M selects)
[!!] Query cache prunes per day: 3486361
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 812K sorts)
[!!] Joins performed without indexes: 1328
[OK] Temporary tables created on disk: 11% (126K on disk / 1M total)
[OK] Thread cache hit rate: 99% (61 created / 42K connections)
[!!] Table cache hit rate: 19% (9K open / 49K opened)
[OK] Open file limit used: 2% (712/25K)
[OK] Table locks acquired immediately: 100% (5M immediate / 5M locks)
[!!] InnoDB  buffer pool / data size: 32.0G/213.4G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
   Run OPTIMIZE TABLE to defragment tables for better performance
   MySQL started within last 24 hours - recommendations may be inaccurate
   Reduce your overall MySQL memory footprint for system stability
   Enable the slow query log to troubleshoot bad queries
   Increasing the query_cache size over 128M may reduce performance
   Adjust your join queries to always utilize indexes
   Increase table_cache gradually to avoid file descriptor limits
   Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
 *** MySQL's maximum memory usage is dangerously high ***
 *** Add RAM before increasing MySQL buffer variables ***
   query_cache_size (> 512M) [see warning above]
   join_buffer_size (> 128.0M, or always use indexes with joins)
   table_cache (> 12288)
   innodb_buffer_pool_size (>= 213G)

我的my.cnf配置如下…

[client]
port            = 3306

[mysqld_safe]
nice            = 0

[mysqld]
tmpdir = /var/lib/mysql/tmp
user            = mysql
port            = 3306
skip-external-locking
character-set-server = utf8
collation-server     = utf8_general_ci
event_scheduler      = 0

key_buffer              = 512M
max_allowed_packet      = 64M
thread_stack            = 512K
thread_cache_size       = 512
sort_buffer_size        = 24M
read_buffer_size        = 8M
read_rnd_buffer_size    = 24M
join_buffer_size        = 128M
# for some nightly processes client sessions set the join buffer to 8 GB

auto-increment-increment = 1
auto-increment-offset    = 1

myisam-recover          = BACKUP
max_connections         = 1024
# max connect errors artificially high to support behaviors of NetScaler monitors
max_connect_errors      = 999999
concurrent_insert       = 2
connect_timeout         = 5
wait_timeout            = 180
net_read_timeout        = 120
net_write_timeout       = 120
back_log                = 128
# this table_open_cache might be too low because of MySQL bugs #16244691 and #65384)
table_open_cache        = 12288
tmp_table_size          = 512M
max_heap_table_size     = 512M
bulk_insert_buffer_size = 512M
open-files-limit        = 8192
open-files              = 1024

query_cache_type        = 1
# large query limit supports SOAP and REST API integrations
query_cache_limit       = 4M
# larger than 512 MB query cache size is problematic; this is typically ~60% full
query_cache_size        = 512M

# set to true on read slaves
read_only = false

slow_query_log_file     = /var/log/mysql/slow.log
slow_query_log          = 0
long_query_time         = 0.2

expire_logs_days        = 10
max_binlog_size         = 1024M
binlog_cache_size       = 32K

sync_binlog             = 0

# SSD RAID10 technically has a write capacity of 10000 IOPS
innodb_io_capacity              = 400
innodb_file_per_table
innodb_table_locks              = true
innodb_lock_wait_timeout        = 30
# These servers have 80 CPU threads; match 1:1
innodb_thread_concurrency       = 48
innodb_commit_concurrency       = 2
innodb_support_xa               = true

innodb_buffer_pool_size = 32G
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size  = 2G

skip-federated

[mysqldump]
quick
quote-names
single-transaction
max_allowed_packet      = 64M

我這裡有一個伺服器怪物來為我們的網站提供動力，因為我們的目錄非常大（300,000 個簡單的 SKU），我只是想知道我是否缺少任何可以進一步配置的東西。:-)

謝謝！

這是一個多麼有趣的巧合。我剛剛回答了一個關於 mysqltuner.pl 的問題

https://dba.stackexchange.com/questions/65888/recommendation-for-mysql-please-mysqltuner/65928#65928

我會嘗試為您的問題做同樣的事情。

來自 mysqltuner.pl 的觀察結果

[!!] Maximum possible memory usage: 220.0G (174% of installed RAM)
[!!] InnoDB  buffer pool / data size: 32.0G/213.4G
[OK] Key buffer size / total MyISAM indexes: 512.0M/3.1G
[OK] Highest usage of available connections: 5% (61/1024)
[--] Total buffers: 35.5G global + 184.5M per thread (1024 max threads)

來自 my.cnf 的觀察結果

sort_buffer_size        = 24M
read_buffer_size        = 8M
read_rnd_buffer_size    = 24M
join_buffer_size        = 128M

分析

建議

建議 #1：將 query_cache_size 設置為 0。沒有它，InnoDB 的性能會更好。

建議 #2：如果您對 MyISAM 表進行大量讀取，請將 key_buffer_size 提高到3G

建議 #3：將innodb_buffer_pool_size提高到 75G

建議 #4：由於innodb_buffer_pool_size超過已安裝 RAM 的一半，請將innodb_buffer_pool_instances設置為伺服器上的核心數。如果伺服器是虛擬機，則保留預設值。

建議#5：由於max_connections * ( join_buffer_size + sort_buffer_size + read_buffer_size + read_rnd_buffer_size )，您需要減少 max_connections。

建議#6：更改設置並重新啟動 mysql 後，重新執行 mysqltuner.pl。如果Maximum possible memory usage: ... (.... of installed RAM)消息顯示超過 75% 的已安裝 RAM，請重複 SUGGESTION #5。

試一試 ！！！

您可能應該從以下開始：

/usr/share/mysql/my-innodb-heavy-4G.cnf

並從那裡開始工作，但請記住，需要優化的不僅僅是伺服器，還有查詢和表（索引）。

query_cache_size不應該超過 128M，因為它對性能的傷害比你獲得的更多..

引用自：https://dba.stackexchange.com/questions/66170

相關問答

Mysql

求關於mysql優化的建議

  • March 2, 2022
檢視問答
Mysql

MySQL程序佔用CPU高達180%（需要優化）

  • November 4, 2019
檢視問答
Mysql

優化 MySQL 伺服器性能

  • March 22, 2018
檢視問答
Mysql

mysqltuner - 寫鍵緩衝區命中率

  • August 16, 2017
檢視問答
Mysql

Mariadb MySQL Tuner 報告令人困惑

  • March 16, 2017
檢視問答
Mysql

EC2 T2 Small 2GB RAM MySQL 優化建議

  • June 14, 2016
檢視問答