This blog is about compiling the 1.7 release of Typica and the Phidgets libaries for Linux. Neal is currently working and hoping to release 2.0 sometime in the future and all this will drastically change.

Linux is a fantastic community driven operating system. It is Free and Open Source Software (FOSS), meaning someone cannot charge for the software and also provide you the source code to do what you want with it.

Typica is also FOSS. Neal provides binaries that will run in the Mac OSX world and Windows world so that  you do not have to compile it. And this works for probably 90% of the people.

I like Linux for many reasons — the freeness, the freedom, the community driven development, and also the ability to re-use hardware that might not run the ever larger and slower Windows or Mac OSX operating systems. Sometime the cast away hardware from those operating system spheres still has good life left in it and it doesn’t have to be junked.

I was using the Mac Mini for my database server and am still using an Dell laptop for my Linux compile environment and roast-logger. It is a very old system, while this XT has a touch screen it is only a 32-bit CPU. If you follow my guide you will have to keep this in mind and figure out some of the 64-bitness differences. Feel free to leave those as comments to this article.

I am using Linux Mint, a debian/ubuntu based system.

Install Packages

You will need to update your system to the latest release. Following that, install some additional packages.

sudo apt-get update
sudo apt-get upgrade
(possibly reboot)
sudo apt-get install libqt4-sql-psql libqt4-dev libqt4-dev-bin \
 libusb-1.0-0-dev libc-dev-bin gcc-multilib g++-4.6 g++ \
 libqtwebkit-dev libqtwebkit4

 

Source Setup

You need a directory for source code if  you do not already have one. Make a directory called something like “src” in your home directory unless you have a preference already setup. In this directory you will setup two trees — one for Phidgets libraries and one for Typica itself.

Phidgets Library Compiling

Go ahead and open a terminal window and CD into your source (or src) directory. You need to download the latest  Phidgets source into this directory. I like to browse to their page for Linux support and copy the URL and then use “wget” to fetch the file to my current directory on the system where I will compile. It saves a step or two of having to copy it later.

The current directory is here, but that could change and you can use Google or whatever search engine you like best in the future… Once you have downloaded the file, extract it, cd into the new directory, and compile the libaries. Since you have installed all the packages listed above, it should just be as simple as the instructions on the Phidgets site, also included below:

wget http://www.phidgets.com/downloads/libraries/libphidget.tar.gz

tar xfvz libphidget.tar.gz

cd libphidget-2.1.8.20151217

./configure

make

sudo make install

If your Phidgets source directory looks differently than mine you will need to update that in the instructions below for Typica where we modify the project to include the headers for Typica.

Typica Compiling

You should “cd” up one directory to the “src” parent directory. Now download the Typica (probably 1.7) source code from the Git site. If you hover over the “Download Zip” link this should be a URL to a “master.zip”; copy the URL and use it with wget again. After downloading the zip we will uncompress it which will create many directories. We will “cd” into the first level and then into the src sub directory and we will create a backup file of one file that we will change.

 wget https://github.com/N3Roaster/typica/archive/master.zip


unzip master.zip


cd typica-master/src


cp Typica.pro Typica.pro.orig

Now we are ready to get nitty gritty. In previous blogs I was able to get around the Vi vs. Emacs vs. Nedit debate by having you use an echo command to append new statements to a file.  This appears to hold true. So even though I would really love to make sure you embrace the power of the Vi side, I will again use the echo-append statement in my examples for brevity’s sake. It is very important that the you add three separate and distinct lines to the file.

echo "include(3rdparty/qextserialport/src/qextserialport.pri)
INCLUDEPATH += /home/kevin/src/libphidget-2.1.8.20151217/
LIBS += -lphidget21
" >> Typica.pro

You are now ready to build the Makefile and then compile the program. First you execute the “qmake” program to create the Makefile, and then you run the “make” program to compile the code into executable binaries.

qmake

make

Bam! You are ready to execute the new compiled program. Unfortunately because of the libraries you will need root privilege again, so you must execute the “Typica” binary with sudo in order to run it as yourself but with root privileges.

sudo ./Typica

If everything worked successfully, the program is now running and asking how to connect to your database.

 

 

The inevitable “oompf” happened much sooner than expected: the Mac Mini linux server died.

I hadn’t even fully tested my backup method! Nothing like trial by fire to make the theory real.

If you don’t want to read the whole article and just want to know, “did it work?” Yes, it worked perfectly.

I had a very old Dell laptop (older than my Mac Mini, actually) that I was setting up for my Typica compile environment. I also used it to verify some of my commands in the previous articles for installing and setting up the postgres database under linux. The only thing it hadn’t done was actually create a database or connect to the cloud. To sum up the situation, it was perfectly poised to now have my database restored, though far from ideal.

The steps were simple:

  1. Install PostgreSQL database software
  2. basic configuration of postgresql (network connectivity)
  3. create the typica database instance
  4. create local user and grant it access to rights to the typica database instance
  5. restore the database-dump file from the cloud

Steps 1-4 are outlined in my initial blog about creating the database. The only thing missing is running the command to restore the data from the dump file.

First, let me explain about the dump file. The dump file is not a copy of the database as it runs. The dump file is actually a text file containing SQL statements that will completely re-create the database. Restoring from these dump files is fairly simple and straight forward. A very large file may take some time, but mine was extremely short.

Once you have created the database and granted your user the permissions to the database, you would need to download your dump file from your cloud backup and copy it to your new database system. I simply navigated to the latest dumpfile in my Google Drive and downloaded it to my regular Windows system. I used PSCP (from Putty) to secure-copy the file dump to my new database host.

Back on the database host I exited my psql command where I created the database and setup the user privileges to it.

I ran three commands. The first two commands were verify I had “read” permissions by listing the directory contents and a “tail” on the dump file to make sure the postgres user had read privilege to the file. The second command is the postgres command to restore the file. Here is the session nearly verbatim:

postgres@roastlogger ~ $ psql
psql (9.3.12)
Type "help" for help.

postgres=# create database typica;
CREATE DATABASE
postgres=# create user roasty with password 'coffeecoffecoffeecoffee';
CREATE ROLE
postgres=# grant all privileges on database typica to roasty;
GRANT
postgres=# \q

postgres@roastlogger ~ $ ls ~kevin
Desktop Downloads Music Public Templates Videos
Documents gdrive Pictures src typica.export.201604100718

postgres@roastlogger ~ $ tail ~kevin/typica.export.201604100718
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

postgres@roastlogger ~ $ psql typica < ~kevin/typica.export.201604100718
SET
SET

And I cut the output short for you. It re-created the database in a very short time.

I then launched my recently compiled Typica program and it immediately asked for how to connect to a database since the previous database is kaput. I input the configuration items and was back in the running.

I think the Mini died from  at the power supply during a recent storm. It was over six years old so I will be replacing it with a new model QNAP home-nas. The QNap will be able to run Samba, PostGreSQL, and Plex with no issues (I also bought ram to upgrade it to max supported RAM).

Computers need backups. Hardware will fail. Are you prepared for the disaster? Will you know how to recover the important data?

I like backing up my data to Google Drive. I bought a Chromebook years ago, and have been a Google user from the Gmail invite days, so I have around 120GB of free cloud-based storage at my disposal. If I ever fill it up, their rates are pretty low to expand. So, given this, I would really sleep better if my database for Typica, running on a six year old Mac Mini, was exported every few days to my Google Drive for safe keeping.

Here’s a funny thing. Google is a firm believer in Linux. They use Linux on their hardware, the Googler’s desktops reportedly mostly run Linux, the Chromebook is Linux, and Android is essentially Linux. But yet, there is no official Google Drive client from Google for Linux. Seriously. This is so odd to me.

However, a Googler has contributed to an open-source product that is a useful method of uploading database exports to Google Drive.

Exporting the Database

In the previous blog for setting up the database I had you configure your user account for special privileges for backups. This is so you could run the pg_dump command like so:

 pg_dump typica > typica.export.$(/bin/date +\%Y\%m\%d\%H\%M)

You should try this now and make sure it works without access errors to the database. This is the basic command for exporting the database and it needs to operate cleanly before we can proceed. Feel free to use more or less on the export file to examine the SQL commands that will rebuild your database after a disaster.

In the example I created the destination filename with a date string. The date string is created automatically at run time because the system executes Yet Another Command to capture the current date in a convenient sortable format: YYYYmmddHHMM. This is specified with the symbols “$(” preceding the date command, and a closing parens following the command.

Google Drive client

The software package I am using to send my backups to the cloud is called GDrive. It is coded with GO, the google language. You can obtain the source and some binaries here. I punted on building it from source for the moment. I downloaded the binary put it in /usr/local/bin.

Once you have the executable in your path, begin with getting the authorizations setup between your local computer accounting using gdrive and your Google Account.

Authorization

You can execute “gdrive help” to see a lengthy list of operations you can perform with the client. It is very full featured, just perhaps a little unwieldy for complex operations. Fortunately for what we want, it will be pretty easy to keep it simple.

To get the Authorization setup between Google and your computer execute the gdrive command with any operation besides “help”. I recommend to start with “list”.

user@local~ $ ./gdrive list
Authentication needed
Go to the following url in your browser:
https://accounts.google.com/o/oauth2/auth?access_type=offline&client_id=36713-farfarfarfapipick.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&response_type=code&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&state=state

Enter verification code:

The first time you run this it will prompt you with a URL and wait for your return an authorization code to allow it to proceed. Follow the instructions on screen.

Setup a Google Drive Subfolder

A “gdrive list” should list a confusing array of folders and documents in your Google Drive. It will be something like this:

0B4UCLDdeCDdisUdN33dtHeRealC0dE  typica.export.201603230645 bin 64.4 KB 2016-03-23 06:45:02

You cannot operate on files and folder with the logical pretty names you see in the browser or other clients. You have to use the confusing code that precedes the logical information. By default it will only list 30 items and they seem to be those most recently modified.

Go to Google Drive in your browser and create a folder that you wish to contain your exports — something descriptive like “Typica Database Exports”. Go into that folder and observe the address URL. The non-English code following “folders” is what you need to specify for the location for your files that you wish to upload.

https://drive.google.com/drive/folders/3BuSyB33sW3nt2SaMEfL0w3r1Day

You can copy it from the address line or by using “gdrive list”.

Now we will attempt to upload the file you created in your pg_dump demo into your specially prepared folder. We specify the folder using the command line option “-p” and the code following it. The filename to upload comes at the end of the command line.

gdrive upload -p 3BuSyB33sW3nt2SaMEfL0w3r1Day typica.export.201603172133

The file should show up in the folder in your browser in short order.

Export to GDrive through pipeline

I think the ideal way to export the database to the cloud is to not even write it to disk before uploading it. If you write it to disk you will just have to delete it. The export to pipeline works for now. I don’t know how large it will grow to be and if this pipelining method will be a problem with memory in the future but that is something you will need to consider and I may have to revisit.

In the first export I had you perform I had you redirect the output to a file through the greater-than symbol. If you did not specify this symbol it would have printed to screen.

We can use another type of redirection called a pipeline, or special character “|”. This tells the Linux system to send the output to another executable as input. The gdrive executable has an option for the upload to specify the STDIN input from a previous command; this is the dash (“-“) in my example below. We still use the “-p CODE” options to specify the destination folder, and then we still follow it with the name you want this file to called when it is created in the cloud.

One last item before you schedule this in the cron scheduling facility. We will want to redirect both STDOUT and STDERR (2>) to a file. If we do not, the local mail facility on the Linux system will end up with many extraneous messages from the executions of the command.

Test this final command from your terminal. When it executes cleanly, with nothing written to the display, schedule it in cron:

/usr/bin/pg_dump -d typica |/usr/local/bin/gdrive upload - -p 3BuSyB33sW3nt2SaMEfL0w3r1Day  typica.export.$(/bin/date +\%Y\%m\%d\%H\%M) >/home/kevin/db_backup.log 2>&1

 

The Typica software was thoughtfully built around the commercial-grade relational database (RDB) system PostgreSQL. Think of Oracle RDB without the thousands of dollars in licensing because it is open-source like Typica and thousands of other software packages. Open Source means the developer(s) make their code completely available so anyone can download it and compile it to run. In so doing the original authors hope these new people will contribute to code by finding and fixing bugs, suggest and/or write new features to improve, documentation, and perhaps even make packages ready to install available to other potential users.

The PostgreSQL software can run on just about any operating system: Windows, Macintosh OSX, Linux (including many home Network-Attached Storage products), Solaris, BSD, and the list could go on. This is due to both the open-source nature of the software and some investment from corporate sources that like the software alternative RDB.

I picked Linux. I will have been using Linux for 20 years come late 2016. It is completely open-source like Typica and PostgreSQL.

I picked my old Mac Mini (2006) to run the software. Apple hardware is generally top-notch. The OSX operating system used to be one of the best but lately I have been a little disenfranchised with direction that Apple has gone with it. I am referring to not just the color scheme, but some major changes that have been step backwards in terms of interoperability. If the OS is going to be hampered with bugs, the GUI ugly, and slow down my old system to molasses-in-January speeds…” well,” I said, “I might as well run Linux and at least get the speed back”. I loaded the Mint distribution last year and then loaded Plex Media Server to share the music library to the Onkyo amp. It is not very useful to me and this platform will make a great hosted database for the home network.

Why Hosted Database?

Why should anyone care about a “hosted database”? A hosted database means the database is running on a different system than the software that will use the datbase. The two systems will communicate over a network connection. This type of setup is not completely necessary since the database could run on the system running the software. However there are some distinct advantages for having a hosted database.

I choose to have a hosted database so that I can use the software from a couple of different systems and still access the same data. For instance, my fancy new Surface Pro should probably stay in the house away from the fire in the roaster and possible tumbles to the concrete floor. So it should not be doing the roast-logging, but it would be very useful for capturing my tasting notes.

So I will use an old laptop to start roast logging. It has been around a few blocks so it could die during this process and I will very likely need to replace it with something like a netbook. Or a retired thin client. Having a hosted database means I do not have to worry about getting the data off a broken system, but rather can install on a new system, connect to the database, and keep on truckin’.

Install and configure PostgreSQL

I will run you through the steps of installing PostgreSQL on a Linux system. If you choose Windows to host the system you will have a very different experience but you may still find this educational. Likewise a Mac host experience will be different though there will be some similar concepts.

If you are using Mint (linux distribution) or other similar Debian package based distro you should find these example instructions are easy to follow. If you are using a Redhat RPM based system you will find things very similar. However you will have to do a little digging to find the transliteration for those package names and “yum” commands versus my apt-get commands.

Install Packages

The system will need to have the PostgreSQL packages installed. The list of package would likely look like this:

  • libpq5:amd64
  • postgresql-client-common:all
  • postgresql-client-9.3:amd64
  • postgresql-common:all
  • postgresql-9.3:amd64
  • postgresql:all

But it is much simpler than. You can install the main package and all the dependencies with a single command. Make sure the system has internet access and from a terminal session enter the command:

"sudo apt-get install postgresql"

Keep your terminal open, you will need it for the following sections as well.

Allow Network access

The software will be secure by default and not allow incoming network connections because that is a risk for a system not properly secured. We will have to accept that risk and configure the database to listen and allow remote connections.

In your terminal, issue the command to change your working directory to “/etc/postgresql/9.3/main/”. Use the tab “auto-complete” ability as your version of PostgreSQL could be update from my example.

Save original CONF files!

I always recommend saving config files before making changes. I don’t always remember and when I don’t I wish I had… So here is your reminder — make an “original” file before making changes. In this particular directory this can be done with a single loop-style command:

sudo for i in *.conf; do cp $i _orig.$i ; done

This will loop around each file ending with “.conf” and make a copy starting with “_orig” as a filename prefix.

Modifying pg_hba.conf

Next we will edit the “hba” file. This file contains how this host is allowed to share it’s network with PostgreSQL. Before we can edit the file we must know what your subnet is for your local network. This could get dicey for you if your local subnet is completely IPv6. Hopefully if it is you know this and do not need this guide for the details but the general information.

Find your IP Address and subnet information

So assuming your have no idea what I’m talking about, run this command on your host system to see what your IP address is:  “ip -4 addr show”. The output will look similar to below:

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.1.109/24 brd 192.168.1.255 scope global eth0

The information we are looking for is associated with device #2 and not the localhost address. The second line contains the IP address (192.168.1.109) and the broadcast (192.168.1.255). This tells us that we will use 192.168.1.0 when we edit the file, next. By using the zero we will specify the entire group of addresses expected, from 1 to 254.

Edit the file

If you have edited files on unix before use your favorite editor and add the following line to the very bottom of the file.

host all all 192.168.1.0/24 md5

If you have never used a file editor in Unix I would love to introduce you to VIM (VIM is not incorrect Roman numerals for 994). But that would stretch this already length blog post into something else entirely. Let’s use this shortcut of using echo to append to the file. For this to work you MUST absolutely use two Greater-Thans, and you should modify the subnet to match yours if it is different. The magic editorless command is:

sudo echo "host all all 192.168.1.0/24 md5" >>pg_hba.conf

One last thing to note. If you have an overly complex network you might need to repeat the network discovery and add an additional line if the clients that are connecting to your database show an entirely different network than the host. And pray that the two networks are routing to each other.

Modify the postgresql.conf

This file must also have one line added to it. This line is simple and intructs the database to listen on any network interface available.  The line can appear at the bottom of the file, so the magic echo command will save you have to learn a Unix file editor if you have not already made a choice for VIM (the right choice) or a wrong choice.

sudo echo " listen_addresses='*' " >>postgresql.conf
Restart the database software
sudo service postgresql restart

Setup User access

The database will run as user “postgres” (more than likely). I am going to recommend that you connect to the database as a user called “roasty” or at least “typica”. However, you could simplify and run make this connection as yourself with your own credentials. It will save one small step with the backups later on, but it is small step and for good security practices I recommend you do not. Just make sure you document the password somewhere.

Create local user (if not yourself)

Choose your username now and run the following command (substituting your username for “roasty” every time you see it in this guide.

sudo useradd roasty

Database operations

We will use the sudo and su commands to become the database operator which is probably postgres. Then we will issue commands to create the database “typica” and grant access to the “roasty” account and yourself, assuming you will be doing the backup operations under your account.

Become the database operator and then execute the “psql” process which allow you perform the necessary operations within the database software.

sudo su - postgres
psql

You are now currently “in” the PostGreSQL database software. You should feel like you zipping around on a lightcycle right now. No? Huh… Right, then, first step is to create a database called “typica”. Be sure to end each line with a semi-colon before pressing enter. If you forget just press the semi-colon on the new line and then press enter again. It still works.

create database typica;

You worked up a sweat creating a database I’m sure, so take a break and have some coffee. No, seriously, the hardest part is definitely passed us so you deserve a break. When you come back we will make sure the user(s) for the software and your backups have a password and grant the necessary privileges to the database you created.

create user roasty with password 'passwordwhichImsureisaboutcoffee';
grant all privileges on typica to roasty;
CREATE USER yourusername WITH PASSWORD 'evenbetterpassword';
GRANT ALL PRIVILEGES ON DATABASE typica TO yourusername;
grant USAGE on SCHEMA public to yourusername;
grant USAGE on SCHEMA typica to yourusername;
grant USAGE on SCHEMA typicafeatures to yourusername;
ALTER ROLE yourusername WITH Superuser;

After the last line you can exit by pressing Ctl-D.

As you can see the “yourusername” had to get a lot of extra roles so that he could perform the database export for backups. This could be done as the database operator (postgres) to save this step and limit access to yourusername but what I have in mind for backups is easier managed under your user account.

Database is Ready!

The database is ready for the software to connect! Remember this hostname or IP address, the name of the database, and the password and username your have specified and go start the software. It will ask  you for this information.

 

It’s been a while

Things have been super busy for the last year and not much noteworthy or mentionable to blog about in either my coffee or tech world. It’s been too boring to write about.

OK that is totally not true. There has been the excitement of fresh lots of coffee from Sweet Maria’s, and then there was the night when I had several people over for a roasting demo (will definitely do that again soon).  AND then there was our visit to the Diria Coffee Processedora in Hojancha, Guanacaste, Costa Rica. I definitely should have blogged about that. That was awesome.

But that was months ago, I don’t think I could capture all the excitement and joy of that visit. I’m sorry…

So here we are, and in the words of Buckaroo Banzai, “no matter where you go, there you are.”

So let’s start over. I’m Kevin, I’m a little nerdy and techy and love coffee. So the next several blogs (I’m envisioning several) will combine both Coffee AND Tech as I explore the Typica program for roast logging. This will be a win-win of instead of just writing about Coffee OR Tech.

Roast Logging and Profiling

I’ve always wanted to have more data from my roasts. I believe that I need to track a roast through temperature and activities in order to understand it. This is the beginning of making a roast profile. A roast profile is simply a guide to turning the beans brown in the best way possible. A profile could be simple, like a flat even climb in temperature until end of roast; or a profile can be complex with a couple of cycles of heating and reducing heat. The different approaches to  cooking the beans can change the beans flavor and improve enjoyment.

The reason is simple– the process of cooking any food create chemical reactions. The chemical reactions break down or create chemicals in the food items, coffee included. Some chemicals have a taste that may be desired and others perhaps we want to avoid. I’m far from an expert on this process but I believe that data logging can assist me in my hobby anyway.

My goal is to capture the data from roasting so that I repeat a great roast or avoid making a mistake again. This type of logging is extremely important to commercial operations who have a lot at stake every time they put some of their investment into the fire. They want to make sure they don’t ruin a batch, and always if possible, improve on a previous batch.

Home roasters can benefit from this as well. We may not have the same investment quantity of beans and as much at risk, but we can benefit from logging.

I have filled half a dozen notebooks with handwritten notes for the last nine years. I’ve also tried several methods of computer or tablet logging. They are ok, but they are difficult to refer to with any precision when it comes to repeatability. And the real challenge with any method of roast-logging or note-taking is also tracking cupping and tasting notes.

I first found Typica a number of years ago while googling. I was interested in the software, it was opensource and used a real relational database– postgres! These are exciting things to a nerdy tech-minded homeroaster! At the time I investigated it a little and even conversed with Neal the author, but decided not to invest in the expensive device it required. Neal required any hardware to be open-source friendly and at that time that was pretty much only the National Instruments expensive data logger device. I couldn’t justify the expensive hardware for hobby operations.

I tried a DMM (digital multimeter) with temp input and optical RS-232 for connecting to the computer. Logging this data was something, but cleaning it up to make a graph in Excel or Google Spreadsheets proved to be very time consuming and difficult. The paper logs continued until I began using an Android tablet and an app called Coffee Roaster.

The Android app was almost good enough for recording data manually and making graphs. I configured it for two temperature trackers and used one to track my burner levels and the second for actual temperatures seen at the DMM. I lost data several times due to upgrades and corrupted database. Through it all I kept at least basic written logs to at least keep track of my numbering ( I did roast #396 in the RK+Grill last week).

Typica Again

Fast forward to late 2015 and I looked at Typica again because of my recent ennui with paper and the android app. Imagine my surprise and joy to find that it can now use a Phidgets device which costs a fifth less than the NI devices. This is hobbiest territory.

I will begin using this but it will take a bit to setup the way I want. This is not quite as simple as just install and make a few configuration likes the Android app. This is a little more complex. But it doesn’t have to be scary. I hope my next few blogs can make it less complex for someone else.

I am using an old Mac Mini computer for a NAS and database host. I was going to use my new Windows computer to run the application but as I began experimenting and thinking about the risk of fire and heat on it, I think I might use an older laptop and/or netbook to do the logging. The logging computer may run Linux– stand by and read on as I discover the path forward.

I’ve already found a limitation that many home roasters will face and will work out a change in code to fix it. Neal created the software for commercial roasters. Commercial roaster can log the bean temperature which is ideal since they have a non-moving piece of the drum. They can put the temperature probe right into the beans. The RK drum and grill setup has one full drum that rotates completely, no fixed piece to mount a probe in the beans. The best I can do is to log the temperature in the chamber outside the drum. This at least gives me data about how hot it is with the burners going. The limitation I hope to patch sets the graph scale at a top of 500F, plenty of head room for a bean probe. But outside temps? Much too low, it should be double that.

Looking forward

As you can tell, I’ve already done a little bit of setup on this so I have an idea of what I can write about. Neal documents many typical scenarios on his site. However, I see where I can provide some additional information in the next few blog entries.  I will write with a little more detail about setting up a hosted/shared database, including how to do backups of the database. I have my backups being copied to my Google Drive account directly, so from database to cloud copy for offline and offsite. I will also attempt to document and write about setting up the compiling environment and my attempts to provide a more home-roaster friendly scaled graph.

Beyond that I imagine I will start trying to document the tastes of the roasts and see if I can improve my roasts through better profiles. We’ll see where this goes.

Oracle VM Repository Alarms

Posted: February 11, 2015 in Oracle VM, perl, tech
Tags: , ,

The Oracle VM platform for virtualization has some amazing gaps in functionality– see previous post about the disk size change problem for starters. But you want to know another big hole in functionality? Alarms! I would love to get alerts and alarms on certain conditions sent to my email or phone.

My embarrassing case in point: the other day a few thin-sized disks grew and filled up a repository. The virtual disks then started spewing SCSI errors. This could have been prevented if 1) the the disks were thick instead of thin, but even bettter would be option 2) some alarms about operating conditions such as repository utilization and performance utilization of the hosts.

So I this is a little script I wrote, you might want to sing it note for note, and be happy. I will eventually get some performance statistics put into my Teamquest environment so I can have performance graphs and alarms from that monitoring. But it’s been a while since I defined a User Table database in it…

In the meantime I have a simple repository utilization alarm script. You can download the script, below. It’s not gorgeous code but it gets the job done. You will need to get a few things working for it to work in your environment, namely you will need to get the Oracle VM 3.3 CLI running with SSH keys instead of password logins. The later version of OVM (starting with one of the 3.2 releases, I think) have added an administration port that can be accessed using SSH. I found the write-up here.

I have my cronjob running as root on the Oracle VM Manager host, the same one that is running the CLI port. After generating the SSH keys for root I skipped the ssh-add command the wiki above mentions, and simply ‘cat the public key file appending it to the oracle account authorized_keys file (eg, cat ~/.ssh/*.pub >>~oracle/.ssh/authorized_keys).

Next, as root, ssh to the admin port as the admin user. Answer yes to save the keys– if you have conflicts you should stop and clean it out of your .ssh/known_hosts file. Once you have authenticated it should authorize that newly added key. While you are in the CLI session you should test your functionality and compatibility of your Oracle VM Manager environment by issuing the command “list repository”. Your test should look something like this:

# ssh admin@localhost -p 10000
The authenticity of host '[localhost]:10000 ([127.0.0.1]:10000)' can't be established.
DSA key fingerprint is ya.da.ya.da.ya.da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[localhost]:10000,[127.0.0.1]:10000' (DSA) to the list of known hosts.
admin@localhost's password: 
OVM> list repository
Command: list repository
Status: Success
Time: 2015-02-11 15:16:43,417 CST
Data: 
 id:0004fb00000300005eexxxxxxx name:Repo1
 id:0004fb0000030000709xxxxxxx name:Repo2
 id:0004fb0000030000455xxxxxxx name:Repo3
OVM>

Go ahead and ‘exit’ after this has completed successfully. Does the output look similar?

Try the SSH connection again and if successful you should not have a password prompt nor a prompt to save the host key. In this second session, please copy the UUID from the earlier test and try the command below and using your UUID after the equal sign:

# ssh admin@localhost -p 10000
OVM> show repository id=
Command: show repository id=0004fb0000030000455xxxxxxx
Status: Success
Time: 2015-02-11 15:22:47,666 CST
Data: 
 File System = 0004fb0000030000455xxxxxxx [fs on NETAPP]
 Manager UUID = 0004fb00000100006edaaaaaaaaaaaaaa
 File System Free (GiB) = 503.32
 File System Total (GiB) = 2037.11
 File System Used (GiB) = 1533.79
 Used % = 75.0
 Refreshed = Yes
 Presented = Yes
 Presented Server 1 = 4c:4c:45:44:00:52:38:10:aa:aa:aa:aa:aa:aa [host1]
 Presented Server 2 = 4c:4c:45:44:00:52:38:10:bb:bb:bb:bb:bb:bb [host2]
 VirtualDisk 1 = blahblah.img [blahblah.img]
 VirtualDisk 2 = blahblhablha.img [blahblahblha.img]
 VirtualCdrom 1 = 0004fb00001500000b984b7f06a25c25.iso [OEL 6.iso]
 Vm 1 = 0004fb0000060000ecccccccccc [virtmach1]
 Vm 5 = 0004fb0000060000ddddddddddd [testme]
 Id = 0004fb0000030000455xxxxxxx [Repo3]
 Name = Repo3
 Locked = false
OVM>

Your output should hopefully look similar to the above info so go ahead and exit out of the CLI. You should have at the minimum the following keywords in four named fields (see underlined words) for my OVMrepomon.pl script to work:

  • File System Free (GiB)
  • File System Total (GiB)
  • File System Used (GiB)
  • Used %

If you have those four fields, let us proceed because your next step is prepare your Perl environment with the modules necessary to retrieve, process, format the data, and send the alarm.

Your Perl executable needs three free modules. You may be able to find some of these in the package repository of your operating system distribution, but that would be more complicated than I want to write for this post. So run your yum commands, or dpkg/apt-get, or whatever… but ultimately you will probably be running CPAN to install a few of these modules.

  • MIME::Lite  — this sends the SMTP alarm message
  • HTML::HashTable — this is an easy way to make an HTML table for the email
  • Net::OpenSSH — this is an easy way to make the SSH connection to the OVMM CLI port

Once you have installed the modules above, download my code and create an executable script from it.

There are seven lines that need to configured for your environment and they are located near the top of the script. They are in one section called “Global Basics” and they control who gets the alarms, the OVM host, and your thresholds for the alarms.

  • # Global Basics to tweak for your installation:
  • my $OVMHOST=’admin@hostname:10000′; # should be in the notation user@host:port for the OVM CLI
  • my $MAILTO=’your best friends email address’; # who receives the alerts?
  • my $MAILFROM=’no-reply’; # who do the alerts come from?
  • my $MAILHOST=”smtpforwarder”; # who can forward the email alerts?
  • my $DEBUG=0;
  • my $PCused=”87″; # percent used threshold
  • my $FSfree=”200″; # gigs free of file system threshold

Run the command a few times, preferable with DEBUG set to 1 or higher and the thresholds set to level to insure a message can be sent. Make sure you are getting the email messages and then turn debug off. Now you are ready to put it in your cron scheduler. Make sure cron is able to execute and deliver a message to your inbox with debug off, and finally adjust the thresholds to what you truly want to be alerted at.

I really want to be able to resize partitions and file systems without having to reboot the virtual machine. It can be really difficult to get an outage on a production database system in our highly available environment. It works better in some virtualization hypervisors than others. It was working perfectly in CentOS virtual machines running in ESXi 5.1 but even that has had some issues recently, so perhaps there is more at play than just the Hypervisor.

We have started using the Oracle VM hypervisor for a small farm of Oracle database servers to save on licensing costs. This suite is Xen based but it has been extended (I believe) from the open-source software.

It’s usable. It’s not horrible. But as far as Virtualization goes it is not the best. But surprisingly cheap considering the name brand. That will probably be fixed after a while. 


 

The scenario is you need more disk space in the virtual machine. So using the OVM Manager you can grow the virtual disk image file. It’s an easy process in the GUI and should complete easily and without error.

At this point things usually go south because the system cannot make the kernel re-read the partition table, though I have found a possible way around the part of the problem. The key issue still remains, and here it is, the infamous error:

  • the kernel failed to re-read the partition table on /dev/xvda (Device or resource busy). As a result, it may not reflect all of your changes until after reboot.

Screen Shot 2014-08-16 at 9.32.29 AM

I’ve tried the usual suspects that can make the kernel re-read the partition table. These are partprobe, kpartx, partx, and ‘for i in `find /sys -name rescan |grep target`; do echo 1 > $i; done’. It doesn’t work under the Xen hypervisor. I think the partition table in question is borrowed from the hypervisor domain.

So what to do? Here’s my partial fix: I found that migrating the VM to another host in the Xen pool will at least show the increased virtual disk size, allowing the usual fdisk delete/recreate process to grow the partition.

But once the partition has been re-sized, even this does not work. I have migrated from host to host to host to host, and run the rescan on host and VM both. I looked at the kernel’s /proc/partitions file on the host and saw a “/dev/loop??” that seemed to correlate to the virtual disk image file in the repository.

Screen Shot 2014-08-16 at 9.50.03 AM

While migrating from host to host I looked at the host’s /proc/partitions file and determined the virtual disk is mounted as a loop device on the host:

Screen Shot 2014-08-16 at 10.14.28 AM

I’ve used kpartx, partprobe on this (/dev/loop2) but to no avail. The new partition 2 size does not get propagated to the kernel of the VM:

  • No change to size of physical volume /dev/xvda2.

Screen Shot 2014-08-16 at 10.25.55 AM

There has to be a way to get this pushed to the VM’s kernel. I just haven’t found it yet.

My previous two posts were about getting utilization statistics out of my Network Appliance filers into a Teamquest database for my IT Service Analyzer and Reporter charts. They are working great and I am using them in a production environment. The thing that bothered me about them is they seemed so slow. The volume stats report would take just over a second for four filer heads and the system stats script seemed to take FOREVER. I timed it. It was only five seconds for four filers but the feeling was still FOREVER.

timing old script

ptime of old volstats

timing the old systats

ptime of old systats

I knew what the problem was and I knew I would have to buckle down and learn SNMP even better, and especially learn Perl SNMP modules in order to tune it back my acceptable standards of runtime. That first script was a quick and dirty hack really, and like most hacks it is just functional. All the SNMP requests were running system commands that could be easily run and debugged from a command line. It’s a great way to learn and get something functional at the same time. But it’s like a baby eating from a bottle, it needs to grow up, eat solid food, go to school, and get a job to support itself. Or, in Perl terms, it needs to use pure Perl code to do the work instead of system commands.

So, enter version 2 of both scripts. My new volume stats script literally runs twice as fast as the old script. My new system stats, also quite literally, runs TEN times as fast. Woo hoo! How is that for tuning code and making things better?

timing new volstats

ptime of new volstats

timing new sys stats

ptime of new sys stats

These new versions run no system commands but do all work using the Net-SNMP Perl modules (not to be confused with Net::SNMP Perl modules). The process of learning the SNMP module took several days of trial and error around my other work. The biggest issue with Perl is the confusing amount of Perl modules available to do the same job. Often, a few google searches will reveal which module has the most support and I would choose that one. But in the case of the Perl SNMP modules there is no clear winner. Both have equal number of blogs and confused postings looking for help with the modules.

So I picked one. It was the wrong one initially, of course. I picked Net::SNMP to start with because it can be built using the CPAN shell (eg, ‘perl -MCPAN -e shell’). The other primary SNMP module being used is the one provided by the Net-SNMP command line packages. This can be more of a challenge to build, but more often than not it can just be installed as a package for your system, which is the easy route I chose. I used the OpenCSW package.

The reason I say that the Net::SNMP package was the wrong path is the challenge for an SNMP illiterate to understand SNMP and specialized MIBs. It appeared that you needed to know the confusingly long ID number of the statistic to use this module. I was (and am still) trying to learn about SNMP and could not figure out the proper way to find the statistics I wanted using this module. So I switched to the other package module which allowed me to use names for statistics that I was used to, like “df64AvailKBytes” to find the full and correct amount of Kilobytes available to a filesystem.

So I set off to learn the module. I started small with test scripts to just gather one or a few statistics. This allowed me to make some quick progress and learn how to address the desired statistics as a scalar, array, or hash, and to grow and process multiple statistics in relation to each other.

I ended up using the VarList method within the module. It allows the script to retrieve a bunch of statistics with a single connection. This is much more efficient than the old script which would make up to a dozen SNMP command requests to each filer head to get the desired statistics. This new method gets them once and then let me step through them one row at a time.

View/download my scripts here:

  1. new version 2 netapp volume stats script
  2. new version 2 netapp sys stats script

There is one thing that bothered me and I never figured out when I worked on the volume statistics script (the second one I tackled). When using the command line utilities the entire disk table can be requested using the name ‘dfTable’. This would not work using the Perl SNMP module even though ‘volTable’ and ‘ifTable’ would work. I do not understand the difference, but instead punted and again used the VarList method for named individual statistics with great success. If you know why, please make a comment. I wonder if I could shave a few tenths of a second off using dfTable…😉

This is a follow on post to my previous article on getting the NetApp filer disk/volume/aggregate statistics charting using TeamQuest ITSAR (IT Service Analyzer and Reporter). So if are you interested in getting some other statistics on usage and utilization of your Network Appliance filers like the one below, read on.

NetApp systats Chart

Utilization statistics in ITSAR

This script and user table agent definition detail how to get the actual filer utilization such as CPU busy, network kilobytes in and out, and some other useful things for potential alerts. Potential alerts? Yes, some of the statistics that can be gathered using the SNMP agent are things like failed disks, failed power supplies, failed fans, the number of spare disks, and more. Simple peruse the Network Appliance SNMP MIB to see everything that is available to us. The table definition and my script can easily be extended before implementation to include the additional information you may be interested in.

Personally, I really trust the NetApp auto-support ability. Our NetApp filers are extremely capable of alerting us when a disk or anything fails. The filer heads are clustered and extremely redundant so I trust them (just not the devil inside, to quote a movie), so I might as well gather a few stats that I may track and alert on at a future time.

I won’t spend a lot of time covering the setup of SNMP on the filer or the TeamQuest host because that’s already done in the previous blog on the subject. Instead I will jump straight into the files and table setup for these new statistics.

The first step is to download the two additional files needed for the filer system statistics.

  1. The Network Appliance TeamQuest table definition for System statistics
  2. The Network Appliance Systats perl script

By now you have all the recommendations on hand and ready to go from my last blog… so save the files above to the same directory. Edit the script to configure the paths, username, password, and community string just like last time. Also make sure that the data directory has write privilege for the user that will be running the TeamQuest UTA which is usually daemon:root. Run it a few times to make sure it is working correctly, but take the time to make sure that the logfiles are writable by the user daemon after you are finished testing.

The script writes two files necessary for calculating the true network statistics. The SNMP statistic delivered is a number in bytes since the system last booted. I don’t think it needs to be stated, but this is not a very flexible statistic to work with for charting. It’s huge! And it gets humongous since the filers never, ever need to restart except for upgrades. I developed the script to use a log file to store the statistic from the last run and do a little math to give us a useful number for ongoing utilization. On execution the script operates like this in regards to the network statistics:

  1. Gets current network statistic
  2. Get last network statistic from log file
  3. Calculates difference
  4. converts to kilobytes
  5. saves current statistic (as read from filer) to the logfile

That’s it! It’s pretty easy to setup and run. The most difficult part of the setup was reading through all the many possible options for defining the statistics in the table definitions. I think I saved you a bit of work there – and in fact, some of the praise there goes to TeamQuest themselves. I was having issues with the way some of the statistics were being averaged and I opened a ticket with them. They were very patient with me and we got it resolved. Tickle me happy!

So import the table definition into your test or production database (“$manager/bin/tqtblprb -i -d testdatabase -f NetApp_sysStats.tbl”). And when that is done build your User Table Agent same as before but referencing the second script and the new table (USER:NetAppSysStats).

I may go ahead and setup some alerts on some of the statistics, there is more to be done!

NetApp all table data

NetApp systats

For as long as I have been using TeamQuest products I have wanted them to provide a solution for my Network Appliance brand filer devices. It was a desire that I could have have written a long time ago but frankly it was a low priority. I had a custom script that would run “df-Ah” on the filer, cut out the columns I wanted and write it to a CSV file that certain people could read in and make an Excel chart with. It was adequate so other higher priority items were worked and this languished for… a really long time. I now, finally, have something like the gauges chart below:

ITSAR chart of NetApp storage utilization

ITSAR chart of my NetApp appliances’ utilization

It finally happened because this last summer we migrated our Solaris web environment into zones. My previous job ran on the old systems and while I could have moved the stupid job I was holding out to force myself to get this written. So after a couple months of running it manually when the user needed the data I bit down and wrote the code necessary to get the capacity data into Teamquest. Basically I leveraged that inherent laziness in me to finally make myself get it done the proper way.

So, this blog documents my efforts to write a real solution to make a beautiful chart in my Teamquest IT Service Analyzer and Reporter where all the wild things go to get charted for management. I wanted more than just the storage utilization metrics we currently provide but that was the most important first step to accomplish and will be covered in this blog. A follow on blog should cover the  CPU, network, failed disk, failed power supply, and other interesting metrics that can be gathered, monitored, charted, and alerted on.

How to duplicate my results in your environment

The first item is get SNMP version 3 working on your filers under a limited user account on the filer. SNMP version 3 is necessary in today’s multi-terabyte world because the fields defined within SNMP version 1 and 2 by the MIB cannot account for the insane amount of “bytes” reported. Yes, it has to report in bytes. So be sure to download the Word Doc available at the NetApp community site and follow through step one. Yes, just the first step is all that is really needed, but don’t forget to set a password for the new user account who is allowed (and only allowed) to use SNMP.

Create a location for your scripts and data files. I like to put my scripts in /opt/teamquest/scripts, with a data directory underneath that. The Teamquest User Table Agent will run as the user ‘daemon’ and group root, so be sure to set appropriate permissions on the directory path and script for read and execution, and the data directory for write permission.

Make sure your system has snmp binaries — the Solaris ones are adequate and will probably be in /usr/sfw/bin if you installed the packages. The OpenCSW binaries are great, too. You will notice I am actually using the OpenCSW binaries at times but I have no good reason too– except that I typically like to have some base OpenCSW packages installed so that I have gtail, allowing me to tail multiple files at the same time.

Download the following files

  1. NetApp MIB for SNMP from NetApp
  2. My Script for NetApp Volume Statistics
  3. TeamQuest table definition for NetApp Volume

Drop the latest NetApp SNMP MIB into the data directory and copy my scripts to the script directory. Use “less” to look into the NetApp MIB and look at some of the options to get in there. There is a lot. I focused on the following values that I will use between this blog (volume statistics) and a future blog on system statistics:  dfTable, productVersion, productModel, productFirmwareVersion, cpuBusyTimePerCent, envFailedFanCount, envFailedPowerSupplyCount, nvramBatteryStatus, diskTotalCount, diskFailedCount, diskSpareCount, misc64NetRcvdBytes, and misc64NetSentBytes. If you see a “64” version of a statistic you will want to use that one to make sure that you are getting the real data figure out of the system.

Test your user and SNMP client with some command line operations before you start editing the script to run in your environment. A command would be like this ‘/opt/csw/bin/snmpget -v3 -n “” -u yourSNMPuser -l authNoPriv -A yourSNMPuserPassword -a Md5 -O qv -c yourcommunity -m /opt/teamquest/scripts/data/netapp.mib your-filername NETWORK-APPLIANCE-MIB::misc64NetRcvdBytes.0’. We will work on this statistic next time but today we are looking at the dfTable statistic for all the stats you want on your storage. So be sure to also test this different SNMP command: ‘ /usr/sfw/bin/snmptable -v3 -n “” -u yourSNMPuser -l authNoPriv -A yourSNMPuserPassword -a Md5 -c yourcommunity -m /opt/teamquest/scripts/data/netapp.mib yourfilername NETWORK-APPLIANCE-MIB::dfTable’ and marvel at the amount of data that comes across your terminal.

If all is successful with your command line tests then you are ready to edit the script and get it configured for your environment. You may be changing the path to the SNMP command and the MIB file, but you will definitely be changing the username, password, and community string. There are several other options to tweak too — do you want to import all volumes or just aggregates? Do you want to ignore snapshots? Test the script several times and make sure it is returning the data the way you want it. You will notice that you have to pass the filer names (comma separated, no spaces) in on the command line. This makes it easy to add and remove filers from your environment without adding or removing User Table Agents from your Teamquest manager, just simply edit the command line options passed to the script. Don’t forget to test with and without the -t=interval options for the TeamQuest format where the interval will match your desired frequency that the agent runs. And don’t worry about the extra options for snapshots or aggregates-only, this can be tweaked at any time to limit the data being absorbed by Teamquest and when you report or alert you can easily filter out what you don’t want.

When you are ready import the third file, the table, into your Teamquest database. You may want to use a test database for a while and then eventually add it to a production database. The command to import the table is “$manager/bin/tqtblprb -i -f NetApp_VolumeStats.tbl” but I heartily recommend you have the command line manual handy and consult it regularly for adding databases, tables, and deleting said items when things go wrong. IT happens.

Adding User Table Agent configuration

Adding User Table Agent configuration

When the table is entered into the database you are ready to add your very own User Table Agent. Connect to the TQ manager of the desired system using your browser. Set the database if you are not using the production database, and then click Collection Agents. On the far right you will see the link “Add Agent”, click that and then “User Table Instance”. Begin to enter the information that makes sense to you such as a name for this agent, the path to the executable, and the interval you want  collection to happen. The class and subclass must match exactly what is in the table file that was imported. It will be “USER” and “NetAppVolumes” unless you changed it. The Program arguments is where you pass in the comma separated list of filer names (no spaces!), a single space and -t=<interval>. Make sure to set that interval to equal what you have entered in below for the actual collection interval. After you save and apply the new settings you simply have to wait until the clock hits the time to match the next collection (every five minute increment of the hour if you are using the 300 second interval like I am).

Be sure to launch TQView and look at the table directly for accurate statistics, play with the filter options, etc. Tip: you can create a test database in ITSAR that only harvests from this test database so that you can test end to end.

table view of data

Using TQview to examine actual data gathered

You will notice that I dropped the actual FlexibleVolume volume type data from my gathering. It may be useful at some point in the future and it can be re-added with a simple edit to the script, but for this first stage all I care about is overall health of the filer and so my ITSAR chart for management is a simple global view of the filer cluster-pair. For this, I use the statistic “FilerCapacity” that the script calculates by summing all of the VolumeType “aggregate” on each filer node. You can see that I have a total of four nodes in my environment (names withheld to protect the innocent).

And that is it for the first stage! On to writing alerts and getting the system stats working.