web stats
Oracle sys.obj$ deleted objects killing performance - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-15-2011, 11:34 AM
khrim khrim is offline
What's HL7?
 
Join Date: Aug 2011
Location: Juno Beach, FL
Posts: 4
khrim is on a distinguished road
Default Oracle sys.obj$ deleted objects killing performance

I have a Mirth Connect 1.8.2 configured and running against an Oracle Database.

It ran great for the first several months, but has been experiencing problems with the performance of the underlying data dictionary tables. (sys.obj$ appears to be the biggest problem)

Is there any way to configure Mirth Connect so that it doesn't create and delete new database objects for the message files that are being processed. My current system has 64 million deleted records in the sys.obj$ data dictionary table and it is killing performance.

thanks,

Kevin Hrim
Reply With Quote
  #2  
Old 08-15-2011, 11:42 AM
Bostad Bostad is offline
Level 85 Channel Wizard
 
Join Date: Mar 2009
Location: Stratford Ontario
Posts: 712
Bostad is an unknown quantity at this point
Default

Do you have message pruning set?

Its under...plugins in 1.8.2 I think. In 2.0 and later its under settings.

Go into your channels and set how long to keep messages for, and then set up your pruner (I run mine hourly).

If that's not it, I'll defer to someone who knows Oracle.
__________________
I can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
http://www.linkedin.com/pub/david-rothbauer/5/923/518
codeismydrug.wordpress.com
hl7coders.wordpress.com

Test all my code suggestions prior to implementation
Reply With Quote
  #3  
Old 08-15-2011, 04:59 PM
khrim khrim is offline
What's HL7?
 
Join Date: Aug 2011
Location: Juno Beach, FL
Posts: 4
khrim is on a distinguished road
Default

It is an Oracle issue. But, I'm trying to prevent it from re-occurring once I get the database repaired. I was hoping somebody who had contributed to the source could identify if the behavior I've noted (all message files appear to create and destroy a unique set of database objects during processing).
Reply With Quote
  #4  
Old 08-16-2011, 12:44 AM
quimicefa quimicefa is offline
Mirth Guru
 
Join Date: Dec 2007
Location: Barcelona
Posts: 235
quimicefa is on a distinguished road
Default

Mirth 1.8.X uses some temporary objects when configured w/ oracle.
When searching for events or messages in the admin app, Mirth seems to create some tables/sequences/indexes to store the search results and help pagination. This may be causing the problems you're experiencing.

Can you identify the object names ? are they somewhat like MSG_TMP_$uid$, IDX_MSG_TMP_$uid$, SEQ_EVT_TMP_$uid$ ...?

HTH
Reply With Quote
  #5  
Old 08-16-2011, 07:57 AM
khrim khrim is offline
What's HL7?
 
Join Date: Aug 2011
Location: Juno Beach, FL
Posts: 4
khrim is on a distinguished road
Default

Yes, those are exactly the object names that I'm encountering. tens of millions of them.

The problems that this is causing:

1. The database will no longer close without a "shutdown abort" . (I believe that this is caused by the high object count, and the SMON background process failing to finish it's cleanup of the sys.obj$ and other dictionary tables)

2. Any queries that access "all_objects" or "dba_objects" are terrible performers

3. Even though the "mirth" schema is small, the expdb utility cannot export in less than 24 hours.

Thanks for your response to my question. So, is there any way already built into Mirth Connect to avoid these MSG_TMP_.... IDX_MSG_TMP_.... etc... objects being created?

Or, is it my problem to fix and submit back to the code base?

Thanks again,
Kevin Hrim
Reply With Quote
  #6  
Old 08-17-2011, 02:54 AM
quimicefa quimicefa is offline
Mirth Guru
 
Join Date: Dec 2007
Location: Barcelona
Posts: 235
quimicefa is on a distinguished road
Default

As far as I know you can't do anything to avoid these objects being created, unless
-don't use message/event search, even revoking CREATE INDEX,TABLE,SEQUENCE to Mirth user.
-switch to Mirth 2.x (I don't know if this behaviour has been fixed)

which oracle are you using? XE? Standard? Enterprise? patched?
I've been running Mirth w Oracle 10gr2 Standard and didn't hear about these performance issues.

Why do you think that these objects are related to the shutdown abort and SMON problems?

If you use expdp after reorganizing Mirth'schema with:
alter table .... MOVE;
alter index ... rebuild online;
takes 24h ??

HTH
Reply With Quote
  #7  
Old 08-17-2011, 11:06 PM
jacobb jacobb is offline
Mirth Employee
 
Join Date: Aug 2006
Location: Irvine, CA
Posts: 1,218
jacobb is an unknown quantity at this point
Default

I think those are created less in the latest releases. If I remember right, they used to be created more often when you did things like reprocessing, removing messages, etc. Try upgrading and let us know if it solves the issue.
__________________
Jacob Brauer
Director, Software Development
NextGen Healthcare

Reply With Quote
  #8  
Old 08-18-2011, 07:34 AM
khrim khrim is offline
What's HL7?
 
Join Date: Aug 2011
Location: Juno Beach, FL
Posts: 4
khrim is on a distinguished road
Default

Thanks for the feedback.

I feel that we are experiencing these issues (SMON failing to keep up) for a couple of reasons.

1. It's a dual core box with a couple other instances. Nothing that consumes too many resources, but enough to keep the cpu wait queue running between 2 and 4 continuously.

2. The instance is shared with Cyclone (EDI) that is also processing a high volume of messages.

3. It is older code, so I should upgrade to see if the impact is less.

4. I believe that I hit a tipping point with the object left overs, after that it was inevitable that I would be creating more deleted objects faster that the SMON process could clean up. (and it appears that this is a very steep linear degradation )

My next steps:

1. Use transportable tablespace feature in Oracle to move the mirth tablespace to a fresh instance.

2. Upgrade to Mirth Connect 2.x

3. Watch to see if issue has been addressed.

Thanks again for everybody's assistance.

Kevin Hrim
Reply With Quote
Reply

Tags
database, oracle database, performance, performance tuning

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 02:26 PM.


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