Optimize MySql How to Optimize MySql

hrdedicated
Site Admin
Posts: 50
Joined: Thu May 30, 2013 10:28 pm

Optimize MySql How to Optimize MySql

Postby hrdedicated » Sat Jun 01, 2013 11:54 am

There any many things comes when you start thinking of optimizing your mysql server, Here is the standered Optimization of your Mysql server

Login into your server with the root user
first take your /etc/my.cnf files backup so that any point you can revert back to old stage
command

cp -p /etc/my.cnf /etc/my.cnf_ORG




Now open the /etc/my.cnf file which is your mysql configuration file

And replace all the content of it with following standered configuration.

Quote
[mysqld]
socket=/var/lib/mysql/mysql.sock
local-infile
old-passwords
safe-show-database
low-priority-updates
symbolic-links
log-error=/var/log/mysql/error.log
log-slow-queries=/var/log/mysql/slowquery.log
long_query_time=5
connect_timeout=10
max_connections=750
max_user_connections=60
max_connect_errors=20
max_allowed_packet=8M
key_buffer=32M
myisam_sort_buffer_size=64M
join_buffer=24M
record_buffer=32M
sort_buffer=2M
table_cache=1024
thread_cache_size=256
wait_timeout=1800
tmp_table_size=32M
query_cache_size=64M
query_cache_limit=4M
skip_name_resolve

[safe_mysqld]
open_files_limit=8192

[mysqldump]
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=16M
add-drop-table
extended-insert
quick

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

[mysql]
socket=/var/lib/mysql/mysql.sock
disable-auto-rehash
connect_timeout=10
local-infile
quick
#unbuffered
#silent

[client]
port=3306
socket=/var/lib/mysql/mysql.sock


Now save the file

Then
create following directory :

mkdir /var/log/mysql
touch /var/log/mysql/error.log
touch /var/log/mysql/slowquery.log

chown mysql:mysql /var/log/mysql -R



and restart the mysql service

service mysql restart

or

service mysqld restart



and you are done!!!

Return to “Hostripples dedicated Servers (Linux/Windows)”

Who is online

Users browsing this forum: No registered users and 3 guests