MyISAM Performance – Settings to Keep Everything in Memory

myisamMySQLmysql-5performance

I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage of my server's resources?

What settings should I attempt configure in my.cnf?
Can I force data + indexes to stay in memory?

Best Answer

MySQL only caches the indexes in MySQL's memory, not the data. The data is only cached by the underlying operating system's filesystem caches. The most important value to tune in my.cnf is key_buffer_size which will allocate memory for MyISAM to cache indexes.

However, unless you have some specific reason for using MyISAM tables you should convert those tables to InnoDB. In this day and age, there are less than a handful of reasons to use use MyISAM and MyISAM is not good for concurrency, data integrity, or crash recovery.

If you switch to InnoDB, the most important setting is innodb_buffer_pool_size which is the internal memory cache that will cache data pages & indexes. Other things to consider are the innodb_log_file_size and make sure to set innodb_file_per_table=1. With 4GB of data+indexes, setting innodb_buffer_pool_size=4G is reasonable. If you are running in a highly concurrent environment, you could experiment with values for innodb_thread_concurrency. My recommendation is to start with innodb_thread_concurrency=0, but you might find it necessary to explicitly set it to be somewhere between 4-8 depending on your workload and version of MySQL.

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

MySQL over total memory allocated usage? memory leak

i have found out why memory leak happeneds. now i have only 1GB memory and no swap.

the performance_schema cause the allocate the memory for about 400M at startup. and innodb will take up to 400M memory usage

so，because of my.cnf use

table-definition-cache= 1024
table-open-cache= 2048 
max-connections= 60

it will cause high performance_schema allocated, more information pls see

ref1

ref2

Best Answer

Related Solutions

MySQL – Optimal Configs for ALTER of Large MyISAM Table to InnoDB

RECOMMENDATION #1

RECOMMENDATION #2

MySQL over total memory allocated usage? memory leak

Related Question