1. cPanel & WHM Documentation
  2. WHM
  3. SQL Services
  4. Edit SQL Configuration
databases whmui

Edit SQL Configuration

Valid for versions 114 through the latest version

Version:

104

114

Last modified: June 13, 2024

Looking for this interface?
Note:

Your hosting provider can enable or disable this interface for resellers in WHM's Edit Reseller Nameservers and Privileges interface (WHM >> Home >> Resellers >> Edit Reseller Nameservers and Privileges).

Overview

This interface allows you to make changes to your MySQL® or MariaDB® configuration. Changes you make in this interface will cause the MySQL or MariaDB services to restart.

Note:

  • In cPanel & WHM version 120 and later, we renamed this interface to Edit Database Configuration and its section in the WHM interface to Database Services.

  • Not all settings are available in all versions of MySQL or MariaDB.

  • This interface accepts values with the following file size units:

    • K — Kilobytes

    • M — Megabytes

    • G — Gigabytes

    • P — Petabytes

    • E — Exabytes

    • This interface defaults to bytes if the user does not specify a file size unit.

Database Optimization

This interface suggests optimized database values for you based on your MySQL or MariaDB usage. You can also manually apply the suggested optimizations to individual settings.

Important:

These optimizations are general suggestions and may not result in increased database performance for all use cases.

Note:
  • Click the document () icon to restore a setting to its default values.
  • Click the arrow () icon to revert a setting to its previous value.

To apply the suggested optimizations for your database, perform the following steps:

  1. Click the Review Suggestions button. The Confirm Changes interface will appear.
  2. Click the Save button if you are satisfied with the suggested database settings.

To manually apply optimization suggestions to individual settings, perform the following steps:

  1. Click the gauge () icon. The Optimization menu will appear.
  2. Click Apply Optimization.
  3. Click the Save button located at the bottom of the Edit SQL interface.

Configuration Settings for MySQL and MariaDB

You can use this interface to adjust the following settings:

Setting
Description
Values
 Default
General Logging This setting enables or disables the general query log. The query log contains information for data processed by the MySQL server. This setting is available in MySQL and MariaDB.
  • Enable
  • Disable
 Disable
General Log File This setting allows you to specify a custom name for the general log file. This setting is available in MySQL and MariaDB. A text string. server_hostname.log
InnoDB Buffer Pool Chunk Size This setting defines the size of the buffer that InnoDB uses to write to the log files on disk. This setting is available in MySQL and MariaDB.
Note:
MariaDB only supports this setting on versions 10.2 and later.
  • Minimum value: 1M
  • Maximum value: 8E
 128M
InnoDB Buffer Pool Instances This setting specifies the number of InnoDB buffer pool instances. InnoDB Buffer Pool Instances help reduce contention and improve concurrency. This setting is available in MySQL and MariaDB.
Note:
  • This setting **only** takes effect when setting the InnoDB buffer pool size to 1GB or more.
  • MariaDB deprecated this setting in version 10.5.
  • MariaDB removed this setting in version 10.6.
  • Minimum value: 1
  • Maximum value: 64
 1
InnoDB Buffer Pool Size This setting defines the InnoDB buffer pool size. The InnoDB buffer pool is the memory area where InnoDB caches table and index data. This setting is available in MySQL and MariaDB.
  • Minimum value: 5M
  • Maximum value: 8E
  • A small buffer pool can cause pages to leave and join the buffer pool too often.
  • 
A buffer pool that is too large may cause swapping due to competition for memory.

  • We recommend keeping the buffer pool size between 50 to 85% of the total physical memory available.
    		 128M
    InnoDB Log Buffer Size This setting allows you to specify the InnoDB log buffer size. The InnoDB log buffer holds data in memory before the system writes it to the log files. This setting is available in MySQL and MariaDB.
    • Minimum value: 5M
    • Maximum value: 8E
    		 16M
    InnoDB Log File Size The InnoDB log file defines the size of each log file. This setting is available in MySQL and MariaDB.
    • Minimum value: 4M
    • Maximum value: 16E
    		 48M
    InnoDB Sort Buffer Size This setting defines the size of the InnoDB sort buffer. The InnoDB sort buffer sets the following values:
    • The sort buffer size for online DDL operations that create or rebuild secondary indexes.
    • The length of the temporary log file when recording concurrent DML.
    • The size of the temporary log file read buffer and write buffer.
    This setting is available in MySQL and MariaDB.
    • Minimum value: 64K
    • Maximum value: 64M
    		 1M
    Interactive Timeout This setting allows you to specify the number of seconds the server waits for an idle connection before closing it. This setting is available in MySQL and MariaDB.
    • Minimum value: 1
    • Maximum value: 31536000
    		 28
    Join Buffer Size This setting defines the minimum size of the buffer for plain index scans, range index scans, and joins that perform full table scans. This setting is available in MySQL and MariaDB.
    • Minimum value: 128
    • Maximum value: 15E
    		 256K
    Key Buffer Size The key buffer size is the size of the buffer used for index blocks. The key buffer is also known as the key cache. The key buffer holds the index blocks used by MyISAM tables. This setting is available in MySQL and MariaDB.
    • Minimum value: 8
    • Maximum value: 3G
    		 128M
    Error Log File Name This setting allows you to specify the name of the Error Log. This setting is available in MySQL and MariaDB. A text string. /var/log/mysqld.log
    Error Log Verbosity This setting allows you to specify the level of verbosity in the error log.
    Note:
    This setting is only available on MySQL version 5.7 or later. This setting is not available on MariaDB.
    • 1
    • 2
    • 3
    For more information, read the MySQL Log Error Verbosity documentation.    		 3
    Log Warnings This setting allows you to specify which additional warnings are logged. Larger numbers increase verbosity. This setting is available in MySQL and MariaDB. A valid log warning value. For more information about log warning values, read the MySQL or MariaDB log warning documentation. 2
    Log Output This setting allows you to specify the format of the general log and slow query log output. More than one setting can be active. This setting is available in MySQL and MariaDB.
    • File
    • Table
    • None
    For more information about log output values, read the MySQL or MariaDB log out put documentation.    		 File
    Long Query Time This setting specifies the maximum number of seconds a query can run before it is logged to the slow query log file. This setting is available in MySQL and MariaDB.
    Note:
    This setting accepts decimal values and has microsecond precision.
    • Minimum value: 0
    • Maximum value: 30
    		 10
    Max Allowed Packet This setting allows you to specify the maximum size of one packet or any generated/intermediate string. This setting is available in MySQL and MariaDB.
    • Minimum value: 1K
    • Maximum value: 1G
    		 256M
    Max Connect Errors This setting allows you to specify the number of failed connection attempts before the server blocks the connection. This setting is available in MySQL and MariaDB.
    Note:
    This setting does not protect against brute force attempts.
    • Minimum value: 1
    • Maximum value: 4294967295
    		 100
    Max Connections This setting allows you to specify the maximum number of concurrent client connections. This setting is available in MySQL and MariaDB.
    • Minimum value: 0
    • Maximum value: 100000
    		 151
    Max Heap Table Size This setting allows you to specify the maximum size to which user-created MEMORY tables are permitted to grow. This setting is available in MySQL and MariaDB.
    • Minimum value: 16K
    • Maximum value: 3G
    		 3G
    Open Files Limit This setting allows you to specify the maximum number of file descriptors available for use. This setting is available in MySQL and MariaDB.
    • Minimum value: 0
    • Maximum value: 4294967295
    		 40
    Performance Schema The Performance Schema is a tool to help a database administrator do performance tuning. This setting is available in MySQL and MariaDB.
    Note:
    All MySQL Performance Schema settings also work in MariaDB.
    • Enable
    • Disable
    		 Disable
    Query Cache Size This setting allows you to specify the amount of memory reserved for caching query results. This setting is available in MySQL and MariaDB.
    Note:
    MySQL deprecated this setting in version 5.7.
    • Minimum value: 0
    • Maximum value: 16E
    		 1M
    Query Cache Type This setting determines query cache behavior for all clients that connect to the server. This setting is available in MySQL and MariaDB.
    • 0
    • 1
    • 2
    For more information on query cache type values, read the MySQL or MariaDB query cache type documentation.    		 0
    Read Buffer Size This setting does the following:
    • Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size for each table it scans.
    • Determines the memory block size for MEMORY tables.
    This setting is available in MySQL and MariaDB.
    • Minimum value: 8K
    • Maximum value: 1G
    		 128K
    Read Random Buffer Size This setting allows you to specify the size of the read random buffer. The read random buffer reads rows from the MyISAM table in sorted order after a key sort. This setting is available in MySQL and MariaDB.
    • Minimum value: 8K
    • Maximum value: 1G
    		 256K
    Slow Query Log
    Note:
    In MariaDB version 10.11 and later, this setting is Log Slow Queries.
    		This setting allows you to enable or disable the slow query log. Enable the slow query log to find queries that take a long time to execute. The slow query log consists of the following: This setting is available in MySQL and MariaDB.
    • Enable
    • Disable
    		 Disable
    Slow Query Log File Name This setting allows you to specify the name or full path of the slow query log file. The name or full path of the slow query log file. server_hostname-slow.log
    Sort Buffer Size This setting defines the following:
    • The sort buffer size for online DDL operations that create or rebuild secondary indexes.
    • The amount by which the temporary log file is extended when recording concurrent DML during an online DDL operation, and the size of the temporary log file read buffer and write buffer.
    For more information about sort buffer size, read the MySQL or MariaDB documentation.
    • Minimum value: 32K
    • Maximum value: 16E
    		 256K
    SQL Mode MySQL and MariaDB can apply different SQL Modes depending on the value of this setting. A comma-delimited list of modes to activate. For more information about SQL Mode values, read the MySQL or MariaDB SQL Mode documentation. Click the link to download the default settings.
    Thread Cache Size This setting allows you to specify the number of threads the server stores in a cache to use. This setting is available in MySQL and MariaDB
    • Minimum value: 0
    • Maximum value: 16K
    		 256
    Temporary Table Size This setting allows you to specify the size of internal, in-memory, temporary tables. The temporary table size does not apply to user-created MEMORY tables. This setting is available in MySQL and MariaDB.
    Note:
    The value of Max Heap Table Size will override this setting if it is a smaller value.
    • Minimum value: 1K
    • Maximum value: 3G
    		 16M
    Wait Timeout This setting allows you to specify the maximum number of seconds the server waits on an idle connection before closing it. This setting is available in MySQL and MariaDB
    • Minimum value: 1
    • Maximum value: 31536000
    		 28800

    Additional Documentation