How to Improve MySQL Large Database Performance
This is my first post for the blog. Recently I have worked on two large database projects on backend MySQL Database. During the period of the project I came across with many problems and somehow I managed them all.
Many of you also came across with the same MySQL Database problem with Performance. Query is not running well etc. During this period I read many blogs and forums to solve these problems. I have figured out some tips or can say some points which we should taken care of.
Database Engine (MYISAM vs. Innodb)
Both MySQL Database engines have its own pros and cons that we have to decide which type of db engine will work great for us. Below are some concerns with this two engines:
- MYISAM use less memory
- It allows full text search
- It locks the table while writing
- It is useful for application in which reading is high and fewer write
- It uses more memory
- It does not support full text search
- InnoDB provides faster performance
- It lock the table at row level while writing to the table
- It works great for the application which make extensive use of read and write both
Good Database Design
Good database design is a backbone for the application performance. Bad design makes application performance less. Table must be normalized. Data Structure is the main factor which must be developed carefully. Every Developer must give time for each table and fields for making good design. You should give proper data type to each field. When you done with database creation, you would like to see what is MySQL suggest you for your database table. Here is the Command which can help you to get this information:
ANALYZE TABLE <table-name>;
You can find full description here: http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
What many of us is knowing that indexes help us to increase the speed of query. Many times indexes create confusion in mind. Creating of proper indexes for the table is necessary but do make table overhead of indexes as indexes take space on the disk. So it increases the workload on the disk. Working on my project I many time need to add and delete column from large table which takes so much time. For increasing performance we can make index ON of OFF. so before whenever I start my database operation I make the index key off and do the operation when operations completed I again makes indexes ON. So rather than making new indexes on my each operation, it will make new index when I male Index on so its only one time. for turning on and index I use below syntax:
Disable Indexes: ALTER TABLE table_name DISABLE KEY
Enable Indexes: ALTER TABLE table_name ENABLE KEY
Hardware makes as important role as other things in database tuning. Our hardware needs just as much attention and tuning as our database and script does. It is also need to check MySQL Configuration file what type of changes we have make to the configuration. There is one tool available which is perl script. You can download and Install on server It tells that which type of configuration change you can make improve the performance.
Note: This are the simple things which I feel good to use and take care for better performance.