AWS Aurora restarted due to an error Out Of Memory
Recently, there was a problem that Aurora database had been restarting at the same time on daily.
Since at the time batch with a huge query had processed, so we guessed it was the cause of restarting Aurora.
We asked AWS Technical Support the reason of the problem, then we recieved below answer.
We think your guess is almost correct. According to your Cloud Watch, we guess that Aurora restarting is maybe caused by the batch process.
In default, 75% of the memory on Aurora is assigned at innodb_buffer_pool.
This buffer is mainly used for query caches, so other uses like table caches, log buffers, memory used in each connection is assined at 25% remained memory.
Therefore you're not able to use the full of 25% memory just for your queries, actually it's less than 25%.
In this case, Due to the memory size used by your batch was exceeded the actual enabled memory size, OOM error occured.The conceivable actions for this problem are like below.
- Decrease the "innodb_buffer_pool_size" from default(75%) to 50~60%.
Set {DBInstanceClassMemory*2/4} to "innodb_buffer_pool_size" in parameter group console.
- Upgrade DBInstance class.
- Optimize your query.
In these, we reccomend the first action for this time.
In this time, We took the first action for the problem. And we haven't face the OOM problem since then.
We learned a lot from AWS Technical Support, so we appreciate them far too much.
The hard bug in MacOS High Sierra
WTF\(^o^)/ﾅﾝﾃｺｯﾀｰ
There's a hard bug in MacOS High Sierra if multiple users are accepted to login.
In above, anyone seems to be able to get a root privilege of the mac without any specific technical skills.
Anyway you'd better to see this page for a detail.
www.wired.com
The person who first discovered this bug had definitely to be freak out, I guess.
Modified behavior of jquery plugins [Counter-Up] to effect at only first viewed.
I think this is the most fantastic plugin for increasing the number text dynamically.
Counter-Up/jquery.counterup.js at master · bfintal/Counter-Up · GitHub
But it's been getting a little old at recent, so there's a problem that the option named "triggerOnce" doesn't work.
The detail of the problem is that the count up effect occurs every time when display is scrolled into the element even though I set a "triggerOnce" option. Anyway, I hope that the effect occurs at just only the first time if I set that option into a initializer, so I fixed the problem to put below code into the jquery.counterup.js.
// Start the count up
setTimeout($this.data('counterup-func'), $settings.delay);
$this.attr("data-counterup_finished", true); // Add
And
var counterUpper = function() {
// Add start
if ($this.data("counterup_finished") == true) {
return false;
}
// Add end
var nums = [];
var divisions = $settings.time / $settings.delay;
var num = $this.text();
...
It's the end.
Make replication between external Mysql and RDS-Aurora.
My external Mysql, which means non-AWS-RDS, has a large capacity records in it, and have been replicating between master and slave which are both external Mysql.It's difficult and too annoying about its slowness to dump data and to import them to Aurora.Instead of mysqldump, I tried to use percona-xtrabackup which is a third-party tools to migrate Mysql Database from S3, but restoring to Aurora with percona-xtrabackup didn't work since mysql version wasn't supported.(Accoding to error message, restoring by S3 data is accepted ver5.6 of source Mysql database.)
So I've given up to using percona-xtrabackup, and I'll show you the snippets how to make replication between external Mysql and RDS-Aurora with mysqldump.
# Refer to this for details.
http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html
This is my environments.
CentOS Linux 7.2.1511 mysql 5.7.16 innobackupex 2.4.8
First of all, in source Mysql you need to add a replication user with Aurora.
mysql > CREATE USER 'repl_aurora'@'aurora_host' IDENTIFIED BY '<password>'; mysql > GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_aurora'@'aurora_host' IDENTIFIED BY '<password>';
And then you make backup as gz archived file. At the same time you need to set option "--master-data=2" to put out MASTER_LOG_FILE and MASTER_LOG_POS statements.
$ MYSQL_PWD="xxxxxxxxxxxx" mysqldump --opt --all-databases --events --default-character-set=binary --master-data=2 -u myuser | gzip > ./backup.sql.gz
After the backup is finished, you need to search MASTER_LOG_FILE and MASTER_LOG_POS statements like this.
With zgrep command, you can grep in gz archived file as it is.
$ zgrep -i "CHANGE MASTER TO" backup.sql.gz > grepped.txt
Import backup data to Aurora.
$ zcat ./backup.sql.gz | mysql -u root -p -h aurora_host da_name
Set master database info to Aurora with prepared stored procedure.
Then 'mysql bin file' and 'position' are able to be fetched from grepped.txt.
mysql > CALL mysql.rds_set_external_master ('source mysql host', port, 'repl_aurora', 'aurora password', 'mysql bin file', position, 0);
Start replication.
mysql > CALL mysql.rds_start_replication;
Watch replication status.
SHOW SLAVE STATUS \G;
What a awesome awk!!
awk!! You're so fantastic!!
I'd love to use "awk" which is a linux command easy to format some kind of text to whatever you want.
For instance as below, you're able to get the most recent loadaverage.
$ uptime
00:08:33 up 134 days, 7:25, 2 users, load average: 0.03, 0.03, 0.05
$ uptime | awk -F'[ ]+' '{print $11}' |sed -e 's/,//'
0.03
Besides, you're also able to do like this.
$ echo ansible_inventory.file
192.168.33.10 ansible_ssh_user=vagrant ansible_ssh_port=66666 ansible_ssh_pass=xxxxxxx
$ cat ansible_inventory.file | awk -F'[ =]' '{print "sshpass -p "$7" ssh -p "$5" "$3"@"$1}'
sshpass -p xxxxxxx ssh -p 66666 vagrant@192.168.33.10
Using awk makes me pretty comfortable!
My recent favorite Podcast
I'm going to introduce my recent favorite Podcast programs for Learning English.
Frequently, English is going to be required when we're in a middle of some programming.
When we're reading docs, watching movies that someone speeches about technology, or stuff like that, there is a case in we have to understand them in English. These below are my favorite programs to Learn English.
- Bilingual News
Mami and Michael, the personality on this program, talk about some interesting topics.
This have been published every week.
- 台本なし英会話レッスン
Japanese Sota and American Nate talk about English grammar, vocabulary, and some episode that how to learn English.
https://ja-jp.facebook.com/daihonnashi/
.htaccessのリダイレクトなどでつかう正規表現の%とか$とか
.htaccessで以下の様にサブドメを継承し、かつ最初のディレクトリをカットするということがしたくて色々やってハマったのでメモ。
http://xxx.from.com/yyy/zzz...
を
http://xxx.to.com/zzz...
にリダイレクトしたい。。。
結論、こんな感じになりました。
RewriteEngine on
RewriteCond %{REQUEST_URI} ^/yyy.*$
RewriteCond %{HTTP_HOST} ^(.*).from.com$
RewriteRule ^yyy/(.*)$ http://%1.to.com/$1 [R=301,L]
RewriteCondのキャプチャパターンをRewriteRuleで使う場合、%Nで指定する。
mod_rewrite - Apache HTTP Server Version 2.4
RewriteRule backreferences: These are backreferences of the form $N (0 <= N <= 9). $1 to $9 provide access to the grouped parts (in parentheses) of the pattern, from the RewriteRule which is subject to the current set of RewriteCond conditions. $0 provides access to the whole string matched by that pattern.
RewriteCond backreferences: These are backreferences of the form %N (0 <= N <= 9). %1 to %9 provide access to the grouped parts (again, in parentheses) of the pattern, from the last matched RewriteCond in the current set of conditions. %0 provides access to the whole string matched by that pattern.<<
なお、RewriteCondの順番を逆にすると%1に値が引き継がれないのですが、これは複数のRewriteCondの中で上から順に下に継承されるからだそう。
こちらのサイトが参考になります！
.htaccess で RewriteCond の後方参照 ← Neo Inspiration
