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)
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 ?