How to tune the MYSQL database for best perfoemance?

1-Make sure that we are using innodb.

2-Make sure that the limits aren't higher than the limits needed for the application so mysqld doesn't eat up the server resources.

3-Use cache whenever is possible as cache can drastically increase  MYSQL performance.

4-Increase innodb_buffer_pool_size to use the memory available for MYSQL.5-If the application doesn't have sensitive data  innodb_flush_log_at_trx_commit can be set to0 to reduce I/O load.

6-If possible use a separate server for MYSQL database.

7-Provide the server with an SSD hard drive to improve I/O performance.

8- If possible combine all the above with clustering.

1. Optimize the innodb_buffer_pool_size

2. Optimize the innodb_log_pool_size

3. Using connection pool at application level or thread pool at MySQL level

4. query_cache_size - use properly or it shall be disabled. Use better indexing for performance

·      Being careful using trigger:

Using triggers on specific database tables can be a nice way to come up with a solution for a specific problem but on the other hand, when a trigger is fired (or “triggered”) by a given SQL statement, then all the work that trigger does becomes a part of the SQL statement. This extra work can slow down the SQL statement, and sometimes that can cause a big hit on performance if the trigger fires a lot.

·      Use the same data type for both primary and foreign key columns:

This means that when defining a primary and foreign key, it’s not a good idea to define the primary key with a VARCHAR data type and a foreign key with a CHAR data type – it’s better to define both keys as either VARCHAR or CHAR.

·      Reducing disk writes and reads:

Do the best you can with the available memory (the RAM – not to be confused with disk space), so that you reduce the number of disk reads and writes to the absolute minimum necessary.

·      Don’t use too many indexes

Indexes can take up a lot of space. So, having too many indexes can actually be damaging to your performance because of the space impact. 

1. use limit in your query

2. avoid select * , put only columns you need

3. indexing as needed

1.After tuning apache to handle larger loads it is beneficial to tune MySQL to additional connections.

2.The MySQLTuner script assesses your MySQL installation, and then outputs suggestions to increase your server’s performance and stability.

The best performance for a relational database like mysql can be achieved firstly by the design pattern that a database developer uses which should include the3 forms of normalization to decrease redundancy and ensure more accessibility according to the requirements of the project and the used programming language to access these data in a web project. Secondly the server configurations (include many options that have to be used also according to the requirements of the application) like the db engine which should be preferably innodb as it offers flexibility by transactions and doesn't lock the data tables from the system as mysiam does.Also routine queries should make use of the caching capability.lastly creating indexes based on the select queries and maintaining the same order.

Profile the workload and don't use MySQL as a queue

Apply proper database data types with proper length, add indexes where required, use memory cache, use dedicated separate instance for database and use innoDB as a database engine, that will surely increase your speed. Also we can improve performance via various things like query optimization and using queues between your processes.

 * Optimize the innodb_log_pool_size

* Using connection pool at application level or thread pool at MySQL level

* query_cache_size - use properly or it shall be disabled. Use better indexing for performance

*Optimize the innodb_buffer_pool_size

1: Profile your workload

2: Understand the four fundamental resources

3: Don't use MySQL as a queue

4: Filter results by cheapest first

5: Know the two scalability death traps

6: Don't focus too much on configuration

7: Watch out for pagination queries

8: Save statistics eagerly, alert reluctantly

9: Learn the three rules of indexing

: Leverage the expertise of your peers