Tuning MySQL on GROUP REPLICATION

Question

I'm close to switch from old version of MySQL 5.6 ( master / slave configuration ) to MySQL 8 ( 3 nodes on multi-master configuration ) in GROUP REPLICATION.

The tables are 99% InnoDB.

The webfarm just copied the previous InnoDB configuration variables to these new machines. I was wondering how to improve performances, each machine has the below configuration:

1) Cpu:    
            12 core (Intel Xeon Processor (Skylake, IBRS))

2) RAM:        
            total        used       free           shared   buff/cache   available

Mem:        49456252     5164100    11487392       18972    32804760     43676276
Swap:        1998844        6924     1991920

3) Disk:
            Filesystem             Size  Used Avail Use% Mounted on
            /dev/sda2               54G  1.2G   50G   3% /
            /dev/mapper/vg0-mysql 1004G  414G  590G  42% /var/lib/mysql

All my databases are 250GB of datas.

InnnoDB variables:

*************************** 1. row ***************************
Variable_name: innodb_adaptive_flushing
        Value: ON
*************************** 2. row ***************************
Variable_name: innodb_adaptive_flushing_lwm
        Value: 10
*************************** 3. row ***************************
Variable_name: innodb_adaptive_hash_index
        Value: ON
*************************** 4. row ***************************
Variable_name: innodb_adaptive_hash_index_parts
        Value: 8
*************************** 5. row ***************************
Variable_name: innodb_adaptive_max_sleep_delay
        Value: 150000
*************************** 6. row ***************************
Variable_name: innodb_api_bk_commit_interval
        Value: 5
*************************** 7. row ***************************
Variable_name: innodb_api_disable_rowlock
        Value: OFF
*************************** 8. row ***************************
Variable_name: innodb_api_enable_binlog
        Value: OFF
*************************** 9. row ***************************
Variable_name: innodb_api_enable_mdl
        Value: OFF
*************************** 10. row ***************************
Variable_name: innodb_api_trx_level
        Value: 0
*************************** 11. row ***************************
Variable_name: innodb_autoextend_increment
        Value: 64
*************************** 12. row ***************************
Variable_name: innodb_autoinc_lock_mode
        Value: 2
*************************** 13. row ***************************
Variable_name: innodb_buffer_pool_chunk_size
        Value: 134217728
*************************** 14. row ***************************
Variable_name: innodb_buffer_pool_dump_at_shutdown
        Value: ON
*************************** 15. row ***************************
Variable_name: innodb_buffer_pool_dump_now
        Value: OFF
*************************** 16. row ***************************
Variable_name: innodb_buffer_pool_dump_pct
        Value: 25
*************************** 17. row ***************************
Variable_name: innodb_buffer_pool_filename
        Value: ib_buffer_pool
*************************** 18. row ***************************
Variable_name: innodb_buffer_pool_in_core_file
        Value: ON
*************************** 19. row ***************************
Variable_name: innodb_buffer_pool_instances
        Value: 8
*************************** 20. row ***************************
Variable_name: innodb_buffer_pool_load_abort
        Value: OFF
*************************** 21. row ***************************
Variable_name: innodb_buffer_pool_load_at_startup
        Value: ON
*************************** 22. row ***************************
Variable_name: innodb_buffer_pool_load_now
        Value: OFF
*************************** 23. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 34359738368
*************************** 24. row ***************************
Variable_name: innodb_change_buffer_max_size
        Value: 25
*************************** 25. row ***************************
Variable_name: innodb_change_buffering
        Value: all
*************************** 26. row ***************************
Variable_name: innodb_checksum_algorithm
        Value: crc32
*************************** 27. row ***************************
Variable_name: innodb_cmp_per_index_enabled
        Value: OFF
*************************** 28. row ***************************
Variable_name: innodb_commit_concurrency
        Value: 0
*************************** 29. row ***************************
Variable_name: innodb_compression_failure_threshold_pct
        Value: 5
*************************** 30. row ***************************
Variable_name: innodb_compression_level
        Value: 6
*************************** 31. row ***************************
Variable_name: innodb_compression_pad_pct_max
        Value: 50
*************************** 32. row ***************************
Variable_name: innodb_concurrency_tickets
        Value: 5000
*************************** 33. row ***************************
Variable_name: innodb_data_file_path
        Value: ibdata1:12M:autoextend
*************************** 34. row ***************************
Variable_name: innodb_data_home_dir
        Value:
*************************** 35. row ***************************
Variable_name: innodb_deadlock_detect
        Value: ON
*************************** 36. row ***************************
Variable_name: innodb_dedicated_server
        Value: OFF
*************************** 37. row ***************************
Variable_name: innodb_default_row_format
        Value: dynamic
*************************** 38. row ***************************
Variable_name: innodb_directories
        Value:
*************************** 39. row ***************************
Variable_name: innodb_disable_sort_file_cache
        Value: OFF
*************************** 40. row ***************************
Variable_name: innodb_doublewrite
        Value: ON
*************************** 41. row ***************************
Variable_name: innodb_fast_shutdown
        Value: 1
*************************** 42. row ***************************
Variable_name: innodb_file_per_table
        Value: ON
*************************** 43. row ***************************
Variable_name: innodb_fill_factor
        Value: 100
*************************** 44. row ***************************
Variable_name: innodb_flush_log_at_timeout
        Value: 1
*************************** 45. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
        Value: 1
*************************** 46. row ***************************
Variable_name: innodb_flush_method
        Value: O_DIRECT
*************************** 47. row ***************************
Variable_name: innodb_flush_neighbors
        Value: 0
*************************** 48. row ***************************
Variable_name: innodb_flush_sync
        Value: ON
*************************** 49. row ***************************
Variable_name: innodb_flushing_avg_loops
        Value: 30
*************************** 50. row ***************************
Variable_name: innodb_force_load_corrupted
        Value: OFF
*************************** 51. row ***************************
Variable_name: innodb_force_recovery
        Value: 0
*************************** 52. row ***************************
Variable_name: innodb_fsync_threshold
        Value: 0
*************************** 53. row ***************************
Variable_name: innodb_ft_aux_table
        Value:
*************************** 54. row ***************************
Variable_name: innodb_ft_cache_size
        Value: 8000000
*************************** 55. row ***************************
Variable_name: innodb_ft_enable_diag_print
        Value: OFF
*************************** 56. row ***************************
Variable_name: innodb_ft_enable_stopword
        Value: ON
*************************** 57. row ***************************
Variable_name: innodb_ft_max_token_size
        Value: 84
*************************** 58. row ***************************
Variable_name: innodb_ft_min_token_size
        Value: 3
*************************** 59. row ***************************
Variable_name: innodb_ft_num_word_optimize
        Value: 2000
*************************** 60. row ***************************
Variable_name: innodb_ft_result_cache_limit
        Value: 2000000000
*************************** 61. row ***************************
Variable_name: innodb_ft_server_stopword_table
        Value:
*************************** 62. row ***************************
Variable_name: innodb_ft_sort_pll_degree
        Value: 2
*************************** 63. row ***************************
Variable_name: innodb_ft_total_cache_size
        Value: 640000000
*************************** 64. row ***************************
Variable_name: innodb_ft_user_stopword_table
        Value:
*************************** 65. row ***************************
Variable_name: innodb_io_capacity
        Value: 200
*************************** 66. row ***************************
Variable_name: innodb_io_capacity_max
        Value: 2000
*************************** 67. row ***************************
Variable_name: innodb_lock_wait_timeout
        Value: 120
*************************** 68. row ***************************
Variable_name: innodb_log_buffer_size
        Value: 134217728
*************************** 69. row ***************************
Variable_name: innodb_log_checksums
        Value: ON
*************************** 70. row ***************************
Variable_name: innodb_log_compressed_pages
        Value: ON
*************************** 71. row ***************************
Variable_name: innodb_log_file_size
        Value: 268435456
*************************** 72. row ***************************
Variable_name: innodb_log_files_in_group
        Value: 2
*************************** 73. row ***************************
Variable_name: innodb_log_group_home_dir
        Value: ./
*************************** 74. row ***************************
Variable_name: innodb_log_spin_cpu_abs_lwm
        Value: 80
*************************** 75. row ***************************
Variable_name: innodb_log_spin_cpu_pct_hwm
        Value: 50
*************************** 76. row ***************************
Variable_name: innodb_log_wait_for_flush_spin_hwm
        Value: 400
*************************** 77. row ***************************
Variable_name: innodb_log_write_ahead_size
        Value: 8192
*************************** 78. row ***************************
Variable_name: innodb_lru_scan_depth
        Value: 1024
*************************** 79. row ***************************
Variable_name: innodb_max_dirty_pages_pct
        Value: 90.000000
*************************** 80. row ***************************
Variable_name: innodb_max_dirty_pages_pct_lwm
        Value: 10.000000
*************************** 81. row ***************************
Variable_name: innodb_max_purge_lag
        Value: 0
*************************** 82. row ***************************
Variable_name: innodb_max_purge_lag_delay
        Value: 0
*************************** 83. row ***************************
Variable_name: innodb_max_undo_log_size
        Value: 1073741824
*************************** 84. row ***************************
Variable_name: innodb_monitor_disable
        Value:
*************************** 85. row ***************************
Variable_name: innodb_monitor_enable
        Value:
*************************** 86. row ***************************
Variable_name: innodb_monitor_reset
        Value:
*************************** 87. row ***************************
Variable_name: innodb_monitor_reset_all
        Value:
*************************** 88. row ***************************
Variable_name: innodb_numa_interleave
        Value: OFF
*************************** 89. row ***************************
Variable_name: innodb_old_blocks_pct
        Value: 37
*************************** 90. row ***************************
Variable_name: innodb_old_blocks_time
        Value: 1000
*************************** 91. row ***************************
Variable_name: innodb_online_alter_log_max_size
        Value: 134217728
*************************** 92. row ***************************
Variable_name: innodb_open_files
        Value: 3459
*************************** 93. row ***************************
Variable_name: innodb_optimize_fulltext_only
        Value: OFF
*************************** 94. row ***************************
Variable_name: innodb_page_cleaners
        Value: 4
*************************** 95. row ***************************
Variable_name: innodb_page_size
        Value: 16384
*************************** 96. row ***************************
Variable_name: innodb_parallel_read_threads
        Value: 4
*************************** 97. row ***************************
Variable_name: innodb_print_all_deadlocks
        Value: OFF
*************************** 98. row ***************************
Variable_name: innodb_print_ddl_logs
        Value: OFF
*************************** 99. row ***************************
Variable_name: innodb_purge_batch_size
        Value: 300
*************************** 100. row ***************************
Variable_name: innodb_purge_rseg_truncate_frequency
        Value: 128
*************************** 101. row ***************************
Variable_name: innodb_purge_threads
        Value: 4
*************************** 102. row ***************************
Variable_name: innodb_random_read_ahead
        Value: OFF
*************************** 103. row ***************************
Variable_name: innodb_read_ahead_threshold
        Value: 56
*************************** 104. row ***************************
Variable_name: innodb_read_io_threads
        Value: 4
*************************** 105. row ***************************
Variable_name: innodb_read_only
        Value: OFF
*************************** 106. row ***************************
Variable_name: innodb_redo_log_archive_dirs
        Value:
*************************** 107. row ***************************
Variable_name: innodb_redo_log_encrypt
        Value: OFF
*************************** 108. row ***************************
Variable_name: innodb_replication_delay
        Value: 0
*************************** 109. row ***************************
Variable_name: innodb_rollback_on_timeout
        Value: OFF
*************************** 110. row ***************************
Variable_name: innodb_rollback_segments
        Value: 128
*************************** 111. row ***************************
Variable_name: innodb_sort_buffer_size
        Value: 1048576
*************************** 112. row ***************************
Variable_name: innodb_spin_wait_delay
        Value: 6
*************************** 113. row ***************************
Variable_name: innodb_spin_wait_pause_multiplier
        Value: 50
*************************** 114. row ***************************
Variable_name: innodb_stats_auto_recalc
        Value: ON
*************************** 115. row ***************************
Variable_name: innodb_stats_include_delete_marked
        Value: OFF
*************************** 116. row ***************************
Variable_name: innodb_stats_method
        Value: nulls_equal
*************************** 117. row ***************************
Variable_name: innodb_stats_on_metadata
        Value: OFF
*************************** 118. row ***************************
Variable_name: innodb_stats_persistent
        Value: ON
*************************** 119. row ***************************
Variable_name: innodb_stats_persistent_sample_pages
        Value: 20
*************************** 120. row ***************************
Variable_name: innodb_stats_transient_sample_pages
        Value: 8
*************************** 121. row ***************************
Variable_name: innodb_status_output
        Value: OFF
*************************** 122. row ***************************
Variable_name: innodb_status_output_locks
        Value: OFF
*************************** 123. row ***************************
Variable_name: innodb_strict_mode
        Value: ON
*************************** 124. row ***************************
Variable_name: innodb_sync_array_size
        Value: 1
*************************** 125. row ***************************
Variable_name: innodb_sync_spin_loops
        Value: 30
*************************** 126. row ***************************
Variable_name: innodb_table_locks
        Value: ON
*************************** 127. row ***************************
Variable_name: innodb_temp_data_file_path
        Value: ibtmp1:12M:autoextend
*************************** 128. row ***************************
Variable_name: innodb_temp_tablespaces_dir
        Value: ./#innodb_temp/
*************************** 129. row ***************************
Variable_name: innodb_thread_concurrency
        Value: 12
*************************** 130. row ***************************
Variable_name: innodb_thread_sleep_delay
        Value: 0
*************************** 131. row ***************************
Variable_name: innodb_tmpdir
        Value:
*************************** 132. row ***************************
Variable_name: innodb_undo_directory
        Value: ./
*************************** 133. row ***************************
Variable_name: innodb_undo_log_encrypt
        Value: OFF
*************************** 134. row ***************************
Variable_name: innodb_undo_log_truncate
        Value: ON
*************************** 135. row ***************************
Variable_name: innodb_undo_tablespaces
        Value: 2
*************************** 136. row ***************************
Variable_name: innodb_use_native_aio
        Value: ON
*************************** 137. row ***************************
Variable_name: innodb_version
        Value: 8.0.17
*************************** 138. row ***************************
Variable_name: innodb_write_io_threads
        Value: 4
*************************** 139. row **************************
Variable_name: max_connections
        Value: 3072
*************************** 140. row ***************************
Variable_name: max_user_connections
        Value: 3072

I read a lot of articles about the size of buffer pool ( rule of 80% ), but i'm still thinking if there are other variables to increase or not.

In the actual master server i've the following statistics data:

In addition i tried to run the mysqltuner perl script, these are the suggestions:

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    table_definition_cache(2000) > 464470 or -1 (autosizing if supported)
    innodb_buffer_pool_size (>= 241.7G) if possible.
    innodb_log_file_size should be (=3G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=24)

The line "innodb_buffer_pool_size (>= 241.7G) if possible." shocked me a bit, how is possibile to have an amount of RAM AND buffer pool so huge?

I understood it's related to my datas size ( 250GB ) i mention before but is not suggestable such a big amount of RAM? Feel free to ask other details and thanks for your help!

@danblack

Below the mysql config file of one of the GROUP REPLICATION machines, these machines are not used by my customers right now, i'll switch to them when all is ready and properly configured.

https://pastebin.com/dS1AeM2R

This is the full mysqltuner report on GR machine: https://pastebin.com/XEFbpGUV

@Wilson Hauck I don't see an SSD or NVMEE as devices:

 pvdisplay
  --- Physical volume ---
  PV Name               /dev/sdc
  VG Name               vg0
  PV Size               <651.93 GiB / not usable 4.00 MiB
  Allocatable           yes
  PE Size               4.00 MiB
  Total PE              166893
  Free PE               493
  Allocated PE          166400
  PV UUID               4Xco4e-Es5d-LcZ2-FHxt-8PYw-B1E6-h9nwP8

cat /sys/block/sdc/queue/rotational
1

These are the output of one machine where i'in going to switch, i repeat that these machines are still not used by the application:

UPTIME 25 hours:

B) https://pastebin.com/Uqtre6BS

C) https://pastebin.com/3HfWdCTR

D) https://pastebin.com/EazB2XWZ

 ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 193064
max locked memory       (kbytes, -l) 65536
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 193064
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

In addition to these information i give you the output of the slave machine used by now from my customers:

UPTIME: 26 days

MySQL config file:

https://pastebin.com/EztvxgYX

mysqltuning script + SHOW GLOBAL STATUS + SHOW GLOBAL VARIABLES + SHOW FULL PROCESSLIST ( i put all here, i fineshed the link available for my reputation)

https://pastebin.com/5rq8sLF1

Disk type:

pvdisplay
--- Physical volume ---
PV Name               /dev/vdb1
VG Name               vg0
PV Size               912.54 GiB / not usable 2.00 MiB
Allocatable           yes
PE Size               4.00 MiB
Total PE              233611
Free PE               5259
Allocated PE          228352
PV UUID               G8aSfO-Ktbg-UcfP-4yoL-jjH9-qjxe-OUN9ni

cat /sys/block/vdb/queue/rotational
1

 ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 459880
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 459880
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


iostat -xm 5 3
Linux 3.2.0-4-amd64  02/24/20        _x86_64_        (12 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
    6.95    0.00    0.65    1.20    0.01   91.19

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.16    0.09    0.15     0.00     0.00    22.30     0.00    1.02    1.94    0.46   0.46   0.01
vdb               5.84    89.65  156.08  208.73     7.59     3.05    59.71     0.92    2.53    1.93    2.97   0.68  24.89
dm-0              0.00     0.00  161.92  269.31     7.59     3.05    50.51     1.21    2.81    2.52    2.98   0.58  24.96

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
    3.35    0.00    0.54    4.43    0.00   91.69

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.60    4.40    1.40     0.02     0.01     9.10     0.01    1.93    2.55    0.00   0.14   0.08
vdb               3.40    35.60  830.40   51.20    17.90     0.30    42.26     1.16    1.31    1.18    3.53   0.85  75.04
dm-0              0.00     0.00  833.80   74.60    17.90     0.30    41.01     1.54    1.69    1.53    3.47   0.83  75.20

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
    3.40    0.00    0.43    4.64    0.00   91.52

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.40    0.00    0.40     0.00     0.00    16.00     0.00    0.00    0.00    0.00   0.00   0.00
vdb               5.80    30.80  810.60   46.80    14.65     0.27    35.63     1.39    1.59    1.61    1.32   1.04  89.36
dm-0              0.00     0.00  816.20   67.20    14.67     0.27    34.63     2.02    2.24    2.34    1.04   1.01  89.36

UPDATE 2ND EDIT

• SLAVE SERVER

( SHOW GLOBAL STATUS; + SHOW GLOBAL VARIABLES; + SHOW FULL PROCESSLIST; )

https://pastebin.com/nTeSuCjZ

• STAGE SERVER

SHOW GLOBAL VARIABLES;

https://pastebin.com/n369zpdB

( SHOW GLOBAL STATUS; + SHOW FULL PROCESSLIST; )

https://pastebin.com/8rDtkubv

Answer 1

B)C)D) -- a very lightly loaded 8.0 server.

Update 2 -- a very busy Slave. Here is a critique:

Observations:

• Version: 5.6.26-log
• 48 GB of RAM
• Uptime = 27d 07:48:48
• You are not running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Increase table_open_cache from 2000 to 5000. And/or see if you have too many tables. Keep an eye on Table_open_cache_misses/Uptime Increase table_definition_cache.

A lot or rows are being inserted and deleted -- at about the same rate. If you are "replacing" big chunks of data, explain. There may be better ways of doing the task. Perhaps: http://mysql.rjweb.org/doc.php/deletebig#optimal_reload_of_a_table

Max_used_connections is quite high (507). This may imply a burst of activity together with a bunch of slow queries. Did you have a near-meltdown?

You do have a lot of slow queries. See this for how to get specific help: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

If you are using SSD drive(s), then innodb_io_capacity = 1000 may help.

Why are you using SHOW SLAVE STATUS 3.8 times per second? That may be a burden on the system. Various other SHOWs are executed abnormally frequently.

There seem to be some MyISAM tables. Can they be converted to InnoDB?

Performance_schema...lost -- These indicate that the tables were not configured big enough. And it probably indicates that you are not using the P_S. So, perhaps it should be turned off.

Turn the Query cache completely off -- change query_cache_size to 0. If you do want it on, don't set the size bigger than 50M.

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((1024M - 1.2 * 6797 * 1024)) / 49152M = 2.1% -- Percent of RAM wasted in key_buffer. -- Decrease key_buffer_size (now 1073741824).

( Key_blocks_used * 1024 / key_buffer_size ) = 6,797 * 1024 / 1024M = 0.65% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 1073741824) to avoid unnecessary memory usage.

( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (7204545 + 51732 + 265181067 + 156828295 + 12360935 + 156828295) / 2360928 = 253 /sec -- IOPs? -- If the hardware can handle it, set innodb_io_capacity (now 200) to about this value.

( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 7204545 + 51732 + 265181067 + 156828295 + 12360935 + 156828295 ) / 200 / 2360928 = 126.7% -- This may be a metric indicating what innodb_io_capacity is set reasonably. -- Increase innodb_io_capacity (now 200) if the hardware can handle it.

( Opened_tables ) = 295,211,003 / 2360928 = 125 /sec -- Frequency of opening Tables -- increase table_open_cache (now 2000)

( Opened_table_definitions ) = 245,012,524 / 2360928 = 103 /sec -- Frequency of opening .frm files -- Increase table_definition_cache (now 1400) and/or table_open_cache (now 2000).

( Table_open_cache_overflows ) = 295,192,212 / 2360928 = 125 /sec -- May need to increase table_open_cache (now 2000)

( Table_open_cache_misses ) = 295,208,827 / 2360928 = 125 /sec -- May need to increase table_open_cache (now 2000)

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 295,208,827 / (1552264289 + 295208827) = 16.0% -- Effectiveness of table_open_cache. -- Increase table_open_cache (now 2000) and check table_open_cache_instances (now 1).

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

( 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_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((236228471 + 156828295) ) / 2360928 = 166 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size (now 25769803776)?

( Innodb_os_log_written ) = 581,123,630,592 / 2360928 = 246142 /sec -- This is an indicator of how busy InnoDB is. -- Very idle or very busy InnoDB.

( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions. -- Adjust innodb_log_buffer_size (now 134217728).

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

( Innodb_log_writes ) = 72,150,946 / 2360928 = 31 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 2,360,928 / 60 * 256M / 581123630592 = 18.2 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 268435456). (Cannot change in AWS.)

( Innodb_dblwr_writes ) = 12,360,935 / 2360928 = 5.2 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

( 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_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.

( max_connections ) = 3,072 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections (now 3072) is too high and various memory settings are high, you could run out of RAM.

( join_buffer_size * Max_used_connections ) = (32M * 507) / 49152M = 33.0% -- (A metric for pondering the size of join_buffer_size.) -- join_buffer_size (now 33554432) should probably be shrunk to avoid running out of RAM.

( 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 ) = 8 / 49152M = 0.02% -- Buffer for multi-row INSERTs and LOAD DATA -- Too big could threaten RAM size. Too small could hinder such operations.

( Created_tmp_tables ) = 173,730,482 / 2360928 = 74 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 60,901,114 / 2360928 = 26 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size (now 201326592) and max_heap_table_size (now 201326592). Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Questions ) = 60,901,114 / 1167645085 = 5.2% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.

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

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (33982982 + 45639342 + 27851942 + 6777983) / 97943382 = 1.17 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_full_join ) = 9,805,365 / 2360928 = 4.2 /sec -- joins without index -- Add suitable index(es) to tables used in JOINs.

( Select_scan ) = 251,823,622 / 2360928 = 106 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 251,823,622 / 1037614281 = 24.3% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( binlog_error_action ) = binlog_error_action = IGNORE_ERROR -- What to do if the binlog cannot be written. -- IGNORE_ERROR is the default for backwards compatibility, but ABORT_SERVER is recommended.

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( Slow_queries ) = 224,832,658 / 2360928 = 95 /sec -- Frequency (Slow queries per sec) -- Rework slow guys; improve indexes; watch disk space for slow log file

( Slow_queries / Questions ) = 224,832,658 / 1167645085 = 19.3% -- Frequency (% of all queries) -- Find slow queries; check indexes.

( 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 Slave reads.

( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Whether to include such in slowlog. -- This clutters the slowlog; turn it off so you can see the real slow queries. And decrease long_query_time (now 10) to catch most interesting queries.

( Max_used_connections ) = 507 -- High-water mark for connections -- Lots of inactive connections is OK; over 100 active connections is likely to be a problem. Max_used_connections (now 507) does not distinguish them; Threads_running (now 1) is instantaneous.

( Com_change_db ) = 43,570,240 / 2360928 = 18 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( Connections ) = 52,611,835 / 2360928 = 22 /sec -- Connections -- Increase wait_timeout (now 720); use pooling?

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 small:

(query_cache_size - Qcache_free_memory) / query_cache_size = 0.01%
1 - Qcache_free_memory / query_cache_size = 0.01%
delayed_insert_timeout = 8

Abnormally large:

Com_begin = 41 /sec
Com_create_view = 0.014 /HR
Com_rename_table = 0.1 /HR
Com_rollback_to_savepoint = 2 /HR
Com_show_binlogs = 11 /HR
Com_show_create_db = 2.2 /HR
Com_show_create_table = 0.2 /sec
Com_show_slave_status = 3.8 /sec
Com_show_table_status = 0.2 /sec
Com_show_triggers = 0.2 /sec
Handler_delete = 220 /sec
Handler_read_first = 99 /sec
Handler_read_key = 60959 /sec
Handler_read_last = 1 /sec
Handler_read_next = 467752 /sec
Handler_read_rnd = 11341 /sec
Handler_savepoint_rollback = 2 /HR
Handler_update = 3004 /sec
Innodb_buffer_pool_pages_misc = 134,346
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 8.5%
Innodb_buffer_pool_write_requests = 8241 /sec
Innodb_rows_deleted = 220 /sec
Key_read_requests = 9261 /sec
Key_write_requests = 1843 /sec
Performance_schema_digest_lost = 5.68e+8
Performance_schema_file_instances_lost = 5.05e+8
Performance_schema_table_instances_lost = 8.75e+7
Qcache_free_memory = 192.0MB
Select_full_range_join = 0.62 /sec
Select_full_range_join / Com_select = 0.14%
Sort_range = 61 /sec
Sort_rows = 9214 /sec
Sort_scan = 68 /sec
Threads_cached = 95
max_user_connections = 3,072
net_buffer_length = 262,144
optimizer_trace_offset = --1
performance_schema_max_cond_instances = 15,188
performance_schema_max_file_instances = 31,508
performance_schema_max_socket_instances = 6,164
performance_schema_max_thread_instances = 6,244
thread_concurrency = 12

Abnormal strings:

Slave_heartbeat_period = 1800
Slave_running = ON
ignore_db_dirs = lost+found
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
read_only = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN

Answer 2

Rate Per Second = RPS

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

max_connections=750  # from 3072 to conserve RAM - max_used_connections was 507 in 27 days
read_rnd_buffer_size=192K  # from 2M to reduce handler_read_rnd_next RPS 133,629
innodb_io_capacity=900  # from 200 to enable higher IOPS to your HDD
join_buffer_size=16M  # from ~ 32M will still support 2.6M rows to be joined
connect_timeout=20  # from 8 seconds for tolerance and to reduce aborted_connects RPHr 593
table_definition_cache=20000  # from 1400 to reduce opened_table_definitions RPS 104
key_cache_age_threshold=7200  # from 300 (seconds) to reduce key_reads RPS 3
log_queries_not_using_indexes=OFF  # from ON to conserve CPU cycles - since log is OFF.

This is just the beginning. View my profile, Network profile for contact info and free Utility Scripts to assist with performance tuning.

Observations: 48G RAM is not enough for the load, 19% of your queries take more than 10 seconds to complete, investment needs to be made in getting indexes where you need them to avoid table scans, com_analyze and com_optimize each indicate no activity in 27 days and more opportunities to improve performance.