Set innodb_buffer_pool_size = 14G
; don't bother with any other tuning until you encounter issues.
If, by 1000 tickets/day, you mean a few thousand queries per day, well, that is 'trivial'. Even a few thousand queries per minute is not a lot.
Further analysis...
Observations:
- Version: 5.6.32-log
- 25 GB of RAM
- Uptime = 22:16:52; some GLOBAL STATUS values may not be meaningful yet.
- You are running on Windows.
- Running 64-bit version
- You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
1700 qps, not queries-per-day! So the VARIABLES
and STATUS
are worth studying.
innodb_buffer_pool_size = 14G
since is currently considerably bigger than the dataset.) Also
innodb_buffer_pool_instances = 14
Com_rollback
is quite high (3.7/sec); are you deliberately running things that need to be undone? Could they check sooner, thereby avoiding a potentially costly rollback? Com_rollback
and Com_commit
are surprisingly close in value. Sounds fishy.
The next upgrade may spit at you about innodb_additional_mem_pool_size
, which is unused and deprecated. Remove it from my.cnf.
myisam_sort_buffer_size = 500M
(The current 3G threatens to cause you to run out of RAM.)
tmp_table_size
and max_heap_table_size
work together. The former is dangerously high; the latter is possibly too small. Set both to 200M.
(Normally I advise against using the Query Cache. However, your application seems to make good use of it.)
Created_tmp_disk_tables
is quite high. That, and some other clues, indicate inefficient queries. See this for details on finding slow queries and presenting them for critique.
Since you are running on Windows, I think thread_cache_size
should be set to 0.
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 1,192 * 1024 / 8M = 14.6%
-- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.
( innodb_buffer_pool_size / _ram ) = 20480M / 25600M = 80.0%
-- % of RAM used for InnoDB buffer_pool
( innodb_buffer_pool_instances ) = 20
-- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 1,107,476 * 16384 / 20480M = 84.5%
-- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,107,476 / 1310720 = 84.5%
-- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,195,551,744 / 20480M = 14.9%
-- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,081,532,416 / (80212 / 3600) / 2 / 2048M = 0.0113
-- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 80,212 / 60 * 2048M / 1081532416 = 2,654
-- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)
( Com_rollback ) = 295,694 / 80212 = 3.7 /sec
-- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.
( innodb_additional_mem_pool_size ) = 32M
-- (deprecated in 5.6.3, removed in 5.7.4.)
-- A high value is unnecessary.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
-- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( join_buffer_size / _ram ) = 262,144 / 25600M = 0.00%
-- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.
( myisam_sort_buffer_size / _ram ) = 3072M / 25600M = 12.0%
-- Used for ALTER, CREATE INDEX, OPTIMIZE, LOAD DATA; set when you need it. Also for MyISAM's REPAIR TABLE.
-- Decrease myisam_sort_buffer_size to keep from blowing out RAM.
( query_prealloc_size / _ram ) = 8,192 / 25600M = 0.00%
-- For parsing. Pct of RAM
( query_alloc_block_size / _ram ) = 8,192 / 25600M = 0.00%
-- For parsing. Pct of RAM
( net_buffer_length / max_allowed_packet ) = 16,384 / 256M = 0.01%
( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue
( Qcache_inserts - Qcache_queries_in_cache ) = (9257553 - 3004) / 80212 = 115 /sec
-- Invalidations/sec.
( Created_tmp_disk_tables ) = 597,469 / 80212 = 7.4 /sec
-- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.
( Created_tmp_disk_tables / Created_tmp_tables ) = 597,469 / 614226 = 97.3%
-- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.
( tmp_table_size ) = 853M
-- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.
( Com_rollback / Com_commit ) = 295,694 / 298826 = 99.0%
-- Rollback : Commit ratio
-- Rollbacks are costly; change app logic
( Select_scan ) = 98,730 / 80212 = 1.2 /sec
-- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( innodb_autoinc_lock_mode ) = 1
-- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".
( long_query_time ) = 10
-- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( Threads_created / Connections ) = 322 / 1160 = 27.8%
-- Rapidity of process creation
-- Increase thread_cache_size (non-Windows)
Abnormally large:
Com_show_keys = 0.063 /sec
Com_show_plugins = 0.27 /HR
Innodb_buffer_pool_pages_free = 1.11e+6
Qcache_hits = 1529 /sec
Select_range = 35 /sec
Select_range / Com_select = 23.2%
Sort_range = 36 /sec
myisam_sort_buffer_size = 3072MB