Re: [Koha] [EXTERNAL] Re: Reports timing out, prevents access to web interfaces
Just out of curiosity, how large is your database, Tasha? We've managed to run into issues w/ about 90K records (standard MARC bibs for books, etc.). This isn't all that large in the grand scheme of things, so when we run into issues, I know either our SQL has some optimization issues, or the report may be a bit too ambitious (lots of UNION statements, etc.) & we should consider only running when the library's closed. We have a support vendor, so this is typically when we ask for help from people smarter than us :-) Wonder if you'd have better luck finding guidance on sites more focused on database administrators? (StackExchange, https://forums.mysql.com/, or even a local DBA/ MySQL user group) Cheers, Cab On Mon, Oct 11, 2021 at 9:52 PM Bales (US), Tasha R wrote: > > Hi Cab, that is in fact helpful feedback. I've taken queries that we've used > for years with Millennium and Oracle, and am rewriting them for Koha and > MySQL. So far, MySQL does seem pickier. I have tried using the "EXPLAIN" > command to help identify inefficient queries, but it didn't really help. > > One tactic we've tried in the past is to create views containing subsets of > data that we need to frequently iterate over, to help decrease processing > time. Maybe that is an approach we could use here, but I am wary of trying > to modify our database in any way. Maybe a last resort. Thanks again. > > > Tasha Bales > Enterprise Services > http://isesi.web.boeing.com/ > > -Original Message- > From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Cab Vinton > Sent: Thursday, October 7, 2021 03:49 > To: koha > Subject: [EXTERNAL] Re: [Koha] Reports timing out, prevents access to web > interfaces > > EXT email: be mindful of links/attachments. > > > > I don't have any particularly helpful advice to offer, unfortunately, but I > can confirm from personal experience that certain queries can indeed bring > your system to a crawl. I don't think there's a solution to this unless > there's a way to firewall report queries from the rest of Koha, or to > automatically throttle misbehaving processes. > > In my case, I'd copied & pasted a report from the wiki, but a change in > Koha's database structure since the report's creation led to, well, very bad > results. > > I think your best bet is to take a really close look at the report to see if > there are ways to achieve the same results more efficiently. In our case, > this means seeking help from folks who are far more experienced with SQL than > we are. > > Best of luck! > > Cab Vinton, Director > Plaistow Public Library > Plaistow, NH > ___ > > Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] [EXTERNAL] Re: Reports timing out, prevents access to web interfaces
Hi Cab, that is in fact helpful feedback. I've taken queries that we've used for years with Millennium and Oracle, and am rewriting them for Koha and MySQL. So far, MySQL does seem pickier. I have tried using the "EXPLAIN" command to help identify inefficient queries, but it didn't really help. One tactic we've tried in the past is to create views containing subsets of data that we need to frequently iterate over, to help decrease processing time. Maybe that is an approach we could use here, but I am wary of trying to modify our database in any way. Maybe a last resort. Thanks again. Tasha Bales Enterprise Services http://isesi.web.boeing.com/ -Original Message- From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Cab Vinton Sent: Thursday, October 7, 2021 03:49 To: koha Subject: [EXTERNAL] Re: [Koha] Reports timing out, prevents access to web interfaces EXT email: be mindful of links/attachments. I don't have any particularly helpful advice to offer, unfortunately, but I can confirm from personal experience that certain queries can indeed bring your system to a crawl. I don't think there's a solution to this unless there's a way to firewall report queries from the rest of Koha, or to automatically throttle misbehaving processes. In my case, I'd copied & pasted a report from the wiki, but a change in Koha's database structure since the report's creation led to, well, very bad results. I think your best bet is to take a really close look at the report to see if there are ways to achieve the same results more efficiently. In our case, this means seeking help from folks who are far more experienced with SQL than we are. Best of luck! Cab Vinton, Director Plaistow Public Library Plaistow, NH ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] [EXTERNAL] Re: Reports timing out, prevents access to web interfaces
Thanks for your reply, Chris. One of our team members posited that there might be an issue---or something we can tweak--to decrease the priority of Reports/MySQL, so that it isn’t sucking the life out of the rest of the application. We’ll keep investigating. Tasha Bales Enterprise Services http://isesi.web.boeing.com/ From: Chris Brown [mailto:ch...@stayawake.co.uk] Sent: Wednesday, October 6, 2021 23:40 To: Bales (US), Tasha R ; koha Subject: [EXTERNAL] Re: [Koha] Reports timing out, prevents access to web interfaces Importance: High EXT email: be mindful of links/attachments. Hi Tasha, Since I'm guessing you have access to a Linux command prompt on your server, you could try running "top". It would at least confirm if the CPU is saturated, and which processes are consuming all the CPU. Best Regards, Chris Brown On Wed, Oct 6, 2021 at 10:32 PM Bales (US), Tasha R mailto:tasha.r.ba...@boeing.com>> wrote: Good afternoon, I'm experiencing an issue where if I'm working in the staff interface and kick off a long-running Report, I'm unable to navigate to or use Koha in any other browser tab, or in any other browser, either in the staff or public interface. I've confirmed that this affects other users working on their own machines too; in other words, my report interrupts their access. I doubt that this is intended behavior. I'm going to try to do some query-tuning (any suggestions for tools to use to optimize/identify inefficient queries?). Any other ideas to troubleshoot this issue? I found an old list post that suggested the Koha Tuning Guide<https://wiki.koha-community.org/wiki/Koha_Tuning_Guide> on the Koha Wiki, but it isn't entirely clear to me if it is comprehensive, or if all parts are relevant to our issue. I'm hesitant to blindly start changing values. Before we pursue the tuning, are there any other suggestions? Is this really a problem that is expected to happen? I should add that long queries that need to be run on a routine basis, we'd run via cronjob. However, I'm worried that staff who work solely on the web might inadvertently run a big one-off report (or do a huge record load, also a problem), and bring down the whole system. FWIW, we have two processors: Intel(R) Xeon(R) Silver 4110 CPU @ 2.10GHz. We have about 1.3 million records and the same number of items. We're using MySQL 5.7, and Koha 20.11.06. Regarding RAM, this is the output of the "free" command, if relevant: totalused freeshared buff/cache available Mem: 65308616 203882041354324 10780021915472 62437176 Swap: 8257532 7808256752 Tasha Bales Enterprise Services http://isesi.web.boeing.com/ ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
