Hi Tim Your patch works. At least the scripts ends in 56 minutes instead of not ending properly after 17 hours.
Thanks a lot This is the dialog # *time ./bin/dspace update-handle-prefix 10230 123456789* Getting information about handles from database... In your repository will be updated 47316 handles to new prefix 123456789 from original 10230! Servlet container (e.g. Apache Tomcat, Jetty, Caucho Resin) must be running. If it is necessary, please make a backup of the database. Are you ready to continue? [y/n]: y Updating handle table... 47316 items updated Updating metadatavalues table... 831 metadata values updated Handles successfully updated in database. Re-creating browse and search indexes... The script has started (Re)building index from scratch. Done with indexing The script has completed real 56m18,467s user 16m35,114s sys 5m25,811s El miércoles, 13 de septiembre de 2023 a las 17:27:08 UTC+2, DSpace Technical Support escribió: > Hi Evelio, > > I responded to your message on Slack as well. But, I've verified that the > query itself is definitely inefficient and created a bug ticket for this at > https://github.com/DSpace/DSpace/issues/9066 > > While I was investigating this, I also realized there may be a "quick fix" > of removing the "cross join" portion of that query. So, I created a small > PR for that: https://github.com/DSpace/DSpace/pull/9067 > > This small PR improves performance slightly on a small database (I only > have 1,000 handles to test with, not 47,000 like you). If you (or someone > else) has time to test this small fix on a larger database, I'd be curious > to find out if it improves the performance there as well. If it does, we > can get this fix merged immediately (and included in 7.6.1). But, if it's > not a complete fix, then I might need to find others to help me investigate > this ticket further to determine what else might be going on. > > Thanks, > > Tim > > On Tuesday, September 12, 2023 at 7:22:26 AM UTC-5 evelio...@gmail.com > wrote: > >> >> Hello >> >> We are migrating from DSpace 5 to 7.6. >> Everything goes smooth but we have copied production database to test >> environment and when we change handle prefix it takes toooo loooong and >> finally does not update. >> After 17 hours we see the below dialog... >> >> # time ./dspace update-handle-prefix 10230 123456789 >> >> Getting information about handles from database... >> >> In your repository will be updated 47316 handles to new prefix 123456789 >> from original 10230! >> >> Servlet container (e.g. Apache Tomcat, Jetty, Caucho Resin) must be >> running. >> >> If it is necessary, please make a backup of the database. >> >> Are you ready to continue? [y/n]: y >> >> No changes have been made to your data. >> >> >> real 1032m21,431s >> >> >> The offending query is >> >> >> select count(distinct handle0_.handle_id) as col_0_0_ from public.handle >> handle0_ cross join public.handle handle1_ where handle0_.handle like $1 >> >> DETALLE: parámetros: $1 = '10230%' >> >> >> Any ideas or workaround? >> >> >> Thanks in advance >> >> Evelio >> >> -- All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx --- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/46bd5f36-4e28-4ecc-a638-288a893e67acn%40googlegroups.com.
