MySQL performance acceleration

Hello, How to make the most performance settings on my server?

Server Info:
Webmin version: 1.981
Virtualmin version: 6.17
Processor information: AMD EPYC 7571, 8 cores
Real memory: 32 GB
Operating system: CentOS Linux 8.5.2111
Kernel and CPU: Linux 4.18.0-348.2.1.el8_5.x86_64 on x86_64
Disk: 128 GB SSD / 100 IOPS

Here are the settings I currently use
MySql Database Settings: /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 64M
thread_cache_size = 100
myisam_sort_buffer_size = 128M
read_rnd_buffer_size = 64M
table_open_cache = 512
max_allowed_packet = 4M
key_buffer_size = 7168M
max_connections = 15
table_cache = 32M
tmp_table_size = 32M
max_heap_table_size = 32M
innodb_buffer_pool_size = 4000M
innodb_buffer_pool_instances = 4
join_buffer_size = 56M
read_buffer_size = 56M
sort_buffer_size = 3584M

It takes hours to add a json data with 500 thousand data to the relevant table in the database. Is there any way to speed this up? The machine I use is really efficient.

Hi,

What is the size of stringifed JSON data?

What is the version of MySQL you’re using there? If you’re running older versions of MySQL, i.e. 5.6 or 5.7, it had a flaw in memory allocation when dealing with large values.

1 Like

Hi, I using MariaDB version 10.3.28 and PHP version 7.2.24 - PHP script execution mode FPM
JSON data size ± 10mb

The values for the following options seem like a culprit of a problem to me:

key_buffer_size = 7168M
innodb_buffer_pool_size = 4000M
sort_buffer_size = 3584M

I would suggest solving familiar kind of problems by resetting a config to defaults, meaning that removing those, mentioned above options from your current MariaDB config file, and restarting the service would most likely solve your issue.

In general, tweaking any kind of cache and buffers options should be done with care, as larger doesn’t mean faster. Most of the times it should be left intact, especially on the latest versions of MariaDB, as defaults would work just fine. If you have to make changes, consider initial default values, and don’t apply new, custom values that are thousands times larger.