Stop MariaDB from locking proces

Question

We run a CentOS DirectAdmin install with MariaDB 10.2.14 where on Magento is installed.

Currently our DB locks very often when a process runs, so all other processes are waiting until the current process finishes. This is quite a problem, because for example, also the adding to cart process is waiting in that case and people can not order.

How can we prevent the DB from being locked so long and solve this issue?

Server:

6x Intel Xeon
32GB RAM
500GB SSD

My.cnf:

[mysqld]

bind-address = 127.0.0.1
local-infile=0
innodb_file_per_table=1
innodb_file_format=barracuda

slow_query_log = 1
slow_query_log_file=/var/log/mysql-log-slow-queries.log

key_buffer = 250M
key_buffer_size = 250M
max_allowed_packet = 128M
table_cache = 512
sort_buffer_size = 7M
read_buffer_size = 7M
read_rnd_buffer_size = 7M
myisam_sort_buffer_size = 64M
tmp_table_size = 190M
query_cache_type = 1
query_cache_size = 220M
query_cache_limit = 512M
thread_cache_size = 150
max_connections = 225
wait_timeout = 300
innodb_buffer_pool_size = 7G
max_heap_table_size =180M
innodb_log_buffer_size = 36M
join_buffer_size = 32M
innodb_buffer_pool_instances = 7

long_query_time = 15
table_definition_cache = 4K
open_files_limit = 60K
table_open_cache = 50767
innodb_log_file_size= 128M
innodb_lock_wait_timeout = 700

Answer 1

Suggestions to consider for your my.cnf [mysqld] section

The following lead with # to disable or REMOVE to allow defaults to serve your requirements Some of these are already mentioned by Rick James in earlier comment.

. key_buffer . key_buffer_size . table_cache . sort_buffer_size . read_buffer_size . read_rnd_buffer_size . MyISAM_sort_buffer_size . join_buffer_size . long_query_time . innodb_lock_wait_timeout

make these changes or add lines to your my.cnf for

query_cache_type=0  # from 1  to turn OFF QC and conserve CPU cycles
query_cache_size=0  # from 220M to conserve RAM for more useful work
query_cache_limit=0  # from 512M to conserve RAM for more useful work
thread_cache_size=100  # from 150  V8 refman suggested CAP to avoid OOM
innodb_lru_scan_depth=100  # from 1024 to minimum to conserve CPU every SECOND
innodb_flush_neighbors=0  # from 1 no need to waste CPU cycles when using SSD
innodb_io_capacity_max=10000  # from 2000 since you have SSD
innodb_io_capacity=5000  # from 200 to use more of your SSD capability

for additional assistance, please check my profile, clk Network profile for contact info.

Answer 2

These are deprecated; their names have changed. Remove them:

key_buffer = 250M
table_cache = 512

These are higher than they should be:

key_buffer_size = 250M
query_cache_size = 220M
thread_cache_size = 150
long_query_time = 15
table_definition_cache = 4K
table_open_cache = 50767
innodb_lock_wait_timeout = 700

The last one may be the villain. It implies that you have some loooong transactions. This is a design flaw in your code. Find a way to make the transactions shorter. If you need help, describe what you are doing to us.

I feel that 5 is plenty long for a transaction.

Do you sometimes get this?

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Answer 3

MySQL will wait a certain amount of time for the lock to be removed before it gives up and throws that error. If you are able to track when you are seeing these error messages down to any consistent time of the day, you should look at what else the server is doing at that time - for instance is a database backup running. By doing this you should be able to narrow down possibilities for what processes could be creating the lock although it's not always that straight forward to do - likely to be a bit of trial and error.

Sometimes deadlock issues can be caused on the database.The reason behind this issue is if you are running a lot of custom scripts and killing the scripts before the database connection gets chance to close.

If you can login to MySQL from CLI and run the following command

SHOW PROCESSLIST;

you will get the following output

+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
|      Id        |   User     |             Host             |       db       | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| 6794372 | db_user| 111.11.0.65:21532 | db_name| Sleep          | 3800 |          | NULL |          0       |          0                   |          0             |
| 6794475 | db_user| 111.11.0.65:27488 | db_name| Sleep         | 3757 |          | NULL |          0        |          0                   |          0             |
| 6794550 | db_user| 111.11.0.65:32670 | db_name| Sleep         | 3731 |          | NULL |          0        |          0                   |          0             |
| 6794797 | db_user| 111.11.0.65:47424 | db_name | Sleep         | 3639 |          | NULL |          0       |          0                   |          0             |
| 6794909 | db_user| 111.11.0.65:56029 | db_name| Sleep         | 3591 |          | NULL |          0       |          0                   |          0              |
| 6794981 | db_user| 111.11.0.65:59201 | db_name| Sleep         | 3567 |          | NULL |          0        |          0                   |          0             |
| 6795096 | db_user| 111.11.0.65:2390 | db_name| Sleep           | 3529 |          | NULL |          0        |          0                   |          0             |
| 6795270 | db_user| 111.11.0.65:10125 | db_name | Sleep         | 3473 |          | NULL |          0       |          0                   |          0             |
| 6795402 | db_user| 111.11.0.65:18407 | db_name| Sleep         | 3424 |          | NULL |         0         |          0                   |          0             |
| 6795701 | db_user| 111.11.0.65:35679 | db_name| Sleep         | 3330 |          | NULL |          0        |          0                   |          0             |
| 6800436 | db_user| 111.11.0.65:57815 | db_name| Sleep         | 1860 |          | NULL |          0       |          0                   |          0             |
| 6806227 | db_user| 111.11.0.67:20650 | db_name| Sleep         |  188 |          | NULL |          1        |          0                   |          0             |
| 6806589 | db_user| 111.11.0.65:36618 | db_name| Query        |   0    | NULL | SHOW PROCESSLIST |       0         |       0                 |       0       |
| 6806742 | db_user| 111.11.0.75:38717 | db_name| Sleep          |   0    |          | NULL |         0         |          0                    |          0            |
| 6806744 | db_user| 111.11.0.75:38819 | db_name| Sleep         |    0    |          | NULL |          61       |          61                  |          61         |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
15 rows in set (0.00 sec)

You can see as an example 6794372 the command is sleep and time is 3800. This is preventing other operations.

These processes should be killed 1 by 1 using the command.

KILL 6794372;

Once you have killed all the sleeping connections, things should start working as normal again