Previous Thread
Next Thread
Print Thread
Hop To
Joined: Jun 2006
Posts: 67
A
journeyman
journeyman
A Offline
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]
Joined: Jun 2004
Posts: 207
C
enthusiast
enthusiast
C Offline
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?
Joined: Jun 2006
Posts: 67
A
journeyman
journeyman
A Offline
Joined: Jun 2006
Posts: 67
We host on a dedicated server at Peer1.


[Linked Image from boards.collectors-society.com]
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
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
Joined: Jun 2006
Posts: 67
A
journeyman
journeyman
A Offline
Joined: Jun 2006
Posts: 67
I think I found it:

https://www.ubbcentral.com/forums/u...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]
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
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...

Joined: Dec 2003
Posts: 1,796
Pooh-Bah
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

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
0 members (), 396 guests, and 110 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)