Sutiable value for key_buffer_size

What is the suitable value for key_buffer_size?

On my server, the value for key_buffer_size is 2097152000. This value is in kilobytes or bytes?

The current value on my server is

mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 2097152000 |
+-----------------+------------+

We have 80+ MyISAM tables but most frequently used tables are around 40.

Server specs are
Operating System : Linux - CentOS 3
Processor : Dual Xeon 3 GHZ Hyperthreaded
Memory : 8 GB DDR Registered ECC

The value is bytes.

What is the total size of your myisam indexes?

How can I calculate the index size?

Try:
SHOW TABLE STATUS WHERE Engine=‘MyISAM’

Then add up the “Index_length” column.

Index length is
Index_length: 88792064

Right, so your total index size is 85 Mb.

Depending on the nature of your database, e.g., do you expect a lot more information to be inserted? I would suggest and key_buffer size of atleast 128Mb

Currently your key_buffer size is around 2GB.

Might be advisable to lower that to free more RAM for the full system.

Anything greater than 90Mb is suitable, but I’d recommend 256Mb.

Thanks Seeple,
Sorry, the index size I told you is for only one table:$

Should I consider the biggest index of any table when setting key buffer size or add all tables index size?

The key buffer is a range of memory allocated for ALL myisam table indexes, so you’ll have to add up ALL the indexes :slight_smile:

MyISAM tables index size is 3793090560 (3617 MB).
Is it ok to have 2GB key buffer size for this much index size?

having enough memory configured for all your indexes is a good idea, but often not a practical goal. if you have 3 GB of indexes, either you have WAY too many indexes defined, or you have like 30 GB of data.

if you have that much data, then there is probably at least one table where not all of the rows are continually used. in that case, figure out the fraction of rows that are actually used, apply that fraction to the index for that table and add 15%. that’s how much key buffer you should allocate to that table.

FYI- the only way to allocate more than 2 GB of memory to mysql would be to recompile your linux kernel with 3GB/1GB user/kernel split. but since you’re using myisam and not innodb, this would not be a good idea because that would leave no memory for the kernel to do disk caching. (myisam depends on the kernel for caching; innodb does its own disk caching)

gyro, what data types are you indexing?

Non-numerical indexes aren’t compressed, however you can set the option PACK KEYS to compress indexes on chars etc.

On CHAR type indexes you can also limit the index length. The optimum index length is AVG(LENGTH(column_name)).

Also, read up on redundant indexes: