Category: mysql

Tuning mysql performance

Author: seven July 5, 2006

The operating system your MySQL server runs on and the server's configuration can be just as important to your server's performance as the indexes (let EXPLAIN be your best friend), schema, or queries themselves. Be shure to check out High Performance MySQL - Server Performance Tuning from mySQL AB. In case you are stuck with badly performing cluster, check out MySQL Clustering - Performance.
The tool which will come into very handy is mysqlreport. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides a much more in-depth understanding of how well MySQL is running.

my.cnf

[mysqld]
back_log = 75
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Author
seven
CEO/CTO at Nivas®
Neven Jacmenović has been passionately involved with computers since late 80s, the age of Atari and Commodore Amiga. As one of internet industry pioneers in Croatia, since 90s, he has been involved in making of many award winning, innovative and successful online project. He is experienced full stack web developer, analyst and system engineer. In his spare time, Neven is transforming retro-futuristic passion into various golang, Adobe Flash and JavaScript/WebGL projects.

    Leave a Reply

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