MySQL Forums
Forum List  »  Partitioning

Re: large InnoDB table partitioning without explicit PK
Posted by: Rick James
Date: November 15, 2014 12:32AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: large InnoDB table partitioning without explicit PK
1293
November 15, 2014 12:32AM


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.