Centos
MySQL正在使用過多的CPU
我有 32GB 記憶體的 i7 3770 專用伺服器，並且安裝了 Centos7，下面是 CPU 詳細資訊
[root@server ~]# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 2 Core(s) per socket: 4 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 58 Model name: Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz Stepping: 9 CPU MHz: 1712.218 BogoMIPS: 6799.29 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 8192K NUMA node0 CPU(s): 0-7並且 mysql 在其上使用了過多的 CPU。我將它用於網路託管，並且沒有要求如此高的訪問量的帳戶。數據庫是混合的，有些是InnoDB，有些是MyISAM，我不能把它們都做成InnoDB或MyISAM，它需要保持這樣。
這是我的mysql配置
[mysqld] default-storage-engine=InnoDB performance-schema=0 max_allowed_packet=268435456 join_buffer_size = 24M explicit_defaults_for_timestamp = 1 read_rnd_buffer_size=1024K read_buffer_size=4M sort_buffer_size=4M table_open_cache=8000 key_buffer_size=2G local-infile=0 tmp_table_size=32M max_heap_table_size=32M slow_query_log=1 slow_query_log_file="/var/log/mysql-slow-queries.log" long_query_time=10 innodb_buffer_pool_size=10G innodb_buffer_pool_instances = 10 innodb_log_file_size=1024M innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_autoinc_lock_mode=0 innodb_read_io_threads=4 innodb_write_io_threads=8Mysql Tuner 結果是這樣的
-------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 911M (Tables: 2990) [--] Data in InnoDB tables: 3G (Tables: 2349) [--] Data in MEMORY tables: 0B (Tables: 38) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'darssopt_mi@%' hasn't specific host restriction. [!!] User 'darssoptions@%' hasn't specific host restriction. [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 6h 22m 27s (1M q [79.246 qps], 264K conn, TX: 6G, RX: 190M) [--] Reads / Writes: 93% / 7% [--] Binary logging is disabled [--] Physical Memory : 31.0G [--] Max MySQL memory : 17.0G [--] Other process memory: 1.4G [--] Total buffers: 12.0G global + 33.2M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 12.8G (41.33% of installed RAM) [OK] Maximum possible memory usage: 17.0G (54.76% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (21/1M) [OK] Highest usage of available connections: 15% (23/151) [OK] Aborted connections: 0.08% (219/264034) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 3% (8K temp sorts / 235K sorts) [!!] Joins performed without indexes: 3128 [OK] Temporary tables created on disk: 19% (22K on disk / 115K total) [OK] Thread cache hit rate: 99% (775 created / 264K connections) [OK] Table cache hit rate: 99% (5K open / 6K opened) [OK] Open file limit used: 15% (6K/40K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.6% (399M used / 2B cache) [OK] Key buffer size / total MyISAM indexes: 2.0G/181.8M [OK] Read Key buffer hit rate: 100.0% (79M cached / 8K reads) [!!] Write Key buffer hit rate: 93.5% (460K cached / 29K writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 10.0G/3.4G [OK] InnoDB buffer pool instances: 10 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 100.00% (4991812913 hits/ 4991885443 total) [!!] InnoDB Write Log efficiency: 41.29% (38836 hits/ 94048 total) [OK] InnoDB log waits: 0.00% (0 waits / 55212 writes) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Restrict Host for user@% to user@SpecificDNSorIp MySQL started within last 24 hours - recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes. Please note this calculation is made by adding Select_full_join + Select_range_check status values and triggered when the total >250 Variables to adjust: join_buffer_size (> 24.0M, or always use indexes with joins)頂部的結果
top - 14:51:35 up 4 days, 22:36, 3 users, load average: 5.88, 5.85, 6.11 Tasks: 275 total, 3 running, 271 sleeping, 0 stopped, 1 zombie %Cpu(s): 9.1 us, 0.8 sy, 0.1 ni, 67.1 id, 22.9 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 32460092 total, 1248560 free, 5231404 used, 25980128 buff/cache KiB Swap: 16760828 total, 16506348 free, 254480 used. 26023012 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 670307 mysql 20 0 15.638g 3.728g 8680 S 62.1 12.0 103:40.03 mysqld 792649 adv+ 20 0 235404 29852 8320 R 3.3 0.1 0:00.10 php-cgi 792670 bdd 20 0 226492 20820 8292 R 1.3 0.1 0:00.04 php-cgi 3359 root 30 10 277760 30508 2720 D 0.7 0.1 30:35.10 python2.7 17 root 20 0 0 0 0 S 0.3 0.0 13:27.67 rcu_sched 19 root 20 0 0 0 0 S 0.3 0.0 12:00.05 rcuos/1 21 root 20 0 0 0 0 S 0.3 0.0 3:48.60 rcuos/3 469 root 0 -20 0 0 0 S 0.3 0.0 8:35.86 kworker/+ 545 root 20 0 0 0 0 D 0.3 0.0 3:40.02 jbd2/md2+ 3336 root 30 10 417280 9084 1244 S 0.3 0.0 16:16.79 python2.7 599324 root 20 0 107804 28732 2080 S 0.3 0.1 0:06.44 tailwatc+ 670290 root 20 0 186500 18572 3344 S 0.3 0.1 0:02.42 cPhulkd + 792376 nobody 20 0 216284 24376 2428 S 0.3 0.1 0:00.02 httpd 792449 superst 20 0 19972 2852 2116 S 0.3 0.0 0:00.05 pop3 792498 root 20 0 155924 2356 1544 R 0.3 0.0 0:00.07 top 1 root 20 0 338544 3812 2176 S 0.0 0.0 2:43.82 systemd 2 root 20 0 0 0 0 S 0.0 0.0 0:00.18 kthreadd這是 iostat 的結果
[root@server ~]# iostat Linux 3.10.0-427.36.1.lve1.4.40.el7.x86_64 (server.connect.rs) 03/23/2017 _x86_64_ (8 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 4.12 0.42 0.68 16.87 0.00 77.92 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sdb 65.24 2957.95 981.59 1264085940 419483991 sda 68.70 2749.45 981.59 1174981406 419483991 md1 0.00 0.05 0.00 23171 38 md0 0.70 0.87 1.92 370128 820580 md3 42.31 496.39 318.45 212134125 136090304 md2 68.18 104.71 650.46 44746045 277975140Mysql幾乎總是40%以上，最高100%
昨天我添加了禁用的 mysql 服務 5 分鐘，負載低於 1。
請告知如何改進它
如我所見，如果 mysql 使用 100%，那麼你有核心i7 cpu，那麼它只使用一個核心，你的使用率可以上升到 700%
還要檢查資源是否存在讀寫延遲等待時間如果啟用了任何從屬複製，請嘗試禁用它並檢查正在向 mysql 發送查詢的 Web 伺服器，對於慢速查詢和殭屍程序，它也可能是錯誤或錯誤查詢檢查
查看以下連結了解詳情
你需要
- 安裝監控。
- 收集資料。
- 分析數據
- 做出改變
- 監控更改的有效性
- …
科學方法是你的朋友。