table_open_cache is the setting that is most likely to get the host off your back.
SHOW VARIABLES LIKE '%open%'; And how long had the system been up when the
STATUS was captured? (Need "per second", not absolute counts.)
Provide me with ram size,
SHOW VARIABLES and
SHOW GLOBAL STATUS; I will critique dozens of things.
Are any of the tables
PARTITIONed? (Each 'partition' is effectively a separate 'table'.)
Also, rethink your 300x59; those are moderately large numbers.
VARIABLES & STATUS Analysis
4 GB of RAM;
Uptime = 145d 22:45:14;
You are not running on Windows;
Running 64-bit version;
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues
key_buffer_size -- lower to 20M; your 400M is wasting RAM.
table_open_cache seems to be doing fine. So, I don't see the need in increasing open file limit. (Nor is there any harm.)
A lot of table scans, etc. Suggest you lower long_query_time to 1 and turn on the slow_query_log. Then we can look in the slowlog for which queries need improvement -- either via composite indexes or other techniques.
expire_logs_days is currently 1. That gie you only one day to discover that something is broken in replication and/or binlogs before you lose info.
Remove most or all OPTIMIZE TABLE calls.
Details and other observations
( Opened_tables ) = 0.15/second -- This is a reasonably low and quite respectable rate. After all, you have been up for 20 weeks, and this is a 'counter'. Point out the 20 weeks to the Hosting provider. table_open_cache is number of entries in a cache for "Opened" tables. Perhaps there is a flurry of opening during backup, but no opening the rest of the time? As for "I do have table_definition_cache set to 4096 as I wasn't able to run queries on views until I increased it." -- Do your VIEWs touch hundreds or thousands of tables? (Yikes!)
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (400M - 1.2 * 4180 * 1024) / 4096M = 9.6% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.
( Key_blocks_used * 1024 / key_buffer_size ) = 4,180 * 1024 / 400M = 1.0% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.
( innodb_buffer_pool_size / _ram ) = 2,306,867,200 / 4096M = 53.7% -- % of RAM used for InnoDB buffer_pool -- For only 4GB of RAM, this is probably OK, unless you have other applications in the same server.
( table_open_cache ) = 4,096 -- Number of table descriptors to cache
-- Several hundred is usually good. However, you have a lot of tables, so, this may be OK, or even too small.
( table_open_cache_instances ) = 1 -- Suggest changing to 8.
Table_open_cache_overflows and _misses are each less than 0.1/sec. -- the table_open_cache is big enough.
( Open_tables / table_open_cache ) = 100% -- But, since you have been Up for several months, this is not necessarily bad.
( (Com_show_create_table + Com_show_fields) / Questions ) = (153314 + 9291657) / 543294681 = 1.7% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.
Query cache -- various STATUS values indicate that it is pretty good as it stands.
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 10,616,310 / (10616310 + 13112655) = 44.7% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.
( Com_rollback / Com_commit ) = 646,634 / 1329015 = 48.7% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic
( Select_scan ) = 16,557,535 / 12609914 = 1.3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 16,557,535 / 57256550 = 28.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 1587 - 1526 ) / ( 1587 + 1526 ) = 2.0% -- Are you closing your prepared statements?
-- Add Closes.
( binlog_format ) = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.
( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
( 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
( max_connect_errors ) = 10,000 -- A small protection against hackers.
-- Perhaps no more than 200.
( Connections ) = 28,367,185 / 12609914 = 2.2 /sec -- Connections
-- Increase wait_timeout; use pooling?
( Max_used_connections / max_connections ) = 167 / 410 = 41% -- Suggest lowering max_connections to, say, 200.
( innodb_io_capacity_max ) = 800 -- This is rather low, but perhaps OK.
( report_port ) = 1 -- This is probably bad, but I have no experience to be more specific. Most people leave it at 3306.
Com_create_db every 4 minutes and Com_drop_db every 2 minutes -- Very high; what's going on?
Com_optimize = 4.9/hour -- OPTIMIZE is virtually useless, especially on InnoDB.