I don't know if I've ever seen a way to query history annotations in the UI by text content? Does the history search do that currently?
ok, no, can confirm that history search does search the annotations.
should probably be a GIN index
not a btree
all the PG text search code I'm looking at uses GINs
but it's probably over to the sqlalchemy folks to figure out how to do a different index type in postgres only. and how not to make that a migration since locking the history table will kill big servers.
yeah something like: drop index, (server gets slow for annotation searchers), export, reimport (new machine), re-create the index with type=gin now
I assume others will see similar problems when they update to >PG12 and have big instances so, we need to discuss this also on the code level.
yeah definitely. I'm thinkin btree is the wrong index type here (and for any other columns with type=text) so, definitely going to be something we need to think about how to change
CREATE INDEX and REINDEX both support the keyword CONCURRENTLY, which allows for (re-)creating an index w/o locking the pertinent table. This operation will of course run even longer than a idxbuild with a writelock and definitely have an impact on server performance but blocking writes for hours can be avoided this way. (Disclaimer: this is according to the manual, I never tried it myself)
Just for fixing ix_history_annotation_association_annotation in our new server instance concurrent idxbuild may not be needed at all as we could do it before putting the box into production. OTOH, I expect the whole process of dump & reload to take around 9h already w/o manual fiddling with any indexes, so depending on how the operation goes schedule-wise I'll still consider it an option :)
What's more problematic is that I apparently can't just substitute GIN for BTREE in CREATE INDEX
When I try "CREATE INDEX ix_history_annotation_association_annotation ON history_annotation_assiciation USING GIN (annotation);" what it get is 'ERROR: data type text has no default operator class for access method "gin"'
Followed by "HINT: You must specify an operator class for the index or define a default operator class for the data type"
The GIN-idx examples in the manual all use functions like to_tsquery() or to_tsvector(). This is frankly all greek to me XD All the text matching I ever did so far in SQL was using LIKE...
https://www.postgresql.org/docs/current/indexes-types.html nothing else there sticks out to me as a better option for text, but, ok, maybe there's some bigger changes involved in switching to that.
but I guess the short term solution is drop the index and hope no one is searching by history
BRINs aren't right, SP-GiST are spatial, GiST lack equality, Hash only have equals.
The index hasn't been created in the first place, so no need to drop it in the new instance. But yes, if we don't find a solution certain querys will probably be slooow... :\
Or else, unless anything in the current version of Galaxy requires v12-features, we could maybe just try our luck with PG v11 and stick with that until a general solution has emerged
The new server instance isn't in production yet, so all options are on the table
can we get this number
For "SELECT idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname='ix_history_annotation_association_annotation';" I get 0,0,0 :)
for 'ix_history_annotation_association_user_id' the numbers are 8,8,8
if its 0, we should just drop it ;)
also upstream
So the zeroes are not just zero because no data was collected it would seem
galaxy_main=> SELECT idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname='ix_history_annotation_association_annotation';
idx_scan | idx_tup_read | idx_tup_fetch
----------+--------------+---------------
0 | 0 | 0
(1 row)
is there a way to do this for all indices in a loop @sj213:matrix.org ?
1 reply
maybe we can remove more?
Alright...I can't find where environment variables are declared for Galaxy.
We're trying to migrate our Apollo services and I cannot make the environment variable change for the life of me. I thought it was in /etc/supervisor/conf.d/<files> but...evidently it isn't picking up. Even after restart.
Is there another place that environment variables can get declared? Something perhaps that I am missing? At least obvious ones.
<we don't use ansible atm, we are currently old school/bare metal>
I should clarify, I didn't answer my question, but, it would appear that the env vars from that file are making their way into galaxy but they're not making it in when modified after restart for some reason. But, I think what I'm trying to figure out now is more of why they don't get picked up on restart compared to is there another place for env vars if that makes sense. Thanks @innovate-invent
9 replies
how do you mean