Get the most out of your Centmin Mod LEMP stack
Become a Member

MariaDB Deadlock Issue

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by anthony parsons, Aug 7, 2018.

  1. anthony parsons

    anthony parsons Premium Member Premium Member

    77
    18
    8
    Feb 12, 2017
    Ratings:
    +22
    Local Time:
    8:58 AM
    Current yum stable
    10.1
    This isn't something I had prior to centminmod, so it is based somewhere in the my.cnf that is causing this. When running the cron to perform heavy actions, I get a whole lot of:

    Code:
    Server error log
    XF\Db\DeadlockException: Job XF:SearchIndex: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction src/XF/Db/AbstractStatement.php:212
    Generated by: Unknown account Aug 7, 2018 at 9:50 PM
    Stack trace
    INSERT  INTO `xf_search_index` (`content_type`, `content_id`, `title`, `message`, `metadata`, `item_date`, `user_id`, `discussion_id`) VALUES ('thread',87594,'Is it just me, or does this site seem like a control freak?','I\'ve been here a couple minutes and already watched as an admin cut off one person\'s thread for seeming to ask for \"therapy\" (actually advice).  In addition, there is a long list of members who were banned for like failing to follow orders and stuff.  Anyone wanna place bets on how long it takes me to trigger an admin?','_md_user_47159 _md_content_thread _md_node_28 _md_thread_87594 _md_prefix_30',1533558214,47159,87594),
    ('post',1471039,'','I highly doubt your interpretation of what occurs at staff level, is what actually occurred. This is a mental health forum, and to say a lot of crazy shit goes on here, is not coming close to scratching the surface of managing this community.\n\nWho is the admin you refer?\n\nMembers do not trigger staff. Staff moderate the community based on very public policy published here. There are no secrets, nothing hidden, just members who usually think they can do whatever they feel like, and get upset when they discover this is not their house, not their rules, and if they want to be here, be here by the rules published, otherwise, just leave without all the fuss and noise / don\'t join to begin with.','_md_user_1 _md_content_post _md_node_28 _md_thread_87594 _md_prefix_30',1533558214,1,87594),
    ('post',1471040,'','Wait.  This is a \"ticket\"?  I could have sworn is was a post.','_md_user_47159 _md_content_post _md_node_28 _md_thread_87594 _md_prefix_30',1533558214,47159,87594),
    ('post',1471041,'','[QUOTE=\"anthony, ticketMessage: 4676, member: 1\"]I highly doubt your interpretation of what occurs at staff level, is what actually occurred. This is a mental health forum, and to say a lot of crazy shit goes on here, is not coming close to scratching the surface of managing this community.\n\nWho is the admin you refer?\n\nMembers do not trigger staff. Staff moderate the community based on very public policy published here. There are no secrets, nothing hidden, just members who usually think they can do whatever they feel like, and get upset when they discover this is not their house, not their rules, and if they want to be here, be here by the rules published, otherwise, just leave without all the fuss and noise / don\'t join to begin with.[/QUOTE]\n\nYeah, that seems pretty triggered to me, Anthony.  You\'re mad.  You\'re threatening and controlling.  Why is that?  Do you want members on this site, or do you want to feel like you own the house?','_md_user_47159 _md_content_post _md_node_28 _md_thread_87594 _md_prefix_30',1533558214,47159,87594),
    ('post',1471042,'','Banned. No tolerance for trolling new members.','_md_user_1 _md_content_post _md_node_28 _md_thread_87594 _md_prefix_30',1533558214,1,87594) ON DUPLICATE KEY UPDATE title = VALUES(title), message = VALUES(message), metadata = VALUES(metadata), item_date = VALUES(item_date), user_id = VALUES(user_id), discussion_id = VALUES(discussion_id)
    ------------
    
    #0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1213, '40001')
    #1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1213, '40001')
    #2 src/XF/Db/AbstractAdapter.php(79): XF\Db\Mysqli\Statement->execute()
    #3 src/XF/Db/AbstractAdapter.php(206): XF\Db\AbstractAdapter->query('INSERT  INTO `x...')
    #4 src/XF/Search/Source/MySqlFt.php(107): XF\Db\AbstractAdapter->insertBulk('xf_search_index', Array, false, 'title = VALUES(...')
    #5 src/XF/Search/Source/AbstractSource.php(35): XF\Search\Source\MySqlFt->flushBulkIndexing()
    #6 src/XF/Search/Search.php(98): XF\Search\Source\AbstractSource->disableBulkIndexing()
    #7 src/XF/Search/Search.php(62): XF\Search\Search->disableBulkIndexing()
    #8 src/XF/Search/Search.php(73): XF\Search\Search->indexEntities('post', Object(XF\Mvc\Entity\ArrayCollection))
    #9 src/XF/Job/SearchIndex.php(43): XF\Search\Search->indexByIds('post', Array)
    #10 src/XF/Job/Manager.php(241): XF\Job\SearchIndex->run(6.9316010475159)
    #11 src/XF/Job/Manager.php(187): XF\Job\Manager->runJobInternal(Array, 6.9316010475159)
    #12 src/XF/Job/Manager.php(76): XF\Job\Manager->runJobEntry(Array, 6.9316010475159)
    #13 job.php(15): XF\Job\Manager->runQueue(false, 8)
    #14 {main}
    Request state
    array(4) {
      ["url"] => string(8) "/job.php"
      ["referrer"] => string(63) "https://www.myptsd.com/admin.php?threads/batch-update&success=1"
      ["_GET"] => array(0) {
      }
      ["_POST"] => array(0) {
      }
    }
    my.cnf looks like:

    Code:
    [client]
    socket=/var/lib/mysql/mysql.sock
    
    [mysql]
    max_allowed_packet = 64M
    
    [mysqld]
    ignore_db_dirs=cmsetiofiotest
    local-infile=0
    ignore_db_dirs=lost+found
    character-set-server=utf8
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    #bind-address=127.0.0.1
    # optimized my.cnf for MariaDB 5.5.x
    # by eva2000
    # vbtechsupport.com
    
    tmpdir=/home/mysqltmp
    
    innodb=ON
    #skip-federated
    ##skip-pbxt
    ##skip-pbxt_statistics
    #skip-archive
    #skip-name-resolve
    #old_passwords
    back_log = 512
    max_connections = 500
    key_buffer_size = 256M
    myisam_sort_buffer_size = 256M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K
    table_definition_cache = 8192
    table_open_cache = 4096
    thread_cache_size = 256
    wait_timeout = 1800
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 256M
    max_allowed_packet = 64M
    #max_seeks_for_key = 4294967295
    #group_concat_max_len = 1024
    max_length_for_sort_data = 1024
    net_buffer_length = 16384
    max_connect_errors = 100000
    concurrent_insert = 2
    read_rnd_buffer_size = 512K
    bulk_insert_buffer_size = 8M
    # query_cache boost for MariaDB >10.1.2+
    # https://community.centminmod.com/posts/30811/
    query_cache_limit = 1024K
    query_cache_size = 80M
    query_cache_type = 1
    query_cache_min_res_unit = 2K
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = InnoDB
    
    log_warnings=1
    slow_query_log=0
    long_query_time=1
    slow_query_log_file=/var/lib/mysql/slowq.log
    #log-error=/var/log/mysqld.log
    
    # innodb settings
    innodb_large_prefix=1
    innodb_purge_threads = 2
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    innodb_open_files = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 512M
    
    ## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
    innodb_buffer_pool_instances=1
    
    innodb_log_files_in_group = 2
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    #innodb_thread_concurrency = 12
    innodb_lock_wait_timeout=50
    innodb_flush_method = O_DIRECT
    innodb_support_xa=1
    
    # 200 * # DISKS
    innodb_io_capacity = 2700
    innodb_io_capacity_max = 5400
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    innodb_flush_neighbors = 0
    
    # mariadb settings
    [mariadb]
    #thread-handling = pool-of-threads
    #thread-pool-size= 20
    #mysql --port=3307 --protocol=tcp
    #extra-port=3307
    #extra-max-connections=1
    
    userstat = 0
    key_cache_segments = 1
    aria_group_commit = none
    aria_group_commit_interval = 0
    aria_log_file_size = 768M
    aria_log_purge_type = immediate
    aria_pagecache_buffer_size = 768M
    aria_sort_buffer_size = 192M
    
    [mariadb-5.5]
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    
    #ignore_db_dirs=
    query_cache_strip_comments=0
    
    innodb_read_ahead = linear
    innodb_adaptive_flushing_method = estimate
    innodb_flush_neighbor_pages = 1
    innodb_stats_update_need_lock = 0
    innodb_log_block_size = 512
    
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    [mysqld_safe]
    socket=/var/lib/mysql/mysql.sock
    #log-error=/var/log/mysqld.log
    #nice = -5
    open-files-limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 64M
    
    [myisamchk]
    tmpdir=/home/mysqltmp
    key_buffer = 256M
    sort_buffer = 32M
    read_buffer = 32M
    write_buffer = 32M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mariadb-10.0]
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    
    # 2 variables needed to switch from XtraDB to InnoDB plugins
    #plugin-load=ha_innodb
    #ignore_builtin_innodb
    
    ## MariaDB 10 only save and restore buffer pool pages
    ## warm up InnoDB buffer pool on server restarts
    innodb_buffer_pool_dump_at_shutdown=1
    innodb_buffer_pool_load_at_startup=1
    innodb_buffer_pool_populate=0
    ## Disabled settings
    performance_schema=OFF
    innodb_stats_on_metadata=OFF
    innodb_sort_buffer_size=2M
    innodb_online_alter_log_max_size=128M
    query_cache_strip_comments=0
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    [mariadb-10.1]
    innodb_file_format = Barracuda
    innodb_default_row_format = dynamic
    innodb_file_per_table = 1
    
    ## wsrep specific
    # wsrep_on=OFF
    # wsrep_provider
    # wsrep_cluster_address
    # binlog_format=ROW
    # default_storage_engine=InnoDB
    # innodb_autoinc_lock_mode=2
    # innodb_doublewrite=1
    # query_cache_size=0
    
    # 2 variables needed to switch from XtraDB to InnoDB plugins
    #plugin-load=ha_innodb
    #ignore_builtin_innodb
    
    ## MariaDB 10 only save and restore buffer pool pages
    ## warm up InnoDB buffer pool on server restarts
    innodb_buffer_pool_dump_at_shutdown=1
    innodb_buffer_pool_load_at_startup=1
    innodb_buffer_pool_populate=0
    ## Disabled settings
    performance_schema=OFF
    innodb_stats_on_metadata=OFF
    innodb_sort_buffer_size=2M
    innodb_online_alter_log_max_size=128M
    query_cache_strip_comments=0
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    # Defragmenting unused space on InnoDB tablespace
    innodb_defragment=1
    innodb_defragment_n_pages=7
    innodb_defragment_stats_accuracy=0
    innodb_defragment_fill_factor_n_recs=20
    innodb_defragment_fill_factor=0.9
    innodb_defragment_frequency=40
    
    
    [mariadb-10.2]
    #innodb_file_format = Barracuda
    innodb_default_row_format = dynamic
    innodb_file_per_table = 1
    
    ## wsrep specific
    # wsrep_on=OFF
    # wsrep_provider
    # wsrep_cluster_address
    # binlog_format=ROW
    # default_storage_engine=InnoDB
    # innodb_autoinc_lock_mode=2
    # innodb_doublewrite=1
    # query_cache_size=0
    
    # 2 variables needed to switch from XtraDB to InnoDB plugins
    #plugin-load=ha_innodb
    #ignore_builtin_innodb
    
    ## MariaDB 10 only save and restore buffer pool pages
    ## warm up InnoDB buffer pool on server restarts
    innodb_buffer_pool_dump_at_shutdown=1
    innodb_buffer_pool_load_at_startup=1
    ## Disabled settings
    performance_schema=OFF
    innodb_stats_on_metadata=OFF
    innodb_sort_buffer_size=2M
    innodb_online_alter_log_max_size=128M
    query_cache_strip_comments=0
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    # Defragmenting unused space on InnoDB tablespace
    innodb_defragment=1
    innodb_defragment_n_pages=7
    innodb_defragment_stats_accuracy=0
    innodb_defragment_fill_factor_n_recs=20
    innodb_defragment_fill_factor=0.9
    innodb_defragment_frequency=40
    
    # page compression
    # 10.1+ https://mariadb.com/kb/en/mariadb/compression/
    #innodb_compression_algorithm=zlib
    #innodb_use_fallocate=0
    #innodb_use_mtflush=0
    #innodb_mtflush_threads=4
    
    Another side note... should I be enabling the page compression options as I use 10.2?
     
    Last edited: Aug 7, 2018
  2. eva2000

    eva2000 Administrator Staff Member

    35,522
    7,833
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,074
    Local Time:
    8:58 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Centmin Mod is tuned to server specs not the specific web app usage and is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)

    See XF 2.0 - Server error log. Deadlocks can be ignored usually and relate to some specific concurrent load at the time and they usually can not be tuned away. Google up on how to use innodb status command and logging to found the queries which are deadlocking maybe related to addons?

    You can try setting these 2 variables to 10240 value and restarting mariadb mysql
    Code (Text):
    table_definition_cache = 8192
    table_open_cache = 4096


    That would totally be up to you to decide and test for. Were you using exact same server hardware specs as before centmin mod setup? What are current and previous server hardware specs? And using MariaDB 10.2 prior to using Centmin Mod? If you were you could check your pre-centminmod my.cnf settings to compare. If you weren't then you may need to tune MariaDB 10.2 specific settings for your Xenforo usage.

    Also how exactly did you do MariaDB 10.2 upgrade as centmin mod 123.09beta01 officially uses MariaDB 10.1 and 10.2 upgrade isn't enabled in unless you enabled the variable to enable upgrade suoport.
     
    • Like Like x 1
  3. anthony parsons

    anthony parsons Premium Member Premium Member

    77
    18
    8
    Feb 12, 2017
    Ratings:
    +22
    Local Time:
    8:58 AM
    Current yum stable
    10.1
    Thanks George. Yer, I get that this is not a fault thing, just asking for someone more knowledgable than I about it. I don't understand the intricacies yet with why you do certain things in centminmod, however, the things you do also make this system run as fast as it does, hence I ask here.

    I was using the same specs and mariaDB 10.2, yes. I had vastly different my.cnf settings though, as iredmail is not centminmod. Again though, I had issues in other areas with slow and aborted queries, that I am not having with centminmod, hence reluctancy to just change things based on how I had them vs. your optimal setup.

    I didn't even use the query cache last time, as much reading stated it is often the cause for table locks, yet you do configure it, and it likely could be the difference to why I don't have slow and aborted queries, but now have table locks. Flipped things on its head basically.

    I don't like to mess with a highly tuned system too much, simply put. I started from scratch before, working things out myself. You know much more and this system works extremely well -- thus reluctancy to change much.

    Yes, I added the custom config setting to upgrade, as 10.2 has considerable improvements to not be using it IMO: Changes & Improvements in MariaDB 10.2
     
    • Informative Informative x 1
  4. eva2000

    eva2000 Administrator Staff Member

    35,522
    7,833
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,074
    Local Time:
    8:58 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    I'd backup /etc/my.cnf make incremental changes/tuning to see if it helps or it just could of been like xenforo linked thread - just coincidental concurrent mysql load causing the deadlock.
     
    • Like Like x 1
..