- Login to the AWS EC2 console
- Find the AWS MySQL my.cnf file and add new configurations
- Restart the AWS MySQL server
- Check the status of the AWS MySQL server
- MySQL Update Issues After reducing the resources
- References
Amazon Web Services provides a free tier where you can use the AWS services free for 12 months but there is some capping applied on each service. You can use up to a threshold and if you breach the threshold then you have to pay. Generally, people deploy WordPress on the AWS Free tier and start their blogging journey. MySQL and WordPress both are installed on t2.medium EC2 instance and people see a crash problem when there is an increase in traffic.
Login to the AWS EC2 console
You need to log in to the AWS EC2 console and open the terminal
Find the AWS MySQL my.cnf file and add new configurations
The my.cnf file is the MySQL configuration file where you can add new configurations related to MySQL. The my.cnf file will be present in /etc/mysql/my.cnf
vim /etc/mysql/my.cnfUnder the [mysqld] section add the below configuration and save the file
[mysqld]
performance_schema = off
key_buffer_size = 16M
tmp_table_size = 1M
innodb_buffer_pool_size = 1M
innodb_log_buffer_size = 1M
max_connections = 25
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 128K
thread_stack = 128K- The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. As the AWS free tier has very low resources hence we will turn off the performance_schema
Restart the AWS MySQL server
systemctl restart mysqlCheck the status of the AWS MySQL server
systemctl status mysqlThe status should reflect the active (running)
After these configurations, your MySQL will not crash
MySQL Update Issues After reducing the resources
It may happen that your Operating system will try to update MySQL automatically and it may fail because of low resources. On my AWS machine, the MySQL database crashed and the error was
root@ip-172-31-19-158:/var/log/mysql# tail -f error.log
2024-06-13T15:26:15.959612Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.37-0ubuntu0.20.04.3) (Ubuntu).
2024-06-13T15:26:49.834252Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.37-0ubuntu0.20.04.3) starting as process 134805
2024-06-13T15:26:50.002380Z 0 [Warning] [MY-000080] [Server] option 'innodb-buffer-pool-size': signed value 1048576 adjusted to 5242880.
2024-06-13T15:26:50.028681Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-06-13T15:26:50.945816Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-06-13T15:26:52.691269Z 4 [System] [MY-013381] [Server] Server upgrade from '80036' to '80037' started.
2024-06-13T15:27:06.070083Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2015, 2024, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- NOTE: This needs to be replicated within the sys_config_clean.inc file INSERT IGNORE INTO sys.sys_config (variable, value) VALUES ('statement_truncate_len', 64), ('statement_performance_analyzer.limit', 100), ('statement_performance_analyzer.view', NULL), ('diagnostics.allow_i_s_tables', 'OFF'), ('diagnostics.include_raw', 'OFF'), ('ps_thread_trx_info.max_length', 65535); ' failed with error code = 1436, error message = 'Thread stack overrun: 12656 bytes used of a 131072 byte stack, and 160000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.'.
2024-06-13T15:27:06.073770Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2024-06-13T15:27:06.074106Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-06-13T15:27:07.715135Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.37-0ubuntu0.20.04.3) (Ubuntu).Error suggests there is a thread stack overrun
'Thread stack overrun: 12656 bytes used of a 131072 byte stack, and 160000 bytes needed.so I increased the config of thread_stack in my.cnf from 128K to 256K and then restarted the MySQL server. This time the update happened smoothly and once the update was done then I again reduced the thread stack from 128K to 256K and restarted the server and everything worked fine
Raj Ranjan