Help with MySQL's maximum memory usage which is dangerously high

Anyone can help me to check this MySQL configuration? I have a VPS 32GB RAM - 8 vcpu and running 1 ecommerce.

MySQLTuner returns me:

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 140.0M, or always use indexes with JOINs)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 407)

my.cnf settings are:

key_buffer_size = 256M
join_buffer_size = 140M
tmp_table_size   = 80M
max_heap_table_size = 80M
thread_pool_size = 24
innodb_buffer_pool_instances = 6
innodb_buffer_pool_size = 6G
innodb_log_file_size = 768M
table_open_cache = 4000
skip_name_resolve = 1

max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections         = 200
#table_cache            = 1024
#thread_concurrency     = 40

tmp-table-size          = 32M
max-heap-table-size     = 32M

query_cache_limit       = 4M
query_cache_size        = 0
query_cache_type        = 0

Additional information

```
-------- Storage Engine Statistics -----------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 76.4M (Tables: 102)
[--] Data in MyISAM tables: 1.3G (Tables: 229)
[OK] Total fragmented tables: 0

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 19h 15m 53s (19M q [124.090 qps], 69K conn, TX: 21G, RX: 6G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 31.5G
[--] Max MySQL memory : 33.8G
[--] Other process memory: 650.8M
[--] Total buffers: 6.3G global + 140.8M per thread (200 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.5G (30.04% of installed RAM)
[!!] Maximum possible memory usage: 33.8G (107.34% of installed

For other people (not this OP because he's using MyISAM tables), in MySQL 5.7, assuming you're using mostly INNODB tables, you can see how much memory is currently being used by running this query:

SHOW ENGINE INNODB STATUS

You can see the maximum amount of memory your instance could need for buffering ONLY by running this query:

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_allowed_packet
+ @@max_connections * ( 
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@net_buffer_length
    + @@net_buffer_length
    + @@thread_stack
    + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

• net_buffer_length twice for 'connection buffer' and 'result buffer'

Is that maximum amount of memory more than your server has available? Especially in AWS Aurora this will cause your DB to reboot regularly.

StackExchange Database Administrators Q#218250, answer score: 3