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.

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!

PHP Tip: Auto-Prepending your Custom Functions Globally

There are just some things that you like done your way. Ever since I’ve been developing with PHP, I’ve kept a file with my own custom developed functions that I know I may want to use again. Everything from a custom send email function that will report bounces back to me to simple one parameter file uploads to encryption functions and so on.
I got into the habit about ten years ago to maintain this master function file and then on every web server that I have under my control, I set the flag in the php.ini file, auto_prepend_file to my master functions file that way all of my custom functions are available to me in every PHP file on that system without me having to do anything.

Bob: The Bot I Created to Watch Out for Me

I first started working on Bob a few years ago. How it began was I lived alone and had my own apartment and had my business. It was not unusual for my family or my best friends not to hear from me for weeks at a time especially if I was knee deep in a development project. However, because of this I realized that if something were to happen to me, it could have been a while before anybody realized it (after all, I do have cerebral palsy so I am more prone to accidents).
So I created Bob. The first task I programmed Bob to do was to check on me every eight or nine hours by sending a message to my phone. If I didn’t respond within so many hours, it would send a text message to my family and my best friends saying, “it’s been x hours since I had checked in, could someone please check on him?” This worked well. Too well. The problem with it was if I would occasionally sleep in, I wouldn’t hear the request to check in and it would send a lot of false alarms.

What I did to solve the “bot that cried wolf” problem was reporting common daily activities to Bob. Whenever I did one of the following activities, Bob would count it as a “check in”, resetting the clock:

  • Post something on Twitter or Facebook
  • Using my phone’s location, check in every time I left and came back to my apartment

Since I was using an Android phone while I was developing the upgrades, I was able to go deeper into the integrations such as:

  • Every time I sent a text message
  • Every time I made a phone call
  • Every time I answered a phone call

All of those activities counted as a “check in” (those activities don’t count now that I switched back over to iOS since Apple doesn’t allow developers access to the phone or messages portion of their devices).

I also expanded it where if I’m at either a hospital or a police station in Bakersfield for over ten minutes (to prevent it from going off by just driving by), it will send me a message asking me if I’m okay? If I don’t respond within ten minutes, it will alert everyone where I’m at.

The next portion of the system that I want to get around to developing is adding motion sensors so Bob automatically knows when I wake up in the mornings and when I go to bed at night.

However, my goal is to expand its functionality than just being a monitoring system. I’m not quite sure how yet but I want to make it smarter to either automatically handle a handful of digital tasks or do a lot of the repetitive steps for me.

My Web Server Backup Script

I thought I would share this simple script that I wrote for my web server to back it up every night.
This simple python script:

  • Creates a temporary directory to copy everything to
  • Copies everything in my home directory
  • Copies everything from my root web directory
  • Copies my php.ini file (I hate resetting up my php.ini file)
  • Dumps all of the databases that I may have in MariaDB into a nice SQL dump file
  • Tars everything up into a single file
  • Then uses one of my favorite Linux CLI tools, RClone to copy the tar file to my Google Drive
  • Cleans up the temp directory that it created
#!/usr/bin/python

import os, time

f=str(time.time())
c="cwfraziercom"
p="/var/backup/"

os.popen("mkdir "+p)
os.popen("cp -r /root "+p)
os.popen("cp -r /var/www/ "+p)
os.popen("cp /etc/php.ini "+p)
os.popen("mysqldump -u root -pPassword > "+p+"sql.sql --all-databases")
os.popen("tar -zcvf /var/backup.tgz "+p)
os.popen("/root/rclone/rclone copy /var/backup.tgz g:/Backups/WebServers/"+c+"/"+f+"/")
os.popen("rm -rf "+p)
os.popen("rm -rf /var/backup.tgz")

I then just schedule the script to run every night by scheduling it as a cron job.

Also, just in case you’re wondering, the reason that I assign the file name as the value of the current epoch time is so each backup file has a unique file name. I keep all of my backups forever so the last thing I want is overlapping file names and accidentally overwriting old backups.