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.
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:
mysqltuning script + SHOW GLOBAL STATUS + SHOW GLOBAL VARIABLES + SHOW FULL PROCESSLIST ( i put all here, i fineshed the link available for my reputation)
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; )
- STAGE SERVER
SHOW GLOBAL VARIABLES;
( SHOW GLOBAL STATUS; + SHOW FULL PROCESSLIST; )
Best Answer
Rate Per Second = RPS
Suggestions to consider for your SLAVE my.cnf [mysqld] section
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.