1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fixed Database speed...

Discussion in 'Resolved Bug Reports' started by Paul, Mar 9, 2011.

  1. Paul

    Paul Well-Known Member

    Hi guys and gals,

    Ok so i have moved from one VPS to another for live server, however i seem to be having some speed issues....

    Turned on Debug mode, and i'm getting the following results..

    Open main forum homepage:
    Timing:
    0.0948 seconds
    Memory:
    4.855 MB
    DB Queries:
    13

    Opening large forum:
    Timing:
    41.6922 seconds
    Memory:
    5.396 MB
    DB Queries:
    14

    Opening thread:
    Timing:
    0.1540 seconds
    Memory:
    5.943 MB
    DB Queries:
    14

    Have optimised database with no significant change in speed. Admittedly the DB is fairly large, however the opening of the larger forum page is not acceptable - it does vary from forum to forum, and was no-where near this slow on my previous VPS.

    An example is another forum with substantially less posts...

    Timing:
    0.1734 seconds
    Memory:
    5.331 MB
    DB Queries:
    14

    Which is fine for the purposes it is being used for.
    So - Anyone have any clues as to what i may be able to do to improve this?

    Many thanks
    Paul
     
  2. Deebs

    Deebs Well-Known Member

    How much memory does your server have? Can you see the output of vmstat or top when you attempt to open the forum causing issues? It could simply be memory being swapped.. Does the forum have a large number of threads inside it..

    On my test instance, opening a forum with the following inside

    Discussions:
    10,776
    Messages:
    238,014
    Sub-Forums: 3

    takes

    Timing:
    0.7433 seconds
    Memory:
    6.361 MB
    DB Queries:
    15
     
  3. Shadab

    Shadab Well-Known Member

    I'm no database expert, but it looks like a query is not hitting the desired index.

    Screenshot.png
     
  4. Paul

    Paul Well-Known Member

    Hiya Deebs,

    It's a large forum -

    Discussions: 287,696 Messages: 1,563,503

    However this worked fine on my previous server which was of a lower spec..

    Previous : 384MB (including CPanel)
    Now : 768MB (no Cpanel used)

    VMStat to follow...

    Thx
    Paul
     
  5. Deebs

    Deebs Well-Known Member

    Interesting, queries 5 and 6 on my test instance are hitting the correct indexes.

    Can you run

    Code:
    show indexes from `database name`.`xf_thread`;
     
  6. Deebs

    Deebs Well-Known Member

    Paul,

    Can you do the above query for me, as Shadab as quite rightly pointed out, the query is not hitting the correct index.
     
  7. Mike

    Mike XenForo Developer Staff Member

    MySQL doesn't want to use the "good" index here, as it thinks the sticky index is more selective. I'm actually looking at changing the index to (node_id, sticky, last_post_date), as this is used in both cases. (It might actually reduce the total number of indexes.)
     
  8. Deebs

    Deebs Well-Known Member

    Could the index cardinality be having an impact on the selection ? (Which is why I am interested in the output of show indexes)
     
  9. Mike

    Mike XenForo Developer Staff Member

    Yeah, probably.

    I've moved this to bugs as it will likely be bringing about a change.
     
    Walter and Vincent like this.
  10. Paul

    Paul Well-Known Member

    Due to the layout of my remote term i cant improve on this until later tonight (at work at present....)

    http://flic.kr/p/9p1TQX

    Many thanks to everyone for their assistance :)

    Paul
     
  11. Paul

    Paul Well-Known Member

    and for the sake of completeness....the VMSTAT info..

    Idle :
    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
    r b swpd free buff cache si so bi bo in cs us sy id wa st
    0 0 4480 510472 2660 86420 0 0 9 18 6 1 0 0 100 0 0

    In progress:

    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
    r b swpd free buff cache si so bi bo in cs us sy id wa st
    0 0 4196 17664 1820 570832 0 0 9 18 6 1 0 0 100 0 0

    Paul
     
  12. Deebs

    Deebs Well-Known Member

    Paul,

    This will require a fix from Mike, your index cardinality looks fine, you are not swapping and memory is not being squeezed on the server.
     
  13. Mike

    Mike XenForo Developer Staff Member

    I've changed the index to the one I mentioned above and it does seem to help, though MySQL choosing to use it can still be a little off. A "USE INDEX" might be useful, though a definite pain to add. I think with bigger data sets (where it's important) it's more likely to use it anyway, so it's probably not important.
     
  14. Paul

    Paul Well-Known Member

    Mike,

    Cool - Many thanks. Will look forward to the next release and see if it's speeded things up a little :)

    Cheers
    Paul
     
  15. Paul

    Paul Well-Known Member

    Mike,

    Just wanted to say that the update has increased and improved the speed dramatically - from 43 seconds down to .47 :)

    Many thanks for implementing this...

    Paul
     
    Mike likes this.
  16. Brogan

    Brogan XenForo Moderator Staff Member

    Wow!
    That's some improvement.
     
  17. Paul

    Paul Well-Known Member

    Brogan,

    Tell me about it. On a 1.5Gb+ database it's amazing - shows how the tweak of indexing on a db can completely affect the speed that it displays at.

    I'm well chuffed at the moment :) It's now useable again :)
     

Share This Page