Hi all and thanks for wonderful forum software and great plugins.
I have a forum which has over 3.5 million 'likes' in the database and as there is a new upgrade to the Thank You Like plugin, I would like to upgrade it .... but the last time I did a 'Recount & Rebuild' on the Thank You Like it took over 23 hours (actually I stopped it at near 24 hours because it was getting slower and slower)!
It began by sending between 300 to 400 queries per second and I stopped it when it was taking near 30 seconds per 300 - 400 .... at this point I think that it still had approx 1 million to do!
My question is:
How can I make this 'Recount' vastly quicker?
Else I do not think that I can do the upgrade as I may need to do a 'Recount'!
Many thanks for any advice.
MyBB version: 1.8.35
PHP: 8.0.30
MySQLi: 10.6.12
Plugins:
Thank You Like 3.4.4
Mention Me 3.2.12
My Alerts 2.0.4
Auto Media 4.1.1
My Insert Buttons 4.0.2
(2023-08-16, 04:03 PM)lost puppy Wrote: [ -> ]the last time I did a 'Recount & Rebuild' on the Thank You Like it took over 23 hours (actually I stopped it at near 24 hours because it was getting slower and slower)!
It began by sending between 300 to 400 queries per second and I stopped it when it was taking near 30 seconds per 300 - 400 .... at this point I think that it still had approx 1 million to do!
Mmm, yeah, looking at the code, it's terribly inefficient in terms of database queries.
(2023-08-16, 04:03 PM)lost puppy Wrote: [ -> ]How can I make this 'Recount' vastly quicker?
It could be done in a few manual queries, each of which might take a little while to run. If necessary, I can come up with those queries for you, however...
(2023-08-16, 04:03 PM)lost puppy Wrote: [ -> ]Else I do not think that I can do the upgrade as I may need to do a 'Recount'!
...there's nothing about an upgrade that would entail the need for a recount if one wasn't already required.
(2023-08-19, 11:40 AM)Laird Wrote: [ -> ] (2023-08-16, 04:03 PM)lost puppy Wrote: [ -> ]the last time I did a 'Recount & Rebuild' on the Thank You Like it took over 23 hours (actually I stopped it at near 24 hours because it was getting slower and slower)!
It began by sending between 300 to 400 queries per second and I stopped it when it was taking near 30 seconds per 300 - 400 .... at this point I think that it still had approx 1 million to do!
Mmm, yeah, looking at the code, it's terribly inefficient in terms of database queries.
(2023-08-16, 04:03 PM)lost puppy Wrote: [ -> ]How can I make this 'Recount' vastly quicker?
It could be done in a few manual queries, each of which might take a little while to run. If necessary, I can come up with those queries for you, however...
(2023-08-16, 04:03 PM)lost puppy Wrote: [ -> ]Else I do not think that I can do the upgrade as I may need to do a 'Recount'!
...there's nothing about an upgrade that would entail the need for a recount if one wasn't already required.
Thank you so much for your reply
It is good to read that an update will not require a recount ... that does allay my concerns.
I would very much like to have the 'like' count actually correct so, if I may impose on you, It would be brilliant to have the manual queries to run?
The forum is very quiet for approx 4-5 hours overnight so I could run them at that time (I would first run them on a 'test' site I have of the forum) and it would be no issue!
Again ... thank you for helping me
(2023-08-20, 02:18 PM)lost puppy Wrote: [ -> ]It would be brilliant to have the manual queries to run?
Attached is a script that hopefully will work for you. The instructions are in a comment block up the top. Please let me know how you go with it, and especially the time it takes the SQL to run on a board with the millions of thanks/likes that yours has. If it's successful, I'll probably add it to the plugin itself, in the plugin's
scripts directory.
(2023-08-21, 08:04 AM)Laird Wrote: [ -> ] (2023-08-20, 02:18 PM)lost puppy Wrote: [ -> ]It would be brilliant to have the manual queries to run?
Attached is a script that hopefully will work for you. The instructions are in a comment block up the top. Please let me know how you go with it, and especially the time it takes the SQL to run on a board with the millions of thanks/likes that yours has. If it's successful, I'll probably add it to the plugin itself, in the plugin's scripts directory.
WOW .... thank you for doing this
I ran it on my test site which has 3,499,755 likes in the db
[
attachment=46240]
There are 6 queries (I think lol) and the first 4 .... each took seconds (I mean the db states 0.0004 for each one)
Only problem was on the 5th query where it returned (I have redacted my db prefix):
UPDATE REDACTED_threads t
SET tyl_tnumtyls = IFNULL(
(
SELECT SUM(tyl_pnumtyls)
FROM REDACTED_posts p
WHERE p.tid = t.tid
),
0
);
MySQL said: Documentation
#1054 - Unknown column 'tyl_tnumtyls' in 'field list'
But it would definitely seem that it worked anyway
Taking 2 members:
On the real site forum Member 1 has 29671 'likes' >> but now on the 'test' site the member has 33690
Member 2 on the real forum has 381953 'likes' >> on 'test' site they now have 424704
Speed wise .... well what can I say ... last time it took near 24 hours and didn't complete (I had to stop it)
This took seconds!!
Stunning ... thank you so very much ... you are a super star
(2023-08-21, 04:43 PM)lost puppy Wrote: [ -> ]There are 6 queries (I think lol) and the first 4 .... each took seconds (I mean the db states 0.0004 for each one)
Excellent - I was hoping it would be fast even on a site with millions of thanks/likes.
(2023-08-21, 04:43 PM)lost puppy Wrote: [ -> ]Only problem was on the 5th query
Oh, I see - that's because the tyl_tnumtyls column was mistakenly removed (by me) from the core threads table in version 3.1.0 (August, 2018) of the plugin, and only reinstated in the latest version 3.4.5 (August, 2023), to which you have not yet upgraded.
I've updated the script (as attached in my previous post) to handle scenarios like yours. Please test the new version of the script (1.1.0) and let me know whether the error is avoided this time.
(2023-08-21, 04:43 PM)lost puppy Wrote: [ -> ]But it would definitely seem that it worked anyway
Taking 2 members:
On the real site forum Member 1 has 29671 'likes' >> but now on the 'test' site the member has 33690
Member 2 on the real forum has 381953 'likes' >> on 'test' site they now have 424704
Speed wise .... well what can I say ... last time it took near 24 hours and didn't complete (I had to stop it)
This took seconds!!
Stunning ... thank you so very much ... you are a super star
That's great news. Cheers for the positive feedback.
(2023-08-22, 01:32 AM)Laird Wrote: [ -> ] (2023-08-21, 04:43 PM)lost puppy Wrote: [ -> ]There are 6 queries (I think lol) and the first 4 .... each took seconds (I mean the db states 0.0004 for each one)
Excellent - I was hoping it would be fast even on a site with millions of thanks/likes.
(2023-08-21, 04:43 PM)lost puppy Wrote: [ -> ]Only problem was on the 5th query
Oh, I see - that's because the tyl_tnumtyls column was mistakenly removed (by me) from the core threads table in version 3.1.0 (August, 2018) of the plugin, and only reinstated in the latest version 3.4.5 (August, 2023), to which you have not yet upgraded.
I've updated the script (as attached in my previous post) to handle scenarios like yours. Please test the new version of the script (1.1.0) and let me know whether the error is avoided this time.
(2023-08-21, 04:43 PM)lost puppy Wrote: [ -> ]But it would definitely seem that it worked anyway
Taking 2 members:
On the real site forum Member 1 has 29671 'likes' >> but now on the 'test' site the member has 33690
Member 2 on the real forum has 381953 'likes' >> on 'test' site they now have 424704
Speed wise .... well what can I say ... last time it took near 24 hours and didn't complete (I had to stop it)
This took seconds!!
Stunning ... thank you so very much ... you are a super star
That's great news. Cheers for the positive feedback.
I tested it and all was well until the 5th query (creation of tyl_tnumtyls column) and the error given was:
Error
SQL query: Copy
PREPARE stmt FROM @query;
MySQL said: Documentation
#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'
The Redacted part was the correct db user name .... so is that something I need to sort out somehow?
The last 2 queries came back with the same error as before (which I guess it would as the column isn't there).
Thanks again Laird
(2023-08-22, 08:21 AM)lost puppy Wrote: [ -> ]I tested it and all was well until the 5th query (creation of tyl_tnumtyls column) and the error given was:
Error
SQL query: Copy
PREPARE stmt FROM @query;
MySQL said: Documentation
#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'
Do you also get an error when you run this (after replacing "YOURPREFIX" with your actual prefix)?
SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';
And is your
threads table using the InnoDB engine? You can check by running (same replacement as previously):
SHOW CREATE TABLE `YOURPREFIX_threads`;
The last line of the output will include "ENGINE=[the engine type]".
(2023-08-22, 09:14 AM)Laird Wrote: [ -> ] (2023-08-22, 08:21 AM)lost puppy Wrote: [ -> ]I tested it and all was well until the 5th query (creation of tyl_tnumtyls column) and the error given was:
Error
SQL query: Copy
PREPARE stmt FROM @query;
MySQL said: Documentation
#1044 - Access denied for user 'REDACTED'@'%' to database 'information_schema'
Do you also get an error when you run this (after replacing "YOURPREFIX" with your actual prefix)?
SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';
And is your threads table using the InnoDB engine? You can check by running (same replacement as previously):
SHOW CREATE TABLE `YOURPREFIX_threads`;
The last line of the output will include "ENGINE=[the engine type]".
No error on the first one ... second one runs and states:
Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
And then states:
Your SQL query has been executed successfully.
After which it shows a small table?
Just ran the full set of queries again to check that I did it correctly and had the same error.
Edit to add:
Having a quick read and seeing this:
Quote:InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type.
All the tables in the db are MyISAM
But looking at the db structure it does say "innoDB is the default storage engine on this MySQL server"
[
attachment=46259]
But the server is running:
MyBB version: 1.8.35
PHP: 8.0.30
MySQLi: 10.6.12
(2023-08-22, 09:39 AM)lost puppy Wrote: [ -> ] (2023-08-22, 09:14 AM)Laird Wrote: [ -> ]Do you also get an error when you run this (after replacing "YOURPREFIX" with your actual prefix)?
SHOW COLUMNS FROM `YOURPREFIX_threads` LIKE 'tyl_tnumtyls';
[...]
No error [...]
That's odd. You say that your database server is MySQL (I assume you mean that it's not the MariaDB fork), and the MySQL documentation
says:
Quote:The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements.
However, you're getting a permission error on one and not on the other. I'm not sure why.
(2023-08-22, 09:39 AM)lost puppy Wrote: [ -> ]All the tables in the db are MyISAM
OK, thanks, that's good to know. It rules out the applicability of this from the same MySQL documentation page (my clarifying addition in square brackets):
Quote:Some [INFORMATION_SCHEMA] tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example, InnoDB tables (tables with names that begin with INNODB_) require the PROCESS privilege.
I'm still trying to figure out from here what's going on...