web stats
Mirth 2.1 RC1 TEST Postgres 8.4 versus Oracle 10gR2 - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-18-2011, 08:04 AM
bruno.martin@chu-dijon.fr bruno.martin@chu-dijon.fr is offline
Mirth Newb
 
Join Date: Dec 2009
Posts: 11
bruno.martin@chu-dijon.fr is on a distinguished road
Default Mirth 2.1 RC1 TEST Postgres 8.4 versus Oracle 10gR2

Attempted comparison of Mirth 2.1 RC1 over Postgres 8.4 versus Oracle 10gR2 (Windows 32 bit OS): load test.

For any comments.

In a first test load of Mirth 2.1 RC1, Windows XP Pro 32 bit, we conducted a comparison of the behavior of Mirth two DBMS: PostgreSQL 8.4 and Oracle 10gR2. Both DBMS were installed with their default configuration, but with in both cases, with creating a tablespace dedicated to specific Mirth (autoextend for Oracle). DBMS were installed in both cases on the same server as Mirth to receive access localhost. The tested Mirth channel was polling every 0.5 seconds returning each time 100 rows from a local source table containing 100,000 lines, the data being written to a target table on the same local database.
- For testing of Mirth + Postgres, the source table and target table (placed in a second tablespace) were tables on local Postgres DBMS.
- For testing of Mirth + Oracle, these two tables (placed in a second tablespace) were tables on Oracle local DBMS.
The Oracle database was shut down during testing on postgres, the Postgres databasewas shut down during testing on Oracle.
The machine OS for these first tests (next Suse Linux 64) was Windows 32, had a SATA 7200 rpm 160 GB disk (55 to 115 MB / s from the utility HD Tune), 2 GB DDR2, an intel core duo processor E5300 2.6 Ghz. The test was performed by activating the complete archive of messages Mirth Canal, with a JVM Hotspot 1.6.0_20, and 256 MB of Heap Size for Mirth.
Results:
A) Disk space occupied by Mirth (internal message table): 3.62 times more space with Oracle than Postgres priori because of the default options of storing Oracle CLOB fields with Oracle in the tablespace dedicated to Mirth: 1344 Mb attached to the table and 3144 MB on a $ SYS_LOBxxx, Oracle proposes by default for each column clob: storage on the tablespace of Mirth chosen: (LOB RESPONSE_MAPà Storage in row, Chunk 8192 PCTINCREASE initial 64K 0, BUFFER_POOL DEFAULT ). The messages from the source (HL7 2.5 ADT) had an average size of 0.707 KB , with 100,800 retrieved messages on the Mirth test channel.
Volume of data stored on disk for the Mirth internal message table: 17.5 times the size of the message source in Postgres, 63.3 times the size of the message source as Oracle (for this test).
? optimization of internal Mirth message table over Oracle for clob columns ? (or more general tuning on the database conf. for clob?)

B) Time Message Transfer: (for the last 64,800 messages): 33 minutes under Postgres, 68 minutes for Oracle.

C) Attempt to search in the Mirth dashboard, on Transformed messages, display mode: 999 messages, quick search (here an HL7 Account number HL7 that does not exist in archived messages). Research period: 3 weeks: Search time: about 20 seconds in Oracle, about 127 seconds under Postgres.

D) Reliability of data transfer (RC1): When the manual shutdown of Mirth Channel on the dashboard after a polling of 100800 messages:
After a manual shutdown of the channel: With postgres: No error, with Oracle Sgbd, 72 errors on the messages last polled before shutdown, messages that are not present among the SENT, not present among the QUEUED messages, not marked as ERRORED (so: lost messages for the distribution to the target, although present (archived) at the Source in Mirth).

The error returned 72 times in the logs (Oracle Mirth) is as follows:
[2011-04-18 13:08:53,812] ERROR (com.mirth.connect.connectors.jdbc.JdbcMessageRece iver:194): Error in channel: ZBR1_Y1_ADT_DEPUIS_CPAGE
org.mule.umo.ComponentException: Cannot route event as component "f28d7ea9-8e4d-46db-9c38-6fca9e4838af" is stopped. Connector that caused exception is: f28d7ea9-8e4d-46db-9c38-6fca9e4838af. Message payload is of type: org.apache.commons.dbutils.BasicRowProcessor$CaseI nsensitiveHashMap

at org.mule.impl.model.AbstractComponent.sendEvent(Ab stractComponent.java:258)
at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:201)
at org.mule.routing.inbound.InboundMessageRouter.send (InboundMessageRouter.java:176)
at org.mule.routing.inbound.InboundMessageRouter.rout e(InboundMessageRouter.java:143)
at org.mule.providers.AbstractMessageReceiver$Default InternalMessageListener.onMessage(AbstractMessageR eceiver.java:487)
at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:266)
at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:229)
at com.mirth.connect.connectors.jdbc.JdbcMessageRecei ver.processMessage(JdbcMessageReceiver.java:179)
at org.mule.providers.TransactedPollingMessageReceive r$1.doInTransaction(TransactedPollingMessageReceiv er.java:98)
at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:72)
at org.mule.providers.TransactedPollingMessageReceive r.poll(TransactedPollingMessageReceiver.java:104)
at org.mule.providers.PollingMessageReceiver.run(Poll ingMessageReceiver.java:97)
at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor.runWorker(ThreadPoolExecutor.java: 1061)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :575)
at java.lang.Thread.run(Unknown Source)

? Mirth 2.1 RC1 problem with Oracle in case of very short polling delay ? (short delay only here for this load test).
---
(rem: a second load test Postgres versus Oracle will be conducted soon on Suse Linux 64-bit os)
Attached Files
File Type: txt SRC_TABLES_ORACLE.txt (9.5 KB, 12 views)
File Type: txt SRC_TABLES_POSTGRES.txt (1.5 KB, 5 views)
File Type: txt EXAMPLE_HL7_ADT_SRC_MSG.txt (1.1 KB, 4 views)
File Type: xml ZBR1_Y1_ADT_DEPUIS_CPAGE_ORACLE.xml (31.0 KB, 4 views)
File Type: xml ZBR1_Y1_ADT_DEPUIS_CPAGE_POSTGRES.xml (30.9 KB, 5 views)
Reply With Quote
  #2  
Old 04-18-2011, 10:09 AM
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

Very interesting information. Thanks for posting it!
__________________
Jacob Brauer
Director, Software Development
NextGen Healthcare

Reply With Quote
  #3  
Old 04-18-2011, 11:23 AM
steven_kehlet steven_kehlet is offline
Mirth Employee
 
Join Date: Jul 2007
Posts: 25
steven_kehlet
Default

Thanks Bruno, very interesting. Can you post a few things to help us understand this better?

- your postgresql.conf
- Postgres' serverlog for the time you were running the benchmark
- Oracle's init variables, obtain with SHOW PARAMETERS;
- Oracle's alert.log for the time you were running the benchmark
Reply With Quote
  #4  
Old 04-18-2011, 10:22 PM
bruno.martin@chu-dijon.fr bruno.martin@chu-dijon.fr is offline
Mirth Newb
 
Join Date: Dec 2009
Posts: 11
bruno.martin@chu-dijon.fr is on a distinguished road
Default

Here Oracle and Postgres configuration files.
Attached Files
File Type: txt postgresql.conf.txt (17.2 KB, 12 views)
File Type: txt init.ora.31320119357.txt (2.6 KB, 8 views)
File Type: txt alert_eaim1.log.txt (91.2 KB, 10 views)
File Type: txt Paramètres d'initialisation Oracle.htm.txt (95.7 KB, 10 views)
Reply With Quote
  #5  
Old 04-18-2011, 10:31 PM
bruno.martin@chu-dijon.fr bruno.martin@chu-dijon.fr is offline
Mirth Newb
 
Join Date: Dec 2009
Posts: 11
bruno.martin@chu-dijon.fr is on a distinguished road
Default

... and postgres logs during this test.
Attached Files
File Type: txt postgresql-2011-04-18_095738.log.txt (10.1 KB, 5 views)
Reply With Quote
  #6  
Old 04-18-2011, 11:23 PM
nicovn nicovn is offline
OBX.2 Kenobi
 
Join Date: Oct 2006
Location: Belgium
Posts: 64
nicovn
Default

Hi Bruno,


It would be nice if you could run the same test on Oracle 11, since the LOB storage in Oracle 11 has been completely redesigned.

http://download.oracle.com/docs/cd/B...at.htm#i972443

I think that you'll see a performance boost...


Best Regards

Nico
__________________
Nico Vannieuwenhuyze

Amaron.be
Reply With Quote
  #7  
Old 04-19-2011, 09:50 AM
bruno.martin@chu-dijon.fr bruno.martin@chu-dijon.fr is offline
Mirth Newb
 
Join Date: Dec 2009
Posts: 11
bruno.martin@chu-dijon.fr is on a distinguished road
Default

Quote:
Originally Posted by nicovn View Post
Hi Bruno,


It would be nice if you could run the same test on Oracle 11, since the LOB storage in Oracle 11 has been completely redesigned.

http://download.oracle.com/docs/cd/B...at.htm#i972443

I think that you'll see a performance boost...


Best Regards

Nico
After completion of the same test on Oracle 11gR1, the result is unfortunately the same:
30 msg / s on Oracle 11g msg / s versus 28.3 msg /s on Oracle 10g
and a disk-storage of 23.24 KB / message , per row of the internal table "message" of Mirth Oracle 11g
versus 22.17 KB / message on Oracle 10g.
Of course the test was performed with a local Oracle database on the same workstation, with this test without error during the manual shutdown of Mirth channel.
(obviously such worksation is undersized to accommodate an Oracle server)
Reply With Quote
  #8  
Old 04-19-2011, 01:52 PM
steven_kehlet steven_kehlet is offline
Mirth Employee
 
Join Date: Jul 2007
Posts: 25
steven_kehlet
Default

Quote:
Originally Posted by bruno.martin@chu-dijon.fr View Post
Here Oracle and Postgres configuration files.
Hi Bruno, I see in the Oracle alert.log lots of "Checkpoint not complete" errors. This happens when the redo logs aren't ready to be recycled yet, and it kills performance because Oracle must stop and wait for the log to become available. You can google around to see what I mean.

My Oracle express instance came with two 50MB redo logs. I would add some bigger log files so Oracle never runs into this, and retest. Something like:

Code:
su - oracle
export ORACLE_HOME='/usr/lib/oracle/xe/app/oracle/product/10.2.0/server' 
export ORACLE_SID=XE 
$ORACLE_HOME/bin/sqlplus / as sysdba 

ALTER DATABASE ADD LOGFILE SIZE 1G;
ALTER DATABASE ADD LOGFILE SIZE 1G;
ALTER DATABASE ADD LOGFILE SIZE 1G;
I'm looking over the other files.
Reply With Quote
  #9  
Old 04-20-2011, 05:00 AM
bruno.martin@chu-dijon.fr bruno.martin@chu-dijon.fr is offline
Mirth Newb
 
Join Date: Dec 2009
Posts: 11
bruno.martin@chu-dijon.fr is on a distinguished road
Default

Quote:
Originally Posted by steven_kehlet View Post
Hi Bruno, I see in the Oracle alert.log lots of "Checkpoint not complete" errors. This happens when the redo logs aren't ready to be recycled yet, and it kills performance because Oracle must stop and wait for the log to become available. You can google around to see what I mean.

My Oracle express instance came with two 50MB redo logs. I would add some bigger log files so Oracle never runs into this, and retest. Something like:

Code:
su - oracle
export ORACLE_HOME='/usr/lib/oracle/xe/app/oracle/product/10.2.0/server' 
export ORACLE_SID=XE 
$ORACLE_HOME/bin/sqlplus / as sysdba 

ALTER DATABASE ADD LOGFILE SIZE 1G;
ALTER DATABASE ADD LOGFILE SIZE 1G;
ALTER DATABASE ADD LOGFILE SIZE 1G;
I'm looking over the other files.
With 3 additional 1 GB redo log files, performance is actually better on Oracle 10gR2 and without error in the file alert.log.
(previously 3 50 MB redo in the default installation)
Result: 27.08 msg / s instead of 15.88 msg / s before (32.72 msg / s with Postgres 8.4).

The problem of disk space remains however, with Oracle.
Would it be worthwhile to use Oracle 11g with an option for compressing the inner Mirth "message" table or possibly the entire Mirth tablespace?
Reply With Quote
  #10  
Old 04-20-2011, 08:24 AM
bruno.martin@chu-dijon.fr bruno.martin@chu-dijon.fr is offline
Mirth Newb
 
Join Date: Dec 2009
Posts: 11
bruno.martin@chu-dijon.fr is on a distinguished road
Default Compression test on Oracle 11gR1

After the same test on a 11gr1 database, with 3 other 1GB redolog files, (total 6 log files)
after drop & recreate Mirth internal tables: "message" and "attachment" to add a storage property: "COMPRESS FOR ALL OPERATIONS",
(tables On the default tablespace for Mirth user (and schema) witch is not compressed)
There was no significant reduction in disk space on the Mirth tablespace (compared with no compressed tables on Oracle 11gr1)
In addition, response times are very bad. (only 1 msg/s) Then again found an error when manualy stopping the channel with message loss undistributed.
However, the resources of the workstation are not saturated. (task manager display: memory: 1.5 GB / 2 GB physical memory, CPU Above 25%)
Attached Images
File Type: jpg Mirth1.2RC_Win32_Oracle11gR1_compressed.JPG (338.0 KB, 13 views)
Attached Files
File Type: txt MAJ_ORA_11GR1_MIRTHDB.sql.txt (2.9 KB, 2 views)
File Type: txt alert_eaim2.log.txt (55.6 KB, 1 views)

Last edited by bruno.martin@chu-dijon.fr; 04-20-2011 at 09:04 AM.
Reply With Quote
Reply

Tags
mirth 2.1rc1, oracle, 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 03:07 PM.


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