MySQL 5.7 High Memory usage Killed by OOM

clickround

Registered
Sep 14, 2020
3
0
1
leb
cPanel Access Level
Root Administrator
i upgrade my WHM server / Cent-OS 7 from MySQL 5.6 to 5.7 this server is dedicated server for whm server [apache, php, mysql] hosting for multi web sites with very small number of visitors. after that upgrade every few hours my server memory full by MySQL which after hours of MySQL restart it exceed 80% of whole memory. i increase my server ram from 13GB to 17.5GB and still get same problem also i create swap for 3GB and still same problem. i change in MySQL configuration without any luck. so if anyone can help with that.


i try also mysqltuner but the problem that MySQL restarting every few hours and they said that MySQL must be running more than 24 hour at lease for recommendation to be accurate.


this is the configuration of the etc/my.cnf file:

Code:
[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1

max_allowed_packet=268435456

open_files_limit=80000



collation_server=utf8_unicode_ci

character_set_server=utf8

slow_query_log=1

long_query_time=3

max_connections = 200

sort_buffer_size=16M

join_buffer_size=1M

innodb_sort_buffer_size=16M

read_buffer_size=1M

read_rnd_buffer_size=2M

max_heap_table_size=64M

tmp_table_size=64M

key_buffer_size=128M

#event_scheduler=on


innodb_log_file_size=384M

query_cache_type=0

query_cache_size=0

table_open_cache=78000

table_definition_cache=39400

innodb_buffer_pool_instances=3

innodb_buffer_pool_size=3G


and the result of mysql tuner i don't know if it can help as MySQL not running from a long time:


 >>  MySQLTuner 1.7.19 - Major Hayden <[email protected]>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering


[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.7.31-log

[OK] Operating on 64-bit architecture


-------- Log file Recommendations ------------------------------------------------------------------

[OK] Log file /var/lib/mysql/bilal.hosting.err exists

[--] Log file: /var/lib/mysql/bilal.hosting.err(38K)

[OK] Log file /var/lib/mysql/bilal.hosting.err is readable.

[OK] Log file /var/lib/mysql/bilal.hosting.err is not empty

[OK] Log file /var/lib/mysql/bilal.hosting.err is smaller than 32 Mb

[!!] /var/lib/mysql/bilal.hosting.err contains 55 warning(s).

[!!] /var/lib/mysql/bilal.hosting.err contains 5 error(s).

[--] 5 start(s) detected in /var/lib/mysql/bilal.hosting.err

[--] 1) 2020-09-13T23:07:20.256464Z 0 [Note] /usr/sbin/mysqld: ready for connections.

[--] 2) 2020-09-13T23:07:15.490922Z 0 [Note] /usr/sbin/mysqld: ready for connections.

[--] 3) 2020-09-13T09:53:37.024396Z 0 [Note] /usr/sbin/mysqld: ready for connections.

[--] 4) 2020-09-12T11:38:30.571913Z 0 [Note] /usr/sbin/mysqld: ready for connections.

[--] 5) 2020-09-12T05:40:13.253650Z 0 [Note] /usr/sbin/mysqld: ready for connections.

[--] 1 shutdown(s) detected in /var/lib/mysql/bilal.hosting.err

[--] 1) 2020-09-13T23:07:17.257863Z 0 [Note] /usr/sbin/mysqld: Shutdown complete


-------- Storage Engine Statistics -----------------------------------------------------------------

[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA

[--] Data in MyISAM tables: 10.0M (Tables: 51)

[--] Data in InnoDB tables: 5.4G (Tables: 30538)

[OK] Total fragmented tables: 0


-------- Analysis Performance Metrics --------------------------------------------------------------

[--] innodb_stats_on_metadata: OFF

[OK] No stat updates during querying INFORMATION_SCHEMA.


-------- Security Recommendations ------------------------------------------------------------------

[OK] There are no anonymous accounts for any database users

[OK] All database users have passwords assigned

[!!] There is no basic password file list!


-------- CVE Security Recommendations --------------------------------------------------------------

[--] Skipped due to --cvefile option undefined


-------- Performance Metrics -----------------------------------------------------------------------

[--] Up for: 7h 37m 6s (1M q [45.248 qps], 7K conn, TX: 2G, RX: 90M)

[--] Reads / Writes: 99% / 1%

[--] Binary logging is disabled

[--] Physical Memory     : 17.0G

[--] Max MySQL memory    : 57.2G

[--] Other process memory: 0B

[--] Total buffers: 3.2G global + 276.2M per thread (200 max threads)

[--] P_S Max memory usage: 72B

[--] Galera GCache Max memory usage: 0B

[OK] Maximum reached memory usage: 5.4G (31.56% of installed RAM)

[!!] Maximum possible memory usage: 57.2G (336.45% of installed RAM)

[!!] Overall possible memory usage with other process exceeded memory

[OK] Slow queries: 0% (74/1M)

[OK] Highest usage of available connections: 4% (8/200)

[OK] Aborted connections: 0.28%  (22/7973)

[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.

[OK] Sorts requiring temporary tables: 6% (3K temp sorts / 45K sorts)

[!!] Joins performed without indexes: 3963

[OK] Temporary tables created on disk: 8% (136K on disk / 1M total)

[OK] Thread cache hit rate: 99% (8 created / 7K connections)

[!!] Table cache hit rate: 1% (39K open / 3M opened)

[!!] table_definition_cache(39400) is lower than number of tables(44053)

[OK] Open file limit used: 0% (386/80K)

[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)


-------- Performance schema ------------------------------------------------------------------------

[--] Memory used by P_S: 72B

[--] Sys schema is installed.


-------- ThreadPool Metrics ------------------------------------------------------------------------

[--] ThreadPool stat is disabled.


-------- MyISAM Metrics ----------------------------------------------------------------------------

[!!] Key buffer used: 18.3% (24M used / 134M cache)

[OK] Key buffer size / total MyISAM indexes: 128.0M/1.1M

[OK] Read Key buffer hit rate: 99.8% (35K cached / 81 reads)


-------- InnoDB Metrics ----------------------------------------------------------------------------

[--] InnoDB is enabled.

[--] InnoDB Thread Concurrency: 0

[OK] InnoDB File per table is activated

[!!] InnoDB buffer pool / data size: 3.0G/5.4G

[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 384.0M * 2/3.0G should be equal to 25%

[OK] InnoDB buffer pool instances: 3

[--] Number of InnoDB Buffer Pool Chunk : 24 for 3 Buffer Pool Instance(s)

[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances

[OK] InnoDB Read buffer efficiency: 99.96% (414562897 hits/ 414708820 total)

[!!] InnoDB Write Log efficiency: 30.71% (1067 hits/ 3475 total)

[OK] InnoDB log waits: 0.00% (0 waits / 2408 writes)


-------- AriaDB Metrics ----------------------------------------------------------------------------

[--] AriaDB is disabled.


-------- TokuDB Metrics ----------------------------------------------------------------------------

[--] TokuDB is disabled.


-------- XtraDB Metrics ----------------------------------------------------------------------------

[--] XtraDB is disabled.


-------- Galera Metrics ----------------------------------------------------------------------------

[--] Galera is disabled.


-------- Replication Metrics -----------------------------------------------------------------------

[--] Galera Synchronous replication: NO

[--] No replication slave(s) for this server.

[--] Binlog format: ROW

[--] XA support enabled: ON

[--] Semi synchronous replication Master: Not Activated

[--] Semi synchronous replication Slave: Not Activated

[--] This is a standalone server


-------- Recommendations ---------------------------------------------------------------------------

General recommendations:

    Control warning line(s) into /var/lib/mysql/bilal.hosting.err file

    Control error line(s) into /var/lib/mysql/bilal.hosting.err 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

    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.

             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html

             (specially the conclusions at the bottom of the page).

    Increase table_open_cache gradually to avoid file descriptor limits

    Read this before increasing table_open_cache over 64: xxxxxxxxxxxxxxxxxxxx

    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/

    This is MyISAM only table_cache scalability problem, InnoDB not affected.

    See more details here: https://bugs.mysql.com/bug.php?id=49177

    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.

    Beware that open_files_limit (80000) variable

    should be greater than table_open_cache (39895)

Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

    join_buffer_size (> 1.0M, or always use indexes with JOINs)

    table_open_cache (> 39895)

    table_definition_cache(39400) > 44053 or -1 (autosizing if supported)

    innodb_buffer_pool_size (>= 5.4G) if possible.
note i try to put innodb_buffer_pool_size to 6G as recommended but still the same memory load.


in mysql tuner result they gave result that Maximum possible memory usage: 57.2G (336.45% of installed RAM) is that a problem and how to fix this issue!


also no load on the server or mysql. and on top just the mysql is taking the memory above 70% of it


and this the output of free:

Code:
              total        used        free      shared  buff/cache   available

Mem:            16G         13G        323M         14M        3.6G        3.6G

Swap:          3.0G        2.1G        933M

Total:          19G         15G        1.2G
OOM Log Example:

Code:
# grep -i "out of memory" /var/log/messages*

/var/log/messages:Sep  7 03:00:23 hosting kernel: Out of memory: Kill process 1969 (mysqld) score 695 or sacrifice child

/var/log/messages:Sep  7 11:41:09 hosting kernel: Out of memory: Kill process 1787 (mysqld) score 756 or sacrifice child

/var/log/messages:Sep  7 13:25:46 hosting kernel: Out of memory: Kill process 8455 (mysqld) score 723 or sacrifice child

/var/log/messages:Sep  7 15:29:27 hosting kernel: Out of memory: Kill process 28063 (mysqld) score 734 or sacrifice child

/var/log/messages:Sep  7 20:56:49 hosting kernel: Out of memory: Kill process 23450 (mysqld) score 727 or sacrifice child

/var/log/messages:Sep  7 20:56:50 hosting kernel: Out of memory: Kill process 2043 (clamd) score 80 or sacrifice child

/var/log/messages:Sep  8 03:05:20 hosting kernel: Out of memory: Kill process 26713 (mysqld) score 681 or sacrifice child

/var/log/messages:Sep  8 05:28:19 hosting kernel: Out of memory: Kill process 3038 (mysqld) score 681 or sacrifice child

/var/log/messages:Sep  8 11:14:35 hosting kernel: Out of memory: Kill process 30391 (mysqld) score 742 or sacrifice child

/var/log/messages:Sep  8 15:02:51 hosting kernel: Out of memory: Kill process 3984 (mysqld) score 742 or sacrifice child

/var/log/messages:Sep  9 12:21:44 hosting kernel: Out of memory: Kill process 1743 (mysqld) score 763 or sacrifice child

/var/log/messages:Sep  9 14:31:21 hosting kernel: Out of memory: Kill process 16796 (mysqld) score 774 or sacrifice child

/var/log/messages:Sep 10 13:01:14 hosting kernel: Out of memory: Kill process 4654 (mysqld) score 788 or sacrifice child

/var/log/messages:Sep 10 13:01:14 hosting kernel: Out of memory: Kill process 2002 (clamd) score 61 or sacrifice child

/var/log/messages:Sep 10 16:31:45 hosting kernel: Out of memory: Kill process 12720 (mysqld) score 816 or sacrifice child
Thanks,
 

andrew.n

Well-Known Member
Jun 9, 2020
240
51
28
EU
cPanel Access Level
Root Administrator
If you only have a few websites with only a few visitors then 10+GB of memory should be much more than enough. I would like into the process list to see what is causing this. You can do so from WHM under MySQL Processlist if I recall.
 

clickround

Registered
Sep 14, 2020
3
0
1
leb
cPanel Access Level
Root Administrator
If you only have a few websites with only a few visitors then 10+GB of memory should be much more than enough. I would like into the process list to see what is causing this. You can do so from WHM under MySQL Processlist if I recall.
yes i check mysql process list periodically using phpmyadmin and "mysqladmin processList" command but most of times empty. this is an example:

Code:
mysqladmin processList
+-------+-----------------+-----------+----+---------+------+-----------------------------+------------------+
| Id    | User            | Host      | db | Command | Time | State                       | Info             |
+-------+-----------------+-----------+----+---------+------+-----------------------------+------------------+
| 1     | event_scheduler | localhost |    | Daemon  | 5496 | Waiting for next activation |                  |
| 14944 | root            | localhost |    | Query   | 0    | starting                    | show processlist |
+-------+-----------------+-----------+----+---------+------+-----------------------------+------------------+
 

andrew.n

Well-Known Member
Jun 9, 2020
240
51
28
EU
cPanel Access Level
Root Administrator
It's similar when you are having high load as well? Looks like something else is causing the high load then.....
 

cPanelLauren

Technical Support Community Manager
Staff member
Nov 14, 2017
13,041
1,206
313
Houston
Troubleshooting high load attributed to MySQL is best performed by a system administrator. If you don't have one you might find one here: System Administration Services
 

clickround

Registered
Sep 14, 2020
3
0
1
leb
cPanel Access Level
Root Administrator
It's similar when you are having high load as well? Looks like something else is causing the high load then.....
yes even when there is high load there is no process in mysql. but mysql consumes above 80% of the memory when check top:

Code:
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
11690 cpanels+  20   0 3091984 269868    828 S   0.3  1.5  32:10.95 java
27629 mysql     20   0   15.8g  14.5g   6528 S   0.3 85.6  81:37.12 mysqld
29470 root      20   0  246052  16328    588 S   0.3  0.1   0:14.03 spamd
29649 nobody    20   0  366968  80760   3380 S   0.3  0.5   0:00.53 httpd
    1 root      20   0  194108   4844   2476 S   0.0  0.0  12:49.30 systemd
and this is the output is show global variables:

Code:
Variable_name     Value     
auto_generate_certs     ON
auto_increment_increment     1
auto_increment_offset     1
autocommit     ON
automatic_sp_privileges     ON
avoid_temporal_upgrade     OFF
back_log     90
basedir     /usr/
big_tables     OFF
bind_address     *
binlog_cache_size     32768
binlog_checksum     CRC32
binlog_direct_non_transactional_updates     OFF
binlog_error_action     ABORT_SERVER
binlog_format     ROW
binlog_group_commit_sync_delay     0
binlog_group_commit_sync_no_delay_count     0
binlog_gtid_simple_recovery     ON
binlog_max_flush_queue_time     0
binlog_order_commits     ON
binlog_row_image     FULL
binlog_rows_query_log_events     OFF
binlog_stmt_cache_size     32768
binlog_transaction_dependency_history_size     25000
binlog_transaction_dependency_tracking     COMMIT_ORDER
block_encryption_mode     aes-128-ecb
bulk_insert_buffer_size     8388608
character_set_client     utf8
character_set_connection     utf8
character_set_database     utf8
character_set_filesystem     binary
character_set_results     utf8
character_set_server     utf8
character_set_system     utf8
character_sets_dir     /usr/share/mysql/charsets/
check_proxy_users     OFF
collation_connection     utf8_unicode_ci
collation_database     utf8_unicode_ci
collation_server     utf8_unicode_ci
completion_type     NO_CHAIN
concurrent_insert     AUTO
connect_timeout     10
core_file     OFF
datadir     /var/lib/mysql/
date_format     %Y-%m-%d
datetime_format     %Y-%m-%d %H:%i:%s
default_authentication_plugin     mysql_native_password
default_password_lifetime     0
default_storage_engine     InnoDB
default_tmp_storage_engine     InnoDB
default_week_format     0
delay_key_write     ON
delayed_insert_limit     100
delayed_insert_timeout     300
delayed_queue_size     1000
disabled_storage_engines     
disconnect_on_expired_password     ON
div_precision_increment     4
end_markers_in_json     OFF
enforce_gtid_consistency     OFF
eq_range_index_dive_limit     200
event_scheduler     ON
expire_logs_days     0
explicit_defaults_for_timestamp     OFF
flush     OFF
flush_time     0
foreign_key_checks     ON
ft_boolean_syntax     + -><()~*:""&|
ft_max_word_len     84
ft_min_word_len     4
ft_query_expansion_limit     20
ft_stopword_file     (built-in)
general_log     OFF
general_log_file     /var/lib/mysql/hosting.log
group_concat_max_len     1024
gtid_executed     
gtid_executed_compression_period     1000
gtid_mode     OFF
gtid_owned     
gtid_purged     
have_compress     YES
have_crypt     YES
have_dynamic_loading     YES
have_geometry     YES
have_openssl     YES
have_profiling     YES
have_query_cache     YES
have_rtree_keys     YES
have_ssl     YES
have_statement_timeout     YES
have_symlink     YES
host_cache_size     328
hostname     hosting.bilal
ignore_builtin_innodb     OFF
ignore_db_dirs     
init_connect     
init_file     
init_slave     
innodb_adaptive_flushing     ON
innodb_adaptive_flushing_lwm     10
Variable_name     Value     
innodb_adaptive_hash_index     ON
innodb_adaptive_hash_index_parts     8
innodb_adaptive_max_sleep_delay     150000
innodb_api_bk_commit_interval     5
innodb_api_disable_rowlock     OFF
innodb_api_enable_binlog     OFF
innodb_api_enable_mdl     OFF
innodb_api_trx_level     0
innodb_autoextend_increment     64
innodb_autoinc_lock_mode     1
innodb_buffer_pool_chunk_size     134217728
innodb_buffer_pool_dump_at_shutdown     ON
innodb_buffer_pool_dump_now     OFF
innodb_buffer_pool_dump_pct     25
innodb_buffer_pool_filename     ib_buffer_pool
innodb_buffer_pool_instances     2
innodb_buffer_pool_load_abort     OFF
innodb_buffer_pool_load_at_startup     ON
innodb_buffer_pool_load_now     OFF
innodb_buffer_pool_size     2147483648
innodb_change_buffer_max_size     25
innodb_change_buffering     all
innodb_checksum_algorithm     crc32
innodb_checksums     ON
innodb_cmp_per_index_enabled     OFF
innodb_commit_concurrency     0
innodb_compression_failure_threshold_pct     5
innodb_compression_level     6
innodb_compression_pad_pct_max     50
innodb_concurrency_tickets     5000
innodb_data_file_path     ibdata1:12M:autoextend
innodb_data_home_dir     
innodb_deadlock_detect     ON
innodb_default_row_format     dynamic
innodb_disable_sort_file_cache     OFF
innodb_doublewrite     ON
innodb_fast_shutdown     1
innodb_file_format     Barracuda
innodb_file_format_check     ON
innodb_file_format_max     Barracuda
innodb_file_per_table     ON
innodb_fill_factor     100
innodb_flush_log_at_timeout     1
innodb_flush_log_at_trx_commit     1
innodb_flush_method     
innodb_flush_neighbors     1
innodb_flush_sync     ON
innodb_flushing_avg_loops     30
innodb_force_load_corrupted     OFF
innodb_force_recovery     0
innodb_ft_aux_table     
innodb_ft_cache_size     8000000
innodb_ft_enable_diag_print     OFF
innodb_ft_enable_stopword     ON
innodb_ft_max_token_size     84
innodb_ft_min_token_size     3
innodb_ft_num_word_optimize     2000
innodb_ft_result_cache_limit     2000000000
innodb_ft_server_stopword_table     
innodb_ft_sort_pll_degree     2
innodb_ft_total_cache_size     640000000
innodb_ft_user_stopword_table     
innodb_io_capacity     200
innodb_io_capacity_max     2000
innodb_large_prefix     ON
innodb_lock_wait_timeout     50
innodb_locks_unsafe_for_binlog     OFF
innodb_log_buffer_size     16777216
innodb_log_checksums     ON
innodb_log_compressed_pages     ON
innodb_log_file_size     268435456
innodb_log_files_in_group     2
innodb_log_group_home_dir     ./
innodb_log_write_ahead_size     8192
innodb_lru_scan_depth     1024
innodb_max_dirty_pages_pct     75.000000
innodb_max_dirty_pages_pct_lwm     0.000000
innodb_max_purge_lag     0
innodb_max_purge_lag_delay     0
innodb_max_undo_log_size     1073741824
innodb_monitor_disable     
innodb_monitor_enable     
innodb_monitor_reset     
innodb_monitor_reset_all     
innodb_numa_interleave     OFF
innodb_old_blocks_pct     37
innodb_old_blocks_time     1000
innodb_online_alter_log_max_size     134217728
innodb_open_files     35000
innodb_optimize_fulltext_only     OFF
innodb_page_cleaners     2
innodb_page_size     16384
innodb_print_all_deadlocks     OFF
innodb_purge_batch_size     300
innodb_purge_rseg_truncate_frequency     128
innodb_purge_threads     4
innodb_random_read_ahead     OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads     4
innodb_read_only     OFF
Variable_name     Value     
innodb_replication_delay     0
innodb_rollback_on_timeout     OFF
innodb_rollback_segments     128
innodb_sort_buffer_size     16777216
innodb_spin_wait_delay     6
innodb_stats_auto_recalc     ON
innodb_stats_include_delete_marked     OFF
innodb_stats_method     nulls_equal
innodb_stats_on_metadata     OFF
innodb_stats_persistent     ON
innodb_stats_persistent_sample_pages     20
innodb_stats_sample_pages     8
innodb_stats_transient_sample_pages     8
innodb_status_output     OFF
innodb_status_output_locks     OFF
innodb_strict_mode     ON
innodb_support_xa     ON
innodb_sync_array_size     1
innodb_sync_spin_loops     30
innodb_table_locks     ON
innodb_temp_data_file_path     ibtmp1:12M:autoextend
innodb_thread_concurrency     0
innodb_thread_sleep_delay     10000
innodb_tmpdir     
innodb_undo_directory     ./
innodb_undo_log_truncate     OFF
innodb_undo_logs     128
innodb_undo_tablespaces     0
innodb_use_native_aio     ON
innodb_version     5.7.31
innodb_write_io_threads     4
interactive_timeout     28800
internal_tmp_disk_storage_engine     InnoDB
join_buffer_size     1048576
keep_files_on_create     OFF
key_buffer_size     134217728
key_cache_age_threshold     300
key_cache_block_size     1024
key_cache_division_limit     100
keyring_operations     ON
large_files_support     ON
large_page_size     0
large_pages     OFF
lc_messages     en_US
lc_messages_dir     /usr/share/mysql/
lc_time_names     en_US
license     GPL
local_infile     ON
lock_wait_timeout     31536000
locked_in_memory     OFF
log_bin     OFF
log_bin_basename     
log_bin_index     
log_bin_trust_function_creators     OFF
log_bin_use_v1_row_events     OFF
log_builtin_as_identified_by_password     OFF
log_error     /var/lib/mysql/hosting.bilal
log_error_verbosity     3
log_output     FILE
log_queries_not_using_indexes     OFF
log_slave_updates     OFF
log_slow_admin_statements     OFF
log_slow_slave_statements     OFF
log_statements_unsafe_for_binlog     ON
log_syslog     OFF
log_syslog_facility     daemon
log_syslog_include_pid     ON
log_syslog_tag     
log_throttle_queries_not_using_indexes     0
log_timestamps     UTC
log_warnings     2
long_query_time     5.000000
low_priority_updates     OFF
lower_case_file_system     OFF
lower_case_table_names     0
master_info_repository     FILE
master_verify_checksum     OFF
max_allowed_packet     268435456
max_binlog_cache_size     18446744073709547520
max_binlog_size     1073741824
max_binlog_stmt_cache_size     18446744073709547520
max_connect_errors     100
max_connections     200
max_delayed_threads     20
max_digest_length     1024
max_error_count     64
max_execution_time     0
max_heap_table_size     67108864
max_insert_delayed_threads     20
max_join_size     18446744073709551615
max_length_for_sort_data     1024
max_points_in_geometry     65536
max_prepared_stmt_count     16382
max_relay_log_size     0
max_seeks_for_key     18446744073709551615
max_sort_length     1024
max_sp_recursion_depth     0
max_tmp_tables     32
max_user_connections     0
max_write_lock_count     18446744073709551615
Variable_name     Value     
metadata_locks_cache_size     1024
metadata_locks_hash_instances     8
min_examined_row_limit     0
multi_range_count     256
myisam_data_pointer_size     6
myisam_max_sort_file_size     9223372036853727232
myisam_mmap_size     18446744073709551615
myisam_recover_options     OFF
myisam_repair_threads     1
myisam_sort_buffer_size     8388608
myisam_stats_method     nulls_unequal
myisam_use_mmap     OFF
mysql_native_password_proxy_users     OFF
net_buffer_length     16384
net_read_timeout     30
net_retry_count     10
net_write_timeout     60
new     OFF
ngram_token_size     2
offline_mode     OFF
old     OFF
old_alter_table     OFF
old_passwords     0
open_files_limit     80000
optimizer_prune_level     1
optimizer_search_depth     62
optimizer_switch     index_merge=on,index_merge_union=on,index_merge_so...
optimizer_trace     enabled=off,one_line=off
optimizer_trace_features     greedy_search=on,range_optimizer=on,dynamic_range=...
optimizer_trace_limit     1
optimizer_trace_max_mem_size     16384
optimizer_trace_offset     -1
parser_max_mem_size     18446744073709551615
performance_schema     ON
performance_schema_accounts_size     -1
performance_schema_digests_size     10000
performance_schema_events_stages_history_long_size     10000
performance_schema_events_stages_history_size     10
performance_schema_events_statements_history_long_...     10000
performance_schema_events_statements_history_size     10
performance_schema_events_transactions_history_lon...     10000
performance_schema_events_transactions_history_siz...     10
performance_schema_events_waits_history_long_size     10000
performance_schema_events_waits_history_size     10
performance_schema_hosts_size     -1
performance_schema_max_cond_classes     80
performance_schema_max_cond_instances     -1
performance_schema_max_digest_length     1024
performance_schema_max_file_classes     80
performance_schema_max_file_handles     32768
performance_schema_max_file_instances     -1
performance_schema_max_index_stat     -1
performance_schema_max_memory_classes     320
performance_schema_max_metadata_locks     -1
performance_schema_max_mutex_classes     210
performance_schema_max_mutex_instances     -1
performance_schema_max_prepared_statements_instanc...     -1
performance_schema_max_program_instances     -1
performance_schema_max_rwlock_classes     50
performance_schema_max_rwlock_instances     -1
performance_schema_max_socket_classes     10
performance_schema_max_socket_instances     -1
performance_schema_max_sql_text_length     1024
performance_schema_max_stage_classes     150
performance_schema_max_statement_classes     193
performance_schema_max_statement_stack     10
performance_schema_max_table_handles     -1
performance_schema_max_table_instances     -1
performance_schema_max_table_lock_stat     -1
performance_schema_max_thread_classes     50
performance_schema_max_thread_instances     -1
performance_schema_session_connect_attrs_size     512
performance_schema_setup_actors_size     -1
performance_schema_setup_objects_size     -1
performance_schema_users_size     -1
pid_file     /var/run/mysqld/mysqld.pid
plugin_dir     /usr/lib64/mysql/plugin/
port     3306
preload_buffer_size     32768
profiling     OFF
profiling_history_size     15
protocol_version     10
query_alloc_block_size     8192
query_cache_limit     1048576
query_cache_min_res_unit     4096
query_cache_size     0
query_cache_type     OFF
query_cache_wlock_invalidate     OFF
query_prealloc_size     8192
range_alloc_block_size     4096
range_optimizer_max_mem_size     8388608
rbr_exec_mode     STRICT
read_buffer_size     524288
read_only     OFF
read_rnd_buffer_size     1048576
relay_log     
relay_log_basename     /var/lib/mysql/hosting-relay-bin
relay_log_index     /var/lib/mysql/hosting-relay-bin.index
relay_log_info_file     relay-log.info
relay_log_info_repository     FILE
Variable_name     Value     
relay_log_purge     ON
relay_log_recovery     OFF
relay_log_space_limit     0
report_host     
report_password     
report_port     3306
report_user     
require_secure_transport     OFF
rpl_stop_slave_timeout     31536000
secure_auth     ON
secure_file_priv     /var/lib/mysql-files/
server_id     0
server_id_bits     32
server_uuid     feb7703f-364c-11e8-bf9b-42010aa20002
session_track_gtids     OFF
session_track_schema     ON
session_track_state_change     OFF
session_track_system_variables     time_zone,autocommit,character_set_client,characte...
session_track_transaction_info     OFF
sha256_password_auto_generate_rsa_keys     ON
sha256_password_private_key_path     private_key.pem
sha256_password_proxy_users     OFF
sha256_password_public_key_path     public_key.pem
show_compatibility_56     OFF
show_create_table_verbosity     OFF
show_old_temporals     OFF
skip_external_locking     ON
skip_name_resolve     OFF
skip_networking     OFF
skip_show_database     OFF
slave_allow_batching     OFF
slave_checkpoint_group     512
slave_checkpoint_period     300
slave_compressed_protocol     OFF
slave_exec_mode     STRICT
slave_load_tmpdir     /tmp
slave_max_allowed_packet     1073741824
slave_net_timeout     60
slave_parallel_type     DATABASE
slave_parallel_workers     0
slave_pending_jobs_size_max     16777216
slave_preserve_commit_order     OFF
slave_rows_search_algorithms     TABLE_SCAN,INDEX_SCAN
slave_skip_errors     OFF
slave_sql_verify_checksum     ON
slave_transaction_retries     10
slave_type_conversions     
slow_launch_time     2
slow_query_log     ON
slow_query_log_file     /var/lib/mysql/hosting-slow.log
socket     /var/lib/mysql/mysql.sock
sort_buffer_size     8388608
sql_auto_is_null     OFF
sql_big_selects     ON
sql_buffer_result     OFF
sql_log_off     OFF
sql_mode     ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_...
sql_notes     ON
sql_quote_show_create     ON
sql_safe_updates     OFF
sql_select_limit     18446744073709551615
sql_slave_skip_counter     0
sql_warnings     OFF
ssl_ca     ca.pem
ssl_capath     
ssl_cert     server-cert.pem
ssl_cipher     
ssl_crl     
ssl_crlpath     
ssl_key     server-key.pem
stored_program_cache     256
super_read_only     OFF
sync_binlog     1
sync_frm     ON
sync_master_info     10000
sync_relay_log     10000
sync_relay_log_info     10000
system_time_zone     EEST
table_definition_cache     17900
table_open_cache     35000
table_open_cache_instances     16
thread_cache_size     10
thread_handling     one-thread-per-connection
thread_stack     262144
time_format     %H:%i:%s
time_zone     SYSTEM
tls_version     TLSv1,TLSv1.1,TLSv1.2
tmp_table_size     67108864
tmpdir     /tmp
transaction_alloc_block_size     8192
transaction_isolation     REPEATABLE-READ
transaction_prealloc_size     4096
transaction_read_only     OFF
transaction_write_set_extraction     OFF
tx_isolation     REPEATABLE-READ
tx_read_only     OFF
unique_checks     ON
updatable_views_with_limit     YES
version     5.7.31-log
version_comment     MySQL Community Server (GPL)
Variable_name     Value     
version_compile_machine     x86_64
version_compile_os     Linux
wait_timeout     28800

and show global status if they can help:

Code:
Variable_name   Value   
Aborted_clients     0
Aborted_connects    6
Binlog_cache_disk_use   0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Bytes_received  187149967
Bytes_sent  539231478
Com_admin_commands  3192
Com_assign_to_keycache  0
Com_alter_db    0
Com_alter_db_upgrade    0
Com_alter_event     0
Com_alter_function  0
Com_alter_instance  0
Com_alter_procedure     0
Com_alter_server    0
Com_alter_table     0
Com_alter_tablespace    0
Com_alter_user  0
Com_analyze     0
Com_begin   707
Com_binlog  0
Com_call_procedure  10
Com_change_db   3467
Com_change_master   0
Com_change_repl_filter  0
Com_check   0
Com_checksum    0
Com_commit  752
Com_create_db   0
Com_create_event    0
Com_create_function     0
Com_create_index    0
Com_create_procedure    0
Com_create_server   0
Com_create_table    0
Com_create_trigger  0
Com_create_udf  0
Com_create_user     0
Com_create_view     0
Com_dealloc_sql     0
Com_delete  790
Com_delete_multi    39
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_server     0
Com_drop_table  0
Com_drop_trigger    0
Com_drop_user   1
Com_drop_view   0
Com_empty_query     0
Com_execute_sql     0
Com_explain_other   0
Com_flush   0
Com_get_diagnostics     0
Com_grant   0
Com_ha_close    0
Com_ha_open     0
Com_ha_read     0
Com_help    0
Com_insert  9398
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_rollback    1
Com_rollback_to_savepoint   0
Com_savepoint   0
Com_select  1080660
Com_set_option  47077
Com_signal  0
Com_show_binlog_events  0
Com_show_binlogs    0
Com_show_charsets   0
Com_show_collations     0
Com_show_create_db  0
Com_show_create_event   0
Com_show_create_func    0
Com_show_create_proc    0
Variable_name   Value   
Com_show_create_table   35
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_events     0
Com_show_errors     0
Com_show_fields     116
Com_show_function_code  0
Com_show_function_status    0
Com_show_grants     0
Com_show_keys   41
Com_show_master_status  1
Com_show_open_tables    0
Com_show_plugins    1
Com_show_privileges     0
Com_show_procedure_code     0
Com_show_procedure_status   0
Com_show_processlist    151
Com_show_profile    0
Com_show_profiles   0
Com_show_relaylog_events    0
Com_show_slave_hosts    0
Com_show_slave_status   1
Com_show_status     103
Com_show_storage_engines    0
Com_show_table_status   0
Com_show_tables     226
Com_show_triggers   1
Com_show_variables  42
Com_show_warnings   23
Com_show_create_user    0
Com_shutdown    0
Com_slave_start     0
Com_slave_stop  0
Com_group_replication_start     0
Com_group_replication_stop  0
Com_stmt_execute    99
Com_stmt_close  99
Com_stmt_fetch  0
Com_stmt_prepare    99
Com_stmt_reset  0
Com_stmt_send_long_data     0
Com_truncate    0
Com_uninstall_plugin    0
Com_unlock_tables   0
Com_update  2619
Com_update_multi    29
Com_xa_commit   0
Com_xa_end  0
Com_xa_prepare  0
Com_xa_recover  0
Com_xa_rollback     0
Com_xa_start    0
Com_stmt_reprepare  0
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     21796
Created_tmp_disk_tables     7006
Created_tmp_files   5594
Created_tmp_tables  465690
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  1092598
Handler_delete  3527
Handler_discover    0
Handler_external_lock   2692797
Handler_mrr_init    0
Handler_prepare     0
Handler_read_first  3291451
Handler_read_key    94909022
Handler_read_last   597
Handler_read_next   584655958
Handler_read_prev   2289457
Handler_read_rnd    22585333
Handler_read_rnd_next   1039026278
Handler_rollback    9
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  3001107
Handler_write   13348212
Innodb_buffer_pool_dump_status  Dumping of buffer pool not started
Innodb_buffer_pool_load_status  Buffer pool(s) load completed at 200915 14:25:20
Innodb_buffer_pool_resize_status   
Innodb_buffer_pool_pages_data   120170
Innodb_buffer_pool_bytes_data   1968865280
Innodb_buffer_pool_pages_dirty  6
Innodb_buffer_pool_bytes_dirty  98304
Innodb_buffer_pool_pages_flushed    190308
Innodb_buffer_pool_pages_free   2047
Innodb_buffer_pool_pages_misc   8839
Innodb_buffer_pool_pages_total  131056
Innodb_buffer_pool_read_ahead_rnd   0
Variable_name   Value   
Innodb_buffer_pool_read_ahead   35380
Innodb_buffer_pool_read_ahead_evicted   842
Innodb_buffer_pool_read_requests    1230004513
Innodb_buffer_pool_reads    156026
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   9391121
Innodb_data_fsyncs  13083
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    3170914816
Innodb_data_reads   223898
Innodb_data_writes  197504
Innodb_data_written     3536651776
Innodb_dblwr_pages_written  24887
Innodb_dblwr_writes     2505
Innodb_log_waits    0
Innodb_log_write_requests   14091
Innodb_log_writes   3345
Innodb_os_log_fsyncs    4539
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   10433536
Innodb_page_size    16384
Innodb_pages_created    1257
Innodb_pages_read   193533
Innodb_pages_written    190308
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    0
Innodb_row_lock_time_avg    0
Innodb_row_lock_time_max    0
Innodb_row_lock_waits   0
Innodb_rows_deleted     3519
Innodb_rows_inserted    20139923
Innodb_rows_read    1686296409
Innodb_rows_updated     4347
Innodb_num_open_files   30356
Innodb_truncated_status_writes  0
Innodb_available_undo_logs  128
Key_blocks_not_flushed  0
Key_blocks_unused   107124
Key_blocks_used     47
Key_read_requests   4732
Key_reads   47
Key_write_requests  20
Key_writes  17
Locked_connects     0
Max_execution_time_exceeded     0
Max_execution_time_set  0
Max_execution_time_set_failed   0
Max_used_connections    12
Max_used_connections_time   2020-09-15 16:00:04
Not_flushed_delayed_rows    0
Ongoing_anonymous_transaction_count     0
Open_files  328
Open_streams    0
Open_table_definitions  17900
Open_tables     31428
Opened_files    2817777
Opened_table_definitions    2090652
Opened_tables   880851
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_index_stat_lost  0
Performance_schema_locker_lost  0
Performance_schema_memory_classes_lost  0
Performance_schema_metadata_lock_lost   0
Performance_schema_mutex_classes_lost   0
Performance_schema_mutex_instances_lost     0
Performance_schema_nested_statement_lost    0
Performance_schema_prepared_statements_lost     0
Performance_schema_program_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_table_lock_stat_lost     0
Performance_schema_thread_classes_lost  0
Performance_schema_thread_instances_lost    0
Performance_schema_users_lost   0
Prepared_stmt_count     0
Qcache_free_blocks  0
Qcache_free_memory  0
Qcache_hits     0
Qcache_inserts  0
Qcache_lowmem_prunes    0
Qcache_not_cached   0
Qcache_queries_in_cache     0
Qcache_total_blocks     0
Variable_name   Value   
Queries     1668933
Questions   1166498
Rsa_public_key  -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQE...
Select_full_join    8768
Select_full_range_join  91
Select_range    41874
Select_range_check  0
Select_scan     67251
Slave_open_temp_tables  0
Slow_launch_threads     0
Slow_queries    10
Sort_merge_passes   9043
Sort_range  86052
Sort_rows   15315042
Sort_scan   51047
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    18446744073709551615
Ssl_ctx_verify_mode     5
Ssl_default_timeout     0
Ssl_finished_accepts    0
Ssl_finished_connects   0
Ssl_server_not_after    Sep  4 04:04:27 2030 GMT
Ssl_server_not_before   Sep  6 04:04:27 2020 GMT
Ssl_session_cache_hits  0
Ssl_session_cache_misses    0
Ssl_session_cache_mode  SERVER
Ssl_session_cache_overflows     0
Ssl_session_cache_size  128
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     
Table_locks_immediate   590
Table_locks_waited  0
Table_open_cache_hits   2872174
Table_open_cache_misses     880851
Table_open_cache_overflows  849416
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  3
Threads_connected   7
Threads_created     12
Threads_running     4
Uptime  7541
Uptime_since_flush_status   7541

Thanks,
 
Last edited: