MySQL config uses 1G per thread - how do I reduce that?-CodePudding

So I often run into performance issues with the server I'm hosting a MySQL db on when my website sees a lot of incoming traffic.

I just saw this in the mysqltuner results:

Total buffers: 4.1G global   1.0G per thread (500 max threads)
[!!] Maximum reached memory usage: 188.4G (299.43% of installed RAM)
[!!] Maximum possible memory usage: 2002.3G (3182.59% of installed RAM)

How did I manage to reserve 1G per thread? And more importantly: how do I lower that? 1G seems insanely high.

total mysqltuner results: https://pastebin.com/s0rc42VJ

CodePudding user response：

That is not the real problem. Inadequate indexes and/or inadequately written queries are causing performance problems.

Still, here are some changes to make:

max_connections = 200
innodb_buffer_pool_size = 3G

Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 5.7.34-0ubuntu0.18.04.1-log
62.9 GB of RAM
Uptime = 1d 19:27:48
171 Queries/sec : 60.2 Questions/sec

The More Important Issues:

There is a lot more RAM than MySQL needs.

innodb_log_file_size is very small (looks like an very old default). The value does not seem to be a problem a the moment. However, if you were to add a 1MB BLOB column to some table it would not be be sufficient.

Details and other observations:

( innodb_buffer_pool_size ) = 4,096 / 67538360729.6 = 6.4% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 147,347 / 262112 = 56.2% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 4294967296) is bigger than necessary?

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( innodb_log_buffer_size / innodb_log_file_size ) = 16M / 16M = 100.0% -- Buffer is in RAM; file is on disk. -- The buffer_size should be smaller and/or the file_size should be larger.

( innodb_flush_method ) = innodb_flush_method = -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( character_set_server ) = character_set_server = latin1 -- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.

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

( bulk_insert_buffer_size ) = 8M / 67538360729.6 = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA -- Too big could threaten RAM size. Too small could hinder such operations.

( Handler_read_rnd_next / Com_select ) = 129,974,931,182 / 7494188 = 17,343 -- Avg rows scanned per SELECT. (approx) -- Consider raising read_buffer_size (now 131072)

( Com__biggest ) = Com__biggest = Com_stmt_prepare -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 7494188). If something else, then it may be a sloppy platform, or may be something else.

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( Max_used_connections / max_connections ) = 34 / 500 = 6.8% -- Peak % of connections -- Since several memory factors can expand based on max_connections (now 500), it is good not to have that setting too high.

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally large:

Com_stmt_close = 55 /sec
Com_stmt_execute = 55 /sec
Com_stmt_prepare = 55 /sec
Handler_read_rnd = 10182 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests   Innodb_buffer_pool_reads ) = 100.0%
Innodb_log_writes / Innodb_log_write_requests = 157.1%
Innodb_rows_deleted   Innodb_rows_inserted = 703 /sec
Innodb_rows_inserted = 703 /sec
Innodb_rows_read = 1040676 /sec
Ssl_accepts = 379,681
Ssl_finished_accepts = 379,681
Ssl_session_cache_misses = 379,428
Ssl_used_session_cache_entries = 85
max_user_connections = 500

Abnormal strings:

innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
require_secure_transport = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN

CodePudding user response：

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

read_rnd_buffer_size=64K  # from 256K to reduce handler_read_rnd_next RPS of 830,686
innodb_lru_scan_depth=100  # from 1024 to reduce 90% of CPU cycles used for function, every second.
connect_timeout=20  # from 10 seconds to reduce aborted_connects of 49 RPhr
net_buffer_length=96K  # from 16K to reduce packet sent count
innodb_write_io_threads=64  # from 4 because of your reads to writes ratio of about 30.

View profile for contact info and free downloadable Utility Scripts to assist with performance tuning.

Posting the last 400 lines of your slow query log would allow suggestions for appropriate index creation to reduce select_scan RPS of 6.

innodb_buffer_pool_size needs to consider the reported 1.9G of data you have today. 70% of RAM would be overallocation and unnecessary. 4G you will find serves you well.