DirectAdmin optimizes MySQL performance (upgrades TCMalloc and modifies configuration)

Recently, I saw a lot of discussions about MySQL dragging down server resources, especially many personal blog owners who use wordpress source code and even send a comment to the MySQL database. Although I cannot fundamentally solve these problems, it is necessary to do some simple optimization for MySQL DirectAdmin Under panel MySQL optimization Take an example to make a simple record.

For MySQL optimization in this article, I divide it into two parts, including upgrading TCMalloc And modify the MySQL configuration file.

★★★ Install TCMalloc ★★★

First, let's focus on how to install TCMalloc to optimize the performance of MySQL under high load. First, root logs in to the server. Since my server uses 64 bit Centos, you need to install the libunwind library first. This step can be skipped for 32-bit systems.

wget http: //download.savannah.gnu.org/releases/libunwind/libunwind-0.99-alpha.tar.gz

tar zxvf libunwind-0.99-alpha.tar.gz

cd libunwind-0.99-alpha/

CFLAGS=-fPIC ./configure

make CFLAGS=-fPIC

make CFLAGS=-fPIC install

Next, we begin to install Tcmalloc.

wget http: //gperftools.googlecode.com/files/gperftools-2.0.tar.gz

tar zxvf  gperftools-2.0.tar.gz

cd gperftools-2.0/

./configure

make && make install

echo “/usr/local/lib” > /etc/ld.so.conf.d/usr_local_lib.conf

/sbin/ldconfig

After compiling, we edit the mysqld_safe file and add Tcmalloc.

vi /usr/bin/mysqld_safe

Find # executing mysqld_safe and add it below:

export LD_PRELOAD=/usr/local/lib/libtcmalloc.so

Save, exit, and restart MySQL.

service mysqld restart

Next, check whether it is effective and running.

lsof -n | grep tcmalloc

If you see something similar to the following, it means success.

mysqld     7758     mysql  mem       REG              253,0   1943001  109233156 /usr/local/lib/libtcmalloc.so.4.1.0

★★★ Modify configuration file ★★★

The default MySQL configuration file of DirectAdmin is very concise.

 [mysqld] local-infile=0

 We need to modify the configuration file. Please refer to the following content (vim/etc/my. cnf)

[mysqld]
local-infile=0

skip-locking

query_cache_limit=1M

query_cache_size=32M

query_cache_type=1

max_connections=500

interactive_timeout=100

wait_timeout=100

connect_timeout=10

thread_cache_size=128

key_buffer=16M

join_buffer=1M

max_allowed_packet=16M

table_cache=1024

record_buffer=1M

sort_buffer_size=2M

read_buffer_size=2M

max_connect_errors=10

# Try number of CPU’s*2 for thread_concurrency

thread_concurrency=4

myisam_sort_buffer_size=64Mserver-id=1

[safe_mysqld]

err-log=/var/log/mysqld.log

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

The above configuration content comes from the DirectAdmin official help center（ http://help.directadmin.com/item.php?id=44 ）, please modify the parameters according to your own situation.

After completion, save, exit, and restart MySQL.

/sbin/service mysqld restart

OK， After the optimization of the above two aspects, I believe your MySQL performance will be greatly improved under high load.