MySQL使用100%IO

分享于 

11分钟阅读

互联网

  繁體

问题:

我的mysql服务器面临严重的性能问题,IO已使用100%和服务被阻塞

我的mysqld.cnf


[mysqld]


symbolic-links=0


user = mysql


pid-file = /var/run/mysqld/mysqld.pid


socket = /var/run/mysqld/mysqld.sock


basedir = /usr


datadir = /var/lib/mysql


tmpdir = /tmp


log-error = /var/log/mysql/error.log


log-warnings = 2


symbolic-links=0


lc-messages-dir = /usr/share/mysql


skip-external-locking


key_buffer = 2G


thread_stack = 192K


thread_cache_size = 350


myisam-recover = BACKUP


max_connections = 1500


query_cache_limit = 100M


query_cache_size = 512M


general_log = 0


long_query_time = 10


bind-address = 0.0.0.0


old_passwords=1


skip-secure-auth


innodb_buffer_pool_size = 30G


innodb_stats_on_metadata=0


table_open_cache = 4096


skip-external-locking


skip-name-resolve


ft_min_word_len = 2


innodb_buffer_pool_instances = 30


log_slow_queries = /var/log/mysql/slow.log


innodb_file_per_table=0


innodb_checksum_algorithm=INNODB


binlog_checksum=NONE




MySQLTuner输出。






[--] Skipped version check for MySQLTuner script


Please enter your MySQL administrative login: root


Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.6.48-log


[OK] Operating on 64-bit architecture



-------- Storage Engine Statistics -----------------------------------------------------------------


[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 


[--] Data in InnoDB tables: 11G (Tables: 44100)


[--] Data in MyISAM tables: 458G (Tables: 52114)


[!!] Total fragmented tables: 13



-------- Security Recommendations ------------------------------------------------------------------


[OK] There are no anonymous accounts for any database users


[OK] All database users have passwords assigned



-------- CVE Security Recommendations --------------------------------------------------------------


[OK] NO SECURITY CVE FOUND FOR YOUR VERSION



-------- Performance Metrics -----------------------------------------------------------------------


[--] Up for: 17h 33m 28s (23M q [366.194 qps], 733K conn, TX: 250G, RX: 5G)


[--] Reads / Writes: 98% / 2%


[--] Binary logging is disabled


[--] Physical Memory : 62.9G


[--] Max MySQL memory : 34.7G


[--] Other process memory: 148.3M


[--] Total buffers: 32.5G global + 1.1M per thread (1500 max threads)


[--] P_S Max memory usage: 627M


[--] Galera GCache Max memory usage: 0B


[OK] Maximum reached memory usage: 33.3G (52.89% of installed RAM)


[OK] Maximum possible memory usage: 34.7G (55.19% of installed RAM)


[OK] Overall possible memory usage with other process is compatible with memory available


[OK] Slow queries: 0% (248/23M)


[OK] Highest usage of available connections: 6% (104/1500)


[OK] Aborted connections: 0.07% (477/733368)


[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.


[OK] Sorts requiring temporary tables: 0% (977 temp sorts / 2M sorts)


[!!] Joins performed without indexes: 12679


[!!] Temporary tables created on disk: 91% (5M on disk / 5M total)


[OK] Thread cache hit rate: 99% (104 created / 733K connections)


[!!] Table cache hit rate: 0% (1K open / 772K opened)


[OK] Open file limit used: 47% (2K/5K)


[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)



-------- Performance schema ------------------------------------------------------------------------


[--] Performance schema is enabled.


[--] Memory used by P_S: 628.0M


[--] Sys schema isn't installed.



-------- ThreadPool Metrics ------------------------------------------------------------------------


[--] ThreadPool stat is disabled.



-------- MyISAM Metrics ----------------------------------------------------------------------------


[!!] Key buffer used: 19.5% (418M used / 2B cache)


[OK] Key buffer size / total MyISAM indexes: 2.0G/3.4G


[OK] Read Key buffer hit rate: 99.4% (404M cached / 2M reads)


[!!] Write Key buffer hit rate: 73.2% (1M cached / 337K writes)



-------- AriaDB Metrics ----------------------------------------------------------------------------


[--] AriaDB is disabled.



-------- InnoDB Metrics ----------------------------------------------------------------------------


[--] InnoDB is enabled.


[OK] InnoDB buffer pool / data size: 30.0G/11.1G


[OK] InnoDB buffer pool instances: 30


[--] InnoDB Buffer Pool Chunk Size not used or defined in your version


[OK] InnoDB Read buffer efficiency: 99.99% (2251466412 hits/ 2251795801 total)


[!!] InnoDB Write Log efficiency: 69.9% (61767 hits/ 88366 total)


[OK] InnoDB log waits: 0.00% (0 waits / 26599 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:


PASSWORD('secure_password'); )


 Restrict Host for user@% to user@SpecificDNSorIp


 MySQL started within last 24 hours - recommendations may be inaccurate


 Adjust your join queries to always utilize indexes


 When making adjustments, make tmp_table_size/max_heap_table_size equal


 Reduce your SELECT DISTINCT queries which have no LIMIT clause


 Increase table_open_cache gradually to avoid file descriptor limits


 Read this before increasing table_open_cache over 64: 


 Beware that open_files_limit (5000) variable 


 should be greater than table_open_cache ( 1745)


Variables to adjust:


 join_buffer_size (> 256.0K, or always use indexes with joins)


 tmp_table_size (> 16M)


 max_heap_table_size (> 16M)


 table_open_cache (> 1745)



I'm using DELL-R320 with 24 Cores + 62GB RAM with OS on SSD and MySQL data on MegaRaid RAID Controller with 5 TB Of storage on SATA Disks.


 ulimit -a


core file size (blocks, -c) 0


data seg size (kbytes, -d) unlimited


scheduling priority (-e) 0


file size (blocks, -f) unlimited


pending signals (-i) 257444


max locked memory (kbytes, -l) 64


max memory size (kbytes, -m) unlimited


open files (-n) 1024


pipe size (512 bytes, -p) 8


POSIX message queues (bytes, -q) 819200


real-time priority (-r) 0


stack size (kbytes, -s) 8192


cpu time (seconds, -t) unlimited


max user processes (-u) 257444


virtual memory (kbytes, -v) unlimited


file locks (-x) unlimited



我重启MySQL之后


Linux 4.9.0-11-amd64 (db) 21/07/20 _x86_64_ (24 CPU)



avg-cpu: %user %nice %system %iowait %steal %idle


 3.65 0.04 1.37 0.86 0.00 94.07



Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util


sda 0.44 2.65 1.66 0.87 0.03 0.02 43.50 0.00 0.89 0.27 2.06 0.31 0.08


sdb 0.72 4.93 87.32 22.48 8.06 3.07 207.53 0.20 1.86 1.32 3.93 1.27 13.95


dm-0 0.00 0.00 88.16 17.89 8.06 3.07 214.87 0.06 0.59 2.00 6.56 1.32 13.96


dm-1 0.00 0.00 0.91 1.29 0.00 0.01 8.00 0.01 4.46 0.24 7.44 0.10 0.02


dm-2 0.00 0.00 1.15 2.22 0.03 0.02 25.79 0.00 1.02 0.36 1.37 0.17 0.06



avg-cpu: %user %nice %system %iowait %steal %idle


 4.43 0.00 3.93 1.73 0.00 89.91



Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util


sda 0.00 8.60 0.40 2.60 0.00 0.04 31.47 0.00 1.33 0.00 1.54 1.33 0.40


sdb 0.00 6.20 207.20 3.20 1.19 0.03 11.95 0.65 3.09 3.14 0.00 1.81 38.08


dm-0 0.00 0.00 207.20 8.60 1.19 0.03 11.66 0.65 3.01 3.13 0.00 1.76 38.00


dm-1 0.00 0.00 0.00 6.40 0.00 0.03 8.00 0.03 5.00 0.00 5.00 0.62 0.40


dm-2 0.00 0.00 0.40 4.80 0.00 0.02 8.31 0.00 0.00 0.00 0.00 0.00 0.00



avg-cpu: %user %nice %system %iowait %steal %idle


 4.78 0.00 3.61 0.49 0.00 91.13



Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util


sda 0.00 1.60 0.00 0.40 0.00 0.01 40.00 0.00 2.00 0.00 2.00 2.00 0.08


sdb 0.00 1.80 25.20 1.60 0.85 0.01 65.55 0.16 5.97 6.35 0.00 5.22 14.00


dm-0 0.00 0.00 25.20 3.00 0.85 0.01 62.30 0.16 5.76 6.44 0.00 4.99 14.08


dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00


dm-2 0.00 0.00 0.00 2.00 0.00 0.01 8.00 0.01 3.60 0.00 3.60 0.40 0.08





答案1:

在MySQLTuner输出中有两个线索。

调整Join查询以始终使用索引

您可以减少"long_query_time"参数,直到罪魁祸首出现。10秒是很长的时间。

减少没有LIMIT子句的SELECT DISTINCT查询

你应该优化查询:不要选择您不需要的行。"SELECT DISTINCT"会一直浪费资源。


答案2:

  • 看来你服务器使用的是普通硬盘,这对于高负载的SQL Server来说不是很好。请尝试使用SSD磁盘。

  • 第二个应该值得尝试的解决方案是将/tmp和/var/tmp挂载为tmpfs

  • 
    Temporary tables created on disk: 91% (5M on disk / 5M total)
    
    
    
    


  • 相关文章