Signature Once 2.0.7

deslocotoco · Feb 14, 2019

Warchamp7 said:
Upgraded from 1.0 to 1.0.1, no longer able to load incredibly large threads (1000+ pages) due to PHP exceeding memory allocation limits. Disabling the add on fixed the problem

Code:

/Tick/Tackk/SignatureOnce/XF/Repository/Post.php, Line 65

Code:

[LIST] [*]ErrorException: Fatal Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2097152 bytes) [*]src/XF/Db/AbstractStatement.php:110 [/LIST]

Edit: I get this problem with 1.0 if 'Show Signature Once Per Thread' is enabled

Any updates on this?

I have a very big thread with more than 4k pages (XF 2.1) and with more than 2 millions views..

I'm afraid of installing and getting trouble.

But i really need to clean up my Forum a little. And is nice because save some resources from my server. Less signatures do load, less server load, I'm right?

rdn · Feb 14, 2019

Warchamp7 said:
Upgraded from 1.0 to 1.0.1, no longer able to load incredibly large threads (1000+ pages) due to PHP exceeding memory allocation limits. Disabling the add on fixed the problem

Code:

/Tick/Tackk/SignatureOnce/XF/Repository/Post.php, Line 65

Code:

[LIST] [*]ErrorException: Fatal Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2097152 bytes) [*]src/XF/Db/AbstractStatement.php:110 [/LIST]

Edit: I get this problem with 1.0 if 'Show Signature Once Per Thread' is enabled

I have this addon installed, with threads having 2k+ pages and loads fine.

Deebs · Mar 3, 2019

There is an issue with this addon when dealing with extremely large threads.

The following query is duplicated and cause major issues:

Code:

SELECT COUNT(*)
FROM `xf_post`
WHERE (`xf_post`.`thread_id` = 234459) AND ((`xf_post`.`message_state` IN ('visible', 'deleted', 'moderated'))) AND ((`xf_post`.`message_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_post`.`user_id` = 2419) AND (`xf_post`.`position` < 58982) AND ((FLOOR(`xf_post`.`position`/30) + 1) >= 1967)

for every user that has posted in the thread for the current page. It increases my query count from 27 to 58 on affected pages.

TickTackk · Mar 3, 2019

Deebs said:
There is an issue with this addon when dealing with extremely large threads.

The following query is duplicated and cause major issues:

Code:

SELECT COUNT(*) FROM `xf_post` WHERE (`xf_post`.`thread_id` = 234459) AND ((`xf_post`.`message_state` IN ('visible', 'deleted', 'moderated'))) AND ((`xf_post`.`message_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_post`.`user_id` = 2419) AND (`xf_post`.`position` < 58982) AND ((FLOOR(`xf_post`.`position`/30) + 1) >= 1967)

for every user that has posted in the thread for the current page. It increases my query count from 27 to 58 on affected pages.

Fixed for next release.

TickTackk · Mar 4, 2019

batpool52! updated Signature Once with a new update entry:

Changes and bug-fixes

Changes

Move options to Threads, discussions and conversations

Update the phrase to show correct description

Bug-fixes

Fixed: +1 query per every message if show once per thread/conversation is enabled is unchecked

Read the rest of this update entry...

Deebs · Mar 4, 2019

@batpool52! This is still not fixed.

If the Show user's signature once per thread is unchecked the following is now the query (and on massive threads causes major slowdowns):

SELECT (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59070) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134204, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 137) AND (xf_post.position < 59071) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134205, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 405) AND (xf_post.position < 59072) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134206, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 223) AND (xf_post.position < 59073) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134207, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59074) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134208, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 339) AND (xf_post.position < 59075) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134210, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 7183) AND (xf_post.position < 59076) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134216, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 2419) AND (xf_post.position < 59077) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134218, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 9616) AND (xf_post.position < 59078) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134220, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 223) AND (xf_post.position < 59079) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134222, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59080) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134223, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 10576) AND (xf_post.position < 59081) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134225, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 496) AND (xf_post.position < 59082) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134226, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59083) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134228, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 339) AND (xf_post.position < 59084) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134229, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59085) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134230, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59086) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134236, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59087) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134237, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59088) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134241, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 2419) AND (xf_post.position < 59089) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134244, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 496) AND (xf_post.position < 59090) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134247, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 2419) AND (xf_post.position < 59091) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134250, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 5324) AND (xf_post.position < 59092) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134252, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 9616) AND (xf_post.position < 59093) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134253, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59094) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134255, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59095) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134259, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59096) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134260, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 9176) AND (xf_post.position < 59097) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134261, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 296) AND (xf_post.position < 59098) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134263, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 601) AND (xf_post.position < 59099) AND ((FLOOR(xf_post.position/30) + 1) >= 1970)
) AS post_4134266 FROM xf_post LIMIT 1

Deebs · Mar 4, 2019

And if you enable the checkbox the query becomes:

SELECT (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59070)
) AS post_4134204, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 137) AND (xf_post.position < 59071)
) AS post_4134205, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 405) AND (xf_post.position < 59072)
) AS post_4134206, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 223) AND (xf_post.position < 59073)
) AS post_4134207, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59074)
) AS post_4134208, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 339) AND (xf_post.position < 59075)
) AS post_4134210, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 7183) AND (xf_post.position < 59076)
) AS post_4134216, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 2419) AND (xf_post.position < 59077)
) AS post_4134218, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 9616) AND (xf_post.position < 59078)
) AS post_4134220, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 223) AND (xf_post.position < 59079)
) AS post_4134222, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59080)
) AS post_4134223, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 10576) AND (xf_post.position < 59081)
) AS post_4134225, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 496) AND (xf_post.position < 59082)
) AS post_4134226, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59083)
) AS post_4134228, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 339) AND (xf_post.position < 59084)
) AS post_4134229, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59085)
) AS post_4134230, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59086)
) AS post_4134236, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59087)
) AS post_4134237, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59088)
) AS post_4134241, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 2419) AND (xf_post.position < 59089)
) AS post_4134244, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 496) AND (xf_post.position < 59090)
) AS post_4134247, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 2419) AND (xf_post.position < 59091)
) AS post_4134250, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 5324) AND (xf_post.position < 59092)
) AS post_4134252, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 9616) AND (xf_post.position < 59093)
) AS post_4134253, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59094)
) AS post_4134255, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 514) AND (xf_post.position < 59095)
) AS post_4134259, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 342) AND (xf_post.position < 59096)
) AS post_4134260, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 9176) AND (xf_post.position < 59097)
) AS post_4134261, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 296) AND (xf_post.position < 59098)
) AS post_4134263, (
SELECT COUNT(*)
FROM xf_post

WHERE (xf_post.thread_id = 234459) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND ((xf_post.message_state IN ('visible', 'deleted', 'moderated'))) AND (xf_post.user_id = 601) AND (xf_post.position < 59099)
) AS post_4134266 FROM xf_post LIMIT 1

So basically the multiple queries are condensed to 1 which does the same as multiple queries.

TickTackk · Mar 5, 2019

batpool52! updated Signature Once with a new update entry:

1.1.0

Changes

Abstracted out most of the parts so support other content types is easier

Improvements

No longer using sub queries

Credits

Thanks to @Xon for giving advice on how JOIN works in certain scenarios

Read the rest of this update entry...

Deebs · Mar 5, 2019

Hi @batpool52! unfortunately this new version is still very slow on very large threads. The following query takes 15 seconds on my test forum:

Code:

            SELECT post.post_id, COUNT(*)
            FROM xf_post AS post
            INNER JOIN xf_post AS post_tmp ON 
            (
                  post_tmp.position < post.position AND
                  post_tmp.user_id = post.user_id AND
                  post_tmp.thread_id = post.thread_id
            )
            WHERE post.thread_id = ?
              AND post_tmp.thread_id = ?
              AND post.message_state IN ('visible', 'deleted', 'moderated')
              AND post_tmp.message_state IN ('visible', 'deleted', 'moderated')
              AND post.position >= ?
              AND post.position < ?
            GROUP BY post.post_id
            ORDER BY post_id ASC
            LIMIT ?
Params: 234459, 234459, 59070, 59100, 30
Run Time: 15.790578

In my original XF 1.x addon my developer used a cache to ensure sub second response times on the same thread so the long SQL queries didn't run all the time.

Xon · Mar 5, 2019

@Deebs can you run an explain on that (or run XF in debug mode?) because there is likely something going weird with MySQL

Deebs · Mar 6, 2019

@Xon,

I think the issue is that the Ref for the first part of the query is null?

All tables have been optimised before running the query. Database is on a SSD and all tmpdirs point to tmpfs.

Xon · Mar 7, 2019

Can you try analyse table xf_post (warning this may take time, and put a lock on the table blocking writes!!) to update statistics and see how much of a difference that makes?

Deebs · Mar 7, 2019

Xon said:
Can you try analyse table xf_post (warning this may take time, and put a lock on the table blocking writes!!) to update statistics and see how much of a difference that makes?

That made no difference, as I mentioned in my previous reply I optimised (recreated under Innodb) all tables prior to running the query several times.

Deebs · Mar 7, 2019

@Xon @batpool52! Guys I feel like such an idiot. Part of the reason for the massive query delay was due to the fact that I had a typo in my.cnf and MySQL was defaulting the innodb_buffer_pool_size which is basically nothing at all.

However, since increasing the memory on subsequent reloads of the same page the query now happens in around 0.43ms and any unvisited page causes huge amounts of IO if the pages are not in InnoDB.

Xon · Mar 7, 2019

Deebs said:
@Xon @batpool52! Guys I feel like such an idiot. Part of the reason for the massive query delay was due to the fact that I had a typo in my.cnf and MySQL was defaulting the innodb_buffer_pool_size which is basically nothing at all.

This would explain it! (IIRC default innodb_buffer_pool_size is 256mb)

However, since increasing the memory on subsequent reloads of the same page the query now happens in around 0.43ms and any unvisited page causes huge amounts of IO if the pages are not in InnoDB.

I've proposed a tweak to the query which may help, but @batpool52! probably will need to leverage caching to reduce the IO load.

Deebs · Mar 7, 2019

Xon said:
This would explain it! (IIRC default innodb_buffer_pool_size is 256mb)

I've proposed a tweak to the query which may help, but @batpool52! probably will need to leverage caching to reduce the IO load.

Yeh that's what I said earlier, my developer, when he wrote the FH Signature Once used the XF cache to keep the IO basically null.

Thanks for your help with this.

TickTackk · Mar 8, 2019

batpool52! updated Signature Once with a new update entry:

1.1.1

Changes

Improved the query to be more lightweight (once again thanks to @Xon)

New

ContentInterface interface for repository now a new method getMessageCountsForSignatureOnce()

Results will be now cached for 120 seconds (2 minutes)

Read the rest of this update entry...

GeorgWin · Mar 9, 2019

After upgrading from version 1.1.0 -> 1.1.1

xf2.0.12
php5.6

Code:

ErrorException: Fatal Error: Call to a member function save() on null src/addons/TickTackk/SignatureOnce/Repository/ContentTrait.php:47
Сгенерированная пользователем: Неизвестная учетная запись 9 Мар 2019 в 03:02

Трассировка стека

#0 [internal function]: XF::handleFatalError()
#1 {main}

TickTackk · Mar 9, 2019

Fixed for next release

TickTackk · Mar 9, 2019

batpool52! updated Signature Once with a new update entry:

1.1.2

Bug-fixes

Fatal Error: Call to a member function save() on null

Read the rest of this update entry...

Signature Once 2.0.7

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Active member

Attachments

Well-known member

Well-known member

Similar threads

We value your privacy