0
 -------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 75d 21h 28m 49s (441M q [67.390 qps], 1M conn, TX: 5795G, RX: 281G)
[--] Reads / Writes: 44% / 56%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 71.7G
[--] Other process memory: 0B
[--] Total buffers: 38.3G global + 32.9M per thread (1000 max threads)
[--] P_S Max memory usage: 1G
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 65.1G (103.78% of installed RAM)
[!!] Maximum possible memory usage: 71.7G (114.32% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (21K/441M)
[OK] Highest usage of available connections: 79% (794/1000)
[!!] Aborted connections: 12.77%  (226078/1769806)
[!!] 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: 5% (26K temp sorts / 519K sorts)
[!!] Joins performed without indexes: 372233
[OK] Temporary tables created on disk: 11% (7M on disk / 62M total)
[OK] Thread cache hit rate: 99% (2K created / 1M connections)
[!!] Table cache hit rate: 0% (4K open / 118M opened)
[OK] Open file limit used: 0% (18/65K)
[OK] Table locks acquired immediately: 100% (1B immediate / 1B locks)
[OK] Binlog cache memory access: 99.06% (67689908 Memory / 68333632 Total)


[!!] Key buffer used: 18.2% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/105.0K
[OK] Read Key buffer hit rate: 100.0% (1B cached / 231K reads)
[!!] Write Key buffer hit rate: 1.2% (364M cached / 4M writes)


[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 8
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 38.0G/35.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.657894736842105 %): 128.0M * 2/38.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 16
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.99% (65676094854 hits/ 65679543386 total)
[OK] InnoDB Write log efficiency: 99.27% (1055996396 hits/ 1063709557 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7713161 writes)


max_connections configured =1000
we average around 500

CPU: 16 virtual
RAM: 64 GB
HDD: SAN(ISCSI)

I need help understanding what is going wrong with our server, some times the DB hung and do not process any requests, at this time i see connections for DB spike to 2000.

Prudhvi Raju
  • 1
  • Where is the rest of the output? – Michael Hampton Sep 30 '19 at 08:47
  • 1
    Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Oct 10 '19 at 00:32
  • What changes did you make to the default configuration? – Rick James Oct 11 '19 at 01:33
  • Show us what query one of those 2000 was running. – Rick James Oct 11 '19 at 01:34

1 Answers1

0

max_connections configured =1000 we average around 500

67.390 qps

Very busy system. However, letting 500 connections vie for action is likely to cause them to stumble over each other. Throttle the number of connections at the client side, if you can.

Turn on the slowlog with a low value of long_query_time (perhaps 0.2). Wait a while, the summarize using pt-query-digest. More details: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Rick James
  • 2,463
  • 1
  • 6
  • 13