[firebird-support] Re: FB 3.0, got problem when add field to Table with existing data

[Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]]

02.06.2016 07:42, trsk...@yahoo.com wrote:
>
> I have a table with existing data & got a problem to add field that must
> have a value -> alter table Tbl1 add Fld1 int not null.

What value must it have? Add a default clause to this statement. Or add 
the field as nullable, populate with data and then alter to NOT NULL.


Dmitry

[firebird-support] FB 3.0, got problem when add field to Table with existing data

[trsk...@yahoo.com [firebird-support]]

Hi guys,

I have a table with existing data & got a problem to add field that must have a 
value -> alter table Tbl1 add Fld1 int not null.

If table doesn't have data, it is OK, but Firebird refused if table already 
have data. Any suggestion how to solve this problem?

Thanks & regards,
Anto

[firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?

[Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]]

02.06.2016 03:44, trsk...@yahoo.com wrote:
>
> Am I correct that maximum allowed RAM for database cache on SS FB 3.0 in
> Win64 is only 2 GB? Calculated from max DefaultDbCachePages (131072) x
> max Page Size (16384) = 2 GB.

Correct for 32-bit builds. 64-bit builds don't have such a limit.

> Setting DefaultDbCachePages more than allowed caused a crashed.

Try a v3.0.1 snapshot build, IIRC it was fixed recently.


Dmitry

[firebird-support] Max RAM for Database Cache in SS FB 3.0 64bit is to small?

[trsk...@yahoo.com [firebird-support]]

Hi guys,

Am I correct that maximum allowed RAM for database cache on SS FB 3.0 in Win64 
is only 2 GB? Calculated from max DefaultDbCachePages (131072) x max Page Size 
(16384) = 2 GB.

Setting DefaultDbCachePages more than allowed caused a crashed.

If this true, how to optimaze free RAM for performance?

Regards,
Anto
 

Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

[fabia...@itbizolutions.com.au [firebird-support]]

Hi Robert

Here is the link to both files as the yahoo group may not allow me to attach 
the files
http://document.nucleuscrm.com.au:7770/pdfs/TMPFiles/TaskManager_Performance_ALL_RAM_Consumed.jpg
http://document.nucleuscrm.com.au:7770/pdfs/TMPFiles/TaskManager_Process_Firebird_Only_Consumed_2GB.jpg

The firebird is definetly 64 bits, however the ODBC that access the server is 
32 bits, but the client should have no effect on the way the server handles the 
DB (in theory).

Cheers,
Fabian

  - Original Message - 
  From: Robert martin r...@chreos.com [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Thursday, June 02, 2016 9:22 AM
  Subject: Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0




  Hi Fabian


  Can't see your screen shot but FB usage at 2GB sounds like the limit for a 
32Bit app.  Have you installed the 32Bit version of FB accidentally?

  Thanks
  Rob

  On 2/06/2016 11:12 a.m., fabia...@itbizolutions.com.au [firebird-support] 
wrote:

  
Dmitry

Something funny is going on regarding the memory consumption, I have 
attached screenshots showing the memory counters. If you look at the process 
itself (file TaskManager_Process_Firebird_Only_Consumed_2GB.jpg), it only 
consumed 2 GB. If you look at Windows Task Manager (file 
TaskManager_Performance_ALL_RAM_Consumed.jpg), where it says "Physical Memory, 
just under the memory graph where it shows 3.13 GB used, the cached memory is 
29963 Mb. I know Firebird is who consumed that CACHED memory because I am 
forcing the DB to read 70GB on purpouse, but why is the memory not showing 
under the process Firebird ? (it only shows 2GB used there).

My aim is to set the config to only use RAM (no file system cache), and to 
place the hole DB in RAM for caching when reading, I am struggling to find a 
config mix that will do that. The config variables are:

FileSystemCacheThreshold = 0
FileSystemCacheSize = 80
TempCacheLimit = 24576M (I am not sure what this value is for, it seems to 
mean the maximum amount of temporary things to have in memory, like DB read 
cache)

The above 3 variables are the ones I am playing with, plus the 
DefaultDBCachePages = 9 in Databases.conf

Cheers,
Fabian

- Original Message - 
From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
To: firebird-support@yahoogroups.com 
Sent: Wednesday, June 01, 2016 3:49 PM
Subject: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

01.06.2016 04:16, Fabian Ernesto Chocron wrote:
>
> We are having trouble setting up the database cache size on FB 3.0
> running on Windows 2008 R2 64 bits with 32 GB ram.
>
> The problem we have is we cannot get the server to allocate the ram for
> the cache as we intend. With FB 2.54 we had the DB cache set very high,
> close to 1 GB per database, all running in RAM memory. With FB 3.0 we
> read it can allocate much more RAM to the cache, but it appears the
> server is allocating very small amount of Ram when the first user
> connects to the DB, and as we connect more users to the DB the ram
> consumption increases slowly.

What memory counters do you look at? Virtual (reserved) memory usage 
should grow high with the first connect, physical (committed) memory 
usage should grow together with your load, while pages are being read 
from disk.

> On firebird.conf
>
> FileSystemCacheThreshold = 0
> FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB
> ram.)

These changes conflict with each other. You disable filesystem cache 
with the first line and still want to use 16GB with the second line.

> On databases.conf
>
> MyTestDB = c:\Temp\MyDb.fdb
> {
> DefaultDbCachePages = 458752
> }

First of all, I'd suggest to reset FileSystemCacheSize back to zero and 
set FileSystemCacheThreshold to something higher than 458752 pages (e.g. 
50). Then re-test your memory usage and only then start tweaking the 
filesystem cache.

BTW, don't you occasionally have page cache override at the database 
level? What is gstat -h output?

Dmitry





++

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

[Non-text portions of this message have been removed]



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7598 / Virus Database: 4591/12342 - Release Date: 

Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

[Robert martin r...@chreos.com [firebird-support]]

Hi Fabian

Can't see your screen shot but FB usage at 2GB sounds like the limit for 
a 32Bit app.  Have you installed the 32Bit version of FB accidentally?


Thanks
Rob

On 2/06/2016 11:12 a.m., fabia...@itbizolutions.com.au 
[firebird-support] wrote:


Dmitry

Something funny is going on regarding the memory consumption, I have 
attached screenshots showing the memory counters. If you look at the 
process itself (file 
TaskManager_Process_Firebird_Only_Consumed_2GB.jpg), it only consumed 
2 GB. If you look at Windows Task Manager (file 
TaskManager_Performance_ALL_RAM_Consumed.jpg), where it says "Physical 
Memory, just under the memory graph where it shows 3.13 GB used, the 
cached memory is 29963 Mb. I know Firebird is who consumed that CACHED 
memory because I am forcing the DB to read 70GB on purpouse, but why 
is the memory not showing under the process Firebird ? (it only shows 
2GB used there).


My aim is to set the config to only use RAM (no file system cache), 
and to place the hole DB in RAM for caching when reading, I am 
struggling to find a config mix that will do that. The config 
variables are:


FileSystemCacheThreshold = 0
FileSystemCacheSize = 80
TempCacheLimit = 24576M (I am not sure what this value is for, it 
seems to mean the maximum amount of temporary things to have in 
memory, like DB read cache)


The above 3 variables are the ones I am playing with, plus the 
DefaultDBCachePages = 9 in Databases.conf


Cheers,
Fabian

- Original Message -
From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]
To: firebird-support@yahoogroups.com
Sent: Wednesday, June 01, 2016 3:49 PM
Subject: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

01.06.2016 04:16, Fabian Ernesto Chocron wrote:
>
> We are having trouble setting up the database cache size on FB 3.0
> running on Windows 2008 R2 64 bits with 32 GB ram.
>
> The problem we have is we cannot get the server to allocate the ram for
> the cache as we intend. With FB 2.54 we had the DB cache set very high,
> close to 1 GB per database, all running in RAM memory. With FB 3.0 we
> read it can allocate much more RAM to the cache, but it appears the
> server is allocating very small amount of Ram when the first user
> connects to the DB, and as we connect more users to the DB the ram
> consumption increases slowly.

What memory counters do you look at? Virtual (reserved) memory usage
should grow high with the first connect, physical (committed) memory
usage should grow together with your load, while pages are being read
from disk.

> On firebird.conf
>
> FileSystemCacheThreshold = 0
> FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB
> ram.)

These changes conflict with each other. You disable filesystem cache
with the first line and still want to use 16GB with the second line.

> On databases.conf
>
> MyTestDB = c:\Temp\MyDb.fdb
> {
> DefaultDbCachePages = 458752
> }

First of all, I'd suggest to reset FileSystemCacheSize back to zero and
set FileSystemCacheThreshold to something higher than 458752 pages (e.g.
50). Then re-test your memory usage and only then start tweaking the
filesystem cache.

BTW, don't you occasionally have page cache override at the database
level? What is gstat -h output?

Dmitry





++

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

[Non-text portions of this message have been removed]



No virus found in this message.
Checked by AVG - www.avg.com 
Version: 2016.0.7598 / Virus Database: 4591/12342 - Release Date: 06/01/16

Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

[fabia...@itbizolutions.com.au [firebird-support]]

Dmitry

Something funny is going on regarding the memory consumption, I have attached 
screenshots showing the memory counters. If you look at the process itself 
(file TaskManager_Process_Firebird_Only_Consumed_2GB.jpg), it only consumed 2 
GB. If you look at Windows Task Manager (file 
TaskManager_Performance_ALL_RAM_Consumed.jpg), where it says "Physical Memory, 
just under the memory graph where it shows 3.13 GB used, the cached memory is 
29963 Mb. I know Firebird is who consumed that CACHED memory because I am 
forcing the DB to read 70GB on purpouse, but why is the memory not showing 
under the process Firebird ? (it only shows 2GB used there).

My aim is to set the config to only use RAM (no file system cache), and to 
place the hole DB in RAM for caching when reading, I am struggling to find a 
config mix that will do that. The config variables are:

FileSystemCacheThreshold = 0
FileSystemCacheSize = 80
TempCacheLimit = 24576M  (I am not sure what this value is for, it seems to 
mean the maximum amount of temporary things to have in memory, like DB read 
cache)

The above 3 variables are the ones I am playing with, plus the 
DefaultDBCachePages = 9 in Databases.conf

Cheers,
Fabian


  - Original Message - 
  From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, June 01, 2016 3:49 PM
  Subject: [firebird-support] Re: Issue with Database Cache Size on FB 3.0


  01.06.2016 04:16, Fabian Ernesto Chocron wrote:
  >
  > We are having trouble setting up the database cache size on FB 3.0
  > running on Windows 2008 R2 64 bits with 32 GB ram.
  >
  > The problem we have is we cannot get the server to allocate the ram for
  > the cache as we intend. With FB 2.54 we had the DB cache set very high,
  > close to 1 GB per database, all running in RAM memory. With FB 3.0 we
  > read it can allocate much more RAM to the cache, but it appears the
  > server is allocating very small amount of Ram when the first user
  > connects to the DB, and as we connect more users to the DB the ram
  > consumption increases slowly.

  What memory counters do you look at? Virtual (reserved) memory usage 
  should grow high with the first connect, physical (committed) memory 
  usage should grow together with your load, while pages are being read 
  from disk.

  > On firebird.conf
  >
  > FileSystemCacheThreshold = 0
  > FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB
  > ram.)

  These changes conflict with each other. You disable filesystem cache 
  with the first line and still want to use 16GB with the second line.

  > On databases.conf
  >
  > MyTestDB = c:\Temp\MyDb.fdb
  > {
  > DefaultDbCachePages = 458752
  > }

  First of all, I'd suggest to reset FileSystemCacheSize back to zero and 
  set FileSystemCacheThreshold to something higher than 458752 pages (e.g. 
  50). Then re-test your memory usage and only then start tweaking the 
  filesystem cache.

  BTW, don't you occasionally have page cache override at the database 
  level? What is gstat -h output?


  Dmitry




  

  

  ++

  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




[Non-text portions of this message have been removed]

Re: [firebird-support] Stored Procedures Execution Plan

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

you must convert any procedure - for which you need plan - to execute block 
format

if you are interested in plan for proc 1 then you need proc 1 execte block
if this proc1 use proc2 you need to make proc2 as execute block..

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, June 1, 2016 2:27 PM
To: firebird-support@yahoogroups.com 
Subject: Re: Re: [firebird-support] Stored Procedures Execution Plan

  

Hi,




One problem with this approach (changing SP's to execute block) is that you 
still have the possibility of doesn't getting called SP plans (SP's called by 
the SP which you are converting to an execute block).

Att.
João Paulo



--
  DE: "liviuslivius liviusliv...@poczta.onet.pl [firebird-support]" 

  Para: "firebird-support@yahoogroups.com"  
  Enviadas: Quarta-feira, 1, Junho 2016 7:27:34
  Assunto: Re: Re: [firebird-support] Stored Procedures Execution Plan



  Hi,

  yes but this was "wrong"
  Consider this:
  inside procedure you have 5 different statements
  and you run query and only two of them are reached - do you really need to 
see all 5?

  Ok - now you got only "natural" word for procedure - i do not know if this is 
better now..
  But if you need plan for procedure - "change it" to execute block and you got 
all statements plan

  regards,
  Karol Bieniaszewski




  W dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones grfro...@gmail.com 
[firebird-support]  napisał:
  
It was sent and received... but this is working for me out of the box in FB 
2.5.4. I just write the select * from SP and ask for plan and it gives me the 
plan for the inside statements. Haven't tested on non-selectable SPs.

Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho 
joao_paul...@yahoo.com [firebird-support]  
escreveu:


  Hi.


  Since this was my first post in this list, I'm not sure if it was sent.

  If you already get it, or this is not the right place to discuss this 
kind of topic, I would be very grateful if you tell me where is.

  Att.
  João Paulo





DE: "joao_paul...@yahoo.com [firebird-support]" 

Para: firebird-support@yahoogroups.com 
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan


  

Hi.


I would like to know if it is possible to display the plan of stored 
procedure statements.

I'm interested in the plan of the statements inside the stored 
procedure (SP), not the plan just informing that I'm executing an SP.

We are running also selectable SP's.

Att.

João Paulo

[firebird-support] Detailed information in regards to Trusted Authentication

[Dalton Calford dcalf...@distributel.ca [firebird-support]]

Hi,


I am testing Firebird 3 and one of the tests happens to be finding out if it 
supports Active Directory.


I am assuming that trusted user authentication passes the user identity as per 
the local machine to the server.


The question I have is how you would setup a linux box (already part of the 
domain and domain authenticated) to accept trusted user authentication from a 
windows box on the same domain.


I am trying to find any documentation that goes into details about the 
configuration of trusted user authentication.


best regards


Dalton

Re: [firebird-support] Re: Firebird 3 on Kubuntu 16.04

[Dalton Calford dcalf...@distributel.ca [firebird-support]]

Thanks,


I removed Firebird and tried the downloads from Sourceforge, all is working 
fine now.


From: firebird-support@yahoogroups.com  on 
behalf of peshk...@mail.ru [firebird-support] 
Sent: June 1, 2016 5:19:23 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Firebird 3 on Kubuntu 16.04



Have you tried binaries from SF?

Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

[fabia...@itbizolutions.com.au [firebird-support]]

Hi Dmitry

I am progressing with solving this one, the first issue was related with a 
config variable reserving most of the ram upfront, hence there was no ram 
available for the DB cache. The second issue was misinterpreting what the 
following meant:

# File system cache size
# Note that the lowest number presently supported is 10%, and the highest number
# is 95%; numbers outside these limits will be set to the default of 30%.

First interpretation on the above was the variable must be between 0 and 1, as 
50% in math is usually written as 0.5, but then the following made it confusing:
# Type: integer, measured in % of total physical RAM

Where it says "integer" it complicated things, as it would not make sense to 
have a percentage forced to be integer, as there would be no way to represent 
40.5% for instance. So I assume the value is an integer because it is 
representing bytes, and bytes cannot have decimals.

Anyways, as I play along with different configurations I am finding the FB 3 
server VERY responsive interms of multiusers, I am glad the SMP efforts are 
showing very interesting performance, so congrats to all the development 
team!!

As a test I am using IBexpert to compare the data between the FB 2.54 DB and 
the 3.0, it is a 80 GB database, and at the same time I am loading users on the 
FB 3 database, and the performance is almost as good as running without any 
other parallel process, so I am very impressed.


Cheers
Fabian




  - Original Message - 
  From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, June 01, 2016 3:49 PM
  Subject: [firebird-support] Re: Issue with Database Cache Size on FB 3.0


  01.06.2016 04:16, Fabian Ernesto Chocron wrote:
  >
  > We are having trouble setting up the database cache size on FB 3.0
  > running on Windows 2008 R2 64 bits with 32 GB ram.
  >
  > The problem we have is we cannot get the server to allocate the ram for
  > the cache as we intend. With FB 2.54 we had the DB cache set very high,
  > close to 1 GB per database, all running in RAM memory. With FB 3.0 we
  > read it can allocate much more RAM to the cache, but it appears the
  > server is allocating very small amount of Ram when the first user
  > connects to the DB, and as we connect more users to the DB the ram
  > consumption increases slowly.

  What memory counters do you look at? Virtual (reserved) memory usage 
  should grow high with the first connect, physical (committed) memory 
  usage should grow together with your load, while pages are being read 
  from disk.

  > On firebird.conf
  >
  > FileSystemCacheThreshold = 0
  > FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB
  > ram.)

  These changes conflict with each other. You disable filesystem cache 
  with the first line and still want to use 16GB with the second line.

  > On databases.conf
  >
  > MyTestDB = c:\Temp\MyDb.fdb
  > {
  > DefaultDbCachePages = 458752
  > }

  First of all, I'd suggest to reset FileSystemCacheSize back to zero and 
  set FileSystemCacheThreshold to something higher than 458752 pages (e.g. 
  50). Then re-test your memory usage and only then start tweaking the 
  filesystem cache.

  BTW, don't you occasionally have page cache override at the database 
  level? What is gstat -h output?


  Dmitry




  

  

  ++

  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

Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

[fabia...@itbizolutions.com.au [firebird-support]]

Vlad

Thank you, english is not my first language, I interpreted the sentence as "go 
calculate the total amount of ram, then set the value as a number within such 
percentage of your ram...", but thanks for the clarification. Perhaps to avoid 
other users making the same mistake each configurable variable on firebird .cfg 
files could have a "domain" explaining what type of variable it is, like 
"Domain = From 0 to 100, up to 2 decimals allowed, decimal separator is DOT". 
Furthermore, in this case I first understood what was required was actually a 
percentage number, but then changed my mind as it would be too hard for most 
users to think that way, as percentage mathematically speaking the way to 
represent 20% is actually 0.2, so it goes from 0 to 1.

I hope this helps other users,

Cheers,
Fabian

  - Original Message - 
  From: hv...@users.sourceforge.net [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, June 01, 2016 9:32 PM
  Subject: [firebird-support] Re: Issue with Database Cache Size on FB 3.0




  ---In firebird-support@yahoogroups.com,  wrote :



  ...

  > The setting we are playing with are:
  > 
  > On firebird.conf
  > 
  > FileSystemCacheThreshold = 0
  > FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB 
  > ram.)

Does you ever read comments describing this setting ? I can do it for you : 

  "measured in % of total physical RAM". 


  I.e. any value not in range [0..100] is wrong by definition. Read 
firebird.log, it should contain 
  error message:

  "Incorrect FileSystemCacheSize setting %d. Using default (30 percent)."

  If you really want to limit file system cache by 50% of avalable RAM - set 
FileSystemCacheSize 
  value to 50 (and restart Firebird), no need to calc in bytes...

  Regards,
  Vlad





  

[firebird-support] Looking for example how to use FBTraceManager api in java.

[Doychin Bondzhev doyc...@dsoft-bg.com [firebird-support]]

Hi,

I'm looking for some example how to use TraceAPI provided in Jaybird.

-- 
Doychin Bondzhev
dSoft-Bulgaria Ltd.
PowerPro - billing & provisioning solution for Service providers
PowerStor - Warehouse & POS
http://www.dsoft-bg.com/
Mobile: +359888243116


[Non-text portions of this message have been removed]

Re: Re: [firebird-support] Stored Procedures Execution Plan

[João Paulo Carvalho joao_paul...@yahoo.com [firebird-support]]

Hi,

One problem with this approach (changing SP's to execute block) is that you 
still have the possibility of doesn't getting called SP plans (SP's called by 
the SP which you are converting to an execute block).
Att.João Paulo

 
  DE: "liviuslivius liviusliv...@poczta.onet.pl [firebird-support]" 

 Para: "firebird-support@yahoogroups.com"  
 Enviadas: Quarta-feira, 1, Junho 2016 7:27:34
 Assunto: Re: Re: [firebird-support] Stored Procedures Execution Plan
   
    Hi, yes but this was "wrong"Consider this:inside procedure you have 5 
different statementsand you run query and only two of them are reached - do you 
really need to see all 5? Ok - now you got only "natural" word for procedure - 
i do not know if this is better now..But if you need plan for procedure - 
"change it" to execute block and you got all statements plan regards,Karol 
BieniaszewskiW dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones 
grfro...@gmail.com [firebird-support]  
napisał:
 It was sent and received... but this is working for me out of the box in FB 
2.5.4. I just write the select * from SP and ask for plan and it gives me the 
plan for the inside statements. Haven't tested on non-selectable SPs. Em qua, 
25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com 
[firebird-support]  escreveu:
  Hi.  Since this was my first post in this list, I'm not sure if it was sent. 
If you already get it, or this is not the right place to discuss this kind of 
topic, I would be very grateful if you tell me where is. Att.João Paulo


DE: "joao_paul...@yahoo.com [firebird-support]" 

 Para: firebird-support@yahoogroups.com 
 Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
 Assunto: [firebird-support] Stored Procedures Execution Plan
 


  Hi.  I would like to know if it is possible to display the plan of stored 
procedure statements. I'm interested in the plan of the statements inside the 
stored procedure (SP), not the plan just informing that I'm executing an SP. We 
are running also selectable SP's. Att. João Paulo  


 

   #yiv7257244709 #yiv7257244709 -- #yiv7257244709ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7257244709 
#yiv7257244709ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7257244709 
#yiv7257244709ygrp-mkp #yiv7257244709hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv7257244709 #yiv7257244709ygrp-mkp #yiv7257244709ads 
{margin-bottom:10px;}#yiv7257244709 #yiv7257244709ygrp-mkp .yiv7257244709ad 
{padding:0 0;}#yiv7257244709 #yiv7257244709ygrp-mkp .yiv7257244709ad p 
{margin:0;}#yiv7257244709 #yiv7257244709ygrp-mkp .yiv7257244709ad a 
{color:#ff;text-decoration:none;}#yiv7257244709 #yiv7257244709ygrp-sponsor 
#yiv7257244709ygrp-lc {font-family:Arial;}#yiv7257244709 
#yiv7257244709ygrp-sponsor #yiv7257244709ygrp-lc #yiv7257244709hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7257244709 
#yiv7257244709ygrp-sponsor #yiv7257244709ygrp-lc .yiv7257244709ad 
{margin-bottom:10px;padding:0 0;}#yiv7257244709 #yiv7257244709actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7257244709 
#yiv7257244709activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7257244709
 #yiv7257244709activity span {font-weight:700;}#yiv7257244709 
#yiv7257244709activity span:first-child 
{text-transform:uppercase;}#yiv7257244709 #yiv7257244709activity span a 
{color:#5085b6;text-decoration:none;}#yiv7257244709 #yiv7257244709activity span 
span {color:#ff7900;}#yiv7257244709 #yiv7257244709activity span 
.yiv7257244709underline {text-decoration:underline;}#yiv7257244709 
.yiv7257244709attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv7257244709 .yiv7257244709attach div a 
{text-decoration:none;}#yiv7257244709 .yiv7257244709attach img 
{border:none;padding-right:5px;}#yiv7257244709 .yiv7257244709attach label 
{display:block;margin-bottom:5px;}#yiv7257244709 .yiv7257244709attach label a 
{text-decoration:none;}#yiv7257244709 blockquote {margin:0 0 0 
4px;}#yiv7257244709 .yiv7257244709bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7257244709 
.yiv7257244709bold a {text-decoration:none;}#yiv7257244709 dd.yiv7257244709last 
p a {font-family:Verdana;font-weight:700;}#yiv7257244709 dd.yiv7257244709last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7257244709 
dd.yiv7257244709last p span.yiv7257244709yshortcuts 
{margin-right:0;}#yiv7257244709 div.yiv7257244709attach-table div div a 
{text-decoration:none;}#yiv7257244709 div.yiv7257244709attach-table 
{width:400px;}#yiv7257244709 div.yiv7257244709file-title a, #yiv7257244709 
div.yiv7257244709file-title a:active, #yiv7257244709 
div.yiv7257244709file-title a:hover, #yiv7257244709 

Re: [firebird-support] Stored Procedures Execution Plan

[João Paulo Carvalho joao_paul...@yahoo.com [firebird-support]]

Hi.

I've been trying to skim over some possibilities to investigate possible 
limitations. Here it follow my results and test cases:
- If you call Stored Procedures (SP's) with EXECUTE PROCEDURE statements, plans 
are not available;- If you call SP's via SELECT FROM, you got expected 
statement plans, if this SP in turn DOESN'T call any other SP;- If you call 
SP's via SELECT FROM, you got partially expected statement plans, if this SP in 
turn CALL any other SP. In this case you won't get called SP plans, and if you 
exec called SP's with EXECUTE PROCEDURE, even the execution of called SP's will 
be omitted;

This is the test case script I used (executed with FlameRobin):
    --
    -- CREATING OBJECTS    --RECREATE TABLE test (c1 INT, c2 INT, c3 
INT);CREATE INDEX test_ix1 ON test (c1);CREATE INDEX test_ix2 ON test (c2);

SET TERM ^ ;RECREATE PROCEDURE test_01_selectable_sub_sp (c_cons INT)RETURNS 
(c_ret INT)ASBEGIN    SELECT c1    FROM test    WHERE c1 = :c_cons    INTO 
:c_ret;
    SELECT c1    FROM test    WHERE c2 = :c_ret    INTO :c_ret;
    SELECT c1    FROM test    WHERE c3 = :c_ret    INTO :c_ret;        
SUSPEND;END^SET TERM ; ^


SET TERM ^ ;RECREATE PROCEDURE test_03_selectable_sp (c_cons INT)RETURNS (c_ret 
INT)ASBEGIN    SELECT c_ret    FROM test_01_selectable_sub_sp(:c_cons)    INTO 
:c_ret;
    SELECT c_ret    FROM test_01_selectable_sub_sp(:c_ret)    INTO :c_ret;      
  SUSPEND;END^SET TERM ; ^

SET TERM ^ ;RECREATE PROCEDURE test_04_selectable_sp (c_cons INT)RETURNS (c_ret 
INT)ASBEGIN    EXECUTE PROCEDURE test_01_selectable_sub_sp(:c_cons)        
RETURNING_VALUES (:c_ret);
    EXECUTE PROCEDURE test_01_selectable_sub_sp(:c_ret)        RETURNING_VALUES 
(:c_ret);        SUSPEND;END^SET TERM ; ^



    --    -- EXECUTING SP'S    --
EXECUTE PROCEDURE test_01_selectable_sub_sp(1);EXECUTE PROCEDURE 
test_03_selectable_sp(1);EXECUTE PROCEDURE test_04_selectable_sp(1);-- Got:--   
   Plan not available.

SELECT * FROM test_01_selectable_sub_sp(1);-- Expected (something like):--    
PLAN (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL) Got:--    
PLAN (TEST INDEX (TEST_IX1))(TEST INDEX (TEST_IX2))(TEST NATURAL) 
Analysis:--    Ok
SELECT * FROM test_03_selectable_sp(1);-- Expected (something like):--    PLAN 
--         (test_01_selectable_sub_sp NATURAL)--                  (test INDEX 
(test_ix1))(test INDEX (test_ix2))(test NATURAL)--         
(test_01_selectable_sub_sp NATURAL)--                  (test INDEX 
(test_ix1))(test INDEX (test_ix2))(test NATURAL) Got:--    PLAN 
(TEST_01_SELECTABLE_SUB_SP NATURAL)(TEST_01_SELECTABLE_SUB_SP NATURAL) 
Analysis:--    Missing called stored procedure plans

SELECT * FROM test_04_selectable_sp(1);-- Expected (something like):--    PLAN 
--         (test_01_selectable_sub_sp NATURAL)--                  (test INDEX 
(test_ix1))(test INDEX (test_ix2))(test NATURAL)--         
(test_01_selectable_sub_sp NATURAL)--                  (test INDEX 
(test_ix1))(test INDEX (test_ix2))(test NATURAL) Got:--    PLAN 
(TEST_04_SELECTABLE_SP NATURAL) Analysis:--    Missing called stored 
procedure plans and indication of called stored procedures execution 


Att.João Paulo

 
  DE: "Gabriel Frones grfro...@gmail.com [firebird-support]" 

 Para: firebird-support@yahoogroups.com 
 Enviadas: Terça-feira, 31, Maio 2016 14:39:56
 Assunto: Re: [firebird-support] Stored Procedures Execution Plan
   
    It was sent and received... but this is working for me out of the box in FB 
2.5.4. I just write the select * from SP and ask for plan and it gives me the 
plan for the inside statements. Haven't tested on non-selectable SPs.
Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com 
[firebird-support]  escreveu:

     Hi.

Since this was my first post in this list, I'm not sure if it was sent.
If you already get it, or this is not the right place to discuss this kind of 
topic, I would be very grateful if you tell me where is.
Att.João Paulo

 
  DE: "joao_paul...@yahoo.com [firebird-support]" 

 Para: firebird-support@yahoogroups.com 
 Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
 Assunto: [firebird-support] Stored Procedures Execution Plan
 

 
    Hi.

I would like to know if it is possible to display the plan of stored procedure 
statements.
I'm interested in the plan of the statements inside the stored procedure (SP), 
not the plan just informing that I'm executing an SP.
We are running also selectable SP's.
Att.João Paulo
  

  

 

  #yiv5385826863 #yiv5385826863 -- #yiv5385826863ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5385826863 
#yiv5385826863ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5385826863 
#yiv5385826863ygrp-mkp #yiv5385826863hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv5385826863 

Re: [firebird-support] Issue with Database Cache Size on FB 3.0

[fabia...@itbizolutions.com.au [firebird-support]]

Dimitry

That is my concern, on FB 2.5 I was able to see the memory allocated as soon as 
the first connection is granted to the DB, but on FB 3 I am not seeing almost 
any memory been allocated what makes me believe that something is not right. 
However as more users connect and use the DB, the memory usage / allocation is 
growing, so something has either changed, or I have not configured properly the 
cfg files.

Regards,
Fabian

  - Original Message - 
  From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, June 01, 2016 3:38 PM
  Subject: Re: [firebird-support] Issue with Database Cache Size on FB 3.0


  01.06.2016 05:30, Alexey Kovyazin wrote:
  >
  > Firebird caches only actually used pages.

  But it allocates all the configured memory immediately, so the memory 
  usage (virtual/reserved bytes) should be visible anyway.


  Dmitry




  
  Posted by: Dmitry Yemanov 
  

  ++

  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

Re: [firebird-support] Issue with Database Cache Size on FB 3.0

[fabia...@itbizolutions.com.au [firebird-support]]

Hi Alexey

Thank you, I've noticed as the App was being used the DB cache was eating more 
memory, so I guess the config may be ok, it is a matter of loading more users 
online and check the memory

Cheers
Fabian

  - Original Message - 
  From: Alexey Kovyazin (ak) a...@ib-aid.com [firebird-support] 
  To: firebird-support@yahoogroups.com 
  Sent: Wednesday, June 01, 2016 12:30 PM
  Subject: Re: [firebird-support] Issue with Database Cache Size on FB 3.0





  Hi Fabian,

  Firebird caches only actually used pages.

  The small cache means that your application touches the small part of the 
database.


  --
  Regards,
  Alexey Kovyazin
  IBSurgeon
  PS It is difficult and wrong to give you any direct advice without all 
details in hands - it can lead to worse performance than with default 
parameters.


  среда, 01 июня 2016г., 04:16 +03:00 от Fabian Ernesto Chocron 
fabia...@itbizolutions.com.au [firebird-support] 
firebird-support@yahoogroups.com:


  
Hi All

We are having trouble setting up the database cache size on FB 3.0 
running on Windows 2008 R2 64 bits with 32 GB ram.

The problem we have is we cannot get the server to allocate the ram for 
the cache as we intend. With FB 2.54 we had the DB cache set very high, 
close to 1 GB per database, all running in RAM memory. With FB 3.0 we 
read it can allocate much more RAM to the cache, but it appears the 
server is allocating very small amount of Ram when the first user 
connects to the DB, and as we connect more users to the DB the ram 
consumption increases slowly. The setting we are playing with are:

On firebird.conf

FileSystemCacheThreshold = 0
FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB 
ram.)

On databases.conf

MyTestDB = c:\Temp\MyDb.fdb
{
DefaultDbCachePages = 458752
}

Any ideas what could be wrong? Or what settings would give us maximum 
RAM usage for the DB cache (we dont want file system cache meaning HDD 
cache, we want to have the DB in RAM for the purpose of reading the DB)

Cheers,
Fabian







  

[firebird-support] Re: Issue with Database Cache Size on FB 3.0

[hv...@users.sourceforge.net [firebird-support]]

---In firebird-support@yahoogroups.com,  wrote :
 
 ...
 
> The setting we are playing with are:
 > 
> On firebird.conf
 > 
> FileSystemCacheThreshold = 0
> FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB 
> ram.)

  Does you ever read comments describing this setting ? I can do it for you : 

"measured in % of total physical RAM". 

 
I.e. any value not in range [0..100] is wrong by definition. Read firebird.log, 
it should contain 
error message:

"Incorrect FileSystemCacheSize setting %d. Using default (30 percent)."

If you really want to limit file system cache by 50% of avalable RAM - set 
FileSystemCacheSize 
value to 50 (and restart Firebird), no need to calc in bytes...

Regards,
Vlad

  

Re: Re: [firebird-support] Stored Procedures Execution Plan

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
yes but this was "wrong"
Consider this:
inside procedure you have 5 different statements
and you run query and only two of them are reached - do you really need to see 
all 5?
 
Ok - now you got only "natural" word for procedure - i do not know if this is 
better now..
But if you need plan for procedure - "change it" to execute block and you got 
all statements plan
 
regards,
Karol Bieniaszewski
 
 
 
 
W dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones grfro...@gmail.com 
[firebird-support]  napisał:
 
It was sent and received... but this is working for me out of the box in FB 
2.5.4. I just write the select * from SP and ask for plan and it gives me the 
plan for the inside statements. Haven't tested on non-selectable SPs.
 
Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com 
[firebird-support]  escreveu:
   
Hi.
 
 
Since this was my first post in this list, I'm not sure if it was sent.
 
If you already get it, or this is not the right place to discuss this kind of 
topic, I would be very grateful if you tell me where is.
 
Att.
João Paulo
DE: "joao_paul...@yahoo.com [firebird-support]" 

Para: firebird-support@yahoogroups.com
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan
 
 
Hi.
 
 
I would like to know if it is possible to display the plan of stored procedure 
statements.
 
I'm interested in the plan of the statements inside the stored procedure (SP), 
not the plan just informing that I'm executing an SP.
 
We are running also selectable SP's.
 
Att.
 
João Paulo
 
 
 

 

[firebird-support] Re: Firebird 3 on Kubuntu 16.04

[peshk...@mail.ru [firebird-support]]

Have you tried binaries from SF?