PHP ODBC Poor Insert Performance : Suggestions ?

Richard_Uren

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

Chris_C

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

Hi Chris,

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

Cheers
Richard

Chris_C

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

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

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

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