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


Advanced Search

Re: large InnoDB table partitioning without explicit PK
Posted by: Miko M ()
Date: November 16, 2014 07:39PM

> > 18k per sec
>
> Impressive. However, did you run the benchmark
> long enough to exhaust the write cache on the RAID
> controller?
[Miko] It was a 12 hour test, no PK, just two IDXs, the DB size was 700M rows, 250GB on a disk, utilisation: 72% %user, 16% idle (way too much for a regular usage).


> > Index 2: Timestamp as varchar2
> ('YYYY:MM:DD:HH24:MI:SS:MS')
>
> This has no advantage over Index1, does it?
[Miko] Well, theoretically there is no advantage. However some queries (spools in fact) use this index (Index2) in a way: select * from Table1 where col2 like '2014-11-16 1%';


> > 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?
[Miko] There are no leading spaces. You are right, my idea is to use this index in points queries.

> Cardinality is not the question -- "randomness"
> is. For example, hashes (MD5, SHA1, etc) are very
> random -- always jumping around in the index.
[Miko] Absolutely! At the moment there are 15-25% unique values (select distinct...) for one hour partition.

> > 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.
[Miko] You mean analyse/check/optimise/repair table, is it correct?

> > 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.
[Miko] There will be a "fresh" installation with empty DB from the very beginning.
But I still need to test performance degradation. This is my biggest headache I have to say.

> > 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.
[Miko] Yes, it is exactly. BEGIN... INSERT1...INSERT2...INSERTN...COMMIT

> > 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.)
[Miko] Sure, no problem. I will try to google a little bit.

> > > > 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] Interesting story, I read it a few months ago.
But as far as I understand it was a regular drive, neither a SAN nor an SSD.
And, as a matter of fact, the 2k inserts/sec was the maximum I could achieved from SATA 7.2k. Which is still a very good number!


> > [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] I strongly believe that with no PK/UK no table scans occur with IDOKU. That is the reason I could reach 18k inserts/sec with IDOKU and "regular" indexes but no PK/UK.
With (very, very long) PK, the maximum was 8k but it went down as the table was growing.

> > [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.)
[Miko] Well, hard to disagree now. Maybe in a few years (of real life experience in working with MySQL) ;-)

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