[fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL
Hi, I'm using SqlDB (via tiOPF framework) in our applications. Our software has a lot of db and non-db unit tests. When I run a section of our db unit tests via SqlDB + Firebird, they complete in about 6-7 seconds. When I run that exact same set of db unit tests via SqlDB + PostgreSQL, they take about 70-90 seconds to complete! Why is the connection to PostgreSQL so VERY slow? Is it the SqlDB+PostgreSQL components or is it something I need to tune in the PostgreSQL config settings? Sorry if this is a dumb question, by knowledge of PostgreSQL is very limited (a week to be exact). Regards, - Graeme - -- fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/ ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL
On Fri, 7 Aug 2009, Graeme Geldenhuys wrote: Hi, I'm using SqlDB (via tiOPF framework) in our applications. Our software has a lot of db and non-db unit tests. When I run a section of our db unit tests via SqlDB + Firebird, they complete in about 6-7 seconds. When I run that exact same set of db unit tests via SqlDB + PostgreSQL, they take about 70-90 seconds to complete! Why is the connection to PostgreSQL so VERY slow? Is it the SqlDB+PostgreSQL components or is it something I need to tune in the PostgreSQL config settings? Most likely the SqlDB+PostgreSQL components. Sorry if this is a dumb question, by knowledge of PostgreSQL is very limited (a week to be exact). My guess is that for each separate transaction component, a new connection is set up. So if you create/free a lot of transaction components, this could be the cause. Instead, try to use 1 transaction component which you commit/commitretaining/rollback. Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL
Michael Van Canneyt wrote: Most likely the SqlDB+PostgreSQL components. :-( I'll try and do other tests maybe via different components to see if there is really such a huge speed penalty on my PostgreSQL setup. I have heard that the default PostgreSQL installation is not tuned for performance - but those where slightly old posts I found on the internet. A magnitude of x15 slower is rather huge for a default install! Hence the reason I thought I would ask here about the SqlDB components. My guess is that for each separate transaction component, a new connection is set up. So if you create/free a lot of transaction My test suite only creates one DB connection for all db related tests. Test hierarchy is as follows. TestProject | + Non-db tests | + Suite1 | + Suite2 | + DB tests + Suite10 + Suite11 "DB test" is a test decorator that sets up the DB connection. Suite10 and Suite11 both run by using the same connection which was setup before. Create/Destroying a DB connection per test suite (Suite10, Suite11,...) is *very* slow, even under Firebird. I hope I understood your comments correctly? As you might guess, I use tiOPF, but I double checked the tiQuerySqlDBxx units and I don't see anything majorly different between the Firebird and PostgreSQL code. What I use is currently available in SubVersion (r1587). For others, tiOPF is available at: http://tiopf.sourceforge.net. Below is the last commit log I had to make to get SqlDB + PostgreSQL to work. r1587 | graemeg | 2009-08-07 14:41:29 +0200 (Fri, 07 Aug 2009) | 13 lines Changed paths: M /tiOPF2/Trunk/Options/tiQueryDataset.pas Disabled CheckPrepared in tiQueryDataset Prepared is automatically called for all TDataset descendants, so this should not really have any impact. The reason why I explicitly disabled it here is because of SqlDB & PostgreSQL. For some reason PostgreSQL raises an error "Unknown fieldtype for parameter <>" when prepare or CheckPrepared is called before the Parameter is retrieved or set. I'll investigate this further to see if this is a timing problem in tiQueryDataset or is the SqlDB code for PostgreSQL. If I don't do what I did above, then I get the errors as shown below. Any thoughts? As you can see from the error numbers (56, 57,...) I get errors on pretty much every sql statement. My sql is all in the normal format: SELECT field1, field2, field3 FROM tablename WHERE OID = :OID As I mentioned in the commit log, I'm not sure if this is an issue it SqlDB+PostgreSQL or in tiOPF SqlDB persistence layer? == 56) textrunner.SQL Database tests.TTestModuleFlyweightFactory.TestModuleFlyweightFactory_GetModuleException: EDatabaseError at $0813D89C : Unknown fieldtype for parameter "OID". 57) textrunner.SQL Database tests.TTestNextIDGenerator.TestGetNextID_1: EDatabaseError at $0813D89C : Unknown fieldtype for parameter "NEXT_GENERAL_ID". == Regards, - Graeme - -- fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/ ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL
Graeme Geldenhuys wrote: TestProject | + Non-db tests | + Suite1 | + Suite2 | + DB tests + Suite10 + Suite11 "DB test" is a test decorator that sets up the DB connection. Suite10 and Suite11 both run by using the same connection which was setup before. My test suite uses DUnit2 instead of FPCUnit, so the test decorators actually work as expected, and is executed only once for all tests under the decorator. Regards, - Graeme - -- fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/ ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL
On Fri, 7 Aug 2009, Graeme Geldenhuys wrote: Michael Van Canneyt wrote: Most likely the SqlDB+PostgreSQL components. :-( I'll try and do other tests maybe via different components to see if there is really such a huge speed penalty on my PostgreSQL setup. I have heard that the default PostgreSQL installation is not tuned for performance - but those where slightly old posts I found on the internet. A magnitude of x15 slower is rather huge for a default install! Hence the reason I thought I would ask here about the SqlDB components. My guess is that for each separate transaction component, a new connection is set up. So if you create/free a lot of transaction My test suite only creates one DB connection for all db related tests. Test hierarchy is as follows. The connection is not relevant. You can have 1 connection component and 15 transaction components. This will result in 15 (!) connections to the postgresql database, not 1. Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL
The usual way I deal with postgres - in java - is to have a connection pool. I believe this is more or less standard in pg for performance and it does work extremely well. I don't i'm afraid, have a pascal db connection pool. bd On Fri, 07 Aug 2009 10:42:03 +0200 Graeme Geldenhuys wrote: > Hi, > > I'm using SqlDB (via tiOPF framework) in our applications. Our > software has a lot of db and non-db unit tests. > > When I run a section of our db unit tests via SqlDB + Firebird, they > complete in about 6-7 seconds. When I run that exact same set of db > unit tests via SqlDB + PostgreSQL, they take about 70-90 seconds to > complete! > > Why is the connection to PostgreSQL so VERY slow? Is it the > SqlDB+PostgreSQL components or is it something I need to tune in the > PostgreSQL config settings? > > Sorry if this is a dumb question, by knowledge of PostgreSQL is very > limited (a week to be exact). > > > Regards, >- Graeme - > ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
