Re: large InnoDB table partitioning without explicit PK

[Rick James]

Not much jumps out from the VARIABLES/STATUS:

( Open_tables / table_open_cache ) = 22 / 1024 = 2.1% -- Cache usage (open tables + tmp tables)
-- Optionally lower table_open_cache (not very important)

( 1 - Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 1 - 25285 / 1579244 = 98.4% -- Utility of read_ahead.
-- Turn off read-ahead (I don't have hard evidence backing this up.)

( innodb_rollback_on_timeout ) = OFF
-- Probably wise to have it ON, for data integrity.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

Various clues that the QC may as well be OFF:
( Qcache_free_memory / query_cache_size ) = 8,371,240 / 8M = 99.8% -- Pct Query Cache free
-- lower query_cache_size
Lowering it releases RAM for other uses, but since the free space will vary over time, this one reading may fool you.
( Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) ) = 2,631,224 / (5 + 2631416 + 2631224) = 50.0% -- Percent of SELECTs that were not cached in the QC.
-- QC is not very useful.
( Qcache_hits / Qcache_inserts ) = 5 / 115 = 0.0435 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.
( Qcache_hits / (Qcache_hits + Com_select) ) = 5 / (5 + 2631416) = 0.0% -- Hit ratio -- SELECTs that used QC
-- Consider turning off the query cache.
( Qcache_free_blocks / Qcache_total_blocks ) = 1 / 1 = 100.0% -- Fragmentation in Query Cache.
-- Various things.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (8M - 8371240) / 0 / 8192 = 0 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

( Select_scan / Com_select ) = 693,434 / 2631416 = 26.4% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries
***** Run pt_query_digest on the Slowlog.

( sort_buffer_size ) = 2,097,144 = 2MB -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok.
-- This may be eating into available RAM; recommend no more than 2M.
(I don't know if that bit of code has gotten into MariaDB yet.)

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (1881655938 + 0 + 0 + 0 + 0 + 15188) / 4556154 = 412 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives
412/sec -- Wow!
And that is nearly all that you are doing (414qps total)

( binlog_format ) = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)
Something to decide on when you set up replication.

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

Slow_queries (>10 seconds) in only 5.5/hour
I have a feeling you are mostly INSERTing now, and you haven't gotten to the big SELECTs??

thread_handling = one-thread-per-connection; for Oracle on Unix, pool-of-threads is preferred.
This is an area where Oracle and MariaDB diverged -- recommend checking it when you go to 10.0.

(Another ~200 checks came up "ok".)