MySQL Forums
Forum List  »  Performance

Re: table_cache
Posted by: Rick James
Date: August 17, 2015 09:30AM

Significant findings:

-- You are using MyISAM entirely; InnoDB is preferred.

-- Lots of queries causing full table scans, tmp tables, tmp tables spilling to disk, etc.
1. SET long_query_time=1
2. Turn on the slowlog
3. Wait a day
4. run pt-query-digest
5. Let's study the first few (worst) queries. Please provide the query, the EXPLAIN, and SHOW CREATE TABLE.

Details and other findings:

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (100M / 0.20 + 8M / 0.70) / 6144M = 8.3%
-- Most of available ram should be made available for caching.
-- Since you are using (mostly) MyISAM, set key_buffer_size to about 20% of available RAM.
-- http://mysql.rjweb.org/doc.php/memory

( Key_blocks_used * 1024 / key_buffer_size ) = 35,791 * 1024 / 100M = 35.0% -- "High-water-mark" percent of key_buffer used
-- Lower key_buffer_size to avoid unnecessary memory usage.

-- The above two items contradict each other. I conclude that you have very little data, or at least very little in the way of INDEXes. If you add a number of INDEXes, revisit these metrics.

( table_open_cache ) = 95,000 -- Number of table descriptors to cache
( Open_tables / table_open_cache ) = 264 / 95000 = 0.28% -- Cache usage (open tables + tmp tables)
-- Lower table_open_cache; several hundred is usually good.

( max_heap_table_size / _ram ) = 100M / 6144M = 1.6% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease max_heap_table_size to, say, 1% of ram.

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

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 5,565 / (5565 + 21361) = 20.7% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( tmp_table_size ) = 100M -- Limit on size of temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Select_scan ) = 134,283 / 116511 = 1.2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 134,283 / 2426388 = 5.5% -- % of selects doing full table scan.
-- Add indexes / optimize queries

( Connections ) = 1,364,723 / 116511 = 12 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 4 -- How many extra processes to keep around (Not relevant when using thread pooling)
( Threads_created / Connections ) = 21,909 / 1364723 = 1.6% -- Rapidity of process creation
-- Increase thread_cache_size to, say, 15.

Options: ReplyQuote


Subject
Views
Written By
Posted
3474
August 13, 2015 01:19AM
1038
August 15, 2015 04:19PM
1134
August 16, 2015 01:24PM
Re: table_cache
1285
August 17, 2015 09:30AM


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.