PHP ODBC Poor Insert Performance : Suggestions ?


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 ?



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.

[Data Sources]
VerticaDSN = "Stats_Database"

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

Locale = en-US

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


  • Options
    Hi Richard,

    Is the PHP server and Vertica hosted on the same server?  I'll try your script on one of my systems.
  • Options
    Hi Chris,

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

  • Options
    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!
  • Options
    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



  • Options
    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.
  • Options
    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 : 

    Seems some other people have the same issue as well.

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

    Thanks Chris,


Leave a Comment

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