[Koha-devel] biblioitems.marcxml & biblioitems.marc / HUGE performance issue !

[David Cook]

Excitement! I've been looking forward to patches for those bugs for years. I don't have the time to test them now, but hoping that someone does soon!

Of course, moving biblioitems.marcxml will break a lot of SQL reports I'm sure, but I think it's worth it. The broken reports should generate noisy errors, so it's not like they would silently fail. And there comes a time where you just need to move on to something new.

 I'm curious about our "table","deletedtable" methodology. I suppose by moving data to "deletedtable" for historical purposes, we're theoretically improving performance for "table" and making queries simple for active records. I think we lose data integrity sometimes (more so with issues and reserves than records) by moving data to historical tables. I suppose we're entrenched in that design now though, so discussion would be a bit moot.

Anyway, hurray for Jonathan adding patches!

David Cook
Systems Librarian
Prosentient Systems
72/330 Wattle St
Ultimo, NSW 2007
Australia

Office: 02 9212 0899
Direct: 02 8005 0595


> -----Original Message-----
> From: koha-devel-bounces at lists.koha-community.org [mailto:koha-devel-
> bounces at lists.koha-community.org] On Behalf Of Jonathan Druart
> Sent: Friday, 26 August 2016 7:39 PM
> To: koha-devel at lists.koha-community.org
> Subject: Re: [Koha-devel] biblioitems.marcxml & biblioitems.marc / HUGE
> performance issue !
> 
> For the record:
>   Bug 10455 - remove redundant 'biblioitems.marc' field and
>   Bug 17196 - Move marcxml out of the biblioitems table have now patches!
> 
> 2016-07-12 17:43 GMT+01:00 Paul Poulain <paul.poulain at biblibre.com>:
> > Hi all,
> >
> > Those days, we're working on a pretty large DB ( >1M biblio), for a
> > customer that want to do many statistics on some fields.
> > We discovered that something "simple" like:
> > SELECT publicationyear, count(publicationyear) FROM biblioitems GROUP
> > BY publicationyear;
> >
> > was giving no result in 10mn.
> > This is a test DB, not optimized, but we were surprised by the results.
> > After investigating we had the idea to create a biblioitems2 table
> > with the same structure EXCEPT MARCXML and MARC fields
> >
> > launch the same SQL query : result in 3seconds !
> > This could be reproduced on any query (on fields without index).
> >
> > I think it's because the innoDB is storing each line in one "object",
> > so, even if you need only one column, you have to read everything.
> > In our case, that was 12GB+ of data to read.
> > biblioitems2 is just a few dozen MB.
> > (all caching values are minimum and there's no index, so not involved
> > in the
> > results)
> >
> > MY CONCLUSIONS:
> >  * the biblioitems.marc field must be removed quickly: it's useless
> > since years, and is only resulting in slowing things
> >  * the bilbioitems.marcxml field should be moved outside from this table.
> > Something like biblio_blob, with biblionumber, biblioitemnumber and
> marcxml.
> > When we need it, just join the tables.
> >
> > I'm almost sure it would have an important impact on Koha, as
> > biblioitems table is called and used "everywhere".
> >
> > any opinion ?
> >
> > --
> > Paul Poulain, Associé-gérant / co-owner BibLibre, Services en
> > logiciels libres pour les bibliothèques BibLibre, Open Source software
> > and services for libraries
> >
> >
> > _______________________________________________
> > Koha-devel mailing list
> > Koha-devel at lists.koha-community.org
> > http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> > website : http://www.koha-community.org/ git :
> > http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha-community.org
> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> website : http://www.koha-community.org/ git : http://git.koha-
> community.org/ bugs : http://bugs.koha-community.org/