This discussion is archived
7 Replies Latest reply: Mar 31, 2008 7:25 AM by 800308 RSS

Data loads are slower on the server?

800308 Newbie
Currently Being Moderated
Howdy,

We're inserting about 100,000 byte arrays, each of 608,000 bytes into an informix List column via JDBC.

My problem is the performance drops (it's nearly twice as slow) when I run it in the test evironment as compared to my development environment, and I expected it to be substantially faster. I'm getting between 7 and 9 rows a second in development, but it drops to 3 to 4 rows per second in test... and that's not fast enough.

My development environment uses THE test database on the unix server (sparc solaris 16 CPU's & 8 Gig), and a local weblogic server on windows development machine (2.8 duo with 2Gig), talking over a really fast LAN (I think it's a million megabits per second, but's that's not my area).

The test database and the test weblogic server are both on the same unix machine... so there's no network involved... so I expected a substantial performance increase... but got the opposite. Solaris's prstat utility reports that the server is barely working during the load... which is indicative that the process is I/O bound... but throughput has actually dropped when we're NOT using the network.

I'm stumped... Please does anyone have any brilliant insights? My next step is vendor support... and that's allways a joy.

Thanx all. Keith.

Edited by: corlettk on 31/03/2008 12:18 - I should have said I'm using a script to start the JVM in both cases, so they've got the same memory allocations.
  • 1. Re: Data loads are slower on the server?
    807601 Newbie
    Currently Being Moderated
    Hello Keith,

    These are the things I actually can think of:

    1) Do you use the same driver? There mabe some restrictions. I had a similar problem sometime ago using DB/2, changed the driver (I think I used the network driver before and changed to the application) increasing performance.
    2) Is it the same Database? There maybe some logging facility active on your server.
    3) Are Indexes involved? They may slow down the insert. You then can insert into some table without indexes and do a "insert into XXXX select" afterwards.
    4) How do you insert the rows? There may be a problem when commit is done to often. But this should also reduce performance in your developer scenario.

    I will continue thinking about it ....

    Lars.
  • 2. Re: Data loads are slower on the server?
    800308 Newbie
    Currently Being Moderated
    Thanx Lars.

    1) I'll definately look into "network vs local" optimised JDBC drivers (and settings)... that might explain it. Thank you!

    2) It is the very same database and table in both instances, It's a "real" table (as apposed to session-temporary-table or whatever) which is (re)created in both instances at the beginning of each run. Logging is active on that database, but has not been activated on that table. More food for thought though, thanx.

    3) Only Informix's internal primary index is involved... it's faster to create index's & stat's in informix after the table is loaded with data.

    4) The rows are inserted in a "tight loop". It commits every 100 rows.... or else we'd run out of lock-lists before the load finished. Commiting every row didn't appear to impact performance... From experience it doesn't UNLESS logging is active on that table.

    I allready owe you four beers. Thanking you sir.

    Keith.
  • 3. Re: Data loads are slower on the server?
    807601 Newbie
    Currently Being Moderated
    Hello Keith,

    I just looked into the informix guide (http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.jdbc_pg.doc/jdbc32.htm). There seems to be some server side driver available.

    Where can I get that beer?

    Lars.
  • 4. Re: Data loads are slower on the server?
    800308 Newbie
    Currently Being Moderated
    My modem keeps throwing an DontPoorThatBeerIntoMeYouIdiotException... so maybe the next time you're in Australia.
  • 5. Re: Data loads are slower on the server?
    807601 Newbie
    Currently Being Moderated
    Hello Keith,

    So I presume it to be kind of brackish when I reach Australia. Keith please drink that beer for me! Skoal!

    Curiously wating for any solution you find,

    Lars.
  • 6. Re: Data loads are slower on the server?
    791266 Explorer
    Currently Being Moderated
    @Op. Sounds like your disk can't handle the load from both the application server and your database. What happens if you e.g. turn off all logging from the application server and other processes?
  • 7. Re: Data loads are slower on the server?
    800308 Newbie
    Currently Being Moderated
    kaj,
    Sounds like your disk can't handle the load from both the application server and your database.
    Yep, that's a possibility... Except... Ummm... I wouldn't have a clue where to start to "turn off logging"... and don't imagine that it'll make any difference because (presuming that this machine is our "standard" setup, and that our "standard" setup hasn't been changed from how I'm used to them being setup, certainly nobody has informed me, but they wouldn't, especially now we've got ITIL to ensure that nobody knowswhatthefucksgoingon) /home is mounted on an internal disk (for transfer speed) and /data is on a T3 disk array (for cost abatement... or come to think of it, it might even be on the SAN these days, see above mushroom syndrome, and I don't even know how to check).

    I'll eliminate some other possibilities first though

    Big thanks ;-) Keith.