XF 1.3 Slow Queries/Slow Database

[Wesker]

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?

 

[wang]

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

 

[Wesker]

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

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.

 

[Wesker]

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

 

[Wesker]

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"

 

[Paul B]

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]

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.

 

[Paul B]

It's a ticket, not a thread.

 

[Wesker]

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)

 

[Wesker]

It's a ticket, not a thread.

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?

 

[Paul B]

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]

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]

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]

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