Skip to main content

PHP ODBC Poor Insert Performance : Suggestions ?

  • March 28, 2014
  • 6 replies
  • 35 views
Richard_Uren
Forum|alt.badge.img
Hi,

I'm setting up Vertica 7 with PHP via ODBC and seeing very slow insert performance at
around 100 records per second. Would anyone have any suggestions as to how i can speed things up ?

Thanks.

Cheers
Richard

Hardware is Intel i7-4770 CPU @ 3.40GHz with 32Gb RAM and 2 x 240Gb SSD.

PHP 5.4.26
unixODBC.x86_64 2.2.14-12.el6_3
Centos 6.5
Vertica 7.0.1 Community Edition & client libraries.

/etc/odbc.ini
[Data Sources]
VerticaDSN = "Stats_Database"

[VerticaDSN]
Description = "Stats DB"
Driver = /opt/vertica/lib64/libverticaodbc.so.7.0.1
Database = stats
ServerName = localhost
UID = dbadmin
PWD = dbadmin
Port = 5433
Locale = en-US
DriverUnicodeType=1

[Driver]
Trce=off
Locale = en-US
DriverManagerEncoding=UTF-8
ODBCInstLib=/usr/lib64/libodbcinst.so
ErrorMessagesPath=/tmp
#LogLevel=6
#LogPath=/tmp
#LogNameSpace=

My basic php test script is at http://s3.amazonaws.com/hd-public/v2.php


    6 replies

    Chris_C
    Forum|alt.badge.img
    • Chris_C
    • Participating Frequently
    • March 28, 2014
    Hi Richard,

    Is the PHP server and Vertica hosted on the same server?  I'll try your script on one of my systems.
    Richard_Uren
    Forum|alt.badge.img
    • Richard_Uren
    • Author
    • Participating Frequently
    • March 28, 2014
    Hi Chris,

    Thanks for taking the time to look into the issue.
    Yes, hosted on the same server.

    Cheers
    Richard
    Chris_C
    Forum|alt.badge.img
    • Chris_C
    • Participating Frequently
    • March 29, 2014
    Ok, two things.  Your prepare statement is inside the FOR loop. It should not be there. Put it before the for loop and keep your execute statements inside the for loop. The benefit of preparing is that you prepare once and reuse the statement with multiple parameter sets. 

    Also, by default, Autocommit is on. Use odbc_autocommit($conn, FALSE) to disable autocommit.

    Let me know if that speeds things up!
    Richard_Uren
    Forum|alt.badge.img
    • Richard_Uren
    • Author
    • Participating Frequently
    • March 29, 2014
    Thanks Chris,

    I've noticed i was doing two inserts in each loop ! Gah.

    Your suggestions do make a difference.

    I've updated the script and ran it twice, once with autocommit on and autocommit off. With autocommit on i get about 220 inserts per second, with autocommit off i get about 290 inserts per second.

    Is that the best performance I can expect ?

    Updated script : http://hd-public.s3.amazonaws.com/v3.php

    Thanks

    Cheers
    Richard


    Chris_C
    Forum|alt.badge.img
    • Chris_C
    • Participating Frequently
    • March 31, 2014
    Sorry, I don't have any performance numbers on PHP inserts. You may want to try the PDO PHP library and see if there are any significant performance differences.
    Richard_Uren
    Forum|alt.badge.img
    • Richard_Uren
    • Author
    • Participating Frequently
    • April 1, 2014
    Thanks Chris - I'll look into PDO and see if I get any traction. Worst case I guess I can fire up Java inside PHP. We're streaming data into Vertica so the overhead isnt killer for us in that scenario if the performance pans out.

    Also : I tried the prepare outside the loop .. and it didn't work. There must be a bug somewhere using prepared statements with Vertica, ODBC and PHP. I've got a minimal test case here : 
    https://community.vertica.com/vertica/topics/php_insert_using_prepared_statement_works

    Seems some other people have the same issue as well.

    Does the code look OK ? Am I doing something wrong ?

    Thanks Chris,

    Cheers
    Richard
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement