ansaurus

Question

Process 40M of documents (and index) as fast as possible

Answer 1

A:

Well, I'd read and parse the files multithreaded (mapping them to objects), putting these objects into a threadsafe queue which checks for duplicates, first in memory, then in the database itself, or if you have the space, totally in memory (don't know how big your xmls are). After or while doing that, you push it into the database one after another, stopping this if you need to check for a duplicate in the database. It's neither pretty nor fast, but considering you have these files unordered... maybe the best solution at hand.

Femaref 2009-10-15 23:28:34

Hi femaref.But if I do that. Will not that just full the memory? So, there's no silver bullet for this... :(Thanks for the reply

Sheldon 2009-10-16 01:34:45

Answer 2

+3 A:

The first problem here, I would say, comes from the disk access time. Even if your xml files only have 1k then they amount to 37GB of data and that takes time to read. Nothing can be done to improve this.

However, you can make sure that you don't waste extra time doing other unnecessarily blocking computation.

If the database is also in the same disk, the batches should be far greater than 1000, you want to access the database as little times as your memory allows it (if the xml files are stored consecutively in disk)
Make sure you free your variables as soon as possible so that the garbage collector can free the memory.
You want to do the xml parsing while the computer is waiting to read the files so you should set up another thread to do the tasks in parallel.

As for your second problem you could do, for each image, an update sql statement on the images with the same index and if no rows are updated the insert this image in a new row. Whether this will perform better than using a select followed by an insert/update depends on the percentage of duplicates you have.

I'm going on a limb and assume the xml files are not being created faster than you can process them, if that's the case all you need to do is save the file names that already have been processed either to the database or to a flat file and read them back the next time it starts, making sure you don't have to start all over again.

Vasco Fernandes 2009-10-15 23:31:40

Hi Vasco, thanks for your answer. I didn't even consider the thing about disk access.Well, the indexer app will be installed in the same machine. But the DB uses exclusively 2 Velociraptors mounted at raid5.I will sure implement the algorithm by using multithreading. I don't now exactly how to do the third point. Isn't it just to start a new thread. Or to do it while disk access I need to use another technique. I'm just starting with this of Java and Multithreading, I came from Ruby.... :SAlso the files exists and are stored on that server. I don't know if that changes something. Thanks!

Sheldon 2009-10-16 01:22:14

About duplicates: in MySQL there is the useful INSERT ... ON DUPLICATE KEY UPDATE statement(http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html) which deals with updating and inserting in a single instruction! Just discovered in one of my StackOverflow questions: http://stackoverflow.com/questions/1492761/performing-an-update-or-insert-depending-whether-a-row-exists-or-not-in-mysql :-)

Enrico Detoma 2009-10-16 05:58:33

If you were using c# you'd have two possibilities, the simpler one would be reading the files asynchronous (maybe you can find an non native implementation in java?). The hard way is to create a different thread. As soon as you create the thread you get parallelization on disk access for free. The hard part and that's what you have to worry about, is sharing information between threads for that you must brush up on locks, synchronized methods and monitors

Vasco Fernandes 2009-10-16 13:19:19

Answer 3

+2 A:

For indexing I suggest you to use Solr which is extremely fast at indexing a large number of documents. Solr also have a dedicated class StreamingUpdateSolrServer for updating the index using multiple threads and batch-commits.

Solr is written in Java and based on the fast full-text search engine library Lucene. It has a simple Java api through which you can submit your documents to the server for indexing.

Here's a discussion about the new StreamingUpdateSolrServer class for indexing documents in large batches.

StreamingUpdateSolrServer is available in the new Solr 1.4 release which should be out in a few days (I'm working with a nigthly 1.4 Solr build since June and I find it pretty stable already).

Enrico Detoma 2009-10-15 23:34:31

Thanks Enrico, I will give it a try.

Sheldon 2009-10-16 01:32:18

Answer 4

A:

Just an addition: I would strongly recommend to store the images in the database, too. If you already start with 120 Mio image-files you will soon be at a point where even modern state of the art filesystems have their boundries.

And you will likely have to get rid of MySQL and get a real DB (DB2, Oracle or SQL Server).

Foxfire 2009-10-16 00:13:30

Hi Foxfire.Can you explain in more details, why to put the images inside the DB?Also one thing that is very important. Is MySQL a bad DBMS for large documents?

Sheldon 2009-10-16 01:31:07

I do not advise storing images inside the DB. The file system is the right tool for the job, of course with subdirectory splitting of the whole corpus of images. Too much innecessary burden for the database to store 120M images. I do not even advise using a relational DB for storing a 37M records index, but that's another story, as I explained in my answer :-)

Enrico Detoma 2009-10-16 06:04:10

Just the MFT/Inode Table will likely be about 150GB in size (that means without ANY file actually having any content). Next thing is even NTFS and ext4 have a hard limit at 4 Billion files. You won't be able to have more files even if backed by a big SAN (or you need multiple FSs, then much fun with organisation of your files).120 Mio files will likely take about 1 day (if you have a decent system) just to open and close them all.

Foxfire 2009-10-16 08:12:09

I had a project where we had a similar problem (image files for a digital geo map). And although we had FAR fewer files (not even 1 Mio) we already had HUGE performance problems when storing in a filesystem, so we switched to the DB approach that solved most of the problems.

Foxfire 2009-10-16 08:14:57

Which DB did you use to store such a large number of images? I always thought that storing images in a DB, which in turn uses the filesystem, was inherently a bad idea, but your comments are very interesting.

Enrico Detoma 2009-10-16 09:48:08

The Database was DB2

Foxfire 2009-10-16 18:19:26

Thank you very much. I'm actually doing this following the recommendations of Foxfire. Another plus I've seen is that images' storage, search and retrieve benefits from the DB files management system. What I'm trying to do, Is convince my client to buy Oracle. I make a test with just 20GB of data. And it performs very fast on oracle compared to MySQL and SQL Server, Haven't test DB2.

Sheldon 2009-10-17 20:41:35

Answer 5

+1 A:

I suspect you are loading this data in one giant db transaction. Your individual data files are nothing challenging-looking. But the number of entries you are making into this database is 120M x avg # of keywords/image. A naive loader program does this:

start transaction
for each index file
    parse file
    for each keyword
        insert (keyword,imagename) into db
commit transaction

This forces your database to buffer the entire data load in a journal file, in case a rollback is required. Conversely, if you have enabled auto-commit, then you are doing a separate transaction for each keyword, which can also be suboptimal, since it is doing concurrency lock/unlock several hundred million times.

A middle ground is to commit every 'n' inserts, say 10,000:

inserts = 0
reset = 10000
start transaction
for each index file
    parse file
    for each keyword
        insert (keyword,imagename) into db
        inserts += 1
        if inserts % reset == 0
            commit transaction
            start transaction
commit transaction

Ideally, a loader program is also restartable - this would require a SELECT query for each index file to see if its image exists in your database before inserting the keywords.

inserts = 0
reset = 10000
start transaction
for each index file
    parse file
    if "SELECT count(*) from IMAGES where name='<insert imagename>'" == 0
        for each keyword
            insert (keyword,imagename) into db
            inserts += 1
            if inserts % reset == 0
                commit transaction
                start transaction
commit transaction

But in your case, this may be prohibitively expensive. As an alternative, you could look into what kind of import facility your database supports, and use your program to create one or more text files, probably no more complex then a list of keyword,imagename comma-delimted pairs. Then the loader should have options like commit interval or exclusive locking to minimize the lock and journal overhead.

Lastly, leave off the index on the KEYWORDS table until after the data has been loaded. During your load, this index does not help in any way, but you are updating it with every keyword,imagename insert. Adding this index after the table is loaded will take some time, but you will only pay this penalty once at the end, instead of multiple times during the load. (If you do the SELECT code in the restartable option, then do have the IMAGES table index during the load, else each SELECT would have to do a full table scan.)

Paul McGuire 2009-10-17 11:00:30

Thank you very much! I'm almost done with that

Sheldon 2009-10-17 20:33:42

ansaurus

tags:

views:

answers:

Process 40M of documents (and index) as fast as possible

related questions