Navigation

    Kopano
    • Register
    • Login
    • Search
    • Categories
    • Get Official Kopano Support
    • Recent
    Statement regarding the closure of the Kopano community forum and the end of the community edition

    Many MySQL lock wait timeout messages

    General Discussion
    mysql mariadb
    2
    2
    416
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • decibel83
      decibel83 last edited by

      Hi,
      I’m using Kopano 8.7.16 on Debian Buster with MariaDB 10.3.

      I’m getting many many errors about lock wait timeouts on MySQL:

      Fri May 14 11:20:38 2021: [error  ] SQL [00408444] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:21:58 2021: [error  ] SQL [00408419] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:22:40 2021: [error  ] SQL [00408386] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:23:08 2021: [error  ] SQL [00408444] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:24:01 2021: [error  ] SQL [00408327] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:24:42 2021: [error  ] SQL [00408288] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:26:02 2021: [error  ] SQL [00408327] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:26:44 2021: [error  ] SQL [00408290] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:27:20 2021: [error  ] SQL [00408386] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:28:46 2021: [error  ] SQL [00408288] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:30:48 2021: [error  ] SQL [00408419] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:31:32 2021: [error  ] SQL [00408327] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:32:50 2021: [error  ] SQL [00408290] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:33:33 2021: [error  ] SQL [00408288] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:34:52 2021: [error  ] SQL [00408386] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:35:35 2021: [error  ] SQL [00408327] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:36:54 2021: [error  ] SQL [00408444] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:37:45 2021: [error  ] SQL [00408290] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:39:48 2021: [error  ] SQL [00408444] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:40:05 2021: [error  ] SQL [00408386] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:42:07 2021: [error  ] SQL [00408288] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:42:31 2021: [error  ] SQL [00408290] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699385) LOCK IN SHARE MODE"
Fri May 14 11:44:09 2021: [error  ] SQL [00408444] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:45:20 2021: [error  ] SQL [00408188] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"
Fri May 14 11:46:11 2021: [error  ] SQL [00408419] result failed: Lock wait timeout exceeded; try restarting transaction, Query: "SELECT 1 FROM properties WHERE hierarchyid IN(8699383) LOCK IN SHARE MODE"

      I don’t know if this is related, but my Kopano installation seems to be very slow sometimes in a random manner, because users reports me that they cannot login or browse messages.

      I configured MariaDB following the Kopano performance tuning hint.

      MariaDB is configured this way into /etc/mysql/conf.d/kopano.cnf:

      [mysqld]
# This can be optionally uncommented. (check if supported on your mysql version)
transaction-isolation = READ-COMMITTED

# Careful this might also needs binlog_format=ROW if you are using a binlog.
#verify# if binlog_format=statement.

# Should be the minimum amount of kopano(server.cfg) threads(default 8) + thread_limit(default 40) + 9.
max_connections = 57

# Best value is 32M, for smaller environments 16M is ok too.
max_allowed_packet = 32M

# should not be set higher than this, since it has to be allocated before being used
tmp_table_size = 64M
max_heap_table_size = 64M

# max size 2M (regardless of size of environment)
sort_buffer_size = 2M
join_buffer_size = 2M

# Good value is mumber of threads configured + 1
# nice writeup about actual scaling of thread_cache_size http://anothermysqldba.blogspot.de/2013/09/mysqloptimization-tip-threadcachesize.html
# #verify# number of threads to be dermineds
thread_cache_size = 9

# Only available on mysql 5.5 and higher as above. Good figure the amount is cores/2, depending of usage give read more threads.
innodb_write_io_threads = 4
innodb_read_io_threads = 4

# (Kopano tables + max mysql connections) * 10, for example (26 + 24) * 10
# #verify# can be checked for example with "mytop". when you have a low "key efficiency" table_cache has to be increased
# table_open_cache was known as table_cache in MySQL 5.1.2 and earlier.
table_open_cache = 500 # DO NOT SET IT TOO HIGH! https://www.percona.com/blog/2009/11/16/table_cachenegative-scalability/

# In order to use enable_sql_procedures = yes in Kopano server.cfg 512K is needed at least.
thread_stack = 512K

# RHEL6 do no use any query cache size !! kills performance
query_cache_size = 64M
query_cache_limit = 2M

# On a shared system (running both Kopano 30% and Mysql 50% and 20% for system Apache etc.) only use 30% of total ram.
innodb_buffer_pool_size = 16G

# On slow storage 100M is recommended
# syntax is file_name:file_size[:autoextend[:max:max_file_size]]
#innodb_data_file_path = ibdata1:50G:autoextend

# On fast like SSD storage or multipath innodb_data_file_path = ibdata1:100G;ibdata2:100G:autoextend
# increment ibdata with 1000MB each time (default is 4MB)
innodb_autoextend_increment = 1000

# Innodb file per table: Kopano tells to disable it but I'd prefer to keep
# it enabled!
#innodb_file_per_table = OFF
innodb_file_per_table = ON

# Be careful 1 second of log (high performance = 2 , normal = 1)
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M

# The threshold compared to innodb (25% of buffer pool size at max 1 GB)
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
#innodb_lock_wait_timeout = 120
innodb_lock_wait_timeout = 1200

# in case of remote storage
#innodb_flush_method = O_DIRECT
# otherwise (storage is local)
innodb_flush_method = O_DSYNC

      Could you help me to understand and solve this problem, please?

      Thank you very much!
      Bye

      1 Reply Last reply Reply Quote 0
      • bob4os
        bob4os last edited by

        If your database is only used on this host, you may also use:

        skip-external-locking
skip-name-resolve

        57 connections is not a whole lot, I would not limit that anyway - the default is 150.
        “query_cache_size” and “query_cache_limit” with InnoDB are a difficult topic - it is usually better to set “0”.

        You may also use multiple buffer pools for better concurrency with:

        innodb_buffer_pool_instances

        I usually match my number of cpu cores with the number of buffer pools and io-threads.
        The overall buffer pool size is calculated “innodb_buffer_pool_instances” * “innodb_buffer_pool_size”.

        Why are you increasing “innodb_lock_wait_timeout” ?

        More is not always better…

        1 Reply Last reply Reply Quote 0
        • First post
          Last post