MySQL Forums :: Partitioning :: large InnoDB table partitioning without explicit PK


Advanced Search

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
large InnoDB table partitioning without explicit PK 2456 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1390 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1220 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 973 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 992 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 877 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 976 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 945 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 989 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1034 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 927 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1152 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1029 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1057 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1043 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1094 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1177 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1113 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 953 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 919 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 911 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 1000 Miko M 12/01/2014 11:16AM


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.