PHP ODBC Poor Insert Performance : Suggestions ?

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


Comments

  • Hi Richard,

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

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

    Cheers
    Richard
  • 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!
  • 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


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

Leave a Comment

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