Significant findings:
-- You are using MyISAM entirely; InnoDB is preferred.
-- Lots of queries causing full table scans, tmp tables, tmp tables spilling to disk, etc.
1. SET long_query_time=1
2. Turn on the slowlog
3. Wait a day
4. run pt-query-digest
5. Let's study the first few (worst) queries. Please provide the query, the EXPLAIN, and SHOW CREATE TABLE.
Details and other findings:
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (100M / 0.20 + 8M / 0.70) / 6144M = 8.3%
-- Most of available ram should be made available for caching.
-- Since you are using (mostly) MyISAM, set key_buffer_size to about 20% of available RAM.
--
http://mysql.rjweb.org/doc.php/memory
( Key_blocks_used * 1024 / key_buffer_size ) = 35,791 * 1024 / 100M = 35.0% -- "High-water-mark" percent of key_buffer used
-- Lower key_buffer_size to avoid unnecessary memory usage.
-- The above two items contradict each other. I conclude that you have very little data, or at least very little in the way of INDEXes. If you add a number of INDEXes, revisit these metrics.
( table_open_cache ) = 95,000 -- Number of table descriptors to cache
( Open_tables / table_open_cache ) = 264 / 95000 = 0.28% -- Cache usage (open tables + tmp tables)
-- Lower table_open_cache; several hundred is usually good.
( max_heap_table_size / _ram ) = 100M / 6144M = 1.6% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease max_heap_table_size to, say, 1% of ram.
( local_infile ) = ON
-- local_infile = ON is a potential security issue
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 5,565 / (5565 + 21361) = 20.7% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.
( tmp_table_size ) = 100M -- Limit on size of temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.
( Select_scan ) = 134,283 / 116511 = 1.2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 134,283 / 2426388 = 5.5% -- % of selects doing full table scan.
-- Add indexes / optimize queries
( Connections ) = 1,364,723 / 116511 = 12 /sec -- Connections
-- Increase wait_timeout; use pooling?
( thread_cache_size ) = 4 -- How many extra processes to keep around (Not relevant when using thread pooling)
( Threads_created / Connections ) = 21,909 / 1364723 = 1.6% -- Rapidity of process creation
-- Increase thread_cache_size to, say, 15.