MySQL 4 and Database Optimisations

[Gamingforce]

I created this thread not just so I can receive help on this, but for many other board owners who wish to get more information on optimising their server can get info too.

I'm running 2 servers each with dual Athlon XP 2000+, 2 GB DDR Memory and SCSI Raid 5 on FreeBSD 4.5. I'm only running one board on these two servers (db/web server setup). We still get long page load times sometime (10-15 seconds). This seems to happen randomly and usually happens more during busier times. I don't think it's the server really because load times are almost around or less than .50 on both servers. I'm beginning to think it's the database.

I installed a hack on my board that shows the page load times and percentages on how long it spent getting data from MySQL and then parsing the data in PHP (http://www.gamingforce.com/forums). I usually get numbers like 85% MySQL and 15% PHP. I think the problem lies in MySQL's table locking. There are a few threads on MySQL 4 already, but do you think all vBulletin owners should upgrade? According to reports converting the user and thread tables to the InnoDB format will result in higher performance. We are using MySQL 3.23.51 currently but are there any benefits or drawbacks to using the 4 series and vBulletin? Will users see performance gains when using MySQL 4?

Also I had a question on heap table types and indexes. I know session uses a heap table but could we convert any other tables to heap to improve performance? Also, are the indexes created by vBulletin team on initial installation optimal? Are there any other indexes to add to improve performance?

I think I have done everything I could hardware wise to make the database fast. I could add another 15k RPM SCSI Drive for a Raid 5 but after checking iostats I don't think the HD is much of a bottleneck right now.

Summarising everything, the 64 thousand dollar question is, how can we improve database performance on vBulletin without discussing about hardware.

Share your tips and secrets if you have any!

[DBs]

I use MySQL 4.01 against a 300mb vBulletin database without any issues whatsoever.

I did try an upgrade to 4.02 but then I found that vBulletin was showing duplicate threads although they were pointing to the same threadid. IOW, it was just a display issue so I reverted back (I even disabled the query cache to be sure, same prob).

Anyway, the query cache. This is a life saver, basically for many types of queries it can cache the results thus saving the expense of having to rerun the query.

eg.

User1 loads the thread titled "Test1". MySQL will run the query and put the result and the query into the cache.

User2+ loads the same thread. MySQL hashes the query, looks for it in the cache, sees that nothing has changed, send the result straight back from the cache.

It will reduce the load by a varying amount but it will certainly speed up your system.

As for heap tables, remember they only live in RAM so exist only for the life of the box, iow, if you reboot or mysql stops, kiss any data within them goodbye.

[Gamingforce]

Are there any compatbility problems with vBulletin and MySQL 4? Did you have to edit any queries?

[DBs]

None

Apart from my issue with moving to 4.01 to 4.02 but I have not had the time to investigate further.

[Fusion]

Originally posted by DBs
I did try an upgrade to 4.02 but then I found that vBulletin was showing duplicate threads although they were pointing to the same threadid. IOW, it was just a display issue so I reverted back (I even disabled the query cache to be sure, same prob).
I take it the vB Devs are looking into this feature? I know I'm asking the obvious, but I needn't say we're hoping to see a fix soon.

[eva2000]

Originally posted by Gamingforce
I created this thread not just so I can receive help on this, but for many other board owners who wish to get more information on optimising their server can get info too.

I'm running 2 servers each with dual Athlon XP 2000+, 2 GB DDR Memory and SCSI Raid 5 on FreeBSD 4.5. I'm only running one board on these two servers (db/web server setup). We still get long page load times sometime (10-15 seconds). This seems to happen randomly and usually happens more during busier times. I don't think it's the server really because load times are almost around or less than .50 on both servers. I'm beginning to think it's the database.

I installed a hack on my board that shows the page load times and percentages on how long it spent getting data from MySQL and then parsing the data in PHP (http://www.gamingforce.com/forums). I usually get numbers like 85% MySQL and 15% PHP. I think the problem lies in MySQL's table locking. There are a few threads on MySQL 4 already, but do you think all vBulletin owners should upgrade? According to reports converting the user and thread tables to the InnoDB format will result in higher performance. We are using MySQL 3.23.51 currently but are there any benefits or drawbacks to using the 4 series and vBulletin? Will users see performance gains when using MySQL 4?

Also I had a question on heap table types and indexes. I know session uses a heap table but could we convert any other tables to heap to improve performance? Also, are the indexes created by vBulletin team on initial installation optimal? Are there any other indexes to add to improve performance?

I think I have done everything I could hardware wise to make the database fast. I could add another 15k RPM SCSI Drive for a Raid 5 but after checking iostats I don't think the HD is much of a bottleneck right now.

Summarising everything, the 64 thousand dollar question is, how can we improve database performance on vBulletin without discussing about hardware.

Share your tips and secrets if you have any! note i'm not a vB developer on mod

first off HEAP format isn't supported for myisamchk and REPAIR functions.. sure you want to sacrifice speed for reliability and the ability of repairing corrupted tables ?

do a search for ethank's attempt to port to innobase tables - i think there's issues with innobase consuming ALOT of memory and disk space if i remember correctly ?

i know all that the proper indexes have already been in vB 2.2.x for a while

have you done all the stuff i recommended in the other thread http://www.vbulletin.com/forum/showthread.php?s=&threadid=51394 ?

[eva2000]

here's the ethank innobase thread i was talking about it was actually Gemini and not innobase tables http://www.vbulletin.com/forum/showthread.php?s=&threadid=40021

summary http://www.vbulletin.com/forum/showthread.php?s=&postid=263070#post263070

of course that was a few versions ago

[eva2000]

another thing i was going to recommend after you did the recommended changes i posted in the other thread was deferring thet threadviews using this hack http://www.vbulletin.org/forum/showthread.php?s=&threadid=40137

but i see you have yet to install PHPA/mod_gzip, upgrade to 2.4.x kernel ? did you reduce the maxclients settings as recommended ?

if you'd like to update your progress in this thread http://www.vbulletin.com/forum/showthread.php?s=&threadid=51394 ?

[fastforward]

Originally posted by DBs
I did try an upgrade to 4.02 but then I found that vBulletin was showing duplicate threads although they were pointing to the same threadid. IOW, it was just a display issue so I reverted back (I even disabled the query cache to be sure, same prob).

I had this problem. It only happens if a user has posted twice in the same thread and that user is actually viewing the forum. Other users that view the forum see only a single thread. It obviously has something to do with the query in forumdisplay.php. I haven't really looked at it closely, but the fault could be either MySQL or vBulletin. For what it's worth, turning off dot icons fixes the problem.

Anyway, the query cache. This is a life saver, basically for many types of queries it can cache the results thus saving the expense of having to rerun the query.

Note that the benefits of the query cache are pretty much negated unless you disable thread view counting. This is because every view requires an update to the thread table which in turn, clears the query cache and means any query using that table in it's from clause will need to revisit the database.

I've found the best thing about version 4 is the boolean full text search. I've hacked search.php, emptied my word and searchindex tables and rely entirely on fulltext indexes for searching. The speed can be much faster than the old method when searching for single words, although the greatest benefit is the low maintenance (I was often getting corrupt indexes in my searchindex table before I switched).

[DBs]

Originally posted by fastforward

I had this problem. It only happens if a user has posted twice in the same thread and that user is actually viewing the forum. Other users that view the forum see only a single thread. It obviously has something to do with the query in forumdisplay.php. I haven't really looked at it closely, but the fault could be either MySQL or vBulletin. For what it's worth, turning off dot icons fixes the problem.

Note that the benefits of the query cache are pretty much negated unless you disable thread view counting. This is because every view requires an update to the thread table which in turn, clears the query cache and means any query using that table in it's from clause will need to revisit the database.

I've found the best thing about version 4 is the boolean full text search. I've hacked search.php, emptied my word and searchindex tables and rely entirely on fulltext indexes for searching. The speed can be much faster than the old method when searching for single words, although the greatest benefit is the low maintenance (I was often getting corrupt indexes in my searchindex table before I switched).

Hmmm, good to know it was not just me. I will have a dig thru the PHP source next week when I have a few moments and then see why it is failing and if I can fix it.

As to the query cache being negated its only for a small portion of vBulletin. There are many other queries which DO benefit from the cache and ultimately lead to a good load drop on the box running MySQL.

eg. This is taken from my dedicated MySQL server with only the vBulletin databases running on it:

Total Questions: 37929801
Queries in cache: 65063
Queries not cached: 44383
Total Hits: 18330130

Therefore the cache has been hit in over 48% of all queries to the server. Quite a saving on potential load on the server if it had had to do the queries itself.

Also there are more queries within vBulletin that can be cached than can't which is always good :p

[fastforward]

This thread reminded me to check if the duplicate thread problem still exists in the latest MySQL beta... apparently, it doesn't.

I've just re-enabled dot icons and the previously problematic threads are now displaying correctly. This is with 4.03 beta downloaded from the CVS. Of course, the dot icons may have been a red herring from the start. :)

If you get around to installing it, let me know how you get on. There are few my.conf syntactical changes since 4.02 that you may need to change.

By the way, there's some more discussion on the query cache in this thread: http://www.vbulletin.com/forum/showthread.php?s=&threadid=40021&perpage=15&highlight=query%20cache&pagenumber=3

[Mike Sullivan]

Originally posted by fastforward
Note that the benefits of the query cache are pretty much negated unless you disable thread view counting. This is because every view requires an update to the thread table which in turn, clears the query cache and means any query using that table in it's from clause will need to revisit the database. You can actually defer views to update in batches in vB3 if you want. It's not quite in real time, but you will still get the view count and will (usually) still have the query cache. :)

[eva2000]

Originally posted by Ed Sullivan
You can actually defer views to update in batches in vB3 if you want. It's not quite in real time, but you will still get the view count and will (usually) still have the query cache. :) it's pretty neat too :D

[Fusion]

Bah! Teases!

[fastforward]

Originally posted by eva2000
it's pretty neat too :D
You can't tease me with new versions! You're talking to someone who's still running a board based on 2.01. :o

I only upgraded the 1.1 version on my other site 2 or 3 months ago!

[eva2000]

Originally posted by fastforward

You can't tease me with new versions! You're talking to someone who's still running a board based on 2.01. :o

I only upgraded the 1.1 version on my other site 2 or 3 months ago! heh :D