Re: Partitioning and multiple threads

[Rick James]

A single thread will not do any parallelism. You are using multiple threads, so it makes some sense to split by PARTITION. "Range" partitioning seems reasonable; then you have explicit control over the partition key.

I need a little more info to further discuss this.
* You have 100M rows being INSERTed in a day?
* They need some kind of PRIMARY KEY, but the details of it are not critical? (This may lead to a compound key tailored to the processing.)
* You will be DELETEing _all_ the records eventually? (DELETE is costly; DROP PARTITION is extremely cheap.)
* If you might not DELETE a row immediately, perhaps it could be copied to another table, then logically deleted from the main, big, table?
* Can the INSERTing script(s) pick which PARTITION to INSERT into? (This might be handy.)
* Do the worker threads care what order they process the rows in? (This might lead to "Hash" partitioning instead of Range.)
* Can a worker thread grab 100 items at a time?
* How do you do the INSERTs? Bulk? LOAD DATA?