[xdatacast]

My board is located at soompi.com.

Is there any way to speed up performance (particularly with search) for a busy forum with any kind of changes/mods to the code ? I am not looking for server/mysql/httpd tweaks as I am fully aware of all optimizations. I'm interested in changes to ibp's code. Anyone else with large boards with similar experience ?

Currently the board gets around 12,000 posts a day of activity with average concurrent users of about 4-600 and 8-1000 during peak hours. Currently everything runs rather quick even at peak hours except for search. "Search" is such a system resource hog even if the board is offline and I am the only one trying to perform a search. Loads go up to 1-2 range just by my single search with no other users on. System runs great even with 1000 concurrent users but once 1 or 2 users attempt to search, loads double/triple. We have therefore disallowed search for some time now. Is my only option to get additional servers just for the sake of search because everything else seems to run nice and fast ?

Full text is in place and the sql runs from ramdisk so IO isn't the issue. Have plenty of ram. Is it just sheer processing power I am lacking ? Currently run dual 2.8 xeons. I'm assuming it must be. Anyone with any input ?

[Luke]

[quote=xdatacast,Dec 7 2004, 06:19 AM]My board is located at soompi.com.

Is there any way to speed up performance (particularly with search) for a busy forum with any kind of changes/mods to the code ? I am not looking for server/mysql/httpd tweaks as I am fully aware of all optimizations. I'm interested in changes to ibp's code. Anyone else with large boards with similar experience ?

Currently the board gets around 12,000 posts a day of activity with average concurrent users of about 4-600 and 8-1000 during peak hours. Currently everything runs rather quick even at peak hours except for search. "Search" is such a system resource hog even if the board is offline and I am the only one trying to perform a search. Loads go up to 1-2 range just by my single search with no other users on. System runs great even with 1000 concurrent users but once 1 or 2 users attempt to search, loads double/triple. We have therefore disallowed search for some time now. Is my only option to get additional servers just for the sake of search because everything else seems to run nice and fast ?

Full text is in place and the sql runs from ramdisk so IO isn't the issue. Have plenty of ram. Is it just sheer processing power I am lacking ? Currently run dual 2.8 xeons. I'm assuming it must be. Anyone with any input ?

<{POST_SNAPBACK}>

[/quote]


I think it saves search results in a database and checks against that for future searches..... So the search table could be really hudge...... Im not sure though how that works... My idea would be to put a flood limit on search. Like where you can only search ever 60 seconds or something. But then you got to make the actual search a bit faster, which I *think* has something to do with that search table.

[Tim Dorr]

Running on a RAM disk? What happens when you reboot? :S

[SuperM]

I am so envy for such quick board running.

Take my congrats and maybe you are doing server optimizations for a little charge? As I understand you did a great job of optimizing for your server.

Perhaps I have the same server config as yours, I am also located on ThePlanet and have Dual Xeon, 2 scsi and 2 GB memories, but my sql going down every 2-5 hours and board is extremely slow Though I have only 200-300 users online at one time (My board is located at supermama.com/InvisionBoard).

Your board is perhaps one ot the IPB biggest I have saw ever

[RafaelT]

You could try submitting a support ticket. IPS might take a look at it for you

[marcele]

Generally I would advise against doing a lot of php tweaks until you have exausted all your server side optimizations..After you make a lot of php tweaks it makes your board harder to upgrade (Matt said that ipb optimizations are coming soon!!)

I know that this isn't a code tweak .. but it would speed things up a lot :

Looking at your board I see that you are still serving everything from apache

First I would recommend serving all your static content and images from a non-forking webserver (it can even be on the same machine) (tux, boa) .etc.

Then I would make sure that my indexes were all in order using Matt's index checker ..

Matt is going to optimize 2.1 (I think I read somewhere that it is going to be released around Jan 1/2005 ) ..

Also I would make sure that I had mod_gzip installed ... and use that instead of ipb's php gzip .. That should cut down on the cpu usage a bit...

Also check out this taskmanager mod for doing a monthly db optimization via ipb's task manager (remember to only schedule it early in the morning as it is db intensive)..

http://mods.invision...ndex.php/f/4102

[mrbuzz]

Yes, definitely try to separate your static content as much as possible. We've found it greatly reduces disk i/o so apache doesn't have to process hundreds of ~500byte gif file requests in its queue.

I'm also confused about having sql data on ramdisk....I can only imagine you meant perhaps the mysql index files and not the data itself. Well, either way, your server is similar to ours at the planet and i hope you have your sql data on a separate scsi disk, away from your web files. We also have similar activity in terms of posts/week.

Searching has been a problem for us as well and we've also disabled searching for pretty much everyone except for our subscribers and moderation staff. I have made some changes to our search_mysql_ftext.php which only searches thru the past week of fulltext indexed posts rather than all of our 4.8m posts (which seems to do nothing except lock up the posts table for us, for a good few minutes). I have yet to discover a way around that. While the changes I made greatly helped improve search performance, it has also greatly crippled it. Though I've also learned that users typically only is interested in something they saw a couple days ago and posts from a month back isn't as useful to them.

[xdatacast]

[quote=mrbuzz,Dec 7 2004, 07:01 PM]Yes, definitely try to separate your static content as much as possible.  We've found it greatly reduces disk i/o so apache doesn't have to process hundreds of ~500byte gif file requests in its queue.

I'm also confused about having sql data on ramdisk....I can only imagine you meant perhaps the mysql index files and not the data itself.   Well, either way, your server is similar to ours at the planet and i hope you have your sql data on a separate scsi disk, away from your web files.  We also have similar activity in terms of posts/week.

Searching has been a problem for us as well and we've also disabled searching for pretty much everyone except for our subscribers and moderation staff.  I have made some changes to our search_mysql_ftext.php which only searches thru the past week of fulltext indexed posts rather than all of our 4.8m posts (which seems to do nothing except lock up the posts table for us, for a good few minutes).  I have yet to discover a way around that.  While the changes I made greatly helped improve search performance, it has also greatly crippled it.  Though I've also learned that users typically only is interested in something they saw a couple days ago and posts from a month back isn't as useful to them.

<{POST_SNAPBACK}>

[/quote]

You're like in the exact same boat as I am. I should have been more clear. You're right, I only keep the MYI file on ramdisk. However keeping the MYI file on ramdisk and keeping a large mysql cache in memory greatly helped performance. I would like to keep the MYD file on ramdisk as well but the memory limitations of the TC servers prevents me from doing that.

I understand the benefits of using something like thttpd but apache isn't really impacting the server during normal search free use. Regardless I will give it a try. Been meaning to get around to it but never actually implemented it. Still have it installed from a compile few months ago. Actually neither is mysql as long as search isn't used. Search is what cripples the server. I can understand if things are running slow and poor to begin with but as far as performance is concerned with everything other than search, it's perfectly fine. All pages load within 2-3 seconds. Most almost instantly even with 800 users on. Here's an actual example...

Scenario : 750 users online and loads are averaging let's say between 2-3 (actually rarely goes above 3 other than short spikes)... I have around 230 apache processes and 100 mysql processes going. Plenty of free buffer/cache memory.

I open 3 windows and initiate 3 search queries at the same time. The loads go up to like 10-20... httpd processes max out limit in httpd.conf... mysql processes max out limit in my.cnf... all within a few seconds and it takes around 2-3 minutes to recover from it assuming I don't close the search windows. Even at that sometimes the searches completely time out all together.

The only thing that seems to help is keeping the db below 2,000,000 posts. We have pruned constantly. The current post count is the highest we have allowed it to be because we simply gave up on the efforts of allowing users to "Search".

I might definitely try the limiting the search to a finite amount of time. How did you implement that ?... I'm assuming you manually changed the skin/source for all instances of search on the forums. Such a simple yet brilliant idea that somehow never occured to me..hehe... Thanks a bunch~~~

[marcele]

[quote=xdatacast,Dec 7 2004, 08:07 PM]I might definitely try the limiting the search to a finite amount of time. How did you implement that ?... I'm assuming you manually changed the skin/source for all instances of search on the forums. Such a simple yet brilliant idea that somehow never occured to me..hehe... Thanks a bunch~~~

<{POST_SNAPBACK}>

[/quote]
Limiting search is a lot easier than that .. Just set your "Number of seconds for search flood control" under member user groups in the ACP.. No editing of skins required ..

[xdatacast]

[quote=marcele,Dec 8 2004, 12:22 AM]Limiting search is a lot easier than that .. Just set your "Number of seconds for search flood control" under member user groups in the ACP.. No editing of skins required ..

<{POST_SNAPBACK}>

[/quote]

It doesn't matter if I limit the amount of searching. It's the search "engine" itself thats causing serious lockups. Even if I made the limit 10 minutes, with 800 users online, there's bound to be more than 3 searches going on at the same time which is more than enough to cripple the server. If I limit the amount of data it can search to begin with, it will greatly improve the search performance. I know this because when the database is approximately only 1,000,000 posts, search works rather well...

[marcele]

[quote=xdatacast,Dec 7 2004, 09:52 PM]It doesn't matter if I limit the amount of searching. It's the search "engine" itself thats causing serious lockups. Even if I made the limit 10 minutes, with 800 users online, there's bound to be more than 3 searches going on at the same time which is more than enough to cripple the server. If I limit the amount of data it can search to begin with, it will greatly improve the search performance. I know this because when the database is approximately only 1,000,000 posts, search works rather well...

<{POST_SNAPBACK}>

[/quote]
Looks like this might be a limitation of mysql's full text indexing ... not the ipb software ..
From this post on mysql.com (look at the bottom of the page in the comments) it looks like other people are getting the same poor results with anything over 2 million rows... (quote: 30 seconds to a minute or longer...).

http://dev.mysql.com...ext_Search.html

If this is the case .. then your only real option (if your want to keep the search, without doing anything fancy server wise) is to limit the search results (say the last month or so) .. or prune your number of topics in your forums to be around the 1,000,000 mark

Maybe mysql 5 will have a better full text index capibility when it ever comes out ..

[mrbuzz]

[quote=xdatacast,Dec 7 2004, 10:07 PM]The only thing that seems to help is keeping the db below 2,000,000 posts. We have pruned constantly. The current post count is the highest we have allowed it to be because we simply gave up on the efforts of allowing users to "Search".

I might definitely try the limiting the search to a finite amount of time. How did you implement that ?... I'm assuming you manually changed the skin/source for all instances of search on the forums. Such a simple yet brilliant idea that somehow never occured to me..hehe... Thanks a bunch~~~

<{POST_SNAPBACK}>

[/quote]

2 million was the magic number I've found as well. Anything beyond that is hell. Though the sorting afterwards is also excrutiatingly slow for pagination. I've tried a few numbers here and there and found that searching over the last ~100,000 posts (about 7-8 days for us) was a good balance between performance and search. I presumed running a limit for 0, 100000 will be faster than post_date > time() - 60*60*24*7.

I've made the changes to my fulltext searching shortly after I realized that we can never fully fix search because searching in IPB is handled by mysql and not using a btree search table model. I really wish there were some better optimization/tweaking techniques for fulltext indexes in mysql. I come from a purely mssql background so a lot of mysql stuff is new to me. If we really needed search, I eventually may move our boards into a replication setup and run searches and other stuff only on the 2nd server....though still can't afford that yet.

Anyway, the changes I made are to my /sources/lib/search_mysql_ftext.php. The bad news is that you have to upgrade to mysql 4.1.7 or later because I use subqueries (to select a temporary table out of the posts table before applying MATCH AGAINST). There might be a better/more optimized way. *shrug* My mysql language/syntax knowledge aren't very good at all so I've resorted to relying on my mssql knowledge to work here. The last 100,000 post number is configurable via a var at the top of the script.

 search_mysql_ftext.php (21.07K)
Number of downloads: 121

This only helps with fulltext searching. I've also made changes to my profile page's "Find all posts by member", though that change involved a number of template bits that I can't remember atm. Anyway, let me know how it works for you and if you make any improvements on this since I'm sure your mysql knowledge is far better than mine. Good luck!

[Matt]

Did you try rebuilding your full text indexes after the upgrade?

[xdatacast]

[quote=mrbuzz,Dec 8 2004, 02:37 AM]2 million was the magic number I've found as well.  Anything beyond that is hell.  Though the sorting afterwards is also excrutiatingly slow for pagination.  I've tried a few numbers here and there and found that searching over the last ~100,000 posts (about 7-8 days for us) was a good balance between performance and search.  I presumed running a limit for 0, 100000 will be faster than post_date > time() - 60*60*24*7.

I've made the changes to my fulltext searching shortly after I realized that we can never fully fix search because searching in IPB is handled by mysql and not using a btree search table model.  I really wish there were some better optimization/tweaking techniques for fulltext indexes in mysql.  I come from a purely mssql background so a lot of mysql stuff is new to me.  If we really needed search, I eventually may move our boards into a replication setup and run searches and other stuff only on the 2nd server....though still can't afford that yet.

Anyway, the changes I made are to my /sources/lib/search_mysql_ftext.php.  The bad news is that you have to upgrade to mysql 4.1.7 or later because I use subqueries (to select a temporary table out of the posts table before applying MATCH AGAINST).  There might be a better/more optimized way. *shrug*  My mysql language/syntax knowledge aren't very good at all so I've resorted to relying on my mssql knowledge to work here.  The last 100,000 post number is configurable via a var at the top of the script.

 search_mysql_ftext.php (21.07K)
Number of downloads: 121

This only helps with fulltext searching.  I've also made changes to my profile page's "Find all posts by member", though that change involved a number of template bits that I can't remember atm.  Anyway, let me know how it works for you and if you make any improvements on this since I'm sure your mysql knowledge is far better than mine.   Good luck!

<{POST_SNAPBACK}>

[/quote]

Thanks.. I will definitely try that and definitely if anything new or good turns up I will let you know.

Matt :

I have tried but I will try again. Don't remember if I did it for 2.0.2 to 2.03 upgrade. I have even tried completely rebuilding the index file.

Thanks Everyone...

[Matt]

Sorry, I meant after the upgrade to MySQL 4.1 (if you've done that yet).

There are some good optimizations in the full text indexes which require a drop and add index to show.

[mrbuzz]

^ Really....good to know. As far as rebuilding indexes, I've only gone as far as REPAIR/OPTIMIZE table....i'm guessing you're saying even that's not enough for taking advantage of the new optimizations? I'll try the drop add later.

[Matt]

This is what I understand from reading up on the new full text indexes in MySQL 4.1:

MySQL 4.0.x used a standard index which was something like (row ID, word, weight). This meant that a word would only be stored once per index row and as these entries weren't fixed length only a linear search can be used to locate row IDs for each word searched when using boolean mode searches which IPB does by default.

MySQL 4.1 has a 2 level index structure for fulltext indexes. This means that when a word is stored in an index block it has a sub list of word weight and the DB row ID which can be a fixed length which allows for a faster binary search rather than picking through each row as a fixed length allows the pointer to move forward without testing for a result.

It does require a complete drop and add to use the new indexing system.

[Nimdock]

Then maybe IPS hosting should upgrade to MySQL 4.1.

[xdatacast]

[quote=Matt,Dec 9 2004, 07:48 AM]This is what I understand from reading up on the new full text indexes in MySQL 4.1:

MySQL 4.0.x used a standard index which was something like (row ID, word, weight). This meant that a word would only be stored once per index row and as these entries weren't fixed length only a linear search can be used to locate row IDs for each word searched when using boolean mode searches which IPB does by default.

MySQL 4.1 has a 2 level index structure for fulltext indexes. This means that when a word is stored in an index block it has a sub list of word weight and the DB row ID which can be a fixed length which allows for a faster binary search rather than picking through each row as a fixed length allows the pointer to move forward without testing for a result.

It does require a complete drop and add to use the new indexing system.

<{POST_SNAPBACK}>

[/quote]


Great~~~ Is there anything on your end that has to be done to IPB for this to work ? or can I just compile and install 4.1 and I'm ready to go ?

[copland007]

Good to know about the indexes Matt.

Loren was testing out 4.1 and ran into a problem with the module I made for invision, just wanted to give you guys a heads up. Seems they have made some interface changes to the usage of some functions which caused a slight bubble for me. Specifically the 'MOD' function now supports N MOD M syntax, so any of my sql statements that were selecting a row 'MOD' caused syntax errors, more info of that guy specifically is here:
http://dev.mysql.com...ns.html#IDX1355

I don't know if it affects any part of IPB, just wanted to give a heads up.

-Matt


edit: Also in the soon to be released 4.1.8 they mention in the changed section:

Quote

FULLTEXT index block size is changed to be 1024 instead of 2048.

This post has been edited by copland007: 09 December 2004 - 06:29 PM