1. 13

      For the curious, here is the SQL which generates a view, used by this rails controller and model.

      1. 4

        Out of interest, why is this generated on demand? When you post a reply, you must be looking up the parent post already (or have its unique identifier from the generated HTML). Can’t you just look up the author of that post and add a new row to a replies table with the {parent ID, post ID}? If you have that table with an index for parent ID, it’s easy to select all replies to posts from a single person. Given the small number of posts any given individual does, it should then be fairly cheap to do a join to look up the dates of the posts and sort.

        1. 3

          It seems that there is hell lot of missing indices that should greatly improve performance of that query.

          For example indices on:

          • is_deleted
          • is_moderated
          • is_following
          • do partial index on score instead of full index

          These should provide some speedup.

          Alternatively try to create materialized view (I know that MySQL do not have them per se, but there are workarounds) or just switch to PostgreSQL.

          1. 2

            It seems that there is hell lot of missing indices that should greatly improve performance of that query.

            I don’t know about the internals of the database, but I’m guessing that the majority of comments are not deleted/moderated so the DB might still choose to do a full scan. is_following seems promising, but this comment mentions that the predicate isn’t getting pushed into the view so it may just be doing the joins for every user at once.

          2. 2

            Wowser. Looks like fun SQL.

            The normal answer would be views, but it appears from reading other comments that this isn’t an option, so we’re left with the classic cliche answers, any of which might work: RAM-up, brute-force, change engines, shard, and so forth.

            The trick probably is figuring out which of these is the easiest to try first. I’m not a Rails guy, so I don’t know the implications of switching engines to Postgres, but that intuitively feels like the right place to start playing around.

            ADD: Forgot temp/semi-permanent tables. Sometimes you can use cron and temp tables to work the same as views would.

            ADD2: Yeah, come to think of it, maybe some kind of temp shim is the way to go. You’re not going to solve the problem, but you can presort out the where clauses such that the indexes will allow the outer query to return faster. You’d need to work with it a bit to be sure, tho. A lot depends on memory size, how much data we’re talking about, and how often you need to hit it.

            1. 4

              I don’t think any of these are great solutions. The real answer is figuring out the query optimizer issue, and fixing it. Since the issue isn’t fixable within a single query (MySQL not pushing down a key predicate into the view), the next step is to perform multiple queries to work around the query optimizer. The predicate in question filters 300k rows down to ~4 (mentioned elsewhere in the thread), so the app should run that initial query, and then a second query using those results.

              For some reason people tend to avoid working around query optimizer issues with multiple queries. I can’t imagine why.

              switching engines to Postgres

              The Postgres optimizer has problems too. It can get confused about lateral joins—or queries that should be optimized to lateral joins—and end up doing the same thing as here. I’ve seen Postgres queries perform full table scans on huge tables to build up one side of a hash join, even though it estimates the other side of the join will filter to ~1 row.

            2. 2

              I would move the sub-queries in the current_vote_* columns to a left join.

              1. 2

                A reasonable compromise might be to time bound reply notifications. If the story is 1-3 months old, do you necessarily need a reply notification? How many people are replying to stories that are that old? (As a proportion of total comments.)

                With some covering indexes it might be a good enough strategy. Definitely better to have even 90% of notifications than 0%. (Unless your @cadey.)

                1. 3

                  I really enjoy receiving late replies to topics or commenting on old posts. Newer responds I catch more often just by rereading the comments.

                  1. 1

                    I do too, but if we can get most of the value with minimal changes then I think it’s a worthwhile compromise. Other options like migrating the database, setting up a VPS or changing lots of business logic are a bigger ask for the maintainers. Plus, an okay solution gives some breathing room for more permanent solution. (Or removes all impetus to make a permanent solution.)

                  2. 1

                    I find I would miss the replies most on the oldest topics

                  3. 1

                    Thank you for the links. I think I would probably try to make a view for user stories and comments (my stories, my comments - filter out negative scores, unfollowed), then try to look for children (replies), and filter/rank those. Possibly that would entail changing the relationship to easier be able to query children/replies - but I’m guessing the current “where parent_id…” should work fine.

                    It would probably mean four queries, but they might be easier to benchmark/optimize.

                    Normally I’d fight strongly for fewer/one query per controller method - but obviously in this case that’s been tried.

                    Personally would probably consider moving to pg - but doing that because of a single query/rails view is silly.

                  1. 4

                    There’s a ton of very popular alias-webkit-*. Is that some Blink internal leaking? Or is some popular site or embed overdoing prefixes?

                    I suspect counting page loads may be heavily skewed towards a few popular sites. webkit-line-clamp is on 1/3rd of page loads. It’s a property I’d expect used on top sites with user-generated content, but rather niche anywhere else.

                    1. 3

                      Given this 4 yo bug, I think it’s a internal Blink detail leaking.

                    1. 7

                      As long as neither the user, nor the site operators can opt out of this, it’s a little bit creepy.

                      1. 6

                        It’s Chrome sending telemetry back to Google from user’s behavior. From a user’s perspective, yeah, I want an opt-out.

                        From a site operator’s perspective, I don’t see the problem. Change this to Googlebot (the search index crawler) and it seems 100% benign and actually really useful. Then it’s just an IP downloading your site’s CSS.

                        1. 5

                          I wouldn’t expect any less from a browser designed to watch everything you do.

                          1. 5

                            I think there are two parts to this: public and non-public websites/apps. For the public part, I don’t see much harm. The HTML and CSS is already out there anyway, anyone can scan it.

                            The non-public part is a different story. Essentially they are leaking information from environments that are supposed to be private and protected. That is awful. So I can’t even trust the browser to keep things secret after I logged in somewhere with my password, or 2FA. They will probably defend this by saying that it is anonymous and aggregated, etc. But already the idea that that can take this information and do with it whatever want without even asking or notifying is incredibly arrogant and subverted.

                          1. 4

                            Hm, I’m not sure if this code and the linked IBM research line up with the estimate published in Discovery Magazine back in ’07. (Archive.org link)

                            Wonder which is more accurate. Fun thought experiment. 😀

                            1. 8

                              I don’t agree with the criticism of the stock photo. That’s reuse, under fair terms, of the image. Seems like the spirit of open source.

                              The complaint about the software is more appropriate. It’s not surprising to me, having worked at the Linux Foundation a few years ago; all the engineers used Linux but all of the business and marketing folks there used Windows and macOS. Still disappointing.

                              1. 6

                                Better than a stock photo is no photo. If you need to show smiling faces, show your team instead of random/anonymous persons. It is more authentic and credible.

                              1. 30

                                I support this idea, so that I may filter them.

                                I don’t dislike Nix, but I’m also not interested in it either. Presently, I simply ignore the posts, though having the option to filter them would be nice.

                                1. 2

                                  Yep, gimme more filtering powers!

                                1. 2

                                  This is an interesting approach, and for many use cases I think a very smart one. Personally, I would design the schema a little bit differently. I would merge a few of the tables together, reducing the need for JOINs and views.

                                  This technique is effectively the most common one in the static website world now. Although they don’t use relational databases, they rely on Git having the same behavior. For plain text with little structure, like the example blog here, I think it’s the better tool.

                                    1. 1

                                      Ah, good catch!

                                    1. 5

                                      Some of my coworkers and I play video games together. We used to do that regularly, on weeknights or weekends, but haven’t kept up with it in the new year. ($WORK went through a couple of restructures, and COVID happened.) But we’re all realizing how much we’ve missed doing it reguarly, and have started to carve out time in our schedules. It’s tough, we haven’t made time for it before, but even getting a little bit of time with them has been truly wonderful.

                                      1. 5

                                        It’s been 4 years already?

                                        1. 3

                                          In a month, it’ll be 5 years :)

                                        1. 16

                                          I’ve been using NewsBlur since 2013, and it works well enough that I’ve been willing to pay the $3/month for it. The main thing I enjoy is having a mobile app that is able to automatically download stories for reading on the subway, and sync its read/unread state with the browser version.

                                          1. 3

                                            2nd vote for NewsBlur. I’ve been using it since before google reader shut down (I kinda saw the writing on the wall on that one), and it’s been worth it ever since

                                            1. 1

                                              I’ll chime in for newsblur as well. Simple, gets out of my way, just plain works.

                                            2. 3

                                              Another vote for NewsBlur. I’ve been using it for a number of years as well (I think since 2013, too!) and absolutely love the “Intelligence Trainer” feature. I’m able to highlight keywords to emphasize or hide from the feeds. It’s like Lobste.rs’s tag filtering but for every feed out there.

                                              It even supports converting email newsletters into RSS feeds (which seems like the opposite approach everyone else takes, hah) which helps clean up my email inbox some.

                                              1. 3

                                                Also been using it since 2013. Being grandfathered into the $12 a year rate almost feels bad considering how much value I get from it.

                                                Especially since I consume Lobster.rs and HN by RSS. Kind of funny that I read this post through NewsBlur!

                                                1. 2

                                                  Yeah, I lied, I’m paying $12/year myself. It was a no-brainer at that price.

                                                2. 1

                                                  Possibly totally stupid question: is there a Linux client or 3rdparty app for NewsBlur?

                                                  1. 2

                                                    Maybe, but not that I’m aware of. I’m happy enough using the browser when I’m not on a phone, so I haven’t looked.

                                                    1. 1

                                                      It’s totally open source, you could run your own instance: https://github.com/samuelclay/NewsBlur

                                                    2. -1

                                                      +1

                                                    1. 6

                                                      I flagged as off-topic as this is a strictly B2B product that’s not even out yet. If this were a break down or review article talking about the possibilities I think it would be more appropriate. Just a spec sheet of a product no individual can get that’s not even out seems like an incorrect fit to Lobsters, IMO.

                                                      Super interesting though, I would love to see it repackaged for consumers. And a comparison against a RasPi 4.

                                                      1. 2

                                                        This is cool, but I don’t understand what exactly it’s trying to solve. As I understand, SHA3 is in a fairly good spot right now. I guess this is a radically different approach, which is nice? Perhaps there is an explanation for “why” in the README but I’m not sufficiently knowledgeable in the hashing world to understand the terminology and it’s implications.

                                                        1. 6

                                                          I have 2 set up so far, but I have 2 more I’m considering.

                                                          For the two I have, one is a Pi 4 4GB running Kodi (by way of LibreELEC), connected to my TV. The other one is a Pi 3 running Home Assistant. Both run cool/fine, and are single purpose for each.

                                                          I’m also considering running a music server (some combination of MPD and snapcast) for one of the remaining pi’s. I had purchased a 314GB HDD from WD when they sold them stupid cheap, and it’s been sitting idle since then. That’s plenty of storage for a music collection.

                                                          1. 2

                                                            I really like this. I have been thinking about replacing my Apple TV for some time. How do you control your Kodi install? Keyboard? Or does it work with some remote controller?

                                                            1. 3

                                                              You can use your TV remote. (If it’s connected with hdmi and supports CEC protocol)

                                                              1. 2

                                                                Oh I see, I have to check that out. Thanks!

                                                              2. 1

                                                                You can also use a mobile app (Kore) to control Kodi on your local network.

                                                            1. 4

                                                              This is such a well-reasoned and smart approach to time representation for computing, I’m sad that it wasn’t adopted more widely (namely, by UNIX systems of the time) but it’s also more complex than counting the number of seconds.

                                                              1. 0

                                                                The worse solution is clearly the better one.

                                                              1. 2

                                                                Clever approach. I wonder if this can be (or already has) spread to other OS’s.

                                                                1. 8

                                                                  I fell back to an old approach:

                                                                  Step 1. What are my friends, coworkers, or target audience using?

                                                                  Step 2. Install that.

                                                                  Step 3. Send them a message.

                                                                  Step 4: Depending on audience, try to interest them in private messaging.

                                                                  1. 3

                                                                    Same. Which means I basically have half a dozen messenger apps.

                                                                    1. 2

                                                                      I like your style. A lot of people say ‘oh my friends would never switch’. Often people never even bother asking. In my experience people are more receptive to new things than they are given credit for.

                                                                      1. 2

                                                                        And, private conversations are a good selling point. Most people want that, but don’t change the defaults. If you switch them to a private-by-default alternative, they sometimes become cheerleaders in their own circles.