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