Dhika CikulJust Want to Learn and Share

For 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|  how to optimize mysql server|  optimize mysql|  how to optimize mysql|  optimizing mysql server|  optimizing mysql|  mysql tuner cpanel|  mysqltuner cpanel|  mysql server optimize|  my cnf high traffic|