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