How much of a table is in use?

I’ve been working on a web based monitoring tool for a while now and it’s getting close to completion. A thought crossed my mind, and be prepared it’s a wild one.

WHAT IF: my site get’s so popular I hit the upper limits of the index or field size of the primary keys of the tables?

Well clearly – it’d break and then i’ld get am email or something telling me to fix it and I would go and change the field types to allow more… problem solved…. but it’s not very nice! and it means downtime! (ironically not something I want for a tool designed to measure downtime!)

Anyway – I’m building in monthly, weekly and daily email routines to let me know certain bits of information regularly without me having to log in, and I thought why not add something that tells me how much I have used? So I did.

Here’s my solution (but not my code):

get a list of tables,
for each table,
get table name,
get primary key,
get field type and convert to human friendly numbers,
get number of rows in use,
work out usage in %,

Half an hour using google and a bit of common sense I now have a page that shows me the stats of the primary key usage and now I can add them to the email routines.

Raspberry PI Time-Lapse Camera Fun.

So I’ve decided that I wanted to do some time-lapse photography for my next project, and my brother wont let me borrow his GoPro…. I could go and buy one – but that’s no fun.

Anyway, I’ve had a Raspberry PI  for some time now (sitting under my bed), and a camera module for it (thanks for an awesome christmas present sis), so I thought… surely that’s possible, no?

Well – have a look at this for how to make it… super simple.

Following those instructions:
install camera, sudo raspi-config then enable camera,
using the following command to take a pic, raspistill -o cam.jpg

And to do multiple shots: raspistill -o frame%04d.jpg -tl 5000 -t 60000

This will store a series of still images in the current directory. And then to turn these into a video file you will need to do the following;
sudo apt-get update (requires network/interenet access)
sudo apt-get install libav-tools
avconv -r 10 -i frame%04d.jpg -r 10 -vcodec libx264 -crf 20 -g 15 timelapse.mp4

wahhoooo – except it didn’t work for me the first, or second time… Meh, I’ll just take the pics and worry about video assembly later.

Anyway – there’s many great resources on how to call the raspistill command/function on the net.

You can re-size the images as they are captured by adding height and width to the call:
raspistill --width 1280 --height 960 -o cam.jpg
And the following link has more about the raspistill command.

Moving on, I want to be able to access my files remotely so time to install samba,
sudo apt-get install samba samba-common-bin
sudo leafpad /etc/samba/smb.conf

and add the following:
[timelapse]
comment = the timelapse folder
path=/home/pi/timelapse
browsable = yes
writable = yes
only guest = yes
create mask = 0777
directory mask = 0777
public = yes

Which will make the folder publicly available on the network (you might need to make the directory first too with mkdir). You will also need to open the file browser with root access and change the permissions on the folder so that ‘other’ is read and write (otherwise you wont be able to remove the files so the PI doesn’t become full (I only have an 8GB SD card in mine!)).

Great so we have a folder and a way of capturing images… but, I want my time-lapse to be over weeks, not hours, so I’m using an interval of 4 mins between frames.

Step One – create a bash script to take the picture. Create a new file called picture.sh or what ever takes your fancy, but with a .sh extension.

Step Two – populate file with the following:
#!/bin/bash
DATE=$(date +"%Y-%m-%d_%H%M")
raspistill -n --width 1280 --height 720 -o /home/pi/timelapse/$DATE.jpg
and SAVE IT. You can probably work out that this takes a picture, and stores it in the folder we created above (or another folder if that takes your fancy) with a date_time stamp. Once saved you will need to modify the properties so that the file is executable.

Step Three – Cron job time. Open a terminal prompt up and type “sudo crontab -e” to get to the cron job editor. Add the following:
*/4 * * * /home/pi/timelapse/picture.sh then write and close (ctrl O and ctrl X)

As you guessed this will fire the bash script we created above once every 4 mins. Search for “cron every x minutes” for more details on how to create and edit cron jobs.

Brill. Job Done… Well… Job almost done. Now I just need to make sure the PI doesn’t fill up and run out of storage and then I will need to make all of the images into a time lapse video.

And here’s the result.