web stats
Mirth & PostGreSQL - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 07-16-2013, 08:05 AM
jtate jtate is offline
Mirth Newb
 
Join Date: May 2013
Posts: 15
jtate is on a distinguished road
Default Mirth & PostGreSQL

Using Mirth Connect 2.2.1.5861 and PostgreSQL 1.12.2...

When performing maintenance on the PostgreSQL database ie: vacuum and reindex, the reindex is stopping all activity in Mirth. Is this normal?
Reply With Quote
  #2  
Old 07-16-2013, 08:11 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

REINDEX is going to cause performance issues as it is taking place, as it does for most databases.

REINDEX locks out writes, but not reads, of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index.

The subsequent CREATE INDEX locks out writes, but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.
Reply With Quote
  #3  
Old 07-16-2013, 10:19 AM
jtate jtate is offline
Mirth Newb
 
Join Date: May 2013
Posts: 15
jtate is on a distinguished road
Default

Thank you very much for the explanation.
Reply With Quote
  #4  
Old 08-21-2013, 01:28 PM
jtate jtate is offline
Mirth Newb
 
Join Date: May 2013
Posts: 15
jtate is on a distinguished road
Default Table Size

I had several channels set to keep 90 or 180 days worth of messages. I have now set them all to 30. My hope is that once these messages purge, the reindex won't take as long to perform. Am I correct in this line of thinking? Also, if this is the case, will a basic vacuum clear out that space so the following reindex will run quicker, or will I need to perform a vacuum-full to accomplish this?
Reply With Quote
  #5  
Old 08-22-2013, 05:28 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,277
cory_cole is on a distinguished road
Default

That is correct thinking but unless you have a lot of traffic or large messages 60-150 days won't increase the time noticably.
Reply With Quote
  #6  
Old 08-22-2013, 05:59 AM
jtate jtate is offline
Mirth Newb
 
Join Date: May 2013
Posts: 15
jtate is on a distinguished road
Default Thanks

When performing the vacuum, do I need to just use the default, or select the "full" to hopefully increase reindex speed?
Reply With Quote
Reply

Tags
postgres

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 01:51 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation