1

Server Specs

RAM     CPU         STORAGE     
80 GB   20 Cores    1536 GB SSD 

TRANSFER      NETWORK IN    NETWORK OUT
20 TB         40 Gbps       8000 Mbps

This is the results from show status:

show status:

Aborted_clients 2   
Aborted_connects    0   
Access_denied_errors    0   
Acl_column_grants   0   
Acl_database_grants 0   
Acl_function_grants 0   
Acl_procedure_grants    0   
Acl_proxy_users 2   
Acl_role_grants 0   
Acl_roles   0   
Acl_table_grants    0   
Acl_users   9   
Aria_pagecache_blocks_not_flushed   0   
Aria_pagecache_blocks_unused    15706   
Aria_pagecache_blocks_used  2   
Aria_pagecache_read_requests    13  
Aria_pagecache_reads    4   
Aria_pagecache_write_requests   6   
Aria_pagecache_writes   0   
Aria_transaction_log_syncs  0   
Binlog_commits  5624    
Binlog_group_commits    5624    
Binlog_group_commit_trigger_count   0   
Binlog_group_commit_trigger_lock_wait   0   
Binlog_group_commit_trigger_timeout 0   
Binlog_snapshot_file    mariadb-bin.000017  
Binlog_snapshot_position    2460550 
Binlog_bytes_written    0   
Binlog_cache_disk_use   0   
Binlog_cache_use    5628    
Binlog_stmt_cache_disk_use  0   
Binlog_stmt_cache_use   0   
Busy_time   0.000000    
Bytes_received  510 
Bytes_sent  1262    
Com_admin_commands  0   
Com_alter_db    0   
Com_alter_db_upgrade    0   
Com_alter_event 0   
Com_alter_function  0   
Com_alter_procedure 0   
Com_alter_server    0   
Com_alter_table 0   
Com_alter_tablespace    0   
Com_analyze 0   
Com_assign_to_keycache  0   
Com_begin   0   
Com_binlog  0   
Com_call_procedure  0   
Com_change_db   1   
Com_change_master   0   
Com_check   0   
Com_checksum    0   
Com_commit  0   
Com_compound_sql    0   
Com_create_db   0   
Com_create_event    0   
Com_create_function 0   
Com_create_index    0   
Com_create_procedure    0   
Com_create_role 0   
Com_create_server   0   
Com_create_table    0   
Com_create_temporary_table  0   
Com_create_trigger  0   
Com_create_udf  0   
Com_create_user 0   
Com_create_view 0   
Com_dealloc_sql 0   
Com_delete  0   
Com_delete_multi    0   
Com_do  0   
Com_drop_db 0   
Com_drop_event  0   
Com_drop_function   0   
Com_drop_index  0   
Com_drop_procedure  0   
Com_drop_role   0   
Com_drop_server 0   
Com_drop_table  0   
Com_drop_temporary_table    0   
Com_drop_trigger    0   
Com_drop_user   0   
Com_drop_view   0   
Com_empty_query 0   
Com_execute_sql 0   
Com_flush   0   
Com_get_diagnostics 0   
Com_grant   0   
Com_grant_role  0   
Com_ha_close    0   
Com_ha_open 0   
Com_ha_read 0   
Com_help    0   
Com_insert  0   
Com_insert_select   0   
Com_install_plugin  0   
Com_kill    0   
Com_load    0   
Com_lock_tables 0   

Com_optimize    0   
Com_preload_keys    0   
Com_prepare_sql 0   
Com_purge   0   
Com_purge_before_date   0   
Com_release_savepoint   0   
Com_rename_table    0   
Com_rename_user 0   
Com_repair  0   
Com_replace 0   
Com_replace_select  0   
Com_reset   0   
Com_resignal    0   
Com_revoke  0   
Com_revoke_all  0   
Com_revoke_role 0   
Com_rollback    0   
Com_rollback_to_savepoint   0   
Com_savepoint   0   
Com_select  2   
Com_set_option  3   
Com_show_authors    0   
Com_show_binlog_events  0   
Com_show_binlogs    0   
Com_show_charsets   0   
Com_show_collations 0   
Com_show_contributors   0   
Com_show_create_db  0   
Com_show_create_event   0   
Com_show_create_func    0   
Com_show_create_proc    0   
Com_show_create_table   0   
Com_show_create_trigger 0   
Com_show_databases  0   
Com_show_engine_logs    0   
Com_show_engine_mutex   0   
Com_show_engine_status  0   
Com_show_errors 0   
Com_show_events 0   
Com_show_explain    0   
Com_show_fields 0   
Com_show_function_status    0   
Com_show_generic    0   
Com_show_grants 0   
Com_show_keys   0   
Com_show_master_status  0   
Com_show_open_tables    0   
Com_show_plugins    0   
Com_show_privileges 0   
Com_show_procedure_status   0   
Com_show_processlist    0   
Com_show_profile    0   
Com_show_profiles   0   
Com_show_relaylog_events    0   
Com_show_slave_hosts    0   
Com_show_slave_status   0   
Com_show_status 1   
Com_show_storage_engines    0   
Com_show_table_status   0   
Com_show_tables 0   
Com_show_triggers   0   
Com_show_variables  3   
Com_show_warnings   0   
Com_shutdown    0   
Com_signal  0   
Com_start_all_slaves    0   
Com_start_slave 0   
Com_stmt_close  0   
Com_stmt_execute    0   
Com_stmt_fetch  0   
Com_stmt_prepare    0   
Com_stmt_reprepare  0   
Com_stmt_reset  0   
Com_stmt_send_long_data 0   
Com_stop_all_slaves 0   
Com_stop_slave  0   
Com_truncate    0   
Com_uninstall_plugin    0   
Com_unlock_tables   0   
Com_update  0   
Com_update_multi    0   
Com_xa_commit   0   
Com_xa_end  0   
Com_xa_prepare  0   
Com_xa_recover  0   
Com_xa_rollback 0   
Com_xa_start    0   
Compression OFF 
Connection_errors_accept    0   
Connection_errors_internal  0   
Connection_errors_max_connections   0   
Connection_errors_peer_address  0   
Connection_errors_select    0   
Connection_errors_tcpwrap   0   
Connections 11180   
Cpu_time    0.000000    
Created_tmp_disk_tables 0   
Created_tmp_files   6   
Created_tmp_tables  4   
Delayed_errors  0   

Delayed_insert_threads  0   
Delayed_writes  0   
Delete_scan 0   
Empty_queries   0   
Executed_events 0   
Executed_triggers   0   
Feature_delay_key_write 0   
Feature_dynamic_columns 0   
Feature_fulltext    0   
Feature_gis 0   
Feature_locale  1   
Feature_subquery    0   
Feature_timezone    0   
Feature_trigger 0   
Feature_xml 0   
Flush_commands  1   
Handler_commit  0   
Handler_delete  0   
Handler_discover    0   
Handler_external_lock   0   
Handler_icp_attempts    0   
Handler_icp_match   0   
Handler_mrr_init    0   
Handler_mrr_key_refills 0   
Handler_mrr_rowid_refills   0   
Handler_prepare 0   
Handler_read_first  0   
Handler_read_key    0   
Handler_read_last   0   
Handler_read_next   0   
Handler_read_prev   0   
Handler_read_retry  0   
Handler_read_rnd    0   
Handler_read_rnd_deleted    0   
Handler_read_rnd_next   11  
Handler_rollback    0   
Handler_savepoint   0   
Handler_savepoint_rollback  0   
Handler_tmp_update  0   
Handler_tmp_write   7   
Handler_update  0   
Handler_write   0   
Innodb_available_undo_logs  128 
Innodb_background_log_sync  1240    
Innodb_buffer_pool_bytes_data   189562880   
Innodb_buffer_pool_bytes_dirty  5832704 
Innodb_buffer_pool_dump_status  Dumping buffer pool(s) not yet started  
Innodb_buffer_pool_load_status  Loading buffer pool(s) not yet started  
Innodb_buffer_pool_pages_data   11570   
Innodb_buffer_pool_pages_dirty  356 
Innodb_buffer_pool_pages_flushed    10802   
Innodb_buffer_pool_pages_free   1561166 
Innodb_buffer_pool_pages_lru_flushed    0   
Innodb_buffer_pool_pages_made_not_young 0   
Innodb_buffer_pool_pages_made_young 0   
Innodb_buffer_pool_pages_misc   120 
Innodb_buffer_pool_pages_old    4428    
Innodb_buffer_pool_pages_total  1572856 
Innodb_buffer_pool_read_ahead   9780    
Innodb_buffer_pool_read_ahead_evicted   0   
Innodb_buffer_pool_read_ahead_rnd   0   
Innodb_buffer_pool_read_requests    2159295327  
Innodb_buffer_pool_reads    1780    
Innodb_buffer_pool_wait_free    0   
Innodb_buffer_pool_write_requests   63831   
Innodb_checkpoint_age   385586  
Innodb_checkpoint_max_age   80826164    
Innodb_data_fsyncs  7786    
Innodb_data_pending_fsyncs  0   
Innodb_data_pending_reads   0   
Innodb_data_pending_writes  0   
Innodb_data_read    189469184   
Innodb_data_reads   11603   
Innodb_data_writes  17885   
Innodb_data_written 361596416   
Innodb_dblwr_pages_written  10802   
Innodb_dblwr_writes 141 
Innodb_deadlocks    0   
Innodb_have_atomic_builtins ON  
Innodb_history_list_length  35  
Innodb_ibuf_discarded_delete_marks  0   
Innodb_ibuf_discarded_deletes   0   
Innodb_ibuf_discarded_inserts   0   
Innodb_ibuf_free_list   0   
Innodb_ibuf_merged_delete_marks 0   
Innodb_ibuf_merged_deletes  0   
Innodb_ibuf_merged_inserts  0   
Innodb_ibuf_merges  0   
Innodb_ibuf_segment_size    2   
Innodb_ibuf_size    1   
Innodb_log_waits    0   
Innodb_log_write_requests   8106    
Innodb_log_writes   6784    
Innodb_lsn_current  1309986856  
Innodb_lsn_flushed  1309986730  
Innodb_lsn_last_checkpoint  1309601270  
Innodb_master_thread_active_loops   1156    
Innodb_master_thread_idle_loops 84  
Innodb_max_trx_id   2809740 
Innodb_mem_adaptive_hash    409079776   

Innodb_mem_dictionary   102244264   
Innodb_mem_total    26990346240 
Innodb_mutex_os_waits   2127    
Innodb_mutex_spin_rounds    42261889    
Innodb_mutex_spin_waits 28215967    
Innodb_oldest_view_low_limit_trx_id 0   
Innodb_os_log_fsyncs    6858    
Innodb_os_log_pending_fsyncs    0   
Innodb_os_log_pending_writes    0   
Innodb_os_log_written   7598080 
Innodb_page_size    16384   
Innodb_pages_created    11  
Innodb_pages_read   11559   
Innodb_pages0_read  33  
Innodb_pages_written    10802   
Innodb_purge_trx_id 2809728 
Innodb_purge_undo_no    0   
Innodb_read_views_memory    720 
Innodb_row_lock_current_waits   3   
Innodb_row_lock_time    2331140 
Innodb_row_lock_time_avg    2578    
Innodb_row_lock_time_max    5959    
Innodb_row_lock_waits   904 
Innodb_rows_deleted 0   
Innodb_rows_inserted    868 
Innodb_rows_read    5115273889  
Innodb_rows_updated 11428   
Innodb_system_rows_deleted  0   
Innodb_system_rows_inserted 0   
Innodb_system_rows_read 0   
Innodb_system_rows_updated  0   
Innodb_s_lock_os_waits  91  
Innodb_s_lock_spin_rounds   2773    
Innodb_s_lock_spin_waits    98  
Innodb_truncated_status_writes  0   
Innodb_x_lock_os_waits  11  
Innodb_x_lock_spin_rounds   372 
Innodb_x_lock_spin_waits    5   
Innodb_page_compression_saved   0   
Innodb_page_compression_trim_sect512    0   
Innodb_page_compression_trim_sect1024   0   
Innodb_page_compression_trim_sect2048   0   
Innodb_page_compression_trim_sect4096   0   
Innodb_page_compression_trim_sect8192   0   
Innodb_page_compression_trim_sect16384  0   
Innodb_page_compression_trim_sect32768  0   
Innodb_num_index_pages_written  2906    
Innodb_num_non_index_pages_written  14979   
Innodb_num_pages_page_compressed    0   
Innodb_num_page_compressed_trim_op  0   
Innodb_num_page_compressed_trim_op_saved    0   
Innodb_num_pages_page_decompressed  0   
Innodb_num_pages_page_compression_error 0   
Innodb_num_pages_encrypted  0   
Innodb_num_pages_decrypted  0   
Innodb_have_lz4 ON  
Innodb_have_lzo OFF 
Innodb_have_lzma    OFF 
Innodb_have_bzip2   OFF 
Innodb_have_snappy  OFF 
Innodb_defragment_compression_failures  0   
Innodb_defragment_failures  0   
Innodb_defragment_count 0   
Innodb_onlineddl_rowlog_rows    0   
Innodb_onlineddl_rowlog_pct_used    0   
Innodb_onlineddl_pct_progress   0   
Innodb_secondary_index_triggered_cluster_reads  13139   
Innodb_secondary_index_triggered_cluster_reads_avo...   0   
Innodb_encryption_rotation_pages_read_from_cache    0   
Innodb_encryption_rotation_pages_read_from_disk 0   
Innodb_encryption_rotation_pages_modified   0   
Innodb_encryption_rotation_pages_flushed    0   
Innodb_encryption_rotation_estimated_iops   0   
Innodb_encryption_key_rotation_list_length  0   
Innodb_scrub_background_page_reorganizations    0   
Innodb_scrub_background_page_splits 0   
Innodb_scrub_background_page_split_failures_underf...   0   
Innodb_scrub_background_page_split_failures_out_of...   0   
Innodb_scrub_background_page_split_failures_missin...   0   
Innodb_scrub_background_page_split_failures_unknow...   0   
Innodb_encryption_num_key_requests  0   
Key_blocks_not_flushed  0   
Key_blocks_unused   19586535    
Key_blocks_used 4   
Key_blocks_warm 0   
Key_read_requests   14  
Key_reads   4   
Key_write_requests  0   
Key_writes  0   
Last_query_cost 10.499000   
Master_gtid_wait_count  0   
Master_gtid_wait_time   0   
Master_gtid_wait_timeouts   0   
Max_statement_time_exceeded 0   
Max_used_connections    7   
Memory_used 67544   
Not_flushed_delayed_rows    0   
Open_files  28  
Open_streams    0   
Open_table_definitions  48  

Open_tables 64  
Opened_files    149 
Opened_plugin_libraries 0   
Opened_table_definitions    0   
Opened_tables   0   
Opened_views    0   
Performance_schema_accounts_lost    0   
Performance_schema_cond_classes_lost    0   
Performance_schema_cond_instances_lost  0   
Performance_schema_digest_lost  0   
Performance_schema_file_classes_lost    0   
Performance_schema_file_handles_lost    0   
Performance_schema_file_instances_lost  0   
Performance_schema_hosts_lost   0   
Performance_schema_locker_lost  0   
Performance_schema_mutex_classes_lost   0   
Performance_schema_mutex_instances_lost 0   
Performance_schema_rwlock_classes_lost  0   
Performance_schema_rwlock_instances_lost    0   
Performance_schema_session_connect_attrs_lost   0   
Performance_schema_socket_classes_lost  0   
Performance_schema_socket_instances_lost    0   
Performance_schema_stage_classes_lost   0   
Performance_schema_statement_classes_lost   0   
Performance_schema_table_handles_lost   0   
Performance_schema_table_instances_lost 0   
Performance_schema_thread_classes_lost  0   
Performance_schema_thread_instances_lost    0   
Performance_schema_users_lost   0   
Prepared_stmt_count 4   
Qcache_free_blocks  372 
Qcache_free_memory  65739128    
Qcache_hits 10833   
Qcache_inserts  16036   
Qcache_lowmem_prunes    0   
Qcache_not_cached   2213    
Qcache_queries_in_cache 774 
Qcache_total_blocks 1937    
Queries 235946  
Questions   10  
Rows_read   0   
Rows_sent   8   
Rows_tmp_read   7   
Rpl_status  AUTH_MASTER 
Select_full_join    0   
Select_full_range_join  0   
Select_range    0   
Select_range_check  0   
Select_scan 4   
Slave_connections   0   
Slave_heartbeat_period  0.000   
Slave_open_temp_tables  0   
Slave_received_heartbeats   0   
Slave_retried_transactions  0   
Slave_running   OFF 
Slave_skipped_errors    0   
Slaves_connected    0   
Slaves_running  0   
Slow_launch_threads 0   
Slow_queries    0   
Sort_merge_passes   0   
Sort_priority_queue_sorts   0   
Sort_range  0   
Sort_rows   0   
Sort_scan   0   
Ssl_accept_renegotiates 0   
Ssl_accepts 0   
Ssl_callback_cache_hits 0   
Ssl_cipher      
Ssl_cipher_list     
Ssl_client_connects 0   
Ssl_connect_renegotiates    0   
Ssl_ctx_verify_depth    0   
Ssl_ctx_verify_mode 0   
Ssl_default_timeout 0   
Ssl_finished_accepts    0   
Ssl_finished_connects   0   
Ssl_server_not_after        
Ssl_server_not_before       
Ssl_session_cache_hits  0   
Ssl_session_cache_misses    0   
Ssl_session_cache_mode  NONE    
Ssl_session_cache_overflows 0   
Ssl_session_cache_size  0   
Ssl_session_cache_timeouts  0   
Ssl_sessions_reused 0   
Ssl_used_session_cache_entries  0   
Ssl_verify_depth    0   
Ssl_verify_mode 0   
Ssl_version     
Subquery_cache_hit  0   
Subquery_cache_miss 0   
Syncs   6   
Table_locks_immediate   35507   
Table_locks_waited  0   
Tc_log_max_pages_used   0   
Tc_log_page_size    0   
Tc_log_page_waits   0   
Threadpool_idle_threads 0   
Threadpool_threads  0   

Threads_cached  0   
Threads_connected   7   
Threads_created 7   
Threads_running 5   
Update_scan 0   
Uptime  1247    
Uptime_since_flush_status   1247    
wsrep_cluster_conf_id   18446744073709551615    
wsrep_cluster_size  0   
wsrep_cluster_state_uuid        
wsrep_cluster_status    Disconnected    
wsrep_connected OFF 
wsrep_local_bf_aborts   0   
wsrep_local_index   18446744073709551615    
wsrep_provider_name     
wsrep_provider_vendor       
wsrep_provider_version      
wsrep_ready OFF 
wsrep_thread_count  0   

MariaDB database server configuration file.

#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = *
#
# * Fine Tuning
#
max_connections     = 3500
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = -1
sort_buffer_size    = 40M
bulk_insert_buffer_size = 160M
tmp_table_size      = 320M
max_heap_table_size = 320M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size     = 23G
#open-files-limit   = 2000
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit       = 128K
query_cache_size        = 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type       = DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings        = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit    = 1000
log_slow_verbosity  = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#report_host        = master1
#auto_increment_increment = 2
#auto_increment_offset  = 1
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog        = 1
expire_logs_days    = 10
max_binlog_size         = 100M
# slaves
#relay_log      = /var/log/mysql/relay-bin
#relay_log_index    = /var/log/mysql/relay-bin.index
#relay_log_info_file    = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode       = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_size = 24G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[mysqld]
character-set-server = utf8

I have tweaked my my.cnf as much as I know how, but still MariaDB spikes crazily - can go from 110% to 200+ in seconds. I have indexed my tables as good as I know how, but still it seems like nothing quells the lag. Is there a way, since I have 80GB of RAM, that I could get MariaDB to use more RAM since currently its's only using around 9% of that total 80GB?

I feel lost here because nothing I'm doing is working and it doesn't even seem to be helping.

1
  • Have you tried changing the innodb_buffer_pool_size and thread_cache_size and see if anything improves? I would also check sysctl vm.swappiness. Sometime disabling swapiness improves performance. Also what kind of queries are common in your server? More select/sort or insert/update? Sep 1, 2017 at 7:26

1 Answer 1

1

in your .cnf within [mysqld] section

max_connections = 300  # from 3500 until you get stabilized
thread_cache_size = 100  # from -1  100 CAP is upper limit at this time
key_buffer_size = 32M  # from 23G whoa, likely an accident. only used by ISAM
REMOVE sort_buffer_size   # from 40M  to default
REMOVE read_buffer_size  # from 2M  to default
REMOVE read_rnd_buffer_size  # from 1M  to default  

normally only 1 change is made per day, in your case change them all to survive.

mysqlcalculator.com could be a valuable resource for your use.

Please repost SHOW GLOBAL STATUS after 24 hours of uptime and add to your question SHOW GLOBAL VARIABLES and SHOW ENGINE INNODB STATUS for further analysis.

3
  • 1
    @derek HEADS UP related to query cache removal in 8.0 mysqlserverteam.com/… this decision may eventually find it's way to MARIADB. Your SHOW GLOBAL STATUS indicates QC was heavily involved during the 1247 seconds of uptime. Sep 12, 2017 at 14:13
  • @derek Any progress since last posting? Please repost SHOW GLOBAL STATUS after 24 hours of uptime. Please repost my.cnf with current configuration. Adding SHOW GLOBAL VARIABLES would be helpful. Normally there is only ONE section [mysqld] in my.cnf. There is a straggler at the end that should probably be a part of BASIC. Oct 6, 2017 at 0:24
  • @derek Please let us know if you are making progress. I see you have 80GB RAM and less than 200M stored in your innodb_buffer_pool_data. innodb_buffer_pool_size=40G #from < 200M would use 1/2 of RAM to your advantage. Oct 29, 2017 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.