| 00:09.01 | jfindlay | shipit |
| 00:23.54 | *** join/#utah emcnabb (~emcnabb@119.254.120.70) |
| 12:27.07 | *** join/#utah emcnabb (~emcnabb@106.2.240.26) |
| 13:20.40 | *** join/#utah josephscott (~josephsco@74.114.153.251) |
| 13:48.01 | *** join/#utah aeryndunham (~aeryndunh@96.71.45.252) |
| 15:03.52 | *** join/#utah braxtron (~braxtron@216.160.248.178) |
| 15:08.43 | *** join/#utah tiwula (~lane@70.89.246.241-Busname-UT.hfc.comcastbusiness.net) |
| 15:12.25 | spangborn | I need to figure out why my Linux server doesn't like to come up after a reboot if I have USB drives plugged in |
| 15:14.32 | keldwud | because it's trying to boot from the USB? |
| 15:14.49 | spangborn | I don't think so, I disabled all of that from the BIOS/UFI |
| 15:14.52 | spangborn | UEFI* |
| 15:15.04 | spangborn | It gets past the POST, but doesn't ever make it into the OS |
| 15:16.04 | keldwud | that's interesting |
| 15:16.16 | keldwud | what do dmesg and dmidecode have to say about it? |
| 15:19.33 | spangborn | idk, I haven't dug into it yet |
| 15:22.13 | spangborn | It just keeps nagging me that it needs to reboot and I've been putting it off because I have to go unplug the drives, let it boot, then plug them back in |
| 15:22.37 | spangborn | screw it, I'll gamble and see if I can get it to go remotely |
| 15:22.43 | spangborn | Just a home server anyway |
| 15:25.11 | spangborn | The-Eclipse:~ sawyer$ ssh sawyer@ubuntu-server |
| 15:25.11 | spangborn | ssh: Could not resolve hostname ubuntu-server: nodename nor servname provided, or not known |
| 15:25.12 | spangborn | WELP |
| 15:28.19 | aeryndunham | I had a LInux install for a while that wouldn't boot if my USB headset was plugged in |
| 15:28.26 | aeryndunham | That was a weird one |
| 15:32.37 | spangborn | I'll have to look at logs |
| 15:32.45 | spangborn | Can't get to the box right now anyway (at Beans and Brews) |
| 15:44.08 | *** join/#utah aarobc (uid151482@gateway/web/irccloud.com/x-ytouggyjjxczkjyl) |
| 15:57.36 | klys | ~slashdot |
| 15:58.50 | klys | good morning. |
| 16:39.37 | *** join/#utah josephscott (~josephsco@74.114.153.251) |
| 17:28.51 | *** join/#utah keldwud (~keldwud@199.192.164.75) |
| 17:28.51 | *** join/#utah keldwud (~keldwud@unaffiliated/keldwud) |
| 17:54.56 | *** join/#utah aeryndunham (~aeryndunh@96.71.45.252) |
| 18:12.25 | *** join/#utah legreathambino (~braxtron@216.160.248.178) |
| 18:13.35 | jfindlay | is there a daemon or common cron job that resets file capabilities? |
| 18:14.50 | jfindlay | hm, or the package was upgraded |
| 19:02.24 | *** join/#utah onesluttychicken (~braxtron@216.160.248.178) |
| 19:47.53 | jfindlay | I have a postgres database with a table containing 300k+ rows and the query `select from 300k_table;` can take up to 20 s. Am I doing something obviously wrong or is that about expected? |
| 19:48.09 | jfindlay | the system has 1 core and 1 GiB memory |
| 19:51.32 | jlp_znc | when was the last time you vacuumed? |
| 19:51.52 | jlp_znc | is it a volatile table (lots of updates/deletes)? |
| 19:52.26 | jlp_znc | and I hope you mean select * from 300k_table |
| 19:52.48 | jlp_znc | if it's taking 20 seconds to give you a syntax error, then no, that's not normal :D |
| 19:53.15 | jfindlay | select without the * just tells me how many rows |
| 19:53.29 | jlp_znc | oh, that's very very expensive on postgres. |
| 19:53.38 | jlp_znc | it has to count them. |
| 19:53.47 | jfindlay | well, I discovered it by accident |
| 19:54.01 | jfindlay | also yes, the table gets updated about once per minute |
| 19:54.10 | jlp_znc | so, have you vacuumed lately? |
| 19:54.22 | jfindlay | what is a vacuum? |
| 19:54.27 | jlp_znc | gasp |
| 19:54.30 | jfindlay | lol |
| 19:54.44 | jlp_znc | so, the way postgres does it's thing is it has a transaction ID associated with every row. |
| 19:55.11 | jlp_znc | when you do an update, transactions that started before you did the update shouldn't see the new value, so both versions of the row are kept in the db. |
| 19:55.47 | jlp_znc | a periodic vacuum (you probably have autovacuum turned on) will clean up any rows that are still in the table that can't be seen by any currently active (or future) transactions |
| 19:56.13 | jlp_znc | this is why count(*) is so slow in postgres. |
| 19:57.43 | jlp_znc | I have this function: https://pastebin.com/YNYCU30c that will give you an estimate on a large table |
| 19:58.01 | jfindlay | does autovacuum need to be tuned proportional to update frequency? |
| 19:58.19 | jlp_znc | it should do it automatically based on how busy/dirty the table is. |
| 19:58.33 | jlp_znc | but counts on big tables are slow in postgres by definition. |
| 19:58.55 | jlp_znc | there are sneaky ways around it using indexes or that function ^^ that uses explain to get an estimate |
| 19:59.24 | jfindlay | ok, well I was proposing the count as a proxy for queries on the table being "slow" relative to how quick they were when the table only had a few k rows |
| 19:59.42 | jfindlay | I actually don't care about the count really |
| 20:00.08 | jfindlay | it could be slow because the table has a jsonb column |
| 20:01.35 | jlp_znc | that shouldn't affect it, I don't think. |
| 20:02.47 | jlp_znc | you can try running a vacuum analyze 300k_table |
| 20:04.10 | jlp_znc | slow queries usually means you need an index or three. |
| 20:04.22 | jlp_znc | try explain your query here |
| 20:04.56 | jlp_znc | http://wiki.postgresql.org/wiki/Show_database_bloat will examine your db and tell you what tables/indexes are in trouble. |
| 20:05.22 | jlp_znc | as I recall (it's been a while) the best way to unbloat an index is to drop and recreate it. |
| 20:07.10 | jfindlay | here is the table: https://ae7.st/pb/?e66b9130e48d3cc9#Qngwr1GLA5zmWlHYdopHzwx8y58tkAO0qUfeH9dpcVc= |
| 20:07.13 | jfindlay | many indices |
| 20:08.26 | jfindlay | argh, also our VM storage is super slow |
| 20:08.34 | jfindlay | could be the real bottleneck here |
| 20:10.03 | jlp_znc | I'd get the explain plan on one of your "slow" queries and see what it's doing |
| 20:11.13 | jlp_znc | paste your explain analyze output here and it will decode it for you: https://explain.depesz.com/ |
| 20:20.27 | jfindlay | I understand that you want to consolidate queries as much as possible into a single statement to delegate the data manipulation to the database itself because requesting data from the DB is typically more expensive than the actual query itself. Does this reasoning apply even if the queries are completely unrelated (assuming you can submit multiple query statements in the same batch)? |
| 20:43.50 | jlp_znc | if they are unrelated, then I woudln't think it matters. |
| 20:44.07 | jlp_znc | but how often are they unrelated? :D |
| 20:44.16 | jlp_znc | it is a "relational" database, after all. |
| 20:45.31 | jfindlay | very similar queries against the same table |
| 20:45.44 | jfindlay | but it looks like grafana sends them separately |
| 20:58.36 | jlp_znc | if they are requesting different data then I'd just let them do their thing |
| 20:59.09 | jlp_znc | quote often, though, those systems use some kind of data abstraction layer that does not make the most efficient use of the db. |
| 20:59.17 | jlp_znc | but that's just the way they work. |
| 21:01.18 | jfindlay | the window washers came by today to wash the inside of the windows and so they pulled up the blinds all the way across the office, so now I have an awesome view of the cascades and Mt Rainier |
| 22:06.15 | BigBen212 | jfindlay: rub it in |
| 22:06.53 | BigBen212 | if I turn around, I have a nice view of 4570 South |
| 22:07.18 | BigBen212 | but at least I get to open and close my own blinds :) |
| 22:19.55 | *** join/#utah youtah (~youtah@67.139.187.131) |
| 22:19.55 | *** mode/#utah [+v youtah] by ChanServ |
| 22:28.22 | jlp_znc | hmm, if I turn around I have a nice view of the back half of the dude who sits behind me. |
| 22:28.49 | jlp_znc | it's not much to look at |
| 22:38.09 | *** join/#utah RyanE (~ryan@154.3.45.82) |
| 23:00.27 | romanovic | can anyone tell me what sort of hell on earth I should expect if I need to drive to several locations in SLC tomorrow |
| 23:00.57 | romanovic | with the SLC marathon going on |
| 23:01.50 | jlp_znc | https://saltlakecitymarathon.com/race-info-maps/marathon/course/ |
| 23:01.50 | youtah | thekev I talked to k7pb... I told him you can hit his house/tower. He is looking at pointing something your way. He was SUPER excited |
| 23:02.23 | jlp_znc | https://saltlakecitymarathon.com/resources/street-closures/ |