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.

RGB colour correction

So I’ve been playing with different colour correction techniques for my LED’s.

So here’s a simple one. Square it. Well ok, square it then divide through by the upper value (square and scale.) So:
unsigned int value = (i*i)/255;

which sort of works. But it’s a little jumpy in the lower regions, as the mapping looks like,
(23,27)->2, ect….

The next I’m trying is something called Quadratic interpolation using¬†a Lagrange Polynomial. BIG words for a curve of best fit. It works by generating a polynomial through a number of pre determined points. I have worked it out using 1->1, 255->255, and then 128->c. I can then change c, effectively changing the correction to the colour. Only problem is that it gets a little complex. So the maths formula is;

(-cx^2 +256cx -255c+128x^2 -16639x+32640)/(16129)

I attempted and failed to implement this as Arduino isn’t really the right platform for doing this kind of arithmetic. It works in excel ‘tho and produces a lovely graph.

mapping curves

A graph showing various possible curves

The Black curve in the graph is actually the square and scale method above. The other are for various choices of c.

So I quickly mapped the first 32 values i->x to the 32 LEDs I have and they are quite linear, from there on I found every bank to be quite similar. Which led me to the conclusion that for small values a one to one correspondence was ideal, then a kind of s curve would be ideal. I tried a few cubics but they were quite complex to implement and even tried a quartic.

So back to a basic square and scale I think for now.

Hyper-V and Vlans

So I’ve now got 2 identical hosts running server 2012 datacenter, with VM’s on both. Now it’s time to get them talking to one and other. Simple right, just sit them all on ‘external’ virtual switches and they can use the physical network to talk to each other. Great except… I don’t really want my test environment hooked upto my live environment, not do I want to change my existing DHCP server settings or have two on the same broadcast domain!

So, Hyper V can use VLANs to segregate traffic. This is the way forward for me.

  1. 1 create a new VLAN for the test environment,
  2. tag the ports for the VLAN,
  3. put all VMs on the new VLAN…
  4. oh and change the management VLAN in the virtual switch manager for the hosts…

Groovey now I can talk to the host over my LAN (on VLAN 1) and the VMs can talk to each other on VLAN 200, but they as VLANs are completely segregated in terms of broadcast domains (for DHCP) and packets. Now I can set up a (virtual) router to bridge between them. PFSENCE time.

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:
comment = the timelapse folder
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 or what ever takes your fancy, but with a .sh extension.

Step Two – populate file with the following:
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/ 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.

Oopsie, server failure

So just turned my server on to play with Hyper V stuffage, and hummmm…. it wont pass POST. It beeps telling me there’s a RAID failure, but then dies before showing me the diagnostics page that it would normally show… HuMmM!

SO, pop the hood, thinking maybe it’s a¬†RAM failure and I knocked a CPU heat sink… which then fell off. Think I’ve found the problem then.

Oh, and then I’ve run into this again….

SLAT error :/

SLAT error :/

Time to break out Server 2012 DC edition again….Which also doesn’t like the server, time to break out the server 2008 disk, which supports SLAT, but didn’t require it. Might have better luck with it. Only problem there is that Server 2008 doesn’t support live migration and replication in the way that 2012 does :/.

*As a heads up for anyone wanting to play around – save some time and do some research on your processor. It needs to be 64bit, support Intel VT-x and Intel VT-x with Extended Page Tables (EPT). The best place to find this information is in the Intel ARK if you are using Intel proc’s. Have a look at my proc’s here.¬†*

Hyper-V = Hyper Frustrating

So it’s about time that I pick up with my Microsoft certs, seeing as I’m now back in IT. I should have done them a long time ago, but nvm. Now’s a good a time as any.

The first thing when embarking on a challenge like this is to set some goals: I want to be MCSE certified within a year, and I don’t want to spend too much time (or money) doing it. I also want it to be useful to me (and look good on my CV). Choosing the right track is also important. Have a look at a recent road map here.

I’m choosing the server route. I’m yet to decide which of the MCSE’s within the route I want, but the first three exams are all the same. ¬†So 410,411 and 412 to start with. I have a good grounding in MS server tech, having built domains from scratch in the win2k days and working for a length of time in a win 2k3/xp+win7 environment, so I don’t think it’ll be too difficult to move the core skills into the 2012 ¬†exam direction.

First things first…

… I’ll be using a set of study books 410, 411 and 412, they don’t really cost that much and should be a good resource for the exams. I’ll also be using various Microsoft Virtual Academy resources as I go. I can’t tell you which ones as I haven’t had an in depth look as of yet.

The most important thing I’ll be using in my test environment. This will be key, as there’s no better way to understand how things work that to get stuck in making them work, breaking them, and fixing them again. It’ll also allow me to experiment with redundancy in a safe way, as I really do like pulling network cables out and watching things fail over.

My test environment will be made from a few machines sitting on my home network. The first is my old workstation, 8GB RAM with Intel Core 2 Quad Q9300. I downloaded my images from MS tech-net for 2012 and 8.1 evals (and the tech previews for Win 10) and proceeded to install Hyper-V Server 2012 as a hypervisor for my VM’s which I shall use.

FAIL#1 So Hyper-V Server is a server core install with no GUI. Which is great if you know all of the powershell, but I find it easier with a GUI. So, another OS is needed. (I could have left Win7 on the machine and use virtualbox, which is my preferred desktop VM solution, but I thought Hyper-V would be a more interesting learning curve.) So onto Win8.1….

FAIL#2 win 8.1 – no slats?, i’ll be honest I don’t really know why this is in place, nore do I really care. It’s only because i’m running old hardware (I did say I was doing this on the cheap). Off to Windows Server 2012 datacenter it is then.

FAIL#3 managed to install Win Serv 2012 DC, but processor visualization was disable in the bios. Turned it on, and voila – a working Hyper-V host.

Lets go virtual

Got it up and running surprisingly quickly too.1 server os choice

So I managed to get a few VM’s up and running, and all talking to each other via a Hyper-V virtual switch (bridged to my home LAN also). WINNING!

New server time

So my other host will be a dual quad core Xeon X5355 ¬†system with 32GB of RAM. NICE. It’s an older Intel 1u rack server with 8 SAS drives in the enclosure (although I only have one actual drive). ¬†Anyway here’s some Windows experience numbers.


Pretty good on the CPU and RAM, which is always good ūüėÄ – the mobo is Intel S5000pal¬†

Here’s some pics.

a pic of the server with the lid off and some RAM removed for testing purposes. Check out all the fans.

a pic of the server with the lid off and some RAM removed for testing purposes. Check out all the fans.

The BIOS loading page.

The BIOS loading page.

CPU cores :)

CPU cores ūüôā

SAS drive performance.

SAS drive performance.

Intel s5000pal motherboard

Intel s5000pal motherboard

additional SATA ports on the motherboard

additional SATA ports on the motherboard

a funky shot of the SAS drive bays

a funky shot of the SAS drive bays

code for parallel bit bang

I’ve just uploaded a video showing an update to driving ws2801 strings. This code is for driving¬†them in parallel serial data strings, rather than sequential serial.

#define numleds 32
#define ledsperline 8
#define updatespeed 0
//this code is written for 4 lines of serial data!
int RED[numleds];
int GREEN[numleds];
int BLUE[numleds];
//line pin def's
int clockpin1 = 2;
int dataPin1 = 3;
int clockpin2 = 4;
int dataPin2 = 5;
int clockpin3 = 6;
int dataPin3 = 7;
int clockpin4 = 8;
int dataPin4 = 9;
//setup stuff
void setup()
//pinMode(13, OUTPUT);
pinMode(clockpin1, OUTPUT);
pinMode(dataPin1, OUTPUT);
pinMode(clockpin2, OUTPUT);
pinMode(dataPin2, OUTPUT);
pinMode(clockpin3, OUTPUT);
pinMode(dataPin3, OUTPUT);
pinMode(clockpin4, OUTPUT);
pinMode(dataPin4, OUTPUT);
for (int i = 0; i < numleds; i = i++)
void loop() {
for(int l=0;l<32;l++){
for(int l=0;l<32;l++){
for(int l=0;l<32;l++){
//main loop
void loop1() {
//assumption made that blue is on...
//fade to purple
for (int i=0;i<256;i++)
{for (int l=0;l<32;l++)
//fade to red
for (int i=0;i<256;i++)
{for (int l=0;l<32;l++)
//fade to yellow
for (int i=0;i<256;i++)
{for (int l=0;l<32;l++)
//fade to green
for (int i=0;i<256;i++)
{for (int l=0;l<32;l++)
//fade to cyan
for (int i=0;i<256;i++)
{for (int l=0;l<32;l++)
//fade to blue
for (int i=0;i<256;i++)
{for (int l=0;l<32;l++) {GREEN[l]=255-i;}
//update function
void updatestring() {
for (int i =0;i<ledsperline;i++)
shiftOut1( BLUE[i],BLUE[i+ledsperline],BLUE[i+2*ledsperline],BLUE[i+3*ledsperline]);
shiftOut1( GREEN[i],GREEN[i+ledsperline],GREEN[i+2*ledsperline],GREEN[i+3*ledsperline]);
shiftOut1( RED[i],RED[i+ledsperline],RED[i+2*ledsperline],RED[i+3*ledsperline]);
for (int i =0;i<ledsperline;i++)
//shiftOut1(dataPin2, clockpin2, BLUE[i+ledsperline]);
//shiftOut1(dataPin2, clockpin2, GREEN[i+ledsperline]);
//shiftOut1(dataPin2, clockpin2, RED[i+ledsperline]);
for (int i =0;i<ledsperline;i++)
//shiftOut1(dataPin3, clockpin3, BLUE[i+2*ledsperline]);
//shiftOut1(dataPin3, clockpin3, GREEN[i+2*ledsperline]);
//shiftOut1(dataPin3, clockpin3, RED[i+2*ledsperline]);
for (int i =0;i<ledsperline;i++)
//shiftOut1(dataPin4, clockpin4, BLUE[i+3*ledsperline]);
//shiftOut1(dataPin4, clockpin4, GREEN[i+3*ledsperline]);
//shiftOut1(dataPin4, clockpin4, RED[i+3*ledsperline]);
//shift out fn
void shiftOut1(uint8_t val1,uint8_t val2,uint8_t val3,uint8_t val4)
uint8_t i;
for (i = 0; i < 8; i++) {
digitalWrite(dataPin1, !!(val1 & (1 << (7 - i))));
digitalWrite(dataPin2, !!(val2 & (1 << (7 - i))));
digitalWrite(dataPin3, !!(val3 & (1 << (7 - i))));
digitalWrite(dataPin4, !!(val4 & (1 << (7 - i))));
digitalWrite(clockpin1, HIGH);
digitalWrite(clockpin2, HIGH);
digitalWrite(clockpin3, HIGH);
digitalWrite(clockpin4, HIGH);
digitalWrite(clockpin1, LOW);
digitalWrite(clockpin2, LOW);
digitalWrite(clockpin3, LOW);
digitalWrite(clockpin4, LOW);

28BYJ-48 Stepper motors + ULN2003 Drivers

So I recently brought 5 cheap stepper motors and driver boards from eBay for like a fiver + pnp.¬†Bargain. I wasn’t expecting much at all but I have some small projects in mind and wanted to see if these would fit the bill.

5 steppers and drivers

5 steppers and drivers

Here’s a quick video of me attempting to get them working.

And here’s my code,¬†cheap_steppers, inspired by this page¬†on Instructables.

Have Fun!