DB design (MARC structure)
Hello all, Nelsonville & me are trying to optimize queries. I'm concluding we should modify a little the DB : * marc_word has "tag", "subfield" and "word" fields. * we are querying on /tag+subfield in ('200a','245f') and word like 'tower%'/ . Such a query CANNOT be optimised by mySQL. * so, an index on /(tag, subfield,word)/ is not used. * I think we could highly optimize the search with a field called 'tagsubfield', an index on /(tagsubfield,word)/. The search being /tagsubfield in ('200a','245f') and word like 'tower%'/. Other searches (a search on "keyword", that does is done only on word, not on tag+subfield) will still be working fast, with index on (word) Does this make sense ? Any comment ? if yes, i'll do it (for friday). -- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
If I understand it correctly this sounds like a great solution to our speed issues with the current marc searching--and it won't compromise the accuracy of the search either. Let's do it. Joshua On Mon, Jun 14, 2004 at 10:59:03AM +0200, Paul POULAIN wrote:
Hello all,
Nelsonville & me are trying to optimize queries. I'm concluding we should modify a little the DB : * marc_word has "tag", "subfield" and "word" fields. * we are querying on /tag+subfield in ('200a','245f') and word like 'tower%'/ . Such a query CANNOT be optimised by mySQL. * so, an index on /(tag, subfield,word)/ is not used. * I think we could highly optimize the search with a field called 'tagsubfield', an index on /(tagsubfield,word)/. The search being /tagsubfield in ('200a','245f') and word like 'tower%'/.
Other searches (a search on "keyword", that does is done only on word, not on tag+subfield) will still be working fast, with index on (word)
Does this make sense ? Any comment ? if yes, i'll do it (for friday).
-- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
Joshua Ferraro a écrit :
If I understand it correctly this sounds like a great solution to our speed issues with the current marc searching--and it won't compromise the accuracy of the search either. Let's do it
ok, it's commited. works fine on a small DB. My SCSI disk is working since 2 hours now to try it on my copy of your DB... I'll let you know the result & confirm you can test it. -- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
Paul POULAIN a écrit :
Joshua Ferraro a écrit :
If I understand it correctly this sounds like a great solution to our speed issues with the current marc searching--and it won't compromise the accuracy of the search either. Let's do it
ok, it's commited. works fine on a small DB.
works fine on your DB too : EXPLAIN SELECT DISTINCT m1.bibid FROM biblio, biblioitems, marc_biblio, marc_word AS m1 WHERE biblio.biblionumber = marc_biblio.biblionumber AND biblio.biblionumber = biblioitems.biblionumber AND m1.bibid = marc_biblio.bibid AND ( m1.word LIKE 'wolf%' AND m1.tagsubfield IN ( '245a' ) ) ORDER BY biblio.title table type possible_keys key key_len ref rows Extra m1 range bibid,word,Marc_Search Marc_Search 259 NULL 114 Using where; Using temporary; Using filesort marc_biblio eq_ref PRIMARY,biblionumber PRIMARY 8 m1.bibid 1 Using where; Distinct biblio eq_ref PRIMARY,blbnoidx PRIMARY 4 marc_biblio.biblionumber 1 Distinct biblioitems ref bibnoidx bibnoidx 4 biblio.biblionumber 1 Using index; Distinct we can't do better ;-) NB : to have something working well : * copy your 2.0 DB * run updatedatabase. (all indexes should be created. Be patient, 5-6 hours needed on your marc_word table) -- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
Paul a écrit, I'm still showing some problems with the searching speed with the latest CVS ... maybe there are still some index problems. I tried an author search on "o'brian, patrick" and the result took about 10-15 seconds to return. Directly from mysql I get: mysql> select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_word as m3 where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title; 77 rows in set (5.34 sec) here's the explain on that query: mysql> explain select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_word as m3 where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title; +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ | m2 | range | bibid,word,Search_Marc | Search_Marc | 259 | NULL | 366 | Using where; Using temporary; Using filesort | | m1 | ref | bibid,word,Search_Marc | bibid | 8 | m2.bibid | 56 | Using where | | marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY | 8 | m1.bibid | 1 | Using where; Distinct | | biblio | eq_ref | PRIMARY,blbnoidx | PRIMARY | 4 | marc_biblio.biblionumber | 1 | Distinct | | biblioitems | ref | bibnoidx | bibnoidx | 4 | biblio.biblionumber | 12 | Using index; Distinct | | m3 | ref | bibid,word,Search_Marc | bibid | 8 | m1.bibid | 20 | Using where; Distinct | +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ 6 rows in set (0.04 sec) mysql> So it still looks like we're using temp and filesort--which I assume is causing the hangup ... if that is the best we can do we may need to start thinking about breaking up marc_word into sections (e.g., marc_word_title; marc_word_author, etc.). The search is really accurate but just too slow for a production database as large as ours. What do folks think, would that speed things up? Joshua On Thu, Jun 17, 2004 at 05:20:45PM +0200, Paul POULAIN wrote:
Paul POULAIN a écrit :
Joshua Ferraro a écrit :
If I understand it correctly this sounds like a great solution to our speed issues with the current marc searching--and it won't compromise the accuracy of the search either. Let's do it
ok, it's commited. works fine on a small DB.
works fine on your DB too :
EXPLAIN SELECT DISTINCT m1.bibid FROM biblio, biblioitems, marc_biblio, marc_word AS m1 WHERE biblio.biblionumber = marc_biblio.biblionumber AND biblio.biblionumber = biblioitems.biblionumber AND m1.bibid = marc_biblio.bibid AND ( m1.word LIKE 'wolf%' AND m1.tagsubfield IN ( '245a' ) ) ORDER BY biblio.title
table type possible_keys key key_len ref rows Extra m1 range bibid,word,Marc_Search Marc_Search 259 NULL 114 Using where; Using temporary; Using filesort marc_biblio eq_ref PRIMARY,biblionumber PRIMARY 8 m1.bibid 1 Using where; Distinct biblio eq_ref PRIMARY,blbnoidx PRIMARY 4 marc_biblio.biblionumber 1 Distinct biblioitems ref bibnoidx bibnoidx 4 biblio.biblionumber 1 Using index; Distinct
we can't do better ;-)
NB : to have something working well : * copy your 2.0 DB * run updatedatabase. (all indexes should be created. Be patient, 5-6 hours needed on your marc_word table)
-- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
------------------------------------------------------- This SF.Net email is sponsored by The 2004 JavaOne(SM) Conference Learn from the experts at JavaOne(SM), Sun's Worldwide Java Developer Conference, June 28 - July 1 at the Moscone Center in San Francisco, CA REGISTER AND SAVE! http://java.sun.com/javaone/sf Priority Code NWMGYKND _______________________________________________ Koha-devel mailing list Koha-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/koha-devel
Joshua Ferraro a écrit :
Paul a écrit,
I'm still showing some problems with the searching speed with the latest CVS ... maybe there are still some index problems. I tried an author search on "o'brian, patrick" and the result took about 10-15 seconds to return. Directly from mysql I get:
mysql> select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_word as m3 where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title;
77 rows in set (5.34 sec)
here's the explain on that query:
mysql> explain select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_word as m3 where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title; +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ | m2 | range | bibid,word,Search_Marc | Search_Marc | 259 | NULL | 366 | Using where; Using temporary; Using filesort | | m1 | ref | bibid,word,Search_Marc | bibid | 8 | m2.bibid | 56 | Using where | | marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY | 8 | m1.bibid | 1 | Using where; Distinct | | biblio | eq_ref | PRIMARY,blbnoidx | PRIMARY | 4 | marc_biblio.biblionumber | 1 | Distinct | | biblioitems | ref | bibnoidx | bibnoidx | 4 | biblio.biblionumber | 12 | Using index; Distinct | | m3 | ref | bibid,word,Search_Marc | bibid | 8 | m1.bibid | 20 | Using where; Distinct | +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ 6 rows in set (0.04 sec)
mysql>
So it still looks like we're using temp and filesort--which I assume is causing the hangup ... if that is the best we can do we may need to start thinking about breaking up marc_word into sections (e.g., marc_word_title; marc_word_author, etc.). The search is really accurate but just too slow for a production database as large as ours. What do folks think, would that speed things up?
I think it's too complex to code. The DB would need a big rewritte & multi-marc support would be a pain. Some ideas to continue speeding things : * analyse the table : http://www.databasejournal.com/features/mysql/article.php/10897_1382791_3 * is your my.cnf correctly set ? (ie with big caches : "Eliminating the filesort to speed things up is best done by calculating how big your result set of this operation can become and then increase the server variable sort_buffer_size which is the maximum size that mysqld will keep in memory before using a filesort instead. Note that if your resultset is extremely big then you might consume more memory than is advisable and then things might slow down because of swaping" hint given here : http://forums.devshed.com/showthread.php?t=149288. Setting sort_buffer_size=16M seems to make my harddisk silent ;-) ) * change the index to make it "unique". The problem being to change the table definition (with a lot of values in it, is will be quite hard). Not sure of the speed improvement * move temporary index to RAM (how ?) * if we add I "limit 0,200" to the query, things are faster. Maybe we could add a "0,50" or some systempref value. * how many lines have you in your marc_word table ? mine is 9 000 000, and answers are faster than for you (around 5seconds), except when the result is >200 entries. * do you have in marc_word only tags that are interesting (= did you discard, for example, 0xx tags). I think yes, even if, for instance, it's not a standard Koha hack ;-) * multi-word is slower than single word (in your case o'brian is a 2 word seach). (note I'm still thinking it's a not a good idea to index 1 letter words). * An idea could be to split marc_word into 10 tables, one for each nXX tag (0xx, 1xx, 2xx...) I'm not sure we had a big improvement here, because for some searches, we could have to query a lot of differents tables. another problem is that some tables would be almost empty (like 0xx), and some would be huge (like 7xx probably). * Remove ordering of the result. It will remove the "using filesort", which is bad. which improvement does it give Joshua ? maybe we could order the result with a perl script AFTER building the result (I'm not sure it would be a good idea, as it's not compatible with any limit clause (& supposes to retrieve all the result list) HTH -- Paul POULAIN Consultant indépendant en logiciels libres responsable francophone de koha (SIGB libre http://www.koha-fr.org)
Paul a écrit,
So it still looks like we're using temp and filesort--which I assume is causing the hangup ... if that is the best we can do we may need to start thinking about breaking up marc_word into sections (e.g., marc_word_title; marc_word_author, etc.). The search is really accurate but just too slow for a production database as large as ours. What do folks think, would that speed things up?
I think it's too complex to code. The DB would need a big rewritte & multi-marc support would be a pain. Some ideas to continue speeding things : * analyse the table : http://www.databasejournal.com/features/mysql/article.php/10897_1382791_3 done:
mysql> analyze table marc_word; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | Koha.marc_word | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (18 min 34.90 sec)
* is your my.cnf correctly set ? (ie with big caches : "Eliminating the filesort to speed things up is best done by calculating how big your result set of this operation can become and then increase the server variable sort_buffer_size which is the maximum size that mysqld will keep in memory before using a filesort instead. Note that if your resultset is extremely big then you might consume more memory than is advisable and then things might slow down because of swaping" hint given here : http://forums.devshed.com/showthread.php?t=149288. Setting sort_buffer_size=16M seems to make my harddisk silent ;-) ) yep ... set to 64M
* change the index to make it "unique". The problem being to change the table definition (with a lot of values in it, is will be quite hard). Not sure of the speed improvement not sure how to do this ...
* move temporary index to RAM (how ?) ??
* if we add I "limit 0,200" to the query, things are faster. Maybe we could add a "0,50" or some systempref value. even when the result set is only about 70 or so it's taking about 18 seconds to return a result DIRECTLY FROM MYSQL.
* how many lines have you in your marc_word table ? mine is 9 000 000, and answers are faster than for you (around 5seconds), except when the result is >200 entries. mysql> select count(*) from marc_word; +----------+ | count(*) | +----------+ | 3933356 | +----------+ 1 row in set (0.00 sec)
* do you have in marc_word only tags that are interesting (= did you discard, for example, 0xx tags). I think yes, even if, for instance, it's not a standard Koha hack ;-) yes, our marc_word table is VERY clean ... it was rebuilt using the build_marc_word.pl script in updater/
* multi-word is slower than single word (in your case o'brian is a 2 word seach). (note I'm still thinking it's a not a good idea to index 1 letter words). we really need the accuracy that one-word indexing provides and there is not much difference in size for us between indexing one-word and two/three-word...
* An idea could be to split marc_word into 10 tables, one for each nXX tag (0xx, 1xx, 2xx...) I'm not sure we had a big improvement here, because for some searches, we could have to query a lot of differents tables. another problem is that some tables would be almost empty (like 0xx), and some would be huge (like 7xx probably). yea, I still like the idea of setting up seperate marc_word tables for each search type ... tho I realize as you said that it would require quite a big change and lots of work ...
* Remove ordering of the result. It will remove the "using filesort", which is bad. which improvement does it give Joshua ? maybe we could order the result with a perl script AFTER building the result (I'm not sure it would be a good idea, as it's not compatible with any limit clause (& supposes to retrieve all the result list) hmmm, removing ordering gets rid of "using filesort" and does speed things up from 18-25 secs to 8-10 secs directly from mysql (still too long?).
I've been looking at the optimization section of the mysql manual http://dev.mysql.com/doc/mysql/en/EXPLAIN.html and I have some questions about our design. first, the two explains: mysql> explain marc_word; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | bibid | bigint(20) | | MUL | 0 | | | tagsubfield | varchar(4) | | MUL | | | | tagorder | tinyint(4) | | MUL | 1 | | | subfieldorder | tinyint(4) | | MUL | 1 | | | word | varchar(255) | | MUL | | | | sndx_word | varchar(255) | | MUL | | | +---------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> explain select distinct m1.bibid from biblio,biblioitems,marc_biblio,marc_word as m1,marc_word as m2,marc_word as m3 where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and (m1.bibid=m2.bibid and m1.bibid=m3.bibid) and ((m1.word like 'o%' and m1.tagsubfield in ('100a','110a', '700a', '710a'))and (m2.word like 'brian%' and m2.tagsubfield in('100a','110a', '700a', '710a'))and (m3.word like 'patrick%' and m3.tagsubfield in('100a','110a', '700a', '710a'))) order by biblio.title; +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ | m2 | range | bibid,word,Search_Marc | Search_Marc | 259 | NULL | 366 | Using where; Using temporary; Using filesort | | m1 | ref | bibid,word,Search_Marc | bibid | 8 | m2.bibid | 28 | Using where | | marc_biblio | eq_ref | PRIMARY,biblionumber | PRIMARY | 8 | m1.bibid | 1 | Using where; Distinct | | biblio | eq_ref | PRIMARY,blbnoidx | PRIMARY | 4 | marc_biblio.biblionumber | 1 | Distinct | | biblioitems | ref | bibnoidx | bibnoidx | 4 | biblio.biblionumber | 12 | Using index; Distinct | | m3 | ref | bibid,word,Search_Marc | bibid | 8 | m1.bibid | 28 | Using where; Distinct | +-------------+--------+------------------------+-------------+---------+--------------------------+------+-----------------------------------------------+ 6 rows in set (0.01 sec) So according to the manual "you can get a good indication of how good a join is by taking the product of the values in the rows column of the EXPLAIN output. This should tell you roughly how many rows MySQL must examine to execute the query." So for this example we have: 366 * 28 * 12 * 28 = 3 443 328
I know there's been a little traffic on the list before about this, but I can't find it now. I'm working on installing Koha 2.0.0 on RedHat Enterprise Linux (and documenting the process), and I'm running into utf-8 problems. Specifically, when I try to do a z3950 search, the log file fills up with "Malformed UTF-8 character" messages. (Just a note, when I installed Koha and was asked if the terminal supports iso 8859-1, I answered 'no.') I tried changing the syntax for LOC in the z3950servers table to "UNIMARC," which produced a "still 1 requests to go" message in Koha that never went away (and didn't produce a log). Can anyone suggest some other things to try? Is this utf-8 stuff going to be a problem elsewhere in Koha? -- Stephen Hedges Skemotah Solutions, USA www.skemotah.com -- shedges@skemotah.com
Stephen Hedges wrote: [...]
I'm working on installing Koha 2.0.0 on RedHat Enterprise Linux (and documenting the process), and I'm running into utf-8 problems. [...]
Gottcha! At last one more person stumpled upon it...! Read this: /koha/z3950/encodingfix/README-english IMPORTING FROM Z39.50 SERVER WITH Character Set: win1250 (ISO 6937/2) into KOHA-2.0 WITH UTF-8 Files from this directory are here as a fix only and are of temporary nature. The fix was delivered to me by Zbigniew Bomart.It will probably work for the Polish Language only. The issue needs to be addressed by Koha developers for the general audience. There's a general problem with z39.50 import feature for koha instalations whose default charset IS NOT iso-8859-1. When importing from z39.50 servers that keep their records in windows-1250 charset encoding and koha installation with UTF-8 one may use the replacement files in this directory. Copy them to the following location: /usr/local/koha/intranet/modules/C4/Biblio.pm /usr/local/koha/intranet/cgi-bin/z3950/search.pl and enjoy importing records in UTF-8 from Character Set: win1250 (ISO 6937/2) servers. I tested it and the files seem to work on Fedora Core 1 and Koha-2.0 Remember to set the appropriate permissions on the files once you copy them. On my machine it is: -rwxr-xr-x 1 apache apache 91813 May 2 18:34 Biblio.pm -rwxr-xr-x 1 apache apache 3263 May 2 18:39 search.pl Benedict P. Barszcz
Stephen Hedges wrote: [...]
I'm working on installing Koha 2.0.0 on RedHat Enterprise Linux (and documenting the process), and I'm running into utf-8 problems. [...]
Gottcha! At last one more person stumpled upon it...! Read this: /koha/z3950/encodingfix/README-english
IMPORTING FROM Z39.50 SERVER WITH Character Set: win1250 (ISO 6937/2) into KOHA-2.0 WITH UTF-8
Files from this directory are here as a fix only and are of temporary nature. The fix was delivered to me by Zbigniew Bomart.It will probably work for the Polish Language only. The issue needs to be addressed by Koha developers for the general audience. There's a general problem with z39.50 import feature for koha instalations whose default charset IS NOT iso-8859-1. When importing from z39.50 servers that keep their records in windows-1250 charset encoding and koha installation with UTF-8 one may use the replacement files in this directory. Copy them to the following location: /usr/local/koha/intranet/modules/C4/Biblio.pm /usr/local/koha/intranet/cgi-bin/z3950/search.pl
and enjoy importing records in UTF-8 from Character Set: win1250 (ISO 6937/2) servers. I tested it and the files seem to work on Fedora Core 1 and Koha-2.0
Remember to set the appropriate permissions on the files once you copy
Thanks, Benedykt, but actually I discovered my problem was just my own stupidity. I found that I was searching for ISBN numbers that actually were _not_ in the Library of Congress. (I tried 4 different ones, what are the chances that none would be in LOC?) When I tried the ISBN search again with an ISBN that I _knew_ was in LOC, then it worked like a charm, didn't matter whether I was using the 2.0.0 code or your code. I'm using RedHat Enterprise Linux AS version 3, maybe there's something different about your Fedora Code 1? Stephen Benedykt P. Barszcz said: them. On my machine it is:
-rwxr-xr-x 1 apache apache 91813 May 2 18:34 Biblio.pm -rwxr-xr-x 1 apache apache 3263 May 2 18:39 search.pl
Benedict P. Barszcz
-- Stephen Hedges Skemotah Solutions, USA www.skemotah.com -- shedges@skemotah.com
participants (4)
-
Benedykt P. Barszcz -
Joshua Ferraro -
Paul POULAIN -
Stephen Hedges