6

I have 300 databases with a maximum of 59 tables each. My hosting provider says I have a lot of opened tables. I am not having any performance issues; but it is causing issues with backup (I use rackspace's cloud databases which uses Percona XtraBackup)

What can cause a lot of open tables and how can I decrease this? I don't get too much traffic and performance is good. I am not sure I understand exactly what opened tables is either.

I do have table_definition_cache set to 4096 as I wasn't able to run queries on views until I increased it.

mysql> SHOW GLOBAL STATUS LIKE 'Open_%';
mysql> +--------------------------+----------+
    -> | Variable_name            | Value    |
    -> +--------------------------+----------+
    -> | Open_files               | 7        |
    -> | Open_streams             | 0        |
    -> | Open_table_definitions   | 4102     |
    -> | Open_tables              | 4096     |
    -> | Opened_files             | 44025318 |
    -> | Opened_table_definitions | 1660409  |
    -> | Opened_tables            | 1857375  |
    -> +--------------------------+----------+

Here are the commands requested:

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984

6

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

Observations: Version: 5.6.17-log; 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.

  • 4GB of ram. I posted those 2 commands (gist.github.com/blasto333/aa4241a4e37447961188356719ea6984). My host reviewed it and made the open file limit 70,000 which it was 32,000 before. They said they don't see any performance issues; but I guess the backup process is pretty resource intensive (done on slave server). Tables are NOT partitioned. All innodb databases with 2 different types of schemas (1 or the other) – Chris Muench Mar 31 '16 at 23:13
  • I added the analysis to my answer. Buried in the middle is an answer to your original question about Opened_tables. (I needed many numbers in order to formulate a clear answer.) – Rick James Apr 1 '16 at 1:51
1

If you're using MyISAM, convert to InnoDB and you might see some drop in this counts. There is no other way to reduce this unless you stop querying them.

Increase the table_open_cache and table_definition_cache & observe the stats...Raise this as much as you can! May be you need to increase system file handlers too and that is a way to go.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.