Announcement

Collapse
No announcement yet.

Mirth 2.1 RC1 TEST Postgres 8.4 versus Oracle 10gR2

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

    sigpic

    Comment


    • #3
      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

      Comment


      • #5
        ... and postgres logs during this test.
        Attached Files

        Comment


        • #6
          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

          Comment


          • #7
            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)

            Comment


            • #8
              Originally posted by [email protected] 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.

              Comment


              • #9
                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?

                Comment


                • #10
                  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 Files
                  Last edited by [email protected]; 04-20-2011, 09:04 AM.

                  Comment


                  • #11
                    solving the problem'

                    Hi all

                    We're having the same error on a mirth instance using derby. The source is reading datas from an oracle instance.

                    Mirh version is

                    Mirth Connect Administrator 2.1.1.5490

                    last available on mirth download site

                    What should we do to solve this issue? We can't acccess oracle configurations since we're not administrators of the oracle server

                    Comment

                    Working...
                    X