MySQL Forums :: Partitioning :: large InnoDB table partitioning without explicit PK


Advanced Search

Re: large InnoDB table partitioning without explicit PK
Posted by: Rick James ()
Date: November 13, 2014 06:40PM

> 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.

Options: ReplyQuote


Subject Views Written By Posted
large InnoDB table partitioning without explicit PK 2455 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1388 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1220 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 973 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 992 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 875 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 976 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 943 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 989 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1033 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 927 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1152 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1029 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1055 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1042 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1093 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1176 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1112 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 953 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 919 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 911 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 999 Miko M 12/01/2014 11:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.