Home Assistant: Heat Pump Automation with Cheap SPOT hours and Github Copilot doing the work

Introduction

Finland has been part of Nord Pool, a pan-European power exchange, since 1998. Meaning, when you sign your power contract with electricity supplier, you can choose a contract utilising the power stock exchange prices.

The prices for the next day are announced every day around 1pm CET. You can combine this information for example with weather forecast to plan your electricity usage for the cheapest hours where applicable.

Home Assistant on the other hand has Nord Pool integration which enables you to optimise the electricity SPOT pricing. There is a lot of articles on how to do that to help you to get started. This articles goes through my current setup and my own experience with both Home Assistant and electricity stock pricing. And how I made everything working with GitHub Copilot vim plugin.

Typical claim is, that normal user cannot really utilise the power stock pricing since it is too much work, warming up the house takes constant amount of energy so there is no way to optimise or it is too much work to do the automation in he first place. The latter might be true, but if you take building a smart home as a hobby, then even that is not true. The more time it takes, the more fun it is.

Home Assistant is a hobby anyway. It’s non commercial product and it is Cloud independent: Meaning, you set it your yourself and you maintain it yourself in your own server. That being said, it is fairly easy to set up. You just need to have a server to install it. That can be dedicated server or mini computer like Raspberry Pi, old PC you have no other use or something that can run Linux.

My choice was to to use my Asus PN41 mini PC I already had running Ubuntu which I had set up earlier to run as my sandbox having several virtual machines running in it. Instead of adding another virtual machine I decided to setup Home Assistant as Docker Container. Installation and set up did not really take too long time. Once I installed mobile app to my phone I already had working setup.

The reason why I wanted to have Home Assistant in the first place though is, that I had two Toshiba Shorai Edge heat pump internal units installed, and Toshiba’s mobile app is installable only with European apple id. I have North American apple id and I really cannot change that, since although living most of the time in Europe, I have close ties to North America. After some googling I figured out that I can get around the limitation with this totally new thing for me at the time called Home Assistant.

Not only did I get the heat pump controls work with Toshiba AC integration I also got the Nord Pool spot prices available on nice ApexCharts and even predict for Nord Pool prices relying on Random forest machine learning algorithms as illustrated below.

After I had Home Assistant container running, Toshiba AC integration installed and mobile app on my phone, I was good to go. Setup up is really fast to do as long as one is familiar with the related technology it really doesn’t take more than an hour. My initial aim was just to be able to manage the internal heating units through my phone. Then later I noticed that ok, it is also much easier, for example, to schedule the heat pumps to different temperatures different times with Home Assistant than with extremely cumbersome Toshiba remote.

On the other hand, I noticed Home Assistant itself had plenty of other interesting features I could utilise while building a smart home gradually. I got four Shelly H&T and one  Shelly Plus H&T thermometers I could have on my Home Assistant dashboard. Three Shelly Plugs to monitor electricity usage for the Heat Pump and other appliances.

Automation

Just having Home Assistant Mobile App running enabled me being able to control heat pump units, follow room temperatures, current weather and forecast, electricity consumption and price is of course nice, but everything is still done manually. I felt I’m missing at least half of the benefits and nothing really changed anything yet.

The I found this blog post on how to automate device for cheapest hours and it was pretty much all I was looking for. At least on idea level it was. It grabs the next days cheapest electricity prices and one can schedule heat pump to increase temperature when the electricity is on it’s cheapest. This happens typically at night – it is just after midnight almost always. I wasn’t very familiar with yaml and I still find the syntax cumbersome to get anything working – anything working easily at least. There’s plenty of scheduling solution with GUI based forms, but for me understanding those was even more difficult. I got this solution for getting next day’s cheapest hours and increase heating during them to work fine except for one thing. Once it started, it did not stop without manually stopping it.

I decided to create a schedule which set the heating back from 24C to 20C at 5am. With Home Assistant of course. If the cheapest hours are at day time, that does not work though. But it worked well enough almost for a year. Then I got more involved with yaml while learning Ansible and writing Pipelines for Azure with yaml. I also utilised yaml syntax highlighting on vim, so it all started to get easier.

Why write own code when there’s Github Copilot

The biggest motivator I found was Github Copilot. I started to use it while writing Python code, but noticed it helps quite a lot with yaml too. I only wanted to change my automations.yaml slightly. I wanted to get the part working, where the heating should stop. And I don’t want any heating blowing full 24C during day time either. Copilot does not write it to you, but it makes it easier to get it done.

So I did this: added the time conditions with after and before.

The code without timing conditions are available from the blog post link above, so I’m not writing it here, although you can check my full automations.yaml from my GitHub repo – not that I expect it to help anyone or to be perfect, but there it is. Then next thing is to stop the increased heating.

I don’t have time conditions there, but I will add them later once I have verified everything works correctly. With Home Assistant it’s better to build things gradually. Then you know easier what does not work and what does.

I also wanted to have things like: If electricity is more expensive than 15c/kWh, decrease heating by 1C:

The above is partly written by ChatGPT, but it typically generates code, which needs a lot of tweaking to get it to work for real, but some of it is usable.

I also often turn heater off when outside is a bit warmer and don’t necessarily remember to put it on before going to sleep. At least in theory this could lead to situation where it gets really cold at night, and then the heater is off when temperature is way below 0C. Then one should really not turn it on anymore before it gets warmer, since it decreases the life of the outside unit some what. If not significantly even.

The yaml code needed

The examples here are pretty much copy/pasted from Toni’s blog post so credits to him.

configuration.yaml

Home Assistant needs a configuration file configuration.yaml and there you need following to get the cheapest hours utilised.

# Helper to keep the start time
input_datetime:
  device_start_time:
    name: Device Start Time
    has_time: true
    has_date: false
  device_end_time:
    name: Device End Time
    has_time: true
    has_date: false

# Include automations.yaml and sensors.yaml
automation: !include automations.yaml
sensor: !include sensors.yaml                                                                                                                                          

sensors.yaml

On sensors.yaml you need following. Note that sensor.nordpool_kwh_fi_eur_3_10_024 must be replaced with the sensor you have for Nord Pool integration.

- platform: template                                                                                                                                                   
  sensors:
    energy_spot_price:
      friendly_name: "Nordpool Spot Price"
      unit_of_measurement: 'c/kWh'
      value_template: "{{ ('sensor.nordpool_kwh_fi_eur_3_10_024')  }}"
    energy_helen:
      friendly_name: "Helen Spot Price €/kWh"
      unit_of_measurement: '€/kWh'
      value_template: "{{ (states('sensor.nordpool_kwh_fi_eur_3_10_024')  | float / 100  ) | round(4) + 0.0034 }}"
 
    energy_helen_alv10c:
      friendly_name: "Helen Spot Price c/kWh alv. 24%"
      unit_of_measurement: 'c/kWh'
      value_template: "{{ (states('sensor.nordpool_kwh_fi_eur_3_10_024')  |  float + 0.34 ) }}" 
 
    cheapest_hours_energy_tomorrow:
      device_class: timestamp
      friendly_name: Cheapest sequential electricity hours
      value_template: >
        {%- set numberOfSequentialHours = 3 -%} 
        {%- set lastHour = 23 -%} 
        {%- set firstHour = 0 -%} 
 
        {%- if state_attr('sensor.nordpool_kwh_fi_eur_3_10_024', 'tomorrow_valid') == true -%} 
          {%- set ns = namespace(counter=0, list=[], cheapestHour=today_at("00:00") + timedelta( hours = (24)), cheapestPrice=999.00) -%} 
          {%- for i in range(firstHour + numberOfSequentialHours, lastHour+1) -%} 
            {%- set ns.counter = 0.0 -%} 
            {%- for j in range(i-numberOfSequentialHours, i) -%} 
              {%- set ns.counter = ns.counter + state_attr('sensor.nordpool_kwh_fi_eur_3_10_024', 'tomorrow')[j] -%} 
            {%- endfor -%} 
            {%- set ns.list = ns.list + [ns.counter] -%} 
            {%- if ns.counter < ns.cheapestPrice -%} 
              {%- set ns.cheapestPrice = ns.counter -%} 
              {%- set ns.cheapestHour = today_at("00:00") + timedelta( hours = (24 + i - numberOfSequentialHours)) -%} 
            {%- endif -%} 
          {%- endfor -%} 
          {{ ns.cheapestHour }}
          {%- set ns.cheapestPrice = ns.cheapestPrice / numberOfSequentialHours -%} 
        {%- endif -%}                   

automations.yaml

Now Here are the triggers I have created in automations.yaml. I have three triggers for pumping up the heat with each one different action for cheap hours. Combining actions with one trigger seem not to work, or I don’t know correct syntax. I decrease the heat after four hours, but since I don’t need to stop heater, when the heating gets decreased. I have only two actions.

First I need to create the input_date times to use later:

# Set device start time: Needs cheapest_hours_energy_tomorrow in sensor.yaml                                                                                           
- id: '1663398489357'
  alias: 'Set device start time'
  description: ''
  trigger:
  - platform: time
    at: '23:10:00'
  condition:
  - condition: not 
    conditions:
    - condition: state
      entity_id: sensor.cheapest_hours_energy_tomorrow
      state: unknown
  action:
  - service: input_datetime.set_datetime
    data:
      time: '{{ as_timestamp(states(''sensor.cheapest_hours_energy_tomorrow'')) | timestamp_custom(''%H:%M'') }}'
    target:
      entity_id: input_datetime.device_start_time
 
 
# Set device end time 4 hours after start time: Needs cheapest_hours_energy_tomorrow in sensor.yaml
- id: '1663398489358'
  alias: 'Set device end time'
  description: ''
  trigger:
  - platform: time
    at: '23:15:00'
  condition:
  - condition: not 
    conditions:
    - condition: state
      entity_id: sensor.cheapest_hours_energy_tomorrow
      state: unknown
  action:
  - service: input_datetime.set_datetime
    data:
      time: '{{ ((as_timestamp(states(''sensor.cheapest_hours_energy_tomorrow'')) + (3600*4)) | timestamp_custom(''%H:%M'')) }}'
    target:
      entity_id: input_datetime.device_end_time
  mode: single

Then the actual triggers:

# Do the actions when time trigger is hit.
# Each action separately: Turn on, set temp, set fan mode
# Make sure AC is on before setting temp or fan mode
- id: '1663399614817'
  alias: Turn on Hallway AC
  description: 'Cheap energy time turn on hallway AC'
  trigger:
  - platform: time
    at: input_datetime.device_start_time
  condition: 
    condition: and
    conditions:
      - condition: time
        after: '00:00'
        before: '05:00'
  action:
  - service: climate.turn_on
    target:
      entity_id: climate.ac_12494102
  mode: single
  
# Set temp to 24C
- id: '1663399614818'
  alias: Increase heating
  description: 'Cheap energy time set heating to 24C'
  trigger:
  - platform: time
    at: input_datetime.device_start_time
  condition: 
    condition: and
    conditions:
      - condition: time
        after: '00:00'
        before: '05:00'
  action:
  - service: climate.set_temperature
    data:
      temperature: 24
    target:
      entity_id: climate.ac_12494102
  mode: single

# Set fan mode to high    
- id: '1663399614819'
  alias: Hallway AC fan to high
  description: 'Cheap energy time set fan to high'
  trigger:
  - platform: time
    at: input_datetime.device_start_time
  condition: 
    condition: and
    conditions:
      - condition: time
        after: '00:00'
        before: '05:00'
  action:
  - service: climate.set_fan_mode
    data:
      fan_mode: "High"
    target:
      entity_id: climate.ac_12494102
  mode: single
  
# Lower fan from High to Auto four hours after start time
- id: '1663399614820'
  alias: Hallway AC fan to Auto
  description: 'Cheap energy time set fan to Auto'
  trigger:
  - platform: time
    at: input_datetime.device_end_time
  condition: 
    condition: and
    conditions:
      - condition: time
        after: '04:00'
        before: '09:00'
  action:
  - service: climate.set_fan_mode
    data:
      fan_mode: "Auto"
    target:
      entity_id: climate.ac_12494102
  mode: single

# Set temp to 20C four hours after start time
- id: '1663399614821'
  alias: Hallway AC temp to 20
  description: 'Cheap energy time set temp to 20'
  trigger:
  - platform: time
    at: input_datetime.device_end_time
  condition: 
    condition: and
    conditions:
      - condition: time
        after: '04:00'
        before: '09:00'
  action:
  - service: climate.set_temperature
    data:
      temperature: 20
    target:
      entity_id: climate.ac_12494102
  mode: single

Full examples

My full yaml files are also in my personal GitHub repo:

Summary

Home Assistant is useful tool to make some simple home automations. Obviously getting the heat pump itself have saved me plenty on electricity bills, but Home Assistant takes me one step further.

Although Home Assistant does provide nice GUI for creating schedules, I do prefer editing the text based yaml files. yaml itself is error prone format and for that good editor is a must. My choice of editor has been vim for last 20 years at least and I see no reason to switch away from it. Although I have tried to switch to Eclipse, Pycharm, VS Code – yet I always go back to vim. I even tried neovim but couldn’t find any difference compared to vim (I do not use lua).

When I found Github Pilot plugin for vim I found it to be a game changer. Not only for writing Python and Azure Pipelines with yaml, but especially for Home Assistant configuration yaml files. I also feel GitHub Copilot extremely addictive. The way it provides suggestions makes me chuckle once in a while and I really miss it almost everywhere – almost. It really would need to write my commit messages with vim fugitive. Feature suggestion for Tim.

Book review: Den svavelgula himlen

Den svavelgula himlenDen svavelgula himlen by Kjell Westö
My rating: 4 of 5 stars

Jag ville läsa den här boken på svenska, även om min svenska är ganska dålig. Jag tänkte att det skulle vara bra möjlighet att praktisera svenska. Jag har studerat svenska i skolan mer än trettio år sedan.

Den här berättelsen handlar livet om en finlandssvensk författare och hans liv från en liten pojke till 55 årig. Den berättar om hans första kärlek och deras liv – Tillsammans och inte tillsammans .

Den berättar också om författarens vänner och deras liv i 60-talets Helsingfors till idag. Det finns många detaljer om Helsingfors skärgård och även om olika platser av staden. Platsen som är kära för mig själv.

Att läsa den här boken tagit mig lite längre tid än jag trodde. Men det var min första finlandssvenska boken med 475 sidor.

Den här också min första bokrecensionen på svenska, sori siitä 🙂

View all my reviews

Book review: Arguing with Zombies: Economics, Politics, and the Fight for a Better Future

Arguing with Zombies: Economics, Politics, and the Fight for a Better FutureArguing with Zombies: Economics, Politics, and the Fight for a Better Future by Paul Krugman
My rating: 4 of 5 stars

I was a bit sceptic about this book. I have read Krugman’s NYTimes blog posts and although I like them, I didn’t expect they would carry a book. But this is well organised and has a lot of additional essays which makes it actually very interesting book to read. Although most of the book is about US economy and welfare including health care, it does go beyond US and covers the economic issues EU has as well.

I would have given actually 5 starts, but there’s some mistakes. This is understandable, since it is mostly collection of blog posts author published in NYTimes after all. But hey, when Krugman covers EU problems and euro as it’s currency, it is true, that Island has it’s own currency, but it’s not EU country and it’s economical issues were way different than anywhere in EU countries. Also, as a Finn, ahem, Ericsson is Swedish company, we here in Finland have Nokia.

Although like said, the focus is mostly focus US. I have lived a decade in Canada and rest of my life in Finland and still found book highly interesting and it’s findings fitting very well for European economic policies as well. Krugman seems to have very European way of thinking about welfare and health care. He calls it social democratic instead of socialism. This is one thing where he goes a bit off. Rightist parties in Europe mostly do support social security and health care; it’s not only social democrats.

View all my reviews

Book review: My Abandonment

My AbandonmentMy Abandonment by Peter Rock
My rating: 4 of 5 stars

I decided to check this book after watching the movie made from it: “Leave No Trace”. I was wondering if it was a good decision, since I already knew how it is going to end, right? Well, no, the book is independent from the movie. No harm reading the book after watching the movie.

It’s interesting book and creates weird psychological tense towards the end. Is anything there as the reader expects things to be?

Great book, great film, great story!

View all my reviews

Book review: Friends, Lovers, and the Big Terrible Thing

Friends, Lovers, and the Big Terrible ThingFriends, Lovers, and the Big Terrible Thing by Matthew Perry
My rating: 4 of 5 stars

This is book mostly about addiction, insecurity, fame and Matthew Perry. It’s less about lovers and even less about Friends, TV show. On many of reviews poor editing is mentioned. Not sure if some of the reason for critic has been fixed on up to date edition or is my English just so poor that I just don’t notice any of that.

I wonder how much easier Matty’s life would have been without all the money and fame. I assume it’s easier to relapse time after the time when you have endless amount of money to spend on rehabs, medication and health care. What ever it is it seems for an outsider, that too much of everything has pretty much ruined Matty’s life, health and relationships.

But he seems to be some what ok now. He is still relatively young and he hasn’t been able to toss out all of his money. I didn’t get feeling he has succeeded in his life though. He has been in number 1 TV Show and number 1 movie same time and earned insane amount of money. Some how any of that does’t seem to make him feel he has succeeded on anything. His life is constant battle against his demons and health issues.

I’m not sure if there’s anything to learn from this book. Except addiction is terrible disease and having too much money certainly does not make it any easier. When you have so much money that you cannot spent any significant amount of it to drugs, pills and alcohol and you can afford to spend $7 million to rehabs alone money obviously does not solve anything.

I didn’t borrow this book to read about Friends, but about Matty’s addiction. It was somewhat entertaining (the way real life Chandler, Matty himself, puts it) and somewhat shocking. I hope Matty gets his shit together and can live happier life now.

View all my reviews

Kirja-arvio: Pääoma 2000-luvulla

Pääoma 2000-luvullaPääoma 2000-luvulla by Thomas Piketty
My rating: 5 of 5 stars

Eittämättä yksi merkittävimpiä taloustieteen kirjoja 2000-luvulla. Oma kiinnostukseni heräsi erityisesti Björn Wahlroosin “Talouden kymmenen tuhoisinta ajatusta” -kirjassaan esittämän kritiikin vuoksi. Nalle on toki taitava talousmies ja omaa kunnioitettavan akateemisen historian, mutta tällä kertaa kritiikki meni pieleen. Hän selkeästikin oli lukenut kirjan hyvin pinnallisesti, ja on täysin jäävi arvostelemaan kirjaa, jonka pääajatus on estää koroillaeläjien yhteiskuntien synty.

Kirjan pääajatus on, että kun pääoman tuoton kasvu ylittää kansantulon kasvun, epäyhtälö r > g, pääomaa alkaa pitkän ajan kuluessa kertymään yksityisille henkilöille, instituutioille ja (öljy)maille niin paljon, että syntyy samanlainen koroillaeläjien yhteiskunta, kuin ennen maailmansotia vielä oli olemassa.

Nallehan on itse omaisuutensa luonut taitavien ja onnekkaiden sijoitusten kautta. Hän on silti loistava esimerkki siitä, mitä kirja tarkoittaa koroillaeläjillä. Jotkut luovat valtavan omaisuuden omalla työllään. Toiset saavat sen perinnöllä tai pääsevät osallisiksi naimalla koroillaeläjän.

Kun on tarpeeksi omaisuutta, niin sen vuotuiset tuotot ovat sitä verta suuret, että vaikka tuotoista kuluttaisi vuodessa yli 100 kertaa mediaanipalkan verran, niin pystyisi edelleen sijoittamaan uudelleen niin paljon, että pääoma jatkaa kasvamistaan yhä kiihtyvässä tahdissa.

Tämähän ei Pikettyn mukaan ole vielä ongelma. Ongelma on se, että pääoman kasvaessa tarpeeksi suureksi, se jatkaa kasvamistaan loputtomasti keskittyen hyvin harvoille, ja sen jälkeen syntyy ihmisryhmä, joille työllä ei ole enää mitään merkitystä, koska työstä ei koskaan voi saada vastaavia tuottoja kuin valtavasta omaisuudesta. Enemmistölle ihmisistä taas ei jää kansantalouden kasvusta juuri mitään käteen, kun se keskittyy superrikkaille. Epäyhtälö r > g. Tällainen tilanne oli vielä ennen maailmansotia. Edelleenkin epäyhtälö r > g pitää paikkansa, ja Pikettyn pahin skenaario on, että jossain vaiheessa luisumme koroillaeläjien maailmaan. Hän ei väitä, että se on vääjäämätöntä, mutta mahdollista.

Koska nykyaikana ihmisten elinikä on jo niin korkea, että perintö tulee varsin myöhään, kannattaa lapsilleen antaa valtavia lahjoituksia jo huomattavasti varhaisemmin. Tämä on tietysti sitä kannattavampaa, mitä alhaisempi lahja- ja perintövero on. Kukahan suomalainen tästä tulee mieleen?

Pikettyn kritiikki ei siis sinällään keskity valtavien omaisuuksien keräämiseen omalla työllään, vaan siihen, että syntyy sukuja ja instituutioita, joiden ja joissa ei enää koskaan tarvitse tehdä tuottavaa työtä tai toimintoja. Työn tai muun tuottavan toiminnan sijaan on kannattavampaa elää perimänsä pääoman koroilla tai yrittää edes päästä naimalla tällaiseen sukuun.

Piketty kritisoi myös superjohtajien valtavia tuloja, jotka mm. Yhdysvalloissa aina 1970-lukuun asti estettiin yhdellä maailman kovimmista progressioista, kunnes tätä alettiin pikkuhiljaa laskemaan ja päästiin alhaisimpaan tasoon Reaganin kaudella. Pikettyn huoli sinällään valtavien palkkojen suhteen ei ole kertymättä jääneet verot, koska suurituloisia, puhumme useista miljoonista, on sen verran vähän, vaan yhteiskunnallisen eriarvoisuuden kasvaminen.

Piketty nostaa esiin yhteiskuntien nykyisen velkaantumisasteen, joka lienee ongelma, jota taloustieteilijät eivät yleisesti ottaen kiistä. Velat ovat kuitenkin huomattavasti pienempiä, kuin yksityinen varallisuus. Euroopan maista suurin yksityinen varallisuus on Italialla ja Espanjalla. Nämä ovat myös yhdet Euroopan velkaantuneimmista maista. Euroopan velat muutoinkin alittavat reilusti yksityisen omaisuuden. Euroopan yksityinen omaisuus on maailman suurin ja esimerkiksi 20 kertaa suurempi, kuin Kiinan omistamat ulkomaiset varat. Kiina ei siis ihan heti ole ostamassa koko maailma.

Ratkaisuksi siihen, ettei koroillaeläjien yhteiskuntia synny uudelleen, eli paluuseen aikaan ennen maailmansotia, sekä velkaantumiseen, Piketty tarjoaa globaalia progressiivista pääomaveroa. Toki hän itsekin myöntää, että ajatus on sangen utopistinen, minkä Nallekin omassa kritiikissään huomioi. Utopistinen ajatuskin voi olla silti toimiva.

Piketty huomauttaa, että olihan valuutta ilman kotimaatakin utopistinen ajatus, mutta silti euro syntyi. Piketty tosin ei näe eurossa oikein mitään hyvää, toisin kuin ehdotuksessaan globaalisista progressiivisesta pääomaverosta.

Piketty ei väitä aineistonsa olevan täydellistä, mutta se kattaa valtavan pitkän aikajanan aina 1700-luvulta nykypäivään. Siksi hänen analyysinsä lienee melko paikkansa pitävää, vaikka epävarmuustekijöitä tietysti on.

Onko hänen tarjoamansa ratkaisu utopiaa, vai toteutettavissa; sitä en tiedä. Yhdysvaltojen toteuttama Foreign Account Tax Compliance Act (FATCA) tuntuu joka tapauksessa toimivan melko tehokkaasti, ja harva yhdysvaltalainen pääsee veroja pakoon muuten, kuin luopumalla kansalaisuudesta. Siinä mielessä vastaavan painostuksen alla globaali progressiivinen pääomaverokin lienisi toteustuskelpoinen, jos valtiot pääsisivät siitä yhteisymmärrykseen. Jälkimmäinen tosin lienee utopiaa. Edes Euroopan maiden välillä ei ole kyetty toteuttamaan FATCA:n kaltaista järjestelmää.

Yhtäkaikki, kirja on erittäin mielenkiintoinen katsaus tulo- ja varallisuuseroihin ja paikoin sangen leppoisaa luettavaa viitteineen kaunokirjallisuuteen.

View all my reviews

Running benchmark: Comparing Ubuntu 20.04 and RHEL 8.5 performance

The claim

I found a claim on Quora that Ubuntu is slow compared to RHEL. I never thought about it. Is it really? It seemed like a sentimental statement with nothing to prove it. I questioned the claim and found out, that many people tried to support the claim still without providing any kind proof.

Instead of continuing to asks any evidence, I decided to dig the evidence my self.

Is there any difference in the performance between the two? I don’t really know, but if you think about default server install with nothing extra, I doubt there could be any significant performance difference.

Ubuntu 20.04 has currently kernel 5.4 where as RHEL 8.5 has 5.13. Libraries and software are pretty much the same. File system by default is XFS on both. I always enable LVM although that could affect performance – certainly not by improving it, but there are other advantages. I don’t think LVM reduces performance much either and as said I always set it up anyway.

In this case I have two virtual machines both having 4GiB RAM and two 3.3GHz CPU cores running on qemu/kvm. The host OS is, yes you guessed right, Ubuntu 20.04 , because on desktop it has certain software I need. And, it works well as virtual host too. That does not affect the results anyhow, since the guest OS has no idea of host OS.

I haven’t tuned either of the guests OS at all except for one thing. I set tuned profile to virtual-guest for both, which makes sense. It is the recommended profile when I run tuned-adm recommend on both of the guests machines.

Put the HammerDB down

Last I ran HammerDB I had to settle with text based version, but this time it had a nice working GUI. But even before quick HammerDB installation, I downloaded Db2 11.5.7 Community Edition. Installed it on both Ubuntu and RHEL. I created SAMPLE database with db2sampl and took timing for that: no difference really. I knew it. Ok that doesn’t prove anything.

But, the real test does. HammerDB.

Ubuntu 20.04

Let’s start with Ubuntu. I read a tutorial on how to run time based benchmark with HammerDB. I want to do this fast. One virtual user only. Looks good.

Ubuntu 20.04

The process goes:

  • Choose Engine and configure it (Db2)
  • Build schema
  • Configure and load driver
  • Configure virtual user
  • Create virtual user(s)
  • Run virtual users
  • Monitor and wait
Here are the results for Ubuntu.

The results for Ubuntu 20.04

System achieved 5178 NPM from 22865 Db2 TPM

RHEL 8.5

Then same thing for RHEL. The machine crashes twice. Reminds me of kernel parameters. We have only 4GiB, so might be I need to tune them. But no, it run all good the third time. In my previous job though, servers with low memory running Db2 on RHEL crashed always without tuning the kernel parameters. There’s a simple formula based on RAM to calculate correct values for Db2 here. That said, I did not change anything from defaults for Ubuntu nor RHEL. It wouldn’t be fair comparison, if I started to tune the kernel parameters for one and not to the other.

Running on RHEL 8.5
There’s finally some I/O wait
The winner is RHEL 8.5 by two New Orders Per Minute (NOPM)

The results for RHEL 8.5

System achieved 5180 NOPM from 22815 Db2 Db2 TPM

First conclusion

There is really no difference between Ubuntu and RHEL what comes to achieved performance results. The two new orders per minute makes 0.04% difference which I’m pretty sure no one can notice just by “using the server a bit”.

Comparison between database engines

Since I already started playing with HammerDB, why not try some more tests. I have earlier installed Db2 on the host machine itself as well as MS SQL Server. I also have virtual machine running Oracle Linux 8 on it with the same 4GiB RAM and two CPU core setup. MySQL and PostgreSQL I have running on the host itself.

The hosts OS, as said, is running Ubuntu Desktop 20.04. It has 4 x 3.3GHz cores and 32GiB RAM and fast NVMe 500GiB M.2 PCIe SSD. This is small form factor machine suitable for industrial use as a headless server running for example Linux. Or you can use it as desktop computer as well. My idea for it was to use it as a platform for several virtual guests, but I wanted to see how it works as a Linux desktop computer as well.

Let’s do few quick tests on the host itself for various database engines. More of a test of HammerDB itself than real comparison between the engines.

Db2

TEST RESULT Ubuntu 20.04 Desktop: System Achieved 6651 NOPM from 2928 TPM.

I’m a bit surprised it didn’t achieve more. Need to test more. It takes time for bufferpools to warm up with automatic memory tuning and with 32GiB memory I’m pretty sure we could get much better results.

MS SQL Server

But let’s check with MS SQL Server I have running on the same machine. Certainly Db2 beat MS SQL Server, right?

MS SQL Server gets higher TPM numbers compared to virtual machines
Obviously the benchmark is somewhat different between the engines,

The winner is… oh no, MS SQL Server

8394 New Orders Per Minute with 19243 SQL Server TPM

Oracle

I have one Oracle 21c Server running on VM running Oracle Linux 8. Oh but Oracle – I’m so lost with it. HammerDB asks too much questions and it seems I need to create another pluggable database. I will do that – later.

PostgeSQL and MySQL

Out of curiosity I ran the test for PostgreSQL and MYSQL:

PostgreSQL: TEST RESULT: 11607 NOPM from 26880 PostgeSQL TPM

MySQL: TEST RESULT: 1739 NOPM from 5252 MySQL TPM

I have no idea why the difference between above two is that significant. Might be for various reasons. I wouldn’t pay much attention on the difference since running the test on host OS and not on virtual machines with proper setup doesn’t make much sense – unlike the more serious comparison I did for Ubuntu Server and RHEL.

Final Conclusion

Without official test for Oracle we cannot make any other conclusion than Oracle is the slowest from these three DB Engines: Db2, MS SQL Server and Oracle. I’m kidding of course; I’m no Oracle expert and just too slow myself to set a proper test for Oracle. That might change once I have enough time to dig deeper on Oracle. For MySQL and PostgreSQL the test was also too quick; more of a test do they work similarly in comparison to Db2 and MS SQL what comes to HammerDB.

What comes to the original claim about Ubuntu being overall slow and which surprisingly many is willing to believe, I think I have busted the claim.

We can speculate how about real server environments and please do, but before you actually have any benchmarks to show otherwise, I take it proven that Ubuntu and RHEL are equally slow or fast.

Also, what comes to MS SQL Server performance compared to Db2, obviously this was not the last word. Let’s try with 10 virtual users beating Db2 for a bit longer.

Db2 with 10 Virtual Users

Final results for Db2 running on this tiny Asus Mini PC PN41 were:

TEST RESULT: System achieved 15650 NOPM from 68735 Db2 TPM.

So we have a winner: Db2 11.5.7?

In a sense Db2 won that it did get the highest number of new orders per minute yes. But in comparing with other database engines I didn’t really organise any meaningful tests between them this time.

Want to test yourself?

Prove me wrong. Run your own tests and provide me your data and conclusions. I have serious doubt Ubuntu Server and RHEL differs much what comes to performance. There certainly is plenty of other things which makes the difference when choosing the distribution. Things like support, cost, platform you are running on and so on. Red Hat certainly has it’s advantages on enterprise level support whereas Ubuntu started strong on desktop, but it is easy to deploy for example on Azure and fully supported.

Harley Benton Attenuator with ’65 Fender Bassman Guitar Amp

I acquired one of the most affordable attenuators/loadboxes available. Recently I have only had ’78 Silverface Fender Vibro Champ at home. Even that is actually pretty loud considering there’s a neighbour behind the wall. Basically I never crank it much beyond volume at 2.

I was thinkikng bringing my VOX AC30 home and getting an attenuator to use with it. But then I noticed there was ’65 BF Fender Bassman 50 for sale at decent price and couldn’t resist getting it. It was just a head and I only had Ulraltone tweed cabinet with Celestion Gold 12″ 15ohm speaker. Bassman output is 4 ohm, so not ideal at all. I found immediately an add for Mexican made Fender VM 212 cabinet for sale – and it was extremely affordable.

These two have 50 year age difference, but they look and sound together extremely good.

’65 Fender Bassman on top of 2015 Bandmaster VM 212

Now, the only issue is the level of volume at home, where I want to use them mostly. I’ve had attenuator, load box, Impulse Response (IR) “something” in my mind for very long time before, but I really didn’t know which one should I get, since I didn’t know what they actually are. Besides I was also thinking getting a “modeller” or “simulator” with maybe possibly having possibility to utilise them at bedroom volume levels.

But then, I like old amps and real hardware. Although I was already totally convinced, that Line 6 Stomp Box would be perfect fit for me, I just couldn’t resist getting 60’s Fender Bassman. Real amp from 60’s. John Lennon used blonde early 60’s Bassman and Paul McCartney ’68 Silverface Bassman after Beatle’s VOX era. ’65 Bassman with AA165 circuit would be such a perfect fit for me being some wehere in the middle between two of those.

But even with just 50 watts it is loud. I mainly play at home either through amp or with GarageBand direcly connecting guitar through audio interface or with mic in front of my small ’78 Fender Vibro Champ. Later 5 watt amp cranked up to volume at 3 at maximum.

I decided to go for most affordable attenuator there is. Harley Benton PA-100 it is. I also considered a bit more expensive Bugera, but that didn’t seem to have output for mic, which I considered to be required for connecting the amp directly to my MacBook through audio interface.

While waiting my order to be mailed I decided to play with Two Note Wall of Sound plugin. It has power amp simulation as well, so it doesn’t necessarily need an amp at all, but like said, I’m a hardware guy. I acquired a cabinet bundle. Fuellerton with plenty of Fender cabinet simulations. Played with it by connecting guitar from my pedal board to audio interface and from there to GarageBand. On Wall of Sound plugin I could easily choose different cabinets and power amp tube modelling. Impressive results!

Next day the attenuator came. Extremely easy to setup. Tried first on cabinet. Without pedals and with pedals. Then hooked it into audio interface. Especially with audio interface and Wall of Sound cabinet simulation and headphones the sound was amazing. Plus I could change the sound directly from Bassman, which I’ve jump connected to use both bass and guitar channels. When I crank volume up on bass channel it breaks up beautifully.

The ability to switch between different cabinet simulations, microphones, rooms for various reverbs and other WoS Capabilities is just amazing. I spent a bit over 100€ for cabinet bundles (Fender and VOX inspired), which is significantly more than for HB attenuator, but that was money well spent.

Here’s a small vid a made with Wall of Sound and both guitar and bass recroded through Bassman head connected to audio interface through mic mod output on HB PA-100.

Configure SQuirreL SQL Client for DB2 for Linux, UNIX and Windows

Configure SQuirreL SQL Client for DB2 for Linux UNIX, and Windows

At least for me the default settings for SQuirreL DB2 driver didn’t work. That’s why I documented here how to modify the default “IBM DB2 App Driver” so it works.

Configure the DB2 driver

  1. First I copied db2jcc4.jar from the DB2 instance. In my case the driver was under

    /opt/ibm/db2/V11.1/java/ but you find it as DB2 instance user under $HOME/sqllib/java as well, since this is symlink to installation directory.

  2. I stored DB2 db2jcc4.jar  under $HOME/Java, but you can choose a different location.
  3. Click Drivers tab and then double click “IBM DB2 App Driver” to modify it.
  4. Highlight “Extra Class Path” tab and click “Add” to add the db2jcc4.jar you have earlier stored to you computer.
  5. Set “Example URL” to jdbc:db2://<HOST>:<PORT>/<DATABASE_NAME>
  6. Set “Class Name” to com.ibm.db2.jcc.DB2Driver

Setup connection to DB2 database

  1. To set up your first DB2 database connection on SQuirreL SQL Client, select the “Aliases” tab and click  icon, to add new database connection.
  2. From “Driver pull down list select “IBM DB2 App Driver”.
  3. Choose “Name” for you connection ie. the database name you are connecting to.
  4. URL is the form you set up when creating the driver: “jdbc:db2://<HOST>:<PORT>/<DATABASE_NAME>”. Set “host” and “dbname” accordingly.

No you can test your connection buy hitting test and then connect:

Ready to go

Once connection is done you can connect to DB2 database:

And start querying:

Questions?

If you have any questions or feedback, please connect with me.

Create Linux VM running CentOS 7.3 minimal with pyodbc and Netezza Client

This document describes how to create Linux Virtual Machine (VM) to be run on macOS or Windows Host. When followed the steps in this document, you will have CentOS 7.3 VM capable of running Netezza Linux Client, unixODBC, Python 3.6 with pyodbc and pandas among others. This setup is useful for developing Python code which needs Netezza connection.

Especially macOS users will benefit from this kind of setup, since there is no Netezza client for macOS.

This document concentrates on deploying the VM on VirtualBox, but the CentOS setup portion is identical also when using other hypervisors ie. VMWare Player, VMWare Workstation or VMWare Fusion.

Note: The LinuxVM created in this documented has all capabilities on Python 3.6. You execute python code calling python3.6 instead of just python, which points to python 2.75.

Install CentOS 7.3 on VirtualBox

  1. Download newest version of VirtualBox and install it: https://www.virtualbox.org/wiki/Downloads
  2. Once installed go to VirtualBox menu and choose “Preferences” and click “Network”.
  3. Click “Host-only Networks” and choose icon add: 
  4. Now you have new “Host-only Network” which is needed for incoming connections. You can check the details by double clicking vboxnet0:
  5. Next create VM with two network adapters:
    1. Choose “New” and select “Name”, “Type” and “Version”:
    2. Click continue. You can keep the memory on 1024MB which is the default.
    3. Click continue and choose “Create virtual hard disk now” and click “Create”.
    4.  “Hard disk type” can be VDI, if you do not plan to run VM on other hypervisors, but if you plan to run it on VMWare hypervisor, choose VMDK. Click “Continue”.
    5. For flexibility choose “Dynamically allocated” and for best performance choose “Fixed size”.
    6. For most purposes 8.0GB is enough, but your needs may vary. Choose “Create”.
    7. Now VM is created, but we need to change some of the network settings:
      1. While new VM is highlighted, choose “Settings” and select “Network” tab.
      2. “Adapter 1” default settings are ok for most cases, but we need to add “Adapter 2” so click “Adapter 2”. We need 2nd Network card for incoming connections, so we select “Enable Network Adapter” and set “Attached to: Host-only Adapter”:
      3. Click “OK”.
  6. We need to have CentOS minimal installation image which we can download from CentOS site: https://www.centos.org/download/
  7. Choose your download site and store the image to desired location. We need it only during intallation.
  8. Once downloaded, go back to VM Settings on virtual box:
    1. Select “Storage” tab and “Controller: IDE” and click the CDROM icon and then another CDROM icon on right side from “Optical Drive” selection and “Choose Virtual Optical Disk File”.
    2. Select the CentOS minimal installation disk image you downloaded on previous step:

      1. Click “OK”
  9. Now we can start the CentOS minimal installation. Choose “Install….” when VM has booted.
  10. Next we get graphical installation screen. We can keep language settings as default and click “Continue”:
  11. Note when you click the VM, it will grab the mouse. To release the mouse, click left CMD (on MacOS).
  12. Click “Network & Host name”. You can specify your hostname as preferred.
  13. Both Network cards are off by default. Set them both to “ON”.
  14. For both Network cards, click “Configure” and on “General” tab choose “Automatically connect to this network when it is available”:
  15. Click “Done” to get out from Network settings, and click “Installation Destination” to confirm storage device selected by default is correct (no need to change anything). Then click “Done” to get back to main screen and you can start the installation by selecting “Begin Installation”.
  16. During installation set root password and select to create user. In my examples for setting up Netezza client, I have chosen to create “Netezza User” with username “nz”. I will also make this user an administrator:
  17. Once installation is done you can click “Finnish configuration” and then “Reboot”.
  18. VM boots now first time. You can either ssh to the system (from Terminal on MacOS, or using Putty on Windows).
  19. If this is only VM using Host-only network on VirtualBox, it’s likely the IP is 192.168.56.101. You can check the IP for device enp0S8 with command: ip addr show when logged in through VirtualBox console.
  20. After installation first thing to do is to update all packages with yum update command. Either as root give command “yum -y update” or as administrative user as “sudo yum -y update”.

Configure file sharing between Host and Guest OS

You might want to be able to share, for instance your PycharmProjects folder to run Python code you developed directly on LinuxVM. That is a bit of the whole point for the LinuxVM in this case.

To achieve that, you need to enable file sharing. There is few additional steps needed I’l go through below:

  1. You need few additional packages first. Run following commands as root:
    1. yum -y update
    2. yum -y install gcc kernel-devel make bzip2
    3. reboot
  2. Once LinuxVM has rebooted and you have LinuxVM Window active select from menu “Devices” –> “Insert Guest Additions CD Image…” . Then log in to LinuxVM as root via VirtualBox console or ssh again and run following commands:
    1. mkdir /cdrom
    2. mount /dev/cdrom /cdrom
    3. /cdrom/VBoxLinuxAdditions.run
  3. Now select the folder you want to share from your Host OS to LinuxVM. Go to VM settings and choose “Shared Folders” tab and click icon and then choose the folder you want to share:
  4. Note: Make sure you set the mount permanent. No need for automount option, since we do it a bit differently below.
  5. Above we are sharing PycharmProjects folder. We want to have PycharmProjects folder mounted on LinuxVM on nz users home directory. As nz user we first create directory PycharmProjects with command: mkdir $HOME/PycharmProjects
  6. Then as root, we add following entry to /etc/fstab:PycharmProjects /home/nz/PycharmProjects vboxsf uid=nz,gid=nz           0 0
  7. After reboot you should now have your PycharProjects folder mounted with read and write access under nz users home directory.

    Note: The purpose for above share is, that when you develop your Python code with Pycharm on MacOS and if your code needs connection to Netezza, you can not run it on MacOS, since there is no Netezza drivers. Instead, when following this guide, you will be able to run you Pycharm edited code seamlessly on the LinuxVM through ssh connection, and once confirmed to work, you can commit your changes.

Install Python 3.6 with pyodbc, pandas and sqlalchemy

Log in to LinuxVM as root and run following commands:

yum -y update
yum -y install git
yum -y install yum-utils
yum -y groupinstall development
yum -y install https://centos7.iuscommunity.org/ius-release.rpm
yum -y install python36u
yum -y install python36u-pip
yum -y install python36u-devel
pip3.6 install pandas
yum -y install unixODBC-devel
pip3.6 install pyodbc
yum -y install gcc-c++
yum -y install python-devel
yum -y install telnet
yum -y install compat-libstdc++-33.i686
yum -y install zlib-1.2.7-17.el7.i686
yum -y install ncurses-libs-5.9-13.20130511.el7.i686
yum -y install libcom_err-1.42.9-9.el7.i686
yum -y install wget
yum -y install net-tools
pip3.6 install sqlalchemy
pip3.6 install psycopg2

Testing pyodbc

Edit the connection string accordingly:

[nz@nzlinux ~]$ python3.6
Python 3.6.2 (default, Jul 18 2017, 22:59:34) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-11)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.connect(server="nz", database="TEST", dsn="NZSQL", user="admin", PWD="password", autocommit=False)
<pyodbc.Connection object at 0x7f66ef8566b0>

Install Netezza Linux client

First you need to download NPS Linux client from IBM Fix Central

Then, as root run following commands (accept all defaults):

mkdir NPS
cd NPS
tar xvfz ../nz-linuxclient-v7.2.1.4-P2.tar.gz
cd linux
./unpack
cd ../linux64
./unpack

Now, log in as nz user and add following lines to $HOME/.bashrc (modify credentials and server details accordingly: NZ_USER, NZ_PASSWORD and NZ_HOST):

NZ_HOST=netezza.domain.com
NZ_DATABASE=SYSTEM
NZ_USER=admin
NZ_PASSWORD=password
export NZ_HOST NZ_DATABASE NZ_USER NZ_PASSWORD
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/nz/lib64
export PATH=$PATH:/usr/local/nz/bin
export ODBCINI=$HOME/.odbc.ini
export NZ_ODBC_INI_PATH=$HOME

To make above changes effective without logging out and in, you can instead run command: . ./.bashrc

Now you should be able to use nzsql:

[nz@nzlinux ~]$ nzsql
Welcome to nzsql, the IBM Netezza SQL interactive terminal.
Type:  \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
SYSTEM.ADMIN(ADMIN)=>

Setup ODBC

Copy following two files, odbc.ini and odbcinst.ini to /etc as root:

odbc.ini
odbcinst.ini

As nz user create following symlinks:

ln -s /etc/odbcinst.ini .
ln -s /etc/odbc.ini .
ln -s /etc/odbc.ini .odbc.ini
ln -s /etc/odbcinst.ini .odbcinst.ini

 Questions

If you have any questions, please connect with me.

Update

The .odbc.ini and .odbcinst.ini issues seems to be fixed with newer Python versions, so creating symlinks to users home directory nor creating system files under /etc are not anymore required. Just using .odbc.ini and .odbcinst.ini in user’s home directory works now as it is supposed to work.