1 Topic by GUI

  • GUI
  • Member
  • Offline
  • Registered: 2004-10-03
  • Posts: 44

Topic: More index issues

I haven't had time to really investigate any of these issues, but we've been having some speed issues, and there are just some of the culprits I've found. I mainly wanted to note what they were and what I did before I forgot. So ye be warned, this might just be me being index happy, but I seem to be in a never ending battle with MySQL...

The first issue I encountered isn't a huge deal since it's only from the mod/admin side, but still takes a noticeable hit. If you do a look up on a certain IP address, it can take some time, since the poster_ip column in the posts table isn't indexed. Indexing that seems to have fixed that up nicely.

Second issue occurs at least on the show new posts since last visit page, and maybe elsewhere. The problem arises in that it's using the forum_id index, which seems to make snese, but then when it has to go sorting it by the last_post column, that kills it. It seems as though adding a index to the last_post column will fix things nicely. It almost seems like a multiple index between the forum_id and last_post would be better, but things seem nice and peachy with just the last_post index (10-30 seconds down to a fraction of a second). Hm, and is the forums table even need to be joined on? Couldn't you just join the forum_perms directly to the topics table? Wait, this is all giving me a sense of deja vu.. Did I already go through all of this the last time I brought up indexes? Hm..

The last problem, which I haven't been able to tackle yet is the viewtopic.php. The query executes fine for relatively forums without a lot of posts, but in forums with a large number of posts, it can take a few seconds to execute. It's first limiting things by the forum_id, which is good, but then when it comes to sorting 8,000 records based on the sticky and last_post column, things turn not so nice. I tried a multi-column index, but MySQL didn't seem to like that.

In any case, these all might actually be due to my screwing around with the server and other funkiness. But, hey, maybe 1 out of 3 might help. I'm just doing some midnight rambling, but I'll try to confirm and further debug these over this weekend.

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

GUI's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: More index issues

Once again, thanks for helping out with debugging these things. Performance is a tricky thing. You can usually increase performance by adding indexes, but you have to account the increased load these indexes put on the server during inserts and updates. I will have a closer look.

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

Rickard's Website

3 Reply by Paul

  • Paul
  • Senior Member
  • Offline
  • From: Wales, UK.
  • Registered: 2003-08-13
  • Posts: 3,089

Re: More index issues

Well something certainly changed. The "show recent posts" search was taking anywhere between 10-30 seconds as well, now its as fast as on a small forum.

4 Reply by GUI

  • GUI
  • Member
  • Offline
  • Registered: 2004-10-03
  • Posts: 44

Re: More index issues

You can usually increase performance by adding indexes, but you have to account the increased load these indexes put on the server during inserts and updates.

However, when the select queries are taking upwards of 30 seconds, usually I consider the possible insert/update performance hit negligible. tongue

The only thing I find strange is why these problems didn't rear their heads before. It's not like we've had a sudden and dramatic increase in load. I think MySQL has it out for me. Or perhaps I need to figure out just what I'm doing, and stop sucking at administering the server.

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

GUI's Website

5 Reply by Paul

  • Paul
  • Senior Member
  • Offline
  • From: Wales, UK.
  • Registered: 2003-08-13
  • Posts: 3,089

Re: More index issues

I must admit I assumed it was a server thing though I'm no expert in these matters. When you first installed PunBB even some of those who didn't like it commented how fast it was in comparison to phpBB and I didn't notice any difference between macaddit and here. It also seemed to be a fairly dramatic change in performance in a very short space of time rather than a gradual tailing off of performance. Doesn't that indicate something actually happened rather than there being an underlying issue with the code.

What I would be really curious to see is a dump of macaddict or a forum of that size running on a completely different server setup.

6 Reply by GUI

  • GUI
  • Member
  • Offline
  • Registered: 2004-10-03
  • Posts: 44

Re: More index issues

Exactly. None of this makes much sense to me either. It's just that I don't have a ton of time to babysit the server to figure out what's going on, so instead me "fixing" things consists of me diving into the server and thrashing about until things are somewhat better. Perhaps not the best way to manage a server, but it seems to work. Wait, no it doesn't. wink Hopefully this summer I'll have some time to dedicate towards actually fixing stuff.

And Rickard has  a semi-recent dump of the forums, so I'd also be interested in whatever results he comes up with.Or if I'm free this weekend, I can try running the same stuff on my server. The only one I'm semi-sure about is the poster_ip column not being indexed, since loading those admin pages has always been semi-slow. The other two problems, well, it makes sense why the queries are slow loading when I look at them, and look at the explain results, but again, why they're just slowing down now baffles me. Although one guy did mention that the "show posts since last visit" was always sorta slow, but definitely not to this extreme. Even stranger, the server is performing rather healthily. There are some spikes in MySQL's thread count, but other than that the server hasn't even gone over the 100% CPU usage mark. With phpBB it would hit over 180%.

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

GUI's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: More index issues

GUI wrote:

However, when the select queries are taking upwards of 30 seconds, usually I consider the possible insert/update performance hit negligible. tongue

Yes, of course. I was primarily thinking of the poster_ip index. Adding new indexes to the posts table in particular is something I want to avoid if possible.

GUI wrote:

And Rickard has  a semi-recent dump of the forums, so I'd also be interested in whatever results he comes up with.

I have imported that dump locally and have planned to have a closer look at it, but as usual, a million other things popped up. I just need to get back into coding mode. I've had a break from everything PunBB related now for a few weeks. It takes some time to get back into the game smile

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

Rickard's Website