1

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

Currently our DB locks very often when a proces runs, so all other processes are waining until the current proces finished. 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 beining locked 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
  • What do you actually see in the database (SHOW PROCESSLIST;) when this happens? Do you encounter errors, or just a freeze? Why do you have lock_wait_timeout = 700? Values here should typically be only a few seconds. – Michael - sqlbot Jun 13 '18 at 9:11
  • Additional information request, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report more helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Oct 11 '18 at 22:29
  • Have you made any progress with your locking situation? We would like to know. If not, please post information requested Oct 11, 2018 (plus SHOW FULL PROCESSLIST;) so we can help you. Thanks – Wilson Hauck Dec 15 '18 at 17:59
  • Please look at this Magento Customer Review instagram.com/p/Br8q3rDHw80 and consider viewing my profile, Network profile. – Wilson Hauck Mar 2 '19 at 11:26
  • @Henk-Z Are you still facing serious delays in response time? – Wilson Hauck Nov 28 '19 at 18:29
1

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

  • Sleeping connections will never cause locks unless you have a bug that is leaving transactions running or otherwise abandoning locks. This is not the correct solution. Also, the OP didn't mention what kind of locks were being observed or what error if any was being thrown. Some locks are waited indefinitely, others are not. – Michael - sqlbot Jun 12 '18 at 12:38
  • Then what is the reason for locks, once i have faced this issue and did the same as given in the answer. If you have solution then your answer are most welcome :) – Rahul Singh Jun 13 '18 at 5:09
  • I should clarify what I said, earlier. Sleeping connections will never cause locks... but they can hold locks, as any connection can. Your solution is valid if you can identity the correct connection to kill. Otherwise, this approach only temporarily resolves the issue. It is not an appropriate solution unless you understand the original cause. – Michael - sqlbot Jun 13 '18 at 9:08
  • 1
    You are likely missing connection CLOSE() function. 15 seconds of General Log would probably document the absence of QUIT that would be posted on connection CLOSE(). – Wilson Hauck May 10 '19 at 23:26
0

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

Lead with # spacebar the following to allow defaults vs overallocation for

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size

adjust the following:

query_cache_limit=1M  # from 512M for max size of 1 RESULT into QC
query_cache_min_res_unit=512  # from 4096 to store more RESULTS into QC
thread_cache_size=100  # from 150 for CAP see refman V8
innodb_lru_scan_depth=100  # from 1024 to conserve CPU time every second
innodb_flush_neighbors=0  # from ON to conserve CPU SSD has no rotational delay
innodb_purge_threads=4  # from 1 to expedite purge activity
innodb_flushing_avg_loops=10  # from 30 to reduce loop delays
max_write_lock_count=16  # to allow RD after nn write lcks vs 4 Billion lcks

please post progress from time to time,

for additional suggestions check my profile, Network profile for contact info.

  • @henk z Have you made any progress with Suggestions above? Please upvote/accept anything that has been helpful when you have time. Thanks – Wilson Hauck Jul 30 '18 at 1:04
  • @henk z Have you made any progress with Suggestions above? Please upvote/accept anything that has been helpful when you have time. Thanks – Wilson Hauck Aug 8 '18 at 19:48
  • @henkz Since you have not replied, we have to believe NONE of these suggestions have been applied or tried. Tell us what has been done and the results, please. – Wilson Hauck Dec 15 '18 at 18:01
  • @henk-z Happy Holidays. Have you made any progress with Suggestions above? Please upvote/accept anything that has been helpful when you have time. Thanks – Wilson Hauck Dec 18 '18 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.