Mysql
我們的 MySQL 性能不佳是否與日常查詢記憶體修剪過多有關？
我們每天都在經歷大量的查詢記憶體修剪，目前為 80851746。即使在 50 多個並發連接的合理負載下，MySQL 也確實很掙扎。
主機是一個物理伺服器，其 SSD 配置為 RAID 5、24 核和 128GB RAM。它是一個專用的 MySQL 5.6 伺服器，可供各種 PHP 客戶端訪問。
該實例由 3 個數據庫組成，總大小為 1TB，所有表都是 InnoDB 和壓縮的。
我在下麵包含了盡可能多的資訊，任何建議將不勝感激。如果需要，我可以提供更多資訊。謝謝。
作業系統資訊
# lsb_release -a Distributor ID: Ubuntu Description: Ubuntu 14.04.5 LTS Release: 14.04 Codename: trusty # uname -a Linux xxxxxxxxxx 3.13.0-32-generic #57-Ubuntu SMP Tue Jul 15 03:51:08 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux記憶體資訊
# free -m total used free shared buffers cached Mem: 128915 128335 580 0 396 78764 -/+ buffers/cache: 49174 79740 Swap: 30517 192 30325中央處理器資訊
# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 45 model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz stepping : 7 microcode : 0x704 cpu MHz : 1200.000 cache size : 15360 KB physical id : 0 siblings : 12 core id : 0 cpu cores : 6 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 s s ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eag erfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic popcnt tsc_deadli ne_timer aes xsave avx lahf_lm ida arat epb xsaveopt pln pts dtherm tpr_shadow vnmi flexpriority ept vpid bogomips : 4999.98 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management: # top top - 10:48:41 up 94 days, 18:37, 5 users, load average: 8.69, 9.65, 9.85 Tasks: 385 total, 1 running, 383 sleeping, 0 stopped, 1 zombie %Cpu0 : 19.7 us, 5.0 sy, 0.0 ni, 71.3 id, 1.0 wa, 3.0 hi, 0.0 si, 0.0 st %Cpu1 : 15.3 us, 3.3 sy, 0.0 ni, 81.0 id, 0.0 wa, 0.3 hi, 0.0 si, 0.0 st %Cpu2 : 24.1 us, 5.7 sy, 0.0 ni, 66.6 id, 1.0 wa, 2.7 hi, 0.0 si, 0.0 st %Cpu3 : 12.7 us, 3.3 sy, 0.0 ni, 83.7 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu4 : 4.0 us, 2.0 sy, 0.0 ni, 93.4 id, 0.3 wa, 0.3 hi, 0.0 si, 0.0 st %Cpu5 : 14.7 us, 4.0 sy, 0.0 ni, 80.0 id, 0.7 wa, 0.7 hi, 0.0 si, 0.0 st %Cpu6 : 17.1 us, 3.7 sy, 0.0 ni, 76.9 id, 1.7 wa, 0.7 hi, 0.0 si, 0.0 st %Cpu7 : 42.5 us, 7.0 sy, 0.0 ni, 49.5 id, 0.7 wa, 0.3 hi, 0.0 si, 0.0 st %Cpu8 : 37.3 us, 2.7 sy, 0.0 ni, 58.7 id, 0.7 wa, 0.7 hi, 0.0 si, 0.0 st %Cpu9 : 33.3 us, 4.3 sy, 0.0 ni, 60.3 id, 0.3 wa, 1.7 hi, 0.0 si, 0.0 st %Cpu10 : 17.3 us, 5.0 sy, 0.0 ni, 75.0 id, 0.7 wa, 2.0 hi, 0.0 si, 0.0 st %Cpu11 : 17.7 us, 4.0 sy, 0.0 ni, 76.7 id, 1.3 wa, 0.3 hi, 0.0 si, 0.0 st %Cpu12 : 0.3 us, 0.3 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu13 : 17.8 us, 4.4 sy, 0.0 ni, 75.8 id, 0.0 wa, 2.0 hi, 0.0 si, 0.0 st %Cpu14 : 1.0 us, 0.3 sy, 0.0 ni, 98.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu15 : 0.3 us, 0.3 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu16 : 25.3 us, 4.3 sy, 0.0 ni, 68.0 id, 0.3 wa, 2.0 hi, 0.0 si, 0.0 st %Cpu17 : 0.0 us, 0.3 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu18 : 27.0 us, 1.0 sy, 0.0 ni, 71.3 id, 0.7 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu19 : 1.3 us, 0.7 sy, 0.0 ni, 98.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu20 : 25.9 us, 0.3 sy, 0.0 ni, 73.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu21 : 5.7 us, 2.3 sy, 0.0 ni, 92.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st %Cpu22 : 51.3 us, 4.7 sy, 0.0 ni, 41.0 id, 0.7 wa, 2.3 hi, 0.0 si, 0.0 st %Cpu23 : 35.3 us, 9.7 sy, 0.0 ni, 51.7 id, 1.0 wa, 2.3 hi, 0.0 si, 0.0 st KiB Mem: 13200956+total, 13146136+used, 548208 free, 406712 buffers KiB Swap: 31250428 total, 198052 used, 31052376 free. 80715696 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 13928 mysql 20 0 56.424g 0.044t 10028 S 517.3 35.4 9937:02 mysqld 540 root 20 0 0 0 0 S 6.0 0.0 2392:26 jbd2/sdb1-8儲存資訊
# dmesg [ 6.615531] megasas: 0x1000:0x005b:0x1000:0x9266: bus 4:slot 0:func 0 [ 6.742104] megasas: FW now in Ready state [ 6.784030] megaraid_sas 0000:04:00.0: irq 94 for MSI/MSI-X [ 6.784042] megaraid_sas 0000:04:00.0: irq 95 for MSI/MSI-X [ 6.784051] megaraid_sas 0000:04:00.0: irq 96 for MSI/MSI-X [ 6.784058] megaraid_sas 0000:04:00.0: irq 97 for MSI/MSI-X [ 6.784065] megaraid_sas 0000:04:00.0: irq 98 for MSI/MSI-X [ 6.784072] megaraid_sas 0000:04:00.0: irq 99 for MSI/MSI-X [ 6.784093] megaraid_sas 0000:04:00.0: irq 100 for MSI/MSI-X [ 6.784100] megaraid_sas 0000:04:00.0: irq 101 for MSI/MSI-X [ 6.784106] megaraid_sas 0000:04:00.0: irq 102 for MSI/MSI-X [ 6.784112] megaraid_sas 0000:04:00.0: irq 103 for MSI/MSI-X [ 6.784119] megaraid_sas 0000:04:00.0: irq 104 for MSI/MSI-X [ 6.784125] megaraid_sas 0000:04:00.0: irq 105 for MSI/MSI-X [ 6.784131] megaraid_sas 0000:04:00.0: irq 106 for MSI/MSI-X [ 6.784137] megaraid_sas 0000:04:00.0: irq 107 for MSI/MSI-X [ 6.784155] megaraid_sas 0000:04:00.0: irq 108 for MSI/MSI-X [ 6.784162] megaraid_sas 0000:04:00.0: irq 109 for MSI/MSI-X [ 6.784204] megaraid_sas 0000:04:00.0: [scsi0]: FW supports<16> MSIX vector,Online CPUs: <24>,Current MSIX <16> [ 6.869100] md: multipath personality registered for level -4 [ 6.893842] megasas:IOC Init cmd success [ 6.917873] megasas: INIT adapter done [ 6.989936] megaraid_sas 0000:04:00.0: Controller type: MR,Memory size is: 1024MB [ 6.990092] scsi0 : LSI SAS based MegaRAID driver [ 6.990290] scsi 0:0:0:0: Direct-Access LSI MR9266-8i 3.16 PQ: 0 ANSI: 5 [ 6.990374] scsi 0:0:1:0: Direct-Access LSI MR9266-8i 3.16 PQ: 0 ANSI: 5 [ 6.991442] scsi 0:0:17:0: Enclosure LSI CORP SAS2X28 0717 PQ: 0 ANSI: 5 [ 7.002766] scsi 0:2:0:0: Direct-Access LSI MR9266-8i 3.16 PQ: 0 ANSI: 5 [ 7.002869] scsi 0:2:1:0: Direct-Access LSI MR9266-8i 3.16 PQ: 0 ANSI: 5 [ 7.008930] scsi 0:0:17:0: Attached scsi generic sg0 type 13 [ 7.009066] sd 0:2:0:0: Attached scsi generic sg1 type 0 [ 7.009080] sd 0:2:0:0: [sda] 2342125568 512-byte logical blocks: (1.19 TB/1.08 TiB) [ 7.009145] sd 0:2:0:0: [sda] Write Protect is off [ 7.009146] sd 0:2:0:0: [sda] Mode Sense: 1f 00 00 08 [ 7.009188] sd 0:2:0:0: [sda] Write cache: enabled, read cache: enabled, doesn't support DPO or FUA [ 7.009212] sd 0:2:1:0: Attached scsi generic sg2 type 0 [ 7.009230] sd 0:2:1:0: [sdb] 8197439488 512-byte logical blocks: (4.19 TB/3.81 TiB) [ 7.009289] sd 0:2:1:0: [sdb] Write Protect is off [ 7.009290] sd 0:2:1:0: [sdb] Mode Sense: 1f 00 00 08 [ 7.009343] sd 0:2:1:0: [sdb] Write cache: enabled, read cache: enabled, doesn't support DPO or FUA [ 7.017925] sda: sda1 sda2 sda3 sda4 [ 7.018501] sd 0:2:0:0: [sda] Attached SCSI disk [ 7.028790] sdb: sdb1 [ 7.029058] sd 0:2:1:0: [sdb] Attached SCSI disk # df -h Filesystem Size Used Avail Use% Mounted on udev 63G 12K 63G 1% /dev tmpfs 13G 1.1M 13G 1% /run /dev/sda1 19G 5.3G 13G 31% / none 4.0K 0 4.0K 0% /sys/fs/cgroup none 5.0M 0 5.0M 0% /run/lock none 63G 12K 63G 1% /run/shm none 100M 0 100M 0% /run/user /dev/sda3 9.1G 2.7G 6.0G 31% /var /dev/sdb1 3.8T 1.5T 2.1T 42% /data tmpfs 48G 96K 48G 1% /mysql_tmpMySQL 資訊
# service mysql status * /usr/bin/mysqladmin Ver 8.42 Distrib 5.6.36-82.1, for debian-linux-gnu on x86_64 Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.6.36-82.1-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 1 day 1 hour 56 min 7 sec Threads: 640 Questions: 730887058 Slow queries: 3189910 Opens: 3837 Flush tables: 1 Open tables: 2006 Queries per second avg: 7828.109MySQL my.cnf
[mysqld] character-set-server = utf8 collation-server = utf8_unicode_ci server-id = 20 user = mysql pid-file = /run/mysqld/mysqld.pid socket = /run/mysqld/mysqld.sock bind-address = 0.0.0.0 skip-name-resolve tmpdir = /mysql_tmp datadir = /data/mysql/ default-storage-engine = InnoDB myisam-recover-options = FORCE,BACKUP binlog_format = mixed log_bin = /data/mysql/binlog/mysql-bin expire-logs-days = 4 sync_binlog = 1 slave-net-timeout = 60 max-connections = 2000 interactive_timeout = 14400 wait_timeout = 14400 tmp-table-size = 1024M max-heap-table-size = 1024M query-cache-type = 1 query-cache-size = 33554432 thread-cache-size = 100 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 key-buffer-size = 32M max-allowed-packet = 16M max-connect-errors = 1000000 innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 4G innodb-buffer-pool-size = 40G innodb-file-per-table = 1 innodb_file-format = Barracuda innodb-flush-log-at-trx-commit = 1 innodb_support_xa = 1 log-error = /var/log/mysql/mysql-error.log log-warnings = 2 slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.logmysqltune 輸出
# mysqltuner >> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net> -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.36-82.1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in InnoDB tables: 778G (Tables: 399) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [!!] Total fragmented tables: 80 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 2h 22m 29s (728M q [7K qps], 19M conn, TX: 1773B, RX: 95B) [--] Reads / Writes: 80% / 20% [--] Total buffers: 41.1G global + 1.1M per thread (2000 max threads) [OK] Maximum possible memory usage: 43.3G (34% of installed RAM) [OK] Slow queries: 0% (3M/728M) [OK] Highest usage of available connections: 51% (1029/2000) [OK] Key buffer size / total MyISAM indexes: 32.0M/109.0K [OK] Key buffer hit rate: 100.0% (1B cached / 15 reads) [OK] Query cache efficiency: 52.9% (301M cached / 569M selects) [!!] Query cache prunes per day: 80851746 [OK] Sorts requiring temporary tables: 1% (208K temp sorts / 13M sorts) [!!] Joins performed without indexes: 60354 [!!] Temporary tables created on disk: 49% (49M on disk / 98M total) [OK] Thread cache hit rate: 99% (9K created / 19M connections) [OK] Table cache hit rate: 52% (2K open / 3K opened) [OK] Open file limit used: 0% (81/65K) [OK] Table locks acquired immediately: 100% (349M immediate / 349M locks) [!!] InnoDB data size / buffer pool: 778.6G/40.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (> 32M) join_buffer_size (> 256.0K, or always use indexes with joins) innodb_buffer_pool_size (>= 778G)
我願意，原因之一是：InnoDB 和查詢記憶體不是好鄰居。為什麼 ？
上
Jun 07, 2014，我回答了為什麼從 MySQL 5.6 開始預設禁用 query_cache_type 的問題？. 在那篇文章中，我詩意地描述了 InnoDB 如何人工處理查詢記憶體。我最初是從High Performance MySQL (2nd Edition) 的第 209-215 頁獲得的資訊。我之前建議人們禁用他們的查詢記憶體：
Sep 25, 2013:使查詢記憶體條目無效（鍵）Sep 26, 2013：查詢記憶體命中值在我的數據庫中沒有改變Dec 23, 2013:具有高 CPU 和記憶體使用率的 MySQL建議
通過將query_cache_size和query_cache_type設置為 0 來禁用查詢記憶體。如果情況有所改善，那就太好了。如果沒有改善，您必須做三 (3) 件事
- 將query_cache_type設置回 1
- 查詢記憶體大小
- 您現在將其設置為 32M (
33554432)- 請增加它
- query_cache_limit
- 你沒有設置它。預設值為 1M。
- 使用32M 的query_cache_size，您至少只能保存 32 個結果，每個結果為 1M。如果您有較小的結果集進入查詢記憶體，這可能會擠滿查詢記憶體並導致大量修剪，因為沒有肘部空間容納新的傳入結果集。
小心 ！！！
如果您使用的是 MySQL 5.7.19 或更早版本，請閱讀 MySQL查詢記憶體配置文件以獲得更權威的建議。
Rolando 已經徹底解決了您關於查詢記憶體的問題，但我想我會補充一點，您的價值對於
innodb_buffer_pool_size專用伺服器來說可能太小了。您通常希望為 InnoDB 緩衝池提供盡可能多的 RAM。在 Engine Yard，我們在這種大小的實例上將 RAM 設置為 85%，儘管我們也預設設置比您在此處使用的
innodb_buffer_pool_size值小得多的值。也就是說，您提供的輸出顯示您有超過 70GB 的核心磁碟記憶體，最好由 InnoDB 直接處理。tmp-table-size``max-heap-table-size``free -m我建議使用
innodb_buffer_pool_size = 100G相當保守的值進行測試，看看是否有幫助。它是否有很大幫助取決於您的工作集大小；如果您經常讀取超過 100G 的 1TB 數據集，那麼它可能對您沒有多大幫助，但如果您能夠將經常訪問的數據放入緩衝池中，那麼您應該會看到提升！