Re: ODP: Re: [firebird-support] About firebird performance

2015-09-06 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support] 

Hi Karol,

I know it from our experience.
The explanation that big page cache requires extra resources for 
synchronization, and experiments shows that combination of 10k buffers 
and enabled file cache gives the best performance for SuperServer 2.5.


However, in 3.0 situation is improved, and 100k is normal value (but 
still requires cache heating).


Regards,
Alexey Kovyazin
IBSurgeon







Hi,

For superserver this should be as high as possible.  Why you 
recommended 1 value? I always set it to highest possible value in 
current environment compared to size of database. And i always disable 
system cache


Regards,
Karol Bieniaszewski


 Oryginalna wiadomość 
Od: "Alexey Kovyazin a...@ib-aid.com [firebird-support]" 


Data: 05.09.2015 19:06 (GMT+01:00)
Do: firebird-support@yahoogroups.com
Temat: Re: [firebird-support] About firebird performance

Hi Hector,

I discovered something else quite interesting:

I realized both IB and FB dbs have pagebuffers set to 100.000. I 
changed FB to 0 and now it works like I expected!!..Same query now 
completes on FB on 50 sec. It seems this is the problem although I 
don't understand why.


100 000 is too high for Firebird SuperServer, it should be set to 1.


You can use optimized Firebird configuration files
http://ib-aid.com/en/optimized-firebird-configuration/

Regards,
Alexey Kovyazin
IBSurgeon






Nevertheless, I'll try on a physical machine as you and Carlos 
suggest and see how it behaves.




2015-09-05 15:47 GMT+02:00 Alexey Kovyazin a...@ib-aid.com 
<mailto:a...@ib-aid.com> [firebird-support] 
:


Hi Hector,

Thanks for statistics, it clarifies situation.

There is 304880 data pages, each is 4kb, so Firebird need to read
1.1Gb of data to count records.
Count uses NATURAL scan, so it's a simple set of disk reads, CPU
is not involved.

Regular SATA3 disk should read 30-70Mb per second (I assume
database is not fragmented, since you told it was freshly
restored), it gives us estimation from 15 to 30 seconds.

So, I think there is a disk IO problem.
Try it on another computer (better not virtual).

Regards,
Alexey






He again Alexey,

As I suspected, my problem is not caused by GC:

gstat over the table reported this:

TABLE1 (503)
Primary pointer page: 9374, Index root page: 9375
Average record length: 317.63, total records: 3261775
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 304880, data page slots: 304880, average fill: 90%

Also, as you suggested ran the query twice a got exaclty the
same times: 51minutes to complete!!!

I must have something obvious which is killing firebird
performance, but cannot guess it.

Any help would be appretiated.


2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
[firebird-support] :

Hi Hector,

So you finally decided to try Firebird - that's good.

I think that you are facing garbage collection problem - if
there are a lot of record versions in the TABLE1 which are
not interested to any transaction, your query will force
garbage collection, and it can take some time.

How to check? run SELECT count(*) twice and compare
execution times, and other query statistics (reads/writes).

Also, run gstat -a -r  and check information for
TABLE1 - VERSIONS and MAX VERSIONS, if there are big numbers
there, problem is certainly related with record versions,
with the initial cause in wrong transactions management -
i.e., long-running write transactions or forced rollbacks.


Regards,
Alexey Kovyazin
IBSurgeon







I'm doing some basic tests with Firebird and I'm facing
something I
cannot understand (I'm a newbie to Firebird which has
worked with
IB7.5 for years, so I apologize if I ask something obvious):

- I have a linux server with firebird 2.5.4 Superserver:
multi-core (4), 3GB RAM
- I placed a "huge" database: 1000 tables, 9GB
- When I issue a query like this "select count(*) from
TABLE1" it
takes very long time to complete (>30 min).
- TABLE 1 has 3 million records

As I told you, I'm quite used to IB7.5, but I guess this is
not normal
at all...probably I'm missing something but I couldn't find any
document which gives me an idea of what I'm doing wrong.

With same hardware an IB7.5 same query took 40 sec to
complete (first
time issued, no cache).

Could anyone help?

-- 
--

Planatec Software S.L. ** <http://www.planatec.es>
telf: +34 964 340 560  ** fax:
+34 961 130 921 





--
Planatec Software S.L.

ODP: Re: [firebird-support] About firebird performance

2015-09-06 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 


Hi,
For superserver this should be as high as possible.  Why you recommended 1 
value? I always set it to highest possible value in current environment 
compared to size of database. And i always disable system cache
Regards,Karol Bieniaszewski

 Oryginalna wiadomość 
Od: "Alexey Kovyazin a...@ib-aid.com [firebird-support]" 
 
Data: 05.09.2015  19:06  (GMT+01:00) 
Do: firebird-support@yahoogroups.com 
Temat: Re: [firebird-support] About firebird performance 


 



  



  
  
  
  
  
Hi Hector,


  
  
  
I discovered something else quite
  interesting:
  

  
  I realized both IB and FB dbs have pagebuffers set to
100.000. I changed FB to 0 and now it works like I
expected!!..Same query now completes on FB on 50 sec. It
seems this is the problem although I don't understand
why.

  

  



100 000 is too high for Firebird SuperServer, it should be set to
1. 





You can use optimized Firebird configuration files

http://ib-aid.com/en/optimized-firebird-configuration/



Regards,

Alexey Kovyazin

IBSurgeon










  

  

  

  
  Nevertheless, I'll try on a physical machine as you
and Carlos suggest and see how it behaves.
  

  
  

  



  2015-09-05 15:47 GMT+02:00 Alexey
Kovyazin a...@ib-aid.com
[firebird-support] :


  
 

  

   
  Hi Hector,



Thanks for statistics, it clarifies
situation.



There is 304880 data pages, each is 4kb, so
Firebird need to read 1.1Gb of data to count
records.

Count uses NATURAL scan, so it's a simple
set of disk reads, CPU is not involved.



Regular SATA3 disk should read 30-70Mb per
second (I assume database is not fragmented,
since you told it was freshly restored), it
gives us estimation from 15 to 30 seconds.



So, I think there is a disk IO problem.

Try it on another computer (better not
virtual).



Regards,

Alexey











  
    

  He again Alexey,



As I suspected, my problem is not
  caused by GC:



gstat over the table reported this:




  TABLE1 (503)
      Primary pointer page: 9374,
Index root page: 9375
      Average record length:
317.63, total records: 3261775
      Average version length: 0.00,
total versions: 0, max versions: 0
      Data pages: 304880, data page
slots: 304880, average fill: 90%




Also, as you suggested ran the
  query twice a got exaclty the same
  times: 51minutes to complete!!!



I must have something obvious which
  is killing firebird performance, but
  cannot guess it.

Re: [firebird-support] About firebird performance

2015-09-06 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Thanks Vlad...you found the problem!! I set  FileSystemCacheThreshold to
50 and it works like a charm...I got chache for the db and works really
fast...even in the VM.

Thanks!!


2015-09-05 23:36 GMT+02:00 hv...@users.sourceforge.net [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> > ---In firebird-support@yahoogroups.com,  wrote :
> >
> > I discovered something else quite interesting:
> >
> > I realized both IB and FB dbs have pagebuffers set to 100.000. I changed
> FB to 0 and now it works like I
> > expected!!..Same query now completes on FB on 50 sec. It seems this is
> the problem although I don't
> > understand why.
>
>   Carefully read firebird.conf. Especially FileSystemCacheThreshold
> setting.
>
>   You've set page cache for database above default value of
> FileSystemCacheThreshold (100K > 64K) thus disable file system caching for
> that database.
>
>   If you really need both page cache of 100K pages and file system cache,
> raise FileSystemCacheThreshold value to be more than 100K
>
> Regards,
> Vlad
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-06 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Thanks Alexey!! As you have read, setting pagebuffers to 0 on the database
solved the problem and now it works as I expected.


2015-09-05 19:06 GMT+02:00 Alexey Kovyazin a...@ib-aid.com [firebird-support]
:

>
>
> Hi Hector,
>
>
> I discovered something else quite interesting:
>
> I realized both IB and FB dbs have pagebuffers set to 100.000. I changed
> FB to 0 and now it works like I expected!!..Same query now completes on FB
> on 50 sec. It seems this is the problem although I don't understand why.
>
>
> 100 000 is too high for Firebird SuperServer, it should be set to 1.
>
>
> You can use optimized Firebird configuration files
> http://ib-aid.com/en/optimized-firebird-configuration/
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
> Nevertheless, I'll try on a physical machine as you and Carlos suggest and
> see how it behaves.
>
>
>
> 2015-09-05 15:47 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
> [firebird-support] < 
> firebird-support@yahoogroups.com>:
>
>>
>>
>> Hi Hector,
>>
>> Thanks for statistics, it clarifies situation.
>>
>> There is 304880 data pages, each is 4kb, so Firebird need to read 1.1Gb
>> of data to count records.
>> Count uses NATURAL scan, so it's a simple set of disk reads, CPU is not
>> involved.
>>
>> Regular SATA3 disk should read 30-70Mb per second (I assume database is
>> not fragmented, since you told it was freshly restored), it gives us
>> estimation from 15 to 30 seconds.
>>
>> So, I think there is a disk IO problem.
>> Try it on another computer (better not virtual).
>>
>> Regards,
>> Alexey
>>
>>
>>
>>
>>
>>
>> He again Alexey,
>>
>> As I suspected, my problem is not caused by GC:
>>
>> gstat over the table reported this:
>>
>> TABLE1 (503)
>> Primary pointer page: 9374, Index root page: 9375
>> Average record length: 317.63, total records: 3261775
>> Average version length: 0.00, total versions: 0, max versions: 0
>> Data pages: 304880, data page slots: 304880, average fill: 90%
>>
>> Also, as you suggested ran the query twice a got exaclty the same times:
>> 51minutes to complete!!!
>>
>> I must have something obvious which is killing firebird performance, but
>> cannot guess it.
>>
>> Any help would be appretiated.
>>
>>
>> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
>> [firebird-support] < 
>> firebird-support@yahoogroups.com>:
>>
>>>
>>>
>>> Hi Hector,
>>>
>>> So you finally decided to try Firebird - that's good.
>>>
>>> I think that you are facing garbage collection problem - if there are a
>>> lot of record versions in the TABLE1 which are not interested to any
>>> transaction, your query will force garbage collection, and it can take some
>>> time.
>>>
>>> How to check? run SELECT count(*) twice and compare execution times, and
>>> other query statistics (reads/writes).
>>>
>>> Also, run gstat -a -r  and check information for TABLE1 -
>>> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
>>> certainly related with record versions, with the initial cause in wrong
>>> transactions management - i.e., long-running write transactions or forced
>>> rollbacks.
>>>
>>>
>>> Regards,
>>> Alexey Kovyazin
>>> IBSurgeon
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> I'm doing some basic tests with Firebird and I'm facing something I
>>> cannot understand (I'm a newbie to Firebird which has worked with
>>> IB7.5 for years, so I apologize if I ask something obvious):
>>>
>>> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
>>> 3GB RAM
>>> - I placed a "huge" database: 1000 tables, 9GB
>>> - When I issue a query like this "select count(*) from TABLE1" it
>>> takes very long time to complete (>30 min).
>>> - TABLE 1 has 3 million records
>>>
>>> As I told you, I'm quite used to IB7.5, but I guess this is not normal
>>> at all...probably I'm missing something but I couldn't find any
>>> document which gives me an idea of what I'm doing wrong.
>>>
>>> With same hardware an IB7.5 same query took 40 sec to complete (first
>>> time issued, no cache).
>>>
>>> Could anyone help?
>>>
>>> --
>>> --
>>> Planatec Software S.L. ** 
>>>  
>>> telf: +34 964 340 560 <%2B34%20964%20340%20560> ** fax: +34 961 130 921
>>> <%2B34%20961%20130%20921>
>>>
>>>
>>>
>>
>> --
>> Planatec Software S.L. ** < 
>> http://www.planatec.es>
>> telf: +34 964 340 560 <%2B34%20964%20340%20560> ** fax: +34 961 130 921
>> <%2B34%20961%20130%20921>
>>
>>
>>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-06 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Thanks for your reply Ann...please see below your comments:

> Very odd.   Could you collect the performance stats for the query on the two 
> systems? >Specifically, I'd like to know how much physical I/O each did - 
> that's reported as Reads >and Writes.  Fetches and Marks don't matter here.

This is what I did:

#  /opt/firebird/bin/gfix -buffers 10 -user SYSDBA -password
XX /sis/arodat/test.fdb
# /etc/ini.d/firebird restart

1) select count(*) from TABLE1 with pagebuffers=10 on the database:
mon$page_reads=305093
mon$page_writes=0
execution time=51min

#  /opt/firebird/bin/gfix -buffers 0 -user SYSDBA -password XX
/sis/arodat/test.fdb
# /etc/ini.d/firebird restart

2) select count(*) from TABLE1 with pagebuffers=0 on the database:
mon$page_reads=305187
mon$page_writes=0
execution time=1sec


Now I'm getting mad...second execution took only 1 sec...this is
certainly due to cache use but if I restarted firebird server process,
does it keep cache?

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-06 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
2015.09.05. 9:00 keltezéssel, Hector Sánchez hec...@planatec.es 
[firebird-support] írta:

> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4), 3GB 
> RAM

Hi,

Can you test with 3.0 (Beta 2 or snapshot)?

Gabor






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] About firebird performance

2015-09-06 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Hi Jesus...I'm using SuperServer version for testing purposes as I think is
the most closed to IB75 behaviour.

2015-09-05 18:50 GMT+02:00 Jesus Garcia jeg...@gmail.com [firebird-support]
:

>
>
> Hello Hector, what version of firebird are you running, super server,
> super classic or classic server?
>
> Jesus Angel Garcia Zarco
> Cointec
>
> El 5/9/2015, a las 18:00, Hector Sánchez hec...@planatec.es
> [firebird-support]  escribió:
>
>
>
> I discovered something else quite interesting:
>
> I realized both IB and FB dbs have pagebuffers set to 100.000. I changed
> FB to 0 and now it works like I expected!!..Same query now completes on FB
> on 50 sec. It seems this is the problem although I don't understand why.
>
> Nevertheless, I'll try on a physical machine as you and Carlos suggest and
> see how it behaves.
>
>
>
> 2015-09-05 15:47 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
> [firebird-support] :
>
>>
>>
>> Hi Hector,
>>
>> Thanks for statistics, it clarifies situation.
>>
>> There is 304880 data pages, each is 4kb, so Firebird need to read 1.1Gb
>> of data to count records.
>> Count uses NATURAL scan, so it's a simple set of disk reads, CPU is not
>> involved.
>>
>> Regular SATA3 disk should read 30-70Mb per second (I assume database is
>> not fragmented, since you told it was freshly restored), it gives us
>> estimation from 15 to 30 seconds.
>>
>> So, I think there is a disk IO problem.
>> Try it on another computer (better not virtual).
>>
>> Regards,
>> Alexey
>>
>>
>>
>>
>>
>>
>> He again Alexey,
>>
>> As I suspected, my problem is not caused by GC:
>>
>> gstat over the table reported this:
>>
>> TABLE1 (503)
>> Primary pointer page: 9374, Index root page: 9375
>> Average record length: 317.63, total records: 3261775
>> Average version length: 0.00, total versions: 0, max versions: 0
>> Data pages: 304880, data page slots: 304880, average fill: 90%
>>
>> Also, as you suggested ran the query twice a got exaclty the same times:
>> 51minutes to complete!!!
>>
>> I must have something obvious which is killing firebird performance, but
>> cannot guess it.
>>
>> Any help would be appretiated.
>>
>>
>> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
>> [firebird-support] < 
>> firebird-support@yahoogroups.com>:
>>
>>>
>>>
>>> Hi Hector,
>>>
>>> So you finally decided to try Firebird - that's good.
>>>
>>> I think that you are facing garbage collection problem - if there are a
>>> lot of record versions in the TABLE1 which are not interested to any
>>> transaction, your query will force garbage collection, and it can take some
>>> time.
>>>
>>> How to check? run SELECT count(*) twice and compare execution times, and
>>> other query statistics (reads/writes).
>>>
>>> Also, run gstat -a -r  and check information for TABLE1 -
>>> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
>>> certainly related with record versions, with the initial cause in wrong
>>> transactions management - i.e., long-running write transactions or forced
>>> rollbacks.
>>>
>>>
>>> Regards,
>>> Alexey Kovyazin
>>> IBSurgeon
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> I'm doing some basic tests with Firebird and I'm facing something I
>>> cannot understand (I'm a newbie to Firebird which has worked with
>>> IB7.5 for years, so I apologize if I ask something obvious):
>>>
>>> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
>>> 3GB RAM
>>> - I placed a "huge" database: 1000 tables, 9GB
>>> - When I issue a query like this "select count(*) from TABLE1" it
>>> takes very long time to complete (>30 min).
>>> - TABLE 1 has 3 million records
>>>
>>> As I told you, I'm quite used to IB7.5, but I guess this is not normal
>>> at all...probably I'm missing something but I couldn't find any
>>> document which gives me an idea of what I'm doing wrong.
>>>
>>> With same hardware an IB7.5 same query took 40 sec to complete (first
>>> time issued, no cache).
>>>
>>> Could anyone help?
>>>
>>> --
>>> --
>>> Planatec Software S.L. ** 
>>>  
>>> telf: +34 964 340 560 <%2B34%20964%20340%20560> ** fax: +34 961 130 921
>>> <%2B34%20961%20130%20921>
>>>
>>>
>>>
>>
>> --
>> Planatec Software S.L. ** 
>> telf: +34 964 340 560 ** fax: +34 961 130 921
>>
>>
>>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread hv...@users.sourceforge.net [firebird-support] 
> ---In firebird-support@yahoogroups.com,  wrote :
 > > I discovered something else quite interesting: > 
 > I realized both IB and FB dbs have pagebuffers set to 100.000. I changed FB 
 > to 0 and now it works like I 
> expected!!..Same query now completes on FB on 50 sec. It seems this is the 
> problem although I don't 
> understand why.
  

  Carefully read firebird.conf. Especially FileSystemCacheThreshold setting.

  You've set page cache for database above default value of 
FileSystemCacheThreshold (100K > 64K) thus disable file system caching for that 
database.

  If you really need both page cache of 100K pages and file system cache, raise 
FileSystemCacheThreshold value to be more than 100K

Regards,
Vlad

RE: [firebird-support] About firebird performance

2015-09-05 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 


> Hi Hector,
> 
> >  I discovered something else quite interesting:
> >
> >  I realized both IB and FB dbs have pagebuffers set to 100.000. I changed FB
> to 0 and now it works like I expected!!..
> >  Same query now completes on FB on 50 sec. It seems this is the problem
> although I don't understand why.
> 
> 100 000 is too high for Firebird SuperServer, it should be set to 1.


While the value is high, depending on the database page size it should not 
cause the issue reported.

Hector,

Please advise on:

- the page size of the database? (FB and IB)

- once you have completed the test, how much memory is being used/allocated to 
the FB/IB engine process?

- are you running the test after a fresh restart of the VMs?


Sean

Re: [firebird-support] About firebird performance

2015-09-05 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support] 

Hi Hector,

I discovered something else quite interesting:

I realized both IB and FB dbs have pagebuffers set to 100.000. I 
changed FB to 0 and now it works like I expected!!..Same query now 
completes on FB on 50 sec. It seems this is the problem although I 
don't understand why.


100 000 is too high for Firebird SuperServer, it should be set to 1.


You can use optimized Firebird configuration files
http://ib-aid.com/en/optimized-firebird-configuration/

Regards,
Alexey Kovyazin
IBSurgeon






Nevertheless, I'll try on a physical machine as you and Carlos suggest 
and see how it behaves.




2015-09-05 15:47 GMT+02:00 Alexey Kovyazin a...@ib-aid.com 
 [firebird-support] 
>:


Hi Hector,

Thanks for statistics, it clarifies situation.

There is 304880 data pages, each is 4kb, so Firebird need to read
1.1Gb of data to count records.
Count uses NATURAL scan, so it's a simple set of disk reads, CPU
is not involved.

Regular SATA3 disk should read 30-70Mb per second (I assume
database is not fragmented, since you told it was freshly
restored), it gives us estimation from 15 to 30 seconds.

So, I think there is a disk IO problem.
Try it on another computer (better not virtual).

Regards,
Alexey






He again Alexey,

As I suspected, my problem is not caused by GC:

gstat over the table reported this:

TABLE1 (503)
Primary pointer page: 9374, Index root page: 9375
Average record length: 317.63, total records: 3261775
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 304880, data page slots: 304880, average fill: 90%

Also, as you suggested ran the query twice a got exaclty the same
times: 51minutes to complete!!!

I must have something obvious which is killing firebird
performance, but cannot guess it.

Any help would be appretiated.


2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
 [firebird-support]
mailto:firebird-support@yahoogroups.com>>:

Hi Hector,

So you finally decided to try Firebird - that's good.

I think that you are facing garbage collection problem - if
there are a lot of record versions in the TABLE1 which are
not interested to any transaction, your query will force
garbage collection, and it can take some time.

How to check? run SELECT count(*) twice and compare execution
times, and other query statistics (reads/writes).

Also, run gstat -a -r  and check information for
TABLE1 - VERSIONS and MAX VERSIONS, if there are big numbers
there, problem is certainly related with record versions,
with the initial cause in wrong transactions management -
i.e., long-running write transactions or forced rollbacks.


Regards,
Alexey Kovyazin
IBSurgeon







I'm doing some basic tests with Firebird and I'm facing
something I
cannot understand (I'm a newbie to Firebird which has worked
with
IB7.5 for years, so I apologize if I ask something obvious):

- I have a linux server with firebird 2.5.4 Superserver:
multi-core (4), 3GB RAM
- I placed a "huge" database: 1000 tables, 9GB
- When I issue a query like this "select count(*) from
TABLE1" it
takes very long time to complete (>30 min).
- TABLE 1 has 3 million records

As I told you, I'm quite used to IB7.5, but I guess this is
not normal
at all...probably I'm missing something but I couldn't find any
document which gives me an idea of what I'm doing wrong.

With same hardware an IB7.5 same query took 40 sec to
complete (first
time issued, no cache).

Could anyone help?

-- 
--

Planatec Software S.L. ** 

telf: +34 964 340 560  ** fax:
+34 961 130 921 





--
Planatec Software S.L. ** 
telf: +34 964 340 560  ** fax: +34
961 130 921 




--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Sat, Sep 5, 2015 at 12:00 PM, Hector Sánchez hec...@planatec.es
[firebird-support]  wrote:

>
>
> I discovered something else quite interesting:
>
> I realized both IB and FB dbs have pagebuffers set to 100.000. I changed
> FB to 0 and now it works like I expected!!..Same query now completes on FB
> on 50 sec. It seems this is the problem although I don't understand why.
>
> Nevertheless, I'll try on a physical machine as you and Carlos suggest and
> see how it behaves.
>
>
Very odd.   Could you collect the performance stats for the query on the
two systems? Specifically, I'd like to know how much physical I/O each did
- that's reported as Reads and Writes.  Fetches and Marks don't matter
here.

Cheers,

Ann

On another system I worked on we realized as the cache got very large that
a totally fair LRU was taking up a significant fraction of the CPU load.
That might be what you're seeing.

Re: [firebird-support] About firebird performance

2015-09-05 Thread Jesus Garcia jeg...@gmail.com [firebird-support] 
Hello Hector, what version of firebird are you running, super server, super 
classic or classic server?

Jesus Angel Garcia Zarco
Cointec

> El 5/9/2015, a las 18:00, Hector Sánchez hec...@planatec.es 
> [firebird-support]  escribió:
> 
> I discovered something else quite interesting:
> 
> I realized both IB and FB dbs have pagebuffers set to 100.000. I changed FB 
> to 0 and now it works like I expected!!..Same query now completes on FB on 50 
> sec. It seems this is the problem although I don't understand why.
> 
> Nevertheless, I'll try on a physical machine as you and Carlos suggest and 
> see how it behaves.
> 
> 
> 
> 2015-09-05 15:47 GMT+02:00 Alexey Kovyazin a...@ib-aid.com [firebird-support] 
> :
>>  
>> Hi Hector,
>> 
>> Thanks for statistics, it clarifies situation.
>> 
>> There is 304880 data pages, each is 4kb, so Firebird need to read 1.1Gb of 
>> data to count records.
>> Count uses NATURAL scan, so it's a simple set of disk reads, CPU is not 
>> involved.
>> 
>> Regular SATA3 disk should read 30-70Mb per second (I assume database is not 
>> fragmented, since you told it was freshly restored), it gives us estimation 
>> from 15 to 30 seconds.
>> 
>> So, I think there is a disk IO problem.
>> Try it on another computer (better not virtual).
>> 
>> Regards,
>> Alexey
>> 
>> 
>> 
>> 
>> 
>>>  
>>> He again Alexey,
>>> 
>>> As I suspected, my problem is not caused by GC:
>>> 
>>> gstat over the table reported this:
>>> 
>>> TABLE1 (503)
>>> Primary pointer page: 9374, Index root page: 9375
>>> Average record length: 317.63, total records: 3261775
>>> Average version length: 0.00, total versions: 0, max versions: 0
>>> Data pages: 304880, data page slots: 304880, average fill: 90%
>>> 
>>> Also, as you suggested ran the query twice a got exaclty the same times: 
>>> 51minutes to complete!!!
>>> 
>>> I must have something obvious which is killing firebird performance, but 
>>> cannot guess it.
>>> 
>>> Any help would be appretiated. 
>>> 
>>> 
>>> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com 
>>> [firebird-support] :
  
 Hi Hector,
 
 So you finally decided to try Firebird - that's good.
 
 I think that you are facing garbage collection problem - if there are a 
 lot of   record versions in the TABLE1 which 
 are not interested to any transaction, your query will force garbage 
 collection, and it can take some time.
 
 How to check? run SELECT count(*) twice and compare execution times, and 
 other query statistics (reads/writes).
 
 Also, run gstat -a -r  and check information for TABLE1 - 
 VERSIONS and MAX VERSIONS, if there are big numbers there, problem is 
 certainly related with record versions, with the initial cause in wrong 
 transactions management - i.e.,   long-running 
 write transactions or forced rollbacks.
 
 
 Regards,
 Alexey Kovyazin
 IBSurgeon
 
 
 
 
 
 
>  
> I'm doing some basic tests with Firebird and I'm facing something I
> cannot understand (I'm a newbie to Firebird which has worked with
> IB7.5 for years, so I apologize if I ask something obvious):
> 
> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4), 
> 3GB RAM
> - I placed a "huge" database: 1000 tables, 9GB
> - When I issue a query like this "select count(*) from TABLE1" it
> takes very long time to complete (>30 min).
> - TABLE 1 has 3 million records
> 
> As I told you, I'm quite used to IB7.5, but I guess this is not normal
> at all...probably I'm missing something but I couldn't find any
> document which gives me an idea of   what 
> I'm doing wrong.
> 
> With same hardware an IB7.5 same query took 40 sec to complete (first
> time issued, no cache).
> 
> Could anyone help?
> 
> -- 
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>>> 
>>> 
>>> --
>>> Planatec Software S.L. ** 
>>> telf: +34 964 340 560 ** fax: +34 961 130 921
> 
> 
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>

Re: [firebird-support] About firebird performance

2015-09-05 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
I discovered something else quite interesting:

I realized both IB and FB dbs have pagebuffers set to 100.000. I changed FB
to 0 and now it works like I expected!!..Same query now completes on FB on
50 sec. It seems this is the problem although I don't understand why.

Nevertheless, I'll try on a physical machine as you and Carlos suggest and
see how it behaves.



2015-09-05 15:47 GMT+02:00 Alexey Kovyazin a...@ib-aid.com [firebird-support]
:

>
>
> Hi Hector,
>
> Thanks for statistics, it clarifies situation.
>
> There is 304880 data pages, each is 4kb, so Firebird need to read 1.1Gb of
> data to count records.
> Count uses NATURAL scan, so it's a simple set of disk reads, CPU is not
> involved.
>
> Regular SATA3 disk should read 30-70Mb per second (I assume database is
> not fragmented, since you told it was freshly restored), it gives us
> estimation from 15 to 30 seconds.
>
> So, I think there is a disk IO problem.
> Try it on another computer (better not virtual).
>
> Regards,
> Alexey
>
>
>
>
>
>
> He again Alexey,
>
> As I suspected, my problem is not caused by GC:
>
> gstat over the table reported this:
>
> TABLE1 (503)
> Primary pointer page: 9374, Index root page: 9375
> Average record length: 317.63, total records: 3261775
> Average version length: 0.00, total versions: 0, max versions: 0
> Data pages: 304880, data page slots: 304880, average fill: 90%
>
> Also, as you suggested ran the query twice a got exaclty the same times:
> 51minutes to complete!!!
>
> I must have something obvious which is killing firebird performance, but
> cannot guess it.
>
> Any help would be appretiated.
>
>
> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
> [firebird-support] < 
> firebird-support@yahoogroups.com>:
>
>>
>>
>> Hi Hector,
>>
>> So you finally decided to try Firebird - that's good.
>>
>> I think that you are facing garbage collection problem - if there are a
>> lot of record versions in the TABLE1 which are not interested to any
>> transaction, your query will force garbage collection, and it can take some
>> time.
>>
>> How to check? run SELECT count(*) twice and compare execution times, and
>> other query statistics (reads/writes).
>>
>> Also, run gstat -a -r  and check information for TABLE1 -
>> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
>> certainly related with record versions, with the initial cause in wrong
>> transactions management - i.e., long-running write transactions or forced
>> rollbacks.
>>
>>
>> Regards,
>> Alexey Kovyazin
>> IBSurgeon
>>
>>
>>
>>
>>
>>
>>
>>
>> I'm doing some basic tests with Firebird and I'm facing something I
>> cannot understand (I'm a newbie to Firebird which has worked with
>> IB7.5 for years, so I apologize if I ask something obvious):
>>
>> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
>> 3GB RAM
>> - I placed a "huge" database: 1000 tables, 9GB
>> - When I issue a query like this "select count(*) from TABLE1" it
>> takes very long time to complete (>30 min).
>> - TABLE 1 has 3 million records
>>
>> As I told you, I'm quite used to IB7.5, but I guess this is not normal
>> at all...probably I'm missing something but I couldn't find any
>> document which gives me an idea of what I'm doing wrong.
>>
>> With same hardware an IB7.5 same query took 40 sec to complete (first
>> time issued, no cache).
>>
>> Could anyone help?
>>
>> --
>> --
>> Planatec Software S.L. ** 
>>  
>> telf: +34 964 340 560 <%2B34%20964%20340%20560> ** fax: +34 961 130 921
>> <%2B34%20961%20130%20921>
>>
>>
>>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Thanks Carlos...environments are exactly the samethat's why semmed
strange to me. It might be caused by the way FB and IB access disk that
it's different in some way and makes my VMs get saturated.

I'll try on a physical server and let you know.

2015-09-05 15:52 GMT+02:00 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support] :

>
>
> VM's consumes resources, so it can affect performance, specially I/O.
>
> I think the first thing you should do is to install IB and FB in the
> exactly same environment and check if they really have that performance
> diference you talked about.
>
> Carlos
> Firebird Performance in Detail - http://videos.firebirddevelopersday.com
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
> Forgot to mention both servers (FB and IB) are on virtual machines over
> KVM. Both VMs have the same resources and same linux distro installed. They
> have linux 64 bit and I installed FB2.5.4 SS 32bit. Could be any issue
> related to bad performance of FB over KVM?
>
> I'll also try to put FB on a phisical server to see if it behaves better.
>
> 2015-09-05 14:40 GMT+02:00 'Carlos H. Cantu' lis...@warmboot.com.br
> [firebird-support] :
>
> Just curious, when you said IB took 40 seconds, were you using the same
> machine that you are using with Firebird? If don't so, can you try it and
> report the result?
>
> []s
> Carlos
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
>
> He again Alexey,
>
> As I suspected, my problem is not caused by GC:
>
> gstat over the table reported this:
>
> TABLE1 (503)
>   Primary pointer page: 9374, Index root page: 9375
>   Average record length: 317.63, total records: 3261775
>   Average version length: 0.00, total versions: 0, max versions: 0
>   Data pages: 304880, data page slots: 304880, average fill: 90%
>
> Also, as you suggested ran the query twice a got exaclty the same times:
> 51minutes to complete!!!
>
> I must have something obvious which is killing firebird performance, but
> cannot guess it.
>
> Any help would be appretiated.
>
>
> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
> [firebird-support] :
>
> Hi Hector,
>
> So you finally decided to try Firebird - that's good.
>
> I think that you are facing garbage collection problem - if there are a
> lot of record versions in the TABLE1 which are not interested to any
> transaction, your query will force garbage collection, and it can take some
> time.
>
> How to check? run SELECT count(*) twice and compare execution times, and
> other query statistics (reads/writes).
>
> Also, run gstat -a -r  and check information for TABLE1 -
> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
> certainly related with record versions, with the initial cause in wrong
> transactions management - i.e., long-running write transactions or forced
> rollbacks.
>
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
>
>
> I'm doing some basic tests with Firebird and I'm facing something I
> cannot understand (I'm a newbie to Firebird which has worked with
> IB7.5 for years, so I apologize if I ask something obvious):
>
> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
> 3GB RAM
> - I placed a "huge" database: 1000 tables, 9GB
> - When I issue a query like this "select count(*) from TABLE1" it
> takes very long time to complete (>30 min).
> - TABLE 1 has 3 million records
>
> As I told you, I'm quite used to IB7.5, but I guess this is not normal
> at all...probably I'm missing something but I couldn't find any
> document which gives me an idea of what I'm doing wrong.
>
> With same hardware an IB7.5 same query took 40 sec to complete (first
> time issued, no cache).
>
> Could anyone help?
>
> --
> --
> Planatec Software S.L. ** 
> 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support]

Re: [firebird-support] About firebird performance

2015-09-05 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support] 

Hi Hector,

Thanks for statistics, it clarifies situation.

There is 304880 data pages, each is 4kb, so Firebird need to read 1.1Gb 
of data to count records.
Count uses NATURAL scan, so it's a simple set of disk reads, CPU is not 
involved.


Regular SATA3 disk should read 30-70Mb per second (I assume database is 
not fragmented, since you told it was freshly restored), it gives us 
estimation from 15 to 30 seconds.


So, I think there is a disk IO problem.
Try it on another computer (better not virtual).

Regards,
Alexey






He again Alexey,

As I suspected, my problem is not caused by GC:

gstat over the table reported this:

TABLE1 (503)
Primary pointer page: 9374, Index root page: 9375
Average record length: 317.63, total records: 3261775
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 304880, data page slots: 304880, average fill: 90%

Also, as you suggested ran the query twice a got exaclty the same 
times: 51minutes to complete!!!


I must have something obvious which is killing firebird performance, 
but cannot guess it.


Any help would be appretiated.


2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com 
 [firebird-support] 
>:


Hi Hector,

So you finally decided to try Firebird - that's good.

I think that you are facing garbage collection problem - if there
are a lot of record versions in the TABLE1 which are not
interested to any transaction, your query will force garbage
collection, and it can take some time.

How to check? run SELECT count(*) twice and compare execution
times, and other query statistics (reads/writes).

Also, run gstat -a -r  and check information for TABLE1
- VERSIONS and MAX VERSIONS, if there are big numbers there,
problem is certainly related with record versions, with the
initial cause in wrong transactions management - i.e.,
long-running write transactions or forced rollbacks.


Regards,
Alexey Kovyazin
IBSurgeon







I'm doing some basic tests with Firebird and I'm facing something I
cannot understand (I'm a newbie to Firebird which has worked with
IB7.5 for years, so I apologize if I ask something obvious):

- I have a linux server with firebird 2.5.4 Superserver:
multi-core (4), 3GB RAM
- I placed a "huge" database: 1000 tables, 9GB
- When I issue a query like this "select count(*) from TABLE1" it
takes very long time to complete (>30 min).
- TABLE 1 has 3 million records

As I told you, I'm quite used to IB7.5, but I guess this is not
normal
at all...probably I'm missing something but I couldn't find any
document which gives me an idea of what I'm doing wrong.

With same hardware an IB7.5 same query took 40 sec to complete (first
time issued, no cache).

Could anyone help?

-- 
--

Planatec Software S.L. ** 

telf: +34 964 340 560  ** fax: +34
961 130 921 





--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Forgot to mention both servers (FB and IB) are on virtual machines over
KVM. Both VMs have the same resources and same linux distro installed. They
have linux 64 bit and I installed FB2.5.4 SS 32bit. Could be any issue
related to bad performance of FB over KVM?

I'll also try to put FB on a phisical server to see if it behaves better.

2015-09-05 14:40 GMT+02:00 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support] :

>
>
> Just curious, when you said IB took 40 seconds, were you using the same
> machine that you are using with Firebird? If don't so, can you try it and
> report the result?
>
> []s
> Carlos
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
> He again Alexey,
>
> As I suspected, my problem is not caused by GC:
>
> gstat over the table reported this:
>
> TABLE1 (503)
>Primary pointer page: 9374, Index root page: 9375
>Average record length: 317.63, total records: 3261775
>Average version length: 0.00, total versions: 0, max versions: 0
>Data pages: 304880, data page slots: 304880, average fill: 90%
>
> Also, as you suggested ran the query twice a got exaclty the same times:
> 51minutes to complete!!!
>
> I must have something obvious which is killing firebird performance, but
> cannot guess it.
>
> Any help would be appretiated.
>
>
> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
> [firebird-support] :
>
> Hi Hector,
>
> So you finally decided to try Firebird - that's good.
>
> I think that you are facing garbage collection problem - if there are a
> lot of record versions in the TABLE1 which are not interested to any
> transaction, your query will force garbage collection, and it can take some
> time.
>
> How to check? run SELECT count(*) twice and compare execution times, and
> other query statistics (reads/writes).
>
> Also, run gstat -a -r  and check information for TABLE1 -
> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
> certainly related with record versions, with the initial cause in wrong
> transactions management - i.e., long-running write transactions or forced
> rollbacks.
>
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
>
>
> I'm doing some basic tests with Firebird and I'm facing something I
> cannot understand (I'm a newbie to Firebird which has worked with
> IB7.5 for years, so I apologize if I ask something obvious):
>
> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
> 3GB RAM
> - I placed a "huge" database: 1000 tables, 9GB
> - When I issue a query like this "select count(*) from TABLE1" it
> takes very long time to complete (>30 min).
> - TABLE 1 has 3 million records
>
> As I told you, I'm quite used to IB7.5, but I guess this is not normal
> at all...probably I'm missing something but I couldn't find any
> document which gives me an idea of what I'm doing wrong.
>
> With same hardware an IB7.5 same query took 40 sec to complete (first
> time issued, no cache).
>
> Could anyone help?
>
> --
> --
> Planatec Software S.L. ** 
> 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Not exactly the same machine, but almost...same server config, same linux
distro. In order to make tests with FB try to be comparable I tried to have
exact the same machines. I'll do what you suggest and post results.

There must be something stupid but I cannot see it as it's not possible
Firebird behaves this way. Now I'm performing a backup on that database and
still haven't finished after more than 2 hours.

2015-09-05 14:40 GMT+02:00 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support] :

>
>
> Just curious, when you said IB took 40 seconds, were you using the same
> machine that you are using with Firebird? If don't so, can you try it and
> report the result?
>
> []s
> Carlos
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
> He again Alexey,
>
> As I suspected, my problem is not caused by GC:
>
> gstat over the table reported this:
>
> TABLE1 (503)
>Primary pointer page: 9374, Index root page: 9375
>Average record length: 317.63, total records: 3261775
>Average version length: 0.00, total versions: 0, max versions: 0
>Data pages: 304880, data page slots: 304880, average fill: 90%
>
> Also, as you suggested ran the query twice a got exaclty the same times:
> 51minutes to complete!!!
>
> I must have something obvious which is killing firebird performance, but
> cannot guess it.
>
> Any help would be appretiated.
>
>
> 2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com
> [firebird-support] :
>
> Hi Hector,
>
> So you finally decided to try Firebird - that's good.
>
> I think that you are facing garbage collection problem - if there are a
> lot of record versions in the TABLE1 which are not interested to any
> transaction, your query will force garbage collection, and it can take some
> time.
>
> How to check? run SELECT count(*) twice and compare execution times, and
> other query statistics (reads/writes).
>
> Also, run gstat -a -r  and check information for TABLE1 -
> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
> certainly related with record versions, with the initial cause in wrong
> transactions management - i.e., long-running write transactions or forced
> rollbacks.
>
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
>
>
> I'm doing some basic tests with Firebird and I'm facing something I
> cannot understand (I'm a newbie to Firebird which has worked with
> IB7.5 for years, so I apologize if I ask something obvious):
>
> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
> 3GB RAM
> - I placed a "huge" database: 1000 tables, 9GB
> - When I issue a query like this "select count(*) from TABLE1" it
> takes very long time to complete (>30 min).
> - TABLE 1 has 3 million records
>
> As I told you, I'm quite used to IB7.5, but I guess this is not normal
> at all...probably I'm missing something but I couldn't find any
> document which gives me an idea of what I'm doing wrong.
>
> With same hardware an IB7.5 same query took 40 sec to complete (first
> time issued, no cache).
>
> Could anyone help?
>
> --
> --
> Planatec Software S.L. ** 
> 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> --
> Planatec Software S.L. ** 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread 'Carlos H. Cantu' lis...@warmboot.com.br [firebird-support]

Re: [firebird-support] About firebird performance

2015-09-05 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
He again Alexey,

As I suspected, my problem is not caused by GC:

gstat over the table reported this:

TABLE1 (503)
Primary pointer page: 9374, Index root page: 9375
Average record length: 317.63, total records: 3261775
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 304880, data page slots: 304880, average fill: 90%

Also, as you suggested ran the query twice a got exaclty the same times:
51minutes to complete!!!

I must have something obvious which is killing firebird performance, but
cannot guess it.

Any help would be appretiated.


2015-09-05 11:59 GMT+02:00 Alexey Kovyazin a...@ib-aid.com [firebird-support]
:

>
>
> Hi Hector,
>
> So you finally decided to try Firebird - that's good.
>
> I think that you are facing garbage collection problem - if there are a
> lot of record versions in the TABLE1 which are not interested to any
> transaction, your query will force garbage collection, and it can take some
> time.
>
> How to check? run SELECT count(*) twice and compare execution times, and
> other query statistics (reads/writes).
>
> Also, run gstat -a -r  and check information for TABLE1 -
> VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
> certainly related with record versions, with the initial cause in wrong
> transactions management - i.e., long-running write transactions or forced
> rollbacks.
>
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
>
>
>
> I'm doing some basic tests with Firebird and I'm facing something I
> cannot understand (I'm a newbie to Firebird which has worked with
> IB7.5 for years, so I apologize if I ask something obvious):
>
> - I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
> 3GB RAM
> - I placed a "huge" database: 1000 tables, 9GB
> - When I issue a query like this "select count(*) from TABLE1" it
> takes very long time to complete (>30 min).
> - TABLE 1 has 3 million records
>
> As I told you, I'm quite used to IB7.5, but I guess this is not normal
> at all...probably I'm missing something but I couldn't find any
> document which gives me an idea of what I'm doing wrong.
>
> With same hardware an IB7.5 same query took 40 sec to complete (first
> time issued, no cache).
>
> Could anyone help?
>
> --
> --
> Planatec Software S.L. ** 
> 
> telf: +34 964 340 560 ** fax: +34 961 130 921
>
>
> 
>

-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread Hector Sánchez hec...@planatec.es [firebird-support] 
Thanks Alexey,

Yes, I finally decided to try Firebird due to the very bad support form
Embarcadero in migrating to newer version of IB. I must say I'm quite
impressed with FB and start planning migrating to it if I manage to get
same performance level that we have with IB.

I'll try what you suggest but I'm afraid this won't be the cause as it's a
fresh new Db (just restored) with no user connected at all, as it's
isolated in a server for testing purposes. So no transaction should be
issued to it. However, if I had GC troubles I should see firebird process
at higher CPU rates, shouldn't I?

I'll let you know.



El 5/9/2015, a las 11:59, Alexey Kovyazin a...@ib-aid.com [firebird-support] <
firebird-support@yahoogroups.com> escribió:



Hi Hector,

So you finally decided to try Firebird - that's good.

I think that you are facing garbage collection problem - if there are a lot
of record versions in the TABLE1 which are not interested to any
transaction, your query will force garbage collection, and it can take some
time.

How to check? run SELECT count(*) twice and compare execution times, and
other query statistics (reads/writes).

Also, run gstat -a -r  and check information for TABLE1 -
VERSIONS and MAX VERSIONS, if there are big numbers there, problem is
certainly related with record versions, with the initial cause in wrong
transactions management - i.e., long-running write transactions or forced
rollbacks.


Regards,
Alexey Kovyazin
IBSurgeon








I'm doing some basic tests with Firebird and I'm facing something I
cannot understand (I'm a newbie to Firebird which has worked with
IB7.5 for years, so I apologize if I ask something obvious):

- I have a linux server with firebird 2.5.4 Superserver: multi-core (4),
3GB RAM
- I placed a "huge" database: 1000 tables, 9GB
- When I issue a query like this "select count(*) from TABLE1" it
takes very long time to complete (>30 min).
- TABLE 1 has 3 million records

As I told you, I'm quite used to IB7.5, but I guess this is not normal
at all...probably I'm missing something but I couldn't find any
document which gives me an idea of what I'm doing wrong.

With same hardware an IB7.5 same query took 40 sec to complete (first
time issued, no cache).

Could anyone help?

-- 
--
Planatec Software S.L. **  
telf: +34 964 340 560 ** fax: +34 961 130 921




-- 
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921

Re: [firebird-support] About firebird performance

2015-09-05 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support] 

Hi Hector,

So you finally decided to try Firebird - that's good.

I think that you are facing garbage collection problem - if there are a 
lot of record versions in the TABLE1 which are not interested to any 
transaction, your query will force garbage collection, and it can take 
some time.


How to check? run SELECT count(*) twice and compare execution times, and 
other query statistics (reads/writes).


Also, run gstat -a -r  and check information for TABLE1 - 
VERSIONS and MAX VERSIONS, if there are big numbers there, problem is 
certainly related with record versions, with the initial cause in wrong 
transactions management - i.e., long-running write transactions or 
forced rollbacks.



Regards,
Alexey Kovyazin
IBSurgeon







I'm doing some basic tests with Firebird and I'm facing something I
cannot understand (I'm a newbie to Firebird which has worked with
IB7.5 for years, so I apologize if I ask something obvious):

- I have a linux server with firebird 2.5.4 Superserver: multi-core 
(4), 3GB RAM

- I placed a "huge" database: 1000 tables, 9GB
- When I issue a query like this "select count(*) from TABLE1" it
takes very long time to complete (>30 min).
- TABLE 1 has 3 million records

As I told you, I'm quite used to IB7.5, but I guess this is not normal
at all...probably I'm missing something but I couldn't find any
document which gives me an idea of what I'm doing wrong.

With same hardware an IB7.5 same query took 40 sec to complete (first
time issued, no cache).

Could anyone help?

--
--
Planatec Software S.L. ** 
telf: +34 964 340 560 ** fax: +34 961 130 921