[Koha] [EXTERNAL] Re: Reports timing out, prevents access to web interfaces

[Cab Vinton]

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
<tasha.r.bales at boeing.com> 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-bounces at lists.katipo.co.nz] On Behalf Of Cab Vinton
> Sent: Thursday, October 7, 2021 03:49
> To: koha <koha at lists.katipo.co.nz>
> 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha