Xapian discuss - Apr 2013 - Converting MySQL database to Xapian

I am looking for some guidance on converting a large MySQL database to Xapian.
The current structure is that the database is broken up into 160
"sub-databases". There are 50,000 or so records in each stub database.
Each record has content that I am full-text indexing. The average size of the
text is about 59k characters. The database is broken up into sub-databases
because the MySQL full-text handling is so abysmal. I use MySQL-Proxy and some
Lua modules to make the searching and inserting transparent to the client
programs. This data is written once and never (or once in a blue moon a record
is deleted) modified. What I am looking to do is:

1. Create a xapian database on a remote server where each record's indexed
text content would be a document (storing the document ID in the MySQL data
record). I will then load the DB from the old MySQL server into the xapian
server (eventually retiring the old server). It must remain in operation during
the change-over.

2. Be able to access the xapian database via PHP and Omega from multiple clients
(read only) on remote systems.

3. Insert new content on the server containing the xapian database. This
insertion will take place on the server containing the xapian database.

Looking over the docs and googling suggests that I may need multiple xapian
databases since the update time appears to be appreciable and I can't knock
the db offline while updating (24/7 operation).

Omega will be run from a remote system.

Part of my problem is that I am not sure of how the remote backends are set up
and do I need local stub DBs, which back-end to use, etc.

I would appreciate any help

Thanks,

Michael Lewis

On Thu, Apr 25, 2013 at 10:56 PM, Michael Lewis <mal at icginc.com>
wrote:
> I would appreciate any help
So I guess it's hard to help with such a large problem here, with
little specific questions.

If I were in your position I would ignore the migration phase for the
time being, and assume there will be ways to do it later.

Then, with some test data you should recreate what you're trying to do
and test it.  I guess you will have questions about mulit-db support
and sharding, etc, but searching back through this mailing list is
normally really helpful when the docs fail you.

It sounds like you have the sharding problem solved though, as your
"sub databases" (I don't really know what that means), are
effectively
shards already.

Xapian is very flexible, so from what you've said I think it will all
be possible.  It's really hard to give any more help without getting
involved properly in your project, or without you breaking it down in
to smaller questions about any problems you've found.

Sym

--
E: sym.roe at talusdesign.co.uk
M: 07742079314
@symroe

Hi Michael,

On Thu, Apr 25, 2013 at 04:56:26PM -0500, Michael Lewis
wrote:
> I am looking for some guidance on converting a large MySQL database to
Xapian. The current structure is that the database is broken up into 160
"sub-databases". There are 50,000 or so records in each stub database.
Each record has content that I am full-text indexing. The average size of the
text is about 59k characters. The database is broken up into sub-databases
because the MySQL full-text handling is so abysmal. I use MySQL-Proxy and some
Lua modules to make the searching and inserting transparent to the client
programs. This data is written once and never (or once in a blue moon a record
is deleted) modified. What I am looking to do is:
we have a similar fragmented setup for our library catalogs, 193 at this time.
See:

http://kug.ub.uni-koeln.de/

The data comes from different sources, e.g. not only from local
library systems, but also from OpenLibrary, WikiSource, Project
Gutenberg and is exported in a unified JSON format.

For each source catalog we create

a) a separate PostgreSQL database with all relevant information
(formerly we used MySQL, but switched to PostgreSQL recently)
b) a separate Xapian index. The data for each document is a
predefined set of catalog fields in JSON format for display in search
result lists. We use pure Xapian, no Omega etc.

With Xapian you can

a) join several or all indexes at search time. For lots of indexes we
experienced problems with wildcard searches, though...

b) physically join each index to a single index with xapian-compact.
This compacted index is optimized for search.

c) export its index and access it as a remote backend

We use both a) and b) by make it configurable for which combination of
databases (=predefined search profile) we use a compacted index. We
don't use c) as all Postgresql databases and Xapian indexes are on
each application server. 

We use a setup of two clusters with two servers each. While one
cluster is searchable the other is updated. As soon as updating has
finished they swap. For load balancing/distribution of search requests
we use haproxy. 

We experienced that putting all information in a search index only is
- for our library catalogs - not advisable. The additioal PostgreSQL
databases comes in handy when analysing and mining our data.

So a possibly setup for you could be - provided you want to keep your
sub-databases

1) Build a xapian index for each mysql sub-database, preferably with
Omega
2) Join the indexes with xapian-compact to a single index.
3) Export this single index, again with Omega. 

Compacting is very fast. For our 193 catalogs with currently
12.387.390 titles it take around 40 minutes.

I wouldn't export each sub-index and then join it remotely at search
time, because I guess

a) setup is a mess
b) degraded performance

Try to export only one index.

Regards,

Oliver Flimm

-- 
Universitaet zu Koeln :: Universitaets- und Stadtbibliothek
IT-Dienste :: Abteilung Universitaetsgesamtkatalog
Universitaetsstr. 33 :: D-50931 Koeln
Tel.: +49 221 470-3330 :: Fax: +49 221 470-5166
flimm at ub.uni-koeln.de :: www.ub.uni-koeln.de