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 the 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 been 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 micro-processor on which it will turn and use an appropriate compiler (Ex for pgcc Pentium micro-processors).

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 equivalent to 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 size and it is also possible to configure the space allocated in Cache from the variables of my.cnf. The two most important variables are “key_buffer_size” and “table_cache” because they are shared by all threads running on the server and significantly influence on 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 will be sorted by an 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 performing standard data. However this requires:

•    Understand what is slow in databases
•    Modelling 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 in the following most common area:

•    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 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 affects the indexes.

•    Unnecessary privileges on tables and columns: Each MySQL access privilege checks the tables and columns of a table which considerably slow down the performance.

•    Incorrect choices in the design of the database.

Modelling the database:

Use the best practices for modelling and database design and the choice of the appropriate methodology used to implement database performance.

A number of 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 at 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 are stored form of b-trees (binary trees), which is a data structure easy to navigate. The index can include a single or multiple columns, the index will be 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, 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 the go slow queries and optimize execution.

Detecting slow queries:

•    Observe delays in executions and abnormal response time
•    Or to make 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 detected slow queries, run EXPLAIN to understand the performance.

Social Bookmarking

With newer version of MySQL (mysql 5.6) you get improved processing speed, replication, instrumentation and query optimization.

The management system of open source databases MySQL taking place now in version 5.6. This version brings many advancements, and the same are available in the Community Edition of the offer provided by Oracle.

Here are some of the latest MySQL 5.6 features:

• MySQL optimizer improved (enriched diagnostic, optimization of subqueries …)
• Engine storage InnoDB optimized (flow up to 230% faster, enhanced availability …)
• New replication features (clusters of self-replication repair, replication up to 5 times faster …)
• Instrumentation magazine (better monitoring queries, objects, users and applications);
• IPv6 compliance, optimizing the default configuration, etc..

More Efficient, More Reliable, More Flexible

“The new improved MySQL 5.6 brings and illustrate once more the Oracle investment for innovation  of MySQL, which is now an ideal solution for web applications, cloud services and embedded as the most demanding choice for webmasters“ said Tomas Ulin , vice president of MySQL Engineering.

Social Bookmarking

Through cPanel you can repair your MySQL database easily. You should follow the below guidelines to repair your MySQL database.

At first, log in to your cPanel account and select MySQL database. You’ll see this option Modify Databases, select a database from the drop down box which you are looking for to repair. Click on Repair DB option.

You should also repair MySQL database through phpMyAdmin

Once you login to your cPanel control panel. Select phpMyAdmin and then select database whose tables you want to repair.

You’ll see the list of the Database tables. Click on the tables those you want to repair or simply click on check all option to select all tables.

After that click on the box With Selected: and select Repair table option. So this will run the REPAIR TABLE SQL query on the selected tables and they will be repaired.

Social Bookmarking