Link to home
Start Free TrialLog in
Avatar of Robert Silver
Robert SilverFlag for United States of America

asked on

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

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)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of theGhost_k8
theGhost_k8
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Silver

ASKER

Thank you so much.
One more thing Query_cache or Qcache  does that also only work with MyISAM or does that
work with all Storage Engines since it deals with parsing of text strings I would think
it applies to all storage engines. Am I right?