Recently, I have seen a lot of discussions about MySQL dragging down server resources. In particular, many personal blog webmasters use WordPress source code and have to write every comment into the MySQL database. Although I cannot fundamentally solve these problems, it is still necessary to do some simple optimizations for MySQL. Below, we will take the MySQL optimization under the DirectAdmin panel as an example. Make a simple note.
Regarding the MySQL optimization in this article, I divided it into two parts, including upgrading TCMalloc and modifying 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, log in to the server as root. Because my server uses 64-bit Centos, I need to install the libunwind library first. This step can be skipped on 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 start installing 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 the compilation is completed, we edit the mysqld_safe file and add the Tcmalloc part.
vi /usr/bin/mysqld_safe
Find #executing mysqld_safe and add below:
export LD_PRELOAD=/usr/local/lib/libtcmalloc.so
Save, exit, and restart MySQL.
service mysqld restart
Next, check whether it takes effect and run it.
lsof -n | grep tcmalloc
If you see something similar to the following, it means success.
mysqld 7758 mysql mem REG 253,0 1943001< x2> 109233156 /usr/local/lib/libtcmalloc.so.4.1.0
★★★Modify the configuration file★★★
DirectAdminThe default MySQL configuration file is very simple.
[mysqld]
local-infile=0
We need to modify the configuration file, 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 official help center of DirectAdmin (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 completing the above two aspects of optimization, I believe your MySQL's performance under high load will be greatly improved.
