innodb_dedicated_server: Configure InnoDB Dedicated Server

Summary: in this tutorial, you will learn how to enable automatic configuration of key variables in InnoDB using innodb_dedicated_server variable.

InnoDB provides you with the option innodb_dedicated_server that allows for the automatic configuration of key variables.

You should only enable the innodb_dedicated_server if you have a MySQL instance running on a dedicated server where it can use all available system resources.

It’s not recommended to enable innodb_dedicated_server when you have a MySQL instance running on a server that shares system resources with other applications.

Enabling innodb_dedicated_server

First, open the MySQL configuration file (my.ini or my.cnf) using a text editor.

Next, locate the [mysqld] section where the server configurations are defined.

Then, add the following line to enable InnoDB dedicated server:

innodb_dedicated_server=ONCode language: SQL (Structured Query Language) (sql)

After that, save the configuration file.

Finally, restart the MySQL server for the change to take effect.

Verifying the configuration

First, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, show the innodb_dedicated_server variable:

SHOW GLOBAL VARIABLES LIKE 'innodb_dedicated_server';Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_dedicated_server | ON    |
+-------------------------+-------+
1 row in set (0.03 sec)Code language: JavaScript (javascript)

Summary

  • Enable innodb_dedicated_server for automatic configuration of the key variables in InnoDB.
Was this tutorial helpful?