1

In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY).

Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.

  • Which version of MySQL? It might not be safe in old versions. – ypercubeᵀᴹ Jun 10 '19 at 16:51
1

In the MySQL Docs for 5.5, 5.6, 5.7, and 8.0 the minimum value is 8.

If you set it to 0 in my.cnf and restart mysqld, it may go to 8 anyway.

Since key_buffer_size is dynamic, login to mysql and run

mysql> SET GLOBAL key_buffeer_size = 0;
mysql> SELECT @@global.key_buffer_size;

Whatever the value is, so be it. The storage engine layer will handle it.

Please note that MyISAM caches index pages only. It was probably simpler to make the default minimum a very low nonzero value of 8 (which is exactly a byte) rather than code logic to see if the key cache is enabled with enough RAM.

UPDATE 2019-06-10 13:16 EDT

root@vt-mysql:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@global.key_buffer_size;
+--------------------------+
| @@global.key_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+
1 row in set (0.05 sec)

mysql> set global key_buffer_size = 0;
ERROR 1438 (HY000): Cannot drop default keycache
mysql> set global key_buffer_size = 8;
ERROR 1438 (HY000): Cannot drop default keycache
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.33-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.05 sec)

mysql>

The documentation is wrong: You cannot resize the global buffer dynamically.

I added key_buffer_size = 0 and restarted mysqld. It ricocheted back to 16M.

I added key_buffer_size = 8 and restarted mysqld. It ricocheted back to 16M.

root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~#
root@vt-mysql:~#
root@vt-mysql:~#
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 0
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 1991
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 8
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 2112
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 2M
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 2234
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~#

I guess the only correct thing I said was : Whatever the value is, so be it. The storage engine layer will handle it.

| improve this answer | |
  • Interesting experiments 👍 – Marcus Jun 11 '19 at 15:49
1

Through 5.7, the tables for user names and permissions are stored in MyISAM tables. Hence it is best to have at least a small amount of cache for their indexes.

16M is probably sufficient for any situation.

Think of it this way -- 10M is what percentage of your RAM? So small as to be insignificant, correct?

8 is meaningless -- the key_buffer is a collection of 1KB index blocks. (As Rolando points out, that gets rounded up to something usable.)

Not every setting can be dynamically changed. 8.0 allows for virtually all settings to be changed. Meanwhile, 8.0 has all-but-eliminated MyISAM, so key_buffer_size is a moot point.

| improve this answer | |
  • Very interesting - and of course, good point the pragmatic observation about the memory proportion (this is more of a curiosity). Are there any tables that wouldn't be included in a mysqldump --all-databases? Based on mysqldump --all-databases | perl -ne 'print "$1\n" if /ENGINE=(\S+)/' | sort -u, v5.7 does indeed have MyISAM tables, but not v8.0. – Marcus Jun 11 '19 at 15:48
  • When dumping, it is unnecessary or unwise, to dump these databses: mysql, information_schema, performance_schema. – Rick James Jun 11 '19 at 16:40
  • The purpose of that dump is to find out if there are any MyISAM tables, so the question still stands - are there any (system) tables that wouldn't be included in a mysqldump --all-databases? – Marcus Jun 12 '19 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.