pushcx

MariaDB [lobsters]> select domain, count(*) as submitted, count(distinct stories.user_id) as submitters, (select count(*) from stories s where s.domain_id = domains.id group by s.user_id order by 1 desc limit 1) as from_one_submitter, (select count(*) from stories s where s.domain_id = domains.id group by s.user_id order by 1 desc limit 1)/count(*)*100 as submited_by_one_percentage from domains join stories on domains.id = stories.domain_id group by domain having count(*) > 5 and (from_one_submitter + 1) * 2 > count(*) order by 5 desc;
+-------------------------------------+-----------+------------+--------------------+----------------------------+
| domain                              | submitted | submitters | from_one_submitter | submited_by_one_percentage |
+-------------------------------------+-----------+------------+--------------------+----------------------------+
| 0xcc.re                             |         6 |          1 |                  6 |                   100.0000 |
| acha.ninja          

pushcx

select domain, count(*) as submitted, count(distinct stories.user_id) as submitters from domains join stories on domains.id = stories.domain_id group by domain having count(*) > 5 and count(distinct stories.user_id) =1 order by 2 desc limit 30;
+----------------------------+-----------+------------+
| domain                     | submitted | submitters |
+----------------------------+-----------+------------+
| vuejsdevelopers.com        |        52 |          1 |
| driftingruby.com           |        48 |          1 |
| pythonspeed.com            |        40 |          1 |
| tboox.org                  |        38 |          1 |
| newrustacean.com           |        34 |          1 |
| joezimjs.com               |        28 |          1 |

pushcx

keyboard = (
  { key = "meta-p"; id = "change_window"; data = "20"; },
  { key = "meta-a"; id = "change_window"; data = "21"; },
  { key = "meta-s"; id = "change_window"; data = "22"; },
  { key = "meta-d"; id = "change_window"; data = "23"; },
  { key = "meta-f"; id = "change_window"; data = "24"; },
  { key = "meta-g"; id = "change_window"; data = "25"; },
  { key = "meta-h"; id = "change_window"; data = "26"; },
  { key = "meta-j"; id = "change_window"; data = "27"; },
  { key = "meta-k"; id = "change_window"; data = "28"; },

pushcx

.outer {
  display: grid;
  gap: 1em;
}

/* may be a bug with width=600px windows here, I'm bad at responsive */
@media (min-width: 600px) {
  .outer {
    grid-template-cols: 1fr 1fr;
    background-color: #eee;

pushcx

context: https://lobste.rs/s/5hshvd/proposal_lobste_rs_performance_analysis#c_bp5fdv

# rails console to collect queries:
irb(main):001:0> u = User.find_by(username: 'pushcx');nil
D, [2019-10-02T14:04:06.531843 #12101] DEBUG -- :    (0.6ms)  SET NAMES utf8mb4,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
D, [2019-10-02T14:04:06.546456 #12101] DEBUG -- :   User Load (1.0ms)  SELECT  `users`.* FROM `users` WHERE `users`.`username` = 'pushcx' LIMIT 1
=> nil
irb(main):002:0> Comment.for_user(u).order("id DESC").includes(:user, :hat, :story => :user).joins(:story).where.not(stories: { is_expired: true }).limit(20) .offset((1 - 1) * 20);nil
D, [2019-10-02T14:05:35.270496 #12101] DEBUG -- :   Comment Load (4609.9ms)  SELECT  `comments`.* FROM `comments` INNER JOIN `stories` ON `stories`.`id` = `comments`.`story_id` WHERE `stories`.`is_expired` != TRUE ORDER BY id DESC LIMIT 11 OFFSET 0
D, [2019-10-0

pushcx

pushcx

pushcx

I was asked a couple Lobsters stats questions and wanted to paste the answers both because there was some detail and because I might as well share the numbers with more than just the submitter. The policy is that I'm happy to answer questions or run queries as long as they don't create a lot of work for me or a worst-of list.

schema if you want to write queries: https://github.com/lobsters/lobsters/blob/master/db/schema.rb

https://lobste.rs/s/cqnzl5/lobste_rs_access_pattern_statistics_for might be interesting for some recent stats

1. roughly how many people visit each day/month

Weekdays I see typically 12-15k unique IPs, so probably a a few more visitors than that to account for shared wifi like coffeshops and coworkers. I think weekends are around half that. I haven't kept a close eye on this because it wouldn't have changed any decision I've made (https://push.cx/2015/will-knowledge-change-a-decision-you-make).

pushcx

+-----------+-----------+
| n_hidings | n_stories |
+-----------+-----------+
|         0 |     19866 |
|         1 |     13070 |
|         2 |      5728 |
|         3 |      1992 |
|         4 |       797 |
|         5 |       384 |
|         6 |       205 |

pushcx

2017-10-25: this few open items of this checklist has been migrated to issues on the ansible repo for better visibility and tracking. This gist is left up in the hopes it's useful for anyone setting up their own site using the lobsters codebase.

Prep

• announce migration + privacy on twitter
• Lobsters post: this checklist, privacy deadline, migration date
• post this plan to lobsters
• transfer lobste.rs registration to pushcx
• transfer @lobsters twitter account to pushcx