Forum performance issues

[Gryzor]

Hello everyone!


I'm really happy to announce that, from the looks of it, the performance issues with the server have been resolved! Through extreme skill and an eye for detail a generous amount of being-at-the-right-place-the-right-time kind of luck and a few wasted hours of peering at a screen, eyes red and almost tearful, I think I may have found the culprit. For the last couple of weeks or more that the fix was put in place I haven't encountered a single moment of downtime!


Nobody can be 100% sure, of course, so I'm asking you good people to post if you find yourselves unable to connect to the forum.


Cheers!
T

[ComSoft6128]

Up to about ten days ago I had frequent problems logging in but since then everything has been fine.

[ervin]

Hello everyone!

I'm really happy to announce that, from the looks of it, the performance issues with the server have been resolved! Through extreme skill and an eye for detail a generous amount of being-at-the-right-place-the-right-time kind of luck and a few wasted hours of peering at a screen, eyes red and almost tearful, I think I may have found the culprit. For the last couple of weeks or more that the fix was put in place I haven't encountered a single moment of downtime!

Nobody can be 100% sure, of course, so I'm asking you good people to post if you find yourselves unable to connect to the forum.

Cheers!
T

I'm dyin' to know... what was the cause?

[Gryzor]

The Likes plugin. Argh!


The thing is, tracking slow queries for some reason (still not sure why) never showed the same thing twice so I couldn't track it down to a specific function/call. Then one day I happened to be monitoring the server while using it and I saw a spike in IOPS. I couldn't connect it directly to the Likes mod, but knowing how the db tables are constructed and/or non-indexed along with some other details helped narrowing it down.


It was part luck, part method; I had been tweaking and/or disabling mods for a while now to see how things go, and I had literally just reached the point I was going to test Likes.


It kills me that, while the problem it caused was so severe and fixing it was trivial, actually tracking the issue was nearly impossible. No amount of logging, monitoring, trapping and debugging stuff ever gave us a single cause, which is really weird if you ask me, but then again I'm no db expert so there may be something fundamental I've been missing.

[ervin]

Yes, I feel your frustration.
I'm a database developer by trade, and have been looking after an insurance company's database for around 15 years.
There have been some strange performance problems over the last 2 months (the database is very, very large now), and I have spent *many* evenings examining trace files recorded during the day, and tweaking indexes.
"Fun".

[Gryzor]

Ouf! That db must be a beast, but the cpcwiki db is no small thing either. Incidentally, given the nature of the issue, I think that this is one of those instances where throwing hardware at the problem would have helped: adding an SSD to the server would have effectively masked the problem since IOPS at the edges of the disks would be a non-issue...