Vlad Fedorkov

Performance consulting for MySQL and Sphinx

Why is stock MySQL slow?

“I’ve installed MySQL and it doesn’t work fast enough for me”. MySQL server is heart of database driven application (if it uses MySQL as database of course!) and any slowness related to running queries is affecting all application layers.

MySQL server tuning and query slowness hunting are always step by step process and without knowing all the data (SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS, SHOW TABLE STATUS LIKE ‘tablename’, EXPLAIN details for slow query is just some of the required information) it would be generally a blind guess. But there are still few things which is related to newly installed MySQL server.

If you are using stock MySQL you might need to check memory pool size which MySQL used to load index data to avoid slow IO requests and increase queries speed. Connect to MySQL and fire two queries:

SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Default values are very small. If you see something less than 128MB (8Mb for older versions) that might be an issue. key_buffer_size used by MyISAM tables while innodb_buffer_pool_size required for InnoDB tables. I would recommend to set one of them to some relatively big value (generally from 25% to 80% of total memory on the box depends on the case).

How do you know which table type you are using? While connected to mysql please type:

SHOW CREATE TABLE 'table_name';

and find ENGINE=MyISAM or ENGINE=InnoDB on the last line. Increase key_buffer_size in my.cnf (/etc/my.cnf or /etc/mysql/my.cnf) if you see MyISAM or innodb_buffer_pool_size for InnoDB and restart MySQL.

Have fun!

Category: Performance

Your email address will not be published. Required fields are marked *