Benchmark testing on PostgreSQL 15 running on RGB lighted ASUS prime B650-plus

While sudo hdparm -tTv /dev/nvme0n1p1 may be the method to test beffered disk read performance, a read good test on your NVMe m.2 disk is to run pgbench -c 32 -j 8 -t 100000 example -U postgres -h 192.168.2.110 . This article shows the preparation work needed before you can start the benchmark testing.

Install and setup PostgreSQL

while sudo apt install postgresql may get you PostgreSQL 15 installed on Ubuntu 23.04, it does not allow remote connections by default. I followed PostgreSQL 14 : Remote Connection to configure the .conf files to allow remote connections. Change from 14 to your PostgreSQL version for file paths.

sudo nano /etc/postgresql/15/main/postgresql.conf

Add line: listen_addresses = ‘*’

sudo nano /etc/postgresql/15/main/pg_hba.conf

Go to the end of the file. Replace 127.0.0.1/32 with 0.0.0.0/0. This will allow any IP including Internet IPs to connect to the database. Then restart the daemon service.

sudo service postgresql restart

Change PostgreSQL password with the following commands:

$ sudo su -
# su - postgres
$ psql
postgres=# \password

Test connecting to PostgreSQL

From a remote host in the LAN, connect to the PostgreSQL instance. I’d trust Google Bard to generate the command with the question:

what’s the command to install just the PostgreSQL client on Ubuntu Linux?

Install the psql client package if psql command does not exist. Then, execute the command with the database host’s IP:

$ psql -U postgres -h 192.168.2.110
postgres=# CREATE DATABASE example;
CREATE DATABASE
postgres=# \list

The last command shows you’ve created the database example . You’d need to install PostgreSQL server on the client where you run the pgbench benchmark tool because that’s a dependency. It’s not desirable but that’s inevitable using pre-packaged pgbench tool. I followed Tuning PostgreSQL with pgbench for the steps:

sudo apt-get install postgresql postgresql-contrib
sudo systemctl disable postgresql && sudo systemctl stop postgresql

The last command disables and stops PostgreSQL as you don’t want the client to be another database instance. If you are using Linux KVM, it’s a good time to take a snapshot. Start executing the commands to initialize the database. specifying -s 50 takes about 0.7 GB.

$ pgbench -i -s 50 example -U postgres -h 192.168.2.110
$ pgbench -c 32 -j 8 -t 100000 example  -U postgres -h 192.168.2.110
Password: 
pgbench (14.8 (Ubuntu 14.8-0ubuntu0.22.10.1), server 15.3 (Ubuntu 15.3-0ubuntu0.23.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 32
number of threads: 8
number of transactions per client: 100000
number of transactions actually processed: 3200000/3200000
latency average = 2.613 ms
initial connection time = 119.114 ms
tps = 12246.444213 (without initial connection time)

The last command starts 32 clients and 8 threads. 100000 means each client executes 100,000 transactions. I’m using WD_BLACK 250GB SN770 NVMe m.2 SSD on a KVM instance of 8 AMD Ryzen 5 7600X vCPU cores with 2 GB RAM. 12,246 tps is quite impressive for a single database instance in my opinion!

Change the Motherboard’s LED light color based on CPU load

Many motherboard manufacturers has software to change the LED light colors. I called Gigabyte and ASUS product technical support. Their software to control the LED lighting only has Windows binaries and they don’t release the source code. I found OpenRGB as the solution to execute commands to change the colors. You’d need to install it, sysstat, and save to 4 profiles: [green, blue, yellow, red]. I am providing the BASH script that can run to set the color based on the latest CPU load within 1 second. So far, openRGB has to run as root. Name the script cpu-rgb.sh below:

### Install OpenRGB and configure profiles of red, green, blue, and red
### Test with `openrgb -p yellow.orp` before executing the script

set -e # exit the script when execution hits any error
# set -x # print the executing lines

if ! command -v mpstat
then
    echo "<mpstat> could not be found"
    exit 1
fi

CPU_IDLE=$(mpstat 1 1 -o JSON | jq -r '.sysstat.hosts[0].statistics[0]."cpu-load"[0].idle')
echo "CPU idle % is $CPU_IDLE"

# returns 1 if CPU is more than 80% idle
CPU_GREEN=$(echo "$CPU_IDLE > 80" | bc -l)

if [ "$CPU_GREEN" = 1 ]; then
  MOBO_COLOR="green"
else
  CPU_BLUE=$(echo "$CPU_IDLE > 50" | bc -l)
  if [ "$CPU_BLUE" = 1 ]; then
    MOBO_COLOR="blue"
  else
    CPU_YELLOW=$(echo "$CPU_IDLE > 20" | bc -l)
    if [ "$CPU_YELLOW" = 1 ]; then
      MOBO_COLOR="yellow"
    else
      MOBO_COLOR="red"
    fi
  fi
fi

case "$MOBO_COLOR" in
  green)
    sudo openrgb -p green.orp
    ;;

  blue)
    sudo openrgb -p blue.orp
    ;;

  yellow)
    sudo openrgb -p yellow.orp
    ;;

  red)
    sudo openrgb -p red.orp
    ;;
esac

Execute while true; do cpu-rgb.sh; done to run the script.