Optimize MySql on VPS with Mysqltuner

wlraider81

Member
Registered
Joined
Aug 15, 2016
Messages
22
Points
3
1. Optimize performance for Mysql commands with Mysqltuner:

Tuner Script MySQL is the solution to this case. It is written in Perl, the main function is to analyze the MySQL server, then make suggestions on configuration parameters obtained. Therefore, MySQL Tuner can improve the performance of server in any circumstances.

The command to Install:

Code:
cd /usr/local/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Access 'root' in mysql:

Code:
mysql -uroot -p

Updated parameters according to MysqlTuner:

Code:
mysql> SET GLOBAL query_cache_size=1024*1024*16;

See parameters were set:

Code:
mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';

2. Optimize the settings in Mysql:

Edit the file '/etc/my.cnf':

Code:
nano /etc/my.cnf
Create the following changes that match your server:

Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
local-infile=0

#ignore_builtin_innodb
innodb_file_per_table=1
default_storage_engine=MyISAM
skip-external-locking
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
max_user_connections=200
max_connections=500
thread_cache_size=128
key_buffer_size=64M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=8M
table_open_cache=256
sort_buffer_size=1M ## 1MB for every 1GB of RAM
read_buffer_size=1M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
net_buffer_length = 8K

collation-server=utf8_unicode_ci
character_set_server=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[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

[client]
default-character-set=utf8

After completing the restart your Mysql:

Code:
/etc/init.d/mysqld restart

or

Code:
service mysqld restart

With the setting on your system Mysql also been offloading 40% of system performance. Also you learn more for the best system config for your server.

Good luck!
 
Older Threads
Recommended Threads
Replies
1
Views
2,669
Replies
12
Views
4,530
m19
Replies
5
Views
3,299
Replies
9
Views
2,728
Similar Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top