(adding a second answer -- to address the VARIABLES and STATUS)
Observations:
Version: 5.6.24
52 GB of RAM
Uptime = 21d 02:26:20
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues
table_open_cache = 200K, yet 156 tables opened per second, and all are misses/overflows from the cache. You must redesign you schema to have a civilized number of tables.
Are there really 403 ROLLBACKs
per second? If so, think about how to avoid some of them. That's probably more than 10% of the transactions.
innodb_lock_wait_timeout = 300 -- Assuming you really need transactions to hang around waiting for 5 minutes, this could be consuming RAM.
tmp_table_size = 8M, together with 51 tmp tables created per second, may be leading to quite a few GB of transient MEMORY tables. Look through the slowlog to find which queries need tmp tables and see if they can be rewritten.
Decrease long_query_time to 2 (now 10), turn on the slowlog, wait a day, run pt-query-digest. Then study the worst few queries -- improving them will speed up the entire system and probably decrease RAM usage.
Details and other observations
( Innodb_buffer_pool_reads ) = 329,533,959 / 1823180 = 180 /sec -- InnoDB buffer_pool I/O read rate
-- check innodb_buffer_pool_size
( Innodb_buffer_pool_pages_flushed ) = 589,768,119 / 1823180 = 323 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size
( innodb_buffer_pool_size / _ram ) = 16,106,127,360 / 53248M = 28.8% -- % of RAM used for InnoDB buffer_pool
( Opened_tables ) = 285,544,112 / 1823180 = 156 /sec -- Frequency of opening Tables
-- increase table_open_cache
( table_open_cache ) = 200,000 -- Number of table descriptors to cache
-- Several hundred is usually good.
( Table_open_cache_overflows ) = 285,343,673 / 1823180 = 156 /sec
-- May need to increase table_open_cache
( Table_open_cache_misses ) = 285,544,112 / 1823180 = 156 /sec
-- May need to increase table_open_cache
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((329533959 + 589768119) ) / 1823180 = 504 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?
( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions.
-- Adjust innodb_log_buffer_size.
( Innodb_log_writes ) = 167,769,519 / 1823180 = 92 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 332,945,398,784 / (1823180 / 3600) / 2 / 100M = 3.13 -- Ratio
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,823,180 / 60 * 100M / 332945398784 = 9.57 -- 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.
( Com_rollback ) = 735,486,482 / 1823180 = 403 /sec -- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.
( innodb_lock_wait_timeout ) = 300 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks.
-- Fix the cause of timeouts rather than increasing this value.
( Innodb_dblwr_writes ) = 14,574,293 / 1823180 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)
( local_infile ) = ON
-- local_infile = ON is a potential security issue
( Questions ) = 7,836,091,851 / 1823180 = 4298 /sec -- Queries (outside SP) -- "qps"
-- >2000 may be stressing server
( Queries ) = 8,262,090,685 / 1823180 = 4531 /sec -- Queries (including inside SP)
-- >3000 may be stressing server
( Created_tmp_tables ) = 92,537,439 / 1823180 = 51 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.
( Created_tmp_disk_tables ) = 6,158,440 / 1823180 = 3.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 being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM.
Better indexes and reformulation of queries can
( Handler_read_rnd_next ) = 266,467,437,770 / 1823180 = 146155 /sec -- High if lots of table scans
-- possibly inadequate keys
( Com_rollback / Com_commit ) = 735,486,482 / 741864016 = 99.1% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic
( Com_show_variables ) = 6,666,028 / 1823180 = 3.7 /sec -- SHOW VARIABLES ...
-- Why are you requesting the VARIABLES so often?
( Select_scan ) = 315,076,005 / 1823180 = 172 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 315,076,005 / 3888296954 = 8.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (613230844 + 1238003 + 0 + 30479625 + 71282659 + 0) / 1823180 = 392 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 23 - 22 ) / ( 23 + 22 ) = 2.2% -- Are you closing your prepared statements?
-- Add Closes.
( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( Com_change_db ) = 31,947,189 / 1823180 = 18 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.
( max_connect_errors ) = 10,000 -- A small protection against hackers.
-- Perhaps no more than 200.
( Connections ) = 21,121,749 / 1823180 = 12 /sec -- Connections
-- Increase wait_timeout; use pooling?
( Threads_running - 1 ) = 22 - 1 = 21 -- Active threads (concurrency when data collected)
-- Optimize queries and/or schema
You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.
39 issues flagged, out of 125 computed Variables/Status/Expressions. Settings not mentioned are mostly OK.
Best Answer
This is not an over-provisioning issue or even a memory misconfiguration.
RESOURCE_SEMAPHORE: "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."
Depending on which version and build you are using of SQL Server, you may be able to query for large memory grants so that you know where to start on which queries are causing a problem. If you are on SQL 2012 SP3 or greater, SQL 2014 SP2 or greater or SQL 2016 RTM or greater, then you can query for this information. See the query at the bottom of New memory grant query hint MIN_GRANT_PERCENT came to rescue.
Check out Query Plans: Memory Grants and High Row Estimates for more information on memory grants, which is the link inside the link sp_Blitz directed you to.