MySQL/MariaDB Configuration (/etc/my.cnf)

The following configuration options are recommended for MySQL and MariaDB.

max_allowed_packet - Determines the maximum size of data allowed in a table cell. 64M is generally a good value, unless larger files/blobs need to be stored, then it can be increased to values as large as 256M or even larger, without significant effects on performance.
innodb_log_buffer_size - The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value changed is 16MB. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O to improve performance. For related information, see InnoDB Memory Configuration, and Section 8.5.4, “Optimizing InnoDB Redo Logging”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. 32M is a good value to use for larger repositories and transactions.
innodb_buffer_pool_size - The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB. 2G is a good value for large repositories with at least 4GB of physical memory available to it. The maximum value depends on the CPU architecture; the maximum is 4G on 32-bit systems and 16 Exabytes on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.
A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary. On a server that runs the database and application, a buffer pool size of about 50% of the machine's physical memory size is more appropriate.
- Competition for physical memory can cause paging in the operating system.
- InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.
- Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
- The time to initialize the buffer pool is roughly proportional to its size. On instances with large buffer pools, initialization time might be significant. To reduce the initialization period, you can save the buffer pool state at server shutdown and restore it at server startup. See Section 14.6.3.8, “Saving and Restoring the Buffer Pool State”.
When you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128 MB.

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter the buffer pool size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than the specified buffer pool size.

innodb_buffer_pool_size can be set dynamically, which allows you to resize the buffer pool without restarting the server. The Innodb_buffer_pool_resize_status status variable reports the status of online buffer pool resizing operations. See Section 14.6.3.2, “Configuring InnoDB Buffer Pool Size” for more information.
innodb_log_file_size - The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default value is 48MB. 768M is a good value for large repositories. This option does not impact the amount of memory MySQL uses.
Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration.

The minimum innodb_log_file_size value was increased from 1MB to 4MB in MySQL 5.7.11.

For related information, see InnoDB Log File Configuration. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

OS Open File Limit

Most default Linux installations limit the maximum number of files MySQL can open to 1024. That limit must be increased to support larger table_open_cache values, otherwise MySQL will automatically scale back the value. When MySQL does scale back the value, it will log a warning (typically to /var/log/mysql/mysql.log or /var/log/mariadb/mariadb.log).

In MySQL, to see what the current open_files_limit:
SHOW GLOBAL VARIABLES LIKE 'open%';

To increase the open_files_limit:

Create the a MariaDB service configuration directory with:
mkdir /etc/systemd/system/mariadb.service.d/
Create/edit /etc/systemd/system/mariadb.service.d/limits.conf and add the following lines:
[Service] LimitNOFILE=200000
Reload the daemon with:
systemctl daemon-reload
Restart MariaDB with:
systemctl restart mariadb
In MySQL:
SHOW GLOBAL VARIABLES LIKE 'open%';

Custom Functions

webCOMAND installs custom MySQL functions like FIND_ANY_IN_SET() automatically when the repository package is installed. To install the repository package manually from the CLI:

php comand.php io_comand_package install -p io_comand_repo

Or, to specifically just install the custom functions manually from the CLI:

php /var/www/webcomand/packages/io_comand_repo/install/functions/install.php

Example

Here is an example my.cnf

[mysqld]
# the following lines are standard, but no need to change if your
# configuration is different.
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

# Only listen on localhost (do not allow remote connections)
bind-address = 127.0.0.1

# Allow large blob handling (up to 16 Megabytes)
# May want to increase this value to as much as 256M
# Ensure related PHP configurations are updated to match
max_allowed_packet = 64M

# Allow larger GROUP_CONCAT results (1024 is default)
group_concat_max_len = 65535

# This is a good configuration for systems with 4GB memory.
#
# NOTE: if innodb_log_buffer_size or innodb_log_file_size change,
# /var/lib/mysql/ib_logfile* must be renamed/removed between the
# next database stop and start.
#innodb_log_buffer_size  = 16M
#innodb_log_file_size    = 1G
#innodb_buffer_pool_size = 128M

# This is a good config for systems with 8GB memory, to increase
# size of InnoDB log buffer and file sizes to accommodate larger
# blobs.
#
# NOTE: if innodb_log_buffer_size or innodb_log_file_size change,
# /var/lib/mysql/ib_logfile* must be renamed/removed between the
# next database stop and start.
#innodb_log_buffer_size  = 32M
#innodb_log_file_size    = 768M
#innodb_buffer_pool_size = 2G

# Increase open table cache to allow more tables to remain in memory
# before they are released, since COMAND repositories frequently
# have many more tables than is typical in typical databases.
table_open_cache = 5000

# Configure default database collation, connection and character set to UTF8
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8

# make table names case-insensitive
lower_case_table_names = 1

# reduce full text search minimum word length matches to
# match on words that are as short as two characters long
ft_min_word_len = 2

[mysqld]
max_allowed_packet = 64M