I am not a dedicated mysql admin but my friend and I have been working hard to fix the badly configured mysql files. We have reduced our number of pools to 2 from 20 and increased pool size to 90GB for a 124GB ram server. I've also enabled file_per_table. What is bugging me is the constant spikes in disk read/write speeds. Picture below is while the server is under decent load (file parsing into db) and it has been up with the updated config for about two days.
max_connections = 1000 max_connect_errors = 10 max_allowed_packet = 1G binlog_cache_size = 1M max_heap_table_size = 3072M table_open_cache = 20000 table_definition_cache = 20000 read_buffer_size = 64M read_rnd_buffer_size = 8M join_buffer_size = 64M sort_buffer_size = 64M key_buffer_size = 4M thread_cache_size = 8 query_cache_size=0 query_cache_limit = 128K ft_min_word_len = 4 memlock thread_stack = 256K transaction_isolation = READ-COMMITTED tmp_table_size = 3G tmpdir = /mysql-tmp group_concat_max_len = 33554432 ignore-db-dir=lost+found server-id = 1 secure-file-priv = "/tmp/" # *** INNODB Specific options *** innodb_buffer_pool_size = 92160M innodb_buffer_pool_instances=2 innodb_data_file_path=ibdata1:12M:autoextend innodb_file_per_table=ON innodb_data_home_dir= innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_log_buffer_size = 8M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_io_capacity = 30000 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_adaptive_hash_index=ON innodb_large_prefix innodb_autoinc_lock_mode = 0
available: 2 nodes (0-1) node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 node 0 size: 65490 MB node 0 free: 4880 MB node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 node 1 size: 65536 MB node 1 free: 9789 MB node distances: node 0 1 0: 10 20 1: 20 10
Harddisk info smartctl -a /dev/sda -d sat+megaraid,00
=== START OF INFORMATION SECTION === Model Family: Seagate Constellation ES (SATA 6Gb/s) Device Model: ST500NM0011 User Capacity: 500,107,862,016 bytes [500 GB] Sector Size: 512 bytes logical/physical Device is: In smartctl database [for details use: -P show] ATA Version is: 8 ATA Standard is: ATA-8-ACS revision 4 Local Time is: Mon Jun 19 10:45:43 2017 CDT SMART support is: Available - device has SMART capability. SMART support is: Enabled
We are altering an existing config file and I do not want to touch stuff that I do not know about. I am not a mysql admin. If you see a setting that makes no sense to you, please let me know. In addition to reducing the pool count to 2 from 20 and increasing the pool size to 90gb from 20gb, I've also set read/write thread counts to 16 each. I did not get a chance to restart the server after adding the thread counts (clients are on it all the time) but I've done the same number for my other servers and they all felt much better.
I don't know ... we are all just doing try and see method here but this inconsistent read/write speeds are making me think something is not right and I am not sure where to look.
I've written a script to go through the database and call
OPTIMIZE TABLE on every table. I do not think that was done before, ever... After I've done that, pool usage value jumped to %95+ on all servers but the efficiency value decreased.