Mysql – mariadb / thesql wont change table_cache / table_open_cache

mariadbMySQL

I've configured my i7 64gb ssd server with centos7 with the following my.cnf

[mysqld]
key_buffer = 12M
max_allowed_packet = 16M
table_cache = 4096
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size = 48M
read_buffer_size = 32M
read_rnd_buffer_size = 104M
myisam_sort_buffer_size = 100M
myisam_max_sort_file_size = 1024m
#myisam_max_extra_sort_file_size = 1024m
myisam_repair_threads = 1
thread_cache_size = 32
query_cache_type = 1
query_cache_size = 512M
query_cache_limit = 8M
max_connections=50
wait_timeout=60
tmp_table_size = 256M
max_heap_table_size = 64M
#innodb_io_capacity = 400
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 24M

But when i run mysql_tuner.pl
i still get

TABLE CACHE
Current table_open_cache = 482 tables
Current table_definition_cache = 4096 tables
You have a total of 1027 tables
You have 482 open tables.
Current table_cache hit rate is 15%
, while 100% of your table cache is in use
You should probably increase your table_cache

Of course i restarted my mariadb instance. but i have no clue.
Search turned up empty as well.

Hope someone could help out Cheers.

Best Answer

The operating system has a low limit. In Unix, see ulimit -n, and change it. Some versions have an unnecessarily low limit of 1024.

Most, but not all, things set by my.cnf (my.ini) stay set. table_open_cache is one that is changed based on the environment. Use SHOW VARIABLES to see the real values. Or SHOW VARIABLES LIKE 'table_open_cache' to see the one setting.

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.