Server Help :: View topic - Size of the forums

Server Help

Trash Talk - Size of the forums

Mine GO BOOM - Wed Apr 18, 2007 4:30 am
Post subject: Size of the forums

The forums have reached a size where they don't handle as well as they should. To me, having the server take a second to make a page sucks. When posting or editing, the search table really eats up a good bit of time. For the past few months, I've been going over the slow query logs of MySQL to see what I need to do to help speed that up.

Number one problem is that since I don't want to waste money on you guys, I bought a very basic VM. By basic, everything about it is great, the only down fall is 192M RAM. This isn't burst/min, this is dedicated to me and is static. Which was great for a while, since I didn't use the server for too much. Then, woolnet started to act up, so I transfered a lot of other processes over to here, more specifically the whole email server for everything hosted there. I can get Postfix, Apache, and MySQL down pretty good, but SpamAssassin is just a hog.

All processes running at full capacity are using around 160MB. This leaves around 30MB for OS buffering (disk cache). For those of you that don't know, that means instead of having every file access have to wait for the hard drive, the OS may have parts of the file in memory already cached and hits that instead. Very handy when MySQL looks at databases. Of course, the forums database alone is 72MB right now.

Normally, this wouldn't be a problem. Disk I/O in Xen (the virtual machine program I'm running under) is pretty weak, in that if someone else's VM is hitting swap, my disk accesses will take a while. To give you an idea about this, on average the system is using 1.5% of the processor and 8.5% on I/O wait. That is bad.

How to improve. My current thoughts have been to get a second server and move off some of the load to that (such as emails/antivirus checking). Maybe even put up a MySQL slave on that machine. Or, I'm looking into just getting a whole dedicated machine for myself. Both of these involve more money, which I'm not really against spending, but I'm frugal. If I would go the dedicated server route, I'm looking into buying rack space at some local data centers here in Las Vegas and putting in my own rack. Or wait a couple of months and when I buy a house, see if I can get some good lines installed.

The next option for improvement is to help deal with the database being so fat for something that shouldn't be. The SQL calls to view the forums isn't too bad yet, though I'll probably look into merging a lot of the similar SQL calls into only a few, so when they do hit the disk, they'll get everything they need in a few calls instead of spread out over 26 separate calls. But for new posts, this won't do much.

About 90% of the time spend on creating/editing a post is the search database. Phpbb has a horrible search engine. I have been looking into different ways to replace it. The easiest one so far has to been to use MySQL's fulltext indexes (12MB) and rewrite search.php to deal with that instead of their current wordlist (3MB)/wordmatch (38MB) tables. Technically, this is a slower replacement for phpbb's search engine, but it is a hell of a lot faster on updates. For a low-memory situation such as what it is currently on, the smaller index couple mean it being quicker.

Another advantage is that MySQL's fulltext index has scoring for the relevance of the search and a nice booleaning method as well. This should help a bit with finding better topics when searching while still giving me the booleaning searching that I like better. If I do go about the fulltext method, I can maybe even include private message searching, as it would be very simple to include that as an extra option. And I can finally replace the god-awful search caching that they do. Ugh, it is a database, don't encode different data inside one text field.

A final option would be to go off to something else. I'm really enjoying a specific user edited version of Punbb that has a very well designed topic/post read history, as well as dynamic forums. This would lose all my little changes, but then I'd have my version control setup from the start and can more easily integrate with official upgrades.

Any thoughts? Probably by next week I'll either have decided on getting another server to offload the mail server to or have revamped the search engine.

Maverick - Wed Apr 18, 2007 7:07 am
Post subject:

If you have too much money, you would be best off with a dedicated server.

I think that fixing the search engine would only be another temporary fix, it doesn't really provide a permanent solution since you can expect that the forum keeps growing.

Maybe that specific user edit version of Punbb you're speaking of might be a good solution if you can migrate the user database and all of the topics to the new system.

Dr Brain - Wed Apr 18, 2007 8:22 am
Post subject:

Can you move the old old posts to a different database, and add a checkbox on search.php for posts older than 2-3 years? It'll probably be pretty slow when searching them, but the hope is that it'll get thrown out of the cache relatively quickly.

Smong - Wed Apr 18, 2007 8:49 am
Post subject:

Buying more hardware sounds like the easiest to me.

A new forum sounds nice but will it really be any better and what will happen to the old one (specifically attachments). Plus it sounds like a lot of effort to try and move stuff across, I wouldn't change the forum unless I knew I could transfer the users. The old posts could stay on the old forum, just make it read only.

Doc Flabby - Wed Apr 18, 2007 9:44 am
Post subject:

If you after a control panel free box have a look at http://corenetworks.net i have just (well about 2 weeks or so ago)rented a dedi box from them to replace the VPS that PSS is currently hosted on. The reason for the upgrade is I want to be able to test my biller, and the VPS doesnt have enough memory to let me do it and host the backup dir server, 2 asss servers and subversion for me (im currently as 90% memory use

)

Bizarrly its cheaper for me to rent this dedi than it is to upgrade my vps....And at Â£1=2$ cheap for me

Its costing me the equivenlent of have 3 mac'd meals a month.

Only problem i can see myself running into is 1000GB/mo bandwidth limit before overage charges start.....But I dont plan on having that much traffic on that server.

Animate Dreams - Wed Apr 18, 2007 11:37 am
Post subject: Re: Size of the forums

Have you considered switching webservers? I know most of the load is the database stuff, but one of my friends runs a direct-download server on a similarly limited machine, and he recently made the switch from apache to lighttpd, and the site has run a lot smoother ever since. Plus, he said he liked the config settings a lot more. I personally don't like it because yum only installs php for apache naturally, so if I use lighttpd, I have to install php myself. I'm assuming that wouldn't really bother you. Now on to the more important stuff:

Mine GO BOOM wrote:

Or wait a couple of months and when I buy a house, see if I can get some good lines installed.

You want to elaborate on this process?

Smong - Wed Apr 18, 2007 1:20 pm
Post subject:

1. Go to a preschool.
2. Browse the art work.
3. Take/Buy some good lines.
4. Hang them up on your wall at home.

This is not a good line:

These lines are segmented since they are being regulated:

Cyan~Fire - Wed Apr 18, 2007 2:14 pm
Post subject:

Is Smong... high?

Smong - Wed Apr 18, 2007 2:18 pm
Post subject:

I got a deadline coming up and I'll do anything to avoid work, which is not good for my grades

Mine GO BOOM - Wed Apr 18, 2007 3:41 pm
Post subject: Re: Size of the forums

Animate Dreams wrote:

You want to elaborate on this process?

Getting fiber installed, be it FiOS or a shared T1/T3 line.

To transfer to a new database is very simple. The attachments are stored as files, not in the database, so there is no conflict there. The only thing that I would lose it all the little coded changes, such as a bunch of people's titles, some of the pages that have the Move to SS Forums button at the bottom, junking of posts, etc. The good features I can readd to any other forum system reasonably quick.

The search fix would actually be a reasonable fix enough. Like I said, phpbb's search engine is faster at searching, but horrible at insertion. The word match table is 1.5 million rows. MySQL's fulltext index is well optimized and can handle our 70,000 post without a hiccup. This will reduce the cache target by 30%.

I still have the previous forum's posts, but one of the reason I didn't keep it in here is because of the searching. Having them locked was no problem, but it flooded the search table to where it is now. With going to a new forum system from phpbb, the topics wouldn't be locked, user account would transfer over nicely (can hack the password part into the code to update the database to new password system upon first successful login, while checking previous password). That is a bit more long term.

Just airing my thoughts for any feedback.

Chambahs - Wed Apr 18, 2007 4:29 pm
Post subject:

Delete the 15 pages of flaming pit of hell.

And start deleting topics that are retarded/waste of space.

Mine GO BOOM - Wed Apr 18, 2007 4:34 pm
Post subject:

Deleting topics to save space won't do anything. That is like drinking diet soda when you super-size your meal.

Animate Dreams - Wed Apr 18, 2007 5:09 pm
Post subject: Re: Size of the forums

Mine GO BOOM wrote:

[..]

Getting fiber installed, be it FiOS or a shared T1/T3 line.

I wasn't aware T1s were available to be run to people's houses. Heck, it was a big enough hassle trying to get Bellsouth to run T1s to cell towers when I worked at Verizon Wireless(not that that was MY job, but I definitely heard about it), I can't imagine it being an easy process for you, either. <3 FiOS though.

Mine GO BOOM - Wed Apr 18, 2007 5:29 pm
Post subject:

Depending on the areas I can move to, different ones already have lines setup to the neighborhood/etc and would only pay for the last mile. So in some locations, it is viable. In others, not a chance in hell.

CypherJF - Wed Apr 18, 2007 6:00 pm
Post subject:

<off-subject>Verizon was supposed to have fiber to everyone's house by like 2012 or something about that timeframe. But obviously, they're not going to make it. Our area had a revised date of 2016 or thereabout. Until then my family is stuck w/ dial-up because of lame Pennsylvania laws and FCC rulings. (Good news though townships of Pennsylvania may soon be allowed to offer free wifi to their communities; Verizon lobbied hard and got it where it was outlawed except for Philly which had a Wifi project underway.).</off-subject>

Smong - Sat Apr 28, 2007 8:51 am
Post subject:

The forums are really slow today.

Cyan~Fire - Sat Apr 28, 2007 10:57 am
Post subject:

Yeah, I've been getting that off and on for a while, too.

CypherJF - Sat Apr 28, 2007 2:16 pm
Post subject:

Ditto; but thought MGB might have been changing things.

Mine GO BOOM - Sat Apr 28, 2007 2:55 pm
Post subject:

I've gotten the server load error, but am really unsure why it prints it so many times. It is just in an if check for the SQL results and does a message_die. Though it is in the footer, which is done after the rest of the page prints.

I have done a couple of things, but nothing to really improve the overall speed. My time has been spent on other projects recently. MySQL is really hit or miss right now with queries (even basic ones), so I probably will lead towards going to my own dedicated machine.

EDIT: Taking the internal search feature of phpbb down to give a trial run to see how well the forums work without the whole search table (which I already have tools to rebuild).

The two colocation centers I've narrowed down to be in Las Vegas and actually work with customers with 1 server (and not a full rack) are: MarquisNet and Dataside. For location wise, MaruisNet is down the street from where I work, while Dataside is in downtown Vegas, a place I generally try to stay away from. It does look like it has its own parking lot, so it maybe a valid place still.

Maverick - Tue May 08, 2007 9:27 am
Post subject:

I hate that you took down the search function, MGB

it worked quite well imo and I could always find the topics I needed.

Confess - Tue May 08, 2007 11:19 am
Post subject:

Why not look into getting a dedicated server?

Doc Flabby - Tue May 08, 2007 11:50 am
Post subject:

Confess wrote:

Why not look into getting a dedicated server?

That is essentially what this entire thread is about -_-

Mine GO BOOM - Tue May 08, 2007 7:56 pm
Post subject:

Confess wrote:

Why not look into getting a dedicated server?

I'm waiting for a response from the companies about their price listings. I'd like to have it all setup within two weeks.

Maverick wrote:

I hate that you took down the search function, MGB :(
it worked quite well imo and I could always find the topics I needed.

It was killing phpbb. I enabled MySQL's fulltext boolean search now.

Now when updating posts, it won't kill the search when it tries to cache the searching data as MySQL does very well with its indexes. Searching is pretty slow right now, but can have much better control over the results. If something doesn't show up soon with my other options I'm trying right now, I'll probably edit in using the relevance option and can return results based upon scores of the match.

Mine GO BOOM - Sun May 13, 2007 5:18 pm
Post subject:

I got the quotes back from the places around here, and am going with MarquisNet for a co-location. Just purchased the parts for a 1U server (excluding hard drives) and will be setting up a Xen system on it.

With my current complaints about Xen, why am I bothering with it on my own server? Because I'll have complete control over the design of the system and everything running on it. I have been playing around with setting it up in virtual machines (getting Xen working under VMware was fun) and spare computers I have here, and I love it. The system I'll be using it with will only be two cores, but my plans for how to allow others to run their own stuff on my spare resources (including ASSS) without effecting the system as a whole have tested out very well (ie: all unreliable vms run only on one core).

I expect local testing at my place will be done by next week, will install it into the data center sometime next week, do some tests on the system during the rest of that week, and hopefully by the last week of the month begin to switch domain names away from the current Unixshell host to my own rack. I plan on keeping the Unixshell site up and running till the end of June as a backup in case something fails, but not anymore after that.