Author Topic: Postgres process hogging HDD 90% of the time.  (Read 1038 times)

uacnix

  • Zen Apprentice
  • *
  • Posts: 10
  • Karma: +0/-0
    • View Profile
Postgres process hogging HDD 90% of the time.
« on: December 08, 2016, 11:25:06 pm »
Hello, I have the following problem- the postgres process is reading something form disk all the time. Whats interesting, in iotop it has the COMMAND
postgres: ebox eboxlogs [local] SELECT
So I thought its somehow connected to the log module, which is also very laggy for me- so laggy it causes timeouts on the webinterface almost every time I try to do something. I tried disabling the log module, but the process is still hogging the HDD. I'd rather avoid restarting the postgres database, but its really eating the drive...
Do you guys have any ideas on what can I do to lower the disk usage by that process?

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #1 on: December 09, 2016, 06:03:16 pm »
You didn't mention which version of Zentyal you were using but to my knowledge postgres is not used by Zentyal (any version) .  Have you installed something that uses postgres?  Did you perhaps mean postfix or postgrey?

uacnix

  • Zen Apprentice
  • *
  • Posts: 10
  • Karma: +0/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #2 on: December 10, 2016, 05:46:57 pm »
Its zentyal 2.2 and it would seem it does something with zentyal components (ebox):

On the other hand, I see deadlocks related to spamassasin's bayesian filter in /var/log/postgresql/postgresql-8.4-main.log.
Still, its hogging the HDD, and I'd love to kill it, but I don't know how it will influence the rest of the services.
« Last Edit: December 10, 2016, 05:49:15 pm by uacnix »

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #3 on: December 10, 2016, 08:23:41 pm »
Apologies,  I wasn't aware that the 2 series used postgres.  You can use pgadmin to kill the query if you like.  You don't need to kill the whole service just the offending process.

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #4 on: December 10, 2016, 10:55:23 pm »
How long do you keep your logs?  Is it possible that you have logs going back to 2011?

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #5 on: December 10, 2016, 11:59:47 pm »
I have looked at your posting history.  You have inherited this relic server from a person who retired.  The only conclusion that I can come to for not updating the server was related to  something you were using not being in the 3 series of Zentyal.  Is this correct?  Do you folks use Zarafa ?

uacnix

  • Zen Apprentice
  • *
  • Posts: 10
  • Karma: +0/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #6 on: December 29, 2016, 11:11:07 am »
The reason of not updating is the relic...ness of the server - I have absolutely no warranty that it's gonna reboot succesfully and won't go FUBAR, making my life a living hell since we are using it as our main mailserver. I'd love to upgrade it to at least 4.x, but I don't know if it won't crash it or kill it.
We use mailserver (pop3/imap and smtp) + mailfilter + webmail and ftp- that would be all
« Last Edit: December 29, 2016, 02:11:29 pm by uacnix »

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #7 on: December 29, 2016, 08:06:58 pm »
You haven't mentioned this elsewhere would you tell me if this machine is physical or virtual?  Are there backups available for the server?  From your description I think it would be fairly straightforward to migrate away from a 2 series to something newer if you are interested.

uacnix

  • Zen Apprentice
  • *
  • Posts: 10
  • Karma: +0/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #8 on: January 02, 2017, 12:36:51 pm »
The machine is "as physical as it can be" - its a separate machine. Backups are made... on the same drive- well I theoretically could plug in some usb drive, then using gui copy everything from that backup location, as we only need emails from it, everything else can go.
The thing is, would I be able to restore those mails and email accounts later on?

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #9 on: January 03, 2017, 04:23:03 am »
I am thinking that it should be possible but I still don't know if you are using Zarafa or roundcube.  One of them uses mbox format (Roundcube) and the other uses a proprietary database format (Zarafa).  Zarafa is actually pretty painless to migrate users because it has facilities to disable password checking (and therefore letting you migrate mailboxes from one machine to another via imap copy tools).

I assume that you have a machine that you can perform testing on?  A virtual machine environment perhaps?

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #10 on: January 07, 2017, 08:37:20 pm »
It has been awhile since you replied last.  The gist of the solution you are looking for is to stand up a new email server that supports IMAP and then use a tool such as IMAPCopy to move things from one server to the  other.  The details of how you get there are dependant on the software that is actually in use.  Since I don't have all the details I am not in a position  to advise further.  If someday you return to this issue I hope you find this useful.

Good luck

uacnix

  • Zen Apprentice
  • *
  • Posts: 10
  • Karma: +0/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #11 on: January 09, 2017, 11:00:29 pm »
Hello,

Sorry I didn't reply, I was on holiday and kinda forgot about the work.
Yesterday I set up Zentyal 4.2 on some free PC I had laying around (Intel C2d E7200 -old server has Xeon X3430, yet with better drive - WD Black 1TB, and 2 gigs of ram instead of pathetic 1gb).
On the old server, the accounts created in the webinterface are also created in /home/<accountname>, and the actual mails are put into /var/vmail/domainname/<accountname>, where they have cur, new, temp and some .Drafts and other folders - almost every mailfolder seems to have those subfolders, also every folder has dovecot-uidlist and dovecot.index and maildirsize like stuff.
I also thought about just copying everything from one server to other, after checking with one account that it works :D

EDIT:
Based on this topic- https://forum.zentyal.org/index.php?topic=7927.0
Quote
If Zarafa is not used, then MDA relies on Procmail to deliver mails to /var/vmail/domain/user folder.
I think we are using Roundcube...
« Last Edit: January 09, 2017, 11:03:50 pm by uacnix »

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #12 on: January 10, 2017, 06:02:39 am »
Try this:

Setup Thunderbird on your own desktop/laptop.  Set  it up to connect  to the old server with your account (IMAP).  Set up a separate account on the "new" server for yourself and connect to it via Thunderbird as well. 

Select a random group of messages on the old server in Thunderbird and then right click --->copy---new server---inbox.  This should work in all cases.

If you are dealing with a handful of users I suggest having the users migrate  themselves just like this.  This takes the logistics problems out of your court and gives the users a little ownership of the outcome.  Depending on your tolerance for hand holding ,  I think this would be a workable solution for about 30-40 users.  Do you have any more than that?  I can suggest a more automated approach if needed.


-Denny

uacnix

  • Zen Apprentice
  • *
  • Posts: 10
  • Karma: +0/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #13 on: January 11, 2017, 02:49:57 am »
We have about 150-200 users, mostly older people "I click deze icons and dose mails show up" computer illiterates.
EDIT:
I did the following in terminal:
Code: [Select]
sudo su postgres
psql -c "select * from pg_stat_activity"
see that mess:

Think - "Hell, why not? I'm not in the office tomorrow, so they'll have to live with it if anything goes wrong"
Code: [Select]
psql -c "select pg_terminate_backend(4536)"Killed some douchebag query which was hogging the HDD all the time, server load levels went right away back to normal (e.g. to 0.08 from 2.60)
Saved the world, day and my ass since I don't have to move it to a new machine, just backup/clone the drive in Acronis just in case and do zentyal upgrade right away from the webinterface.

Next thing I gotta solve is:
Quote
How do i protect this server from spoofing our own emails, like my email is it@domain.com, and i get mails from it@domain.com, also how do i add more sophisticated rules into spamassassin's local.cf and make that actually work, since "module reload just grabs the list of whitelist/blacklist emails and does plain whitelist_from/blacklist_from, where I can use whitelist_from_rcvd or shortcircuiting for lower CPU usage"
But thats another story :3 Thank You all for your help guys!

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +58/-0
    • View Profile
Re: Postgres process hogging HDD 90% of the time.
« Reply #14 on: January 11, 2017, 03:39:51 am »
I can appreciate your position and I really don't want to rain on your parade but there is no clean way to upgrade from a 2.x system to a 3.x or 4.x.  Basically you will need to  migrate your mail by hand and stand up the  rest manually.  BTW spamassasin is no longer a managed package in Zentyal 4.x though you can install it yourself and manage it via command line just fine.