MySQL Optimization

Optimization in MySQL involves three components:

• Optimizing the MySQL Server
• Optimizing the database
• Query Optimization

Optimizing the configuration of MySQL

Server optimization includes a variety of approaches and methods, what I am trying to present here is an introduction to the basic approaches:

• Server compilation
• Tuning server parameters
• Tuning other parameters

To perform a good quality optimization, proceed by a practical methodology to modify data one by one and test each time result of the system to have an idea about the report. Measure the system performance before and after performing the optimization to check whether the system has optimized or not.

Server Compilation

It is recommended to use the source code version of the MySQL server and compile taking into account various system parameters to know the character set to use the microprocessor on which it will turn and use an appropriate compiler (Ex for pgcc Pentium microprocessors).

Tuning Server Parameters

It is possible to optimize the operation of MySQL by changing the values of the server parameters.

• To view the settings you must use the command: mysql> SHOW VARIABLES;
• To see the effect of the parameters on the server you must run the command: mysql> SHOW STATUS;

There are many monitoring tools to see the effects of the changes made to the settings on the receiver that are equivalent to the Mytop MySQL Monitoring Tool.

Note: my.cnf file contains all the parameters which you want to optimize.

As a first step, it is possible to start with the memory manager settings. The guiding principle is that if the server has more memory the faster it goes, however, we must ensure that the memory is available.

Mysql contains a set of InnoDB buffer pool sizes and it is also possible to configure the space allocated in Cache from the variables of my.cnf. The two most important variables “key_buffer_size” and “table_cache” shared by all threads running on the server and significantly influence the performance.

Example of variables:

• key_buffer_size: memory used for backups of MyISAM indexes.
• table_cache:number of working tables simultaneously.
• read_buffer_size: memory used for saving data from full table scans.
• sort_buffer_size: memory used for storing data tables that sorted by order.

Tuning other parameters

• The MySQL server is recommended for optimum performance on Solaris OS, however, it is possible to optimize it on other OS to be closer to its ideal performance.
• RAID-0 is recommended for optimizing operations to read and write. And the use of SCSI rather than IDE drivers.
• Using fast networks optimizes response time and the communication between client/server and master/slave server replication.

Optimizing the database

Generally for optimizing databases, we look to the best practices and design methodologies for databases that can implement database schemas and perform standard data. However, this requires:

• Understand what is slow in databases
• Modeling the database
• Using Indexes
• Use OPTIMIZE TABLE

What slowed the database

• A number of factors are usually the cause of the slow database. We include the following most common areas:
• Not enough indexes: The primary cause of slowness is the use of tables without indexes or no indexes on columns involved in the research. This does not mean that all tables must have indexes but must consider the needs of indexing.
• Excessive use of indexes: The increase in the number of indexes affects the performance level of updates, in fact, during the update of a table the add operations, modification, and deletion generally affect the indexes.
• Unnecessary privileges on tables and columns: Each MySQL access privilege checks the tables and columns of a table which considerably slows down the performance.
• Incorrect choices in the design of the database.

Modeling the database:

• Use the best practices for modeling and database design and the choice of the appropriate methodology used to implement database performance.
• Many considerations should be taken as:
• Appropriate choice of field types: always try to choose the most appropriate variables needed (e.g. For storing a number do not use more than 10 digits, even it is better to use a TINYINT deviation). Use the smaller fields for columns.
• Use fixed-length fields: the use of predetermined lengths to optimize access to columns because their positions are predefined. This involves reducing the use of VARCHAR, TEXT, and BLOB (for TEXT and BLOB, it is recommended to break the normalization of the database schema and data to back up these fields in third tables).
• Increase the use of NOT NULL when it is possible to optimize the storage space.
  Choosing the right type for tables: MySQL allows you to have the same table schema types.
  Make good indexing tables.

Using indexes

An index is a look-up table that allows us to quickly find rows in a table. The indexes used to determine the position of the record sought at a table. Indexes in MySQL stored form of b-trees (binary trees), which is a data structure easy to navigate.

The index includes single or multiple columns, the index called during a search on indexed columns. The basic idea to remember is that if a search is common and it included one or more columns, one must create the indexes corresponding to optimize the response time via the command CREATE INDEX.

Using OPTIMIZE TABLE

Equivalent to disk defragmentation, OPTIMIZE TABLE for fragmenting tables.

Query Optimization:  MySQL can analyze queries and know the time and the execution plan. Such information can include slow queries and optimize execution.

Detecting slow queries:

• Observe delays in executions and abnormal response time
• Or to make a benchmark: test applications to see what components are slower.
• Or check the Slow query log: it is possible to activate this option in MySQL setting variable – log-slow-queries

Once slow queries, run EXPLAIN to understand the performance.