| Time |
Nick |
Message |
| 07:27 |
|
kworstell-isl joined #evergreen |
| 08:06 |
|
JBoyer joined #evergreen |
| 08:40 |
|
mmorgan joined #evergreen |
| 08:45 |
|
Dyrcona joined #evergreen |
| 09:05 |
|
dguarrac joined #evergreen |
| 10:14 |
Bmagic |
I'm having trouble connecting to git://git.esilibrary.com/migration-tools.git rhamby JBoyer gmcharlt. FYI. The gittolite interface is working, but git fetch doesn't. Maybe I missed an announcement about this repo? 2 years old seems wrong |
| 10:16 |
Dyrcona |
Bmagic: migration-tools moved to github. |
| 10:17 |
Bmagic |
ah! I thought I remembered something like that somewhere |
| 10:17 |
Bmagic |
I shoulda check historic emails |
| 10:17 |
Dyrcona |
https://github.com/EquinoxOpenLibraryInitiative/migration-tools |
| 10:17 |
Bmagic |
ty |
| 10:18 |
Dyrcona |
It turns out that I haven't cloned it on this laptop, yet. |
| 10:22 |
csharp_ |
oof - opac queries on EG 3.10/PG 14 are taking 7 minutes |
| 10:22 |
csharp_ |
done all the vacuuming, etc. - doing an explain analyze now |
| 10:22 |
csharp_ |
(on a test server, not PINES production) |
| 10:23 |
Bmagic |
hmmm, that's no good |
| 10:23 |
Dyrcona |
csharp_: Not good, but not bad that you've caught it before going to production. |
| 10:23 |
rhamby |
Dyrcona got there before I did :) |
| 10:23 |
csharp_ |
working theory is that a setting in PG config is missing/off |
| 10:36 |
Dyrcona |
csharp_: You've optimized the settings for the hardware? |
| 10:36 |
csharp_ |
Dyrcona: I carried over the optimized settings for this box for PG 10 |
| 10:37 |
csharp_ |
I haven't done a lot of reading docs/release notes yet |
| 10:38 |
Dyrcona |
OK. I've done the same before. It seems to work, but maybe something new should be tweaked. |
| 10:48 |
Dyrcona |
I'm going to switch my main development database over to be "optimized" for Pg 14, but I'm going to first upgrade a copy of production data to latest master to get the necessary upgrades. |
| 10:48 |
Dyrcona |
This is the server with the bad drive, however. |
| 10:49 |
* Dyrcona |
wants to replace it soon, but the replacement won't be able to run multiple copies of production data. |
| 10:58 |
Dyrcona |
Maybe I'll go to rel_3_10 instead. |
| 11:06 |
|
JBoyer joined #evergreen |
| 11:29 |
|
berick joined #evergreen |
| 12:06 |
|
Christineb joined #evergreen |
| 12:11 |
|
jihpringle joined #evergreen |
| 13:20 |
Dyrcona |
Well..... I just found out that Pg 10 through 13 all have my "pg_opt.conf" in the conf.d/. Only 1 is supposed to have it at a time. That could explain some of the issues with performance that I've been experiencing. |
| 13:22 |
|
jvwoolf joined #evergreen |
| 14:43 |
csharp_ |
ok, whatever this is I'm seeing appears to be a PG 14 problem - took the same data from a working-fine PG 10 DB, imported it into a PG 14 DB and the super-long query time is happening now |
| 14:44 |
* csharp_ |
changes plans to upgrade to PG 14 |
| 15:09 |
Bmagic |
:) |
| 15:43 |
Dyrcona |
csharp_: Did you get the EXPLAIN output? |
| 15:56 |
csharp_ |
Dyrcona: https://explain.depesz.com/s/n6dZ |
| 15:57 |
csharp_ |
Seq Scan on copy_vis_attr_cache (cost=0.00..321,880.78 rows=6,307,318 width=8) (actual time=25.560..442,323.755 rows=9,951,136 loops=1) appears to be the bottleneck |
| 16:02 |
|
jvwoolf left #evergreen |
| 16:03 |
Dyrcona |
Hmm. |
| 16:07 |
Dyrcona |
If it is what I suspect, I think some container queries might also be slower on Pg 14. |
| 16:10 |
* Dyrcona |
makes a note to look into this more. |
| 16:10 |
csharp_ |
Dyrcona++ |
| 16:12 |
csharp_ |
same query on PG10: https://explain.depesz.com/s/H588 |
| 16:13 |
csharp_ |
simlar datasets |
| 16:13 |
Dyrcona |
csharp_++ # I was just about to say that I'll try in on Pg10, too. |
| 16:13 |
csharp_ |
similar, even |
| 16:14 |
csharp_ |
the query in question: https://pastebin.com/WKqW5tgQ |
| 16:15 |
Dyrcona |
I think the problem is using @@ on an array of INT: vis_attr_vector @@ c_attr.vis_test. |
| 16:15 |
Dyrcona |
vis_attr_vector is INT[] not tsvector. |
| 16:16 |
Dyrcona |
Line 91 in the paste. |
| 16:18 |
csharp_ |
that's all flying right over my head :-) |
| 16:18 |
csharp_ |
reading https://www.postgresql.org/docs/14/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH though |
| 16:21 |
Dyrcona |
@@ is meant for tsvector/tsquery search. A field with datatype INT[] isn't a tsvector. Somewhere along the line Pg may have stopped coercing array types. |
| 16:21 |
pinesol |
Dyrcona: Error: "" is not a valid command. |
| 16:21 |
Dyrcona |
:) |
| 16:21 |
Dyrcona |
@@@ |
| 16:21 |
pinesol |
Dyrcona: Beyond here be dragons. |
| 16:21 |
Dyrcona |
pinesol: You betcha! |
| 16:21 |
pinesol |
Dyrcona: Have you tried throwing it across the room? |
| 16:21 |
Dyrcona |
csharp_ has already found the dragons. |
| 16:29 |
|
JBoyer_but_on_We joined #evergreen |
| 16:30 |
Dyrcona |
It might be as simple as adding to_tsvector() in appropriate places. |
| 16:31 |
Dyrcona |
If you want an idea of the potential scope for changes: grep -r vis_attr_vector Open-ILS/ |
| 16:35 |
* Dyrcona |
calls it a day. |
| 17:06 |
|
mmorgan left #evergreen |
| 17:24 |
|
jihpringle joined #evergreen |
| 17:44 |
|
JBoyer_ joined #evergreen |
| 17:52 |
miker |
csharp_ / Dyrcona (later): @@ is not just for tsvectors. see: https://www.postgresql.org/docs/14/intarray.html |
| 17:53 |
miker |
IOW, it's not doing tsearch stuff there, it's doing query_int stuff |
