Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I searched google but couldnt find an exact answer, some have this value at 10000 or even 20000. Others say that even 1000 is too much for 1gb of RAM. Confusion.

My number of opened_tables is growing. Mysqltuner says I should raise the table_cache value, I tried to raise it to 2K, then 3K, then 4K, then I made it 512 to see what happens. Here is the report:

Currently running supported MySQL version 5.0.67-community-nt-log
Operating on 32-bit architecture with less than 2GB RAM
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 12M (Tables: 3)
Data in MEMORY tables: 380K (Tables: 2)
Data in MyISAM tables: 83M (Tables: 84)
Total fragmented tables: 16
All database users have passwords assigned
Up for: 16h 12m 55s (161K q [2.000 qps], 6K conn, TX: 281M, RX: 22M)
Reads / Writes: 69% / 31%
Total buffers: 128.0M global + 2.1M per thread (100 max threads)
Maximum possible memory usage: 334.3M (16% of installed RAM)
Slow queries: 1% (11/161K)
Highest usage of available connections: 10% (10/100)
Key buffer size / total MyISAM indexes: 32.0M/4.1M
Key buffer hit rate: 97% (849K cached / 20K reads)
Query cache efficiency: 28% (26K cached / 93K selects)
Query cache prunes per day: 0
Sorts requiring temporary tables: 1% (1 temp sorts / 21K sorts)
Temporary tables created on disk: 1% (2 on disk / 15K total)
Thread cache hit rate: 99% (10 created / 6K connections)
Table cache hit rate: 5% (139 open / 2K opened)
Open file limit used: 20% (232/1K)
Table locks acquired immediately: 99% (116K immediate / 116K locks)
InnoDB data size / buffer pool: 12.5M/32.0M
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
table_cache (> 512)
Scan Complete

mysql> show global STATUS LIKE 'open%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 222   |
| Open_streams  | 0     |
| Open_tables   | 127   |
| Opened_tables | 2335  |
+---------------+-------+
4 rows in set (0.00 sec)

But opened_tables is still growing, should I raise it again?? Or is not worth attention?

Also some people say that the problem is usually in creating a lot of temp tables, as we can see from the stats its not my case. Whats the ratio between table_cache and RAM ?

thanks

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

The reason you didn't find an answer is that there's not an answer. The appropriate value for table_cache doesn't correlate to an amount of system memory.

Note that table_cache was renamed table_open_cache in MySQL 5.1.3 and is referred to by the new name in newer versions of MySQL.

As I mentioned in my answer to this similar recent question, among the things that can increment opened_tables, some of them are both harmless and inevitable (such as making backups with mysqldump, which, in most cases, issues some variant of FLUSH TABLES, which increments the counters as each table is reopened after the flush, either for the purpose of backing up the table or by other client threads accessing the recently-flushed tables, which must naturally be re-opened).

What I didn't think to mention in that post is -- and I trust you will find some measure of amusement in this -- that the act of running mysqltuner against your server can, itself, increment the opened_tables counter, thus invalidating its own results.

A commenter on my answer referenced this article, where the author points out that tweaking table_cache worsens performance the more "optimally" you set it.

The bottom line is that this is a good example of a variable that is best left alone unless you have a specific reason to tweak it, and the output of a tuning script doesn't count. Tuning scripts are often well-intentioned but that is sometimes the best thing that can be said about them:

The problem with correlations that sometimes appear to be true is that people begin to believe they will always be true. Oracle DBAs abandoned ratio-based tuning years ago, and we wish MySQL DBAs would follow their lead.

We wish even more fervently that people wouldn’t write “tuning scripts” that codify these dangerous practices and teach them to thousands of people. This leads to our second suggestion of what not to do: don’t use tuning scripts! There are several very popular ones that you can find on the Internet. It’s probably best to ignore them.

We also suggest that you avoid the word “tuning,” which we’ve used liberally in the past few paragraphs. We favor “configuration” or “optimization” instead (as long as that’s what you’re actually doing; see Chapter 3). The word “tuning” conjures up images of an undisciplined novice who tweaks the server and sees what happens. We suggested in the previous section that this practice is best left to those who are researching server internals.

“Tuning” your server can be a stunning waste of time.

-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Locations 12173-12182). OReilly Media - A. Kindle Edition.

As a side note, MySQL 5.0 is end of life. MySQL 5.0.67 was released over 4 years ago and a significant number of bugs were fixed in the 5.0 release series since then. If I had a 5.0.67 server, and it was working, then the last thing I would want to do would be to change anything about its configuration.

share|improve this answer
    
so you suggesting to leave that variable at default value of 64 ? –  Beavis IsCool Jul 2 '13 at 12:19
    
Yes, unless you are attempting to solve a performance-related issue that you believe to be related to the value of this variable. And, if you are trying to resolve a performance-related issue, you should most likely see how your application performs on MySQL 5.6 rather than trying to tune 5.0. –  Michael - sqlbot Jul 2 '13 at 19:34
    
I see, I have no perfomance issues nor do I struggle to migrate to 5.6 version. Just trying out new things to me like that mysqltuner. Ok, I got your point. thanks for your answer –  Beavis IsCool Jul 2 '13 at 23:36
add comment

It could be because your application might be causing MySQL to create tmp tables if Open_tables value is well below the table_open_cache value. Creating tmp tables also increments Opened_tables counter. I run Mysqltuner script too and notice the table cache hit rate is below 50%. I was wondering why it does not make use of available free cache space, yet the number keeps increasing. The same book that is referenced by Michael explains it. High Performance MySQL: Optimization, Backups, and Replication

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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