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 15, 2014 12:05AM

> 18k per sec

Impressive. However, did you run the benchmark long enough to exhaust the write cache on the RAID controller?

> Index 2: Timestamp as varchar2 ('YYYY:MM:DD:HH24:MI:SS:MS')

This has no advantage over Index1, does it?

> Index 3 (planned): BIGINT as varchar2 - High cardinality; 10M+ of possible different values

"varchar2"? What do you do about leading spaces? Or do you intend to do only point queries?

Cardinality is not the question -- "randomness" is. For example, hashes (MD5, SHA1, etc) are very random -- always jumping around in the index.

> In fact I thought about such AUTO_INCREMENT artificial key, but saw no real reason to do this (definitely it is my lack of experience in that area).

The reason is subtle -- If you ever need to do maintenance that requires walking through the table, a PRIMARY KEY (A_I or other) comes in very handy.

> I decided to disable the binlog, now I have to enable it back.

But the binlog is how you get the data to the Slave. With it off, you are stuck with dumping the entire table or doing some kind of hack.

> This "JDBC stream" comes from 3rd party

Grrrr... I mutter about 3rd party software. And I recently added a blog on 5.7's "Query Rewrite" that will salvage performace for some such software. But I doubt if QR could help your situation.

Ah, could your perhaps put a BEGIN...COMMIT around a bunch of INSERTs? Or is it totally 'closed'? BEGIN...COMMIT overrides autocommit.

> Could you please expand on that a little bit more or point me to some further reading in your blog / on the Internet?

OK, but not tonight. (And I don't remember reading anyone else's posting on this.)

> > > lower_case_table_names=1
> [Miko] No, it is Linux.

I hope you don't have any trouble with that.
lower_case_table_names - http://forums.mysql.com/read.php?22,622083 (Change DB to case-insensitive = problems)
table name casefolding - http://forums.mysql.com/read.php?10,611283 (mysql and xampp)
index - http://forums.mysql.com/read.php?24,604730 (Slow insert rate in mysql innodb)
(and possibly others)

> [Miko] So basically - provided that there are neither PK nor UKs such syntax does not result in performance degradation with inserts, is it correct?

I _thought_ that it IODKU would do a table scan if you did not have a PK or UK. The fact that you gave no clue of that major performance problem makes me wonder.

> [Miko] Ok. Thanks. Need to read a little more to get better understanding of configuration details.

Certainly. I try to point out the ones I think are important. I expect you to read up on them and agree (or disagree) with me. Since there are hundreds of settings, it is a daunting task to study them all and make intelligent decisions for a given system. (And some settings have misleading names. thread_cache_size is one of the most confusing.)

Options: ReplyQuote


Subject Views Written By Posted
large InnoDB table partitioning without explicit PK 2492 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1410 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1243 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 989 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 1012 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 893 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 996 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 962 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 1005 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1053 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 946 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1174 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1044 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1073 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1060 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1113 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1203 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1129 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 966 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 938 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 925 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 1015 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.