Code tweaks (21st Nov edition)

Message Bookmarked

Bookmark Removed

I think i've improved the permissions handling code so that adding permissions SHOULDN'T cause such a server lock up. (it was lazily coded before). a couple of quick tests seems to indicate that it has improved, but experience has told me that it's best to wait til the server load is higher (usually about 16:00 GMT, esp fridays)

― Britain's Obtusest Shepherd (Alan), Monday, 21 November 2005 22:32 (twenty years ago)

in addition, though a couple of the really bad poxyfules today have been me fixing issues, there does seem to be an issue with a table somewhere cos recreating newanswers is taking WAY too long. i think it might be the big messages table. this is at the core of the site of course, so i won't attempt a check/repair until the server is at its lowest (this weekend sometime)

― Britain's Obtusest Shepherd (Alan), Monday, 21 November 2005 22:44 (twenty years ago)

Is the new permissions stuff rolled out yet?

― Occam's Reznor (ex machina), Monday, 21 November 2005 23:10 (twenty years ago)

yes, the permissions code is in place - i'm waiting to try it when the server is more stressed. say tomorrow afternoon.

― Britain's Obtusest Shepherd (Alan), Monday, 21 November 2005 23:14 (twenty years ago)

well that's already kind of fucked :-(
got quite a pxyfl when steve locked an ILE thread

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 00:52 (twenty years ago)

i'm gonna try locking this at 12:10 GMT...

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 12:10 (twenty years ago)

FUUUUUUUUCK

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 12:37 (twenty years ago)

ok, here's the deal. every time a thread is viewed (thread.php) the permissions table is read ONCE for possible permission exceptions that would stop posting to the thread. so when there are lots of people requesting threads there are lots of SELECTs happening from this one (quite small < 1000 rows).

when a permission is added, this is an "INSERT low_priority".

Why on earth would this lock up the site?

(i tried changing the table "engine" to a record-level-lock capable at one point and it may even be still fixed that way - i'd have to check.)

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 12:44 (twenty years ago)

As an FYI Alan, I deleted a thread on ILM just before that last outage. I don't know if it's connected or not.

― Pashmina (Pashmina), Tuesday, 22 November 2005 12:47 (twenty years ago)

thread deletions don't cause much in the way of sql lock up. yet simply adding a row to the permissions table does consistently (at times of medium use) cause a pile up of sql threads that takes a good 10 mins to clear, and another 5 or so to get back to normal.

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 12:56 (twenty years ago)

yes i've always found deletion not much of a problem but locking is disastrous even though it is the more desirable option in most cases.

― Sororah T Massacre (blueski), Tuesday, 22 November 2005 13:14 (twenty years ago)

oh and removing permissions works fine. BAH

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 13:22 (twenty years ago)

Gawd, I locked that thread yesterday, thinking it would cause less fuckage, as well!

Mod stuff I've been doing on ilm has almost all been deleting messages, mainly spam, I haven't locked very often recently.

― Pashmina (Pashmina), Tuesday, 22 November 2005 13:22 (twenty years ago)

even editing messages - and the messages table is HUGE and constantly being accessed obv - is quite a quick job. it's just INSERTs into permissions, and only at times when there is some server demand (i.e when same table is being read), that things go nutso :-(

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 13:27 (twenty years ago)

you've reminded me that i was going to start up another modreq thread about some steps to stop anon spamming. maybe later in the week, when i've had a bit more fiddle.

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 13:28 (twenty years ago)

Is the table being locked first? I'm not sure if this is relevant, but the MySQL handbook warns about low-pri locks on tables with lots of reads:

If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no threads that want a READ lock. When the thread has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables

― stet (stet), Tuesday, 22 November 2005 14:25 (twenty years ago)

thanks, i will look into that

― Britain's Obtusest Shepherd (Alan), Tuesday, 22 November 2005 15:05 (twenty years ago)

every time i read the mysql docs i change my mind about the relevance of insert delayed vs insert low_priority.

i'm SURE it was delayed before, and i changed it to what it is now (low priority) cos the docs seemed to suggest it, but NOW it seems that delayed is right again :-(

― Britain's Obtusest Shepherd (Alan), Wednesday, 23 November 2005 00:34 (twenty years ago)

try again tomorrow...

― Britain's Obtusest Shepherd (Alan), Wednesday, 23 November 2005 00:59 (twenty years ago)

Could you not just insert straight away, as with messages?

― stet (stet), Wednesday, 23 November 2005 01:59 (twenty years ago)

you'd think.

i'm gonna have another go... apologies in advance to all current users :-(

― Britain's Obtusest Shepherd (Alan), Wednesday, 23 November 2005 15:20 (twenty years ago)

bit of a SQL backup but not to the point of pxyfule-ing.

trouble is the server load is so variable, it's hard to proper quantitative checks :-(

i'm coming to think that the occasional "optimize table" (at low load times) may well be the way to go. concurrent inserts and selects on MyISAM tables shouldnt really conflict AT ALL unless there are deleted rows in the table, and they can be cleared up with optimize table.

or so i'm given to understanding.

― Britain's Obtusest Shepherd (Alan), Wednesday, 23 November 2005 15:24 (twenty years ago)

i did another one with not a blink. hmmm

― Britain's Obtusest Shepherd (Alan), Wednesday, 23 November 2005 16:00 (twenty years ago)

i don't want to jinx things, but it's all been quite smooth recently. not permissions - just stuff in general. i think the odd optimise table here and there must be doing wonders.

― Britain's Obtusest Shepherd (Alan), Thursday, 24 November 2005 13:47 (twenty years ago)

I'm honestly not being sarccy when I say, of course things are smooth today, because it's Thanksgiving, which means that most American users are offline.

Not that there's anything wrong with American users - just that the server load is considerably lightened when half the users are not online.

I'm sure your coding updates are great, but don't count your chicken until next week when they're all back online!

:crosses fingers that I'm wrong::

― -Stroke-Model (kate), Thursday, 24 November 2005 13:55 (twenty years ago)

d'oh! slaps head. thanks Kate :-)

― Britain's Obtusest Shepherd (Alan), Thursday, 24 November 2005 15:29 (twenty years ago)

has the situation improved regarding locking threads/posters? as in to the extent that it causes fewer problems than actually deleting threads?

― Sororah T Massacre (blueski), Friday, 25 November 2005 13:56 (twenty years ago)

erm, not so sure to be honest. i had a bad pile up this morning. it's erratic. also i will be making thread deletions into "move to board 0" this weekend, so thread deletion won't be so irreversible

― Britain's Obtusest Shepherd (Alan), Friday, 25 November 2005 14:15 (twenty years ago)

you know what, discounting permission changes it STILL feels smoother than usual here. is there a boxing day for thanksgiving??

― Britain's Obtusest Shepherd (Alan), Friday, 25 November 2005 15:53 (twenty years ago)

Well, not a boxing day, but most people have the Friday off, as it's the busiest day of the year.

― Sophisticated Boom Boom (kate), Friday, 25 November 2005 15:56 (twenty years ago)

yeah, most americans get the thursday and friday off, and so then there's a lot of travel and family-visiting--the weekend will probably be lighter than a normal weekend as well because people are trapped at grandma's without internet.

― teeny (teeny), Friday, 25 November 2005 16:02 (twenty years ago)

So be prepared for Monday crashing as everyone simultaneously posts going "HI DERE WHAT IS IT MADE"

― Ned Raggett (Ned), Friday, 25 November 2005 16:51 (twenty years ago)

This thread has been locked by an administrator

You must be logged in to post. Please either login here, or if you are not registered, you may register here.