Why I ditched MySQL and put Bob on DynamoDB

Over the past few years, I have all but given up on using MySQL whenever I need to write a database, just because I don’t like having to be careful about how many queries per second I can conduct without worrying about how much load the database server can handle at once and I have never liked the Oracle licensing arrangements.
monitor-2728124_1920.jpg

Over the past few years, I have all but given up on using MySQL whenever I need to write a database, just because I don’t like having to be careful about how many queries per second I can conduct without worrying about how much load the database server can handle at once and I have never liked the Oracle licensing arrangements.

When I first started working on Bob years ago, I meant it to only be ran off of a single Raspberry Pi 3 which worked well for a while back when all Bob was doing was sending me a text message every eight hours and notifying everyone if I didn’t respond. During that time, the Raspberry Pi was serving as both the web server (Apache) as well as the database server (MySQL) which worked great at the time. However, as I started adding more and more functionality to Bob such as location tracking, social media checks, etc the MySQL service on the Raspberry Pi would crash, but even worse, it would silently crash so I could go a few days without noticing it was down. Not exactly what you want from a program that is supposed to be monitoring your life 24/7.

I eventually worked around the issue by lightening the load on how much data it stored and how often the scripts queried the data but it was a half ass fix.

So last month, when I decided to seriously work on Bob again, the very first decision I made was to ditch MySQL, and overhaul the backend to run exclusively on Amazon’s DynamoDB.

Why DynamoDB?

First of all, I’ve always been a huge fan of Amazon Web Services. Secondly, it’s a complete unmanaged solution. You create the tables and add the data and Amazon manages the rest.

When you create your tables, you specify how many reads and writes per second that each table needs to perform at and Amazon automatically spreads your data across how ever many servers that’s needed to support the specified throughput (we’ll come back to this).

By default, all tables only run off of solid state hard drives making it incredibly fast.

No Licensing Fees

Although it’s not open source, there are no licensing fees to use DynamoDB, you only pay for the hardware consumption that you provision per hour. For instance, if you know that your application will be heavily used during business hours during weekdays, you can provision to have more throughput during those hours and only get charged for those hours. Which brings me to my favorite feature of DynamoDB, auto scaling.

Auto Scaling

As I mentioned before, when you setup your tables, you get to specify how many reads and writes per second you want each table to handle but the truly beautiful part is its completely dynamic meaning you can adjust it throughout the day.

With old database models, you would typically have to think of your maximum expected capacity and run at that maximum capacity 24/7. With DynamoDB, you can specify a minimum and maximum read and write capacity and it will automatically scale up or scale back down based on usage.

For example, I have all of my tables set with a minimum read and write capacity 5 per second and a maximum of 10000 and have a rule where if at anytime, if 50% of my capacity is being used, double my capacity up until 10000.

What does this mean for Bob?

The more data we can collect, the more accurate algorithms can be.

Let me give you one example, on my personal account I have my computers reporting to Bob my usage based on mouse movement. When I had MySQL powering the backend, I had to build in a sleep mechanism where when it detected mouse movement, the computer would report it to Bob and then put itself to sleep for sixty seconds because otherwise, it would try to report to Bob multiple times per second and eventually overwhelm the database server. Now we can collect data up to milliseconds instead of minutes.

When you think of everything that’s either putting data into Bob, or taking data out: everything from computer check ins to motion sensor data to scripts that run every minute, on the minute 24/7, you start to see why MySQL started getting so overwhelmed.

So with the database bottleneck almost completely removed, I look forward to throwing as much data as possible into Bob!

Day One Script for Ubuntu

Screenshot from 2017-10-06 00-32-25.png

Since I have been developing a lot more lately, I haven't been on my iPad as much. So when I pulled up Day One today, I was kinda sad to see that my last entry was over a month ago.

I know Day One has a macOS app but my preferred development enviornment is Ubuntu running on a Lenovo Thinkpad. Since I took the day off from working on Bob today, I decided to write a short python script to write journal entries directly from my Ubuntu terminal.

It was actually pretty easy, I just created a recipe in IFTTT to send a Webhook request and turn the information that I pass to it into a journal entry. The whole thing took probably forty five minutes. I still have to add things like image support but that will be another night.

I'm just happy I can quickly add text entries at the moment! 

#!/usr/bin/python

import requests

print "Entry title:"
title=raw_input()

print "Content:"

contents = []

while True:

    try:
        line = raw_input("")

    except EOFError:
        break

    contents.append(line)

text = '\n'.join(contents)

print "Entry: \n\nTitle: "+title+"\n\nContent: "+text+"\n\nSave entry? "

text = text.replace('\n', '<br /><br />')

save=raw_input()

if (save == "Y" or save == "y"):
    r = requests.post("https://maker.ifttt.com/trigger/journal_entry/with/key/xxxxx", data={'value1': title, 'value2': text})

RClone: Tool for accessing your cloud via CLI

Most of the Linux servers that I setup and/or maintain I typically run in CLI (command line interface) mode and don’t even bother to install a GUI just to preserve disk space, memory usage and CPU usage. However, it can be a pain to access my cloud storage services. Then a few years ago, I discovered RClone. RClone lets you perform basic file and directory functions such as copy, move, delete and sync. I primarily use my Google Drive but RClone supports:

  • Google Drive
  • Dropbox
  • OpenStack Swift
  • Amazon S3
  • Google Cloud Storage
  • Amazon Drive
  • OneDrive
  • Hubic
  • Backblaze B2
  • Yandex Disk
  • SFTP
  • FTP
  • HTTP

One of the reasons why I love RClone is how simple it is to use:

  • Download it from http://rclone.org/downloads
  • Extract it using tar -xvf rclone*
  • Run it for the first time using ./rclone config
  • It will then prompt you for the connection type, I choose 8 for Google Drive
  • Then it will prompt you to name the connection. I keep it simple by just naming mine “g”
  • It will then ask you to authenticate
  • Confirm your settings and you’re done

Now to use it, for example to copy a file to your cloud storage, just use:

/path/to/rclone/rclone copy localfile (name of your storage):/

In my case:

/home/pi/rclone/rclone copy myfile.ext g:/

Quick Tip: Use screen to keep your application running in the background in terminal

Every once in a while you need to keep an application running in an SSH session after you logout. To be able to logout and log back in later, you’ll need to install an application called “screen”.

To install screen on Ubuntu/Debian type apt-get install screen in the terminal.

To use screen:

  • Start the application by using the command: screen application
  • Get the application running how you want it to run and then type CTRL + A + D to return to the terminal
  • Now you can do whatever else you need to in terminal or logout of SSH while your application runs in the background
  • To get back to your application, simply log back in via SSH or open terminal again and type screen -r and your session will come back up