Welcome to Centmin Mod Community
Become a Member

Xenforo Enable Delayed Insert SQL Queries

Discussion in 'Forum software usage' started by rdan, Dec 28, 2015.

  1. rdan

    rdan Premium Member Premium Member

    4,308
    1,044
    113
    May 25, 2014
    Ratings:
    +1,504
    Local Time:
    4:41 PM
    Mainline
    10.2
    Do you guys have this option enabled?
    What are your XF database engine?
    Mine are all Innodb and few memory.

    With previous MariaDB/XF version, I have to disable this because I can't post when that option is enabled and all my tables are on innodb, Now it works fine even if it's enabled.
     
  2. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    disabled for me :)
     
  3. Sunka

    Sunka Well-Known Member

    1,021
    281
    83
    Oct 31, 2015
    Rijeka, Croatia
    Ratings:
    +459
    Local Time:
    10:41 AM
    Nginx 1.15.0
    MariaDB 10.2.15
    Disabled
    Innodb all but few that have to be mysql
     
  4. rdan

    rdan Premium Member Premium Member

    4,308
    1,044
    113
    May 25, 2014
    Ratings:
    +1,504
    Local Time:
    4:41 PM
    Mainline
    10.2
    And why?
    I enabled it, and don't have issues :).
     
  5. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    not needed with the improvements in MySQL performance especially in MariaDB 10+ and Xenforo's current usage patterns if you have InnoDB tables in use

    see INSERT DELAYED - MariaDB Knowledge Base and MySQL :: MySQL 5.6 Reference Manual :: 13.2.5.2 INSERT DELAYED Syntax

     
    • Like Like x 2
    • Informative Informative x 2
  6. Xon

    Xon Active Member

    147
    59
    28
    Nov 16, 2015
    Ratings:
    +189
    Local Time:
    4:41 PM
    1.15.x
    MariaDB 10.3.x
    INSERT DELAYED is a hack to workaround the limitations of MyISAM which does full-table locks rather than row-level locking.
     
    • Like Like x 2
    • Agree Agree x 2
  7. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    much better explanation :D
     
    • Like Like x 1
  8. Tien Nguyen Van

    Tien Nguyen Van New Member

    5
    2
    3
    May 30, 2018
    Ratings:
    +2
    Local Time:
    3:41 PM
    Hello,

    How can I enable this option? I got this error message when importing database for my website: "DELAYED option not supported for table"
     
  9. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Is this for Xenforo ? or other web script/app ? What version of MySQL were you coming from (source mysql backup data's MySQL version) ? How are you backing up and importing the mysql data ? exact commands used ?

    Googled - https://mariadb.com/kb/en/library/insert-delayed/

    Centmin Mod MariaDB has default max_delayed_threads = 20 so is not disabled at MariaDB MySQL global level
    Code (Text):
    mysqladmin var | grep max_delayed | tr -s ' '
    | max_delayed_threads | 20 |
    


    insert delayed only work with MyISAM, MEMORY, ARCHIVE and BLACKHOLE tables but MySQL 5.6.6 already deprecated insert delayed. But Centmin Mod MariaDB MySQL and Xenforo default to creating InnoDB tables by default, so you'd want to disable Xenforo insert delay option.

    check your delayed insert counts in mysql status output
    Code (Text):
    mysqladmin ext | grep -i delayed
    | Delayed_errors                                               | 0                                      |
    | Delayed_insert_threads                                       | 0                                      |
    | Delayed_writes                                               | 0                                      |
    | Not_flushed_delayed_rows                                     | 0                                      |
    


    But the MySQL docs left something out MySQL Bugs: #5777: InnoDB do not support INSERT DELAYED that after MySQL 5.6.6 and MariaDB 10, deprecating delayed inserts means just a warning but message you see and the query still goes through replacing DELAYED INSERT as normal INSERT. So unless your import abruptly stops, importing to completion with just that warning message should be ok I think.

    MySQL Bugs: #5777: InnoDB do not support INSERT DELAYED
    MySQL :: MySQL 5.6 Reference Manual :: 13.2.5.3 INSERT DELAYED Syntax
     
  10. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
  11. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Or use mysqldump in SSH command to backup database to sql file as delayed inserts is disabled by default for backups
    Code (Text):
     mysqldump --help | grep insert
      -c, --complete-insert
                          Use complete insert statements.
      --delayed-insert    Insert rows with INSERT DELAYED.
      -e, --extended-insert
                          (Defaults to on; use --skip-extended-insert to disable.)
      --insert-ignore     Insert rows with INSERT IGNORE.
                          --quick, --extended-insert, --lock-tables, --set-charset,
                          --create-options, --quick, --extended-insert,
    complete-insert                   FALSE
    delayed-insert                    FALSE
    extended-insert                   TRUE
    insert-ignore                     FALSE
    

    http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

    backup where /path/to/ is directory you want to save sql backup file to
    Code (Text):
    mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql
    

    restore
    Code (Text):
    mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql
    
     
  12. Tien Nguyen Van

    Tien Nguyen Van New Member

    5
    2
    3
    May 30, 2018
    Ratings:
    +2
    Local Time:
    3:41 PM
    Thanks for quick reply @eva2000 (this is one of the reason I went from EE to CMM). I exported databases using PhpMyAdmin and as your recommendation, I understand that I have to disable DELAY option when export. The problem is that I can't find: config.inc.php file location to modify it.

    If you are talking about the option when exporting from PhpMyAdmin interface, I disabled it: Pasteboard — Uploaded Image
     
  13. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    on old server try using locate command to find it
    Code (Text):
    updatedb
    locate config.inc.php
    
     
  14. Tien Nguyen Van

    Tien Nguyen Van New Member

    5
    2
    3
    May 30, 2018
    Ratings:
    +2
    Local Time:
    3:41 PM
    Wow! That's so cool. Your knowledge in this field must be at MASTER level. I now can import the database very well.

    I will submit other ticket if I face other problems. Thanks again for your kindly support.
     
    • Like Like x 1
  15. eva2000

    eva2000 Administrator Staff Member

    36,402
    7,992
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,306
    Local Time:
    6:41 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    You're welcome. FYI, my reply in your intro thread has alot of juicy info and tips to get the most out of Centmin Mod LEMP - guaranteed you'd learn something new everyday Introduction :D
     
    • Like Like x 1
..