Previous Thread
Next Thread
Print Thread
Hop To
Tweaking performance with 5million posts #248612 03/05/2012 6:52 PM
Joined: Jun 2006
Posts: 67
Architecht Offline OP
journeyman
OP Offline
journeyman
Joined: Jun 2006
Posts: 67
Hi all,

We run a UBB forum with alot of volume:

Board Statistics
Version: 7.2.2
Total Members: 19,090
Total Forums: 72
Total Topics: 242,514
Total Posts: 5,434,546



As you can see we're still running 7.2.2 because we've been casually waiting forever for either v8 or v7.6 to come out.

One of our bigger headaches is the search function, which can lock up the boards for long periods of time. I've restricted the search range, search frequency, max results, etc.. However, in general, I think it's just poor code and data structure in the search function for dealing with this volume of posts.

We're in the midst of upgrading the hardware to 24gigs of ram as putting the whole database into RAM tends to speed things up.

However, does anyone have any thoughts on either:

1. Tweaks to the MySQL settings that are not part of the stock install to speed things up.

2. Whether there would be any significant performance improvements from the upgrade to 7.5. We've looked over some of the change logs and not had anything leap out at us, but I thought I'd check.



[Linked Image from boards.collectors-society.com]
Re: Tweaking performance with 5million posts [Re: Architecht] #248615 03/06/2012 1:04 AM
Joined: Jun 2004
Posts: 207
cascadeclimbers Offline
enthusiast
Offline
enthusiast
Joined: Jun 2004
Posts: 207
5 million posts. Wow.

We are running into problems too and are having to upgrade our hardware to accommodate our measly 1 million posts. In talking with Sirdude I know he has been pretty militant to optimize every query he can.

I think there are a couple of tables that could use indexes. Topic is sticky and topic is approved.

Sirdude has some code that runs the search through Apache Solr. Contact him and I could provide some help getting that setup with the Solr part. We don't have our forums on it yet but I am using our forum data in solr for something else that processes a hundred queries a minute. Full text ueries that will crash your server in MySQL will run in a millisecond in Solr. Solr's footprint is actually very low, however the import process seems to load the full query results into ram, so you'll need to make sure your start your servlet up with 5 gigs of ram, which doesn't sound like will be a problem with for you. I imagine there might be other ways to run the import with lower memory but I haven't investigated it.

Who are you hosting with?


Won't you take me to Funkytown?
Re: Tweaking performance with 5million posts [Re: Architecht] #248617 03/06/2012 2:43 PM
Joined: Jun 2006
Posts: 67
Architecht Offline OP
journeyman
OP Offline
journeyman
Joined: Jun 2006
Posts: 67
We host on a dedicated server at Peer1.


[Linked Image from boards.collectors-society.com]
Re: Tweaking performance with 5million posts [Re: Architecht] #248619 03/06/2012 3:05 PM
Joined: Dec 2003
Posts: 5,998
Ruben Offline
Offline
Joined: Dec 2003
Posts: 5,998
Well I do know that Rick and SD have worked on larger boards( I wish I had the problem)
To improve performance.
Search for "archive" with SD as the author.
Wipe out the newer than fields.
Since I think the first relevant post is like 2009
That should limit the results to something acceptable to review.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: Tweaking performance with 5million posts [Re: Architecht] #248623 03/06/2012 6:15 PM
Joined: Jun 2006
Posts: 67
Architecht Offline OP
journeyman
OP Offline
journeyman
Joined: Jun 2006
Posts: 67
I think I found it:

https://www.ubbcentral.com/forums/ub...e_any_database_diet_tips_my_f#Post229244

Looks like it was Rick. However, the advice seems to consist of prune or wait for v8.


[Linked Image from boards.collectors-society.com]
Re: Tweaking performance with 5million posts [Re: Architecht] #248637 03/08/2012 6:20 PM
Joined: Apr 2007
Posts: 3,938
SD Offline
Former Developer
Offline
Former Developer
Joined: Apr 2007
Posts: 3,938
7.6 has addressed searching on large tables ( posts, topics ). any version from 7.5.6 and below will have terrible search performance and noted in 100s of posts prior.

the only solution for pre 7.6 is to do what you did by essentially adding more RAM, so the sort ( this is the real killer ) can be done in RAM vs on HDD.

the key is adding a new index which is the year 2038 MINUS the current date / time.. this forces a naturally REVERSED index and no sort is required.

that means that your RAM requirements will go from 24Gig to 8Gig (at most) for a board your size.

i already have tested this out on 2 boards of 3 and 4 mill posts ( rough figures ) to work..

2c

note: i haven't added a post about it in the latest release notes, but consider it done already... just too much of a geeky post to really interest many...

Re: Tweaking performance with 5million posts [Re: SD] #248696 03/15/2012 9:52 AM
Joined: Dec 2003
Posts: 1,796
AllenAyres Offline
Pooh-Bah
Offline
Pooh-Bah
Joined: Dec 2003
Posts: 1,796
Originally Posted by SD

note: i haven't added a post about it in the latest release notes, but consider it done already... just too much of a geeky post to really interest many...


You're forgetting where you're posting... we eat that kind of stuff up smile


- Allen
- ThreadsDev | PraiseCafe

Forum Search
ShoutChat Box
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Mobile app?
by Baldeagle - 12/06/2019 9:32 PM
How do you change Text Line spacing?
by jorb - 11/23/2019 12:14 AM
What happened to FAQ or Forum Help
by Ruben - 11/20/2019 11:58 AM
Search feature encountering an Error message
by jorb - 11/20/2019 12:06 AM
UBB Dev
by JAISP - 11/03/2019 11:01 AM
Who's Online Now
1 registered members (isaac), 90 guests, and 311 spiders.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Amusing Terain Scenics
Amusing Terain Scenics
by isaac, August 19
Sky places
Sky places
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Snapshot build 20191023)