• 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

Wesker

Well-known member
#1
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:

Wesker

Well-known member
#5
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"
 

Brogan

XenForo moderator
Staff member
#6
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.
 

Wesker

Well-known member
#7
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.
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.
 

Wesker

Well-known member
#9
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)
 

Brogan

XenForo moderator
Staff member
#11
Other than what I posted above, there's not much more (i.e. nothing) we can suggest with regards to that particular log entry.
 

Wesker

Well-known member
#12
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.
 

Xon

Well-known member
#13
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.
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.
 

Wesker

Well-known member
#14
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.
Thank you. Will install this once we finish our upgrade :)