Life by the park

How to make MySQL blazingly fast

1) Increase innodb_buffer_pool_size to 80% of memory of dedicated host. At least give it 265M. Here under is a snippet from mysql.ini

set-variable=innodb_buffer_pool_size=256M
set-variable=innodb_additional_mem_pool_size=20M

Without any further optimization it gives you 50% performance gain (against old setting of 32Mb)
2) Increase innodb_log_file_size to 25% of innodb_buffer_pool_size

set-variable=innodb_log_file_size=64M
set-variable=innodb_log_buffer_size=8M

Packaged with optimization above it can shave you up to additional 10%
3) Make sure that for read-only transactions your switched ON auto commit setting. And then switched it back for read-write tasks. Set read-only settings properly to JDBC statement as well.
Here you will see drastic improvement if you treated read-only transaction wrongly i.e. committed and rolled back them yourself from DAO
4) Make sure that you execute operation with DB concurrently. I was able to get 30% improvement when running 8 threads. There is a catch here though – by default internally MySQL holds 8 threads to serve DB requests. Make number of threads here and there consistent.
5) Change isolation level to READ-UNCOMMITTED if it possible (default for InnoDB is REPEATABLE READ)

set-variable=transaction-isolation=READ-UNCOMMITTED

My tests show that gain here is almost negligible around 4%

Похожие записи:

• Work: MySQL config
• MySQL INSERT: the power of cunning mind
• Work: SQL Relay
• unicode
• Java: Fuzzy String Matching