wlraider81
Member
- 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:
Access 'root' in mysql:
Updated parameters according to MysqlTuner:
See parameters were set:
2. Optimize the settings in Mysql:
Edit the file '/etc/my.cnf':
Create the following changes that match your server:
After completing the restart your Mysql:
or
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!
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.plAccess 'root' in mysql:
Code:
mysql -uroot -pUpdated 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
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=utf8After completing the restart your Mysql:
Code:
/etc/init.d/mysqld restartor
Code:
service mysqld restartWith 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!
