troubleshooting Question

How do I know my init-file=/var/lib/mysql/server.init statement is working?

Avatar of Robert Silver
Robert SilverFlag for United States of America asked on
MySQL Server
2 Comments1 Solution413 ViewsLast Modified:
The following statements were used to improve performance by loading the keys into memory cache or so I believe:


set GLOBAL Country_cache.key_buffer_size=6291456;
CACHE INDEX world.Country in Country_cache;
LOAD INDEX INTO CACHE world.Country;

set GLOBAL City_cache.key_buffer_size=629145600;
CACHE INDEX world.City in City_cache;
LOAD INDEX INTO CACHE world.City;

These statements appear to be working interactively but how can I verify the status
of these caches ????

Also The statements:
key_buffer_size=4G
hot_cache.key_buffer_size=2G
cold_cache.key_buffer_size=2G

Allocation of these buffers - how do they relate to to the first statements about the
caching of table indexes/keys ?
I mean is there a book /document source that fully explains both MyISAM caching and Innodb caching?

It would really help if there was a way to verify this
e.g
show variables like '%cache%'    # does not show any such cache information
                                                             # e.g cache of

or better yet:
Show Table caches
show index caches;

I tried this:

mysql(world)show GLOBAL variables like '%cache%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| binlog_cache_size            | 32768      |
| have_query_cache             | YES        |
| key_cache_age_threshold      | 300        |
| key_cache_block_size         | 1024       |
| key_cache_division_limit     | 100        |
| max_binlog_cache_size        | 4294963200 |
| ndb_cache_check_time         | 0          |
| ndb_index_stat_cache_entries | 32         |
| query_cache_limit            | 1048576    |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 0          |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
| table_definition_cache       | 256        |
| table_open_cache             | 64         |
| thread_cache_size            | 0          |
+------------------------------+------------+
16 rows in set (0.00 sec)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros