Re: large InnoDB table partitioning without explicit PK

[Miko M]

Many thanks for your hints again.

It seems that query_cache_size does not work the way it is supposed to. However I did not even try to check/analyse it since 8MB seems to be nothing with the all RAM available.

Regarding Select_scan / Com_select - (relatively high) number of full table scans, there was a select statement such as select count(*) from table1.... executed on a regular basis (from application) but I've finally managed to disabled it. Having checked the slow query log I think the problem with table scans has disappeared.

At the moment every ten minutes the most recent ten minute record set is spooled in to a file, compressed and transferred onto another system.
So these selects are almost the only (slow) queries reported into the slow query log.
Basically all the queries/aggregations are executed on another machine (Oracle DB).
My idea it to change it and perform aggregations on MySQL directly (either on a heap table / temp or on a slave or both).

Finally, there was one strange behaviour I observed during my testing. I think it is worth to be mentioned about.
With 10k+ inserts per sec, the volume of data that was spooled into a file was definitely much higher.
Once I decreased amount of available RAM, and extended the spool volume from 10 min to 1 hour (just for testing purposes), I quickly noticed that all available RAM was eaten up and MySQL started crashing.
I have not observed such behaviour with 10 min spools but it still made me think.

Regarding "thread_handling = one-thread-per-connection" - definitely I will check it during performance testing. Thanks for letting me know.