Hi folks!

I'm considering using MySQL for an upcoming project. It is a digital production management server, which will need to scale into the terrabyte+ range. A vast majority (99%+) of the data taking up this space will be binary large objects, some of which could range to many gigabytes in size, but most "files" will be in the 1mb to 100mb range. Basically think of it as a source control system for digital media files, with version and history management, check-in/check-out, branching/merging, searching, etc. The db has to be able to run on Linux and Windows at a minimum.

Does anybody think MySQL is not up to the task here? This database won't be overly complex in model I don't think, but it will get HUGE both in total size and number of records (some files could get hundreds of versions, and remember, they are binary...tough to diff well). I plan on not using transactions, nor do we need to wait for the SP functionality. The database will be "front-ended" by a single app server, this server will handle client requests, and will be written in Java (thus, we would be using J/Connector). The server will be responsible for synchronization / transaction issues of consistent objects, thus we do not need TX support out of the db. If someday in the unlikely event we ever need to scale the appserver, it will do distributed synchronization / lock management. The thinking is we want to keep this out of the db, since the db is going to probably be the bottleneck here.

Once performance concern I have is redundant data transfer. Since we will have several client, some very thin (web) and others very thick, I want them to be very dump as to the db structure, it needs to be encapsulated into the appserver so that the API exposed tot he client is very simple. However, this does mean that when a file is requested, it will need to be transferred out of MySQL to the appserver, then from the appserver to the client. Now, if the appserver and db are two processes on the same machine, no biggie (although this still isn't great). But, if they are on different machines, we now have the data going across the network twice. Now, for scalability reasons, it would be nice to offer the ability to run the appserver and db on different machines, but I think it will be far more common that people just want to use one monster box to simplify things. Therefore, it would be desireable to run MySQL embedded, right? I'm thinking we could implement a simple C++ wrapper which embeds both the JVM and the db, so transfer of data between the two is lightning quick? Will we see a performance difference vs running two processes on the same machine? It's obviously a more complex solution, since the "wrapper" would have to be configurable to be told whether or not the MySQL should be created locally in process, or whether it exists elsewhere (so he doesn't create it), and then pass the info into the JVM via system property or something. Does anybody think this is overkill? Is MySQL embeddable like this?

On a side note, eventually we would like to be able to develop "data blades" which can inspect the data in different ways and build custom indices for special types of searching, like based on contrast, color, etc of the data in a picture for instance. Does MySQL have this data blade / plugin concept, or would this be a 100% external solution which simply points into our db by the unique identifiers?