First, let's consider those status variables:
Open tables : The number of tables that are open.
Opened_tables : The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.
Surprisingly, the answer to your question lies within the question itself.
The two variables would only make more sense if you throw one more status variable into the mix : Uptime (or Uptime_since_flush status for fresh averages after FLUSH STATUS).
You should be comparing Open_tables agsinst (Opened_tables / Uptime). If Open_tables climbs above (Opened_tables / Uptime), now you have cause for concern and should keep an eye open for things like the following:
UPDATE 2011-08-31 12:18 EDT
Please note why I also suggested using Uptime_since_flush_status instead of Uptime to get a fix Opened_tables pattern of growth for a given period.
For example, if you run FLUSH STATUS;
every Monday at midnight, you can generate an OpenTableFactor:
SELECT *, (Open_tables * Uptime / Opened_Tables) OpenTableFactor FROM
(SELECT variable_value Uptime FROM information_schema.global_status
WHERE variable_name = 'Uptime_since_flush_status') up,
(SELECT variable_value Open_tables FROM information_schema.global_status
WHERE variable_name = 'Open_tables') opn,
(SELECT IF(variable_value=0,1,variable_value) Opened_tables
FROM information_schema.global_status
WHERE variable_name = 'Opened_tables') opnd;
This open table factor amounts to the number that represent the number of open tables at any given moment against the average number of opened tables throughtout a given period. With a FLUSH HOSTS;
every week/day/host, that average is against the week/day/hour.
Here is a sample out from one of my employer's clients:
mysql> SELECT *, (Open_tables * Uptime / Opened_Tables) OpenTableFactor FROM (SELECT variable_value Uptime FROM information_sc hema.global_status WHERE variable_name = 'Uptime_since_flush_status') up, (SELECT variable_value Open_tables FROM informat ion_schema.global_status WHERE variable_name = 'Open_tables') opn, (SELECT IF(variable_value=0,1,variable_value) Opened_ta bles FROM information_schema.global_status WHERE variable_name = 'Opened_tables') opnd;
+----------+-------------+---------------+-------------------+
| Uptime | Open_tables | Opened_tables | OpenTableFactor |
+----------+-------------+---------------+-------------------+
| 14385123 | 16326 | 30429078 | 7717.996519579068 |
+----------+-------------+---------------+-------------------+
1 row in set (0.00 sec)
This client normally maintains about 7745 OpenTableFactor at max. If OpenTableFactor drops suddenly (even if a little), it could indicate lower traffic patterns, high aborted conenctions, and so forth. If OpenTableFactor never changes (even if a little), it could present you with an opportunity to change these settings:
Once adjusted, the OpenTableFactor may change constantly or hit another ceiling or plateau. Thus, using different units within the status variables becomes vital for this kind of tuning.
UPDATE 2011-08-31 12:42 EDT
The SQL query I ran for the OpenTableFactor does not works for MySQL 5.0 and back. If you are using MySQL Administrator or MONyog, you could customize a graph using the formula in the query and monitor. MONyog collects the history using SQLLite for later historical graphing. This can be done for any version of MySQL.