September 17, 2014

Getting annoyed with MyISAM multiple key caches.

As I’ve wrote few times using multiple key caches is a great way to get CPU scalability if you’re using MyISAM. It is however very annoying – this feature really looks half baked to me.

The problem with multiple key caches and mapping of tables to the different files is – there is no way to see the existing key caches, their mapping and stats. The only thing you can access is key cache parameters – via structured variables

In particular I would like to:

See the list of created caches Right now I can create key caches with random names causing invisible resource consumption. It is possible to make an error in key cache creation but it is not possible to later find out such key cache exists. This in my opinion violates fundamental design principle – if you can create something you should be able to also list what you have created.

See the mappings I can now map tables to be cached in different key caches but there is no way to see the current mappings or to see where each given table is cached.

See other key cache stats To tune the caches I need to see number of accesses, blocks used, number of misses. Such basic feature either does not exist or is not documented.

I’m not sure how relevant is MyISAM with MariaDB on a way but this is surely sad to see the feature of multiple key caches first introduced in MySQL 4.1 was newer quite polished in terms of usability.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Mark Callaghan says:

    Is Maria still expected to replace MyISAM? It was at one point and that was justification for not fixing performance problems in MyISAM (problems including mutex contention).

  2. Agreed 100%. It feels like the multiple key cache support was added for someone who desperately needed it and didn’t care about the administrative side at all.

  3. Matthew Montgomery says:
  4. Harrison Fisk says:

    You can see most of this via mysqladmin debug in the error log (except the mappings), so you can know the sizes and tuning and such. I think it would be nice to have an I_S table or similar, but that is true for several things with MyISAM, such as locks.

    For example:

    Key caches:
    default
    Buffer_size: 314572800
    Block_size: 1024
    Division_limit: 100
    Age_limit: 300
    blocks used: 3
    not flushed: 0
    w_requests: 0
    writes: 0
    r_requests: 6
    reads: 3

    keycache1
    Buffer_size: 131072
    Block_size: 1024
    Division_limit: 100
    Age_limit: 300
    blocks used: 0
    not flushed: 0
    w_requests: 0
    writes: 0
    r_requests: 0
    reads: 0

  5. peter says:

    Harrison,

    Thanks – indeed this way I can see the key caches in the error log, though it does not make it easily accessible from the applications. You’re right – locks held is another issue where MyISAM is lacking.

  6. peter says:

    Matthew,

    Yes this can be implemented as a plugin. I however does not like this plugin obsession. I would like to see the server have features which I need in its default installation (though I do not really care if this is linked in stuff or plugins) – just having plugin available somewhere is helpful for your own installation but if you’re dealing with customer systems it is a lot of hassle – you well need feature when you do not want to restart the server on the first place. Second adding third party plugins may require internal approval and QA process. MyISAM is compiled in so I think such little things should be too.

  7. Shane Bester says:

    should never use ‘mysqladmin debug’ on a server that is even moderately loaded. it tends to crash very often… there are a few bug reports about it already.

  8. Steven Roussey says:

    Multiple key caches are great when you know what your problem is and how extra caches can be utilized. Also handy for a server that is also a slave of another, since you can limit the cache dilution of the slave process.

  9. peter says:

    Steven,

    Right. Though because you have to map whole indexes it is not so easy – the slave thread vs reads would often touch same tables just different pages.

    I find in most useful to deal with MyISAM contention.

  10. Nate Pierce says:

    After seeing the wafflegrid presentation at the conference this year, I am kicking around the idea of trying to replace and/or augment the myisam keycache with memcached. Due to resources, we’re still running on 4.0, so this would get us past the single 4G global keycache barrier w/out having to wade through upgrade issues and buy us some time. I’m not intimately familiar with either codebase, but it looks like it may be possible. Anyone have any thoughts on the matter?

  11. Claudio says:

    Single Mutex really sucks, but multiple Key Caches help a bit, and helped me in a very bad mysql deadlock issue.
    I created ten tailored key caches for the ten most high concurrent tables and at 48 hours later it seems that the cpu peaks (up to 120 on 16 cores) disappeared. Still the problem is on the concurrent access to the same table by multiple threads at once. Anybody has suspects on the OS as well? I have’em on FreeBSD scheduler. On which OS did you guys experienced this?
    Claudio

Speak Your Mind

*