Top's virt mem total is about what can be used, not what is used, but the swapping is fatal for performance and you report lots of other memory stress. Until proved otherwise I'd say big table scans and perhaps mem leaks are the best causal candidates.
We absolutely need the results of that information_schema query. If its crash elicited a MySQL error log entry, what was that error report? If it didn't, apparently system crashes are precluding mysql error log writes.
Apache doesn't use a lot of mem. How much does the app use? How much RAM is actually available to MySQL?
With production DBs of this size, and with problems like those you're having, there absolutely needs to be a twin system where you can try out problem solutions, settings &c.
Here's output from an analytic script ...
MySQL version 5.6.39
Slow queries/sec unknown Enable the slow query log
Queries not using indexes 0 Find & optimise those queries with indexes
Queries needing full table scans/sec 3.6003 Find & optimise those queries
InnoDB buffer pool inefficiency 0.0218 Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool 0.0218 Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing 0.25 Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Mins between InnoDB log rotation 56067.0789 Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec 7.4487 If high, increase Innodb_buffer_pool_size up to 70% of RAM
Est. best InnoDB log file size 32MB Average hour of writes rounded up to 32MB boundary
MyISAM key buffer size 0.13MB Pre-8.0 MyISAM system tables need about 40MB
MyISAM query cache spills / sec 0 If high, grow query_cache_size up to 50
MyISAM query cache hits / sec 0 If < 5, try turning query cache off
MyISAM cache miss ratio 282.81 If > 10, grow key_buffer_size
MyISAM key write ratio 0 Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate 0.01 Should be high when the buffer is full
MyISAM query cache hits/inserts 0 If low, consider disabling the cache
Temp tables created/sec 3.38 If > 20, find the queries responsible and optimise them,
Temp tables created on disk/Questions 0.0401 If > .05, try growing join_buffer_size, optimise responsible queries,
Temp tables created on disk/sec 0.51 If high, grow tmp_table_size, max_heap_size. Use HEAP tables in queries
Database changes / connection 0.64 Use db.table syntax
Processes/connection 0 If high, grow thread_cache_size
Files opened/sec 3.03 If > 5, increase table_open_cache
Queries/sec 14.91 Thousands is busy: you may need better hardware
Writes/sec 0.36 If, over 50/sec, better disk hardware may be needed
Insert statements/sec 0.1
Inserts/sec 0.1 Can be up to 20K/sec with efficient memory use, see here
Next table row requests/sec 891 If high, queries need optimising esp. with covering indexes
Max MySQL RAM use 3.2GB Must be less than RAM available to MySQL
Available RAM 16GB If less than Max MySQL memory use, more memory needed
Points of interest ...
1 Given the mem used, it's a surprisingly unbusy system, just 15 queries/sec
2 But about a quarter of those queries require full table scans---these queries are using lots of mem, need covering index optimisation
3 Nearly a thousand hrs between InnoDB log rotations is way too lomg
4 key_buffer_size is way too low (lots of hits on mysql system tables? if so why?)
5 Max RAM use calculates to 3.2GB but the OS says it's using 25GB, as if there is a giant mem leak
6 innodb_buffer_pool_size
use isn't really out of line, but with so many signs of mem distress, it needs to grow to 70-80% or so
of RAM availale to MySQL. No it won't increase mem distress, it's likely to reduce it. And you need to optimise those table scan queries asap.
What is the machine's swap setting?
Edited 1 time(s). Last edit at 03/15/2019 02:53PM by Peter Brawley.