SMF
SMF copied to clipboard
SimpleMachines
Memory issue on board index with show avatars variable
Basic Information
Memory issue result in 500 error due to query on attachment table
Steps to reproduce
All documented here: https://www.simplemachines.org/community/index.php?topic=588288.msg4170695#msg4170695
Expected result
Memory overflow when last posters in board have many attachments
Actual result
500 error
Version/Git revision
2.1.4
Database Engine
All
Database Version
No response
PHP Version
8
Logs
No response
Additional Information
No response
Looks like the change is that it is using the attachment_type key instead of id_member. I suspect that either way the query won't be optimized depending on the forum setup. If you have a lot of members with avatars as attachments, but a low number of overall attachments, then the key on the id_member would return a lower number of possible matches. However, if you have a lower number of members with a higher number of attachments, this will return a more optimized number of results.
@albertlast You are good with queries. Any thoughts? Just so your aware, you can't trigger this by default because the built in theme does not support avatars on the board index.
Also, we could use AND id_msg = 0 to the query as well. Would change it to use the id_msg index.
I suspect what's going on here is that under some circumstances it ends up using an id_member of 0... (Deleted topic? Deleted post? Welcome message?)
Which would cause this query to return all attachments.
I think any change to the query that ensures it's looking at the proper attachment type or non-zero id_member would work.
You can get the query in test by updating index.template.php and setting: $settings['avatars_on_boardIndex'] = true;
That is a good assessment as from what I see regular attachments do not associate the id_member at all, just id_msg. We could include both and the query optimizer from MySQL would pick the best one.
As a side note, running a explain on that query, at least on my test forums (2.1 and 3.0) all seem to indicate it's hitting a temp + filesort. Nasty, but I don't want to touch it if it's working fine in general. That is happening on the smf_boards table as well, so not typically going to be a issue where most forums (even the largest), would have under 250 boards and thus not much to optimize
For admins - where the problem lies - there is no filter on boards.
I.e., get 'em all.
I think the EXPLAIN is just showing a physical sequential read for that.
Just some info: forum has 95 boards and 20 K attachments
There are 6 boards where the last poster was deleted.
I am not sure what you need exactly, and I am off for a two week holiday without laptop. So not really, sorry
Could you add and explain analyze of the orginal and changed query?
Just read the linked topic, all the info is there. https://www.simplemachines.org/community/index.php?topic=588288.msg4170667#msg4170667
Could you add and explain analyze of the orginal and changed query?
Just read the linked topic, all the info is there. https://www.simplemachines.org/community/index.php?topic=588288.msg4170667#msg4170667
no this information are not in this topic.
@Bopske just run the query in phpmyadmin like you did here: https://www.simplemachines.org/community/index.php?msg=4170665 but instead addin explain in frontof the query you add "EXPLAIN ANALYZE" for more infos about this: https://planetscale.com/blog/how-read-mysql-explains
Ran an analyze on the new query
Result: localhost/deb77453_smftest2/ https://s228.webhostingserver.nl/phpMyAdmin/index.php?route=/database/sql&db=deb77453_smftest2 Uw SQL-query is succesvol uitgevoerd.
ANALYZE SELECT c.id_cat, c.name AS cat_name, c.description AS cat_desc, b.id_board, b.name AS board_name, b.description, CASE WHEN b.redirect != '' THEN 1 ELSE 0 END AS is_redirect, b.num_posts, b.num_topics, b.unapproved_posts, b.unapproved_topics, b.id_parent, b.id_cat, COALESCE(m.poster_time, 0) AS poster_time, COALESCE(mem.member_name, m.poster_name) AS poster_name, m.subject, m.id_topic, COALESCE(mem.real_name, m.poster_name) AS real_name,
(CASE WHEN COALESCE(lb.id_msg, 0) >= b.id_last_msg THEN 1 ELSE 0 END) AS is_read, COALESCE(lb.id_msg, -1) + 1 AS new_from,
c.can_collapse,
COALESCE(mem.id_member, 0) AS id_member, mem.avatar, m.id_msg, mem.email_address, mem.avatar, COALESCE(am.id_attach, 0) AS member_id_attach, am.filename AS member_filename, am.attachment_type AS member_attach_type
FROM smf_boards AS b
LEFT JOIN smf_categories AS c ON (c.id_cat = b.id[...]
1 SIMPLE b ALL NULL NULL NULL NULL 95 95.00 100.00 96.84 Using where; Using temporary; Using filesort 1 SIMPLE c eq_ref PRIMARY PRIMARY 1 deb77453_smftest2.b.id_cat 1 1.00 100.00 100.00 1 SIMPLE m eq_ref PRIMARY PRIMARY 4 deb77453_smftest2.b.id_last_msg 1 0.90 100.00 100.00 1 SIMPLE mem eq_ref PRIMARY PRIMARY 3 deb77453_smftest2.m.id_member 1 0.83 100.00 100.00 Using where 1 SIMPLE am ref|filter ID_MEMBER,attachment_type ID_MEMBER|attachment_type 3|1 deb77453_smftest2.m.id_member 39 (1%) 0.80 (0%) 1.26 100.00 Using where; Using rowid filter 1 SIMPLE lb eq_ref PRIMARY PRIMARY 5 const,deb77453_smftest2.b.id_board 1 0.72 100.00 100.00
And this was the original query
localhost/deb77453_smftest2/ https://s228.webhostingserver.nl/phpMyAdmin/index.php?route=/database/sql&db=deb77453_smftest2 Uw SQL-query is succesvol uitgevoerd.
ANALYZE SELECT c.id_cat, c.name AS cat_name, c.description AS cat_desc, b.id_board, b.name AS board_name, b.description, CASE WHEN b.redirect != '' THEN 1 ELSE 0 END AS is_redirect, b.num_posts, b.num_topics, b.unapproved_posts, b.unapproved_topics, b.id_parent, b.id_cat, COALESCE(m.poster_time, 0) AS poster_time, COALESCE(mem.member_name, m.poster_name) AS poster_name, m.subject, m.id_topic, COALESCE(mem.real_name, m.poster_name) AS real_name,
(CASE WHEN COALESCE(lb.id_msg, 0) >= b.id_last_msg THEN 1 ELSE 0 END) AS is_read, COALESCE(lb.id_msg, -1) + 1 AS new_from,
c.can_collapse,
COALESCE(mem.id_member, 0) AS id_member, mem.avatar, m.id_msg, mem.email_address, mem.avatar, COALESCE(am.id_attach, 0) AS member_id_attach, am.filename AS member_filename, am.attachment_type AS member_attach_type
FROM smf_boards AS b
LEFT JOIN smf_categories AS c ON (c.id_cat = b.id[...]
1 SIMPLE b ALL NULL NULL NULL NULL 95 95.00 100.00 96.84 Using where; Using temporary; Using filesort 1 SIMPLE c eq_ref PRIMARY PRIMARY 1 deb77453_smftest2.b.id_cat 1 1.00 100.00 100.00 1 SIMPLE m eq_ref PRIMARY PRIMARY 4 deb77453_smftest2.b.id_last_msg 1 0.90 100.00 100.00 1 SIMPLE mem eq_ref PRIMARY PRIMARY 3 deb77453_smftest2.m.id_member 1 0.83 100.00 100.00 Using where 1 SIMPLE am ref ID_MEMBER ID_MEMBER 3 deb77453_smftest2.m.id_member 39 1473.77 100.00 100.00 Using where 1 SIMPLE lb eq_ref PRIMARY PRIMARY 5 const,deb77453_smftest2.b.id_board 1 0.43 100.00 100.00