Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2015
    Posts
    2

    Unanswered: Extremely Slow READ + Writes ---> 30X [ Informix vs MySQL vs MongoDB ]

    ****************************
    1. Problem : SLOW READ : 30X times , SLOW WRITE: 2X times
    ****************************

    Trying to compare the performance of Reads and inserts in Informix vs MySQL , MongoDB.
    Well there are many posts online which says that Informix is definitely faster then MySQL, but during my testing I am getting very bad performance from Informix.
    batch of 50 consecutive SELECTS with a simple where condition takes 7000 ms whereas MySQl takes only 100 ms and mongodb takes only 5 ms.
    Informix : 7000 ms
    MySQl : 250 ms
    MongoDB : 5 ms

    Writes are alspo slow but manageable as shown below for a batch of 50 consecutive inserts
    Informix : 250 ms
    MySQl : 100 ms
    MongoDB : 50 ms
    Did anyone face simillar issues ?

    Heres what we did, same was done on MySQL AND mongodb and the performance was way Faster. See attached Graph for more details.

    ****************************
    2. Testing Procedure for Informix
    ****************************
    Step 1). CREATES: --- Created a Simple Table with 2 fields ID , readings.
    Where readings is of TimeSeries - RowType having internally 2 fields (tstamp, value).

    Step 2). INSERT :---- Next We ran Multiple Inserts using currentTime as integer formatted for "ID" field and current time for "tStamp" field and again a random number for "value" field
    INSERT INTO humidity_data_vti3 VALUES
    ( 2015040405070200398 , "20150404.050702":ATETIME YEAR TO FRACTION(5), 285)


    Step 3). SELECTS :---- Finally we ran a loop 500 times and each time we generate a randome number and select data from table where "value" field has that randome number.

    select id, tstamp, value from humidity_data_vti3 where value= < RandomValue>


    ****************************
    3. Performance Graphs (click to expand)
    ****************************
    Click image for larger version. 

Name:	test1_with_InformixSimpleAndTsTable.PNG 
Views:	4 
Size:	23.9 KB 
ID:	16298

    Click image for larger version. 

Name:	test2_withSimpleAndTS_Tables.PNG 
Views:	3 
Size:	62.7 KB 
ID:	16299
    ****************************
    4. Technical Information
    ****************************
    operating system server/client : Windows 7
    Informix version :- IBM Informix Windows 64 Bit , v12.10
    Java Library used :- com.informix.jdbc.IfxDriver [ Artifact: com.springsource.com.informix.jdbc , version: 3.0.0.JC3]
    Connection String :-
    jdbc:informix-sqli://localhost:turbo1/db_sri:INFORMIXSERVER=ol_ds_sri;USER=informix;PASS WORD=XXXX
    Connection Properties : {CLIENT_LOCALE=en_US.utf8, DB_LOCALE=en_US.utf8}


    ****************************
    5. How Table was created
    ****************************
    #####################################
    create Database
    #####################################
    create database sensorTSDB


    #####################################
    Creating DBSpace :-
    #####################################
    execute function admin('create dbspace','dbspace1',
    '/data/IBM/informixdemo/demo_on/dbspace1','100 MB','0');

    #####################################
    Creating container
    #####################################
    EXECUTE PROCEDURE TSContainerCreate('sensor_data_containerName', 'ol_ds_sri_dbspace','sensor_data_ts_subtype', 6000, 7000);

    #####################################
    Creating row type :-
    #####################################
    CREATE ROW TYPE sensor_data_ts_subtype (
    tstamp datetime year to fraction(5),
    value decimal(14,3)
    );


    #####################################
    Create Table :-
    #####################################
    CREATE TABLE humidity_data (
    id decimal(20,0) PRIMARY KEY NOT NULL,
    readings TimeSeries(sensor_data_ts_subtype)
    );


    #####################################
    create a Virtual view on the timeseries data :-
    #####################################
    EXECUTE PROCEDURE TSCreateVirtualTab('humidity_data_vti3', 'humidity_data',
    'origin(2015-01-01 00:00:00.00000),calendar(ts_1min),container(sensor _data_containerName),threshold(10000),regular',1, 'readings');

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Vyshal,

    on a well tuned system, Informix can perform 2.000.000 (two millions) complex transactions per SECOND. If 50 simple SELECT take 7 SECONDS, this means you have a critical problem, on Informix configuration, or missing index, or statistics not up to date. It also may be a combination of the 3.
    But stating that informix is slower than mySql, NO WAY.
    Did you try setting sqltrace on and check what it says ?

    By the way I was not aware that MySql could handle TimeSeries datatype.

    Did you try the same test using your MongoDB application connected to Informix with the informix MongoDB API ?
    It should be faster that MongoDB itself
    Last edited by begooden-it; 05-25-15 at 15:12.

  3. #3
    Join Date
    May 2008
    Posts
    19
    Hi Vishal,

    I agree with begooden-it, your configuration seems not to be tuned well.
    For example:
    How many disks are you using for Informix and how are the dbspaces distributed over them?
    Did you separate physical and logical logs from the rootdbs and how big are they?
    Did you define tempdbspaces?
    Maybe you could post your onconfig.

    Best,
    wowbagger_771

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •