Server performance is the basic thing behind the 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.

Wait_timeout = 60
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 the slash. With Windows, no one needs to search through directories to find option files for max_connections.

Out of memory miscalculation may vary:

max_connections=400
One can open the ini files with any text editor. The options file may contain multiple sections marked in brackets. Use the 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.

Information_schema.innodb_buffer_page =0.0000000
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 the low performance.
Innodb_flush_method = normal
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.

Innodb_flush_log_at_trx_commit= 2
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.
  • 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,
Innodb-log-file-size variable – This configuration controls the size of the log file. It is also known as redo file. Log files record each variable to the database including UPDATE, DELETE nd INSERT commands.

Max-connections Variable – MySQL server keeps the connection to be remained open even when the application is not using it.

Max_connection=400
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 cache smaller queries. If you set the value too low it may not be cached the bigger queries. So you have to set the limit according to your own need.

query_cache_size = 128MB
query_cache_limit = 4MB
Innodb-flush-method variable – The default value is fdatasync. This value is common while you have a battery backup cache component.

Innodb_flush_method=6
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 with cp/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.

Binlog-row-event-max-size = #
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.

Log_bin_trust_function_creators
Was this answer helpful? 0 Users Found This Useful (0 Votes)