Key buffer size optimisation advise

[Richard G]

I ran mysql tuner and amongst a few other things I got this:

-------- MyISAM Metrics ---------------------------------------
[!!] Key buffer used: 29.7% (38.0M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/113.2M
[OK] Read Key buffer hit rate: 99.3% (1M cached / 14K reads)
[!!] Write Key buffer hit rate: 59.3% (142K cached / 84K writes)

And the advise was the following:
key_buffer_size (~ 39M)

Shout this mean I should lower the key_buffer_size? I had a look in mariadb 10.6 with this command:
SHOW GLOBAL VARIABLES LIKE 'key_buffer_size';
and the result was:
| key_buffer_size | 134217728 |
Seems this is the new default on MariaDB.

So am I correct that this would mean MariaDB is using 134 M and this is way too much? I better use something like 64M or what should I do?

 

[BillyS]

What this is telling you is you have set aside 128.0M for the key buffer cache. You are only using 38.0M / 128.0M or 29.7% of the cache. Your key buffer is set to 128.0M. The recommendation is to lower it to 39.0M because that is greater than the 38.0M you are using. Personally, I would move it to something like 48 or 64 and keep an eye on it since you do have MyISAM storage. In fact, your total MyISAM indexes are 113.2M.

I'm actually wondering if you set the key buffer size to a value less than 113.2M (like 39, 48, or 64) if that [OK] turns into a [!!] for this row

[OK] Key buffer size / total MyISAM indexes: 128.0M/113.2M

Overall this isnt a big deal unless the server doesnt have a lot of memory. Moving from 128M to 64M only saves you 64M of memory.

 

[johannes]

1) never change a running system
2) if possible convert to InnoDB and circumvent this value at all
3) this:

Personally, I would move it to something like 48 or 64 and keep an eye on it since you do have MyISAM storage.

We have set 32mb with a little bit fewer customers, and its fine. 48-64 is sure nice, but also no harm if set higher. Zhenyapan even postet 8M in 2022, somewhere here in the board.
4) And .. maybe not for MyIsam, but for InnoDb - have a look at the growing logfiles, if activated, and set a limit, ex. 256MB. If logfiles becoming to big, they slowering the DB.

 

[Stije]

Do you use MyISAM at all? Else you can just make it small.

IMHO, Mysql tuner gives most of the time info for a database only server. Not considering you also have other services. I wouldn't give to much attention to this. Mostly the standard settings of MariaDB are good enough. When you do some tuning with user connections, maybe buffer size and max allowed packet etc. But this script gives a full blown setup which is not really good. You can maybe check some of the info. Or use mysql primer for more info but i wouldn't user the settings as given by these scripts. Check your stats and see what is making things maybe slower and do some gentle changes.

Also good stuff here for some tuning tips.

Configuring MariaDB for Optimal Performance

How to get optimal performance.

mariadb.com

 

[BillyS]

Do you use MyISAM at all?

Pretty sure this answers that question

[!!] Key buffer used: 29.7% (38.0M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/113.2M

 

[Zhenyapan]

I'm usually checking with mysqltuner servers once per 2-3 months. In my experience average Key buffer used: about 40-60% of total MyISAM indexes too (as on your report). So having a lot of RAM I keep it about 75% of total MyISAM indexes. But in my case it about 5-6G, with such small like 128M you can keep it as it is. Cutting this 30-50M will not help you (if you have 4G+ RAM)

 

[Richard G]

Lot of answers, but most important to me is that I can keep it as it is. We got 64 GB Ram in our system so that should not be any issue then.

1) never change a running system

Normally not. But I've discovered this one to have a lot of high iowait statusses. I was wondering where that came from.
So I ran the mysql tuner previous month and changed setting in the my.cnf file, if I'm correct it was:
innodb_buffer_pool_size=2048M
and after that the iowaits dropped very much. They are low now while on other servers I've got 0 or almost 0 so that's why I was looking further.

At this moment I added:
table_definition_cache=2048
and changed logfile size to 512M.

Current /etc/my.cnf on that server:

[mysqld]
max_allowed_packet=64M
local-infile=0
bind-address=127.0.0.1
skip-name-resolve=1

sql_mode=
innodb_file_per_table
table_definition_cache=2048
innodb_buffer_pool_size=2048M
innodb_log_file_size=512M

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysql/mysqld.log
slow_query_log
slow_query_log_file=/var/log/mysql/mariadb-slow.log

[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

2) if possible convert to InnoDB and circumvent this value at all

Is it better to convert all to InnoDB and if yes, how is that done?
Edit: I already asked how before and discovered not every app was happy with that, so I will keep MyIsam for now I guess. Or maybe now that's different, but I'm a bit scared to do this.

have a look at the growing logfiles, if activated, and set a limit, ex. 256MB

I didn't have a limit before, but now set it to 512. If I'm not mistaken it was smaller before, I thin 256M but the mysqltuner advised to set it to 25% of the cache of buffer pool size (don't remember which one), best would be 512M so I changed it to that.

At this moment the iowaits on that server are around 1%.

 

[Richard G]

I now used a newer version of mysqltuner (2.5.3, previous was 2.0.5 or something).

This is the result:

General recommendations:
    Remove Anonymous User accounts: there are 2 anonymous accounts.
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
    Performance schema should be activated for better diagnostics
    MyISAM engine is deprecated, consider migrating to InnoDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    performance_schema=ON
    key_buffer_size (~ 26M)
    innodb_log_buffer_size (> 16M)

But I read elsewhere I better could keep my hands of the join_buffer_size so I didn't change that ever. Looking good now?

 

[Richard G]

Another server has this line:

Reduce your SELECT DISTINCT queries which have no LIMIT clause

How is that fixed the best way?

 

[zEitEr]

How is that fixed the best way?

Patch your PHP applications. That's the only possible way, I believe.

 

[Richard G]

Patch your PHP applications.

Thank you Alex.
So this is from applications? So from users not from the server? As I have all php-fpm configured the same on all servers. Most users are using Wordpress, not really something special.
So probably nothing to really worry about?