exo-jcr

The issue was tested on server configuration:

=== Hardware ===
Model : SuperMicro 7045B-3R
CPU : 2 x Intel Xeon E5345 (Quad-Core, 8MB cache, 2.33GHh, FSB - 1333 GHz, 80W)
RAM : Apacer 8?2GB FB DDR2 ECC 667MHz, Total 16GB
HDD : 5?250GB SATA in RAID 5 (Super AOC LPZCR2)
MotherBoard : Super X7DB3 (Intel 5000P, on-board Adaptec AIC 9140 SAS controller)

=== Software ===
OS : Fedora 7 x86_64, kernel 2.6.22.9-91.fc7
Java : java version "1.5.0_14"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_14-b03)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_14-b03, mixed mode)
MySQL : MySQL-server-5.0.45-0.glibc23.x86_64

MySQL was installed from rpm obtained from www.mysql.com.

Initial configuration (/etc/my.cnf) used from MySQL distro template my-huge.cnf (/usr/share/mysql/my-huge.cnf).

Next steps were performed using various resources from Internet (www.mysql.com, forums).

The main notes were applied to a 'huge' template.

1. key_buffer_size. On dedicated MySQL servers, the rule-of-thumb is to aim to set the key_buffer_size to at least a quarter,
but no more than half, of the total amount of memory on the server.
Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server)

key_buffer = 4096M

2. sort_buffer_size. Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size.
Increase this value for faster ORDER BY or GROUP BY operations.
The default value for sort_buffer_size is 2MB. Please note this buffer is at a per client level.

sort_buffer_size = 8M

3. read_buffer_size. Each thread that does a sequential scan allocates a buffer of this size for each table it scans.
If you do many sequential scans,you might want to increase this value. Default value is 128K. Optimal is 2M for 'huge' operations.

read_buffer_size = 8M, using same as sort_buffer_size, but may be decreased to 2M.

4. read_rnd_buffer_size is used after a sort for reading the rows in the sorted order.
If your application has a lot of queries with ORDER BY, increasing this can improve the performance.
This is buffer is also at a per client basis. The default value for read_rnd_buffer_size is 128K.
A general rule of thumb is to allocate 1MB for every 1GB memory.

read_rnd_buffer_size = 16M

5. myisam_sort_buffer_size. Used as buffer when alter table & myisam sorting is required.

myisam_sort_buffer_size = 128M, but actually may be decreased to 64M in case of out of memory errors.

6. thread_cache_size. The number of threads created to handle connections

thread_cache_size = 16

These parameters were changed in original my-huge.cnf file.
The performance result with these changes much better of a default configuration.
Use these parameters values as a start point for production servers according to a concrete hardware (CPU/memory).

Notes about work with BLOBs
It's a necessary to set max_allowed_packet variable higher of default (1M).
Actually MySQL driver may create a packed with size of whole file you are writing. Let it be
max_allowed_packet=32M

And have much of memory for user threads...

There are a BLOBs tip from MySQL http://www.mysql.com/news-and-events/newsletter/2003-09/a0000000237.html

MySQL supports BLOBs (Binary Large Objects), which means you can store any binary file into MySQL. Many people ask, what is the maximum size of a BLOB in MySQL.
The theoretical limit in MySQL 4.0 is 2G, however each blob requires generally to have 3 copies of it in the memory (stored in various buffers) so you need a lot of memory, if you have large BLOBs stored in MySQL. This is the reason, why the theoretical limit can be reached only on 64bit systems. The Practical limits are around some hundreds of megs per BLOB.

So, a BLOBs with size of some hundreds of megs can be stored only.

max_allowed_packet notes (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html)

The maximum size of one packet or any generated/intermediate string.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB.

Seems MySQL JDBC driver convert any InputStream for BLOB into bytes (com.mysql.jdbc.PreparedStatement.streamToBytes()).

http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html

So, no chance to upload large file anyway, only if we will not have a much memory (more of the file size).