> Played with MyISAM - (performance) results seemed to be better than InnoDB but table level locking and possible long recover after crash discorged me from using it.
Right. Go with InnoDB.
96 PARTITIONs -- that's about the practical limit. (There are inefficiencies when having too many partitions; 5.7 / 10.1 may fix some of them.)
> Estimated number of records inserted per hour: 20M (peak 50M)
50M INSERTs/hr = 14K/sec => several performance issues. First, some questions:
* Disks? Rotating? SSDs? RAIDed?
* Batch insert? LOAD DATA? Multi-threaded?
* How random are the indexes?
You really ought to have an PRIMARY KEY, even it is an artificial AUTO_INCREMENT. (If you don't specify a PK, InnoDB gives you a _hidden_ 6-byte int-like thingie.)
> 5.2.10-MariaDB
> Change version to mariadb10.0.x to support better partition pruning (it is almost not supported now)
Good.
> RANGE partitioning
Good. See my blog for more discussion:
http://mysql.rjweb.org/doc.php/partitionmaint
> Add an additional index key
Caution -- each INDEX must be updated for every row INSERTed. If they are random, then this could lead to I/O thrashing.
> Enable replication (and bin log)
Fine. Note that the longer you wait, the bigger the dump/reload will be to initialize the Slave. And the gotchas may bite you.
> Use temp tables / heap storage engine to copy in data from a single partition of Table1 and play with inserted records (do aggregations and store results into other tables)
Excellent! (I won't need to lecture on such.)
I found this to be especially efficient:
1. Load batches of rows into a MEMORY table in a _separate_ database.
2. Aggregate from that table into the Summary tables
3. Load from that table into the big Fact table.
Furthermore,
* Use RBR (Row Based Replication)
* binlog_ignore_database = the database with the MEMORY table.
This will cut down significantly on the replication traffic.
> I'm aware of costly JDBC inserts (no batch inserts, autocommit, etc.)
You _will_ need to improve on that in order to achieve 50M inserts/hour. 90% of the cost of a single-row INSERT is overhead that could be amortized if you batch the inserts.
> and "ON DUPLICATE KEY UPDATE"
If you can gather thousand(s) of rows at a time in the MEMORY table, then about 2 SQLs (an INSERT..SELECT..LEFT JOIN, and a UPDATE..JOIN) will
* replace the IODKU, and
* be 2 COMMITs (autocommit) instead of thousand(s)
(If you want the details on the 2 queries, I'll scratch my head and reconstruct them. Or maybe I will finish my blog on such.)
> just partition drops.
After 96 (or 36) hours?
> `col14` smallint(5) unsigned DEFAULT '0',
Two more lectures averted -- SMALLINT (and other sizes), and UNSIGNED.
What about "NOT NULL"?
> ### my.cnf
(SHOW VARIABLES is better. Possibly not necessary at the moment. But if you would like tuning advice, provide SHOW VARIABLES and SHOW GLOBAL STATUS.)
> query_cache_size=8M
Hardly worth having. And, since you have a high ingestion rate, you probably have a high QC purge rate. Recommend
query_cache_size=0
query_cache_type=OFF
> lower_case_table_names=1
Windows?
> table_cache=1024
-->
table_open_cache=5000
Note that each partition is effectively a "table".
> innodb_flush_log_at_trx_commit=0
Aha, you already found that one. Recommend 2 over 0; hardly any slower, possibly more secure.
> insert into Table1(col1,col2,..., col19) values (val1, val2,..., val19) ON DUPLICATE KEY UPDATE col1 = col1
Ouch. IODKU needs a UNIQUE (or PRIMARY) key in order to check for "DUPLICATE KEY"!
Did you think about INSERT IGNORE?
After you upgrade, be sure to set
innodb_buffer_pool_instances = 8 -- or some value > 1
> Average record size 209B
> 800 million of records in the table (number of partitions: 36)
> 64GB RAM,
> innodb_buffer_pool_size=32768M
> Buffer pool hit rate 1000 / 1000
--> 167 GB for the table. (Plus indexes?) However,... Before the table gets to 32GB (re buffer_pool), you _may_ start being I/O bound. This is where the randomness of the indexes, and other issues, comes into play.
The hit rate suggests that you have not hit the problem _yet_.
Oh, one more question -- Are the records inserted in roughly timestamp order? That is, is the 'latest' partition hot while the others are cold? Another thing to note: in 5.2 (I think), INSERT does no PARTITION pruning. Another reason to upgrade. (No, I don't know for a fact that 10.0's INSERT will prune.)
If all the activity is in one partition, then the "working set" is only 1.7GB (167GB/96), which is much less than 32GB. This implies that even a 'random' index won't lead to thrashing.
Too much information? Probably not. And I love expounding on scaling/performance of MySQL.
Can any of those VARCHARs be normalized in order to shrink the disk footprint? The normalization is a couple of SQLs while the data is still in the MEMORY table -- very cheap and efficient.