Fixed Database speed...

[Paul]

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]

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: 3http://testforums.freddyshouse.com/forums/general.4/

takes

Timing:
0.7433 seconds
Memory:
6.361 MB
DB Queries:
15

 

[Shadab]

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

 

[Paul]

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]

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

Can you run

show indexes from `database name`.`xf_thread`;

 

[Deebs]

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]

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]

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)

 

[Mike]

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

Yeah, probably.

I've moved this to bugs as it will likely be bringing about a change.

 

[Paul]

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

 

[Paul]

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]

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]

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]

Mike,

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

Cheers
Paul

 

[Paul]

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 B]

Wow!
That's some improvement.

 

[Paul]

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