INSERTing millions of files into a table

Question

I archive millions of XML files (1-100MB each) in a table with the structure of

CREATE TABLE Data
(
    ID int(11) unsigned NOT NULL,
    XML longtext COMPRESSED,
    PRIMARY KEY(ID)
) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO Data (ID,XML) VALUES ($id,LOAD_FILE('file.xml'));

The process is slow, around 2-5 inserts/second. The entire database would be too large for an SDD drive, and I create the database on a separate HDD, but I move the files in batches to an SDD drive to make the reading faster. Note that disk speed is not the rate-determining step, as XML data are hugely shrunk by compression.

I tried InnoDB to gain concurrent insert, but the size of InnoDB ibd is three times larger than ARIA/MyISAM, and InnoDB is much slower on HDD.

I tried ROCKSDB, but it cannot be created on a separate disk, as there is one single directory for all tables. Also, the memory management of ROCKSDB is terrible for such scenarios (or I could not find the proper configuration).

I did not try ARCHIVE engine performance since it needs ID to be in order.

My current solution is to INSERT concurrently to a temporary InnoDB table on SSD and then INSERT INTO SELECT from the InnoDB table to the ARIA table on HDD. The problem is the integrity and delay in emptying the InnoDB and starting the concurrent INSERT process.

I appreciate any possible solution.

Answer 1

Some notes:

• InnoDB's COMPRESSED gives about 50% shrinkage of disk space.
• gzip (and most other file-based tools) compress down to about 33%. So, it is better to shrink in the client instead of the server, even if they are the same machine. Note: This requires storing into LONGBLOB, not LONGTEXT. And it requires the clients to uncompress when reading.
• By moving compression to the client, inserting can use multiple processes (and CPU cores) to speed up the task. Still, I would guess that 3 processes is perhaps the practical limit; the number of cores is definitely a limit. (Using too many processes makes them stumble over each other, thereby decreasing overall efficiency.)
• Conclusion from above items: To significantly speed up loading the database, have multiple processes, each compressing and inserting of one XML file into a table with id and LONGBLOB.
• If the XMLs are left as files, the OS can handle [de]compression. (And probably the 33% metric applies.) Meanwhile, the database table could have two columns: id and filename (or path).
• For inserting/fetching a single compressed XML, each of the techniques will take about the same amount of elapsed time -- [un]compressing and I/O are the main factors.

Answer 2

This is a lot of work to get all these files into the database.

Question: What are you going to do with them once they're in there?

If the answer is something like ...

When X happens, I want to extract the XML into a file and then ..."

... then your solution is simple. Don't do it!

That would be a classic case of incurring lots of overhead getting them into the database, followed by more overhead getting them out again, just to use them as the files they started off as.

If you do decide you need to keep them in the database, then ...
That's a very simple table structure and I assume you're only ever going to retrieve the whole "slab" of Xml in one go, based on the id column. If you're going to do anything else then you should be thinking that you might need to extract those data that you want to filter by into simple columns. Yes, there are fancy Xml-aware indexes these days, but they'll never perform as well as a single column with an index on it.