8 ms·
Why AO3 Was Down
- schoen 1y agoA bookmark for every view of "Gangnam Style"! https://arstechnica.com/information-technology/2014/12/gangnam-style-overflows-int_max-forces-youtube-to-go-64-bit/ https://arstechnica.com/information-technology/2014/12/gangn...
- wging 1y agoThat article was from 2014, it has many more views now (about 5.6 billion).
- notorandit 1y ago> typical database column Typical for 70s and 80s. Honestly, designing a 21st century database is a different thing if compared to back then. You can use 128 bit integers, provided that you really want to use integers. And maybe you put a timestamp along.
- j16sdiz 1y agolet's use 128bit integer and handle them like floats in php! and maybe put a 32bit timestamp along and pretend it can somehow store more than a 32bit integer can.
- jarofgreen 1y agoor use UUID/GUIDS, many databases (eg PostgreSQL) and frameworks (eg Django) support them.
- dwedge 1y agoUsing uuids can cause lots of problems with indexing, fragmentation, row size and index size
- throwawaysoxjje 1y agoNah I made the same mistake back in 2009 for a system that was storing behavior events during malware analysis. You don’t often expect to have two billion of something until you do.
- 9dev 1y agoIt's not like those two billion things just materialise in your database, right? Someone must have watched that graph climb, and climb, and climb, approaching the limit.
- detaro 1y agoIf they have that graph and remember the limit they choose 15 years ago... It's not something you think about constantly running a mostly stable code-wise site.
- shakna 1y agoSalesforce is a rather popular platform. Its defaults are also either a 18-character ID, or a 32bit integer. So, unless you take the effort to actually fight Apex, you're gonna hit this problem sooner or later.
- quickthrowman 1y agoDoesn’t an 18-character alphanumeric ID give you 18^36 combinations? 1.54 x 10^45 seems like enough combinations.
- shakna 1y agoThat's the point of the "or". You probably don't know which you're getting. It's what makes that particular design decision bite you more often.
- Sharlin 1y agoOne of the first things I internalized about databases was "just always use BIGSERIAL for primary keys". There are very few good reasons not to.
- deleted 1y ago[deleted]
- looperhacks 1y agoMaybe don't: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_s...
- rsynnott 1y agoThe website appears to date from 2008. This was a _very_ common latent bug at that point, particularly because Rails would basically force you to implement it. I assume this got fixed at some point, but for a long time all ActiveRecord models had an autoincrementing ID, which had to be a signed 32 bit int. There were scary monkey-patching workarounds if you wanted something more sensible. EDIT: And, yes, it is apparently Rails! https://fanlore.org/wiki/Archive_of_Our_Own#Timeline https://fanlore.org/wiki/Archive_of_Our_Own#Timeline
- RainyDayTmrw 1y agoIt's kinda impressive that they got to 2 billion rows - with indexes, no less - without falling over.
- jiggawatts 1y agoPoint queries — typical of this kind of app - scales as log(n) in the number of rows. (Assuming a typical b-tree database index.) This kind of workload cheerfully “scales” to your disk capacity.
- RainyDayTmrw 1y agoLocking would make that more complex, I believe?
- jiggawatts 1y agoNot necessarily. Typically a b-tree single-row modification updates only one leaf page. Higher levels change only 1/N times where N is the number of items per page, typically in the hundreds. So three levels up the chance of a lock conflict is only 1/N^3 or about one in a million. Four levels it is one in a hundred million, etc...
- deleted 1y ago[deleted]
- charcircuit 1y ago>to fix it they have to migrate the entire database to use a different type for bookmark IDs... except of course this will take a while because there are two Billion Of Them Lol You can shard them between 2 tables. Then migrate them to a single one later.
- ohdeargodno 1y agoThere's no SLA for Harry Styles porn. Run the migration, lock the table for two days and redo the same in 13 years when you get to 4 billion bookmarks.
- kijin 1y agoIn 13 years, the Unix timestamp will probably be a much bigger problem.
- camel-cdr 1y ago> There's no SLA for Harry Styles porn But what about my good night's sleep? How can I go to bed without reading about my favorite blorbos?
- ohdeargodno 1y agoReal ones use bookmarks to find them ag- ah, shit. Real ones back them up in a single .txt file
- rsynnott 1y agoI mean I’d assume they went for a 64bit integer. In a few million years, people who are into weird porn about whatever the temporally local equivalent of Harry Styles is (probably some sort of robot) will once again be mildly inconvenienced.
- madaxe_again 1y agoThis is like seeing a brick wall 40 miles down a straight road and yet still managing to drive into it, and then blaming the wall.
- darkwater 1y agoI guess that whoever maintains that infra simply hadn't thought of it or was not aware. It's not something you get for free in a monitoring system with some agent like disk usage for example. You need to know and remember you have a hard limit on IDs and be aware at which ID you are.
- hinkley 1y agoMeanwhile if I keep reminding people where the wall is and how fast we are approaching it I’m considered “negative”. That”s the real reason this stuff happens. If someone noticed, the got tired of harping on it and without the constant barrage everyone else immediately let it go out of sight, out of mind.
- darkwater 1y agoIn a company, totally. But here it is a volunteer effort, I doubt it had happened.
- ohdeargodno 1y agoAo3 doesn't have a dude getting slack alerts by a dozen monitoring agents. It's one of the last holdouts of the old, more personal internet. Hell, it's even certain that they forgot or even didn't know that the type was an unsigned int. And that's perfect. Blame the wall too, because it was running just fine. It's a site to write (mostly porn), with better uptime and more daily users than most of the companies posted on HN daily.
- camel-cdr 1y agoI wasn't sure what the percentage of porn is, so I counted the number of works for each maturity rating: 4,247,583: Teen And Up Audiences 4,173,082: General Audiences 2,816,083: Explicit 2,271,446: Mature 1,676,061: Not Rated
- Groxx 1y agoHa, a site I worked on hit this limit for the "follow relationships" table - had to build a new compound key table to migrate to, with triggers to dual read/write, to unbreak everything. In a few hours of "wtf" -> "oh crap" -> "well I guess we gotta do it right this time" and quick coding. And then I pulled apart PT-OSC to make it more... less incredibly stupid about resource use, so it wouldn't cause too much load while it backfilled. And let it run for about 6 weeks. Good luck! It's a fun problem to have - excess success, and a light puzzle to solve :)
- bilka 1y agoDo you happen to have those PT-OSC changes around? We've already migrated bookmarks with the downtime (with PT-OSC), but there are more tables that would be nice to get migrated away from int without going into maintenance or shedding a lot of load.
- Groxx 1y agoNo, it's long, long gone. When I did it, the script was a bit of a mess of trigger setup, and then a backfill that only monitored replica lag, as if the status of the much less heavily used failover instance was somehow the most important part of a database. Hopefully that's no longer true, and none of this is necessary any more. So I essentially split it in half, so I could keep only the trigger setup, and carefully read the queries the backfill would perform so I could duplicate it. And then wrote a very simple loop of "select N records, copy to new table, check how long that took. scale up by min(5%, 100), scale down by 30%, if outside target bounds". Intentionally very polite to the main DB, because once the triggers are in place it really doesn't matter how long it takes. It dropped down to single digits at peak load on some days, so I think that was the correct choice.
- 12_throw_away 1y agoFor anyone who feels like looking up exactly what this bookmark was pointing to: I did, and very much wish I hadn't!
- eknkc 1y agoWhat in the name of fuck
- heavensteeth 1y agoI ask in complete earnest: is that your honest reaction to seeing it, or did you hype it up for your comment? Personally very little could evoke that kind of reaction from me. Maybe a little, "oh, that's an interesting thing to be turned on by" but for the most part, who cares?
- ThrowawayTestr 1y agoI can't remember the time when I was so innocent that forced mpreg breeding wasn't shocking.
- Freak_NL 1y agoI mean, it's fiction. If a writer can't explore the depths of human behaviour there, then where? The only uncomfortable thing there are the explicit references to Harry Styles and Louis Tomlinson. I do take exception to using real people in fiction if you proceed to heap abuse on the characters which you model on those celebrities. (The story seems to use only the given names, but the tagging makes the link explicit.) Obviously, you can refer to real world famous people in fiction — it would be silly to write a book about 2025 America and not mention that the president is Trump if it includes political themes — but there are limits.
- rsynnott 1y agoAIUI AO3 has an “anything not literally illegal goes, as long as it’s fanfic” policy, so does get a certain amount of this sort of thing.
- zerocrates 1y agoIs it faster to convert a column like this to unsigned? Obviously assuming you don't use negative IDs in the application. That's much more of a "kick the can down the road" solution to only double your usable range, but if all positive the values in the rows shouldn't actually have to change, just the column metadata, so it could theoretically be more or less instantaneous. I guess in practice this doesn't happen; the server would rather use its generic "rebuild the table" alter method for changing a column type. But it seems like you could reasonably do it if it's a signed-to-unsigned change and there's no negative values and there's an index on the column to make checking that fact fast. Or one of those third-party/lower-level type tools could let you do it without any checking.
- adamcharnock 1y agoAn interesting idea! I suspect a major speed up would come from the fact that the column is staying the same size. So (I assume) far fewer bytes would need to be moved around.
- afandian 1y agoI don't know what DB was used in this csae, but Postgres doesn't have unsigned integers. It always struck me as hugely wasteful, as e.g. sequences start at zero by default.
- masklinn 1y agoAt $dayjob we've actually used this property once or twice: if you need to merge two tables you can keep the positive ids for the first one, and use negative ids for the second one. It only works once, but damn if it's not effective when you need it, and it conveniently flags all the records with an id under some limit (positive and negative both) as "pre-transition" record when you're looking for patterns.
- afandian 1y agoI’ve also seen positive and negative ids for entities with different properties (can’t recall what). Felt like an unnecessary hack though.
- p0w3n3d 1y agoHacker News helps me everyday break my information bubble. Archive Of Our Own is something that I wouldn't walk into when wandering through the internet
- chii 1y ago> I wouldn't walk into when wandering through the internet it's interesting that some people are on the internet but is very well insulated! AO3 is very well known for me...
- parlortricks 1y agothis is the first i've heard of it
- diggan 1y ago> it's interesting that some people are on the internet but is very well insulated Not sure I'd call it "insulated", the internet is just very, very vast, even when considering "just" the English-speaking web. Then you have all the other "versions" out there too that are kind of hidden to most people :) Anecdotal, but also first time I heard about AO3, and I'd consider myself having broad interests and generally well-read, although my interests doesn't include fanfiction so maybe not so weird I haven't heard about it before.
- deleted 1y ago[deleted]
- jorvi 1y agoIts very much a gendered thing. If you have lots of female (online) friends and late night topics with them ended up trending spicy, you might hear of AO3. FWIW the vast majority of writing on there is decidedly mediocre. There is also an even more inferior alternative called Wattpad. Funnily enough you learn that in general we aren't all that different in our tastes, it's just that what men like to watch, women like to read / imagine. Edit: to paint the picture, this[0] was sent to me a while back :-) [0]https://www.tiktok.com/@alexarowe11/video/7468462146347617579 https://www.tiktok.com/@alexarowe11/video/746846214634761757...
- olivermuty 1y agoUh, the bookmark that broke it all was to a part of the internet I have yet to experience since getting online some 30 years ago. Alphas and betas and omegas, it was a wild ride.
- Freak_NL 1y agoThe bookmark itself, for the curious: https://archiveofourown.org/bookmarks/2147483647 https://archiveofourown.org/bookmarks/2147483647 That alpha/beta/omega thing is quite huge apparently, but not something you would ever encounter outside of specific subcultures (like Archive of Our Own): https://en.wikipedia.org/wiki/Omegaverse https://en.wikipedia.org/wiki/Omegaverse
- CubsFan1060 1y agoI didn't see it mentioned, but the quick fix for this (assuming you don't depend on the order of id's) is just to alter your sequence to use the max negative int, and increment from there. Not a complete solution, but buys enough time to actually fix the issue.
- kristianp 1y agoThey're using mysql and Rails. And Jira. > Mysql2::Error: Out of range value for column 'id' at row 1 (Mysql2::Error) https://otwarchive.atlassian.net/jira/software/c/projects/AO3/issues/AO3-7031?jql=project%20%3D%20%22AO3%22%20ORDER%20BY%20created%20DESC https://otwarchive.atlassian.net/jira/software/c/projects/AO...