MySQL Forums
Forum List  »  Partitioning

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
> 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 -
>,622083 (Change
> DB to case-insensitive = problems)
> table name casefolding -
>,611283 (mysql
> and xampp)
> index -,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

Written By
Re: large InnoDB table partitioning without explicit PK
November 16, 2014 07:39PM

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.