<div dir="ltr">also, as a more than 1M biblios library, hurray for Jonathan :)<br></div><div class="gmail_extra"><br><div class="gmail_quote">2016-08-31 0:58 GMT+02:00 David Cook <span dir="ltr"><<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">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!<br>
<br>
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.<br>
<br>
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.<br>
<br>
Anyway, hurray for Jonathan adding patches!<br>
<span class=""><br>
David Cook<br>
Systems Librarian<br>
Prosentient Systems<br>
72/330 Wattle St<br>
Ultimo, NSW 2007<br>
</span>Australia<br>
<span class=""><br>
Office: 02 9212 0899<br>
Direct: 02 8005 0595<br>
<br>
<br>
</span><span class="">> -----Original Message-----<br>
> From: <a href="mailto:koha-devel-bounces@lists.koha-community.org">koha-devel-bounces@lists.koha-<wbr>community.org</a> [mailto:<a href="mailto:koha-devel-">koha-devel-</a><br>
> <a href="mailto:bounces@lists.koha-community.org">bounces@lists.koha-community.<wbr>org</a>] On Behalf Of Jonathan Druart<br>
> Sent: Friday, 26 August 2016 7:39 PM<br>
> To: <a href="mailto:koha-devel@lists.koha-community.org">koha-devel@lists.koha-<wbr>community.org</a><br>
> Subject: Re: [Koha-devel] biblioitems.marcxml & biblioitems.marc / HUGE<br>
> performance issue !<br>
><br>
</span><div><div class="h5">> For the record:<br>
> Bug 10455 - remove redundant 'biblioitems.marc' field and<br>
> Bug 17196 - Move marcxml out of the biblioitems table have now patches!<br>
><br>
> 2016-07-12 17:43 GMT+01:00 Paul Poulain <<a href="mailto:paul.poulain@biblibre.com">paul.poulain@biblibre.com</a>>:<br>
> > Hi all,<br>
> ><br>
> > Those days, we're working on a pretty large DB ( >1M biblio), for a<br>
> > customer that want to do many statistics on some fields.<br>
> > We discovered that something "simple" like:<br>
> > SELECT publicationyear, count(publicationyear) FROM biblioitems GROUP<br>
> > BY publicationyear;<br>
> ><br>
> > was giving no result in 10mn.<br>
> > This is a test DB, not optimized, but we were surprised by the results.<br>
> > After investigating we had the idea to create a biblioitems2 table<br>
> > with the same structure EXCEPT MARCXML and MARC fields<br>
> ><br>
> > launch the same SQL query : result in 3seconds !<br>
> > This could be reproduced on any query (on fields without index).<br>
> ><br>
> > I think it's because the innoDB is storing each line in one "object",<br>
> > so, even if you need only one column, you have to read everything.<br>
> > In our case, that was 12GB+ of data to read.<br>
> > biblioitems2 is just a few dozen MB.<br>
> > (all caching values are minimum and there's no index, so not involved<br>
> > in the<br>
> > results)<br>
> ><br>
> > MY CONCLUSIONS:<br>
> > * the biblioitems.marc field must be removed quickly: it's useless<br>
> > since years, and is only resulting in slowing things<br>
> > * the bilbioitems.marcxml field should be moved outside from this table.<br>
> > Something like biblio_blob, with biblionumber, biblioitemnumber and<br>
> marcxml.<br>
> > When we need it, just join the tables.<br>
> ><br>
> > I'm almost sure it would have an important impact on Koha, as<br>
> > biblioitems table is called and used "everywhere".<br>
> ><br>
> > any opinion ?<br>
> ><br>
> > --<br>
> > Paul Poulain, Associé-gérant / co-owner BibLibre, Services en<br>
> > logiciels libres pour les bibliothèques BibLibre, Open Source software<br>
> > and services for libraries<br>
> ><br>
> ><br>
> > ______________________________<wbr>_________________<br>
> > Koha-devel mailing list<br>
> > <a href="mailto:Koha-devel@lists.koha-community.org">Koha-devel@lists.koha-<wbr>community.org</a><br>
> > <a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" rel="noreferrer" target="_blank">http://lists.koha-community.<wbr>org/cgi-bin/mailman/listinfo/<wbr>koha-devel</a><br>
> > website : <a href="http://www.koha-community.org/" rel="noreferrer" target="_blank">http://www.koha-community.org/</a> git :<br>
> > <a href="http://git.koha-community.org/" rel="noreferrer" target="_blank">http://git.koha-community.org/</a> bugs : <a href="http://bugs.koha-community.org/" rel="noreferrer" target="_blank">http://bugs.koha-community.<wbr>org/</a><br>
> ______________________________<wbr>_________________<br>
> Koha-devel mailing list<br>
> <a href="mailto:Koha-devel@lists.koha-community.org">Koha-devel@lists.koha-<wbr>community.org</a><br>
> <a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" rel="noreferrer" target="_blank">http://lists.koha-community.<wbr>org/cgi-bin/mailman/listinfo/<wbr>koha-devel</a><br>
> website : <a href="http://www.koha-community.org/" rel="noreferrer" target="_blank">http://www.koha-community.org/</a> git : <a href="http://git.koha-" rel="noreferrer" target="_blank">http://git.koha-</a><br>
</div></div>> <a href="http://community.org/" rel="noreferrer" target="_blank">community.org/</a> bugs : <a href="http://bugs.koha-community.org/" rel="noreferrer" target="_blank">http://bugs.koha-community.<wbr>org/</a><br>
<div class="HOEnZb"><div class="h5"><br>
<br>
______________________________<wbr>_________________<br>
Koha-devel mailing list<br>
<a href="mailto:Koha-devel@lists.koha-community.org">Koha-devel@lists.koha-<wbr>community.org</a><br>
<a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" rel="noreferrer" target="_blank">http://lists.koha-community.<wbr>org/cgi-bin/mailman/listinfo/<wbr>koha-devel</a><br>
website : <a href="http://www.koha-community.org/" rel="noreferrer" target="_blank">http://www.koha-community.org/</a><br>
git : <a href="http://git.koha-community.org/" rel="noreferrer" target="_blank">http://git.koha-community.org/</a><br>
bugs : <a href="http://bugs.koha-community.org/" rel="noreferrer" target="_blank">http://bugs.koha-community.<wbr>org/</a></div></div></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div>
<br>
<span style="font-family:courier new,courier,monospace"><strong>Nicolas Legrand</strong></span></div>
<div>
<span style="font-family:courier new,courier,monospace">Administration technique et développements du système de gestion de la bibliothèque</span></div>
<br>
<div>
<span style="font-family:courier new,courier,monospace"><img alt="Logo BULAC" src="http://www.bulac.fr/a/img/logo-bulac-mail.png" style="width:200px;height:60px"></span></div>
<div>
</div>
<div>
<span style="color:#ff5e0e"><span><span style="font-family:courier new,courier,monospace">Bibliothèque universitaire </span></span></span></div>
<div>
<span style="color:#ff5e0e"><span><span style="font-family:courier new,courier,monospace">des langues et civilisations</span></span></span><br>
</div>
<div>
<span style="font-family:courier new,courier,monospace">65 rue des Grands Moulins</span></div>
<div>
<span style="font-family:courier new,courier,monospace">F-75013 PARIS</span></div>
<div>
<span style="font-family:courier new,courier,monospace">T +33 1 81 69 <strong>18 22</strong></span></div>
<div>
<a href="http://www.bulac.fr" target="_blank">www.bulac.fr</a></div></div>
</div>