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

Fixed  Database speed...

Paul

Well-known member
#1
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
 

Deebs

Well-known member
#2
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
 

Paul

Well-known member
#4
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
 

Deebs

Well-known member
#5
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`;
 

Deebs

Well-known member
#6
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
Paul,

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

Mike

XenForo developer
Staff member
#7
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.)
 

Deebs

Well-known member
#8
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.)
Could the index cardinality be having an impact on the selection ? (Which is why I am interested in the output of show indexes)
 

Paul

Well-known member
#11
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
 

Deebs

Well-known member
#12
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.
 

Mike

XenForo developer
Staff member
#13
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.
 

Paul

Well-known member
#14
Mike,

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

Cheers
Paul
 

Paul

Well-known member
#15
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
 

Paul

Well-known member
#17
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 :)