DirectAdmin optimizes MySQL performance (upgrades TCMalloc and modifies configuration)

Time: July 1, 2012 Column: VPS Tutorial Author: Zhao Rong Click: 16236 times

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.

About this article MySQL optimization 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.

label ： DirectAdmin , MySQL optimization , TCMalloc

↓↓↓ You may be interested in these ↓↓↓

WHM/cPanel data migration to DA (DirectAdmin)

DirectAdmin (DA) Latest Installation Tutorial (2020)

[Transfer] DirectAdmin automatically issues Let's Encrypt free certificate, which can also be automatically updated

DirectAdmin Tutorial: Converting PHP Mode CLI to CGI

DirectAdmin Panel Latest (2013) Installation Tutorial

Troubleshooting DirectAdmin panel section

DirectAdmin Latest (2012) Custom Installation Tutorial

DirectAdmin Install eAccelerator Zend ioncube One click Installation Package

Statement: Blogs are only for sharing information and are not recommendations. Websites do not participate in transactions and are not intermediaries. The content only represents personal views and is not authoritative. Readers should consider it and take their own risks! Every penny and every commodity is still the eternal truth. Juvenile readers (including physical and psychological) should visit this website with their guardians! This article is written by（ Zhao Rong ）Original compilation, reprint, please keep the link: DirectAdmin optimizes MySQL performance (upgrades TCMalloc and modifies configuration) The code is not easy, please do not copy or paste!
About using: This website mainly shares server and VPS information, and does not provide any product sales or purchase. All visitors please purchase and use products within the scope of national laws and regulations. QQ group discussion: 683851361
About safety: Any IDC has the possibility of going bankrupt and running away. The host line is even more uncontrollable. Monthly payment and backup are your best choices. Please keep good and regular backup habits

DirectAdmin optimizes MySQL performance (upgrades TCMalloc and modifies configuration): currently there are 24 messages

1. 

   11th floor

   patqian :

   ❓ Why can I configure this on the other two hosts? The other one can't be configured. I have configured it for N times since starting MYSQL. Like the other two hosts, it just can't be started
   #service mysqld restart
   Shutting down MySQL. SUCCESS!
   Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/cccc.cccc.pid).

   2014-10-01 16:38 [Reply]
2. 

   10th floor

   Military division :

   In the 1.4.5 version of DA/etc, there is no my.cnf but only my.cnf.old.. It seems that the accessories inside are useless

   2014-06-12 13:14 [Reply]
3. 

   9th floor

   bihui :

   h ttp://tiger.im/297.html

   Refer to this to fix it. My version is 5.0, and it will be fixed after upgrading to 5.1

   2013-12-20 23:18 [Reply]
   • 

     Zhao Rong :

     The environment and component version configured by each person may be different. Tutorials are dead and live, and must be modified according to their own conditions.

     2013-12-20 23:22 [Reply]
4. 

   8th floor

   bihui :

   I got dizzy. I installed it several times, but it failed. Centos 5.1

   [ root@server gperftools-2.0]# lsof -n | grep tcmalloc
   [ root@server gperftools-2.0]#

   I have checked the conf files. I am depressed

   2013-12-20 18:10 [Reply]
5. 

   7th floor

   bihui :

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

   What is this business for?

   2013-12-20 14:19 [Reply]
   • 

     Zhao Rong :

     Write a line to a file

     2013-12-20 17:44 [Reply]
     • 

       bihui :

       The line after writing should be/usr/local/lib
       And there are no quotation marks in the conf file

       2013-12-20 17:47 [Reply]
6. 

   6th floor

   bihui :

   Lsof - n | grep tcmalloc The thing you said does not appear after the execution

   [ root@server gperftools-2.0]# lsof -n | grep tcmalloc
   [ root@server gperftools-2.0]#

   2013-12-20 14:08 [Reply]
   • 

     Zhao Rong :

     This indicates that the installation was not successful.

     2013-12-20 14:12 [Reply]
7. 

   5F

   james :

   After installing Tcmalloc, the database cannot be started. Fortunately, there is a backup, which is a false alarm.
   How can I uninstall Tcmalloc? I'm a novice. Thank you

   2013-01-05 10:43 [Reply]
   • 

     Zhao Rong :

     Edit mysqld_safe to remove Tcmalloc.
     However, I have installed this on different servers for many times, and none of them failed, let alone failed to start the database... It's too tragic for you

     2013-01-05 10:52 [Reply]
     • 

       james :

       Hello, in the second step, I don't have the my.cnf file, but there are two similar files, my.cnf.old and my.cnf.rpmsave. Which one should I modify? thank you

       2013-01-05 11:26 [Reply]
       • 

         Zhao Rong :

         You can directly create a

         2013-01-05 11:43 [Reply]
         • 

           james :

           ERROR! The server quit without updating PID file. There is an error restarting the database. What should I do? Thank you very much

           2013-01-05 11:56 [Reply]
           • 

             Zhao Rong :

             Modify the my.cnf file, the configuration file is wrong, or clear the log, or Google related error messages.

             2013-01-05 12:12 [Reply]
             • 

               james :

               It has been solved. Thank you. The database versions are different. Please refer to the/usr/share/mysql/my *. cnf files. Select a suitable one for your own server and copy it.

               2013-01-05 12:37 [Reply]
               • 

                 Hunhui :

                 I succeeded in using your method. I struggled for a long time, but I just found one to copy

                 2013-05-02 07:51 [Reply]
8. 

   4th floor

   Yusky :

   Is the server not installed by default?

   2012-07-08 03:28 [Reply]
   • 

     Zhao Rong :

     The server generally has an operating system installed by default.

     2012-07-08 07:59 [Reply]
9. 

   floor

   zodiacg :

   Some parameters of MySQL 5.5 configuration file have changed compared with 5.1. For example, skip locking is now skip external locking. Moreover, it is so wicked that it has not changed the contents of its own small memory configuration file... Last time I used my small.cnf directly, it could not be started directly... Finally, I added skip innodb and left it alone

   If you are worried about the resource consumption of comments, you can consider third-party comment services, such as Disgus and China.

   2012-07-02 08:31 [Reply]
   • 

     Zhao Rong :

     Yes, this article describes the~

     2012-07-02 08:31 [Reply]
10. 

    Bench

    imnpc :

    😎 Collect for use

    2012-07-01 17:45 [Reply]
11. 

    sofa

    Wolf :

    Thank you for such a good article. I accept it. Direct COPY

    2012-07-01 16:08 [Reply]

Comment

Click here to cancel the reply.

Nickname*

Email* (Teach you to set your own personal portrait)

website