Is It key_buffer or key_buffer_size?

Question

I search the internet regarding the correct variable in my.cnf file. Some said that key_buffer_size is deprecated, but some said that key_buffer_size is the correct variable in my.cnf.

So, what is really the correct variable here? Is it key_buffer or key_buffer_size?

I'm using Ubuntu 12.04.

And also I have the two key_buffer variable in my.cnf file. This is what I got after installing MySQL.

The first one is located under this:

[mysqld]
key_buffer              = 16M

The other one is located under this:

[isamchk]
key_buffer              = 16M

Solution

I don't think key_buffer_size is deprecated, mysql use key_buffer_size in the documentation on their website from the earliest available right up to the latest version. There is also a bug report that requests deprecated variables emit warnings at startup which suggests that it's key_buffer that is deprecated.

I personally would go with the documentation as it should be authoritative and whilst the internet is full of useful information it's also full of misinformation.

---

Having said that it seems like mysql goes out of it's way to match variable names you provide to it's variables and will do so as long as the name you provide is unique

With key_buffer_size = 16m

mysql> show variables like '%key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)

Change to key_buffer = 6m

mysql> show variables like '%key_buffer%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 6291456 |
+-----------------+---------+
1 row in set (0.00 sec)

Change to key_b =16m

mysql> show variables like '%key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)

Change to key_ = 16m and mysql fails to start as key_ isn't unique.

Comments

[jaYPabs]

Thank you. I'm just wondering because after installing MySQL, the variable is not key_buffer_size, instead it is key_buffer only.

[user9517]

@user176890: Yes, I noticed that too. If you're really curious you should ask Canonical who maintain and update Ubuntu.

[Giacomo1968]

The key tip here—which Lain beat me to—was to use SHOW VARIABLE LIKE '%key_buffer%'; I get confused about MySQL naming conventions all of the time and SHOW VARIABLES is a life-saver.

[user9517]

Beat you to by 6 months @JakeGould

[Giacomo1968]

@Iain Holy cr@p! Old post stung me again.

[aldemarcalazans]

Recently, I had a suggestive experience replacing MariaDB by MySQL 5.7, in Xampp for Windows, where MySQL 5.7 initially did not started. The reason was two directives that were removed in MySQL 5.7: 'key_buffer' (I had to replace it by key_buffer_size) and 'innodb_additional_mem_pool_size' (I had to delete it from the configuration). So, even if your installation is working with key_buffer, it is advised to replace it by key_buffer_size.