MySQL Performance Tuning Tips And Techniques.

As every one knows that, Performance tuning is the prominent task of DBA’s. There could be different ways to do performance tuning,and you could see lot of blogs talking about couple of memory related variables to be tuned, and suggests that memory allocation should be in some % based on available memory.

 I actually don’t believe that if we tune and allocate memory based on some % of selected variables is called the performance tuning or we can expert the performance as the docs says, its all depends on the kind of work load and the type of application and the system on which mysql is running.

Performance tuning is not just a task that you can do it in some time, actually it is a continuous process.  As mentioned above there are different ways to do performance tuning, which comprises the following at a high level

1) Operating system tuning
2) Storage Tuning
3) MySQL Server Tuning

When it comes to Operating system and Storage side there could be different parameters that should consider, like the Operating system, server configuration (CPU,Memory,Disk .etc), type of storage (Local,SAN,NAS .etc) and type of application and workload etc. At this point of time I would like to focus on MySQL Server Tuning rather that Operating System and Storage Tuning, and very soon will write one post to cover those 2 topics with respect to MySQL.

Coming to the MySQL performance tuning, as discussed above performance tuning means allocating the right memory to the right parameters. We could see lot of blogs talking about performance tuning mainly focus on few MySQL parameters and % of memory to be allocate to each one of them in general. Just by allocating the memory to those parameters we can’t say that performance tuning is done or the DB server configured to with the best configuration, because that is just predicted configuration based on the type of application and the work load. Though the initial configuration performs better for your DB server while the data size growing the performance tuning issues will come for sure, thats the reason why I mention “Performance Tuning is a continuous process”.

You might be having a question that, then how to find out the bottleneck of MySQL server and do actual MySQL Performance Tuning. That was my question when I was started learning and now I have the answer and would like to share with you.

MySQL Server Performance Tuning : The best / right way of understanding performance tuning bottleneck of MySQL is to check the global status of MySQL Server and try to understand the status variables and the relationship between them, and start tuning corresponding variables.

Lets look at couple of MySQL status variables and understand some performance tuning tips.

InnoDB Buffer Pool Size : To decide whether the innodb buffer pool size is optimal or not for InnoDB workloads , look at the global status of these variables Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests calculate the % of reads that are going to disk and adjust the buffer pool size accordingly.
          NOTE : Initially all the data has to read from the disk as part of warmup process you should consider that before calculate the % of disk reads, and you can’t provide as much memory as the DB size is the optimal to be selected based on the available resources and application workload. If 70-80% of reads served from the memory then that is good enough for a bigger Databases.
 
Key Buffer Size : To check whether key buffer size is optimal or not for MyISAM workload, look at the global status of Key_reads / Key_read_requests. As explained above if 70-80% of reads servers from the memory then it will be good for the bigger Databases.

InnoDB Log Buffer Size :  To tune the optimal value for innodb log buffer, check the global status of Innodb_log_writes / Innodb_log_write_requests, calculate the % and adjust the log buffer size accordingly. Consider the limitation on configuring bigger innodb log buffers and tune accordingly.

Tmp Table Size / Max Heap Table Size : To get the optimal value for temporary table, check the global status of Created_tmp_disk_tables / Created_tmp_tables, calculate the % of tables gets created on the disk and tune this variable accordingly.

Thread Cache Size : To get the optimum value for the the thread cache, check the global status of Threads_created and adjust the threads_cache accordingly.

Table Open Cache : To get the optimum value for table open cache, check the global status of Open_tables / Opened_tables , calculate the % and adjust the Table Open Cache accordingly.

The same process applies to all other major variables, Make sure that you are completely understand how it works and the consequence of these changes then only touch the production systems.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers

MySQL Plugins Installation, Uninstalling Plugins And Types Of Installations.

Through this post I would like to walk you through Plugin installation, Uninstalling Plugins and various types of plugin installations.

MySQL provides a great flexibility to its users to control and configure their DB servers based on their own requirements.

MySQL 5.1 and up supports a plugin API that enables creation of server components. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server.

Go through MySQL Plugin API for more information.

PREREQUISITE : Make sure that you have copied required plugin libraries to the plugin directory, and corresponding plugin configuration parameters have been added to configuration file.

Coming to the installation types, we can do plugin installation in the following two ways :

1)  Plugins installed with the INSTALL PLUGIN statement, which is a permanent method of installing a plugin.

    A plugin that is located in a plugin library file can be loaded at run time with the INSTALL PLUGIN statement. The statement also registers the plugin in the mysql.plugin table to cause the server to load it on subsequent restarts. For this reason, INSTALL PLUGIN requires the INSERT privilege for the mysql.plugin table.

    EX : mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name’;
   
    This is the statement to be used to install a plugin. As mentioned above in “PREREQUISITE” section make sure that plugin library placed on the plugin directory provided in the configuration file.

    Login to the DB instance and run the above statement. Suppose If you are installing LDAP authentication plugin then we have to place “auth_ldap.so” in the plugin directory and execute the following statement.

        mysql> INSTALL PLUGIN auth_ldap SONAME 'auth_ldap.so';
    
    INSTALL PLUGIN also registers the plugin by adding a line that indicates the plugin name and library file name to the mysql.plugin table. At server startup, the server loads and initializes any plugin that is listed in the mysql.plugin table.

    Once if the plugin installation is done, then you can go ahead and add the plugin specific configuration parameters to configuration file.

    To check the list of installed plugins run “show plugins;” it will display the list of plugins running on the Instance.
   
    For more information go through Installing Plugins.

2) The Second way of doing it is load the plugin at the startup of MySQL instance itself using --plugin_name[=value] startup option.

     A plugin that is located in a plugin library file can be loaded at server startup with the --plugin-load option. Normally, the server enables the plugin at startup, although this can be changed with the --plugin_name option.

    The option value is a semicolon-separated list of name=plugin_library pairs. Each name is the name of the plugin, and plugin_library is the name of the shared library that contains the plugin code. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. Each library file must be located in the directory named by the plugin_dir system variable.

    This option does not register any plugin in the mysql.plugin table. For subsequent restarts, the server loads the plugin again only if --plugin-load is given again. That is, this option effects a one-time installation that persists only for one server invocation.

    If the server knows about a plugin when it starts (for example, because the plugin is named using a --plugin-load option or registered in the mysql.plugin table), the server loads and enables the plugin by default. It is possible to control activation for such a plugin using a --plugin_name[=value] startup option named after the plugin.

    EX : Suppose If you are installing LDAP authentication plugin then we have to place “auth_ldap.so” in the plugin directory and add “--plugin-load=auth_ldap.so” configuration parameter to configuration file.

    For more information go through Installing Plugins.

Uninstalling Plugins:

    A plugin known to the server can be uninstalled to disable it at run time with the UNINSTALL PLUGIN statement. The statement unloads the plugin and removes it from the mysql.plugin table if it is registered there. For this reason, UNINSTALL PLUGIN statement requires the DELETE privilege for the mysql.plugin table. With the plugin no longer registered in the table, the server will not load the plugin automatically for subsequent restarts.

    UNINSTALL PLUGIN can unload plugins regardless of whether they were loaded with INSTALL PLUGIN or --plugin-load.

    UNINSTALL PLUGIN is subject to these exceptions:

            It cannot unload plugins that are built in to the server. These can be identified as those that have a library name of NULL in the output from INFORMATION_SCHEMA.PLUGINS or SHOW PLUGINS.

            It cannot unload plugins for which the server was started with --plugin_name=FORCE_PLUS_PERMANENT, which prevents plugin unloading at runtime. These can be identified from the LOAD_OPTION column of the INFORMATION_SCHEMA.PLUGINS table.

    For more information go through Installing & Un installing Plugins.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers