Hitachi Vantara Pentaho Community Forums
Results 1 to 15 of 15

Thread: Weird performance Issue

  1. #1

    Default Weird performance Issue

    Hi all.
    I've been running Kettle 2.4.0 ETL jobs from about 3 or 4 months, las week I set up a new server and nightly run now takes 40 or 50 minutes more.

    My old server (development) is a P4 3.0 GHz, Linux RHEL 4, Java 6, 80 GB IDE Disk , 2.5 GB RAM, PostgreSQL 8.2.3

    My new server (production) is P4 HT 3.2 GHz, Debian 4.0r1, Java 5, 80 GB SATA Disk, 2 GB RAM, PostgreSQL 8.2.4

    I have made several test with hdparm, bonnie++ and postgres. The new configuration is 4 or 5 times faster than the old one. But nightly run takes longer!!!

    It's really weird because both server ara running the same jobs and transformations with the same configuration.

    What I missed? I have spent 4 days thinking it was a Postgres issue or disk issue but the new server is really faster than the old one.

    I will appreciate any help.

    Regards
    Agustin

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    500MB less physical memory, the difference between Java 5/6 ... maybe something else, do you get any error in the system log of the system.

    Maybe you just hit a threshold on your data size e.g.

    These kind of things are very hard to diagnose off-site.

    Regards,
    Sven

  3. #3

    Default Maybe it's about memory

    Because I have tunned JBoss and Postgres to use all the memory, there is only 300MB free memory.
    I don't have any error in log messages and develpment and production are running the same jobs and etls with the same data input.
    I will run job by job and I'll compare to find where is the bottleneck.
    Regards
    Agustin

  4. #4

    Default I'm testing with no success

    I have found the jobs bottleneck are in insert steps.
    I have a text file input step which puts data in a table output step.
    In the old server it takes 13 seconds and in the new server it takes 40 seconds.
    Monitoring the disk I see a lot of disk access.
    How can I test if it's waiting to Postgres or if it's waiting to SO while reading the file?

  5. #5

    Default There isn't a memory problem

    I have stop jboss and I have obtained the same performance.
    When running there are a lot of disk i/o access.
    Doing dstat I see a lot of 1000k to 1200k writes.
    But I don't know how to find if this is a Text File Input problem or Table Output.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    As a hunch I would blame it on the new version of postgres (if the kettle version is the same). Any chance of trying it with the postgres version of the other server.

    Regards,
    Sven

  7. #7

    Default I have had Postgres 8.1.9 before I updated to Postgres 8.2.4

    Before I have complain about performance here, I have spent 4 days trying to improve hardware, SO and Postgres.
    With Postgres 8.1.9 I have experience worst performance, then I decided to upgrade to Postgres 8.2.4.
    I have tested disks, SO and postgres. It works really fine.
    How can I mesure rows/sec in spoon?
    The transformation is waiting is Table Output step and I see a lot of i/o!!!
    Table output is doing row by row insert with the prepared statment?
    Maybe this is the cause of large i/o operations.

  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It depends on how you set it up. In a typical operation, Table Output writes rows in batches of thousands of rows to the database.
    If your Table Output step is waiting and you are not consuming all your CPU in the transformation, chances are very high that indeed you have a I/O problem.
    Remember that this can also be the network. Check that traffic doesn't go over a slow uplink, VPN or something like that.

    As far as PGSQL is concerned, verify that your setup is the same as the old one. See to it that you are not swapping.
    Also, if you give all but 300MB of RAM to JBoss/PGSQL, you will not get the best performance. Leave more memory for the operating system. The operating system benefits a great deal by caching frequently used files, especially things like transaction logs etc.

    As usual, YMMV :-)

    Just my €0.02,

    Matt

  9. #9

    Default Has the same setup of development

    I leave more memory to the SO, and stop JBoss with the same performance.
    I didn't change the transformation setup, it's the same!!!
    In table output I have Commit size = 0 and checked use batch update for inserts but I have no why is checked!!!

    I'm running the transformation locally, there aren't network traffic.

    Thank you very much for you help

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    In table output I have Commit size = 0 and checked use batch update for inserts but I have no why is checked!!!
    Commit size == 0 is mentioned A LOT on this forum AND in the FAQ as a big "NO, DON'T DO IT!!" message.

    Set it to 2000 or something and try again.
    No wonder your database is killing itself.

    Let us know how many times faster your transformation runs.

    Matt

  11. #11

    Thumbs up Yes I know it

    But the same ETL in an older machine performs better.
    I think this is SATA disk issue!!!
    By the way I have put the corresponding commit size to each transformation and know the hole process takes 13 minutes. 8 times faster.

    I will do the same in the development server and compare again.

    Tomorrow I'll give you the results.

    Thank you very much for your help.

    Agustin

  12. #12
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    8 times faster.
    Well, there's a nice reference case for everyone else that stumbles onto this thread.
    Mind you, there is a reason why we made the default commit size a non-zero number (100 I think).

    As for PGSQL, perhaps you turned on logging or something? That might explain a few things. Check postgresql.conf to make sure.

    Matt

  13. #13
    Join Date
    Jul 2007
    Posts
    8

    Default

    On last thing that comes to my mind is the file system - especially the journalling. Is it possible that the newer machine uses ext3 with strict journaling and the other one uses say ext2.

    Regards

    Felix

  14. #14

    Default Last result

    I have restore the last production backup to the old server, and with the last changes (commit size > 0) there isn't a better performance.

    Both file systems (new server and old server) have ext3 defaults. No logging in postgresql.
    So I can't explain the performance difference in both server with the same configuration. I think is a combination of hardware (SATA vs IDE) and little software configurations and versions.

  15. #15
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    That unfortunately hints at a regression problem in PostgreSQL.
    That being said, I don't think a lot of folks are running with automatic commit turned on. ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.