[firebird-support] SQL slower after N executions?

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Hi,

I have a batch job that among other things executes a certain SQL query 
to load some data.

This query takes about 10-12 seconds the first ~320 times, but then 
suddenly takes ~45-50 seconds every time.

Any idea what could be causing this?

The query is rather long, see below. Firebird 3.0.4 on Windows Server 
2016 64 bit. App is ASP Framework using FirebirdSql.Data.FirebirdClient 
7.1.1.0.

I can't see any other operations slowing down at the same point in time. 
I can't see any particular change in the app's memory usage (it varies 
up and down both before and after the time of slowdown). I did try a 
change of Firebird's cache settings (more page buffers), but it didn't 
seem to have any effect (yes I did restart FB service to ensure changes 
took effect). Windows file system cache doesn't seem to hit the roof at 
this particular time, so I see no connection there.

Stopping the batch and restarting it, with the app still open, doesn't 
help, but restarting the entire app leads to the shorter execution time 
again.

Each execution of this SQL query is done in a new connection (not 
pooled). Before this SQL, the same connection is also used to empty the 
table "TmpFKExportId" and then fill it with some new data, a simple list 
of 250 bigints).

The app does do other DB work that spans multiple executions of this SQL 
query, using a separate connection. Those operations do not exhibit any 
noticable slowdown.

I'm at a loss... Any suggestions of things to try are most welcome!

SQL:

select distinct Upg."ECO_ID", Upg."ECO_TYPE"
from (
     select 'Företag' "Bärartyp", F."ECO_ID" "Bärare", Uhant."ECO_ID"
     from "Företag" F
     inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
     inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = 
F."Uppgiftshanterare"
     union
     select 'FöretagOmsättningsår' "Bärartyp", FtgOmsar."ECO_ID" 
"Bärare", Uhant."ECO_ID"
     from "Företag" F
     inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
     inner join "FöretagOmsättningsår" FtgOmsar on FtgOmsar."Företag" = 
F."ECO_ID"
     inner join "År" Ar on Ar."ECO_ID" = FtgOmsar."Omsättningsår"
     inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = 
FtgOmsar."Uppgiftshanterare"
     where Ar."Årtal" = (
     select max(Ar2."Årtal")
     from "FöretagOmsättningsår" FtgOmsar2
     inner join "År" Ar2 on Ar2.."ECO_ID" = FtgOmsar2."Omsättningsår"
     where FtgOmsar2."Företag" = F."ECO_ID"
   )
     union
     select 'ITmiljö' "Bärartyp", ITmilj."ECO_ID" "Bärare", Uhant."ECO_ID"
     from "Företag" F
     inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
     inner join "ITmiljö" ITmilj on ITmilj."ECO_ID" = F."ITmiljö"
     inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = 
ITmilj."Uppgiftshanterare"
     union
     select 'Befattningsinnehav' "Bärartyp", BI."ECO_ID" "Bärare", 
Uhant."ECO_ID"
     from "Företag" F
     inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
     inner join "Befattningsinnehav" BI on BI."Företag" = F."ECO_ID"
     inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = 
BI."Uppgiftshanterare"
     union
     select 'Person' "Bärartyp", P."ECO_ID" "Bärare", Uhant."ECO_ID"
     from "Företag" F
     inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
     inner join "Befattningsinnehav" BI on BI."Företag" = F."ECO_ID"
     inner join "Person" P on P."ECO_ID" = BI."Person"
     inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = 
P."Uppgiftshanterare"
   ) Uhant
inner join "Uppgiftshållare" UH on UH."Hanterare" = Uhant."ECO_ID"
inner join "Uppgift" Upg on Upg."Hållare" = UH."ECO_ID"
left join "Uppgiftsspärr" SpUhant on SpUhant."Uppgiftshanterare" = 
Uhant."ECO_ID" and Uhant."Bärartyp" in ('Epostadress', 'Person')
left join "Uppgiftsspärr" SpUH on SpUH."Uppgiftshållare" = UH."ECO_ID"
where Upg."TogsBort" is null
   and SpUhant."ECO_ID" is null
   and SpUH."ECO_ID" is null
   and not exists (
     select 1
     from "Uppgiftshållare" UH2
     where UH2."ECO_ID" = Upg."RelateradHållare"
   and (
     exists (
   select 1
   from "Uppgiftsspärr" Sp2
   where Sp2."Uppgiftshållare" = UH2."ECO_ID"
     )
     or exists (
   select 1
   from "Uppgiftsspärr" Sp2
   inner join "Uppgiftshanterare" Uhant2 on Uhant2."ECO_ID" = 
Sp2."Uppgiftshanterare"
   where Uhant2."ECO_ID" = UH2."Hanterare"
     )
   )
   )

Mvh,
Kjell



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

ODP: [firebird-support] SQL slower after N executions?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

There are many possibilities without access i can only hint you:
Look at MON$Tranasctions maybe you have active one which stop garbage collecion.
Look also at sort buffer setting if firebird.conf
Look at settings about buffers in database itself (gfix -h show you value).
Look also at automatic sweep settings (also gfix -h show you value).
Update Firebird to most recent version official is 3.0.5 but i use most recent 
snapshot without problems.

More can be tell after some details provided.

regards,
Karol Bieniaszewski

Re: [firebird-support] SQL slower after N executions?

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

I don't know why things slow down, Kjell, if the batch job updates records,
I guess it could be related to there being lots of versions of some records
and that garbage are only collected after your application ends (it sounds
plausible if transactions are still open when you "Stopping the batch and
restarting it, with the app still open". Maybe you should check if there's
a large gap between oldest and next transaction when you stop the batch
(similar to Karols first recommendation)? Another thing that would not harm
is to change from

  where Ar."Årtal" = ( select max( Ar2."Årtal" )
  from "FöretagOmsättningsår" FtgOmsar2
  inner join "År" Ar2 on Ar2.."ECO_ID" =
FtgOmsar2."Omsättningsår"
  where FtgOmsar2."Företag" = F."ECO_ID" )

to
 where not exists ( select 1
from "FöretagOmsättningsår" FtgOmsar2
inner join "År" Ar2 on Ar2.."ECO_ID" =
FtgOmsar2."Omsättningsår"
where FtgOmsar2."Företag" = F."ECO_ID"
  and Ar."Årtal" < Ar2."Årtal" )

in the second part of your query. This will probably not help with the
slowdown, but it should not be slower than your max( and it may be slightly
quicker (although the table names "FöretagOmsättningsår" and "År" does
indicate these are tiny tables).

Set

ons. 29. apr. 2020 kl. 13:44 skrev Kjell Rilbe
kjell.ri...@marknadsinformation.se [firebird-support] <
firebird-support@yahoogroups.com>:

> Hi,
>
> I have a batch job that among other things executes a certain SQL query
> to load some data.
>
> This query takes about 10-12 seconds the first ~320 times, but then
> suddenly takes ~45-50 seconds every time.
>
> Any idea what could be causing this?
>
> The query is rather long, see below. Firebird 3.0.4 on Windows Server
> 2016 64 bit. App is ASP Framework using FirebirdSql.Data.FirebirdClient
> 7.1.1.0.
>
> I can't see any other operations slowing down at the same point in time.
> I can't see any particular change in the app's memory usage (it varies
> up and down both before and after the time of slowdown). I did try a
> change of Firebird's cache settings (more page buffers), but it didn't
> seem to have any effect (yes I did restart FB service to ensure changes
> took effect). Windows file system cache doesn't seem to hit the roof at
> this particular time, so I see no connection there.
>
> Stopping the batch and restarting it, with the app still open, doesn't
> help, but restarting the entire app leads to the shorter execution time
> again.
>
> Each execution of this SQL query is done in a new connection (not
> pooled). Before this SQL, the same connection is also used to empty the
> table "TmpFKExportId" and then fill it with some new data, a simple list
> of 250 bigints).
>
> The app does do other DB work that spans multiple executions of this SQL
> query, using a separate connection. Those operations do not exhibit any
> noticable slowdown.
>
> I'm at a loss... Any suggestions of things to try are most welcome!
>
> SQL:
>
> select distinct Upg."ECO_ID", Upg."ECO_TYPE"
> from (
>  select 'Företag' "Bärartyp", F."ECO_ID" "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> F."Uppgiftshanterare"
>  union
>  select 'FöretagOmsättningsår' "Bärartyp", FtgOmsar."ECO_ID"
> "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "FöretagOmsättningsår" FtgOmsar on FtgOmsar."Företag" =
> F."ECO_ID"
>  inner join "År" Ar on Ar."ECO_ID" = FtgOmsar."Omsättningsår"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> FtgOmsar."Uppgiftshanterare"
>  where Ar."Årtal" = (
>  select max(Ar2."Årtal")
>  from "FöretagOmsättningsår" FtgOmsar2
>  inner join "År" Ar2 on Ar2.."ECO_ID" = FtgOmsar2."Omsättningsår"
>  where FtgOmsar2."Företag" = F."ECO_ID"
>)
>  union
>  select 'ITmiljö' "Bärartyp", ITmilj."ECO_ID" "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "ITmiljö" ITmilj on ITmilj."ECO_ID" = F."ITmiljö"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> ITmilj."Uppgiftshanterare"
>  union
>  select 'Befattningsinnehav' "Bärartyp", BI."ECO_ID" "Bärare",
> Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "Befattningsinnehav" BI on BI."Företag" = F."ECO_ID"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> BI."Uppgiftshanterare"
>  union
>  select 'Person' "Bärartyp", P."ECO_ID" "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "Befattningsinnehav" BI on BI."Företag" 

[firebird-support] Firebird 2.5 and CentOS 7.7

['P-Soft - Fabio Codebue' f.code...@p-soft.biz [firebird-support]]

We are trying to install Firebird 2.5.9 Super Server on  Linux  Server 
CentOS (Linux release 7.7.1908 (Core))
IT doen't start because it can't load moduloe 
/usr/lib64/firebird/intl/fbintl.so


Into to the log file we read:

Can't load INTL module '/usr/lib64/firebird/intl/fbintl.so'

Some ideas?

Fabio Codebue


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz 



pec: amministrazi...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9

Re: [firebird-support] Firebird 2.5 and CentOS 7.7

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

29.04.2020 15:40, 'P-Soft - Fabio Codebue' f.code...@p-soft.biz 
[firebird-support] wrote:
> Can't load INTL module '/usr/lib64/firebird/intl/fbintl.so'
> 
> Some ideas?

   Check dependencies using ldd.

-- 
   WBR, SD.






++

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: ODP: [firebird-support] SQL slower after N executions?

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Thanks,

I tried firing up Gregor Kobler's FBMonitor (similar to the old but dead 
Sinatica Monitor).

I found that OAT is 222085103 while OIT and OST (what's that?) is 
261140768 and growing. I assume the OAT must have got stuck somehow 
quite a while back, since it's so far behind the others. The machine was 
rebooted for upgrades rather recently, and I can't see any stuck 
transactions, so the OAT must be a zombie one.

No auto sweep (interval set to 0), so I'm trying a manual sweep now. 
Guess I should schedule that weekly or something.

I couldn't see anything interesting happening with the trans numbers 
when I exited the batch app. Will restart it after the manual sweep and 
see if anything interesting shows up. The slowdown, if still there, 
would probably happen after I go to sleep, so I'll have to follow up 
tomorrow.

Other info about the database, in case it says anything:

Page size: 16384
ODS: 12.0
Page buffers: 614400 (roughly 9.5 Gbyte)
Sweep interval: 0 (like I mentioned)
Forced writes: off (yes, I know the risks)

 From databases.conf:
DefaultDbCachePages = 600K (same as Page buffers above, of course)
FileSystemCacheThreshold = 1000K

 From firebird.conf:
FileSystemCacheSize = 40
TempCacheLimit = 4G
MaxUnflushedWrites = 100
MaxUnflushedWriteTime = 60
ServerMode = Super

All other performance params default. Some auth and access restriction 
params are non default, but I assume that's irrelevant.

System has 40 Gbyte RAM and the temp disks have 70+ Gbyte free.

Regards,
Kjell

Den 2020-04-29 kl. 14:00, skrev Karol Bieniaszewski 
liviusliv...@poczta.onet.pl [firebird-support]:

> Hi
>
> There are many possibilities without access i can only hint you:
>
> Look at MON$Tranasctions maybe you have active one which stop garbage 
> collecion.
>
> Look also at sort buffer setting if firebird.conf
>
> Look at settings about buffers in database itself (gfix -h show you 
> value).
>
> Look also at automatic sweep settings (also gfix -h show you value).
>
> Update Firebird to most recent version official is 3.0.5 but i use 
> most recent snapshot without problems.
>
> More can be tell after some details provided.
>
> regards,
>
> Karol Bieniaszewski
>
> 


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

Re: [firebird-support] Firebird 2.5 and CentOS 7.7

[Emil Totev em...@tot-consult.com [firebird-support]]

This is a known problem with the EPEL rpms.
Workarounds include using the rpm packages for superclassic, which don't
have the problem, or installing the project-provided rpms (which install
under /opt). It *might* also work if you replace that specific file with
the one from the EPEL rpm from 2.5.7 (the problem appeared in 2.5.8).

Good luck!

ODP: ODP: [firebird-support] SQL slower after N executions?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Simply run query with join two tables MON$ATATACHEMENTS and MON$TRANSACTIONS – 
you can also be interested with joining with MON$STATEMENTS
And then you can see which attachement consume this transaction (you can 
retrive IP port and proces ID (PID)

Pozdrawiam,
Karol Bieniaszewski

Re[2]: [firebird-support] Firebird 2.5 and CentOS 7.7

['P-Soft - Fabio Codebue' f.code...@p-soft.biz [firebird-support]]

Dmitry

these are results of ldd

[root@server intl]# ldd /usr/lib64/firebird/intl/fbintl.so
linux-vdso.so.1 =>  (0x7fff8a979000)
libicuuc.so.50 => /usr/lib64/libicuuc.so.50 (0x7fedb5de9000)
libicudata.so.50 => /usr/lib64/libicudata.so.50 (0x7fedb4816000)
libicui18n.so.50 => /usr/lib64/libicui18n.so.50 (0x7fedb4417000)
libdl.so.2 => /usr/lib64/libdl.so.2 (0x7fedb4213000)
libncurses.so.5 => /usr/lib64/libncurses.so.5 (0x7fedb3fec000)
libtinfo.so.5 => /usr/lib64/libtinfo.so.5 (0x7fedb3dc2000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x7fedb3abb000)
libm.so.6 => /usr/lib64/libm.so.6 (0x7fedb37b9000)
libgcc_s.so.1 => /usr/lib64/libgcc_s.so.1 (0x7fedb35a3000)
libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x7fedb3387000)
libc.so.6 => /usr/lib64/libc.so.6 (0x7fedb2fb9000)
/lib64/ld-linux-x86-64.so.2 (0x7fedb6439000)
[root@server intl]# ldd /usr/lib64/firebird/intl/fbintl
linux-vdso.so.1 =>  (0x7fff085d7000)
libicuuc.so.50 => /usr/lib64/libicuuc.so.50 (0x7f8ad16ec000)
libicudata.so.50 => /usr/lib64/libicudata.so.50 (0x7f8ad0119000)
libicui18n.so.50 => /usr/lib64/libicui18n.so.50 (0x7f8acfd1a000)
libdl.so.2 => /usr/lib64/libdl.so.2 (0x7f8acfb16000)
libncurses.so.5 => /usr/lib64/libncurses.so.5 (0x7f8acf8ef000)
libtinfo.so.5 => /usr/lib64/libtinfo.so.5 (0x7f8acf6c5000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x7f8acf3be000)
libm.so.6 => /usr/lib64/libm.so.6 (0x7f8acf0bc000)
libgcc_s.so.1 => /usr/lib64/libgcc_s.so.1 (0x7f8aceea6000)
libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x7f8acec8a000)
libc.so.6 => /usr/lib64/libc.so.6 (0x7f8ace8bc000)
/lib64/ld-linux-x86-64.so.2 (0x7f8ad1d3c000)


Fabio Codebue


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz 



pec: amministrazi...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9



-- Messaggio originale --
Da: "Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]" 


A: firebird-support@yahoogroups.com
Inviato: 29/04/2020 16:11:44
Oggetto: Re: [firebird-support] Firebird 2.5 and CentOS 7.7


29.04.2020 15:40, 'P-Soft - Fabio Codebue' f.code...@p-soft.biz 
[firebird-support] wrote:

 Can't load INTL module '/usr/lib64/firebird/intl/fbintl.so'

 Some ideas?


   Check dependencies using ldd.

--
   WBR, SD.






++

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] Firebird 2.5 and CentOS 7.7

[Philippe Makowski pmakow...@ibphoenix.fr [firebird-support]]

Are you using EPEL package ?
or Firebird project binaries ?

There is a compilation issue in Epel package since 2.5.8, and nobody
understand exactly what
fbintl from 2.5.7 package is ok

cf https://bugzilla.redhat.com/show_bug.cgi?id=1636177
and http://tracker.firebirdsql.org/browse/CORE-5925

but you can use firebird 3
https://copr.fedorainfracloud.org/coprs/makowski/firebird/