How To Optimizing MySQL server

Posted by cikul | Posted in Internet, Linux, web | Posted on 20-04-2009-05-2008

11

mysqlFor the web server that using mysql as a database engine. Sometimes the process in mysql need a large resource, especially if server has a high traffic. According to my experience, to the high traffic server, the MySQL process itself can spend more than 30% of server resources. So, I will give you tips for optimizing mysql on the server that has high traffic.

Mysql configuration located in the my.cnf file, usually this file is in the /etc directory, but can also in other folders. For more safe, you can search my.cnf file to make sure the location of the file.

 locate my.cnf

Once you found location of my.cnf file, edit the file with the editor that you like, and enter the configuration as below :

Note: Before you edit the my.cnf file, do the backup of the file first. so if new configuration you create fails, you can rollback to the old configuration. Some configuration have notes, you have to adjust the specifications of your servers .

[mysqld]
set-variable = max_connections=400
safe-show-database
local-infile=0
datadir=/var/lib/mysql
skip-locking
skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=64M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=256M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1200
record_buffer=1M
sort_buffer_size=2M ## 1MB for every 1GB of RAM
read_buffer_size=2M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=2M  ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

After you save the configuration, please restart your mysql, try and taste the difference :) You can also use the free tool from http://www.day32.com/MySQL/ to obtain additional recommendations for setting up your mysql.

Have a nice try.. :)

Update :

For Cpanel Server, you can get cpanel optimization tips by running this script :

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

you can get recommendation to optimize your mysql server like this example :

——– Recommendations —————————————————–

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Adjust your join queries to always utilize indexes

When making adjustments, make tmp_table_size/max_heap_table_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Set thread_cache_size to 4 as a starting value

Increase table_cache gradually to avoid file descriptor limits

Your applications are not closing MySQL connections properly

Variables to adjust:

query_cache_size (>= 8M)

join_buffer_size (> 128.0K, or always use indexes with joins)

tmp_table_size (> 32M)

max_heap_table_size (> 16M)

thread_cache_size (start at 4)

table_cache (> 64)

and you can edit your /etc/my.cnf using the recommendations.

Next, you can optimize all databases with this command:

mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

You can Learn the basis for fine-tuning MySQL performance on your cPanel-based server via logging, monitoring and utilizing MySQL performance tools. Key highlights include: understanding the most importantly used variables for MySQL configuration settings, and benchmark testing with different architectures from this videos :

You can get PDF of the presentation here

 

Incoming search terms:

optimize mysql server | your applications are not closing mysql connections properly | how to optimize mysql server | optimize mysql | 

Share and Enjoy

Comments posted (11)

Thanks for the tip. We’ve optimized all our servers in the datacentre using your tips.

Robert Punani,
Google Inc.

[…] Source : dhika.cikul.or.id […]

Have you tried scripting the database?. Doing so will create a text file with the necessary SQL needed to create the database, tables, keys, etc.

For optimization of Windows Server 2003, using fewer performance monitor counters can clear up some server resources, as the server’s overall performance is a bit slow

Nice info on MySQL configurations. I also use the feature of scripting the database so that I get plain text files which I can backup and keep safe easily!

[…] system setting. Now this one should be handled by DBA, and this link provides the answer (hopefully). Double […]

Good review about performance. Also you can find helpfull mysql tips from my site.

Thank you

[…] system setting. Now this one should be handled by DBA, and this link provides the answer (hopefully). Double Yay… If you enjoyed this article, please […]

[…] system setting. Now this one should be handled by DBA, and this link provides the answer (hopefully). Double Yay… No Comments Season […]

Great post on MySQL configurations. I’ve been trying to implement the feature for scripting the database to get plain text files that I can easily backup and keep safe. I found a lot of useful tips here :)

I’ve always found the MySQL databases to be quite hard to install on my websites. So I appreciate your tips.

Write a comment

*