getting out-of-memory errors after upgrade

[dave peterson]

hello everyone, i have a Linode server running LAMP and it gets 20-40 users at a time (according to google-analytics), it has been running for several years and was in bad need of upgrading, so i upgraded to debian 9, and php 7.1, and mariadb 10.1.41, but ever since i've been getting out-of-memory errors that eventually crash mysql (and the server too, if left long enough). The server has 64gb RAM.

i'm hoping there is some configuration setting that will help. Mysqltuner tells me to increase innodb_buffer_pool_size to 5G, but mysql doesn't startup if i do. It also tells me to increase the join_buffer_size to >6M, but again mysql won't start if i do.

i'm building a new server with debian10/php7.3/mariadb 10.3.18, so i'm not looking to make the old server's config "perfect", i really just want to get it stable so i'm not woken up in the middle of the night!

-------------------------------
error example:

[ERROR] mysqld: Out of memory (Needed 1046536 bytes)

-------------------------------
and here are the settings that have changed:

binlog_format = MIXED
delay_key_write = ON
expire_logs_days = 10
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 2G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
join_buffer_size = 6M
key_buffer_size = 256M
key_cache_block_size = 1024
log_bin = 1
log_error = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes = 1
log_warnings = 2
long_query_time = 1
low_priority_updates = 1
max_allowed_packet = 128M
max_binlog_size = 1G
max_connections = 175
max_heap_table_size = 1G
myisam_recover_options = BACKUP,FORCE
myisam_use_mmap = ON
open_files_limit = 10000
performance_schema = OFF
query_cache_limit = 2M
query_cache_min_res_unit = 4096
query_cache_size = 128M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
skip_external_locking = ON
skip_name_resolve = ON
slave_net_timeout = 1800
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
sort_buffer_size = 4M
sync_binlog = 1
table_definition_cache = 2000
table_open_cache = 5000
thread_cache_size = 200
thread_stack = 256K
tmp_table_size = 1G

-------------------------------
and here is output from mysqltuner:

[0;32mOK[0m] Currently running supported MySQL version 10.1.41-MariaDB-0+deb9u1
[0;32mOK[0m] Operating on 64-bit architecture
------ Storage Engine Statistics
[0;34m--[0m] Status: [0;32m+Aria [0m[0;32m+CSV [0m[0;32m+InnoDB [0m[0;32m+MEMORY [0m[0;32m+MRG_MyISAM [0m[0;32m+MyISAM [0m[0;32m+PERFORMANCE_SCHEMA [0m[0;32m+SEQUENCE
[0;34m--[0m] Data in MyISAM tables: 2G (Tables: 592)
[0;34m--[0m] Data in MEMORY tables: 0B (Tables: 1)
[0;34m--[0m] Data in InnoDB tables: 5G (Tables: 261)
[0;32mOK[0m] Total fragmented tables: 0
------ Performance Metrics
[0;34m--[0m] Up for: 42m 54s (236K q [92.045 qps], 25K conn, TX: 2G, RX: 106M)
[0;34m--[0m] Reads / Writes: 57% / 43%
[0;34m--[0m] Binary logging is enabled (GTID MODE: OFF)
[0;34m--[0m] Physical Memory : 63.1G
[0;34m--[0m] Max MySQL memory : 5.6G
[0;34m--[0m] Other process memory: 120.9M
[0;34m--[0m] Total buffers: 3.5G global + 12.2M per thread (175 max threads)
[0;34m--[0m] P_S Max memory usage: 0B
[0;34m--[0m] Galera GCache Max memory usage: 0B
[0;32mOK[0m] Maximum reached memory usage: 4.7G (7.43% of installed RAM)
[0;32mOK[0m] Maximum possible memory usage: 5.6G (8.91% of installed RAM)
[0;32mOK[0m] Overall possible memory usage with other process is compatible with memory available
[0;32mOK[0m] Slow queries: 5% (14K/236K)
[0;32mOK[0m] Highest usage of available connections: 55% (97/175)
[0;32mOK[0m] Aborted connections: 0.00% (0/25583)
[0;32mOK[0m] Query cache efficiency: 40.6% (73K cached / 182K selects)
[0;32mOK[0m] Query cache prunes per day: 0
[0;32mOK[0m] Sorts requiring temporary tables: 0% (6 temp sorts / 7K sorts)
[0;31m!![0m] Joins performed without indexes: 14
[0;31m!![0m] Temporary tables created on disk: 41% (3K on disk / 7K total)
[0;32mOK[0m] Thread cache hit rate: 99% (97 created / 25K connections)
[0;32mOK[0m] Table cache hit rate: 99% (1K open / 1K opened)
[0;32mOK[0m] Open file limit used: 12% (1K/10K)
[0;32mOK[0m] Table locks acquired immediately: 98% (124K immediate / 125K locks)
[0;32mOK[0m] Binlog cache memory access: 100.00% ( 65625 Memory / 65625 Total)
------ Performance schema
[0;34m--[0m] Performance schema is disabled.
------ ThreadPool Metrics
[0;34m--[0m] ThreadPool stat is enabled.
[0;34m--[0m] Thread Pool Size: 16 thread(s).
[0;34m--[0m] Using default value is good enough for your version (10.1.41-MariaDB-0+deb9u1)
------ MyISAM Metrics
[0;31m!![0m] Key buffer used: 12.9% (34M used / 268M cache)
[0;32mOK[0m] Key buffer size / total MyISAM indexes: 256.0M/682.7M
[0;32mOK[0m] Read Key buffer hit rate: 100.0% (8M cached / 3K reads)
[0;31m!![0m] Write Key buffer hit rate: 11.6% (2K cached / 1K writes)
------ AriaDB Metrics
[0;34m--[0m] AriaDB is enabled.
[0;32mOK[0m] Aria pagecache size / total Aria indexes: 128.0M/1B
[0;32mOK[0m] Aria pagecache hit rate: 99.9% (4M cached / 2K reads)
------ InnoDB Metrics
[0;34m--[0m] InnoDB is enabled.
[0;31m!![0m] InnoDB buffer pool / data size: 2.0G/5.2G
[0;32mOK[0m] InnoDB buffer pool instances: 2
[0;34m--[0m] InnoDB Buffer Pool Chunk Size not used or defined in your version
[0;32mOK[0m] InnoDB Read buffer efficiency: 99.99% (349510240 hits/ 349559551 total)
[0;31m!![0m] InnoDB Write Log efficiency: 38.11% (41436 hits/ 108727 total)
[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 67291 writes)
------ TokuDB Metrics
[0;34m--[0m] TokuDB is disabled.
------ Galera Metrics
[0;34m--[0m] Galera is disabled.
------ Replication Metrics
[0;34m--[0m] Galera Synchronous replication: NO
[0;34m--[0m] No replication slave(s) for this server.
[0;34m--[0m] This is a standalone server.
-------- Recommendations
General recommendations:
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
join_buffer_size (> 6.0M, or always use indexes with joins)
innodb_buffer_pool_size (>= 5G) if possible.

thanks!