Query Read Performance

verbanverban Registered User
edited August 26 in Vertica Forum

I don't know why Vertica send data slowly!
(I mean when it processed query and want to send it to output)

for example when i run my query (on vertica machine, 1 node), after 2nd second it start to write to file but it tacks about 120 seconds for writing a 1GB file (10M records).
it means about 8MBps!
(it was same when i queried 1M records to file)

i was suspicious of disk limit for writing, i wrote the vsql output on ramdisk but it was same
and also i switched to receiving output remotely but it consumed only about 12% of 1Gbps interface!

how can i find what is the bottleneck?
how can i improve it?

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Can you show how you are writing output to a file?

  • marcothesanemarcothesane Employee, Registered User
    Rewrite your query like so:

    CREATE LOCAL TEMPORARY TABLE foo
    ON COMMIT PRESERVE ROWS AS

    KSAFE 0;

    And time that run.
    The difference between the timing you get here and the timing you got before comes from:
    1. "Send data to client" in the Vertica plan. A relatively small portion, the only part in Vertica.
    2. The network between Vertica and the client. This can be substantial.
    3. The data formatting effort in the SQL client: vsql, or whatever you use. This can be substantial, too
    4. Finally, writing results wherever you want them.

    This should explain it all.
    Marco
  • marcothesanemarcothesane Employee, Registered User
    Rewrite your query like so:

    CREATE LOCAL TEMPORARY TABLE foo
    ON COMMIT PRESERVE ROWS AS
    ( your full select)
    KSAFE 0;

    And time that run.
    The difference between the timing you get here and the timing you got before comes from:
    1. "Send data to client" in the Vertica plan. A relatively small portion, the only part in Vertica.
    2. The network between Vertica and the client. This can be substantial.
    3. The data formatting effort in the SQL client: vsql, or whatever you use. This can be substantial, too
    4. Finally, writing results wherever you want them.

    This should explain it all.
    Marco
  • verbanverban Registered User

    Thanks Marco,
    It's about 10 times faster...
    1M takes about 2.0 sec (10M is about 19sec)
    I checked disk write speed and it was good (about 200MB/s)

    But why storing data on disk is slow??
    (i store fetched record locally with vsql on vertica machine)

  • verbanverban Registered User

    @Jim_Knicely
    i want to fetched it remotely with jdbc,
    last week i found fetching data is very slow and network interface utilization is about 10%,
    then i decided to measure vertica max read first
    bcoz my disk write is good and vsql write 1GB file continuously on disk during 120 sec!!
    i'm not interested in working with file :)

  • verbanverban Registered User

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file