You are not logged in.

#1 2022-08-03 11:50:45

fow0ryl
Member
From: Braunschweig
Registered: 2021-04-30
Posts: 11

PostgreSQL prevented HDD standby mode

Hello,
I have installed PostgreSQL as described here on my Desktop System running 7/24. Just like a server.
Everything except HDD standby mode works as expected.

The Linux system is installed an a small SSD (sda) and the postgreSQL data directory points to a dedicated ZFS dataset, mounted at "/data/postgres".
The ZFS pool consists of 3 HDD's (sdb/sdc/sdd)

Here is my postgresql.conf.

log_timezone = 'Europe/Berlin'
stats_temp_directory = '/run/postgresql'
datestyle = 'iso, dmy'
timezone = 'Europe/Berlin'
default_text_search_config = 'pg_catalog.german'

If I stop postgres with "systemctl stop postgresql" the HDD's are going to standby mode after a few minutes.
But when starting postgres the HDD's wake up immediately and remain active. Standy mode is never reached.
I have checked this wirth "hdparm -C /dev/sdb /dev/sdc /dev/sdd".
Forcing the HDD's to go to standby with "hdparn -y .." works, but after a few seconds (maybe 5 - 10) they are active again.

So I'm sure that the problem comes from postgres itself ...

As you can see I have changed stats_temp_directory as described in the Wiki Prevent_disk_writes_when_idle
But this seems not to be enough to enable spindown sad

What else to configure to let HDD's sleep most time of the day?

Henning

Offline

#2 2022-08-03 14:21:43

schard
Member
From: Hannover
Registered: 2016-05-06
Posts: 1,406
Website

Re: PostgreSQL prevented HDD standby mode

# mount
# df -h
# free -m

Солідарність з Україною

Offline

#3 2022-08-03 17:08:24

fow0ryl
Member
From: Braunschweig
Registered: 2021-04-30
Posts: 11

Re: PostgreSQL prevented HDD standby mode

# mount

proc on /proc type proc (rw,nosuid,nodev,noexec,relatime)
sys on /sys type sysfs (rw,nosuid,nodev,noexec,relatime)
dev on /dev type devtmpfs (rw,nosuid,relatime,size=8003144k,nr_inodes=2000786,mode=755,inode64)
run on /run type tmpfs (rw,nosuid,nodev,relatime,mode=755,inode64)
efivarfs on /sys/firmware/efi/efivars type efivarfs (rw,nosuid,nodev,noexec,relatime)
/dev/sda2 on / type btrfs (rw,noatime,ssd,space_cache,subvolid=5,subvol=/)
securityfs on /sys/kernel/security type securityfs (rw,nosuid,nodev,noexec,relatime)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev,inode64)
devpts on /dev/pts type devpts (rw,nosuid,noexec,relatime,gid=5,mode=620,ptmxmode=000)
cgroup2 on /sys/fs/cgroup type cgroup2 (rw,nosuid,nodev,noexec,relatime,nsdelegate,memory_recursiveprot)
pstore on /sys/fs/pstore type pstore (rw,nosuid,nodev,noexec,relatime)
bpf on /sys/fs/bpf type bpf (rw,nosuid,nodev,noexec,relatime,mode=700)
systemd-1 on /proc/sys/fs/binfmt_misc type autofs (rw,relatime,fd=30,pgrp=1,timeout=0,minproto=5,maxproto=5,direct,pipe_ino=11734)
mqueue on /dev/mqueue type mqueue (rw,nosuid,nodev,noexec,relatime)
hugetlbfs on /dev/hugepages type hugetlbfs (rw,relatime,pagesize=2M)
debugfs on /sys/kernel/debug type debugfs (rw,nosuid,nodev,noexec,relatime)
tracefs on /sys/kernel/tracing type tracefs (rw,nosuid,nodev,noexec,relatime)
configfs on /sys/kernel/config type configfs (rw,nosuid,nodev,noexec,relatime)
fusectl on /sys/fs/fuse/connections type fusectl (rw,nosuid,nodev,noexec,relatime)
none on /run/credentials/systemd-sysusers.service type ramfs (ro,nosuid,nodev,noexec,relatime,mode=700)
tmpfs on /tmp type tmpfs (rw,noatime,inode64)
/dev/sda3 on /data/local type btrfs (rw,noatime,ssd,space_cache,subvolid=5,subvol=/)
/dev/sda1 on /boot/efi type vfat (rw,relatime,fmask=0077,dmask=0077,codepage=437,iocharset=ascii,shortname=mixed,utf8,errors=remount-ro)
zPoolW/local/postgres on /data/postgres type zfs (rw,noatime,xattr,posixacl)
zPoolW/local/tmp on /data/tmp type zfs (rw,relatime,xattr,posixacl)
zPoolW/local/data on /data/zdata type zfs (rw,relatime,xattr,posixacl)
zPoolW/local/nextcloud on /data/nextcloud type zfs (rw,relatime,xattr,posixacl)
tmpfs on /run/user/1000 type tmpfs (rw,nosuid,nodev,relatime,size=1602708k,nr_inodes=400677,mode=700,uid=1000,gid=1000,inode64)
gvfsd-fuse on /run/user/1000/gvfs type fuse.gvfsd-fuse (rw,nosuid,nodev,relatime,user_id=1000,group_id=1000)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,nosuid,nodev,noexec,relatime)

# df -h

Dateisystem                                                  Größe Benutzt Verf. Verw% Eingehängt auf
dev                                                           7,7G       0  7,7G    0% /dev
run                                                           7,7G    1,3M  7,7G    1% /run
/dev/sda2                                                      63G     57G  5,8G   91% /
tmpfs                                                         7,7G    144M  7,6G    2% /dev/shm
tmpfs                                                         7,7G    1,1G  6,7G   14% /tmp
/dev/sda3                                                     800G     87G  713G   11% /data/local
/dev/sda1                                                     300M    420K  299M    1% /boot/efi
zPoolW/local/postgres                                         8,5T    148M  8,5T    1% /data/postgres
zPoolW/local/tmp                                              8,6T     73G  8,5T    1% /data/tmp
zPoolW/local/data                                             8,9T    357G  8,5T    4% /data/zdata
zPoolW/local/nextcloud                                        8,5T    4,4G  8,5T    1% /data/nextcloud
tmpfs                                                         1,6G     84K  1,6G    1% /run/user/1000

# free -m

gesamt       benutzt     frei      gemns.  Puffer/Cache verfügbar
Speicher:      15651        6687        1274        2147        7689        6482
Swap:              0           0           0

Offline

#4 2022-08-04 06:40:41

schard
Member
From: Hannover
Registered: 2016-05-06
Posts: 1,406
Website

Re: PostgreSQL prevented HDD standby mode

Is this with postgres running or stopped?
The tmpfs under /run is barely used.
Also your root partition is running full, though in your case this could just be an unbalanced btrfs.

Last edited by schard (2022-08-04 06:41:40)


Солідарність з Україною

Offline

#5 2022-08-04 13:27:29

fow0ryl
Member
From: Braunschweig
Registered: 2021-04-30
Posts: 11

Re: PostgreSQL prevented HDD standby mode

Yes, postgres is running.

Most of content in / is static and not realy needed. i.e. 4 GB pacman cache.
I will clean it if necessary...

The system is barely used during working days. Only 2 users, syncing contacts and documents via nextcloud. Maybe 30 minutes a day.
Thats why I would like to  spindown hdd's.

Power usage with hdd's in suspend mode is about 30W. With hdd's running its about 50W, up to 80W spikes.
So hdd's running is  20W *  24h = 0,48 kW/h energy waste per day. And of course it's heating up my craft room. Ok @ winter days, dumb @ summertime  ....

Offline

Board footer

Powered by FluxBB