1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.3 Slow Queries/Slow Database

Discussion in 'XenForo Questions and Support' started by Wesker, Aug 15, 2016.

  1. Wesker

    Wesker Active Member

    Over the past several weeks, gradually our server has been declining in performance. Recently however we have had some severe issues with slow queries which result in locking up the database (500/504 errors) and MySQL server is responding quite slow. Specific add ons are taking 1+ hours to install to the point we have to stop installations. It's a mess and now we are urgently trying to resolve this.

    Currently we have one of the well known xF members here assisting us with repairing configurations, applying better caching, optimizing, logging queries and attempted to reconfigure elasticsearch. Hardware is maxed out well more than need. We are going to be moving to 1.5.9 soon.

    Are there any recommendations out there what else we can do?
    Last edited: Aug 16, 2016
  2. wang

    wang Well-Known Member

    Can you please post some of those slow queries so we can check them out?
  3. Wesker

    Wesker Active Member

    It's a rather large file. 4 GB logged in just under 10 days. I would install an easier to use mod for this but having issues installing items.
  4. Wesker

    Wesker Active Member

    Getting some help on this now. Should have some more answers in a moment.
  5. Wesker

    Wesker Active Member

    We have checked the logs and found duplicate entries are getting generated on large scale due to this reason the logs on the server are getting full faster. Below the duplicate entry generated:

    "60815 19:11:46 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_spam_trigger_log"
  6. Brogan

    Brogan XenForo Moderator Staff Member

    We had a similar ticket related to this earlier today.

    This is a warning that indicates that this query may not replicate safely.

    You would need to use a different binlog format, either row or mixed mode - see here: https://dev.mysql.com/doc/refman/5.7/en/binary-log-formats.html

    If you wish to maintain roughly the same binlog format, mixed mode will use statement-based replication unless it detects the statement may be unsafe.
  7. Wesker

    Wesker Active Member

    Thank you @Brogan. Will work on that soon.

    Do you by chance have the URL to that thread of the other individual having the same issue? Just curious if his site is having similar issues as ours.
  8. Brogan

    Brogan XenForo Moderator Staff Member

    It's a ticket, not a thread.
  9. Wesker

    Wesker Active Member

    For reference:

    [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO xf_bb_code_parse_cache
    (content_type, content_id, parse_tree, cache_version, cache_date)
    VALUES ('post', '2574301', 'a:1:{i:0;s:4:\"Bump\";}', '1471312326', '1471313546')
    ON DUPLICATE KEY UPDATE parse_tree = VALUES(parse_tree),
    cache_version = VALUES(cache_version),
    cache_date = VALUES(cache_date)
  10. Wesker

    Wesker Active Member

    With tickets do we get faster support from you guys? Can you handle direct requests and/or guide us on specific changes we need to make here?
  11. Brogan

    Brogan XenForo Moderator Staff Member

    Other than what I posted above, there's not much more (i.e. nothing) we can suggest with regards to that particular log entry.
  12. Wesker

    Wesker Active Member

    Wanted to give people a solution to this.

    You have 2 options to fix this:

    1). Follow this guide and make changes - https://xenforo.com/community/threads/sql-duplicate-key-update-xf_bb_code_parse_cache.69650/ and optimize the table
    2). Follow this guide - https://xenforo.com/community/threads/problem-table-xf_bb_code_parse_cache.116960/ and simply truncate the table which is the fastest solution

    We went with truncating since the table is used for performance reasons and if your site is already performing well on the frontend then this isn't needed.
  13. Xon

    Xon Well-Known Member

    I forgot about this add-on which I haven't released on Xenforo.com; XenForo-BBCodeCacheTweak. On my two sites, this reduced the xf_bb_code_parse_cache from +9gb of junk to 200-500mb.

    Basically; it changes the caching policy from everything to just large (> 120 characters) and slow stuff (0.01 seconds). Both settings are adjustable.

    This doesn't solve any row format problems related to the table; but it reduces the write load which can help perfromance.
    Steve F, Wesker and maszd like this.
  14. Wesker

    Wesker Active Member

    Thank you. Will install this once we finish our upgrade :)

Share This Page