Hello, I just noticed that the auditor table trigger is disabled during the visibility update, so that answers that question, it is something that is done. But it seem like it has a very small performance impact. On my test system reingest of 150 record batches takes 2.5% longer when the auditor table trigger is enabled. So my primary reason for doing it would just be to avoid audit table bloat.
I’ve been trying out GNU parallel to run a bunch of ingest updates in parallel. I am seeing collisions on inserting into metabib.browse_entry, when two different queries each contain the same entry and are both trying to insert a new row. And I saw one deadlock detected also. But the joblog feature seems like it will take care of that, once the bulk of the queries are done, it can just resume running all the failed queries and just run them serially to finish up. Josh Stompro - LARL IT Director From: Open-ils-general [mailto:open-ils-general-boun...@list.georgialibraries.org] On Behalf Of Josh Stompro Sent: Wednesday, April 25, 2018 8:35 AM To: Evergreen Discussion Group <open-ils-general@list.georgialibraries.org> Subject: Re: [OPEN-ILS-GENERAL] 3.0.2-3.0.3 Upgrade DB script takes over a week to finish This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing> Feedback<http://aka.ms/SafetyTipsFeedback> Hello again, does anyone disable the auditor table trigger for biblio.record_entry for updates like the 901 or a full reingest using the 1=1 method? I wonder if that would speed things up at all, and avoid some auditor table bloat. I also noticed that the auditor table for auditor.biblio_record_entry_history wasn’t updated with the new biblio.record_entry columns for vis_attr_vector, merge_date and merged_to. Is that something that I’m supposed to do manually whenever an audited table gets altered? Josh Stompro - LARL IT Director From: Open-ils-general [mailto:open-ils-general-boun...@list.georgialibraries.org] On Behalf Of Josh Stompro Sent: Tuesday, April 24, 2018 9:03 AM To: Evergreen Discussion Group <open-ils-general@list.georgialibraries.org<mailto:open-ils-general@list.georgialibraries.org>> Subject: Re: [OPEN-ILS-GENERAL] 3.0.2-3.0.3 Upgrade DB script takes over a week to finish This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing<http://aka.ms/LearnAboutSpoofing> Feedback<http://aka.ms/SafetyTipsFeedback> Hello, Thank you all for the great info in this thread. I've been trying out some test upgrades from 2.10 -> 3.1 and have some questions that I thought I would just add here. I just want to make sure I understand which of the longer running updates do the same things as other updates for efficiency. My notes on the upgrade scripts are at https://docs.google.com/document/d/1Rh-ei7ffm3BVeMaJSDFte6LGgP4xt5VU16YZSOJ4uzg/edit?usp=sharing List of longer running updates 1. 2.10.7-2.11.0 – 901 field update via update set id=id method 2. 2.11.3-2.12.0 – Facet and Browse reingest via metabib.reingest_metabib_field_entries 3. 2.12.0-2.12.1 – Browse reingest via metabib.reingest_metabib_field_entries 4. 2.12.6-3.0.0 a. Display field reingest via metabib.reingest_metabib_field_entries b. Authority reingest via update set id=id method in a transaction 5. 3.0.6-3.1.0 – full reingest via update set id=id method in a function So from what I think I understand, these updates can all be completed by performing the 1. Authority reingest from 2.12.6-3.0.0. 2. Full reingest from 3.0.6-3.1.0. (should deal with all the indexes along with bre trigger updates for the 901 update). I’m looking into breaking these updates into smaller chunks and running them in parallel to speed them up and allow the system to function somewhat normally. I usually use the pingest.pl script, but I don’t think that script would take care of bre trigger based updates, since I think it calls metabib.reingest_metabib_field_entries to perform updates just on the indices. But I think I may run into trouble running multiple instances of each of these updates because of the transactions. I’m thinking that as is, the updating of the config.internal_flag and “alter table” will block other transactions. So maybe I’m better off using pingest.pl for the search re-indexing, which can be run in parallel, and then a simpler query for the “update set id=id on bre” bre trigger updates, which should skip the indexing updates. The authority update I should maybe just run serially, cut up into smaller chunks of records. Is there a pingest.pl script for authority reindexing? Also, could someone tell me the difference between using a “DO $FUNC$” for the 3.0.6-3.1.0 reingest verses how the 2.12.6-3.0.0 authority reingest uses commands in a transaction block? Is one method superior to another? Is the DO $FUNC$ method implicitly in a transaction? Thanks Josh Stompro - LARL IT Director -----Original Message----- From: Open-ils-general [mailto:open-ils-general-boun...@list.georgialibraries.org] On Behalf Of Jesse McCarty Sent: Wednesday, April 04, 2018 2:10 PM To: 'Evergreen Discussion Group' <open-ils-general@list.georgialibraries.org<mailto:open-ils-general@list.georgialibraries.org>> Subject: Re: [OPEN-ILS-GENERAL] 3.0.2-3.0.3 Upgrade DB script takes over a week to finish Jason, If the authority reingest is the one that runs in the 2.12.0-2.12.1 script, it looks like between an hour/hour and a half on our test system this morning. I don't know how many bib records we have, but our Database sits about 24GB if that helps for comparison. Jesse McCarty City of Burlington Information Systems Technician -----Original Message----- From: Open-ils-general [mailto:open-ils-general-boun...@list.georgialibraries.org] On Behalf Of Jason Stephenson Sent: Wednesday, April 04, 2018 11:58 AM To: open-ils-general@list.georgialibraries.org<mailto:open-ils-general@list.georgialibraries.org> Subject: Re: [OPEN-ILS-GENERAL] 3.0.2-3.0.3 Upgrade DB script takes over a week to finish On 04/04/2018 02:46 PM, Jesse McCarty wrote: > Thank you Kathy, > > Those changes seemed to work great. What seemed to never end completed > in about a minute, and I was able to start up Evergreen and > search/renew and otherwise interact with the test system as expected > (in my limited IT testing role not being a library staff member that > fully utilizes the system). Is this to be expected to complete that > fast or should I be wondering if something didn't go as needed? Jesse, I think you're OK. That update went from never finishing on my test system to running in just 12 minutes. (We have several million bib records.) By never finishing, I mean I let it run for a few days before stopping it. If there were any problems, you should see error messages in the console where the script was running. I like to pipe the output to "tee" so it also goes to a file: psql -f upgrade_script.sql |& tee ~/upgrade_script.log The "|&" is a shortcut to send both standard output and errors to the file. Do you know how long the authority ingest part of the updrade scripts runs for you? That runs for over 30 hours on my test system, and it is already about as optimized as it can be. Cheers, Jason
