ElkArte Community

Elk Development => Bug Reports => Topic started by: tino on February 08, 2021, 02:29:46 pm

Title: Slow Query with large forum
Post by: tino on February 08, 2021, 02:29:46 pm
I am not sure if this is due to the import or just a issue in general, when clicking on a board and retrieving all the posts it is slow to load. If I drop the attachments of the join and SELECT then it drops to < 0.5s on all my tests.

The attachments table is currently empty, I tried adding one attachment to see if that helped but nothing. There is about 393,731 Posts in 136,146 Topics in that board. So not that many, although a fair few.

Code: [Select]
 SELECT
   t.id_topic, t.num_replies, t.locked, t.num_views, t.num_likes, t.is_sticky, t.id_poll, t.id_previous_board,
   COALESCE(lt.id_msg, lmr.id_msg, -1) + 1 AS new_from,
   t.id_last_msg, t.approved, t.unapproved_posts, t.id_redirect_topic, t.id_first_msg,
   ml.poster_time AS last_poster_time, ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
   ml.poster_name AS last_member_name, ml.id_member AS last_id_member, ml.smileys_enabled AS last_smileys,
   COALESCE(meml.real_name, ml.poster_name) AS last_display_name,
   mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
   mf.poster_name AS first_member_name, mf.id_member AS first_id_member, mf.smileys_enabled AS first_smileys,
   COALESCE(memf.real_name, mf.poster_name) AS first_display_name
    ,meml.avatar,COALESCE(a.id_attach, 0) AS id_attach,a.filename,a.attachment_type,meml.email_address
FROM elkarte_topics AS t
   INNER JOIN elkarte_messages AS ml ON (ml.id_msg = t.id_last_msg)
   INNER JOIN elkarte_messages AS mf ON (mf.id_msg = t.id_first_msg)
   LEFT JOIN elkarte_members AS meml ON (meml.id_member = ml.id_member)
   LEFT JOIN elkarte_members AS memf ON (memf.id_member = mf.id_member)
   LEFT JOIN elkarte_log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = 1)
   LEFT JOIN elkarte_log_mark_read AS lmr ON (lmr.id_board = 8 AND lmr.id_member = 1)
   LEFT JOIN elkarte_attachments AS a ON (a.id_member = ml.id_member AND a.id_member != 0)
WHERE t.id_board = 8
ORDER BY is_sticky DESC, t.id_last_msg DESC
LIMIT 0, 20
in .../sources/subs/MessageIndex.subs.php line 138, which took 16.95581889 seconds at 0.51222014 into request.
Title: Re: Slow Query with large forum
Post by: Spuds on February 08, 2021, 03:19:07 pm
That does not seem right (yes captain obvious here)

Could you run that through explain, I wonder if an index is missing somewhere.  Thanks for the report!
Title: Re: Slow Query with large forum
Post by: Spuds on February 08, 2021, 03:30:09 pm
Here is what I'm seeing ... but this is just on a small test db I currently have up.


idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtreflast_message, first_message, id_board, last_message_s...last_message_sticky2const578Using where
1SIMPLEmleq_refPRIMARYPRIMARY4elkartesql1.t.id_last_msg1
1SIMPLEmemleq_refPRIMARYPRIMARY3elkartesql1.ml.id_member1
1SIMPLEmfeq_refPRIMARYPRIMARY4elkartesql1.t.id_first_msg1
1SIMPLEmemfeq_refPRIMARYPRIMARY3elkartesql1.mf.id_member1
1SIMPLElteq_refPRIMARY,id_topicPRIMARY6const,elkartesql1.t.id_topic1
1SIMPLElmrconstPRIMARYPRIMARY5const,const1
1SIMPLEarefid_memberid_member3elkartesql1.ml.id_member19Using where
Title: Re: Slow Query with large forum
Post by: tino on February 08, 2021, 04:54:57 pm
Sorry I had run it through explain, I forgot to put the results here. They are...

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra    
1    SIMPLE    t    ref    last_message,first_message,id_board,last_message_s...    id_board    2    const    118073    Using temporary; Using filesort
1    SIMPLE    ml    eq_ref    PRIMARY    PRIMARY    4    elkarte.t.id_last_msg    1    
1    SIMPLE    meml    eq_ref    PRIMARY    PRIMARY    3    elkarte.ml.id_member    1    
1    SIMPLE    mf    eq_ref    PRIMARY    PRIMARY    4    elkarte.t.id_first_msg    1    
1    SIMPLE    memf    eq_ref    PRIMARY    PRIMARY    3    elkarte.mf.id_member    1    
1    SIMPLE    lt    eq_ref    PRIMARY,id_topic    PRIMARY    6    const,elkarte.t.id_topic    1    
1    SIMPLE    lmr    const    PRIMARY    PRIMARY    5    const,const    1    
1    SIMPLE    a    ALL    id_member    NULL    NULL    NULL    1    Using where; Using join buffer (flat, BNL join)

It has an index on id_member it just ignores it.
Title: Re: Slow Query with large forum
Post by: Spuds on February 08, 2021, 05:03:42 pm
Just to be sure it has these indexs on the attachment table
PRIMARY   id_attach
UNIQUE   id_member, id_attach
INDEX   id_msg
INDEX   attachment_type
INDEX   id_thumb

postgre?
Title: Re: Slow Query with large forum
Post by: tino on February 08, 2021, 05:13:56 pm
Quote from: Spuds – Just to be sure it has these indexs on the attachment table
PRIMARY   id_attach
UNIQUE   id_member, id_attach
INDEX   id_msg
INDEX   attachment_type
INDEX   id_thumb

postgre?

Correct, MySQL on this one. Well MariaDB.

I’m going to look at the my.cnf later and tweak that is it’s a default Debian install currently.
Title: Re: Slow Query with large forum
Post by: Spuds on February 08, 2021, 05:27:44 pm
I should have noted that the explain I posted was from MariaDB (10.2) ... really strange that its ignoring that index. 
Title: Re: Slow Query with large forum
Post by: tino on February 08, 2021, 06:08:09 pm
Quote from: Spuds – I should have noted that the explain I posted was from MariaDB (10.2) ... really strange that its ignoring that index. 

It sees the index, for some reason it chooses to ignore it.
Title: Re: Slow Query with large forum
Post by: tino on February 08, 2021, 08:10:00 pm
So changing the join_buffer_size from 256.000 KiB to 512KiB removed the slow query and it drops to  0.0006 seconds.

It still ignores the index though and has the following on a explain; Using where; Using join buffer (flat, BNL join)

Interestingly (to me) if I remove this from the SELECT
Code: [Select]
COALESCE(a.id_attach, 0) AS id_attach,a.filename,a.attachment_type,
then it will use the index and has this in the explain Using where; Using index

I tried all combinations of those variables but it wanted them all to use the index. I am running 10.3.27
Title: Re: Slow Query with large forum
Post by: tino on February 08, 2021, 09:10:06 pm
Well that speed increase was short lived, it seems MySQL had cached my query so testing with that disabled means it jumps to 15seconds again.

It’s something related to the join query size I feel. I’ll see what more I can find. Although I think that query could be split out as it’s got quite a few joins.
Title: Re: Slow Query with large forum
Post by: Spuds on February 08, 2021, 09:38:56 pm
Thank you for you effort on this ... I'm going to ping @emanuele to see if he has any idea on this.  In the meantime I need to build up a  DB with lots more data so I can reproduce this behavior.  I think my db will be busy tonight :D
Title: Re: Slow Query with large forum
Post by: badmonkey on February 08, 2021, 10:15:47 pm
Could there be a relationship to

https://www.elkarte.net/community/index.php?topic=5933.0


?
Title: Re: Slow Query with large forum
Post by: tino on February 09, 2021, 07:21:41 am
I worked out why it wasn't using the index if that helps. I think this is a very unique set of circumstances but if you have no attachments ( or very few ) then the index is never built as MySQL correctly doesn't think it needs to. The issue is when you are joining a large table it sorts through them and tries to find something and then falls over.

I populated the table with 10 empty records for the attachments and it uses the index the same as your's did @spuds.
Title: Re: Slow Query with large forum
Post by: Spuds on February 09, 2021, 11:28:28 am
That is really strange, almost seems like a bug in the db query optimizer.   I can cause the index issue when I remove the AND a.id_member != 0 which is there to make the index query.  I'm still building up a larger db for testing, forgot to run it last night so its running now so I can test and see if I can repo this on 10.2.
Title: Re: Slow Query with large forum
Post by: tino on February 09, 2021, 01:23:35 pm
I don’t think it’s a bug in the optimiser, it makes logical sense in how it would do it.

The only optimisation you can do is a sub select really to pull those results back. I don’t think the Elkarte parser supports those though, it expect it will also only benefit when you don’t have the index, but that would need to be verified.

I’m happy to class this one as just a weird set of circumstances. It’s easy enough to mitigate by populating the table with some empty records or uploading some attachments.
Title: Re: Slow Query with large forum
Post by: Spuds on February 09, 2021, 03:05:19 pm
I have not gotten mine to the point where it is using temporary and thus far I'm only in the 5 digits vs your 6 digit initial.

1.1.7 will support sub selects, that was part of that limit issue you helped debug.  We could query the attach table and if empty force use avatars on index to false for that query.

Like you noted a bit of a corner case but that is what makes it interesting.