MySQL Forums
Forum List  »  Partitioning

Re: large InnoDB table partitioning without explicit PK
Posted by: Miko M
Date: November 16, 2014 08:31PM

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.

Options: ReplyQuote

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

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.