Tuning of any database is required in order to optimize its performance. A well-tuned database performs faster and maximizes the use of system resources. If your database is not optimized, it will response poorly and eventually effect your business. Similarly if you are using MySQL for storing your data then don't forget to optimize it. MySQL provides different storage engines and each storage engine has unique design and properties. So it is important to optimize each storage engine according to its design. I will discuss performance tuning tips for InnoDB.
Schema Tips
Primary Key: In InnoDB, accessing data using PK is much faster than any other key. So it is best to do lookups using PK. Consider some tips on PK:
Innodb_buffer_pool_size: All the activities of innodb starts here. It is the main buffer and stores data, index page and locks. Innodb buffer cache is much more efficient than OS cache for write operations. It is recommended to set buffer pool to atleast 70-80% of memory.
Innodb_log_file_size: It is important for write intensive operations. Set its value high but keep in mind that high value also increases recovery time. So to the keep balance between performance and recovery time you can set it to 256M.
Innodb_flush_log_at_trx_commit: In InnoDB every time you commit a transaction, logs are also flushed by default. Using can set its value to 0,1 or 2. Setting its value 0 will cause data loss in case of MySQL crash. In case of 1, data remain safe if you are using a good hardware. Set its value to 2 if you can afford to lose last second transaction.
Innodb_file_per_table: Large number of table usually increases startup time. Setting its value to 1 will keep each table in a separate file.
OS and Hardware: Choose faster CPU that give fast response like Core, X86_64 etc. In case of OS it should be of atleast 32 bit.
Application Optimization Tips
Transactions: Transactions are associated with innodb even if you don't use them and each transaction has some cost. For better performance use AUTOCOMMIT with transactions.
Lock tables: Avoid using lock tables. Lock tables are used with table level locking storage engines and for row level locking, transactions are used.
Schema Tips
Primary Key: In InnoDB, accessing data using PK is much faster than any other key. So it is best to do lookups using PK. Consider some tips on PK:
- Using PK you can access clustered data together.
- Data clustering is done by PK.
- PK sometime leads to table fragmentation so it is recommended to insert data in PK order.
- Avoid using a very long PK as it waste lot of disk space. If you really need to use it then create an AUTO_INCREMENT column.
- Long PK also effect index performance so keep it short.
- Avoid using large Bolbs as they take large storage space.
- Blobs cause row fragmentation.
- It is useful to store compressed data in Blobs.
- Avoid using UNIQUE Indexes as they are very expensive.
- Indexes in InnoDB take large space as they are not prefix compressed.
- Avoid excessive index.
Innodb_buffer_pool_size: All the activities of innodb starts here. It is the main buffer and stores data, index page and locks. Innodb buffer cache is much more efficient than OS cache for write operations. It is recommended to set buffer pool to atleast 70-80% of memory.
Innodb_log_file_size: It is important for write intensive operations. Set its value high but keep in mind that high value also increases recovery time. So to the keep balance between performance and recovery time you can set it to 256M.
Innodb_flush_log_at_trx_commit: In InnoDB every time you commit a transaction, logs are also flushed by default. Using can set its value to 0,1 or 2. Setting its value 0 will cause data loss in case of MySQL crash. In case of 1, data remain safe if you are using a good hardware. Set its value to 2 if you can afford to lose last second transaction.
Innodb_file_per_table: Large number of table usually increases startup time. Setting its value to 1 will keep each table in a separate file.
OS and Hardware: Choose faster CPU that give fast response like Core, X86_64 etc. In case of OS it should be of atleast 32 bit.
Application Optimization Tips
Transactions: Transactions are associated with innodb even if you don't use them and each transaction has some cost. For better performance use AUTOCOMMIT with transactions.
Lock tables: Avoid using lock tables. Lock tables are used with table level locking storage engines and for row level locking, transactions are used.
No comments:
Post a Comment