Optimization and Tuning of MYSQL
MySQL is the most suitable option of database for web applications. It is a central component of the mostly used LAMP web application software stack. After installation of MySQL, configuration is required. However, it is not necessary to do MySQL configuration every time you restarts. It is referred to configuration file. With this configuration, it is also important to think about server performance. Server performance is the basic thing behind stable application. Performance leads to create profit along with productivity. Therefore, it is essential to tune MySQL database for the best performance and to extract sleeping connections.
MySQL options file may be short or long. It is based on the number of configurations one set. Server related options are stored in the $MYSQL-HOME directory whereas user related configurations are stored in the user directory which contains tilde character with slash. With Windows, no one needs to search through directories to find option files for max_connections.
Out of memory miscalculation may vary:
One can open the ini files with any text editor. The options file may contain multiple sections marked in brackets. Use simple text editor with the execution of “sudovim/etc/mysql/my.cnf”.
The Functionality of SQL with my.cnf commands:
Innodb-buffer-pool-size:
The buffer pool is a storage area to cache data and index in memory. It is used to keep the frequently accessed data in memory.
Innodb-flush-method:
With regards to flushing, this is assigned to O-DIRECT in order to avoid double buffering. This should be done unless the I/O system produces low performance.
Innodb-flush-log-at-trx-commit:
It indicates the event with the log file. “1” is the safest setting as the log is flushed to disk after every transaction.
SQL Performance Tuning:
Prior to the performance tuning, one should be clear about each and every change. One must observe keen on the following factors.
Max-connections Variable – MySQL server keeps the connection to be remained open even when the application is not using it.
Query cache:
As the name suggests the query_cache_size is the memory which is available to query caching.query_cache_limit is the number of kilobytes in which a query to be cached. If you set the value too high then it does not cached smaller queries. If you set the value too low it may not cached the bigger queries. So you have to set the limit according to your own need.
Innodb-flush-method variable – The default value is fdatasync. This value is common while you have a battery backup cache component.
Configuration Optimization:
Every user of MySQL should tweak the configuration. The current version has some defaults than its processors. If you edit locally, you can copy the file into the main system by copying it into the shared folder withcp/etc/mysql/my.cnf/home/vagrant/code and edit it with a regular text editor.
Lists of my.cnf commands:
Max-binlog-size:
This setting determines how the large binary logs should be. These are logs which log the transactions and queries.
Log-bin:
This option enables binary logging. Without this, there is no replication. MySQL has same defaults which make things as ready for production. Of course, every app is different and has additional custom tweaks.
MySQL is the most suitable option of database for web applications. It is a central component of the mostly used LAMP web application software stack. After installation of MySQL, configuration is required. However, it is not necessary to do MySQL configuration every time you restarts. It is referred to configuration file. With this configuration, it is also important to think about server performance. Server performance is the basic thing behind stable application. Performance leads to create profit along with productivity. Therefore, it is essential to tune MySQL database for the best performance and to extract sleeping connections.
Code:
Wait_timeout = 60Out of memory miscalculation may vary:
Code:
max_connections=400The Functionality of SQL with my.cnf commands:
Innodb-buffer-pool-size:
The buffer pool is a storage area to cache data and index in memory. It is used to keep the frequently accessed data in memory.
Code:
Information_schema.innodb_buffer_page =0.0000000With regards to flushing, this is assigned to O-DIRECT in order to avoid double buffering. This should be done unless the I/O system produces low performance.
Code:
Innodb_flush_method = normalIt indicates the event with the log file. “1” is the safest setting as the log is flushed to disk after every transaction.
Code:
Innodb_flush_log_at_trx_commit= 2Prior to the performance tuning, one should be clear about each and every change. One must observe keen on the following factors.
- Put the changes ever in the right section. This section determines each variable. In order to see changes and the impacts created, one has to put configuration and tuning variable in the right section.
- Follow these settings,
Max-connections Variable – MySQL server keeps the connection to be remained open even when the application is not using it.
Code:
Max_connection=400As the name suggests the query_cache_size is the memory which is available to query caching.query_cache_limit is the number of kilobytes in which a query to be cached. If you set the value too high then it does not cached smaller queries. If you set the value too low it may not cached the bigger queries. So you have to set the limit according to your own need.
Code:
query_cache_size = 128MB
query_cache_limit = 4MB
Code:
Innodb_flush_method=6Every user of MySQL should tweak the configuration. The current version has some defaults than its processors. If you edit locally, you can copy the file into the main system by copying it into the shared folder withcp/etc/mysql/my.cnf/home/vagrant/code and edit it with a regular text editor.
Lists of my.cnf commands:
Max-binlog-size:
This setting determines how the large binary logs should be. These are logs which log the transactions and queries.
Code:
Binlog-row-event-max-size = #This option enables binary logging. Without this, there is no replication. MySQL has same defaults which make things as ready for production. Of course, every app is different and has additional custom tweaks.
Code:
Log_bin_trust_function_creators
