1. Use Innodb storage engine as compared to MyISAM for transaction tables
2. Have file per table flag turned on in the my.cnf file (innodb_file_per_table)
3. Put indexes on fields with highest SI
4. Try to avoid any blob entry
5. Leverage Load balance - Writes to Master and Reads (Reports) to Slave
6. max_connections and related per thread buffers need to be set keeping in mind the overall memory allocated
7. Do not use default innodb_buffer_pool, infact. Most places recommend to set this parameter 80% of RAM available.
8. mtop is a poor man''s utility and very handy for a DBA. Monitor - CPU, Memory & Connections
9. Use latest and stable MySQL db version and use native connector
10. Set slow query flag and tune the instances logged
11. Monitor replication lag and attempt to avoid insert... select pattern as they run on Master as well as Slave.
12.Have primary key mandated for every table
13. select count(1) on a table with high no. of records can be slow
14. Consider partitioning of tables that have high growth.
15. Avoid firing any query on information_schema especially when the no. of schemas/tables in that instance is high
16. For log tables, consider NOT having archival tables and later deleting thousands of old records but, instead partition the log tables say on weekly, fortnightly, monthly, quarterly basis depending on data growth/reads and then just drop the old partitions.
2. Have file per table flag turned on in the my.cnf file (innodb_file_per_table)
3. Put indexes on fields with highest SI
4. Try to avoid any blob entry
5. Leverage Load balance - Writes to Master and Reads (Reports) to Slave
6. max_connections and related per thread buffers need to be set keeping in mind the overall memory allocated
7. Do not use default innodb_buffer_pool, infact. Most places recommend to set this parameter 80% of RAM available.
8. mtop is a poor man''s utility and very handy for a DBA. Monitor - CPU, Memory & Connections
9. Use latest and stable MySQL db version and use native connector
10. Set slow query flag and tune the instances logged
11. Monitor replication lag and attempt to avoid insert... select pattern as they run on Master as well as Slave.
12.Have primary key mandated for every table
13. select count(1) on a table with high no. of records can be slow
14. Consider partitioning of tables that have high growth.
15. Avoid firing any query on information_schema especially when the no. of schemas/tables in that instance is high
16. For log tables, consider NOT having archival tables and later deleting thousands of old records but, instead partition the log tables say on weekly, fortnightly, monthly, quarterly basis depending on data growth/reads and then just drop the old partitions.
