1

Topic: Lack of indexes?

We've just switched the MacAddict Forums from phpBB to punbb. I've been eyeing PunBB for quite some time now, but first had to wait for 1.2 to be released, and then I had to get my act together. In any case, I hacked my way through the conversion this evening. We weigh in at over 700,000 posts, so I think we win the PunBB wang measuring contest. wink It would have been somewhere around 1.4 million to give you guys a real post count boost, but we lost all of 2003's post in a server crash (same week as you guys went down. Except we didn't have backups. Whoops.).

So anyway, after converting, I unveiled the new forums, and its processor usage instantly spiked to around 180%. And here I thought PunBB was going to be king of efficiency. tongue I could restart the server, and instantly it was crippled within a minute to the point of non-usability. After trying to figure out just what was going on, signs finally pointed to the viewtopic page. Upon closer inspection, I soon discovered, that essentially nothing besides the primary columns are indexed in the tables. It was absolutely killing performance on pretty much any JOIN or WHERE clause. After adding a few indexes here and there, performance is awesome, although I'm still going to do a more detailed lookthrough whenever I get the time.

Anyway, just wondering if there was any reason for this / making you guys aware.

Hard work may not kill you, but why take chances?

Re: Lack of indexes?

did you use chacmools convertor to migrate from phpbb? if so it might be worth checking you have all the indexes in a normal 1.2 install as there were problems (although they should be fixed now)

also you might want to consider posting your site to punbbig

Re: Lack of indexes?

I'm sorry for what happened, but as Connorhd pointed out, there is a problem with the migration tool. It drops all the indexes to make the migration process quicker. Problem is, it fails to add the indexes back after all the posts, topics and users have been inserted. As far as I know, the author of the migration tool, Chacmool, has fixed the problem, but I have yet to receive an update from him. I would be glad to help you out if there's anything I can do. You seem to know your way around databases though, so if you want to make sure the database is properly indexed, install a fresh 1.2 copy somewhere else and compare that one with your live installation.

BTW, I'm so glad you chose to go with PunBB. I'm a regular visitor of MacAddict and now, I just might start reading the forums as well smile

Edit: Reading through the forums, I find the three features people seem to miss the most are polls, private messages and BBCode/smiley buttons in the post form. Polls I'm not so sure about, but maybe I need to rethink my attitude towards those other two features smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: Lack of indexes?

@GUI: just so you know(and you probably do) the link on your front page to the forums still says phpbb2 but redirects to your pun forum.

~James
FluxBB - Less is more

5

Re: Lack of indexes?

Ah, very interesting. I thought I looked at the default database schema and saw no indexes there either. But I guess I was hardly paying very good attention at 1 AM with the server going kaput like that. Thanks for the info, though, that's what I get for going with a beta converter for a production site. We like to live on the wild side. I'll go through tonight and make sure I indexed all the proper columns.

Oh, and here's the obligitory thank you for such great forum software. But really. It fulfills my ideals of forum software nearly perfectly. What's so strange is that nearly every mod that I had personally developed for phpBB was already apart of punBB. That means your brilliant. tongue

As for PMs, polls and BBCode/smiley buttons, don't let the regulars get to you. Their just resistant to change, and personally I would like to keep our forums sans-those-features. In likelihood, we'll probably cave into a few features, but in my ideal world, punBB remains free of those features with mods available for those that need them. But that's just my take on it. Your free to do whatever you want.

Connor: Already submitted to the big list. Yeah, I like measuring wangs.

Dr.Jeckyl: Yeah, I'm lazy and went for the redirect route. I'll eventually convert all those links, but mainly just wanted to get everything converted this weekend. But thanks for the heads up.

And you guys should also check out the MaxAttack theme developed by a few mods/ex-mods there. It's a shining example of why CSS rocks. Just don't be afraid of Max. wink

But this is hardly a bug report anymore. Ah, well...

Hard work may not kill you, but why take chances?

Re: Lack of indexes?

GUI wrote:

Thanks for the info, though, that's what I get for going with a beta converter for a production site.

One could as easily blame me. I've known about the problem for some time now and I could have posted a note about it or even removed the migration tool temporarily. I'm glad you sorted it out though.

GUI wrote:

Oh, and here's the obligitory thank you for such great forum software. But really. It fulfills my ideals of forum software nearly perfectly. What's so strange is that nearly every mod that I had personally developed for phpBB was already apart of punBB. That means your brilliant. tongue

Why thank you smile

GUI wrote:

As for PMs, polls and BBCode/smiley buttons, don't let the regulars get to you. Their just resistant to change, and personally I would like to keep our forums sans-those-features. In likelihood, we'll probably cave into a few features, but in my ideal world, punBB remains free of those features with mods available for those that need them. But that's just my take on it. Your free to do whatever you want.

Well, not letting the regulars get to you is difficult. PM in particular is a feature that is being requested constantly. I wonder how long I can put it off smile

GUI wrote:

And you guys should also check out the MaxAttack theme developed by a few mods/ex-mods there. It's a shining example of why CSS rocks. Just don't be afraid of Max. wink

I'm guessing that theme is installed on your forums. However, I am unable to register. I get an error message "DB Error: not found" when I click the register button. I don't believe that error is from PunBB.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

7

Re: Lack of indexes?

GUI wrote:

And you guys should also check out the MaxAttack theme developed by a few mods/ex-mods there. It's a shining example of why CSS rocks. Just don't be afraid of Max. wink

Damn. I was hoping nobody was going to use .block, .blocktable etc for styling. I was just about to remove that whole layer of divs. Oh well.

8

Re: Lack of indexes?

If there is a work-around using those classes, I can modify the css to that theme.  smile

9

Re: Lack of indexes?

Yeah, I'm trying to sort out the DB error stuff. Something with my ad rotator stuff. Stupid ads. However, not being able to register isn't good. Hopefully I'll be able to fix that quickly.

Paul: the user "roo" here is the one who mostly created it. He was requesting some changes in the HTML structure anyway, so could contact him to see what he has to say. Who knows, maybe he'd love the new changes. Or hate you forever for making him do more work. wink

Oh, database issues are fixed me thinks, so you should be able to register away.

Hard work may not kill you, but why take chances?

10

Re: Lack of indexes?

Yarg. Roo be to quick for me.

Hard work may not kill you, but why take chances?

Re: Lack of indexes?

GUI wrote:

Oh, database issues are fixed me thinks, so you should be able to register away.

Yes, it appears to work fine now.

MaxAttack is great. It really shows off how much you can change the "look and feel" without messing up the source. Good stuff smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

12

Re: Lack of indexes?

roo: I'm just thinking about bloat reduction. Three layers of divs just seems a bit much now. The way around it would be to put bottom, left, right borders on box and top, left right borders on the H2. I'm just thinking aloud really. I think the fact that the designer of wordpress referred to PunBB's markup as a little heavy got to me smile. Maybe I should stop worrying, I don't think that markup is adding particularly to page weight.

13

Re: Lack of indexes?

Tell you what...  If you'd like, I'll happily go through some of the pages and make notes on how I'd do things, and pass that list along to you.

I think that some of the mark-up could be trimmed down, but having extra wrappers in there is a real boon to designers.  What if the designer wanted to put a shadow around each post?  Or come fancy image as a border?  As it is now, you leave open many options to the designer and that's a very good thing.  But there are perhaps a few things that I'd do differently.

I won't presume to know the mark-up or the forum as well as you do, but I'd be happy to share what I came across when trying to come up with a theme that relied more heavily on images.  From there you could go through it and implement that which you like and trash what you don't like.

I figured I'd e-mail it to you, but I can put it in a thread too if you'd prefer.

14

Re: Lack of indexes?

It may be a tad heavier, but it seems very extensible to me. Granted, I haven't really messed with any HTML yet, but what roo and company have been able to do is pretty impressive with the same source. How I'm glad I don't have to maintain three different HTML sources now. smile But I'll let you designers bicker over the semantics.

Rickard: Regarding polls/PMs/whatever else. I trust your judgment, but I don't want to see punBB bloatified, simply at a slower rate. Polls seem completely superfluous to me. Who needs them? Yeah, we might end up adding them to MacAddict, but only because we have such demanding users opposed to change. I'm all about change. I think more people will agree that polls are unnecessary, but when it comes to PMs, things get more divided (at least among us before we converted). E-mail is everything PMs should be, but better. And I love PunBB's contact form. I always thought PMs were a nice way to keep your e-mail address hidden from spam bots, but allow communication, and the contact form solves that nicely. However, the biggest argument I heard for PMs were the fact that some people check their e-mail less often then the forums. So if you see a buddy online, you can shoot a PM to them and catch them. But if they're not checking their e-mail, under PunBB that contact is lost. I check my e-mail every minute (yeah, don't laugh), so that's not really an issue for me. And in my eyes if you want instant communication, hop on AIM. I can't tell you how refreshing it was to get an e-mail with content in my box, and then carry out the rest of my conversation via e-mail rather than a cumbersome forum interface.

So hold strong, I say! I know your under pressure for those features in particular, but I'm still trying to convince everyone they're unneeded. If you want them, a mod would be nice, maybe even pimp that mod on the download/features page to bring more people in that might be turned off by the no PMs/polls. But I think a lot of your users enjoy the lack of these features (I just converted a much smaller phpBB forum to punBB, and the administrator couldn't be happier), but are far less vocal about it.

I have a feeling I'm making little sense, but that's my take on it. So, fight the man! wink

Hard work may not kill you, but why take chances?

15

Re: Lack of indexes?

roo: Email me. I have started making changes already. I have actually been waiting for somebody to try a more complicated skin as it's the only way of finding out what works and what doesn't.

16 (edited by roo 2005-01-24 14:37)

Re: Lack of indexes?

Will do.  It might not be until later this week though.  I'll be as speedy as I can.

Re: Lack of indexes?

GUI: keep up the good work of forcing your users to use a better forum wink lots of people wouldn't dare change to punbb for for fear of upsetting people wink

18

Re: Lack of indexes?

Just thought you should know, MaxAttack is broken in IE6, not that users of desktop iPods would be much concerned smile

I just popped in to the MacAddict forums. They say criticism builds character. My character is now well and truly built.

19

Re: Lack of indexes?

Yeah, well that's what you get for using a crappy browser. wink

And don't mind their criticism too much. They're just in a bad mood because things have changed. I like our work, so that's all that matters. tongue

Hard work may not kill you, but why take chances?

20

Re: Lack of indexes?

Paul wrote:

Just thought you should know, MaxAttack is broken in IE6, not that users of desktop iPods would be much concerned smile

I just popped in to the MacAddict forums. They say criticism builds character. My character is now well and truly built.

Broken?  I understand that's it's pretty funky in IE, but I didn't think it was broken...

But given the scope of the site (MacAddict) and the age of WinIE, I don't mind that IE can't render it properly. 

Oh, and as for the criticism, they do that whenever anything changes.  Don't let it get to you.   Actually, I'll put that a better way:  Ignore the whining, only listen to constructive criticism.  That weeks about just about all of the comments right off. smile  (I go by registered_user over there, btw, and I'm no stranger to them whining about stuff and calling it constructive.)

21

Re: Lack of indexes?

roo: The problem is the peekaboo bug. The fix is all that HEIGHT: 1px stuff at the top of base.css.

22

Re: Lack of indexes?

oh... I thought I nabbed that one... I'll dig back into it and seek it out.  Thanks for the heads up.   There's a lot of little things that need attention in that theme, I'll add that to the list.

23

Re: Lack of indexes?

roo/GUI: There is one thing a lot of your members are saying about Oxygen which I have to agree with and thats the font size is too small. I have always found that PunBB's base font size of 10px/62.5% gives me a headache. I always change it to 11px/70% for myself, but maybe thats because my eyesight vanished down the same plughole as my hair. It has been reported that text appears smaller on a Mac than it does on a PC.

P.S. It is actually proving rather valuable observing PunBB 1.2 on a large and busy board.

Re: Lack of indexes?

Actually, I was thinking about just that earlier today. Maybe increasing the default font size a tad is a good idea.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

25

Re: Lack of indexes?

I don't see the peekabo bug anywhere Paul... some margins vanish and what not, and most of the rollovers aren't fancy in WinIE, but I don't see any obscured content.  Is there anywhere in particular you that you noticed it? 

And the default font size might be a touch small, it's noticed more among the Mac crowd probably because we typically use higher screen resolutions.  The actual size in pixels, however, is absolutely identical on both platforms.