Обсуждение: odbc vs. libpq performance
Hi,
I have a simple program that inserts data into a single table (see details below). When I have the program connect to the DB via ODBC, performance is significantly slower compared to when I have it connect via libpq. I was able to achieve ~1000 transactions per second using libpq, but only ~4 transactions per second using the ODBC driver.
At first I thought that auto-commit was enabled in ODBC, but I went back and made sure to set auto-commit off and performance remained the same:
“SQLSetConnectAttr(handle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);”
So I’m wondering if I’m not setting the auto-commit property correctly, or if I’m doing something wrong with ODBC (perhaps not using the right settings), or if there is an issue in the PostgreSQL ODBC driver.
I searched the archives and found one possibly related issue: http://www.postgresql.org/message-id/f147cdfff1924e52926ef90d26971476@EXCH2013.mustinformatique.fr. However, I tried the solution they proposed there, but it didn’t improve my issue.
Any help is greatly appreciated! Thanks!
Alejandro
Important details:
· About my program
o Written in C/C++
o Compiled with GCC 4.4.7
o The program basically spawns a given number of threads, each one establishes its own connection to the DB and then performs 100 transactions
o Each transaction simply consists of performing 25 inserts into a single table
o The program uses prepared statements
o The program can connect via ODBC or via libpq
o Both the program and the DB run on the same system, but connect via TCP
· PostgreSQL version: “PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit”
· PostgreSQL installation: followed the YUM installation instructions for Red Hat here: https://wiki.postgresql.org/wiki/YUM_Installation
· No important changes to postgresql.conf file
· OS:
o Red Hat 6.3
o Linux 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
· Connection:
o Using unixODBC 2.2.14
o PostgreSQL ODBC driver: 09.03.0300
o My DSN configuration:
§ Description = My test DB
§ Driver = <path to PostgreSQL ODBC driver>
§ Trace = No
§ TraceFile =
§ Servername = localhost
§ Database = mytestdb
§ Port = 5432
§ UseServerSidePrepare = 1
§ ReadOnly = No
§ RowVersioning = No
§ ShowSystemTables = No
§ ShowOidColumn = No
§ FakeOidIndex = No
§ ConnSettings =
Hi,
Sorry to resend, just hoping someone could take a look and let me know if this is an issue with the PostgreSQL ODBC driver.
Thanks!
Alejandro
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Vilches, Alejandro
Sent: Wednesday, April 1, 2015 11:51 AM
To: 'pgsql-odbc@postgresql.org'
Subject: [ODBC] odbc vs. libpq performance
Hi,
I have a simple program that inserts data into a single table (see details below). When I have the program connect to the DB via ODBC, performance is significantly slower compared to when I have it connect via libpq. I was able to achieve ~1000 transactions per second using libpq, but only ~4 transactions per second using the ODBC driver.
At first I thought that auto-commit was enabled in ODBC, but I went back and made sure to set auto-commit off and performance remained the same:
“SQLSetConnectAttr(handle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);”
So I’m wondering if I’m not setting the auto-commit property correctly, or if I’m doing something wrong with ODBC (perhaps not using the right settings), or if there is an issue in the PostgreSQL ODBC driver.
I searched the archives and found one possibly related issue: http://www.postgresql.org/message-id/f147cdfff1924e52926ef90d26971476@EXCH2013.mustinformatique.fr. However, I tried the solution they proposed there, but it didn’t improve my issue.
Any help is greatly appreciated! Thanks!
Alejandro
Important details:
· About my program
o Written in C/C++
o Compiled with GCC 4.4.7
o The program basically spawns a given number of threads, each one establishes its own connection to the DB and then performs 100 transactions
o Each transaction simply consists of performing 25 inserts into a single table
o The program uses prepared statements
o The program can connect via ODBC or via libpq
o Both the program and the DB run on the same system, but connect via TCP
· PostgreSQL version: “PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit”
· PostgreSQL installation: followed the YUM installation instructions for Red Hat here: https://wiki.postgresql.org/wiki/YUM_Installation
· No important changes to postgresql.conf file
· OS:
o Red Hat 6.3
o Linux 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
· Connection:
o Using unixODBC 2.2.14
o PostgreSQL ODBC driver: 09.03.0300
o My DSN configuration:
§ Description = My test DB
§ Driver = <path to PostgreSQL ODBC driver>
§ Trace = No
§ TraceFile =
§ Servername = localhost
§ Database = mytestdb
§ Port = 5432
§ UseServerSidePrepare = 1
§ ReadOnly = No
§ RowVersioning = No
§ ShowSystemTables = No
§ ShowOidColumn = No
§ FakeOidIndex = No
§ ConnSettings =
On 04/16/2015 03:30 AM, Vilches, Alejandro wrote: > I have a simple program that inserts data into a single table (see details below). When I have the program connect tothe DB via ODBC, performance is significantly slower compared to when I have it connect via libpq. I was able to achieve~1000 transactions per second using libpq, but only ~4 transactions per second using the ODBC driver. I'd suggest setting log_min_duration_statement = 0 in the server's config file. Then repeat the test and look at the log file to see where the time is spent. If that gives no clue, I'm happy to take a look, if you can reproduce it with a small test program and post it to a list. - Heikki
Hi Heikki, Sorry for taking so long to reply, I was away for a while and came back just recently. So, before addressing your questions, I discovered something interesting: when connecting via ODBC, transactions are beingcommitted automatically (even though I'm setting SQL_ATTR_AUTOCOMMIT to SQL_AUTOCOMMIT_OFF). This would explain thepoor performance I've been seeing. To address your questions: I'm attaching my little test program connecting via ODBC. To run it you'll need to create the table (which is pretty simpleas you'll see from the insert statement). You'll also need to set the connection parameters. Finally, you can playwith the number of threads to try to get different performance results. To compile you'll need "-lodbc -lpthread -std=c++0x". So, I'm wondering: am I just setting the auto commit parameter incorrectly or is it possible that there is a bug (eitherin unixODBC or the PostgreSQL ODBC driver)? I also ran my tests with "log_min_duration_statement = 0" as you suggested. When connecting via ODBC I do see that eachinsert statement is taking longer (compared to when I connect via libpq). Let me know if you have any questions or if there is any other information I could provide. Thanks! Alejandro -----Original Message----- From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki Linnakangas Sent: Thursday, April 16, 2015 12:15 AM To: Vilches, Alejandro; 'pgsql-odbc@postgresql.org' Subject: Re: [ODBC] odbc vs. libpq performance On 04/16/2015 03:30 AM, Vilches, Alejandro wrote: > I have a simple program that inserts data into a single table (see details below). When I have the program connect tothe DB via ODBC, performance is significantly slower compared to when I have it connect via libpq. I was able to achieve~1000 transactions per second using libpq, but only ~4 transactions per second using the ODBC driver. I'd suggest setting log_min_duration_statement = 0 in the server's config file. Then repeat the test and look at the logfile to see where the time is spent. If that gives no clue, I'm happy to take a look, if you can reproduce it with a smalltest program and post it to a list. - Heikki
Вложения
I've continued looking into this and I discovered something: I figured out how to successfully set the SQL_ATTR_AUTOCOMMITattribute to off. If you set it prior to establishing the connection, this setting is ignored and itdefaults to on. If you set it after establishing the connection, then it does recognize the setting and it works as specified. The ODBC documentation specifies that this attribute can be set either before or after establishing the connection. If it'snot accepting the setting before establishing the connection, is this an issue in the PostgreSQL ODBC driver? -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Vilches, Alejandro Sent: Tuesday, July 14, 2015 3:20 PM To: hlinnaka@iki.fi; 'pgsql-odbc@postgresql.org' Subject: Re: [ODBC] odbc vs. libpq performance Hi Heikki, Sorry for taking so long to reply, I was away for a while and came back just recently. So, before addressing your questions, I discovered something interesting: when connecting via ODBC, transactions are beingcommitted automatically (even though I'm setting SQL_ATTR_AUTOCOMMIT to SQL_AUTOCOMMIT_OFF). This would explain thepoor performance I've been seeing. To address your questions: I'm attaching my little test program connecting via ODBC. To run it you'll need to create the table (which is pretty simpleas you'll see from the insert statement). You'll also need to set the connection parameters. Finally, you can playwith the number of threads to try to get different performance results. To compile you'll need "-lodbc -lpthread -std=c++0x". So, I'm wondering: am I just setting the auto commit parameter incorrectly or is it possible that there is a bug (eitherin unixODBC or the PostgreSQL ODBC driver)? I also ran my tests with "log_min_duration_statement = 0" as you suggested. When connecting via ODBC I do see that eachinsert statement is taking longer (compared to when I connect via libpq). Let me know if you have any questions or if there is any other information I could provide. Thanks! Alejandro -----Original Message----- From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki Linnakangas Sent: Thursday, April 16, 2015 12:15 AM To: Vilches, Alejandro; 'pgsql-odbc@postgresql.org' Subject: Re: [ODBC] odbc vs. libpq performance On 04/16/2015 03:30 AM, Vilches, Alejandro wrote: > I have a simple program that inserts data into a single table (see details below). When I have the program connect tothe DB via ODBC, performance is significantly slower compared to when I have it connect via libpq. I was able to achieve~1000 transactions per second using libpq, but only ~4 transactions per second using the ODBC driver. I'd suggest setting log_min_duration_statement = 0 in the server's config file. Then repeat the test and look at the logfile to see where the time is spent. If that gives no clue, I'm happy to take a look, if you can reproduce it with a smalltest program and post it to a list. - Heikki
On 07/31/2015 02:33 AM, Vilches, Alejandro wrote: > I've continued looking into this and I discovered something: I > figured out how to successfully set the SQL_ATTR_AUTOCOMMIT attribute > to off. If you set it prior to establishing the connection, this > setting is ignored and it defaults to on. If you set it after > establishing the connection, then it does recognize the setting and > it works as specified. > > The ODBC documentation specifies that this attribute can be set > either before or after establishing the connection. If it's not > accepting the setting before establishing the connection, is this an > issue in the PostgreSQL ODBC driver? A-ha! Yes, that's a bug in the driver. In fact, it was fixed back in January: http://www.postgresql.org/message-id/BY1PR0401MB143087AD121D0251C00E5045A85B0@BY1PR0401MB1430.namprd04.prod.outlook.com That fix will be included in the next release. - Heikki
(Sorry, didn't reply to the mailing list initially.) Oh, great! Thanks for letting me know! Now, even when setting auto commit to off, performance is still significantly lower (compared to libpq). For example, runningthe sample program I provided earlier on my machine (and setting auto commit to off), I'm a getting ~100 transactionsper second. I did see an improvement compared to before (when I wasn't able to set auto commit to off) as Iwas only getting ~4 transactions per second. However, if I change the program to use libpq, I get ~1400 transactions persecond. I was expecting to see better performance, but the difference shouldn't be too significant, right? What performancedifference should I expect when using ODBC vs libpq? Thanks! Alejandro -----Original Message----- From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki Linnakangas Sent: Saturday, August 1, 2015 11:07 AM To: Vilches, Alejandro; 'pgsql-odbc@postgresql.org' Subject: Re: [ODBC] odbc vs. libpq performance On 07/31/2015 02:33 AM, Vilches, Alejandro wrote: > I've continued looking into this and I discovered something: I figured > out how to successfully set the SQL_ATTR_AUTOCOMMIT attribute to off. > If you set it prior to establishing the connection, this setting is > ignored and it defaults to on. If you set it after establishing the > connection, then it does recognize the setting and it works as > specified. > > The ODBC documentation specifies that this attribute can be set either > before or after establishing the connection. If it's not accepting > the setting before establishing the connection, is this an issue in > the PostgreSQL ODBC driver? A-ha! Yes, that's a bug in the driver. In fact, it was fixed back in January: http://www.postgresql.org/message-id/BY1PR0401MB143087AD121D0251C00E5045A85B0@BY1PR0401MB1430.namprd04.prod.outlook.com That fix will be included in the next release. - Heikki