Thread: 500 times slower
Hi!
I have the program which using LOBS with postgresql. When I connect to
local windows server or remote linux server everything is fine. But when
I connect to remote Windows server the function SQLDirectExec runs 500
times longer than normally. Problem concerns postgres on Windows with
version > 7.4.1 and also 8.0. 7.x runs under cygwin and 8.x natively.
Previously in the old cygwin which contains 7.4.1 postgres the
performance was ok.
I've included simple test case which shows the problem.
Karol
#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
void ODBCError(SQLRETURN rc,const char*where,bool ignoreError=false)
{
bool terminate=true;
const char*err;
switch (rc)
{
case SQL_SUCCESS: err="SQL_SUCCESS" ;terminate=false;break;
case SQL_SUCCESS_WITH_INFO:err="SQL_SUCCESS_WITH_INFO";terminate=false;break;
case SQL_NO_DATA :err="SQL_NO_DATA" ;terminate=false;break;
default:
err="ERROR";break;
}
if(!ignoreError)
if (terminate)
{
std::cerr<<where<<":"<<err<<std::endl;
exit(0);
}
}
bool ODBCExec(SQLHDBC hdbc,const char* stmt)
{
SQLHSTMT hstmt;
ODBCError(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt),"SQLAllocHandle-Stmt");
ODBCError(SQLExecDirect(hstmt,(SQLCHAR*)stmt,SQL_NTS),"SQLExecDirect",true);
ODBCError(SQLFreeHandle(SQL_HANDLE_STMT,hstmt),"SQLFreeHandle-Stmt");
return true;
}
bool ODBCTest()
{
SQLHENV henv;
ODBCError(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv),"SQLAllocHandle-Env");
ODBCError(SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3 ,NULL),"SQLSetEnvAttr");
SQLHDBC hdbc;
ODBCError(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc),"SQLAllocHandle-Conn");
const char*server="pi";
const char*user="legnica_e";
const char*passwd="syndis";
ODBCError(SQLConnect(hdbc,(SQLCHAR*)server,strlen(server),(SQLCHAR*)user,strlen(user),(SQLCHAR*)passwd,strlen(passwd)),"SQLConnect");
ODBCExec(hdbc,"drop table test");
ODBCExec(hdbc,"create table test(t text)");
ODBCExec(hdbc,"create table test(t text)");
ODBCExec(hdbc,"insert into test values ('0123456789')");
for (int x=0;x<20;++x)ODBCExec(hdbc,"update test set t=t||t");
SQLHSTMT hstmt;
ODBCError(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt),"SQLAllocHandle-Stmt");
const char*stmt="select t from test";
ULONGLONG t1,t2;
GetSystemTimeAsFileTime((FILETIME*)&t1);
ODBCError(SQLExecDirect(hstmt,(SQLCHAR*)stmt,SQL_NTS),"SQLExecDirect");
GetSystemTimeAsFileTime((FILETIME*)&t2);
t2-=t1;
std::cerr<<"SQLExecDirect time:"<<t2/10000<<"msec."<<std::endl;
SQLRETURN rc,rc2;
int maxsize=32760;
char*buf=new char[maxsize];
SQLLEN ind;
while ((rc=SQLFetch(hstmt))!=SQL_NO_DATA)
{
ODBCError(rc,"SQLFetch");
int count=0;
while((rc2=SQLGetData(hstmt,1,SQL_C_CHAR,buf,maxsize,&ind))!=SQL_NO_DATA)
{
ODBCError(rc2,"SQLGetData");
if ((ind>maxsize)||(ind==SQL_NO_TOTAL))
ind=maxsize;
count+=ind;
std::cerr<<'.';//<<"reading "<<ind<<" bytes of data"<<std::endl;
}
std::cerr<<std::endl;
ODBCError(rc2,"SQLGetData");
std::cerr<<"total "<<count<<" bytes of data"<<std::endl;
}
ODBCError(rc,"SQLFetch");
delete[] buf;
ODBCError(SQLFreeHandle(SQL_HANDLE_STMT,hstmt),"SQLFreeHandle-Stmt");
ODBCError(SQLDisconnect(hdbc),"SQLDisconnect");
ODBCError(SQLFreeHandle(SQL_HANDLE_DBC,hdbc),"SQLFreeHandle-Conn");
ODBCError(SQLFreeHandle(SQL_HANDLE_ENV,henv),"SQLFreeHandle-Env");
return 0;
}
int main(int argc,char**argv)
{
return ODBCTest();
}
The problem exists only between windows client and remote windows server. The other configurations (for example remote linux server) works ok. We are using stable version of psqlodbc driver.
Karol Szkudlarek wrote: > > The problem exists only between windows client and remote windows > server. The other configurations (for example remote linux server) > works ok. We are using stable version of psqlodbc driver. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings Hi! My collegue spent some time to dig the following case and it looks like Nagle algorithm and delayed ACKs related problem. In psqlodbc.h #define SOCK_BUFFER_SIZE 4096 I changed that value to 8192 and driver works fine for me. I am not sure why this change helps. Karol
> -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Karol Szkudlarek > Sent: 09 February 2005 11:48 > To: Karol Szkudlarek > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] 500 times slower > > Karol Szkudlarek wrote: > > > > The problem exists only between windows client and remote windows > > server. The other configurations (for example remote linux server) > > works ok. We are using stable version of psqlodbc driver. > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 7: don't forget to increase your free space map settings > > Hi! > > My collegue spent some time to dig the following case and it > looks like > Nagle algorithm and delayed ACKs related problem. > In psqlodbc.h > #define SOCK_BUFFER_SIZE 4096 > > I changed that value to 8192 and driver works fine for me. > I am not sure why this change helps. Err, no neither am I. Why do you think it's got something to do with Nagle/delayed ACKs? The only thing that instantly rings bells for me is that the max size of a text field is 8190 bytes at present (which really should be increased, if not removed altogether), which won't fit in the default buffer. But then, I wouldn't expect to see the performance drop you describe with a 4096 byte buffer, only one much smaller. Anyone else got any ideas? Regards, Dave
Dave Page wrote:
>
>
> Err, no neither am I. Why do you think it's got something to do with
> Nagle/delayed ACKs?
>
> The only thing that instantly rings bells for me is that the max size of
> a text field is 8190 bytes at present (which really should be increased,
> if not removed altogether), which won't fit in the default buffer. But
> then, I wouldn't expect to see the performance drop you describe with a
> 4096 byte buffer, only one much smaller.
>
> Anyone else got any ideas?
>
> Regards, Dave
>
Hi Dave!
In my test case I read LOB of 10 megabytes size.
So then in the function (with 4kB SOCK_BUFFER_SIZE):
SOCK_get_next_byte(SocketClass *self)
below line (socket.c):
self->buffer_filled_in = recv(self->socket, (char *) self->buffer_in,
self->buffer_size, 0);
runs in the following schema: 0msec, 200msec, 0msec, 200msec... etc.
So above times suggest delayed ACK timer (200msec).
Regards,
Karol
> -----Original Message----- > From: Karol Szkudlarek [mailto:karol@mikronika.com.pl] > Sent: 09 February 2005 14:06 > To: Dave Page > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] 500 times slower > > Hi Dave! > > In my test case I read LOB of 10 megabytes size. > So then in the function (with 4kB SOCK_BUFFER_SIZE): > SOCK_get_next_byte(SocketClass *self) > below line (socket.c): > > self->buffer_filled_in = recv(self->socket, (char *) self->buffer_in, > self->buffer_size, 0); > > runs in the following schema: 0msec, 200msec, 0msec, 200msec... etc. > So above times suggest delayed ACK timer (200msec). OK, that figures. I (and others I've asked) cannot see a reason not to increase the buffers size other than the extra memory that'll be used (which is trivial). Anyone disagree before I do so? Regards, Dave.
> -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Dave Page > Sent: 09 February 2005 14:53 > To: Karol Szkudlarek > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] 500 times slower > > > OK, that figures. I (and others I've asked) cannot see a reason not to > increase the buffers size other than the extra memory that'll be used > (which is trivial). Anyone disagree before I do so? SOCK_BUFFER_SIZE increased to 8192 in CVS. Regards, Dave