Last weekend I replaced my aging ASG V8 box with a slightly newer box with a dual-core CPU and double the memory. I also took the opportunity to upgrade to UTM9, with a fresh install of 9.003 onto which I restored a fresh V8 backup.
Right from the outset, I noticed that something was occasionally spiking the CPU, which I could tell by the increased noise from the cooling fan as it ramped up to cool the processor. I ran top and found that postgres was consuming 99% of the CPU at those times. These spikes would only last for around 10-15 seconds, so I shrugged and ignored it.
However, after a few days, the regular noise spikes wore me down, and I decided to see if there was anything I could to to ease the problem. I didn't want to reduce my reporting settings, so I searched the forums for possible other solutions and found this thread. While it refers to 8.305, the symptoms were sufficiently similar that it was worth a look.
In that thread, Scott Klassen suggests running the command
/etc/init.d/postgresql rebuild
Sometime during V8, this changed to
/etc/init.d/postgresql92 rebuild
with the caveat that all reporting data will be lost. Since I'd only been running this installation for a few days, I figured it was worth a try:
loginuser@astaro:/home/login > sudo /etc/init.d/postgresql rebuild
Rebuilding PostgreSQL database, all reporting data will be lost!
Enter "yes" to continue...
yes
:: Stopping PostgreSQL done
:: Initializing the PostgreSQL database done
:: Starting PostgreSQL done
no schema upgrades found
:: Restarting SMTP Proxy
:: Stopping SMTP Proxy
[ ok ]
:: Starting SMTP Proxy
[ ok ]
[ ok ]
loginuser@astaro:/home/login >
Checking the system log showed the following:
2012:10:31-22:34:18 astaro postgres[3695]: [2-1] LOG: received fast shutdown request
2012:10:31-22:34:18 astaro postgres[3695]: [3-1] LOG: aborting any active transactions
2012:10:31-22:34:18 astaro postgres[6732]: [2-1] FATAL: terminating connection due to administrator command
2012:10:31-22:34:18 astaro postgres[6708]: [2-1] FATAL: terminating connection due to administrator command
2012:10:31-22:34:18 astaro postgres[5252]: [2-1] FATAL: terminating connection due to administrator command
2012:10:31-22:34:18 astaro postgres[4548]: [2-1] FATAL: terminating connection due to administrator command
2012:10:31-22:34:18 astaro postgres[3702]: [2-1] LOG: autovacuum launcher shutting down
2012:10:31-22:34:18 astaro postgres[4487]: [2-1] FATAL: terminating connection due to administrator command
2012:10:31-22:34:18 astaro postgres[3700]: [1-1] LOG: shutting down
2012:10:31-22:34:19 astaro postgres[3700]: [2-1] LOG: database system is shut down
2012:10:31-22:34:27 astaro postgres[8781]: [1-1] LOG: database system was shut down at 2012-10-31 22:34:27 CDT
2012:10:31-22:34:27 astaro postgres[8780]: [1-1] LOG: database system is ready to accept connections
2012:10:31-22:34:27 astaro postgres[8784]: [1-1] LOG: autovacuum launcher started
2012:10:31-22:34:28 astaro postgres[8804]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:34:28 astaro postgres[8824]: [2-1] ERROR: database "epp" does not exist
2012:10:31-22:34:28 astaro postgres[8824]: [2-2] STATEMENT: DROP DATABASE epp;
2012:10:31-22:34:28 astaro postgres[8824]: [2-3]
2012:10:31-22:34:28 astaro postgres[8826]: [2-1] ERROR: role "epp" does not exist
2012:10:31-22:34:28 astaro postgres[8826]: [2-2] STATEMENT: DROP ROLE epp;
2012:10:31-22:34:28 astaro postgres[8826]: [2-3]
2012:10:31-22:34:30 astaro postgres[8831]: [2-1] FATAL: database "smtp" does not exist
2012:10:31-22:34:33 astaro postgres[8832]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:34:37 astaro postgres[8853]: [2-1] ERROR: database "hotspot" does not exist
2012:10:31-22:34:37 astaro postgres[8853]: [2-2] STATEMENT: DROP DATABASE hotspot;
2012:10:31-22:34:37 astaro postgres[8853]: [2-3]
2012:10:31-22:34:38 astaro postgres[8856]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:34:40 astaro postgres[8857]: [2-1] FATAL: database "smtp" does not exist
2012:10:31-22:34:43 astaro postgres[8859]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:34:48 astaro postgres[8881]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:34:50 astaro postgres[8882]: [2-1] FATAL: database "smtp" does not exist
2012:10:31-22:34:53 astaro postgres[8883]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:34:55 astaro postgres[8925]: [2-1] ERROR: database "reporting" does not exist
2012:10:31-22:34:55 astaro postgres[8925]: [2-2] STATEMENT: DROP DATABASE reporting;
2012:10:31-22:34:55 astaro postgres[8925]: [2-3]
2012:10:31-22:34:55 astaro postgres[8927]: [2-1] ERROR: tablespace "reporting" does not exist
2012:10:31-22:34:55 astaro postgres[8927]: [2-2] STATEMENT: DROP TABLESPACE reporting;
2012:10:31-22:34:55 astaro postgres[8929]: [2-1] ERROR: role "reporting" does not exist
2012:10:31-22:34:55 astaro postgres[8929]: [2-2] STATEMENT: DROP ROLE reporting;
2012:10:31-22:34:55 astaro postgres[8929]: [2-3]
2012:10:31-22:34:58 astaro postgres[8936]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:35:00 astaro postgres[8937]: [2-1] FATAL: database "smtp" does not exist
2012:10:31-22:35:03 astaro postgres[8963]: [2-1] FATAL: database "reporting" does not exist
2012:10:31-22:35:08 astaro postgres[8968]: [2-1] ERROR: function ins_accounting(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist at character 8
2012:10:31-22:35:08 astaro postgres[8968]: [2-2] HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2012:10:31-22:35:08 astaro postgres[8968]: [2-3] STATEMENT: select ins_accounting($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
2012:10:31-22:35:10 astaro postgres[8988]: [2-1] FATAL: database "smtp" does not exist
2012:10:31-22:35:20 astaro postgres[9006]: [2-1] ERROR: relation "m" does not exist at character 42
2012:10:31-22:35:20 astaro postgres[9006]: [2-2] STATEMENT: SELECT count(DISTINCT message_id) FROM m WHERE cluster_id = '0' AND location = 'work'
2012:10:31-22:35:20 astaro postgres[9006]: [3-1] ERROR: relation "m" does not exist at character 15
2012:10:31-22:35:20 astaro postgres[9006]: [3-2] STATEMENT: SELECT * FROM m WHERE cluster_id = '0' AND location = 'output' AND action = 'delete'
2012:10:31-22:35:20 astaro postgres[9006]: [4-1] LOG: unexpected EOF on client connection
I'm no database expert, but that sure looks to me like something wasn't right!
Sure enough, the CPU spikes have now disappeared completely. Great news, but it does leave me wondering how the DB got screwed up in the first place from a fresh install.
This thread was automatically locked due to age.
