MySQL Server Performance Tips

MySQL Server Performance Tips:

• Do regular maintenance of MyISAM tables with analyze and optimize commands.
• Choose better backup policy for DBs. It completely depends on which storage engine we are using.
• Always try compress static data so can save the space and no need to take backup often.
• Check MySQL memory utilization before set any high value for buffer parameters.
• While using InnoDB storage engine,always use INNODB_FILE_PER_TABLE.
• Use benchmarking to identify current performance of server.
• Keep in mind that regular backup should not affect the server performance during peak time.
• Always design schema properly, so when data will grow and if we want to change the structure or something it should be easy to scale.

DB Design Tips:

• Keep in mind that 3rd normal form is not always good. Design database as per requirement and try to use less joins.
• Always try to use boolean flags rather than create any varchar field.
• Indexes are always good for performance but dont create index on every field.
• Avoid to create duplicate indexes as it will not being used and consume more space.
• For the email address column in large tables, try to create reverse column and than make index on it. It will be faster.
• Use appropriate data types always. Unnecessary, avoid to give large value to data types.
• Try to avoid NULL. Rather than that give ‘ ‘ for text field and 0 for numeric field.

MySQL Query Optimization Tips

• Use EXPLAIN to profile the query execution plan
• Use Slow Query Log to track down slow queries
• Don’t use DISTINCT when you have or could use GROUP BY
• Use LOAD DATA instead of INSERT while inserting bulk records.
• Don’t use ORDER BY RAND() if you have less records. Its for large amount of data
• Use SQL_NO_CACHE when you are SELECT frequently updated data or large sets of data
• Always avoid wildcards at the start of LIKE queries. It will not use any index.
• Always keep in mind that complex queries should be split.
• Try to use caching for similar queries.
• Don’t use COUNT (*) on any Innodb tables for search,If you want total no of rows, than use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() functions.

MySQL Configuration Parameters Tips

• For InnoDB try to use innodb_flush_method (O_DIRECT). It will bypass OS filecache for innodb data files.
• Before set any parameter, check that its session level or global level parameter and set value according.
• Try to use session level parameters while doing any bulk operations.
• Run MySQL with STRICT SQL_MODE so server can give proper warnings.
• Maximum time, try to use –skip-name-resolve for connect mysql server through hostname.