Category Archives: Know How

Simulating a Tesla Powerwall with InfluxDB

Tesla Powerwall

It’s a little over a year ago that I got solar panels installed on my roof. Not a Tesla Solar Roof, but the most efficient solar panels available last year. From SunPower. The SPR-X21-350-BLK. Together with a SolarEdge SE5K converter its producing 5300 Watt during peak hours.

I’m interested if a Tesla Powerwall is worth it financially. Will it pay back the investment in a reasonable amount of time?

In the Netherlands there is still a net metering policy until 2023, which means that you can administratively subtract the electricity you have delivered to, from what you have used from the electricity network. Basically you can use the electricity network as a big battery to store solar overproduction.

Starting in 2023 the net metering policy will be phased out gradually. Based on the current pricing you pay around €0.25 per kWh, but for delivering you only get between €0.04 and €0.12 per kWh depending on the contract with your supplier. It becomes financially more attractive to use as much electricity as possible from your solar panels directly or via a battery.

Preparing for the year 2023 I have some research questions after one year of having solar panels:

  • How much electricity is actually used directly from my solar panels?
  • Is it worth investing in a Tesla Powerwall to use more of my own solar power?

Collecting the data

Since the start I’ve been collecting metrics from the Solaredge inverter using a wrapper script around sunspec-monitor. The main metric I’m collecting from the inverter is the “energy_total” metric, which is the total amount of Watt hours produced by the inverter. This metric is collected every 60 seconds and stored in InfluxDB.

Using a USB cable connected to the P1 port of my electricity meter I’m also collecting metrics about the amount of electricity consumed from and delivered to the electricity network every 60 seconds.

Overview of metrics collection from Inverter and Electricity Meter

In InfluxDB the stored metrics look like this:

> select net_used, delivered, produced from "electricity" order by time desc limit 10;
name: electricity
time                 net_used delivered produced
----                 -------- --------- --------
2020-05-07T07:32:00Z 3207201  5214424   6368160
2020-05-07T07:31:00Z 3207201  5214367   6368100
2020-05-07T07:30:00Z 3207201  5214310   6368039
2020-05-07T07:29:00Z 3207201  5214253   6367978
2020-05-07T07:28:00Z 3207201  5214196   6367918
2020-05-07T07:27:00Z 3207201  5214140   6367858
2020-05-07T07:26:00Z 3207201  5214084   6367798
2020-05-07T07:25:00Z 3207201  5214028   6367739
2020-05-07T07:24:00Z 3207201  5213972   6367679
2020-05-07T07:23:00Z 3207201  5213917   6367620

When turning this data into a graph, on a sunny day it look like this:

Electricity Usage, Delivery, Production on a sunny day (20200404)

On a cloudy day it looks like this:

Electricity Usage, Delivery, Production on a sunny day (20200414)

As you can see, I’m not using all the electricity produced by the solar panels directly. Most of it actually is delivered to the electricity network. And during the night, there of course is no sun, so I’m using from the electricity network.

Statistics from Year 1

Looking at some day graphs is nice, but what does this mean overall in a year?

  1. How much electricity did the solar panels produce?
  2. How much electricity was delivered to/used from the network?
  3. How much electricity was consumed from the solar panels directly?
  4. How much electricity did I consume in total?

The answers to the 1st two questions is pretty easy to find. Take the number from the 3 columns in InfluxDB from today and subtract the values from 1 year ago.

The answer to question 3 can be answered by subtracting “delivery” from “production”. Its also possible to have per minute statistics for direct consumption by creating a “Continuous Query” in InfluxDB:

SELECT mean(produced) - mean(delivered) AS consumed
INTO energy.autogen.electricity
FROM energy.autogen.electricity
GROUP BY time(1m), *

The Continuous Query will only generate new “consumed” values. To generate previous values, execute the part between BEGIN and END once.

For the answer to question 4, we need to sum “consumed” (generated by the previous Continuous Query) and “net_used”:

CREATE CONTINUOUS QUERY cq_total_used ON energy
SELECT mean(consumed) + mean(net_used) AS total_used
INTO energy.autogen.electricity
FROM energy.autogen.electricity
GROUP BY time(1m), *

In InfluxDB it now looks like this (first and last metric of year 1):

> select net_used, delivered, produced, consumed, total_used from "electricity" where time < '2019-04-21' order by time desc limit 1;
name: electricity
time                 net_used delivered produced consumed total_used
----                 -------- --------- -------- -------- ----------
2019-04-20T23:59:00Z 337620   1224      373      -851     336769

> select net_used, delivered, produced, consumed, total_used from "electricity" where time < '2020-04-21' order by time desc limit 1;
name: electricity
time                 net_used delivered produced consumed total_used
----                 -------- --------- -------- -------- ----------
2020-04-20T23:59:00Z 3117433  4841893   5919356  1077463  4194896

Putting the results in a Grafana dashboard gives an interesting overview of year 1:

Overview of electricity production, usage and delivery (ignore the rounding errors)

The answers to my questions:

  1. How much electricity did the solar panels produce?
    • 5917kWh produced
  2. How much electricity was delivered to/used from the network?
    • 4841kWh delivered to the network
    • 2779kWh used from the network
  3. How much electricity was consumed from the solar panels directly?
    • 1075kWh consumed directly
  4. How much electricity did I consume in total?
    • 3853kWh used in total

The solar panels produced 54% more then I consumed in total, but still I need to get 72% of my electricity from the electricity network. The average daily production/usage graph below shows why. Most of the electricity is consumed when the sun is not shining. 😕

Daily average electricity production / usage

What does this mean in terms of yearly costs/profit, taking €0.25 per kWh for usage and €0.11 per kWh for delivery. Without having solar panels my cost would have been (3853 x 0.25) = €963.25

  • With net metering: (4841-2779) x 0.11 = €226.82 profit
  • Without net metering: 694.75 – 532.51 = €162.24 costs
    • 2779 x 0.25 = €694.75 costs
    • 4841 x 0.11 = €532.51 profit

What if I only would get €0.04 per kWh for delivery?

  • With net metering: (4841-2779) x 0.04 = €82.48 profit
  • Without net metering: 694.75 – 193.64 = €501.11 costs
    • 2779 x 0.25 = €694.75 costs
    • 4841 x 0.04 = €193.64 profit

Conclusions from Year 1

  • Only 18% of the solar energy is directly consumed
  • About 54% more electricity is produced then actually needed
  • Still 72% of the electricity comes from the electricity network, because there is no(t enough) solar energy available when needed
  • Currently there is a annual profit of €227.04
  • Without net metering that would been €162.24 profit
  • Future worse case (€0.04 without net metering after 2023): €501.11 costs 🙁

Simulating the Tesla Powerwall

The Tesla Powerwall. There are many interesting things to write about it, but let’s keep it simple and focused. Some specs:

  • 14kWh of electricity can be stored
  • 13.5kWh of this is usable (completely discharging would be bad for the battery)
  • Of the electricity you put in, 90% you will get out of it (Round Trip Efficiency)
  • Current price: € 8240

With all the data in InfluxDB and the specs above, it is possible to simulate a Powerwall minute by minute. I’ve written some python code that does this. In the simulation the battery has a minimum threshold of 500 Wh, a maximum of 14000 Wh and it takes the Round Trip Efficiency of 90% into account by dividing by 0.9 when electricity is used from the battery.

#!/usr/bin/env python3
from influxdb import InfluxDBClient

influx_client = InfluxDBClient('localhost', 8086, 'username', 'password', 'database')

def influx(measurements):
  except Exception as e:
    print('Failed to write to influxdb: ', e)

def charge(battery, delivered, net_used):
    if net_used > 0:
        if battery['level'] - (net_used / battery['efficiency']) < battery['min']:
            remaining_battery = battery['level'] - battery['min']
            battery['level'] = battery['min']
            battery['net_usage'] = battery['net_usage'] + (net_used - (remaining_battery * battery['efficiency']))
            battery['level'] = battery['level'] - (net_used / battery['efficiency'])

    if delivered > 0:
        if battery['level'] + delivered > battery['max']:
            remaining_battery = battery['max'] - battery['level']
            battery['level'] = battery['max']
            battery['net_delivery'] = battery['net_delivery'] + (delivered - remaining_battery)
            battery['level'] = battery['level'] + delivered

    return battery

def main():
    prev_point = None
    measurements = []
    starttime = '2019-01-01'
    battery = {
        "level": 0,
        "net_usage": 0,   
        "net_delivery": 0,
        "min": 500,  
        "max": 14000,
        "efficiency": 0.9,

    result = influx_client.query("""select delivered, net_used from "autogen"."electricity" where time >= '{}' order by time;""".format(starttime))

    points = result.get_points()
    for point in points:
        if prev_point is not None:
            for key in ['delivered', 'net_used']:
                if point[key] is None:
                    point[key] = prev_point[key]
            delivered = int(point['delivered']) - int(prev_point['delivered'])
            net_used = int(point['net_used']) - int(prev_point['net_used'])
            battery = charge(battery, delivered, net_used)
              "measurement": "battery",
              "time": point['time'],
              "fields": {
                  "powerwall_level": int(battery['level']),
                  "powerwall_net_usage": int(battery['net_usage']),
                  "powerwall_net_delivery": int(battery['net_delivery']),
        prev_point = point
        if len(measurements) > 1000:
            measurements = []

if __name__ == "__main__":

A part of the result is shown below in a graph with the simulation of 3 days. To compare, I’ve also created a graph without the Tesla Powerwall simulation.

Tesla Powerwall Simulation: 3 days of electricity usage, delivery and Tesla Powerwall electricity level
3 day electricity usage, delivery and production without a Tesla Powerwall

With a Tesla Powerwall less electricity is used from the network (Net Usage (blue)). During a sunny day (day 2) the Powerwall is completely charged. The day after electricity is still being used from the Powerwall that was produced the day before. That is pretty cool! 🙂

What would this have meant when I would have had a Powerwall in the past year? These statistics can be collected the same way the “Statistics for Year 1” were collected.

> select * from "battery" where time < '2019-04-22' order by time asc limit 1;
name: battery
time                 powerwall_level powerwall_net_delivery powerwall_net_usage
----                 --------------- ---------------------- -------------------
2019-04-21T00:01:00Z 500             0                      453

> select * from "battery" where time < '2020-04-21' order by time desc limit 1;
name: battery
time                 powerwall_level powerwall_net_delivery powerwall_net_usage
----                 --------------- ---------------------- -------------------
2020-04-20T23:59:00Z 10216           2876167                1020952

Querying InfluxDB for this data shows that with a Powerwall, 2877kWh would have been delivered to the electricity network and 1021kWh would have still been used from the network. This Grafana dashboard gives a clear overview:

Without a Powerwall I needed to get 72% of my electricity from the network. This is now reduced to 26%. 31% of the solar production gets stored in the Powerwall for later use.

Why do I still need to get 26% of the electricity from the network?

Daily Electricity Network Usage / Delivery when simulating a Tesla Powerwall

The graph above shows why. In the winter there is just not enough solar production to cover my needs. The graph below shows the same data, but without a Powerwall.

Daily Electricity Network Usage / Delivery (without a Tesla Powerwall)

Back to the simulation. What does it mean in terms of costs/profit, again taking €0.25 per kWh for usage and €0.11 per kWh for delivery.

  • With net metering: (2877-1021) x 0.11 = €204.16 profit
  • Without net metering: 316.47 – 255.25 = €61.22 profit
    • 1021 x 0.25 = €255.25 costs
    • 2877 x 0.11 = €316.47 profit

Or what if I only would get €0.04 per kWh for delivery?

  • With net metering: (2877-1021) x 0.04 = €82.48 profit
  • Without net metering: 115.08 – 255.25 = €140.17 costs
    • 1021 x 0.25 = €255.25 costs
    • 2877 x 0.04 = €115.08 profit

Conclusions from Simulating a Tesla Powerwall

  • I would make €204.16 profit instead of €226.82 currently, with net metering. This is actually a decrease in profit because energy gets lost because of the round trip efficiency of the Powerwall.
  • Worst case in the example scenario described above (€0.04 per kWh for delivery), without net metering I would have €140.17 energy costs with a Powerwall and €501.11 costs without. Here it starts to work out.
  • With a yearly cost saving of €360.94 (501.11-140.17) it would take around 23 years to make a Tesla Powerwall profitable in my current situation.

So is it worth investing in a Tesla Powerwall to use more of my own solar power?

It depends on your investment horizon. But for me it’s a “No”. 23 years is a bit too much. Taking into account that the Tesla Powerwall 2 only has a warranty period of 10 years. Besides that the battery quality will get worse over time and the storage capacity of the Powerwall will decrease.

Other considerations

Timing of Heating Hot-water Storage Tank

You have to get it while its hot, right? Definitely with solar. To prepare for the year 2023, you should use as much electricity from your solar panels directly as possible. Also if you have a Tesla Powerwall.

I’m not going to cook earlier during the day. And the low-temperature heating mostly happens in the winter during the night to keep the in-house temperature stable, which is supposed to be efficient already. But what could be done is heating the hot-water storage tank during the day, when there is solar power available. This should decrease the amount of electricity delivered to and used from the electricity network.


You don’t have to buy a Telsa Powerwall. There are many alternatives, like a battery from the LG Chem RESU series. I’ve done the same calculations as with the Powerwall, without net metering, €0.04 per kWh for delivery. With a Return on Investment of 12 to 14 years, this seems to be more interesting then the Powerwall.

Net deliveryNet UsageStored kwhInvestmentSavings / YearROI
No battery4841 (82%)2779 (72%)0 (0%)€ 0€ 0
Powerwall2877 (49%)1021 (26%)1965 (33%)€ 8240€ 360.9422.8 years
RESU 132958 (50%)1009 (26%)1884 (32%)€ 6853€ 367.1818.7 years
RESU 103077 (52%)1118 (29%)1765 (30%)€ 4961€ 344.6914.4 years
RESU 6.63302 (56%)1327 (34%)1540 (26%)€ 3812€ 301.4412.6 years
RESU 3.33870 (65%)1861 (48%)972 (16%)€ 2602€ 190.6613.6 years

Measuring Power Consumption with Broadlink SP3S, python, influxdb and grafana

A while ago I was researching the possibilities to measure the power consumption of some devices in my house via Wifi. I came across the Broadlink SP3S Smart Plug. It met my requirements: relatively cheap, power measurement and Wifi. It comes with an IOS and Android App. There a big chance the app is not directly connecting to the SP3S, but to “the Cloud” where the SP3S sends its data to. This is how most companies design their products nowadays. I wasn’t really looking forward to share my power consumption data with Broadlink in “the Cloud”. With the App you can also turn the power on/off, which scares me a little bit. The Broadlink Cloud controlling this power switch. Nah, not for me.

I will explain how I installed the Broadlink SP3S without it making a connection to the internet and show how I use a python script to read the power meter data from the SP3S, store it to InfluxDB and use Grafana to display the collected data in a graph.

Note: When you want to buy a Broadlink SP3S, please make sure you buy the SP3S and not the SP3, which only is a power switch, not a power meter.

Install the Broadlink SP3S

In the step-by-step instructions below I will configure the SP3S to connect to my Wifi so I can connect to it from my local network to retrieve the power meter data. I use a laptop running Linux to connect to initially connect to the SP3S to configure it. I also run a Debian Linux machine as router to control the firewall between the local network and the internet.

  • Plug the SP3S in a wall socket
  • Press the On/Off button for 6 seconds to reset the SP3S. The power button starts blinking rapidly.
  • Press the On/Off button another 6 seconds to enable the Wifi Access Point on the SP3S. The power button blinks rapidly with pauzes.
  • Connect to the Wifi Access Point, it should be called “BroadlinkProv”
  • Look up the MAC address of the SP3S
$ ip neigh dev wlp3s0 lladdr 34:ea:34:79:7b:ff REACHABLE
  • Block the MAC address to access the Internet in the router (I’m using a Debian Linux machine as router). It is important to block the MAC address before connecting the SP3S to your Wifi network so that it will never be able to access the internet.
$ iptables -A FORWARD -m mac --mac-source 34:ea:34:79:7b:ff -j DROP
$ ip6tables -A FORWARD -m mac --mac-source 34:ea:34:79:7b:ff -j DROP
$ git clone
$ cd python-broadlink
$ python3 -m venv venv
$ . venv/bin/activate
$ pip3 install pyaes
$ mkdir lib
$ ln -s broadlink lib/broadlink
$ python3
Python 3.5.3 (default, Sep 27 2018, 17:25:39)
[GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import broadlink
>>> broadlink.setup('myssid', 'mynetworkpass', 3)
  • Now you will get disconnected from the SP3S Wifi Access Point. The SP3S will connect to the Wifi network configured above

When this firewall rule is added to the router as well, you will see that the SP3S immediately tries to connect to the internet.

$ iptables -I FORWARD -m mac --mac-source 34:ea:34:79:7b:ff -j LOG --log-level debug --log-prefix "Broadlink: "

$ tail /var/log/syslog
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=258 PROTO=UDP SPT=16404 DPT=16384 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=259 PROTO=UDP SPT=16404 DPT=1812 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=260 PROTO=UDP SPT=16404 DPT=8080 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=261 PROTO=UDP SPT=16404 DPT=80 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=262 PROTO=UDP SPT=16404 DPT=8090 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=263 PROTO=UDP SPT=16404 DPT=16384 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=264 PROTO=UDP SPT=16404 DPT=1812 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=265 PROTO=UDP SPT=16404 DPT=8080 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=266 PROTO=UDP SPT=16404 DPT=80 LEN=56
Broadlink: IN=eth1 OUT=eth0 MAC=e0:69:95:73:10:bf:34:ea:34:79:7b:ff:08:00 SRC= DST= LEN=76 TOS=0x00 PREC=0x00 TTL=63 ID=267 PROTO=UDP SPT=16404 DPT=8090 LEN=56

Let’s try to find out what the destination IP addresses are by using tcpdump.

$ tcpdump -ni eth1 host and port 53
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth1, link-type EN10MB (Ethernet), capture size 262144 bytes
12:38:22.460717 IP > 0+ A? (44)
12:38:22.460870 IP > 0 1/0/0 A (60)
12:38:38.480835 IP > 0+ A? (46)
12:38:38.480962 IP > 0 1/0/0 A (62)

So the SP3S immediately tries to contact ( and ( on ports 16384, 1812, 8080, 80 and 8090 once it has a network connection. The iptables DROP rules in my router block this traffic. 🙂

Using broadlink_cli to retrieve meter data

Using “broadlink_cli” from python-broadlink the current energy consumption can be retrieved from the SP3S. To make “broadlink_cli” work, some things need to be modified when using the cloned git repository as python library.

Create a symlink to “broadlink_cli”:

$ ln -s cli/broadlink_cli

Edit “broadlink_cli” and change this:

import broadlink
import sys


import sys
sys.path.insert(0, './')
import broadlink

Retrieve the current usage from the SP3S using “broadlink_cli”:

$ ./broadlink_cli --type 0x947a --host --mac 34ea34797bff --energy

Turn on the power:

$ ./broadlink_cli --type 0x947a --host --mac 34ea34797bff --turnon
== Turned * ON * ==

Store the power meter data to InfluxDB

The python script below reads the power consumption every 30 seconds from the SP3S and stores it to InfluxDB.

#!/usr/bin/env python3
import sys
import time
import datetime
from influxdb import InfluxDBClient

sys.path.insert(0, './')
import broadlink

name = '<NAME>' # What is the SP3S connected to?
type = int(0x947a) #
host = ''
mac = bytearray.fromhex('34ea34796e9c') # The MAC address of the SP3S (without colons!)

dev = broadlink.gendevice(type, (host, 80), mac)
influx_client = InfluxDBClient('<INFLUXDB_HOSTNAME>', 8086, '<USERNAME>', '<PASSWORD>', '<DATABASE>')

def get_data():
    return dev.get_energy()

def influx(value):
    if value is None:
    json_body = [
            "measurement": name,
            "fields": {
                "usage": float(value),
        print('Failed to write to influxdb')

while True:
    except Exception as err:
        print('Error: %s' % str(err))

Graphing the result in Grafana

In grafana use this configuration for the graph. Replace <NAME> with the name that is in the script.

Some interesting results

Measuring the power usage of several devices gives interesting insight in what a device is actually doing power-wise. Some examples are below.

The washer consumes around 2200 Watt at the beginning of a ~1:45h, 40°C program. And at the end about 500 Watt to centrifuge to dry the clothes a little bit.
The washer consumes 2200 Watt a bit longer in case of a ~1:45h, 60°C program.
My washer is actually a wash-dry combination. When starting the dry program after a ~1:45h, 40°C program you see that drying consumes even more energy than washing.
The fridge consumes around 80 Watt about 30% of the time too keep the fridge cool. When you look good you actually see 3 mini-spikes in the morning where I opened the fridge and the light turned on.
The electric heatpump starts heating the 150 liter hot water tank at 23:00. It ramps up to 1250 Watt. It starts exactly when electricity switches to low tariff, smart 🙂 The heatpump also heats the house and tries to keep it around one temperature level. This is the most power efficient for a well isolated house they say. The heatpump is consuming 700 Watt for this continuously when it gets colder in the house during the night.
When it gets too warm in the house the heatpump also has the ability to cool. This is less power consuming than heating.
And sometimes this heating/cooling system is just stupid. During the day it is too warm and the same night it is too cold.

Routed IPTV via a Debian router (XS4ALL or KPN)

At home my FTTH Internet connection is provided by XS4ALL. They provide a FRITZ!Box router to connect to the Internet. Instead of using the FRITZ!Box I’ve always used my own Debian GNU/Linux machine to route traffic to the internet.

The XS4ALL uplink has 2 VLANs:

  • VLAN4: TV (bridged, RFC1483)
  • VLAN6: PPPoE IPv4 + IPv6 internet connection

My XS4ALL uplink is connected to a managed switch. My Motorola 1963 TV Receiver is directly connected to an untagged VLAN4 port on my switch. This way the TV Receiver is directly connected to the TV platform on OSI Layer 2.

Recently I got a letter from XS4ALL saying that this setup is going to change. The TV Receiver can not be connected to the TV platform directly anymore, but needs to be part of the internal network. This adds the ability to support Internet services (like Youtube, Netflix, etc.) on the TV Receiver.

Current setup

In my current setup the upstream connection is connected to a managed switch. VLAN4 and VLAN6 are tagged on this switchport. The TV Receiver is connected to an untagged VLAN4 switchport. It can directly communicate with the TV platform. The Debian Router is connected to a tagged VLAN6 switchport for internet access and a tagged VLAN1 switchport for the local network. Devices on the local network connect to the Internet via the Debian Router on VLAN1.

New setup

In the new setup the TV Receiver is not in untagged VLAN4 anymore. Instead VLAN4 is now tagged on the switchport of the Debian Router as it will function as a gateway to the TV Platform. I created VLAN104 in which the TV Receiver will be. It’s also possible to create a setup where the TV Receiver is in VLAN1, but my Managed Switch currently doesn’t support IGMP Snooping. The result of that would be that if you are watching TV, all other devices in VLAN1 also receive the IPTV multicast traffic.

Layer 2 / Layer 3 view

In a more detailed view, leaving out the physical hardware, it looks like the diagram below. Local devices on VLAN1 access the Internet through the Debian Router, which routes the traffic to VLAN6. The TV Receiver on VLAN104 accesses the TV Platfrom through the Debian router, which routes it to VLAN4. The Debian Router runs an igmpproxy to route Multicast Traffic (IPTV) from VLAN4 to VLAN104. The red arrow shows that the TV Receiver is now also able to access the Internet for for services like Youtube or Netflix.

How is the Debian Router configured?

First of all the Debian Router has 1 physical interface, 4 VLAN interfaces and 1 PPPoE interface. They are configured in /etc/network/interfaces:

auto eth0
iface eth0 inet manual
    up ip link set up dev eth0
    down ip link set down dev eth0

auto vlan1
iface vlan1 inet manual
    pre-up ip link add link eth0 name vlan1 type vlan id 1
    up ip link set up dev vlan1
    up ip addr add brd + dev vlan1
    down ip addr del dev vlan1
    down ip link set down dev vlan1
    post-down ip link delete vlan1

auto vlan4
iface vlan4 inet manual
    pre-up ip link add link eth0 name vlan4 type vlan id 4
    up ip link set up dev vlan4
    post-up dhclient vlan4
    pre-down dhclient -x
    down ip link set down dev vlan4
    post-down ip link delete vlan4

# Internet (PPPoE)
auto vlan6
iface vlan6 inet manual
    pre-up ip link add link eth0 name vlan6 type vlan id 6
    up ip link set up dev vlan6
    down ip link set down dev vlan6
    post-down ip link delete vlan6

# IPTV (Internal)
auto vlan104
iface vlan104 inet manual
    pre-up ip link add link eth0 name vlan104 type vlan id 104
    up ip link set up dev vlan104
    up ip addr add brd + dev vlan104
    down ip addr del dev vlan104
    down ip link set down dev vlan104
    post-down ip link delete vlan104

auto xs4all
iface xs4all inet ppp
    provider xs4all

The DHCP client configuration in /etc/dhcp/dhclient.conf will request a subnet-mask (option 1), broadcast-address (option 28), routers (option 3) and Classless Static Routes (option 121) on VLAN4:

option rfc3442-classless-static-routes code 121 = array of unsigned integer 8;
interface "vlan4" {
  request subnet-mask, broadcast-address, routers, rfc3442-classless-static-routes;
  send vendor-class-identifier "IPTV_RG";

This will result in the fact that the vlan4 interface will get an IP address and additional routes will be added to the route table of the Debian Router to be able to access the TV Platform:

# ip addr show dev vlan4
5: vlan4@eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default qlen 1000
    link/ether 00:1b:21:c3:f8:90 brd ff:ff:ff:ff:ff:ff
    inet brd scope global vlan4
       valid_lft forever preferred_lft forever

# ip route | grep vlan4 dev vlan4 proto kernel scope link src via dev vlan4

Configure /etc/igmpproxy.conf to forward multicast traffic from VLAN4 to VLAN104:

phyint vlan4 upstream  ratelimit 0  threshold 1

phyint vlan104 downstream  ratelimit 0  threshold 1

Make sure IPv4 forwarding is enabled:

# cat /proc/sys/net/ipv4/ip_forward

And configure IPTables to allow the traffic we want to allow:

# allow igmpproxy traffic to the TV Receiver
iptables -A INPUT -i vlan104 -j ACCEPT
iptables -A OUTPUT -o vlan104 -j ACCEPT

# allow dhclient + igmpproxy traffic to the TV Platform
iptables -A INPUT -i vlan4 -d -j ACCEPT
iptables -A OUTPUT -o vlan4 -p udp --dport 68 -j ACCEPT
iptables -A OUTPUT -o vlan4 -p igmp -d -j ACCEPT

# allow TV Receiver traffic to the TV Platform and apply Source NAT
iptables -A FORWARD -i vlan104 -o vlan4 -j ACCEPT
iptables -A FORWARD -i vlan4 -o vlan104 -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A FORWARD -i vlan4 -o vlan104 -p udp -d -j ACCEPT
iptables -t nat -A POSTROUTING -o vlan4 -j MASQUERADE

# allow TV Receiver traffic to the internet
iptables -A FORWARD -i vlan104 -o ppp0 -j ACCEPT
iptables -A FORWARD -i ppp0 -o vlan104 -m state --state RELATED,ESTABLISHED -j ACCEPT
iptables -t nat -A POSTROUTING -o ppp0 -j MASQUERADE

Debian Jessie: bye bye bind9 + dnssec-tools, hello PowerDNS

I recently upgraded my DNS server to Debian Jessie. In fact I reinstalled it from scratch and used puppet to install and configure all the required components. This DNS server, running bind9, is the authoritative nameserver for uses DNSSEC. To apply DNSSEC I used dnssec-tools, which gives you tools like zonesigner, rollerd and donuts to sign, roll and check your DNSSEC enabled zones. Two years ago I had a hard time setting this up, hitting various bugs in dnssec-tools 1.13-1 from Debian Wheezy. I ended up running a quite stable setup after packaging dnssec-tools 1.14 and using a patched version of zonesigner that didn’t increase the serial of the zone.

While installing the same setup on Debian Jessie, I noticed that dnssec-tools wasn’t in Jessie because of a bug in rollerd. I decided to install the dnssec-tools 1.14 package I used before on Debian Wheezy. This all seemed fine until I receive this email from my daily donuts run:

undefined method Net::DNS::RR::new_from_hash at /usr/lib/x86_64-linux-gnu/perl5/5.20/Net/DNS/ line 791.
Net::DNS::RR::AUTOLOAD("Net::DNS::RR", "rname", "", "serial", 2014081039, "class", "IN", "expire", 1814400, ...) called at /usr/share/perl5/Net/DNS/ZoneFile/ line 201
Net::DNS::ZoneFile::Fast::parse("file", "", "origin", "", "soft_errors", 1, "on_error", CODE(0x4ec0698)) called at /usr/sbin/donuts line 338

This thread indicated there are more related issues in the dnssec-tools package.

Time to re-evaluate. Debian Jessie is frozen, dnssec-tools didn’t get in and there is not much conversation going on in bugreport #754704 that kicked dnssec-tools out of testing. I also can’t update the signed zones as long as this is broken and the current signed zone is valid until 3 weeks from now. 😥

OpenDNSSEC looked like an alternative. I could have also used the tools that come with bind9 to sign, roll and check my zones. But I liked to try something new, PowerDNS.

# apt-get install pdns-server

As a previous bind9 user, the easiest way was to put all zone configuration from my original named.conf in /etc/powerdns/bindbackend.conf. I was amazed. It just worked. 😀

The zone was still a pre-signed DNSSEC zone. While reading the PowerDNS documentation I found out that PowerDNS is able to do “Front-signing”, which is an amazing feature. PowerDNS does the signing part on-the-fly. There is no need to re-sign the zone every time you make a change to the zone.

First of all I changed the filename in /etc/powerdns/bindbackend.conf to the unsigned one. After that I created a database to manage the DNSSEC keys, added a line to the PowerDNS configuration to use this database and restarted PowerDNS.

# pdnssec create-bind-db /var/lib/powerdns/bind-dnssec-db.sqlite3
# echo "bind-dnssec-db=/var/lib/powerdns/bind-dnssec-db.sqlite3" &gt;&gt; /etc/powerdns/pdns.d/pdns.simplebind.conf
# systemctl restart pdns

I liked to keep the KSK and ZSKs I was already using for my zone, so I imported those.

# pdnssec import-zone-key KSK
# pdnssec import-zone-key ZSK
# pdnssec import-zone-key ZSK
# pdnssec deactivate-zone-key 3
# pdnssec rectify-zone
# dig +short +dnssec SOA 2014081039 28800 3600 1814400 600
SOA 8 2 600 20150115000000 20141225000000 43743 lqH6nrHf6YPcLv2TgQgC4gOI4gOGORsmfj/LDJAhu+GpWpiFTnQGtj08 I2TocYQ0jwkoar370quZyvKNAyjTBGNUw6rOxdjbxAn8DhMpBPi7TMfq PP7NXJLkxbx2aIW9r1C0iMk5WAYbi01bEsJY014WiX+s+QdRDPwWaanZ zFI=

That’s it. I’m really happy PowerDNS integrated DNSSEC in it’s product instead of having an additional toolset to manage DNSSEC pre-signed zones.


On January 19th, 20:39:59 UTC, it got completely out of hand. The images below from showed me the zone was expired on all the Authoritative DNS slaves.

Hovering with my mouse over the purple lines showed me the expired status:

While the zone hosted on the Authoritative DNS master was completely fine:

What was going on here? 😕

It was clear that the slaves didn’t transfer the zone after it was re-signed by the Authoritative DNS master. According to RFC 1996 the SOA record should be increased if you want the Authoritative DNS slaves to update their zones. This is something that was clearly not done in my case.

I found the SOA-EDIT setting. My current SERIAL is configured in the YYYYMMDDSS format, so I configured the SOA-EDIT setting to use INCEPTION-INCREMENT.


This overrules the SERIAL that is configured in the on-disk zone-file. Every Thursday after the zone is re-signed the SERIAL is automatically increased and all Authoritative DNS slaves will transfer the new zone.

SSD caching using Linux and bcache

A couple of manufacturers are selling solutions to speed up your big HDD using a relative small SSD. There are techniques like:

But there is also a lot of development on the Linux front. There is Flashcache developed by Facebook, dm-cache by VISA and bcache by Kent Overstreet. The last one is interesting, because it’s a patch on top of the Linux kernel and will hopefully be accepted upstream some day.

Hardware setup

In my low power homeserver I use a 2TB Western Digital Green disk (5400 RPM). To give bcache a try I bought a 60GB Intel 330 SSD. Some facts about these data-carriers. The 2TB WD can do about 110 MB/s of sequential reads/writes. This traditional HDD does about 100 random operations per second. The 60GB Intel 330 can sequentially read about 500 MB/s and write about 450 MB/s. Random reads are done in about 42.000 operations per second, random writes in about 52.000. The SSD is much faster!

The image below shows the idea of SSD caching. Frequently accessed data is cached on the SSD to gain better read performance. Writes can be cached on the SSD using the writeback mechanism.

Prepare Linux kernel and userspace software

To be able to use bcache, there are 2 things needed:

  1. A bcache patched kernel
  2. bcache-tools for commands like make-bcache and probe-bcache

I used the latest available 3.2 kernel. The bcache-3.2 branch from Kent’s git repo merged successfully. Don’t forget to enable the BCACHE module before compiling.

On my low power home server I use Debian. Since there is no bcache-tools Debian package available yet, I created my own. Fortunately damoxc already packaged bcache-tools for Ubuntu once.

Debian package:…/bcache/
Git web:;a=summary

Bcache setup

Unfortunately bcache isn’t plug-and-play. You can’t use bcache with an existing formatted partition. First you have to create a caching device (SSD) and a backing device (HDD) on top of two existing devices. Those devices can be attached to each other to create a /dev/bcache0 device. This device can be formatted with your favourite filesystem. The creation of a caching and backing device is necessary because it’s a software implementation. Bcache needs to know what is going on. For example when booting, bcache needs to know what devices to attach to each other. The commands for this procedure are shown in the image below.

After this I had a working SSD caching setup. Frequently used data is stored on the SSD. Accessing and reading frequently used files is much faster now. By default bcache uses writethrough caching, which means that only reads are cached. Writes are being written directly to the backing device (HDD).

To speed up the writes you have to enable writeback caching. But you have to take in mind, there is a risk of losing data when using a writeback cache. For example when there is a power failure or when the SSD dies. Bcache uses a fairly simple journalling mechanism on the caching device. In case of a power failure bcache will try to recover the data. But there is a chance you will end up with corruption.

echo writeback &gt; /sys/block/sda/sda[X]/bcache/cache_mode

When writes are cached on the caching device, the cache is called dirty. The cache is clean again, when all cached writes have been written to the backing device. You can check the state of the writeback cache via:

cat /sys/block/sda/sda[X]/bcache/state

To detach the caching device from the backing device run the command below (/dev/bcache0 will still be available). This can take a while when the write cache contains dirty data, because it must be written to the backing device first.

echo 1 &gt; /sys/block/sda/sda[X]/bcache/detach

Attach the caching device again (or attach another caching device):

echo [SSD bcache UUID] &gt; /sys/block/sda/sda[X]/bcache/attach

Unregister the caching device (can be done with or without detaching) (/dev/bcache0 will still be available because of the backing device):

echo 1 &gt; /sys/fs/bcache/[SSD bcache UUID]/unregister

Register the caching device again (or register another caching device):

echo /dev/sdb[Y] &gt; /sys/fs/bcache/register

Attach the caching device:

echo [SSD bcache UUID] &gt; /sys/block/sda/sda[X]/bcache/attach

Stop the backing device (after unmounting /dev/bcache0 it will be stopped and removed, don’t forget to unregister the caching device):

echo 1 &gt; /sys/block/sda/sda[X]/bcache/stop


To benchmark this setup I used two different tools. Bonnie++ and fio – Flexible IO tester.



Unfortunately Bonnie++ isn’t that well suited to test SSD caching setups.

This graph shows that I’m hitting the limit on sequential input and output in the HDD-only and SSD-only tests. The bcache test doesn’t show much difference to the HDD-only test in this case. Bonnie++ isn’t able to warm up the cache and all sequential writes are bypassing the write cache.

In the File metadata tests the performance improves when using bcache.



The Flexible IO tester is much better to benchmark these situations. For these tests I used the ssd-test example jobfile and modified the size parameter to 8G.

seq-read: io=4084MB, bw=69695KB/s, iops=17423, runt= 60001msec
rand-read: io=30308KB, bw=517032B/s, iops=126, runt= 60026msec
seq-write: io=2792MB, bw=47642KB/s, iops=11910, runt= 60001msec
rand-write: io=37436KB, bw=633522B/s, iops=154, runt= 60510msec

seq-read: io=6509MB, bw=110995KB/s, iops=27748, runt= 60049msec
rand-read: io=1896MB, bw=32356KB/s, iops=8088, runt= 60001msec
seq-write: io=2111MB, bw=36031KB/s, iops=9007, runt= 60001msec
rand-write: io=1212MB, bw=20681KB/s, iops=5170, runt= 60001msec

seq-read: io=4127.9MB, bw=70447KB/s, iops=17611, runt= 60001msec
rand-read: io=262396KB, bw=4367.8KB/s, iops=1091, runt= 60076msec
seq-write: io=2516.2MB, bw=42956KB/s, iops=10738, runt= 60001msec
rand-write: io=2273.4MB, bw=38798KB/s, iops=9699, runt= 60001msec

In these tests the SSD is much faster with random operations. With the use of bcache random operations are done a lot faster in comparison to the HDD-only tests. It’s interesting that I’m not able to hit the sequential IO limits of the HDD and SSD in these tests. I think this is because my CPU (Intel G620) isn’t powerful enough for these tests. fio hits the IO limits of the SSD in an another machine with a Intel i5 processor.

Less CPU overhead with Qemu-KVM from Debian Wheezy

An interesting thing happened last week when I upgraded qemu-kvm from version 0.12.5 (Debian Squeeze) to 1.1.2 (Debian Wheezy). After a reboot (shutdown and start) of all my VM’s, they are using less CPU in total! I noticed this from the stats Collectd is collecting for me.

I’m running about 5 Debian Linux VM’s on my low power home server (Intel G620, 8G DDR3, DH67CF). Most of the time the VM’s are idle. As you can see in the graph below the CPU usage dropped. In particular the System CPU usage. The Wait-IO usage is mostly from Collectd, saving all the stats.

Looking a bit further I also noticed that the Local Timer Interrupts and Rescheduling Interrupts are halved.

They’ve done a nice job at Qemu-KVM!

Testing a D-Link Green Switch

Since a while I’ve been monitoring the power consumption of devices in my home using a power meter from (advised by Remi). This power meter is a good one because it is very precise. It starts measuring at 0.2 Watt.

I needed an Ethernet switch to connect my TV, NMT and PS3 to my home network. While searching for a proper switch, I came across It looked promising. The Green Calculator, a 8.7MB Flash app which is using a lot of CPU (hello D-Link! is this Green?!? what about HTML5?), showed me I could save 70,98% of energy (2h, 1-5 ports > 28.7Wh D-Link Green vs. 99Wh Conventional per day) using D-Links Green technology.

I couldn’t find any green switches from other manufacturers so gave it a try. I bought a D-Link DGS-1005D. It’s a 5-ports unmanaged Gigabit Ehternet switch, supporting IEEE802.3az (Energy Efficient Ethernet), IEEE802.3x (Flow Control), 9000 bytes Jumbo Frames and IEEE802.1p QoS (4 queues).

So I did some tests using the power meter. As reference I used a HP Procurve 408 (8 ports 100Mbit switch).

HP Procurve 408

Port # 1 2 3 4 5 Watt 24h 2h + 22h idle kWh annually
Adapter 1.4 33.6 33.6 12.264
4.4 105.6 105.6 38.544
m 4.9 117.6 106.6 38.909
m m 5.4 129.6 107.6 39.274
m m m 5.9 141.6 108.6 39.639
m m m m 6.4 153.6 109.6 40.004
m m m m m 6.8 163.2 110.4 40.296

D-Link DGS-1005D

Port # 1 2 3 4 5 Watt 24h 2h + 22h idle kWh annually
Adapter 0.0 0.0 0.0 0.0 🙂
1.1 26.4 26.4 9.636
g 1.6 38.4 27.4 10.001
g m 1.8 43.2 27.8 10.147
g m g 2.1 50.4 28.4 10.366
g m g g 2.5 60 29.2 10.658
g m g g g 2.9 69.6 30 10.950
g m m g g 2.7 64.8 29.6 10.804
g m m m g 2.5 60 29.2 10.658
g m m m m 2.3 55.2 28.8 10.512

m = 100 Mbit, g = 1 Gbit

First of all it’s interesting to see that the power adapter from HP is using 1.4 watts on it’s own already. Besides that it’s nice to know that a 100Mbit port uses less energy than a Gigabit port. The Green Calculator is quiet right in my case. I’m saving about 72~74% of energy.

ext3 overhead on 1TB storage

Recently I bought a portable harddrive from Western Digital. The Western Digital Elements (WDE1U10000E) carries 1 Terabyte of space and can be connected via USB 2.0. 1TB, for just 99,00 Euro (2008-12-27). According to Wikipedia and the SI standard the drive must contain 1,000,000,000,000 bytes (1TB). fdisk shows us:

Disk /dev/sda: 1000.2 GB, 1000204886016 bytes

So that is correct.

The disk is preformatted FAT32. After mounting the disk, df shows me there is actually 976283280 KiB (932GiB) available. This is about 999714078720 bytes. It looks like 490807296 bytes (468MiB) is gone, but it must be used for File Allocation Tables.

Because FAT32 is crappy old and I use Linux and want a journaling filesystem, I will reformat the device with ext3. After setting the right partition table type via fdisk.

$ mkfs.ext3 -m0 /dev/sda1
mke2fs 1.41.3 (12-Oct-2008)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
61054976 inodes, 244190000 blocks
0 blocks (0.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=0
7453 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
        102400000, 214990848

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

After a minute or 10 (yeah! USB 2.0) it was all done. First of all 2646016 bytes (2584 KiB) is not formatted (1000204886016 – (244190000 * 4096)). After mounting the disk, df shows me this time there is 961432072 KiB (917 GiB) available. This is less then FAT32, but we have a journaling filesystem now. 15327928 (97676000 – 961432072) KiB is used for that. But why and how?

dumpe2fs /dev/sda1 shows us:

dumpe2fs 1.41.3 (12-Oct-2008)
Filesystem volume name:   <none>
Last mounted on:          <not available>
Filesystem UUID:          0bdd2888-06fc-4b22-a6e5-987ac65236ee
Filesystem magic number:  0xEF53
Filesystem revision #:    1 (dynamic)
Filesystem features:      has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file
Filesystem flags:         signed_directory_hash
Default mount options:    (none)
Filesystem state:         clean
Errors behavior:          Continue
Filesystem OS type:       Linux
Inode count:              61054976
Block count:              244190000
Reserved block count:     0
Free blocks:              240306876
Free inodes:              61054965
First block:              0
Block size:               4096
Fragment size:            4096
Reserved GDT blocks:      965
Blocks per group:         32768
Fragments per group:      32768
Inodes per group:         8192
Inode blocks per group:   512
Filesystem created:       Sun Dec 28 14:22:22 2008
Last mount time:          Sun Dec 28 14:35:20 2008
Last write time:          Sun Dec 28 14:35:20 2008
Mount count:              1
Maximum mount count:      36
Last checked:             Sun Dec 28 14:22:22 2008
Check interval:           15552000 (6 months)
Next check after:         Fri Jun 26 15:22:22 2009
Reserved blocks uid:      0 (user root)
Reserved blocks gid:      0 (group root)
First inode:              11
Inode size:               256
Required extra isize:     28
Desired extra isize:      28
Journal inode:            8
Default directory hash:   half_md4
Directory Hash Seed:      0bda5622-6cc2-4a1a-8135-c3f810580d43
Journal backup:           inode blocks
Journal size:             128M
  • /dev/sda1 has 7453 block groups.
  • Inode size is 256 bytes.
  • 8192 inodes for each block group.

7453 * 256 * 8192 makes 15630073856 bytes (15263744 KiB) for inode space.

15327928 – 15263744 = 64184 unexplained KiB left

Besides one primary superblock, 18 extra backup superblocks are stored on the disk. A superblock is 256 bytes, though it is stored in a 4 KiB block. 19 * 4096 makes 77824 bytes (76 KiB).

64184 – 76 = 64108 unexplained KiB left

If someone has an explanation for it, please leave a reply.

61054976 inodes means there can be stored over 61 million files on the formatted 917 GiB. This is way too much for me. 10% of it is enough too, so there will also be less space needed for storing the inodes. Formatting the disk with option -i 131072 sould better fit me.