Excessive communication between RT and postgresql

After upgrading to RT 4.2.1, the postgresql database is producing massive volumes of WAL files around the clock with peaks during office hours.

Before the upgrade - we had very limited volumes of WAL files produced, and nothing at all during non work hours. Now - at least 32MB, but usually 64MB of WAL is written every minute, when the system is supposed to be quiet.

The pattern of volume of WAL produced closely match up to the graph of network traffic between the RT server and the database server.

Should there be any traffic at all between the RT server and database server when the system is not in use? Apart from occasional handshakes etc.

Any clues on what this might be caused by?

We are running
RT 4.2.1 and Apache 2.2.15 on CentOS 6.4
Postgresql 9.3 on CentOS 5.10

Kind regards
//Henrik

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

After upgrading to RT 4.2.1, the postgresql database is producing
massive volumes of WAL files around the clock with peaks during office
hours.

And what version were you upgrading from?

Before the upgrade – we had very limited volumes of WAL files
produced, and nothing at all during non work hours. Now – at least
32MB, but usually 64MB of WAL is written every minute, when the system
is supposed to be quiet.

Does that output cease if you temporarily turn off the webserver? What
about if you disable your incoming MTA?

Should there be any traffic at all between the RT server and database
server when the system is not in use? Apart from occasional handshakes
etc.

Depends what you mean by “in use.” RT has no long-running programs
aside from the web front-end; as such, requests to the database are only
generated by requests to Apache, or the cron jobs. At times when there
are no Apache requests and no cron jobs, there should be no significant
database traffic.

Bear in mind that even if no users are accessing the website, incoming
email gets POST’ed to the mail gateway endpoint, which may account for
some of the activity when you believe the system is not in use.

It may be enlightening to examine the generated WAL logs using

  • Alex

We upgraded from 4.0.17.

Yes. Stopping RT stopped the WAL generation. Continued as if nothing happened when started up again.

The mail into the system should be quite negligible in this case since that would make the database grow in roughly the same pace as the WAL log generation, but the database has grown only 200M while there has been written more than 500G of WAL.

pg_xlogdump shows that more than 99% of the activity is due to inserts, deletes and subsequent vacuuming on rt4.sessions and corresponding toast tables.

Regards
//Henrik

Confidentiality Notice: This message is private and may contain confidential and proprietary information. If you have received this message in error, please notify us and remove it from your system and note that you must not copy, distribute or take any action in reliance on it. Any unauthorized use or disclosure of the contents of this message is not permitted and may be unlawful.

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Alex Vandiver
Sent: den 16 december 2013 23:17
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Excessive communication between RT and postgresql

After upgrading to RT 4.2.1, the postgresql database is producing
massive volumes of WAL files around the clock with peaks during office
hours.

And what version were you upgrading from?

Before the upgrade – we had very limited volumes of WAL files
produced, and nothing at all during non work hours. Now – at least
32MB, but usually 64MB of WAL is written every minute, when the system
is supposed to be quiet.

Does that output cease if you temporarily turn off the webserver? What about if you disable your incoming MTA?

Should there be any traffic at all between the RT server and database
server when the system is not in use? Apart from occasional handshakes
etc.

Depends what you mean by “in use.” RT has no long-running programs aside from the web front-end; as such, requests to the database are only generated by requests to Apache, or the cron jobs. At times when there are no Apache requests and no cron jobs, there should be no significant database traffic.

Bear in mind that even if no users are accessing the website, incoming email gets POST’ed to the mail gateway endpoint, which may account for some of the activity when you believe the system is not in use.

It may be enlightening to examine the generated WAL logs using PostgreSQL: Documentation: 16: O.3. pg_xlogdump renamed to pg_waldump

  • Alex