Experienced problems with our RDS instance

0
votes

we experienced problems with our RDS instance.

RDS stops running. RDS are in state of "green"(on the AWS console) but we cannot connect to the RDS instance.

Cloud Logs we found following errors:

2018-03-07 8:52:31 47886953160896 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer... InnoDB: Set innodb_force_recovery to ignore this error.

2018-03-07 8:52:32 47886953160896 [ERROR] Plugin 'InnoDB' init function returned error. 2018-03-07 8:53:46 47508779897024 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer... InnoDB: Set innodb_force_recovery to ignore this error. 2018-03-07 8:53:46 47508779897024 [ERROR] Plugin 'InnoDB' init function returned error.

When we tried to reboot RDS instance its take almost 2 hours to reboot. After rebooting its working fine again!.

Can someone help us to know the root cause of this incident.

1
amazon-web-servicesamazon-rdsfailover
It's hard to tell without knowing about your workload or instance size. Best guess would be you need more ram or a smaller buffer pool. Maybe both. – Steve Buzonas
[Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer... That message is about an attempt to recover from a problem... [ERROR] Plugin 'InnoDB' init function returned error. ...but it didn't recover successfully. The actual problem would be earlier in the logs. – Michael - sqlbot

1 Answers

0
votes

As t2.small provides 2G of RAM. However you might be knowing, most DB engines tend to use up 75% of the memory for caching purposes such as queries, temporary tables, table scans to make things go faster.

For our Maria DB engine, following parameters are by default set to below pre-optimized values :

  1. innodb_buffer_pool_size (DB instance size *3/4= 1.5 Gb)
  2. key_buffer_size (16777216 = 16.7 Mb)
  3. innodb_log_buffer_size (8388608 =8.3Mb)

Apart from that the OS and the RDS Processes will also use some amount of RAM to do their own operations. Hence to summarize, around 1.6 Gb approximately is utilized by DB engine and the actual usable memory which will be getting after taking out these values innodb_buffer_pool_size, key_buffer_size, innodb_log_buffer_size will be around 400 MB.

Overall a decrease in your Freeable Memory as low as ~137MB. As a result, Swap Usage increased drastically in the same time period to 152MB approximately.

FreeableMemory was quite low and there was a high swap utilization. Further, due to the memory pressure ( insufficient memory and high swap usage), RDS internal monitoring system was not able to proceed with host communication which in turn resulted into underlying host replacement.