> > 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) ;-)