Chapter 7. Handling Large Data

The pros of storing data in the database:

• Simpler data management, backups, etc.
• Easier to retrieve and store data.
• No hassle with low-level filesystem management.

The cons of storing data in the database:

• 4Gbyte/2Gbyte max table size.
• Much slower.

If you store data on the hard drive in a raw form, there are many challenges that you need to solve. One problem is the filename determination algorithm. You will need to choose filesystem-safe filenames. You will also need to break your files across directories if you will be having large numbers of files. Filesystems, although very fast, will have problems manipulating very large numbers of files within the same directory. There are also low-level implementation issues and filesystem quirks that you will have to be aware of (such as inode sizing, etc.)

If you choose the database to store your data for simplicity, you will have to ensure that your tables don't exceed their current size limitations. You will also have to be aware that transferring the data through the database will be quite a bit slower than the filesystem.

With that said, let's look at what you have to do to interface with MySQL to transfer large data.

The first thing you need to do is to decide what the largest amount of data you will ever transfer at once. There is a mysqld parameter which you will have to adjust to accommodate your data sizes. That parameter is net_buffer_length which determines the largest packet you can communicate with. You need to set this to at least the size of the largest BLOB you are going to use.

The next issue is transferring the data from the server. If you ever transfer multiple BLOBS during one query, you will probably want to use mysql_use_result to stream the data so that you don't suffer too much from malloc penalties. You need to be aware, though, that it will still buffer the entire size of the row. Thus, if you have a 2Mbyte entry in your table, both the client and the server will need to malloc this much memory at once.

Determining to use the database for large BLOB storage will be a choice of how much easier it is for you, and whether or not you can deal with the performance and space restrictions.