MySQL Forums
Forum List  »  Performance

Re: Help for checking server configuration
Posted by: Rick James
Date: August 22, 2015 02:59PM

Observations:

Version: 5.5.42-cll
0.75 GB of RAM -- Is this really how little RAM you have?
You are not running on Windows.
Running 64-bit version
It appears that you are running both MyISAM and InnoDB.
If you consider finishing conversion to InnoDB, read
http://mysql.rjweb.org/doc.php/myisam2innodb

Main issues:

-- You have 3 possible actions for Engines and RAM:
1. Since you are using both MyISAM and InnoDB, for now I recommend
innodb_buffer_pool_size = 200M
key_buffer_size = 64M (as you have it)
2. Since some of the STATUS values indicate that these should be larger, so the 'better' answer would be to increase RAM.
3. Alternatively, convert all user tables to InnoDB so you can use the buffer_pool and shrink the key_buffer.

-- Recommend decreasing both tmp_table_size and max_heap_table_size to to 4M.

-- Decrease query_cache_size to 25M. (The other 50M is better served for other caches.)

-- Find the "slow" queries; show them to us; we will help you change them so they don't use disk tmp tables, and otherwise run faster.

-- Stop using OPTIMIZE TABLE -- it costs a lot, and has virtually no benefit, especially at the frequency at which you are using it. In some cases once a month is warranted.

-- Recommend changing innodb_stats_on_metadata to OFF. This _may_ be a huge benefit. If you measure this change by itself, let me know.

Running out of RAM (and swapping) is much worse on performance than shrinking the various caches, etc.


Details, plus other issues:

( innodb_buffer_pool_size / _ram ) = 420M / 768M = 54.7% -- % of RAM used for InnoDB buffer_pool
-- For this small a RAM, 420M may be too large.

( Opened_files ) = 20,464,833 / 535867 = 38 /sec -- Frequency of opening Files
-- Yet Opened_tables is only 0.16/sec.
-- Increase table_open_cache? Decrease number of tables?
-- The readings are confusing, so I don't have a specific recommendation. Still 38/sec is bad.

( Innodb_row_lock_time_avg ) = 1,680 -- Avg time to lock a row (millisec)
-- (Unclear what to do to help this)

( max_connections ) = 60 -- Maximum number of connections (threads). Impacts various allocations.
-- Max_used_connections = 53, so max_connections is not excessively high
-- If max_connections is too high and various memory settings are high, you could run out of RAM.

( tmp_table_size ) = 256M
( max_heap_table_size / _ram ) = 16M / 768M = 2.1% -- 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.
-- Recommend decreasing both tmp_table_size and max_heap_table_size to to 4M.

( innodb_stats_on_metadata ) = ON -- Re-analyze table when touching stats.
-- ON is likely to slow down certain SHOWs and information_schema accesses. This is especially because of the next item:

( (Com_show_create_table + Com_show_fields) / Questions ) = (2910 + 4822234) / 103585998 = 4.7% -- Naughty framework -- spending a lot of effort rediscovering the schema.
( Com_check ) = 2.3/hour
-- Complain to the 3rd party vendor.

( Key_writes / Key_write_requests ) = 621,507 / 1019205 = 61.0% -- key_buffer effectiveness for writes
-- If had more RAM, I would recommend increasing key_buffer_size.
-- Alternatively, convert all user tables to InnoDB so you can use the buffer_pool and shrink the key_buffer.

( Created_tmp_disk_tables ) = 4,932,899 / 535867 = 9.2 /sec -- Frequency of creating _disk_ "temp" tables as part of complex SELECTs
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 4,932,899 / (4932899 + 5089085) = 49.2% -- Percent of temp tables that spilled to disk
-- Check the rules for temp tables being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM.
-- Find the "slow" queries; show them to us; we will help you change them so they don't use disk tmp tables, and otherwise run faster.

( tmp_table_size ) = 256M -- Limit on size of _MEMORY_ temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps 4M for your small RAM.

( Select_scan ) = 5,745,044 / 535867 = 11 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Com_optimize ) = 49/hour
-- OPTIMIZE TABLE is virtually useless, especially for InnoDB.

( Select_scan / Com_select ) = 5,745,044 / 20863794 = 27.5% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( log_slow_queries ) = OFF -- Whether to log slow queries. (Before 5.1.29, 5.6.1)
( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2 and turn on the slowlog

( Connections ) = 1,172,184 / 535867 = 2.2 /sec -- Connections
-- Increase wait_timeout; use pooling?

-- max_long_data_size is deprecated; why is it being set?

( Innodb_row_lock_time_avg ) = 1,680 -- Avg time to lock a row (millisec)
-- Increase innodb_log_buffer_size from 8M to 12M

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help for checking server configuration
922
August 22, 2015 02:59PM


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.