What do "key buffer size" and "total MyISAM indexes" unit sizes mean?

Question

I ran MySQLTuner on my database to check if things are working correctly and optimally, but I was confused as to the units used for these two values.

Here is what the output looks like for performance metrics

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 5h 11m 20s (47M q [249.471 qps], 45M conn, TX: 65B, RX: 8B)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 1.1G global + 2.7M per thread (300 max threads)
[!!] Maximum possible memory usage: 1.9G (198% of installed RAM)
[OK] Slow queries: 0% (43/47M)
[OK] Highest usage of available connections: 5% (17/300)
[OK] Key buffer size / total MyISAM indexes: 600.0M/128.0K
[OK] Key buffer hit rate: 100.0% (17K cached / 0 reads)
[OK] Query cache efficiency: 98.1% (46M cached / 47M selects)
[!!] Query cache prunes per day: 22728
[OK] Sorts requiring temporary tables: 0% (9 temp sorts / 65K sorts)
[!!] Joins performed without indexes: 2839
[OK] Temporary tables created on disk: 0% (235 on disk / 42K total)
[OK] Thread cache hit rate: 99% (79 created / 45M connections)
[!!] Table cache hit rate: 1% (400 open / 27K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[!!] Connections aborted: 99%
[!!] InnoDB data size / buffer pool: 6.5G/128.0M

But my question is about this line

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

I dont know what the units mean, or how different they are in size. What is 'M' and 'K'

I know my key buffer size should be bigger than MyISAM indexes but I cant tell if it is because I dont understand the units. I've lloked online but couldn't find the answers I was looking for. If someone could please clarify this for me I would really appreciate it.

Do you mean this [MySQLTuner](https://github.com/major/MySQLTuner-perl)? — surfmuggle, Jun 30 '21 at 13:32

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Are you sure you looked into the documentation. Quoting From MySQL Documentation:

To minimize disk I/O, the MyISAM storage engine employs a cache mechanism to keep the most frequently accessed table blocks in memory:

For index blocks, a special structure called the key cache (or key buffer) is maintained.

To control the size of the key cache, use the key_buffer_size system variable.

If not wrong those are size unit; Here M stands for Mega Bytes and K stands for Kilo Bytes.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 17 '15 at 14:40

Rahul

76,197
13
71
125

I think you misread my question. I'm looking for what the M and K mean but someone on serverfault answered it. "M is Megabytes, K is Kilobytes". it sounds obvious and actually I thought that was the case but I wanted to be sure before I made changes to the database. But thank you for your input all the same. I do need to read over the MSQL Document. – Joe Lloyd Jun 17 '15 at 14:59
1

Ok, it's like `600.0M/128.0K` means 128K used whereas max is set to 600M. – Rahul Jun 17 '15 at 15:03

score 1 · Answer 2 · answered Jul 04 '15 at 02:56

1

The total size of the indexes (.MYI files) for all your MyISAM tables is 128KB (kilobytes). Hence key_buffer_size = 600M (megabytes) is overkill.

answered Jul 04 '15 at 02:56

Rick James

135,179
13
127
222

What do "key buffer size" and "total MyISAM indexes" unit sizes mean?

2 Answers2