Running it all on the Raspberry Pi (Part 3) - Performance Issue Resolved

In my previous two post (Part 1 and 2) I described how I moved all of the processes for tracking the sensor data onto the Raspberry Pi. I ended the second post with some concerns in relation to CPU usage of the php-cgi process being to high (around 100% when accessing the charts).

The php-cgi process was taking care of calling the PHP script, which then would access the SQLite database and return data for one particular sensor. Since a SQLite database can only be accessed by one process at the time (database locked until released/closed) special steps had been taken to wait for the database to be available to be queried (i.e. by using the busyTimeout()-routine). In most scenarios this would work fine, but since the sensor page fetches data for three sensors simultaneously, these processes were mainly waiting for each other to finish.

SQLite is perfect as an easy lightweight database, but when you require multiple sessions into the database you are better of using some sort of database server. For the Raspberry Pi MySQL server is a good option. I also decided to replace Lighttpd with Apache2, since I am more familiar with Apache than I am with Lighttpd.

Roughly we need to perform the following tasks to switch from Lighttpd/SQLite to Apache2/MySQL

• Uninstall/remove Lighttpd
• Install MySQL server
• Install Apache2 with support for PHP5
• Install MySQL support for PHP5 and Python
• Create MySQL database, tables and application user
• Update Python and PHP scripts

Uninstall/remove Lighttpd

To remove Lighttpd issue the following command

sudo apt-get --purge remove lighttpd

Install and update software

Install MySQL server

sudo apt-get install mysql-server

(re)Install Apache2, PHP5

sudo apt-get install --reinstall apache2 php5 libapache2-mod-php5
sudo apt-get install php5-common php5-cgi php5 php5-mysql

Install MySQL support for Python

sudo apt-get install python-mysqldb

Last step we make sure that the user pi has full access to the folder (/var/www/) which holds the files served by Apache2

sudo chown www-data:www-data /var/www
sudo chmod 775 /var/www
sudo usermod -a -G www-data pi

Create MySQL database, tables and application user

Start the MySQL command line interface using the following command (user root and ask for password). You will be asked to enter the password for the root (MySQL) user, this is the same password as has been entered when install the mysql-server package.

mysql -u root -p

You should see something like the following

pi@raspberrypi ~ $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.31-0+wheezy1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

You are now ready to issue the commands listed below. When done, the exit command will take you back to the *nix-shell.

Creating the new database

-- Create database: sensordata
create database sensordata;

Creating the tables and indexes

-- Create table: sensor_types
create table sensor_types (
  type_id       int not null auto_increment
, type_name     char(200) not null
, primary key (type_id)
);

-- Create table: location
create table locations(
  location_id    int not null auto_increment
, location_name  char(200) not null
, primary key (location_id)
);

-- Create table: sensors
create table sensors(
  sensor_id     int not null auto_increment
, type_id       integer not null
, location_id   integer not null
, sensor_name   char(200) not null
, primary key (sensor_id)
, constraint foreign key (type_id) references sensor_types(type_id)
, constraint foreign key (location_id) references locations(location_id)
);
create index type_idx on sensor_types(type_id);
create index location_idx on locations(location_id);

-- Create table: sensor_data
create table sensor_data(
  id            int not null auto_increment
, timestamp     timestamp not null default current_timestamp
, sensor_id     integer not null
, value         real
, primary key (id)
, constraint foreign key (sensor_id) references sensors(sensor_id)
);
create index sensor_idx on sensors(sensor_id);

Creating the application user

-- Create user
create user 'rpi'@'localhost' identified by 'rpi';

-- Grant privileges
grant all on sensordata.* to 'rpi'@'localhost';

Seed the database with initial data

-- Add locations
insert into locations(location_name) values('Bedroom');
insert into locations(location_name) values('Office');
insert into locations(location_name) values('Guestroom');
insert into locations(location_name) values('Hall');
insert into locations(location_name) values('Kitchen/Livingroom');
insert into locations(location_name) values('Storageroom');

-- Add sensor types
insert into sensor_types(type_name) values('Temperature');
insert into sensor_types(type_name) values('Humidity');

-- Add sensors
insert into sensors(type_id,sensor_name,location_id) values(1,'DS18B20',2);
insert into sensors(type_id,sensor_name,location_id) values(2,'DHT22 - Temperature',2);
insert into sensors(type_id,sensor_name,location_id) values(3,'DHT22 - Humidity',2);

Update Python and PHP scripts

Updated version of the Python script 1

#!/usr/bin/env python

import os
import glob
import time
import re
import RPi.GPIO as GPIO
import urllib2 as url
import subprocess
import MySQLdb as sql

# Load kernel modules for 1-wire devices
os.system( 'modprobe w1-gpio' )
os.system( 'modprobe w1-therm' )

# Determine location of first found DS18B20 sensor
base_dir = '/sys/bus/w1/devices/'
device_folder = glob.glob( base_dir + '28*' )[0]
device_file = device_folder + '/w1_slave'

# Configure GPIO
GPIO.setmode( GPIO.BCM )
GPIO.setwarnings( False )

# Connect to MySQL database
con = sql.connect( host = "localhost", user = "rpi", passwd = "rpi", db = "sensordata" )
cur = con.cursor()

# Log data to the MySQL database
def logData( id, value):
    try:
        cur.execute( "insert into sensor_data(sensor_id,value) values( {0}, {1} )".format( id, value ) )
        con.commit()
        return
    except:
        if con:
            con.rollback()
        return

# Controle state for LED pin (turn on/off the connected LED)
def ledMode( PiPin, mode ):
    GPIO.setup( PiPin, GPIO.OUT )
    GPIO.output( PiPin, mode )
    return

# Read data from the raw device
def read_temp_raw():
    f = open(device_file, 'r')
    lines = f.readlines()
    f.close()
    return lines

# Determine temperature and humidity from the DHT22/AM2302 sensor
def read_dht22( PiPin ):
    output = subprocess.check_output(["./Adafruit_DHT", "2302", str(PiPin)])
    matches = re.search("Temp =\s+([0-9.]+)", output)
    if ( matches ):
        logData( 2, float(matches.group(1)) )
    matches = re.search("Hum =\s+([0-9.]+)", output)
    if ( matches ):
        logData( 3, float(matches.group(1)) )
    return

# Determine temperature from the DS18B20 sensor
def read_temp():
    lines = read_temp_raw()
    while lines[0].strip()[-3:] != 'YES':
        time.sleep(0.2)
        lines = read_temp_raw()
    equals_pos = lines[1].find('t=')
    if equals_pos != -1:
        temp_string = lines[1][equals_pos+2:]
        temp_c = float(temp_string) / 1000.0
        temp_f = temp_c * 9.0 / 5.0 + 32.0
        return temp_c, temp_f

# Turn off all LEDs
ledMode( 14, GPIO.LOW )
ledMode( 15, GPIO.LOW )
ledMode( 18, GPIO.LOW )

while True:
    temp_c, temp_f = read_temp()
    ledMode( 14, GPIO.HIGH if temp_c < 27 else GPIO.LOW )
    ledMode( 15, GPIO.HIGH if temp_c >= 27 and temp_c < 29 else GPIO.LOW )
    ledMode( 18, GPIO.HIGH if temp_c >= 29 else GPIO.LOW )
    ts = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    print '{0} - Temperature = {1:.2f} C ({2:.2f} F)'.format( ts, temp_c, temp_f )
    logData( 1, temp_c )
    read_dht22(22)
    time.sleep(30)

Updated version of the PHP script 1

<?php
/* ========================================================================== */
/* Globals                                                                    */
/* ========================================================================== */
    define( 'NEWLINE', "\n");

/* ========================================================================== */
/* Open MySQL connection                                                      */
/* ========================================================================== */
    $db = new mysqli( 'localhost', 'rpi', 'rpi', 'sensordata' );

/* ========================================================================== */
/* List sensor data                                                           */
/* ========================================================================== */
    function process_csv_log_data( $sensor, $period ) {
        global $db;

        $results = $db->query( "select d.timestamp
                                ,      d.value
                                from   sensor_data d
                                where  d.sensor_id = ${sensor}
                                and    timestampdiff(HOUR,d.timestamp,now()) <= ${period}
                                order by d.timestamp" );

        header("Content-type: text/csv");
        echo 'timestamp,temperature'.NEWLINE;

        while ( $row = mysqli_fetch_assoc( $results ) ) {
            printf('%s,%s'.NEWLINE, $row['timestamp'], $row['value']);
       }
    }

/* ========================================================================== */
/* Main process                                                               */
/* ========================================================================== */
    $command = isset( $_GET['action'] )? $_GET['action'] : 'UNKNOWN_COMMAND';

    switch( $command )
    {
        case 'csv_data';
            $id = (int)$_GET['id'];
            $period = (int)$_GET['period'];
            process_csv_log_data( $id, $period );
        break;
    }

/* ========================================================================== */
/* Close MySQL connection                                                     */
/* ========================================================================== */
    $db->close();
?>

Checking performance

CPU usage while collecting data

top - 17:18:38 up 3 days, 17:31,  1 user,  load average: 0.20, 0.13, 0.14
Tasks: 100 total,   1 running,  99 sleeping,   0 stopped,   0 zombie
%Cpu(s):  1.0 us,  3.6 sy,  0.0 ni, 58.6 id, 36.8 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem:    448776 total,   424648 used,    24128 free,    48428 buffers
KiB Swap:   102396 total,        0 used,   102396 free,   261840 cached

  PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
25311 pi        20   0  4672 1432 1024 R   1.6  0.3   0:00.35 top
   37 root      20   0     0    0    0 S   1.0  0.0  14:29.91 mmcqd/0
21636 mysql     20   0  320m  38m 6768 S   0.7  8.8  12:53.03 mysqld
 1594 root      20   0  1744  528  436 S   0.3  0.1   3:37.80 ifplugd
 1607 root      20   0     0    0    0 S   0.3  0.0   4:50.62 RTW_CMD_THREAD
 2525 root      20   0     0    0    0 S   0.3  0.0   8:31.91 w1_bus_master1
24989 pi        20   0  9884 1680 1008 S   0.3  0.4   0:05.29 sshd
    1 root      20   0  2144  728  620 S   0.0  0.2   0:11.61 init
    2 root      20   0     0    0    0 S   0.0  0.0   0:00.12 kthreadd

CPU usage when accessing the charts (3 charts, all displaying 24 hours of data)

top - 17:19:06 up 3 days, 17:32,  1 user,  load average: 0.13, 0.12, 0.13
Tasks: 100 total,   1 running,  99 sleeping,   0 stopped,   0 zombie
%Cpu(s): 25.8 us,  4.6 sy,  0.0 ni, 68.3 id,  0.0 wa,  0.0 hi,  1.3 si,  0.0 st
KiB Mem:    448776 total,   424656 used,    24120 free,    48428 buffers
KiB Swap:   102396 total,        0 used,   102396 free,   261840 cached

  PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
21636 mysql     20   0  320m  38m 6768 S  16.6  8.8  12:53.65 mysqld
10753 www-data  20   0 36744 6912 2868 S   4.6  1.5   0:10.98 apache2
10752 www-data  20   0 36804 7036 2932 S   3.9  1.6   0:12.17 apache2
12020 www-data  20   0 36740 6904 2868 S   3.6  1.5   0:06.46 apache2
25311 pi        20   0  4672 1432 1024 R   1.6  0.3   0:00.74 top
    6 root      20   0     0    0    0 S   0.3  0.0   0:37.72 kworker/u:0
 1600 root      20   0  1744  500  408 S   0.3  0.1   2:30.72 ifplugd
24989 pi        20   0  9884 1680 1008 S   0.3  0.4   0:05.41 sshd
25135 root      20   0     0    0    0 S   0.3  0.0   0:01.80 kworker/0:2

As before we see three processes (apache2) from the www-data user. Unlike before each of these processes only consume 4-5% CPU, which is a huge improvement to what we had before (31-35% CPU). Big improvement!!

I am very pleased with the results, this leaves enough CPU-cycles free to used for other tasks…