2

I am using my router with OpenWRT as a small server. To experiment a little with more advanced database administration in my programming projects I set up psql on it. The.server uses my HDD, which is attached via USB to the router. This is no high-availability.system,. primarily as a DB connection point inside my private network.

Everything is going alright so far. I just experienced that my hd-idle setup is not working anymore. I highly suspect PostgreSQL to be the reason for that. Since the drive is not really that quiet and it's standing in my living room, I was Keen to spin the HDD down if I didn't use it for an hour. I now register quite some regular I/O on the device and I guess it's logging or similar processes. Is it possible to reduce the frequency of these helper processes down to maybe once a day?

  • SSDs are not that expensive these days; any reason you don't want to buy one? – mustaccio Jan 6 at 23:29
  • 1
    Simply because I had the HDD already and wanted to use existing hardware! – Daniel Bischof Jan 7 at 5:21
  • Postgresql has also a totally braindamaged feature that it sync() disk on all write operations. You can turn it off somewhere in postgresql.conf . Also Ext4 fs has a braindamaged option that it syncs data in every 5 seconds, it can be turned off with mount flags. Well, and the linux VM has also a braindamaed feature that it uses a very small write cache, it can be turned off with sysctls. Doing all 3, postgresql will be much faster than mysql and probably no one can say why it is not the default....... – peterh Jan 7 at 6:51
  • Hm. That might be an experiment. I can imagine that would tax the RAM of system a little bit more though... – Daniel Bischof Jan 7 at 12:08
  • What version of PostgreSQL, and with what settings? Is the database completely idle? – jjanes Jan 8 at 16:26
0

OpenWRT doesn't provide anything that I know of anyway to spindown hard drives. From the documentation,

Unfortunately there is no solution that works always for everybody. The first option to try is provided by the package hd-idle, which will spin down any disk accessible via the SCSI layer. Install it and test if it can spindown the hard disk (let us assume that your disk is /dev/sda):

The other solution is hdparm -y. So what you're doing is using something to tell your drives when to spin down. The idea is you now either

  1. Script a daemon to monitor for when use stops (such as in the case of unmount),
  2. Periodically tell the hard drive to spin itself down and hope it doesn't wake.

If PostgreSQL wakes the drive up, that's absolutely to be expected. Like any decent database, there is always maintenance to be done, and even when you're not accessing that database it will autovacuum. You may consider

  1. Disabling autovacuum (which will help). But, then you'll have to manually VACUUM. Even disabling autovacuum, I have no idea if the PostgreSQL daemon will wake back up the drive.
  2. Shutting down PostgreSQL before you set the hard drives to spindown.
  • Yes, I use hd-idle for the job. Since that works otherwise, I can consider that not to be the issue. This is also a question to try to understand the DB server itself better so it would be great if you could help me out there. What would I have to change to reduce (not deactivate) the I/O accesses? In what ways would that be a general penalty for my system? – Daniel Bischof Jan 7 at 5:24
0

On an idle database server with default configuration, the only thing which would keep the hard-drive active is the "pg_stat_tmp" directory. You can use the setting "stats_temp_directory" to move this someplace else, like a RAM drive (e.g. /dev/shm)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.