Configuring the MyISAM segmented key cache

We can dramatically improve the performance of our MyISAM tables by splitting the key cache into multiple segments. This is useful if we have high concurrency in our database usage (meaning there are lots of threads trying to access the key cache).

How to do it...

1. Launch the mysql command-line client application and connect to our MariaDB server as the root user or as a user with the SUPER privilege.

2. View the current number of segments with the following command:

   SHOW VARIABLES LIKE 'key_cache_segments'\G
   

3. Set the number of segments to 64 with the following command:

   SET GLOBAL key_cache_segments = 64;
   

4. Make the setting permanent by adding the following lines of code to the end of our my.cnf or my.ini file (or to an existing [mysqld] section):

   [mysqld]
   key_cache_segments = 64

How it works...

Whenever a MyISAM thread accesses the key cache, it needs to first acquire a lock. Lots of threads trying to get a lock on a single, monolithic key cache is a big choke...