| Subject | Re: Queries done through ISQL must faster than in production |
|---|---|
| Author | peterson77 |
| Post date | 2010-02-08T15:42:25Z |
Mark and Helen,
Thanks for your replies. The query returns very few rows, so I do not believe that the size of the result set is the reason for such a large performance difference (The query time using JDBC is 180 seconds while the query time in ISQL is 5 seconds). Because the size of the fb_inet_server process created by isql (49meg) is so much larger than the size of each fb-inet_server process created through JDBC (32meg), my suspicion has been that this performance difference is related to memory.
To try and figure this out, I need to understand what goes into the memory footprint of each fb_inet_server process. There are a total of 8 fb_inet_server processes running in a connection pool. When I run 'top' on the server, each fb_inet_server process created via Jaybird takes around 32meg of VIRT memory.
1) The buffers are set to 2048 (gfix -buffers 2048). This means each connection in the pool should take 8 meg (2048 x 4k page = 8meg).
2) The TempCacheLimit (formerly SortMemCacheLimit) is not set in firebird.conf, so the default should be 8meg. This means each connection should take an additional 8meg; although perhaps this is not allocated until needed.
3) Perhaps the Lock manager can be configured??
I am not specifying any connection parameters in the Jaybird connection string. So can anyone tell me what parameters control the baseline memory footprint of an fb_inet_server process and why the memory would be different between the connections created through ISQL and the connections created through JDBC? What am I missing?
The application using Jaybird 2.1.6/Firebird 2.1 classic in a
tomcat application on a 6gig RHEL 5 box.
- Steve
Thanks for your replies. The query returns very few rows, so I do not believe that the size of the result set is the reason for such a large performance difference (The query time using JDBC is 180 seconds while the query time in ISQL is 5 seconds). Because the size of the fb_inet_server process created by isql (49meg) is so much larger than the size of each fb-inet_server process created through JDBC (32meg), my suspicion has been that this performance difference is related to memory.
To try and figure this out, I need to understand what goes into the memory footprint of each fb_inet_server process. There are a total of 8 fb_inet_server processes running in a connection pool. When I run 'top' on the server, each fb_inet_server process created via Jaybird takes around 32meg of VIRT memory.
1) The buffers are set to 2048 (gfix -buffers 2048). This means each connection in the pool should take 8 meg (2048 x 4k page = 8meg).
2) The TempCacheLimit (formerly SortMemCacheLimit) is not set in firebird.conf, so the default should be 8meg. This means each connection should take an additional 8meg; although perhaps this is not allocated until needed.
3) Perhaps the Lock manager can be configured??
I am not specifying any connection parameters in the Jaybird connection string. So can anyone tell me what parameters control the baseline memory footprint of an fb_inet_server process and why the memory would be different between the connections created through ISQL and the connections created through JDBC? What am I missing?
The application using Jaybird 2.1.6/Firebird 2.1 classic in a
tomcat application on a 6gig RHEL 5 box.
- Steve
--- In firebird-support@yahoogroups.com, "Mark Rotteveel" <Avalanche1979@...> wrote:
>
> > The version of Firebird is 2.1.2. The application environment is Java
> > under Tomcat w/Jaybird 2.1.6 JDBC driver. The server has 6Gig of memory with
> > dual processors. Both ISQL and the Jaybird connections are done through
> > localhost. The database size is about 23gig, page size is 4k.
>
> By default JDBC drivers fetch a lot of rows into the resultset which 1) takes more memory and 2) takes more time. Have you tried setting the fetchsize of the JDBC statement to a small number (eg 10).
> --
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
>