We have a t2.medium instance on AWS' RDS platform (4gb, 2xCpu). We're using MariaDB 10.1.19 with a single database, ~120 tables and ~2gb of data in total.
After running for about a week we get this error, at first only some requests, then on the heavy requests and then it starts affecting more queries. Eventually resulting in a reboot.
Php (PDO) error:
General error: 5 Out of memory (Needed 262680 bytes)
Mysql (from error.log)
2017-06-14 10:55:29 47531169896192 [ERROR] mysqld: Out of memory (Needed 262680 bytes)
This shows the 'freeable memory' on the instance (the big spike being when we rebooted).
We're were using default mysql config. We ran mysqltuner it mentioned:
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
join_buffer_size (> 512.0K, or always use indexes with joins)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=1G) if possible.
innodb_buffer_pool_instances(=2)
So we've since tried increasing the join_buffer_size
(to 512k) and increased the key_buffer_size
(to 16M) but didn't seem to make any difference. Not sure if the error is the result of the OS having no ram available, or the mysql process itself.
Any suggestions? (Thanks)
EXPLAIN SELECT ...
? I assume so, since you said "optimize," but... you know, just checking. :)