Amazing Tips for Improve, Tuning and Increase MySQL Database Performance

Do you used MySQL Database as your database server and found that the performance is less powerfull than SQL Server, PostgreSQL, Oracle or another database server ? Let’s try to increase the performance by tuning up your database server with the following tips :

1. Checked your DNS setting and make sure that the setting are in the proper configuration
2. Add the IP address of host client into /etc/hosts entry on MySQL database server
   Tips#1 & 2 will increase the speed of connection between host client and MySQL server. Without proper DNS setting and /etc/hosts entry, MySQL will be checked the address of host client for security reason.
3. Improve your MySQL Configuration and used improved setting
   To prevent any problem while running on the production state, MySQL will be activating moderate setting by default. Moderate setting means that MySQL will not activating highly performance setting. Below is an example :
   # This is for a system with little memory (32M – 64M) where MySQL plays
   # an important part, or systems up to 128M where MySQL is used together with
   # other programs (such as a web server)MySQL usually included improved configuration called my-large.cnf. Checked this file out and applied the setting into my.cnf configuration. Please note that we must backup the my.cnf before trying to change any configuration. Also, don’t forget to making MySQL on the off mode before changing any setting.If we have powerful server with huge memory (ex : more than 2 GB), we could increase the performance by increasing the key_buffer_Size (for table with MYISAM engine) or innodb_buffer_pool_size (for table with inno-db engine) and increasing the table_cache setting.
4. Increase Server Memory (RAM)
   It’s hardware improvement. High memory will ensure that server could serve data traffic without problem. Server will not used the swap memory if we have enough physical memory. Swap memory is okay while our memory is not enough but the swap memory usually more slower than physical memory
5. Do not used catch-all query, e.g : select *
   Used specified column on the select clause. Select * will trying to get all of result, although it was not needed and doesn’t fit with all of situation. If we want to get item_name on the items table, I strongly recommended to use “Select item_name from items” rather than “select * from items”
6. Create an index for such column/fields. Used an index for searching field or any field whichh often be usage

Reference :

1. Linux Magazine MySQL Tuning
   
2. http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
3. http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
4. The MySQL Query Performance Improvement Process