large BLOB inserts html, currently, hibernate, properly, problem

amy

Hi all. I'm currently evaluating how to properly handle large BLOB inserts in our web application.

This web-application lets users to upload (using html form) large files and they are inserted into db.

Backbone is done using spring/hibernate/oracle

Now the problem is that inserting ~500mb blob into db takes around 180 seconds-gt;so hibernate session takes this long-gt;and as hibernate session is bind to the current user thread this means 180 sec wait time for the web-user.

So any ideas how to overcome this?
1) Stop using DB as a file storage and save all files into local disk
or
2) Perhaps some background process that transfers files from local tmp directory into db
or something else perhaps?

Inserting 500mb blob into a database, congrats for getting that to work at all.  Never tried it (hope the DB server has a large disk ), but I'm not sure how you can really make it quicker.  Try normal JDBC and see how quickly that can do it.

Thank you! Oracle 10g that we are using supports terabyte-lobs, according to oracle max size is between 8-128 terabytes. We've done the blob optimization as instructured by oracle. Spring is tuned to use oraclelobhandler and BlobByteArrayType.

Disk size isnt an issue, as most of the files are under lt;100megs and are stored in db only for 30 days or less.

Anyway, so is it a common advice to move away from the db and start using the filesystem instead?

Wow, the power of Oracle 128 terabytes, thats errr quite big........

I think it would be interesting to do some profiling to see where the time is spent.  Is it the database write, server paging out to memory etc...  If the database is co-located then your not dealing with network issues.  If you simply swap a remote database for a remote filesystem you might not get the performance increase you hoped.  I said previously, its always interesting to try straight JDBC and see what the difference is over Hibernate.

I'm not sure about common advise, regarding switching.  I would say its very much personal opinion.  Storing the users files with the user does make sense, then again I'm sure some people would disagree.

I'll say moving to the file system is common advice.
I believe using the filesystem instead of the DB will speed things up (I have no idea how much), however you will no longer have transactions, are they important to you?
However it doesn't look like you're having a problem with the DB itself, but maybe you should look at other parts of your app, for example: the biggest bottleneck here seems to be the network, are those big files compressed before sending them over the wire? Is compression useful for that type of files?
No matter how much you optimize it, uploading such a file will always take some time and the UI will play an important role telling the user what's going on. Maybe you can handle the uploads like gmail does, uploading file in the background while editing the mail (that sounds easy to do! ).

Good luck,
Federico.


Originally Posted by fschroderI'll say moving to the file system is common advice.

As I said, I'm sure people would disagree.Originally Posted by fschroderI believe using the filesystem instead of the DB will speed things up (I have no idea how much), however you will no longer have transactions, are they important to you?

I would agree, but you need to properly profile it to see what improvement you actually get.Originally Posted by fschroderNo matter how much you optimize it, uploading such a file will always take some time and the UI will play an important role telling the user what's going on. Maybe you can handle the uploads like gmail does, uploading file in the background while editing the mail (that sounds easy to do! ).

Regardless of the approach taken, the upload is going to take a while.  After that it might make sense to push the save onto another thread so the user can carry on.


Originally Posted by karldmooreAs I said, I'm sure people would disagree.

Sure someone will, and I'd really like to hear their arguments.Originally Posted by karldmooreI would agree, but you need to properly profile it to see what improvement you actually get.

That's absolutely true! Hopefully the gain will be noticeable using the app and not just xxx milliseconds saved by some JDBC processing.Originally Posted by karldmooreRegardless of the approach taken, the upload is going to take a while.  After that it might make sense to push the save onto another thread so the user can carry on.

This is a good idea, however if something can still go wrong after the upload this will make notifications to the user more difficult.


Originally Posted by fschroderThis is a good idea, however if something can still go wrong after the upload this will make notifications to the user more difficult.

I would agree that when something goes async you need some feedback.  You could have a progress bar for the file upload, but for the database or file system save what can you do.  Once you've posted your simply waiting for a response.

Might be of interest.
sho...pload+progress


Originally Posted by karldmooreRegardless of the approach taken, the upload is going to take a while.  After that it might make sense to push the save onto another thread so the user can carry on.

And this is exactly how I went on with this by using spring's taskexecutor abstraction. Result is working beautifully.

Cheers,
Karri


Originally Posted by kakoniAnd this is exactly how I went on with this by using spring's taskexecutor abstraction. Result is working beautifully.

Fantastic, glad it worked and the information was useful!