How do I optimze mysql?

Important Notes:
PHP/mySQL sites can often be slow because of the ammount of reasources needed.  Often, these sites can be sped up simply by optimizing the configuration of the my.cnf file.

Getting Started:

1. You can check to see if your server is being overloaded by mySQL by issuing the top command:
   
   

   [root@localhost]# top

   
   You should see results similar to the following:
   
    
   
   
   
2. If you see the process mysqld near the top of the list, then you need to optimize your my.cnf file in order to have mySQL run more efficiently on your server.  Also, check the Load Average in the top right.  If the load average is above 2.0, it is considered a high load average.
   
   
3. To solve this type of issue, you need to make sure that you have at least a true Pentium of at least 2.4Ghz or greater.  The reason is because Celeron processors have trouble processing mySQL.  If you are having trouble with mySQl, you may consider upgrading to a server with a better processor.
   
4. If you do have a server with a Pentium processor or higher, and you're still having trouble with your server running slow, you'll want to change the my.cnf file on your machine.  You can do this with the following command:
   
   The following command will backup the original my.cnf file to the /root directory:
   
   

   [root@localhost]# mv /etc/my.cnf /root

   
   The next command copies a pre-optimized my.cnf file to where the my.cnf file is read from:
   
   

   [root@localhost]# /cp /usr/share/doc/mysql-server-5.0.27/my-huge.cnf /etc/my.cnf

   
   
5. Now you'll need to edit the my.cnf  file using the following command.  ( I use the 'vi' text editor, feel free to use nano or whatever your prefrence is)
   
   

   [root@localhost]# vi /etc/my.cnf

   
   Your screen should look similar to the following screen shot:
   
   
6. While you're editing the my.cnf file, you'll want to change the thread_concurrency.  You'll need to set the value equal to 2x's the number of cpu's.  Example:  If you have a dual core processor, put 4.   If you have a quad core processor, put 8.
   
7. Finally, all that needs to be done is to restart the mySQL server using this command:
   
   

   [root@localhost]# service mysqld restart

Related Articles

No related articles were found.

Attachments

No attachments were found.

Visitor Comments

No visitor comments posted. Post a comment