Re: [firebird-support] Database/Table Corruption

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

before you try FirstAID, download IBPump and try to pump all data to the 
new database with the same structure.



Regards,
Alexey Kovyazin
IBSurgeon


On 27.12.2019 22:49, Hugo Eyng hugoe...@msn.com [firebird-support] wrote:


Hello.

FB 2.5.7
Windows 10 64 bits
Page size 4096

I am facing some dificulties to backup and restore a corrupted DB.  I 
searched the Internet to find some help and tried everthing I read.


I already tried "gfix -full - mend" and did not work.

gbak  -B  -V -user SYSDBA -password masterkey MYDB.FDB MYDB.FBK

.
.

gbak:46 records written
gbak: ERROR:internal Firebird consistency check (decompression overran 
buffer (179), file: sqz.cpp line: 239)

gbak: ERROR:gds_$receive failed
gbak:Exiting before completion due to errors
gbak: ERROR:internal Firebird consistency check (can't continue after 
bugcheck)



I already dropped the table that causes this message but than the 
error occurs ahead.


...

.
.
gbak:writing table constraints
gbak:writing constraint INTEG_1
gbak:writing constraint INTEG_2
gbak:writing constraint INTEG_3
gbak:writing constraint INTEG_4
gbak:writing constraint INTEG_5
gbak:writing constraint INTEG_6
gbak:writing constraint INTEG_7
gbak:writing constraint INTEG_8
gbak:writing constraint INTEG_9
gbak:writing constraint INTEG_10
gbak:writing constraint INTEG_11
gbak:writing constraint INTEG_12
gbak:writing constraint INTEG_13
gbak:writing constraint INTEG_14
gbak:writing constraint INTEG_15
gbak:writing constraint INTEG_16
gbak:writing constraint INTEG_17
gbak: ERROR:internal Firebird consistency check (decompression overran 
buffer (179), file: sqz.cpp line: 239)

gbak:Exiting before completion due to errors
gbak: ERROR:internal Firebird consistency check (can't continue after 
bugcheck)


Any help will be apreciated.

Atenciosamente,

+ + Hugo Eyng + +

Re: [firebird-support] Re: High write access on disk

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

For predictable load reads/fetches is very useful metric, but in case of 
spikes like Thomas has - 96 connections to 1300, it will be far less useful.

Regards,
Alexey

On 13.11.2019 14:50, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] wrote:
> 13.11.2019 12:41, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote:
>> we also started with Page Buffers = 25%  RAM and increased it step by step 
>> (and still
>> continue to work with fine tuning).
> Isn't it better to use cache hit ratio to make the decision about its 
> growth?
>
>







++

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] Re: High write access on disk

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

Yes, better use default - pagecache in terms of Linux is different from 
Page Buffers/Page cache of Firebird.


Regards,
Alexey


On 13.11.2019 15:10, kragh.tho...@yahoo.com [firebird-support] wrote:


Hey Alexey

Thanks for the answer, I only have one question left, do I need to 
adjust linux page cache(vm.pagecache) according to amount of RAM 
allocated for DefaultDBCachePages, so that memory is not over 
"subscribed"? Eg. if DefaultDBCachePages is increased should i then 
decrease vm.pagecache? Or should I use vm.pagecache default values and 
have Linux work it out?

Re: [firebird-support] Re: High write access on disk

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Thomas,

Ok, good to know that you are prepared for the migration! FB3 with 
proper migration and config certainly will increase the overall performance.


In my presentation regarding Linux I spoke about specific case which is 
under everyday control, and in our long way, we also started with Page 
Buffers = 25%  RAM and increased it step by step (and still continue to 
work with fine tuning).


Also, there we have 320Gb and smooth predictable increase of connections 
during the day, and you have much more intensive spikes, so, better be 
more conservative - if you will see no problems with 25% of RAM, 
increase to 40-50-70, week by week.


Regards,
Alexey




On 13.11.2019 13:59, kragh.tho...@yahoo.com [firebird-support] wrote:


Hey Alexey

Its not that I see high traffic to /tmp as a problem, I was just 
worried that it was becoming a bottleneck in our system. Great to 
know that HQbird has a way of tracking these qureys, I have already 
been looking into HQbird especially because of prepared statements.


Regarding the upgrade to Fb 3 SuperServer, a complete restore of 
production database to Fb 3 have already been made in our dev 
envioment last week without problems, and for almost a year dev, test 
and preprod have been running Fb 3, so i am quite comfortable in that 
regard. We have also uses FB TraceManager to weed out bad queries, and 
procedures and triggers recompile without errors.
My only concern is that I somehow configures Fb 3 in a way that 
results in worse performance that our current 2.5 installation.


The server is a dedicated Firebird server with a single database, does 
this change your recommendation to allocate 25% RAM for 
DefaultDBCachePages? My initial estimate was based on your 
presentation at this years Firebird conference(20_tuninglinux.pdf page 28)


Thomas Kragh

Re: [firebird-support] Re: High write access on disk

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Thomas,

My advice was only to fix the obvious mistake regarding the TempCacheLimit.
In general, I don't see a problem in high traffic  to /tmp, and I don't 
understand why do you think sorting queries produce the problem - and 
even if they produce, the first attempt would be to identify such 
queries with TempSpaceLogThreshold (it works in HQbird only) and then 
fix them with index/disabling index, restructuring it, etc.


As another short advice:
1) I need to say that tuning of 2.5 SuperClassic and 3.0 SuperClassic is 
different, and, at least, I would recommend to a) put 
DefaultDBCachePages to databases.conf b) allocate 25% of RAM at the 
first step, then increase it by 10-20%.


2) Migration 2.5 - > 3.0 requires at least clean metadata (isql -x > 
metadata.sql on 2.5,  isql -i metadata.sql on 3.0 should not give 
errors), I hope you tested it, and, also, 3.0.4 have different 
optimization, so some queries can start to work slower, and thorough 
testing is required.


Regards,
Alexey Kovyazin
IBSurgeon



On 13.11.2019 11:56, kragh.tho...@yahoo.com [firebird-support] wrote:


Hey Alexey

Thanks for the advise, I changed TempCacheLimit to 21, last 
night and it looks like it had a positive effect on the disk queue 
size on sda. I have read a lot about Firebird tuning however I have 
never come across that limit on fb 2.5


https://pasteboard.co/IGsU650A.png
The green line is when I updated the config. The spikes around 
midnight is due to backup/restore. The queue size has gone from around 
1 to 0,1-0,2 during office hours.


However the amount of wirte access to sda, did not change after the 
config change. Perhaps the sensor that we use monitors the writes 
Firebird makes when firebird sorts response?

https://pasteboard.co/IGt93ec.png


To accommodate further growth in users I am planning an upgrade to 
Firebird 3.0.4(SuperServer) this weekend. The server will be the same, 
however with a ram upgrade to 192Gb. I am planning to use the 
following config, do you or anyone else see any problems?


Firebird config
DefaultDbCachePages = 6000K #96Gb (page size 16Kb)
FileSystemCacheThreshold = 2K
TempCacheLimit = 10G   #10Gb
TempBlockSize = 2M
LockMemSize = 116117248
LockHashSlots = 40099

OS config

Vm.pagecache = 30

Vm.swappiness = 10

vm.min_free_kbytes = 1048576

vm.max_map_count=25

fs.file-max=2097152

net.core.somaxconn = 4096

net.core.netdev_max_backlog = 65536

net.core.optmem_max = 25165824


Thomas Kragh

Re: [firebird-support] High write access on disk

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Thomas,

Seems like your system generates a lot of sort files - the problem can 
be that TempCacheLimit in Firebird 2.5 cannot be more than 2Gb -1.

When you set it to something between 2 and 4Gb, it means 0.

So, set something like
TempCacheLimit=21

Consider to tune other parameters from our optimized configurations
https://ib-aid.com/en/optimized-firebird-configuration/

and worth to look through
https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/
https://ib-aid.com/en/articles/23-more-ways-to-speed-up-firebird/

You provided lockprint from the moment when you had only 96 users, and 
peak seems to be ~1300 users, so it is not very useful :)


Regards,
Alexey

On 13.11.2019 0:45, kragh.tho...@yahoo.com [firebird-support] wrote:


Hey

Where I work we run a website with at growing number of users, over a 
period of two months we have seen Firebird slow down at peek hours, 
where the number of concurrent website users is about 6.000. Usually 
there is about 250 attachments, however when the slowdown occurs the 
number rises to 800-1000 in about 5-10 seconds. This is what to be 
expected if query speed slows down.



During my investigation I found out that there is a lot writing to the 
root partition(sda) where /tmp is located. both under normal load and 
more so when slowdown occurs. Queue size for sda rises above 1 during 
slowdown. Read/write operations to sdb where the database is located 
seems normal and is a fraction of operations on sda.



Is this high number of write operations normal for Firebird, or do I 
need to tune some Firebird or OS settings?


Is it perhaps because TempCacheLimit is too low, and Firebird uses 
disk for sorting, and OS is forced to flush this data to disk because 
almost all memory is used for filecaching?




System information:

CentOs 7

16 core virtual machine with 128Gb of Ram

3Par 8200 SAN (6 SSD about 75.000 IOPS)


Server is dedicated to one database.


Firebird 2.5.8 (superclassic)

TempCacheLimit = 4294967296

DefaultDbCachePages = 2048

LockMemSize = 5048576

LockHashSlots = 30011


Database size: 155Gb


[user@dbserver]$ free -m

total   usedfree  shared  buff/cache   available

Mem:   128765   3727912  4147 124125  120569

Swap:  0 0  0



fb_lock_print - not under load:

LOCK_HEADER BLOCK

Version: 145, Active owner:  0, Length: 116117248, Used: 
111204848


Flags: 0x0001

Enqs: 17690670118, Converts: 74244796, Rejects: 20098430, 
Blocks: 413686610


Deadlock scans:  0, Deadlocks:  0, Scan interval:  10

Acquires: 20215919905, Acquire blocks: 1290646628, Spin count:   0

Mutex wait: 6.4%

Hash slots: 30011, Hash lengths (min/avg/max): 0/   0/   9

Remove node:  0, Insert queue:  0, Insert prior:  0

Owners (96):forward: 26814936, backward: 24959608

Free owners (1183): forward: 61820848, backward: 88775232

Free locks (148866):forward: 71783848, backward: 1184592

Free requests (1442650):forward: 11030120, backward: 
30750136


Lock Ordering: Enabled


Best regards

Thomas Kragh

Re: [firebird-support] Firebird 3.0 performance, Windows 10 vs Mint, big different

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,


Don't know, if this means file cached work or not.


It works.

I recommend to do several experiments with different Pafe buffers and 
FileSystemCacheThreshold values (Buffers > FileSystemCacheThreshold , 
Buffers < FileSystemCacheThreshold ), and check RAMMap. Try to do it 
with and without restart of  the computer.




If file cached worked, then there is something else that make Firebird 
runs slower on Windows 10.




Windows 10 is a desktop OS, and it demonstrates slowness in so many 
cases, caused by so many reasons, so I would not use it as a server, 
until there is no other choice.
I personally experienced slowness on one of my own desktops, and perfect 
performance on the another one. Better just stay out from this lottery.


Try to make experiments with Windows Server, it will make more sense.

Regards,
Alexey Kovyazin
IBSurgeon




Anto

Re: [firebird-support] How to know if FileSystemCacheThreshold works?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

Download RAMMap from Sysinternals (Micorosft), and see if database file 
is in list of cached files.


Regards,
Alexey Kovyazin
IBSurgeon

On 22.06.2019 11:42, trsk...@yahoo.com [firebird-support] wrote:


Hi guys,

I have FB 3.04 SS in windows 10.

I have a database with these setting on database.conf

my_dba = C:\Dba\db.fdb
{
FileSystemCacheThreshold = 393216
LockMemSize = 16M
LockHashSlots = 30011
TempCacheLimit = 2048M
TempBlockSize = 2M
FileSystemCacheSize = 50
}

Database page size : 16834
Database pagebuffers : 131072

Size of database file is about 8GB. Total Windows RAM, 16GB, free RAM, 
more than 8GB.


I run the same query via flamerobin about three time, then looked on 
WIndows Task Manager and each time the query was running, the disk 
kept spinning.


Does it means that Firebird's FileSystemCache doesn't works? How to 
know if it works?


Thanks in advance.

Re: [firebird-support] Understanding Firebird Security

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,


So, I did understand  correctly - anyone can open any Firebird database and view the data 

(unless it happens to be encrypted).

No, not exactly, - you can protect from scenario with replacing security 
database with a change of database owner (create database under MyUser).



I am rather shocked by  that.


Why? Any database can be opened if you have access to the database file 
- there are plenty of recovery tools for MSSQL, PostrgeSQL, etc, to dump 
the contents of the database.



Regards,
Alexey Kovyazin
IBSurgeon


On 20.05.2019 13:32, sbai...@mutualconsultants.ltd.uk [firebird-support] 
wrote:


Alexey, thank you for the extremenly quick response.


So, I did understand correctly - anyone can open any Firebird database 
and view the data (unless it happens to be encrypted).


I am rather shocked by that.

Steve Bailey

Re: [firebird-support] Understanding Firebird Security

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,


This is the point where I confess to being confused. I presume I am 
wrong but it looks like any Firebird database has a "public back 
door". What stops me taking a copy of SecretDatabase.fdb and 
connecting to it on my own Firebird installation?




If you have access to Firebird server and to the database file, you can 
get a copy of IBSurgeon FirstAID (recovery tool) and view data without 
any password :)


To protect database file in such situation (for example, if you 
distribute it to the untrusted environment), consider to use encryption 
- there are ready to use third-party plugins available or you can build 
your own.


Regards,
Alexey Kovyazin
IBSurgeon




Steve Bailey

Re: [firebird-support] "12. Don't forget to enable file cache with big Firebird cache page"

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Karol,

This advice is based on our experience - disabling file cache for big 
databases (definitely larger than RAM size) leads to slowness.

I don't have explanation for this on the theoretical level.

If you have real-world example when Firebird works faster without file 
cache, please provide details about it.


Regards,
Alexey Kovyazin
IBSurgeon





On 09.01.2019 10:57, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:

Hi,
i have read on http://firebirdsql.org/
article "23 More Ways To Speed Up Firebird".
Can you explain me point "12. Don't forget to enable file cache with 
big Firebird cache page".

I can imagine difference only in 2 situations.
1. When all attachments are disconnected and connected again.
Then yes, Windows cache can be more than benefit here - but we can 
turn on linger to achive near the same.

2. Database is bigger than Firebird cache.
Than using also system cache can increase performance as there is a 
chance that Windows will cache some portion of database not cached by 
FB itself.
And Windows can decide then, if it neeed cache other files or some 
portion of DB.
Is there any benefit of enabling system cache if Firebird cache is set 
to bigger value than database itself?

I see oposite performance in such situation.
But difference is marginally from 0 to 5% difference - than i do not 
know if there is any difference or i see some other process involved.

Arethere other situations when enabling system cache will benefit also?
regards,
Karol Bieniaszewski

Re: [firebird-support] Firebird vs. PostgreSQL

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Dalton,

I am totally agree with you - all these features would be definitely good.

Regards,
Alexey




Hi Alexey,

Just my 2cents from someone who has not had time to review the code 
and could not find a breakdown of the firebird code structure in a 
very cursory search.



> (1) Schema support as per oracle not microsoft (although you can 
simulate MS style schema with a proper implementation)

>   agree, we need schema support

If we have a system table that has the columns (parent, child, type, 
internal_reference) and had it hold the user facing table names, while 
referencing a system generated name (the internal reference) that is 
used in blr and other tasks, then most of the work would be adding a 
name lookup function to the parser which uses a recursive CTE to do 
the name matching.  This would be the least amount of work and would 
mostly involve the parser.



> (2) Cube SQL support
>  would be good, but not critical,

This is hard and can wait, because it is mostly making the 
api's/result sets be bug for bug compatible with the MS platforms


> (3) Linked Databases in addition to the psql connections
>  would be good, but not critical

This can wait, but, I do believe it is critical in many work places.

> (4) Jobs with Schedular
>  would be good, not critical, can be implemented with bundled 
product (not core)


All this needs is some system tables to be created and parser support 
added.  The actual implementation can be written by third parties as 
long as the tables exist and are documented.


> (5) Replication
> Master-Slave is already in HQbird for 2.5 and 3.0, and will be in 
community Firebird 4.0

> Master-Master is the next step for 5.0.

Great news!

>  (6) High Available Clustering
>  agree, it will be based on top of the replication.
Sounds like this would be in 5.0

Overall, things like JSON can be done with packages, stored procs and 
psql functions, not needing anything from the developers, but the 
schema and schedular support could be covered by someone who knows the 
parser well.


Best regards

Dalton





On Wed, 7 Nov 2018 at 14:32, Alexey Kovyazin a...@ib-aid.com 
 [firebird-support] 
> wrote:


Hello Dalton,


>Where do you see the list of features that are destined to be in 5?

There is no list of features yet. However, as a member of
Technical Task Group, I will work to include into 5.0 the 3 main
features: multi-master replication, partitioning/table spaces,
schema support.



>I have been a supporter of firebird for years but there are
several things that hold it back

My opinion:

(1) Schema support as per oracle not microsoft (although you can
simulate MS style schema with a proper implementation)

agree, we need schema support

(2) Cube SQL support
would be good, but not critical

(3) Linked Databases in addition to the psql connections
would be good, but not critical

(4) Jobs with Schedular
would be good, not critical, can be implemented with bundled
product (not core)

(5) Replication
Master-Slave is already in HQbird for 2.5 and 3.0, and will be in
community Firebird 4.0
Master-Master is the next step for 5.0.

(6) High Available Clustering
agree, it will be based on top of the replication.

Regards,
Alexey



On Wed, 7 Nov 2018 at 12:19, Alexey Kovyazin a...@ib-aid.com
 [firebird-support]
mailto:firebird-support@yahoogroups.com>> wrote:

Thank you!

In my opinion, Firebird will close the gap with PG in main
features in 2
years, with FB5.0 version.

Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com 

On 07.11.2018 19:58, 'Martijn Tonies (Upscene Productions)'
m.ton...@upscene.com 
[firebird-support] wrote:
> Hello Alexey,
>
>> What about compilation of stored procedures - is it true
that it is
>> possible to create SP with errors, and they appear only
when it will be
>> invoked?
> Unsure, will try tomorrow. Do note, that for example, Oracle
allows you to
> 'save' or 'compile' invalid stored routines > they are
stored in the
> database
> anyway and you can ask for a list of errors. In Database
Workbench, these
> routines are shown in red as to signal they are currently
invalid.
>
>> Also, about checking the parameters - is it true that you
can create
>> stored procedure which invokes other stored procedure with
wrong list of
>> parameters, and it will give error only at the execution time?
> I'll let you know.
>
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> 

Re: [firebird-support] Firebird vs. PostgreSQL

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello All,

As there are many people involved and truly interested in further 
Firebird development in this thread, I would like to remind, that 
Firebird is funded by Firebird Foundation sponsors and members, and 
there is always ability to boost development with money sponsorship and 
donations.

If you want to help with it, consider to become a sponsor
https://www.firebirdsql.org/en/how-to-become-a-sponsor/

For example, with extra USD$36k/year FF could allocate the additional 
grant for C++ developer, to implement more features in the short timeframe.
36k  = 3 Platinum Sponsors (12k/year = 1k per month ) or 6 Gold (6k/year 
= 500 usd per month) or 12 Silver (3k) - or 36 Bronze sponsors (1k/year 
= 80 USD/month).

The companies - Platinum sponsors have access to Task Technical Group 
and can influence and support their desired features in Firebird, other 
sponsors have a voting membership in Firebird Foundation (which can be 
bought separately by USD$300/year).

Regards,
Alexey Kovyazin
IBSurgeon

Re: [firebird-support] Firebird vs. PostgreSQL

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Dalton,


>Where do you see the list of features that are destined to be in 5?

There is no list of features yet. However, as a member of Technical Task 
Group, I will work to include into 5.0 the 3 main features: multi-master 
replication, partitioning/table spaces, schema support.



>I have been a supporter of firebird for years but there are several 
things that hold it back


My opinion:

(1) Schema support as per oracle not microsoft (although you can 
simulate MS style schema with a proper implementation)


agree, we need schema support

(2) Cube SQL support
would be good, but not critical

(3) Linked Databases in addition to the psql connections
would be good, but not critical

(4) Jobs with Schedular
would be good, not critical, can be implemented with bundled product 
(not core)


(5) Replication
Master-Slave is already in HQbird for 2.5 and 3.0, and will be in 
community Firebird 4.0

Master-Master is the next step for 5.0.

(6) High Available Clustering
agree, it will be based on top of the replication.

Regards,
Alexey



On Wed, 7 Nov 2018 at 12:19, Alexey Kovyazin a...@ib-aid.com 
 [firebird-support] 
> wrote:


   Thank you!

   In my opinion, Firebird will close the gap with PG in main features
   in 2
   years, with FB5.0 version.

   Regards,
   Alexey Kovyazin
   IBSurgeon www.ib-aid.com 

   On 07.11.2018 19:58, 'Martijn Tonies (Upscene Productions)'
   m.ton...@upscene.com 
   [firebird-support] wrote:
> Hello Alexey,
>
>> What about compilation of stored procedures - is it true that it is
>> possible to create SP with errors, and they appear only when it
   will be
>> invoked?
> Unsure, will try tomorrow. Do note, that for example, Oracle
   allows you to
> 'save' or 'compile' invalid stored routines > they are stored in the
> database
> anyway and you can ask for a list of errors. In Database
   Workbench, these
> routines are shown in red as to signal they are currently invalid.
>
>> Also, about checking the parameters - is it true that you can create
>> stored procedure which invokes other stored procedure with wrong
   list of
>> parameters, and it will give error only at the execution time?
> I'll let you know.
>
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Regards,
> Alexey
>
>
>
> On 07.11.2018 19:27, 'Martijn Tonies (Upscene Productions)'
> m.ton...@upscene.com 
   [firebird-support] wrote:
>> Yes, it's true and it's rather annoying when, for example,
   developing an
>> interactive database development tool;)
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>



   

   

   ++

   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


   firebird-support-fullfeatu...@yahoogroups.com
   

Re: [firebird-support] Firebird vs. PostgreSQL

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Thank you!

In my opinion, Firebird will close the gap with PG in main features in 2 
years, with FB5.0 version.

Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com

On 07.11.2018 19:58, 'Martijn Tonies (Upscene Productions)' 
m.ton...@upscene.com [firebird-support] wrote:
> Hello Alexey,
>
>> What about compilation of stored procedures - is it true that it is
>> possible to create SP with errors, and they appear only when it will be
>> invoked?
> Unsure, will try tomorrow. Do note, that for example, Oracle allows you to
> 'save' or 'compile' invalid stored routines > they are stored in the
> database
> anyway and you can ask for a list of errors. In Database Workbench, these
> routines are shown in red as to signal they are currently invalid.
>
>> Also, about checking the parameters - is it true that you can create
>> stored procedure which invokes other stored procedure with wrong list of
>> parameters, and it will give error only at the execution time?
> I'll let you know.
>
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Regards,
> Alexey
>
>
>
> On 07.11.2018 19:27, 'Martijn Tonies (Upscene Productions)'
> m.ton...@upscene.com [firebird-support] wrote:
>> Yes, it's true and it's rather annoying when, for example, developing an
>> interactive database development tool;)
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>







++

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] Firebird vs. PostgreSQL

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Martijn,

What about compilation of stored procedures - is it true that it is 
possible to create SP with errors, and they appear only when it will be 
invoked?

Also, about checking the parameters - is it true that you can create 
stored procedure which invokes other stored procedure with wrong list of 
parameters, and it will give error only at the execution time?

Regards,
Alexey



On 07.11.2018 19:27, 'Martijn Tonies (Upscene Productions)' 
m.ton...@upscene.com [firebird-support] wrote:
> Yes, it's true and it's rather annoying when, for example, developing an
> interactive database development tool;)







++

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] Firebird cluster

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,


As you probably know, Firebird a) uses complex mechanism to write 
consistent data to the database file even in the multi-users 
environment, b) intensively uses own cache, which must be synced with 
database file.

SafeKit says it is byte-level replication.
So, essentially it is the same as DRBD or shadow or VM-based replication 
- the copy of a database locked for the access (even read-only), and 
byte-level replication will replicate all low-level errors which occur 
in Firebird.
In the best case, the "replica" database will have the same state as 
master database after hard-reset.

The true native replication should be logical and transaction-aware.

Regards,
Alexey



On 22.08.2018 17:47, Slavomir Skopalik skopa...@elektlabs.cz 
[firebird-support] wrote:
> At this moment, I searching what is available and really used.
>
> Main objective is HA cluster. however it is also depend on other
> services like SLA for Firebird/solution.
>
> I know about HQBird, evidian safekit and FireSwarm.
>
> Any other?
>
> Any of them has some significant issue?
>
> Slavek
>
>
> On 20.8.2018 23:28, Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] wrote:
>> 20.08.2018 22:07, Slavomir Skopalik skopa...@elektlabs.cz [firebird-support] 
>> wrote:
>>> Any other solution that will work?
>>  It depends on what kind of cluster and for what purpose is needed. 
>> Simple master-slave
>> failover is included into HQBird.
>>
>>
>
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>







++

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] Re: how can the creation of fb_sort files be avoided? 2.2 GB of fb_sort files

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

Since you have enough RAM, you can increase TempCacheLimit in 
firebird.conf, for example, set 20Gb.
Fb_sort_xxx files will be created only if sorting will require more than 
TempCacheLimit.


Regards,
Alexey Kovyazin
IBSurgeon


On 07.08.2018 10:35, bieten2...@yahoo.de [firebird-support] wrote:


Hi everyone,

it would be great if I where to receive an answer to this question :-)
I am unsure whether the issue is so easy to answer that everyone 
thinks it is self explanatory
or that it is a rather challenging question which requires lots of 
in-depth Fire Bird knowledge ^^


Best Regards,
James



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

Hallo Everyone!

Although I spend a lot of time reading about performance tuning and 
optimizing Firebird according to the guides that the Firebird Team has 
published over the years, I could not figure out how to stop Firebird 
from creating about 2.2 GB of fb_sort files in the C:\Windows\Temp 
directory.


As described in other discussions the files disappear when the 
database is not being used intensively but I would not like to see 
them created in the first place.


As far as I see it, it is disadvantageous, when temp-files are created 
on the slow hard disk instead of keeping them in the fast RAM. I 
suspect that this also applies to Firebird.


You guys will very probably require system information in order to help:
Windows 2008 R2 Server (Virtual Machine)
Running Firebird 2.5.8.27089 in SuperClassic Mode
65,53 GB Ram available
Database Size 58,96 GB
8 CPU Cores


What do I have to do in order to stop the creation of fb_sort files?
My concern is that Firebird performance is suffering because of this.
I could of course also be mistaken and fb_sort-Files do not have a 
(negative) influence on performance.

If this were true, then I would be relieved :-)

Thanks a lot for your competent advice in advance and best Regards,
James

Re: [firebird-support] Firebird 3.0 - Won't Start - terminated due to startup error (2)

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

Don't install from repo, better download installation package from 
firebirdsql.org and install it instead.



Regards,
Alexey Kovyazin
IBSurgeon


On 27.07.2018 19:41, 
mailinglist.int.it.firebird.supp...@principledgroup.com 
[firebird-support] wrote:



Problem: Firebird 3.0 will not start.

NOTE: the following three (3) errors generated every time firebird tries
to start, either from reboot or manual start via /etc/init.d/firebird3.0
start --

From the firebird log (name of testing computer is 'client'):

client Fri Jul 27 11:15:30 2018
/usr/sbin/fbguard: guardian starting /usr/sbin/firebird

client Fri Jul 27 11:15:31 2018
/usr/sbin/fbguard: /usr/sbin/firebird terminated due to startup
error (2)

client Fri Jul 27 11:15:31 2018
/usr/sbin/fbguard: /usr/sbin/firebird terminated due to startup
error (2)

Environment:

Devuan Linux version 2.0 - 64-bit --- fresh install (derivative of
debian, see devuan.org)
Desktop = Mate

Firebird 3.0 (installed directly from default repository using synaptic)

Please advise.

Thank you in advance for any help offered.

Dave D.
Grand Rapids, MI USA

Re: [firebird-support] Plan with stored procedure

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Josef,

Firebird 3 shows plan Natural for all procedures.
2.5 also sometimes shows Natural, sometimes  - mix of plans of queries 
inside the procedure.

So, you cannot apply this plan to the statement.

Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com





On 22.06.2018 9:47, josef.gschwendt...@quattro-soft.de 
[firebird-support] wrote:


Hi,


for a certain select-statement IB-Expert shows the plan below:

PLAN JOIN (PRO INDEX (RDB$FOREIGN218), SORT (SORT (SC_COSTS NATURAL)))


If I apend this plan to the statement I get the error below:

There is no alias or table named SC_COSTS at this scope level.


My problem is, that I don't find any name or alias that works within 
this plan.



Does anybody know how to do this?


Regards,

Josef

Re: [firebird-support] Diagnosing CPU usage

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Rudi,

Usually,  queries with the dozens of millions of index operations are 
responsible for high CPU usage.
You can find them with HQbird MonLogger: get snapshot during the high 
CPU peak, open it, open tab Statements (disable check "Link to selected 
attachment"), and sort on record_idx_reads.

If there are many records - Remove prepared
The queries with the highest numbers of record_idx_reads are those which 
responsible for high CPU usage.


Regards,
Alexey Kovyazin
IBSurgeon




On 10.05.2018 16:12, Rudi Feijó rudi.fe...@multidadosti.com.br 
[firebird-support] wrote:


Hello

I have a situation which is hard to debug. We are running about 50 
databases

(firebird 3.0.3) on a single server, all of them connected to different
applications.

CPU usage is normaly low, but sporadically it will peak at 100% and cause
problems for all the connections.

We have no way of debugging every single one of the applications being 
run,

and there is no single obvious operation that could justify this high cpu
usage.

Is there a way to monitor queries not only for the time they take, but for
the CPU they use?

A few years ago I tried FBScanner, but as far as I remember it didn’t 
gauge

cpu usage.

Atenciosamente,

Rudi Feijó

Multidados Informática Ltda.
* (11) 2579-8789

*  
rudi.fe...@multidadosti..com.br


*  www.multidadosti.com.br
*  www.whatsappmailing.com.br

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

Re: [firebird-support] Measure the effectiveness of incrementing page buffer...

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

I am totally agree with Mark.
Ratio of page reads/page fetches for all queries has a little sense.
For example, the single badly designed query with many page fetches can 
"improve" this parameter a lot, without any real improvement in the 
performance, of course.


Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com





On 04.05.2018 12:29, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:


On 3-5-2018 15:52, Javier Cintron fcintr...@yahoo.com [firebird-support]
wrote:
>
>
> Thank you very much!

You shouldn't focus purely on this metric, greater page caches may lead
to more pages returned from memory, but also increase memory consumption
and overhead from things like cache eviction/coordination in the case of
Classic / SuperClassic (where the cache is per connection).

Mark

> On Thursday, May 3, 2018, 3:30:32 AM CDT, Dimitry Sibiryakov
> s...@ibphoenix.com [firebird-support] 
> wrote:
>
>
> 03.05.2018 7:56, Javier Cintron fcintr...@yahoo.com [firebird-support]
> wrote:
> > Firebird ver 2.5.7.27050 64 bits
> > Windows Server 2012R2
> >
> > ¿Is there a way to calculate the effectiveness of incrementing the
> page cache buffer ?
>
> select cast(mon$page_reads as double precision)/mon$page_fetches from
> mon$io_stats
> where mon$stat_group=0
> If the value is getting lower - you are on the right way.
>
> --
> WBR, SD.
>
>
>
>

--
Mark Rotteveel

Re: [firebird-support] What regular maintenance actions does Firebird need in order to function properly?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello,

We recommend perform at least everyday sweep - with disconnect of long 
running active writeable transactions before it.


Can you please clarify what do you mean by
> measuring the effectiveness of the page cache
?

Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com




On 04.05.2018 5:55, Javier Cintron fcintr...@yahoo.com 
[firebird-support] wrote:

Firebird ver 2.5.7.27050 64 bits
Superserver
Windows Server 2012R2

Besides, backing up my databases and measuring the effectiveness of 
the page cache, what other regular maintenance actions does Firebird 
need in order to function properly?


thanks in advance

Re: [firebird-support] windows server 2016 as server for firebird 2.5.7

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Nils,

No other issues.

Regards,
Alexey Kovyazin
IBSurgeon


On 05.12.2017 14:52, Nils Boedeker nilsboede...@t-online.de 
[firebird-support] wrote:


Hi

I want to use windows server 2016 as server for

firebird 2.5.7 (Classic Server)

are there any known issus with using firebird with this system?

with best regareds

Nils

PS: The power plan ist on "High Performance"

Re: [firebird-support] Converting to firebird 3

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Firebird 3 is not a silver bullet, it mostly increases performance in 
the multiprocessing area.
With 30 connections, I would say that multiprocessing is not a problem, 
most likely you are having problems with SQL queries.


I would suggest to take a look on
https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/
https://www.slideshare.net/ibsurgeon/resolving-firebird-performance-problems

Regards,
Alexey Kovyazin
IBSurgeon

On 11.10.2017 12:35, 'Gian Uys' gian...@gmail.com [firebird-support] wrote:


Hi

We have an application with roughly 20 000 lines of code and have been 
using firebird 2.5 since the start. We do experience performance 
problems with 30+ client connections and are thinking of moving to 
firebird 3.


Is firebird 3 drastically going to improve performance and how immense 
would the conversion to firebird 3 be?


Thanks

Gian

Re: [firebird-support] detect if database has changed

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Look at System Audit feature.

Regards,
Alexey Kovyazin
IBSurgeon HQbird
www.ib-aid.com

On 09.10.2017 2:49, Hamish Moffatt ham...@risingsoftware.com 
[firebird-support] wrote:


Is there an official way to detect if a database has changed?

I have some expensive reports and backups to run, but if I know that the
database has not changed since I last ran them I can skip those.

fbstat (gstat) shows the next transaction number, but that changes even
when only read-only transactions have run.

Currently I am looking at the timestamp of the .fdb file, which also
changes when only read-only transactions run.

I would like to know if the data or schema actually changed, not just a
transaction has run that didn't change anything.

Thanks

Hamish

Re: [firebird-support] Re: Sort error No free space found in temporary...

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

If you have big sort files (you can check their existence manually in 
temp folder or use HQbird FBBDataGuard to monitor them for you), 
increasing TempCacheLimit to fit all of them can speed up sorting 
queries (with GROUP BY and ORDER BY, for example).


However, the better approach is to decrease record sets which should be 
sorted.
For example, many developers use ORDER BY for internal processing in 
stored procedures - usually it can be simply removed.


Regards,
Alexey Kovyazin
IBSurgeon


On 03.09.2017 20:47, kragh.tho...@yahoo.com [firebird-support] wrote:

Hey
Thanks for the clarification, that is also the only explanation that 
made sense to me, after i had a look at the optimized config published 
at ib-aid.com.


Alexey perhaps you can answer this, is there anything to gain from 
configuring TempCacheLimit to use 2, 4 or 6Gb? Or perhaps more 
important could this have an negative effect? Of course provided that 
the server has plenty of RAM.

Re: [firebird-support] Re: Sort error No free space found in temporary...

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

I remember I answered incorrectly that time :)

TempCacheLimit is always per server instance.

For Classic it means it is allocated per each connection (since each 
connection has own server process), for SuperClassic and SuperServer 
temp space will be shared between connections, since there is the single 
server process.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com




On 02.09.2017 20:46, Thomas Steinmaurer t...@iblogmanager.com 
[firebird-support] wrote:


Hi Thomas,

> From what i can read on the internet TempCacheLimit, is per server
> process, and not per attachment.

In SuperServer architecture, right, but if I remember correctly, you
have mentioned in one of your first posts that you are using
SuperClassic, right?

> http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html

Talking about SuperServer.

> 
https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html


Talking about SuperClassic with answers that it is per connection.

>
>
> I believe I have found the statements that caused the problem, first 
one

> was executede about 400/second and contained a union, that was replaced
> by union all in the hope that would eliminate the need for temp cache.

UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of
applying DISTINCT with first sorting to remove duplicates is applied.

DISTINCT, sorting, group by (internally first doing a sort) will
internally produce temporary data.

> The second statement is a really nasty dynamically build common table
> expression containing upwards of 10 union.. Here we are in the process
> of moving this search to Elastisearch.
>
> We just purchased FB TraceManager at our company to weed performance
> issues  - Thanks for a great product : )

Great to hear. Thanks. :-)

--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.

Re: [firebird-support] Firebird slowdown.

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

It can be several reasons.

Try ##7, 20, 21, 23 from this list
https://www.ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/

Regards,
Alexey

On 15.08.2017 19:50, anderphil...@hotmail.com [firebird-support] wrote:



Hi, I have a strange problem which is probably quite obvious to 
someone.  If anyone could help it would be much appreciated.



We run a site connected to a Firebird database, its only about 700MB 
in size. We have started running into a regular slowdown of the site 
and have narrowed it down to the database.



The site runs very slowly (7000ms to 8000ms response times) when 
usually it is in the 2000-3000 region.



The odd thing I have discovered is that the site works at normal 
speed, IF I run a SELECT on a couple of the tables in FlameRobin and 
keep them open and the transcations uncommited.



The differnce in the web app response time goes from 2000-3000ms to 
7000ms-8000ms just a couple of seconds after commiting the 
transactions and closing flame robin. Opening them again and running 
the select will bring the app response time right down.



In Process Monitor fbserver.exe uses only roughly 50,000k which seems 
quite low and the server still has over 62% ram free.



The server specs are:


4x Intel(R) Xeon(R) CPU X5650 @ 2.67GHz

physical memory: 4096 MB

7gb disk free


Firebird 2.5.3 x64 super server

Windows 2008 R2

iis 8.5


The application is a remobjects server connected to a ISAPI dll.


We are running a (perhaps out of date) optimised flamerobin.conf file.


Here are the non hashed lines:


DefaultDbCachePages = 384

TempBlockSize = 2048576

TempCacheLimit = 77108864

LockMemSize = 7048576

LockHashSlots = 30011

CpuAffinityMask = 15


I have tried increasing the DefaultDbCachePages to  and it did not 
make a difference



Any help appreciated.


Thankyou


Regards

Philip

Re: [firebird-support] Can anyone advise the difference between Firebird 2.5 32 bit vs Firebird 3 32 bit?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

In short - 5 years of development.
These versions are different major releases, no backward compatibility.

Regards,
Alexey Kovyazin
IBSurgeon HQbird www.ib-aid.com



On 23.06.2017 12:15, 'Vonica Tng (Madsoft)' vonica@madsoft.com.sg 
[firebird-support] wrote:


Hi,

Can anyone advise the difference between Firebird 2.5 32 bit vs Firebird 3
32 bit?

Vonica Tng

Sales & Marketing Support Executive

MadSoft Solutions Pte Ltd

(formerly known as QnE Consultants Pte Ltd)

178 Paya Lebar Road

#05-01/02/03 Paya Lebar 178

Singapore 409030

Tel : (65) 6296 9919 ext 140

Fax : (65) 6296 9949

Email : 
vonica@madsoft.com.sg

Website :  www.madsoft.com.sg

Connect with us: 
 madsoft.sg
 
madsoftsg

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

Re: [firebird-support] FB in Azure VM?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

For 6 months of fees of powerful Azure VM you can buy the whole server.
I don't know if they charge for IO rounds, like Amazon - there IO adds a 
huge $$ to the bill.


If you want your database hosted and be reliable and safe - rent 2 
servers with SSD in Hetzner and install asynchronous replication between 
them (with 1 minute sync).

The main server like this
https://www.hetzner.de/ot/hosting/produkte_rootserver/dx141
and replica like this
https://www.hetzner.de/ot/hosting/produkte_rootserver/ex51ssd

It will cost you ~200 EUR/month totally and you'll get approximately  5 
times cheaper than Azure.


If you need more details - we did such things for one of our client 
recently, it can be even cheaper, than 200/month.


Regards,
Alexey



On 21.06.2017 13:08, Kjell Rilbe kjell.ri...@marknadsinformation.se 
[firebird-support] wrote:


Hi,

We're considering a move from dedicated server (at a hosting provider)
to hosting "everything" in the cloud, namely Azure.

They don't provide FB as "app as a service", so we're forced to go with
a VM where we maintain our own FB installation, which is not a problem
per se, but means we'll have to manage it more ourselves.

What experiences do you have with deploying FB in an Azure VM or similar?

If I understand MS correctly, they can provide a locally mounted
high-performance disk, but I'm not sure if they can provide mirroring,
e.g. RAID 1.

On our dedicated server we have a local RAID 1 volume (mechanical 10k or
possibly 15k rpm SCSI) for the databse, and make full nightly backups.
Forced writes off. Transaction load isn't very high, but we do need to
be able to execute long-running and "heavy" queries while at the same
time a web app makes short transactions that require short response times.

I'd like to know if you think the cloud VM approach for a FB
installation is viable, any special consideration we should be aware of,
etc.

I do know that the wire protocol is not very efficient if the network
latency is high, so you don't have to mention that aspect. I.e. we're
probably better of having both FB and the apps hosted in the same cloud...

But... do you know if this is a problem if FB is in an Azure VM and the
app is hosted in an Azure App Service? Network latency ok in that 
scenario?


Regards,
Kjell
--

Marknadsinformation logotyp

Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Marknadsinformation i Sverige AB
Ulvsundavägen 106C
168 67 Bromma
www.marknadsinformation.se 
08-514 905 90

Företagskontakt.se 
Personkontakt.se 

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

Re: [firebird-support] internal firebird consistency check

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Usually such corruptions happen due to hardware problems.
Check you RAM
https://ib-aid.com/en/articles/how-to-check-ram-and-avoid-database-corruptions/

Try to fix with standard means
https://ib-aid.com/en/articles/how-to-repair-a-corrupt-firebird-database/

If it fails, FirstAID will help.

It is not possible to 100% protect from all types of corruptions, but it 
possible to eliminate their consequences - implement a failover solution 
based on Firebird replication (VM failover does not work).


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com



1. I got following error reported Firebird-3.0.2.32703_0_Win32 :
"internal firebird consistency check (cannot find record back version 
(291), file: vio.cpp line: 4676)"


2. Environment:
.Net Provider 5.9.1.0
FlameRobin 0.9.3 (git hash 5ece15b)
Windows 10 Home Operating System

3. Back up reported the same error and failed. I'm not able to 
reproduce the error from the copy of the corrupted database.


4. How can I avoid such corruption of database and how can I recover 
such corrupted database.


Thanks and regards,
Rajiv

Re: [firebird-support] nbackup

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Hugo,

Yes, in this case you can check MON$ tables.
Please note that in case of many connections query to MON$ can consume 
significant resources.



How do you recover if  it's stuck?


nbackup -n

>I noticed that the larger the database is the longer it takes to do 
nbackup even if the delta is very small.


In 2.5 nbackup of any level scans the whole database file.
Consider to migrate to 3.0 to take advantage of nbackup improvements

Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com



Hi Alexey,

In my case the database is seldom larger 500 MB and the backup takes 
30 maximum seconds.


Would it not be sufficient to check the  MON$BACKUP_STATE field 
(thanks Thomas) and if its stuck on state 1 or 2 for several minutes 
then I execute "ALTER DATABASE END BACKUP".

How do you recover if it's stuck?

I noticed that the larger the database is the longer it takes to do 
nbackup even if the delta is very small.


BR,

hugo

Re: [firebird-support] nbackup

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Hugo,

In HQbird, we monitor size and age of delta files.
If it is bigger or older than appropriate size/age (like 500Mb/6 hours, 
for example), the database is marked as critical in Control Center.
You can implement the same - the status of the database and path to 
delta file can be retrieved from the database.


Regards,
Alexey Kovyazin



Hello,

Is there a way to ask a database if someone is performing a nbackup?

If it's in "the physical backup mode".

I'm always paranoid if there is a power failure during a backup and it 
gets stuck in that mode and I would like it to recover from such 
situation.

We have many hundreds of installations and it's difficult to monitor all.

Firebird 2.5 on Windows

BR,
Hugo

Re: [firebird-support] Table Size

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Ed,

There is IBAanalyst tool (now it is a part of HQbird), it shows sizes of 
tables and associated indices, see column Size

http://ib-aid.com/images/iba_tables.gif

Regards,
Alexey Kovyazin
IBSurgeon HQbird
www.ib-aid.com



I have a number of growing databases on my server and would like to 
occasionally see how much space each table is taking in the 
database--there may be some log files that could be cleaned up now and 
then. Many of my tables include blob fields, so this becomes different 
than just doing record count times the amount of data in a record.


Does anyone have any tools that they can recommend that could help me 
easily manage this?


much appreciated,

Ed Dressel

Re: AW: [firebird-support] Firebird SuperClassic hangs

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

The only thing I can say that situation is not obvious, I suggest to ask 
for professional support:

https://www.firebirdsql.org/en/support/

Regards,
Alexey





Hi Alexey,

I have already changed the configuration, but it didn’t help. What I 
find strange is the fact, that the memory consumption drops like it 
would do when you close all connections. See my first description of 
the problem. And only to be clear when I say hang, basically it locks 
up without any sign of processing. When does Firebird perform deadlock 
scans? We have a lot of things going on and this number stay at 0 
until the problem shows up. Is something causing a deadlock that FB 
cannot properly detect? How to analyze such a problem?


Best regards
Freddy Ertl

Von: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]

Gesendet: Dienstag, 9. Mai 2017 13:48
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Firebird SuperClassic hangs


Hi Freddy,

Try to use optimized Firebird configuration
https://ib-aid.com/en/optimized-firebird-configuration/

As Sean already told, hang could be caused by very long processing of 
some requests - extremely long queues in lock table, or related with 
excessive number of record versions (Max Versions in database 
statistics), or with other reasons. I would recommend to perform 
detailed investigation of the problem.



Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com


Hi,

we’re running Firebird 2.5.7 SuperClassic on Windows Server 2012R2 as 
part of a web application. Database is around 130GB with millions of 
transaction per day. From time to time Firebird hangs. It does not 
respond to any client. It’s also not possible to connect locally to 
the server. The interesting thing is the memory consumption that drops 
from 5-7GB to around 50MB and the CPU load goes down to 0%. When 
Firebird is in this state, I also see a lot of waiting threads in the 
Task Manager under “Analyze wait chain”. The only way to get out, is 
to stop the service. This takes longer than normal and sometimes shows 
some kind of timeout. But the process is stopped and can be started 
again. I also ran fb_lock_print when the problem occurred. It shows 
something under “Deadlock scans” which is not normal, I think.


LOCK_HEADER BLOCK
Version: 145, Active owner: 0, Length: 25165824, Used: 24798216
Flags: 0x0001
Enqs: 17157097538, Converts: 22693011, Rejects: 12135777, Blocks: 
93039760

Deadlock scans: 289, Deadlocks: 1, Scan interval: 10
Acquires: 18346589405, Acquire blocks: 1203176111, Spin count: 0
Mutex wait: 6.6%
Hash slots: 1009, Hash lengths (min/avg/max): 47/ 67/ 93
Remove node: 0, Insert queue: 0, Insert prior: 0
Owners (151): forward: 30704, backward: 21097392
Free owners (4): forward: 750216, backward: 24346456
Free locks (10120): forward: 28728, backward: 693760
Free requests (7877): forward: 8570432, backward: 1678000
Lock Ordering: Enabled

Has anybody experienced something like this? Thanks in advance.

Best regards
Freddy Ertl



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

Re: [firebird-support] Firebird SuperClassic hangs

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Freddy,

Try to use optimized Firebird configuration
https://ib-aid.com/en/optimized-firebird-configuration/

As Sean already told, hang could be caused by very long processing of 
some requests - extremely long queues in lock table, or related with 
excessive number of record versions (Max Versions in database 
statistics), or with other reasons.  I would recommend to perform 
detailed investigation of the problem.



Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com



Hi,

we’re running Firebird 2.5.7 SuperClassic on Windows Server 2012R2 as 
part of a web application. Database is around 130GB with millions of 
transaction per day. From time to time Firebird hangs. It does not 
respond to any client. It’s also not possible to connect locally to 
the server. The interesting thing is the memory consumption that drops 
from 5-7GB to around 50MB and the CPU load goes down to 0%. When 
Firebird is in this state, I also see a lot of waiting threads in the 
Task Manager under “Analyze wait chain”. The only way to get out, is 
to stop the service. This takes longer than normal and sometimes shows 
some kind of timeout. But the process is stopped and can be started 
again. I also ran  fb_lock_print when the problem occurred. It shows 
something under “Deadlock scans” which is not normal, I think.


LOCK_HEADER BLOCK

Version: 145, Active owner:  0, Length: 25165824, Used: 24798216

Flags: 0x0001

Enqs: 17157097538, Converts: 22693011, Rejects: 12135777, Blocks: 93039760

Deadlock scans:289, Deadlocks:  1, Scan interval:  10

Acquires: 18346589405, Acquire blocks: 1203176111, Spin count:   0

Mutex wait: 6.6%

Hash slots: 1009, Hash lengths (min/avg/max):   47/ 67/  93

Remove node:  0, Insert queue:  0, Insert prior:  0

Owners (151):   forward:  30704, backward: 21097392

Free owners (4): forward: 750216, backward: 24346456

Free locks (10120): forward:  28728, backward: 693760

Free requests (7877):forward: 8570432, backward: 1678000

Lock Ordering: Enabled

Has anybody experienced something like this? Thanks in advance.

Best regards

Freddy Ertl

Re: [firebird-support] Firebird 3 superserver configuration for high load

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Look at the English version of the page
https://ib-aid.com/en/optimized-firebird-configuration/

Regards,
Alexey Kovyazin
IBSurgeon



Those are for 2.5 aren't them?
The only ones I found there are at
https://ib-aid.com/br/optimized-firebird-configuration/

I was guessing there are different values for cache size on superserver?

Atenciosamente,

Rudi Feijó

Multidados Informática Ltda.
* (11) 2579-8794 / 2579-8795
*  
rudi.fe...@multidadosti.com.br

*  www.multidadosti.com.br
 www.timesheet.com.br

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: quinta-feira, 20 de abril de 2017 17:43
To: =?iso-8859-1?Q?Rudi_Feij=F3_rudi.feijo@
Subject: Re: [firebird-support] Firebird 3 superserver configuration for
high load

There is optimized configuration files in the IBSurgeon site. You can
use it as a base, and fine tune as you need.

Carlos
Migration Guide to FB 3 -

http://www.firebirdnews.org/migration-guide-to-firebird-3/

RFrfmcbfs> Hello
RFrfmcbfs> We have recently installed FB 3 on SuperServer mode.

RFrfmcbfs> From the articles, we deduced this is the best mode for our 
high

concurrency
RFrfmcbfs> scenario (about 100 databases, 10gb in size, each with 50 
to 300

RFrfmcbfs> simultaneous users doing CRUD operations on the same tables).

RFrfmcbfs>

RFrfmcbfs> Is there any published guide on optimizing firebird.conf 
for FB3

and this
RFrfmcbfs> scenario? I remember there was a lot of testing and
recommendations for
RFrfmcbfs> config changes on fb 2.5.

RFrfmcbfs>

RFrfmcbfs> Thanks.

RFrfmcbfs>

RFrfmcbfs> Atenciosamente,

RFrfmcbfs> Rudi Feijó

RFrfmcbfs> Multidados Informática Ltda.
RFrfmcbfs> * (11) 2579-8794 / 2579-8795
RFrfmcbfs> * < 
mailto:ariane.cut...@multidadosti.com.br>
RFrfmcbfs> 
rudi.fe...@multidadosti.com.br
RFrfmcbfs> * < 
http://www.multidadosti.com.br/> 
www.multidadosti.com.br
RFrfmcbfs> <  http://www.timesheet.com.br/>
 www.timesheet.com.br

RFrfmcbfs>

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

RFrfmcbfs> 
RFrfmcbfs> Posted by: =?iso-8859-1?Q?Rudi_Feij=F3?=
RFrfmcbfs> < 
rudi.fe...@multidadosti.com.br>
RFrfmcbfs> 

RFrfmcbfs>
++

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

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

RFrfmcbfs>
++
RFrfmcbfs> 

RFrfmcbfs> Yahoo Groups Links



Livre de vírus.
 www.avg.com.

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

Re: [firebird-support] Improve remote query speed on Windows Server 2012 VPS

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Migrate to Firebird 3 - it has protocol improvements for high latency 
networks.

With 2.5 you will never reach such good results as with FB3.

Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com






Hi.
I'm testing Firebird 2.5 x64 on Windows Server 2012 R2 VPS
From tests,remote access database works slow,a simple query on a small 
table with about 500 records


select * from MYTABLE where id=200

takes
   Total execution time: 5.388s
   PLAN (MYTABLE INDEX (PK_MYTABLE))

  the same query executed local on the server(same db,server) takes 
 0.065s


queries are done with Flamerobin,from IBExpert the same results

How can I improve remote speed?
Thanks

Re: [firebird-support] Firebird 2.5 classic performance issue on linux64

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Andreas,

I think the main thing is

   * blockdev -setfra 32768  - this has nothing to do with
 firebird but speeds up reads in the RAID array quite a bit. Thanks
 to wltjr on IRC.

and, probably, backup/restore.

In 2.5 the bad effect of autosweep is almost eliminated on the systems 
with low and moderate writes.

Regards,
Alexey Kovyazin


> Hi guys,
>
> I'm just responding one more time because I want to mark this as solved
> for me:
>
> Several things did the trick:
>
>* Upgrade Firebird to 2.5.7 (apparently there was a subquery bug in
>  2.5.4 which wasn't fixed in the debian package) - thanks to LiENUS
>  on IRC.
>* blockdev -setfra 32768  - this has nothing to do with
>  firebird but speeds up reads in the RAID array quite a bit. Thanks
>  to wltjr on IRC.
>* switching to manual sweeping over automatic and transaction-based
>* doing a complete gbak backup/restore one more time to re-index
>* tweaking xinetd (flags = REUSE NODELAY)
>
> Tweaking a system for firebird is a little bit different from tweaking
> it for other databases, because firebird uses one huge file and not
> several of them scattered all over the filesystem. Not judging, just
> saying it's a different approach :)
>
> Maybe I'll write something up on our blog for this so people can more
> easily find the bottlenecks. This took me two weeks of research and a
> lot of asking around. One simple best-practice howto would have saved me :)
>
> If anyone cares, I will write this down as an optimized firebird linux
> setup from start to finish and run this by you guys. Most of the howtos
> and optimization resources I could find focus on Windows (CPUAffinity
> tweaks, etc.) - and I'd like to help people out who want to skip the
> windows section :)
>
> Thanks again.
>
> Andreas
>
>   
>
>
> On 28.02.2017 00:33, 'Leyne, Sean' s...@broadviewsoftware.com
> [firebird-support] wrote:
>>   
>>
>> Andreas,
>>
>>   
>>
>>   
>>
>> this is the one thing I am getting when I am connecting to the
>> database. I am not the one working productively on the system, so I
>> can't really tell wether this has become faster or is still the same.
>>
>> LOCK_HEADER BLOCK
>>  Version: 145, Active owner:  0, Length: 7048576, Used: 540536
>>  Flags: 0x0001
>>  Enqs:   5031, Converts:113, Rejects:  8, Blocks: 11
>>  Deadlock scans:  0, Deadlocks:  0, Scan interval:  10
>>  Acquires:   7695, Acquire blocks:  3, Spin count:   0
>>  Mutex wait: 0.0%
>>  Hash slots: 30011, Hash lengths (min/avg/max):0/   0/   4
>>  Remove node:  0, Insert queue:  0, Insert prior:  0
>>  Owners (3):forward: 252920, backward: 490968
>>  Free owners: *empty*
>>  Free locks (5):forward: 254960, backward: 519480
>>  Free requests (6):forward: 540344, backward: 403464
>>  Lock Ordering: Enabled
>>
>> This is what the fb_lock_print output looks like.
>>
>>  Those numbers look to be very good.
>>
>>  Q: Why are you running Classic server?  How many
>> users/connections are there usually to the database?
>>
>>  Perhaps SuperServer provide better performance – it would allow
>> you to “blow up” the page cache size.
>>
>>   
>>
>>







++

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] Firebird 2.5 classic performance issue on linux64

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

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

Please note that if you have number of buffers set in database header, 
it overrides firebird.conf value.


Check that RAID has BBU, read-write cache is enabled and write is set to 
write-back.
After that  - start to check performance of your queries - non-optimal 
queries lead to 90% of performance problems.


Regards,
Alexey Kovyazin
IBSurgeon


Hi everyone,
I originally tried to post to this like I would to a normal mailing list. I've 
never seen an open source project that required me to have a yahoo account ;) I 
have a firebird-related problem however.
I'll try and provide as many details as possible, so this message is
going to be a little bit longer, but here goes:

I encountered a bit of trouble migrating a firebird server from an
ancient w2k SBS to a powerful debian-based DL380 server.

I'll share my story, so people facing the same problems can scratch
things off their list. First off, the application connecting to firebird
was horribly slow. After the switch to classic flavor, things didn't
really seem to change, but our client told us it has become a bit better.

The following things have already been done:

- increase page size to 16kB (gbak -> and restore with new pagesize)
- increased buffers for firebird to use up to 4GB of ram (256kB) via gfix
- switchted to async writes because we have redundancy and data security
on hardware level (also via gfix)
- got rid of automatic sweeping by transaction count and added a cron
job to handle this after hours

After all of this didn't really seem to do much, we had to pull out the
big guns:

- additional raid controller
- additional two-disk raid-0 array
- switched to XFS (the firebird db file is 43 GB big)

Performance improved a bit, but still not the way we would like it to be.

Here are a few things that I have personally noticed:

- $FIREBIRD_TMP="/path/to/my/fast/space/" will be entirely ignored.
- setting TempDirectories doesn't seem to get evaluated either

That makes me think which other settings I did in firebird.conf will
just be thrown out of the window. We run firebird through xinetd, I even
gave it a scheduler priority (nice value) of -15 but CPU doesn't really
seem to be the issue. It just seems like firebird takes forever to
process a request that goes across several tables.

Here's the stuff that is out of our control:

The software using firebird under the hood is just horribly designed.
There's binary data in the database, which makes it huge to begin with
and I doubt that queries, selects, joins, etc. are implemented in a
smart way.

BUT: all of this ran pretty fast on an ancient windows machine with
synchronous writes and lots of other inefficient configuration. I also
read that certain borland libraries are having performance issues
connecting to a linux64 firebird.

Versions:

Serverside: firebird2.5-classic: Installed: 2.5.4.26856
Clientside: gds32.dll / dbxup_fb.dll

Connection is done via network host:/path/to/db-file.fdb which works
just fine, but horribly slow.

If you guys need any more input, I can provide it.

Questions:
- What else can I do to improve performance (I'm still missing the one
thing that feels like releasing the hand-brake)?
- Why is firebird ignoring the environment variable and the .conf setting?
- Is there a way to explicitely provide a config file to firebird? The
manual page doesn't offer such an option.

Thanks very much in advance and sorry for whipping up half a novel

Andreas
-- *Andreas Zeller* Geschäftsleitung lux-medien.com 
 Carl-Friedrich-Gauss-Str. 56 47475 
Kamp-Lintfort Office: +49 2151 32 66 628 Fax: +49 2151 32 66 721 
Mobil: +49 163 27 9 1979

Re: [firebird-support] FB 2.5 migrate to 3.0

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Recompile all stored procedures and triggers in Firebird 3.

Regards,
Alexey Kovyazin
IBSurgeon HQbird www.ib-aid.com/hqbird


Dear Sir/Madam,

I am trying to migrate my database from 2.5 to 3.0 now.
It was tested before and worked successfully 
with great help from firebird community.

But unfortunately, all my notes were gone with my lost laptop.

Now I show all my steps I have done for migration procedure.

1. Use gbak (FB 2.5) to backup Firebird 2.5 database file.
2. Use gbak (FB 3.0) to restore backuped FBK file to a new database file.
3. Use isql, to run: SQL> alter user sysdba set password 'masterkey';
respond: Database: myimport, User: SYSDBA
4. Open new database file in IBExpert, it works.
5. Run my own applicatoin which works with FB2.5, error message shows below:
[FireDAC][Phys][FB]Your user name and password are not defined. Ask your 
database administrator to set up a Firebird login.


Connection string inside my code

  object dbcMain: TFDConnection
Params.Strings = (
  'DriverID=FB'
  'User_Name=sysdba'
  'Password=masterkey')


I guess that one or two steps must be missed in this procedure but I really 
can't recall my memory any more.
Please anyone can show me what is the key point to get this new FDB file 
working?

Thanks.


Best regards,

James

Re: [firebird-support] High Availability Firebird

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Please take a look
http://www.slideshare.net/ibsurgeon/failsafe-cluster-for-firebirdsql-and-something-more

Regards,
Alexey Kovyazin
IBSurgeon


Hi Everyone,

First, I apologise if this isn't the correct group - please forgive me.

I'm trying to find some contemporary (ie from in the last 24 months /
Firebird 3.0+) info/documentation on using Firebird in a High
Availability configuration. What I'm thinking is using Firebird on a
pair of Centos7 Servers with Pacemaker and/or HAProxy.

Can anyone please point me in the right direction - thanks

Matt

Re: [firebird-support] Migration from Interbase 7.5 to Firebird 2.5.4

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

We did it several times for our clients.
Not very difficult, but requires to detect and rewrite wrong SQL queries.

Regards,
Alexey Kovyazin
IBSurgeon



Hi ,
It is my First question in this group.

I would like to know if someone has accomplished a migration from

Interbase 7.5 to Firebird 2.5.4

Re: [firebird-support] internal Firebird consistency check (decompression overran buffer (179), file: sqz.cpp line: 239)

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Ian,

It seems that you have multi-volume database, is it correct?

Regards,
Alexey Kovyazin
IBSurgeon


Hi FB Support!


We have been running FB since IB4.2 days and have only previously had 
any database corruption when we've had hardware issues...until 
recently, when I've seen this error on 2 different servers that I'm 
reasonably confident are both fine! However, our setup is probably 
fairly unique. I'm not panicking at the moment as we recovered all the 
data from the backup, but as I've had this twice in a month on 
different machines I'm keen to adjust the setup so it doesn't happen 
again.



We are running about 20 customers on a server, each with their own set 
of Firebird/PHP-FPM/Nginx/custom python servers running as services 
bound to separate IP addresses – this is (in theory!) to keep each 
customer isolated from the others whilst sharing most of the resources 
on the box without too much overhead from containers or full 
visualisation.



FB is listening as a tcpsvd service but with a shared lock folder:


exec tcpsvd -c 60 -u firebird:firebird -l $remote_bind_addr 
$remote_bind_addr $remote_bind_port /usr/sbin/fb_inet_server -i -e 
"/srv/$CUSTOMER/interbase/" -el "/tmp/firebird/tmpfs"



and with a “localhost” for our own convenience:


exec tcpsvd -c 60 -u firebird:firebird -l 127.0.0.1 127.0.0.1 3050 
/usr/sbin/fb_inet_server -i -e "/var/interbase/" -el "/tmp/firebird/tmpfs"



I had assumed because they were using the share lock folder that this 
is allowed.



Each night we loop through all the customers and run :


for database in /customers/*.gdb

do

echo “Doing $database:”

echo -n “gbak ”

gbak -b -g localhost:$database /backupdata/$database.gbak

echo $?

echo -n “gfix ”

gfix -sweep localhost:$database

echo $?

echo -n “isql “

echo “EXECURE PROCEDURE nightlySQL;” | isql localhost$database

echo $?

done


Whilst this backup is running we don't disable any external access and 
have a mixture of python and php clients accessing throughout the 
night using the $remote_bind_addr:$database connection string rather 
than localhost:.



Over the weekend a 12GB backup finished successfully, but errors 
started appearing in the firebird2.5.log file at the same time as the 
next database was being backed up – suggesting that either the gfix or 
isql had tripped up before the script moved on. However, the output 
from the backup was:



Doing :

gbak 0

gfix 0

isql 0


The firebird log has the next customer's sweep starting immediately 
after the first “internal Firebird consistency check (decompression 
overran buffer (179), file: sqz.cpp line: 239)” on the now corrupt 
database which suggests the isql line is “to blame” - but the 
nightlySQL doesn't do a lot, just deletes from a table and 
re-populates a load of summaries from a big table. The firebird log 
error coincides with an NGINX request for the data that the nightlySQL 
is building, but I've repeated this today and it doesn't by itself 
kill the database. I've got nothing else in either syslog or dmesg.



I've run IBSurgeon against the file. It gives me the following output:


03/10/2016 11:23:53 INFO: Open database files: Z:\home\**-bad.gdb


03/10/2016 11:23:53 INFO: Analyzing database low-level structures...

03/10/2016 11:27:42 INFO: Actual PageCount: 1534112 found in database

03/10/2016 11:27:42 ERROR: Found 18 undefined or unrecognized pages.

03/10/2016 11:27:42 INFO: == DATABASE IS READY FOR DIAGNOSING AND 
REPAIRING. 


03/10/2016 11:27:42 INFO: == Now choose "Diagnose" or "Repair". 

03/10/2016 11:51:31 INFO: --- Starting diagnose

03/10/2016 11:51:31 INFO: Running procedure: Header page check

03/10/2016 11:51:31 INFO: ODS Major = 11 (32779)

03/10/2016 11:51:31 INFO: ODS Minor = 2

03/10/2016 11:51:31 INFO: Next transaction = 13910909

03/10/2016 11:51:31 INFO: Oldest transaction = 13910907

03/10/2016 11:51:31 INFO: Oldest active = 13910908

03/10/2016 11:51:31 INFO: Oldest snapshot = 13910908

03/10/2016 11:51:31 INFO: PageSize is Ok = 8192

03/10/2016 11:51:31 INFO: Running procedure: Checking of RDB$Pages 
consistency


03/10/2016 11:53:42 INFO: Checking of RDB$Pages consistency: Ok

03/10/2016 11:53:42 INFO: Running procedure: Low-level check of all 
relations


03/10/2016 11:53:43 INFO: Relation RDB$DATABASE (1) is OK

03/10/2016 11:53:44 INFO: Relation RDB$FIELDS (2) is OK

03/10/2016 11:53:46 INFO: Relation RDB$INDEX_SEGMENTS (3) is OK

03/10/2016 11:53:47 INFO: Relation RDB$INDICES (4) is OK

03/10/2016 11:53:47 INFO: Relation RDB$RELATION_FIELDS (5) is OK

03/10/2016 11:53:48 INFO: Relation RDB$RELATIONS (6) is OK

03/10/2016 11:53:48 INFO: Relation RDB$VIEW_RELATIONS (7) is OK

03/10/2016 11:53:48 INFO: Relation RDB$FORMATS (8) is OK

03/10/2016 11:53:48 INFO: Relation RDB$SECURITY_CLASSES (9) is OK

03/10/2016 11:53:48 ERROR: DP#1533855 has wrong rel#:136

03/10/2016 11:53:48 ERROR: Found 1 record errors on datapage#1533855

03/10/2016 11:53:48 ERROR: Error on 

Re: [firebird-support] how to keep history of statements per transaction?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

use Trace API - the basic variant with text output is included into 
Firebird.


If you need GUI and retrospective analysis, you can try HQbird (trial 
version of Standard is 14 days), it contains PerfMon tool with TraceAPI 
support


More details in HQbird Guide, page 66
http://ib-aid.com/download/docs/hqbird2016r1userguide.pdf

Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com





Hi,

I'm looking for easy to use solution that can help me trace statements
executed inside every transaction and keep them until transaction is 
active.


Once it is closed(commit or rollback) statements are discarded.

I need this to be able to run on a production system in order to track
which code path leaves open transaction or keeps transaction open for
long time.

Any ideas how to make this?

--
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]

[firebird-support] Call for papers for Firebird Conference 2016

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi All!

Call For Papers
Conference to be held at the Barcelo Praha Hotel, Prague, Czech 
Republic, on the 7th and 8th of October 2016.
The Firebird Foundation is seeking submissions from Firebird users and 
developers on the theme of "Firebird in the Enterprise". A session 
submission should have a title and at least 200 words outlining the main 
points of your talk.


A session will last for 50 minutes plus 10 minutes for turnaround and 
questions. If your talk takes more than a single session, then it should 
span multiple sessions. If your session is especially popular you may be 
asked to repeat it.


You can submit a session draft directly to c...@firebirdsql.org
If you have any questions also please contact c...@firebirdsql.org

Submissions must be made by the deadline of Midnight July 1st, 2016. 
Once received there will be a review of the submitted papers and authors 
will then be informed which papers have been accepted or rejected.


Publication of Papers (after the conference)

Each selected speaker will be expected to turn in a technical paper in 
electronic format for each abstract topic originally approved.


All illustrations and graphics pertaining to each paper must also be 
submitted. The papers should contain enough detail to enable a reader to 
fully understand the topic without benefit of a physical presentation. 
Speakers may also submit samples of code and/or presentation slides.


All accepted papers will be published electronically after the 
conference. By submitting materials to the Firebird conference, you 
grant the Firebird Foundation permission to use, reproduce, audio/video 
record and distribute your materials.


Regards,
Alexey Kovyazin
IBSurgeon

Re: [firebird-support] Is Firebird 3 ready for Production?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Fabian,

Also I recommend you to try our optimized configuration file for FB3:
http://ib-aid.com/en/optimized-firebird-configuration/

Regards,
Alexey


Hi Alexey


Thank you for the comments. So far everything has gone ok, I fixed all 
the fields with ASCII charset at the FB 2.54 DB (changed the domain to 
charset NONE), and now the backup and restore workes fine, with no errors.


I've noticed the new FB3 DB has now two SYSDBA users, one seems to be 
disabled, and the other has the masterkey password even though the 
install asked me for the password, but it appears it ignored my 
answer, or it used the password I entered for the disabled SYSDBA 
users... no big deal.


Other than that I have not encountered any other issues so far, I will 
recompile all stored procedures and views and triggers shortly, but I 
don't expect any issues there because the DB is operational with the 
Application and no errors have appeared on the APP side using the FB3 DB.


Cheers,
Fabian

Re: [firebird-support] Is Firebird 3 ready for Production?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Fabian,


Is is ready for production, but migration process in not just 
backup/restore (it was never so in older versions, but it worked 
relatively well due to high backward incompatibility) - it requires 
thorough check of triggers, stored procedures, data itself and ad hoc 
queries.


When we do migration for our clients, we use our automation tools which 
ensure that we have traced all problematic queries before going to 
production.
I don't think it's possible to do such checking manually in case of big 
enough system - and comments in this thread confirm it.


Regards,
Alexey Kovyazin
IBSurgeon
http://ib-aid.com/en/firebird-database-migration/




Hi All

We have a multi-user, multi-company, cloud based system running with
Firebird 2.54 as the main database, with about 100 concurrent 
connections.

We are considering migrating to FB 3.0 to take advantaje of the SMP
architecture, we have a 32 core / processors server, so we are hopping to
see an increased performance. We also have the need to set up online
replication.
Would anyone be able to advise if it it too soon to jump into FB 3.0 for
production? And if the performance increase would be enough to make the
upgrade worth considering 3.0 has not been around for long yet, and 
may be

"taking too much risk" to move into it so soon? And regarding the
replication, any advise on what product may be the right choice?

Cheers
Fabian

Re: [firebird-support] Re: Question about few Firebird details

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Yes, sure, my mistake - I should not answer questions so late and need 
to sleep more :)


Regards,
Alexey



23.05.2016 22:32, Alexey Kovyazin wrote:
>
> 44. This option turn off individual savepoints.

Individual savepoints are never turned off. This option disables the
root (transaction-level) savepoint used to transform a rollback into a
commit (by undoing all the transaction changes).

Dmitry

Re: [firebird-support] Question about few Firebird details

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Karol,

44. This option turn off individual savepoints. Our tests proof that 
gives slight  increase in case of massive inserts or updates. Use it 
with caution, since the rollback will be forced (through TIP), so if you 
insert less than 50k records, there could be difference with and without 
NO_AUTO_UNDO  - Firebird will be not able to transform rollback into 
commit with savepoints. If you insert more than 50k records (actually 
50-60k, it depends on memory, not exact ##, if I remember correctly), 
rollback will be done through TIP anyway.


45. Try it with table like
CREATE TABLE T1(i1 integer not null primary key, iSort integer, 
BIGSTARTING VarchaR(2000));
insert there 10 mln records and do 2 queries as in the example. It will 
take 10 mins, and and you'll see the difference by yourself.


Regards,
Alexey Kovyazin
IBSurgeon



Hi,
https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/
i have few questions:
44. Use NO_AUTO_UNDO - what happend when i do rollback? I do not 
understand what really do this option..

30. Use derived tables to optimize SELECT with ORDER BY/GROUP BY -
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N
FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY
why this can be faster then?
SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2
Firebird sort whole resultset of "sorted key - Field2" or do different 
work?

I see in FB3 plan sort width and it looks like not true but?
regards,
Karol Bieniaszewski

[firebird-support] Firebird 3.0 Is Available Now!

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

  Firebird 3.0 Is Available Now - with major improvements in 
performance, security and SQL features

April 19, 2016 -- The Firebird Project is pleased to announce the 
release of version 3 of Firebird, its open source relational database 
management system:
http://www.firebirdsql.org/en/firebird-3-0-0/

This version is the most extensive re-architecture of the Firebird DBMS 
since its initial release more than 14 years ago.  Its new 
multi-threaded engine processes and shared page caching enable better 
utilization of multi-core hardware and large address spaces. With these 
changes, this release features dramatic performance increases, while 
maintaining compatibility with old client libraries.

In addition, Firebird 3.0 includes a long list of SQL improvements 
(including Window and statistical functions), enhanced security features 
such as wire and database encryption and multiple security databases, 
and more -- over 100 significant improvements.  Yet even with its new 
enterprise-level features, it retains the tiny footprint and simple 
deployment and management that have made it "the right tool for the job" 
for so many applications.

Steve Summers, President of the non-profit Firebird Foundation, said: 
"We're very excited about this release. Re-architecting a system as 
complex as a relational database system is a very difficult task. We're 
extremely impressed with the skills shown by Firebird's development team 
and the great job they've done."

Dmitry Yemanov, the core team leader, agreed. "Firebird 3.0 development 
was quite a challenge but we've finally completed the work and ensured 
that it complies with our QA standards. Besides the scalability and 
security features that were our top priority tasks, I'm particularly 
happy about the performance improvements in garbage collection and 
incremental backup areas. With the renewed architecture and 
significantly refactored code, this release establishes a solid platform 
for our further progress. Thanks to all the Firebird developers and 
contributors."

Mr. Summers added: "If you're building a system that needs to store 
structured data at end-users' sites, from a few megabytes to hundreds of 
gigabytes, on a desktop or a network with thousands of users, Firebird 
has always been a great choice. It's an even better choice now. You owe 
it to yourself to at least take a look!"

Pricing and Availability

Firebird 3.0 is an open source database system that is completely free 
of charge for any use – including deployment as part of commercial 
products (not just non-profit, private or educational use like some of 
its competitors).  Both binary packages and the complete source code can 
be downloaded at no cost through the Firebird Project website:
http://www.firebirdsql.org/en/firebird-3-0-0/

No registration or activation is required.

As of today, Firebird 3.0 is available in 32 and 64 bit versions for 
both Windows and Linux. OS X and other platforms will be available soon. 
Drivers for JDBC (Java), .NET, ODBC, Python and more are also available 
through the FirebirdSQL.org web site, along with documentation.
http://www.firebirdsql.org/en/downloads/

Additional tools (drivers, administration, replication, UDF 
(user-defined-function) libraries, etc.) are also available through 
third parties:
http://www.firebirdsql.org/en/third-party-tools/

About the Firebird Foundation

The Firebird Foundation is a non-profit organization that supports the 
development and maintenance of the Firebird relational database system. 
The Foundation was incorporated in 2002 and is currently supported by 
more than 300 active members and 23 sponsors. If you find Firebird 
useful, and especially if it is helping you earn money, please join us 
to help ensure the future of this valuable tool.  For details, please 
click the Community tab on our website
http://www.firebirdsql.org/en/firebird-foundation/

For more information, contact:

Steve Summers, sesumm...@firebirdsql.org, or

Alexey Kovyazin, c...@firebirdsql.org

Re: [firebird-support] Performance issues moving to new server with later Firebird version

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Bob,

In general, we recommend to apply the same configuration as at the old 
server (even if it looks not very good), and compare the performance - 
if there will be a problem, obviously it will be related with the new 
hardware/OS.


If there will be the same performance, then you can tune firebird.conf.
If you don't know exactly how changes will work, change one parameter at 
a time.


Also, you can get optimized configuration file from our web-site
http://ib-aid.com/en/optimized-firebird-configuration/

And, look at the hardware guide: 
http://ib-aid.com/en/articles/firebird-hardware-guide/


Regards,
Alexey Kovyazin
IBSurgeon HQbird





We have a 127GB Firebird database that was running on Windows Server 
2008 x64 with 16GB of RAM and Firebird SuperClassic v2.5.2.26539.  The 
database has a page size of 8192 and buffers of 6144, and ODS version 
11.2.



Due to time constraints I could not do a backup+restore on the new 
server, which is Windows 2012 with 32GB RAM and Firebird SuperClassic 
v2.5.5.26952. However, most activity on this new server with the old 
database is much slower than the old server.  I'm only seeing about 
1.5GB Private Bytes in use, jumping occasionally to 2.5GB with a 
working set of 1.1GB and Virtual Size of 2.7 (as per Process Explorer).



The only changes that I have made to the stock Firebird.conf are as 
follows:


DefaultDbCachePages = 384

FileSystemCacheSize = 50

TempBlockSize = 2048576

TempCacheLimit = 567108864


The database is comprised of many images up to 1.5MB in size, but they 
are not accessed very often. The actual data size outside of these 
images is probably 1/3 of the total DB size.



There are typically about 20 connections to the database servicing up 
to 140 users, with a mix of read and write transactions, as well as 
some reporting.



The header statistics look like this:

Database header page information:
Flags   0
Checksum12345
Generation  106862144
Page size   8192
ODS version 11.2
Oldest transaction  103869407
Oldest active   103869408
Oldest snapshot 103869408
Next transaction103870442
Bumped transaction  1
Sequence number 0
Next attachment ID  2991646
Implementation ID   26
Shadow count0
Page buffers6144
Next header page0
Database dialect3
Creation date   Feb 2, 2014 22:06:05
Attributes  force write

Variable header data:
Sweep interval: 0
*END*



I'm looking for some guidance as to how changes that should be made to 
the Firebird configuration file and to the database when I do a 
backup/restore.  For instance, I'm sure that I want to jump to 16KB 
pages, but what about buffers?



There are other services that run on this server, so I cannot use all 
resources for the database. However, I believe it is provisioned well 
enough that I can certainly use half of the resources - processor and 
memory - without affecting the application server and web server that 
are running on the same box.



Thank you for your time,


Bob M..

Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Patrick,

If you say that problem occurred recently, I would suggest you to check 
SAN disks health.


However, these values


Average system IOPS under load read: 100

>Average system IOPS under load write: 550
>Backup Restore IOPS read: 1700
>Backup Restore IOPS write: 250

are really, really low.
1700 IOPS for the database with 4k page means 6.8Mb/sec (in case of 
random reads).


I suggest to install a single SSD drive and check how it will work.
SSD IOPS looks like
  Random Read 4KB (QD=32) :   283.050 MB/s [ 69104.0 IOPS]
  Random Write 4KB (QD=32) :   213.837 MB/s [ 52206.2 IOPS]


From our optimization practice we found that if you need to optimize 
only the single instance of the database, the most cost effective way is 
to upgrade to SSD first, and only then fix other problems.


Regards,
Alexey Kovyazin
IBSurgeon HQbird www.ib-aid.com




hi,

recently we had some strange performance issues with our Firebird DB 
server.


On high load, our server started to slow down. Select and update SQL 
query times did go up by more than 500% on average,


but reaching unreasonable high execution times at worst case. (several 
minutes instead of < 1sec)



OIT/OAT/Next Transaction statistics was within 1000 the hole time

We were not able to messure any hardware limiting factor. Indeed, this 
system was running with only 8 cores at about 70% CPU usage on max. load.


We decided that this may be our problem since we experienced a similar 
problem at about 80% CPU load in the past.


So we upgraded the hardware. As expected, the CPU-load dropped to ~35% 
usage on max. load scenario.


But this did not solve the problem.

Same story for the harddisk system. The usage is not even near it's 
max capacity.



We also can't see any impact on the harddisk.

We'r kind of stuck with our ideas, because we have no idea what could 
be a potential bottleneck to the system.


Since the hardware doesn't show a limit, there have to be anything 
else - most likely firebird engine related that's limiting our system.


We would be very grateful if anyone can give us hints where we can 
search further.


Or someone has similar experiences to share with us.



Operating System: Windows Server 2003

Firebird: 2.1.5 Classic

Dedicated database server (VMWare)


CPU: 16 cores, each 2.4 GHz

RAM: 32 GB

About 14GB are used from OS and firebird processes under max load.

HDD: SAN Storage System


Average system IOPS under load read: 100

Average system IOPS under load write: 550

Backup Restore IOPS read: 1700

Backup Restore IOPS write: 250

SAN IPOS Limit (max): 3000


Firebird Config Settings, based on defaults

DefaultDbCachePages = 1024

LockMemSize = 134247728

LockHashSlots = 20011

Database

size: about 45 GB

450 to 550 concurrent connections

Daily average of 65 transactions / second (peak should be higher)


FB_LOCK_PRINT (without any params) while system was slowing down (~4 
days uptime).


I have to note, Firebird was not able to print the complete output 
(stats was not cropped by me)



LOCK_HEADER BLOCK

Version: 16, Active owner:  0, Length: 134247728, Used: 82169316

Semmask: 0x0, Flags: 0x0001

Enqs: 4211018659, Converts: 10050437, Rejects: 9115488, Blocks: 105409192

Deadlock scans:   1049, Deadlocks:  0, Scan interval:  10

Acquires: 4723416170, Acquire blocks: 640857597, Spin count:   0

Mutex wait: 13.6%

Hash slots: 15077, Hash lengths (min/avg/max):3/  12/  25

Remove node:  0, Insert queue: 36, Insert prior: 74815332

Owners (456):forward: 131316, backward: 14899392

Free owners (9):forward: 39711576, backward: 49867232

Free locks (42409):forward: 65924212, backward: 23319052


With best Regards,


Patrick Friessnegg

Synesc GmbH

Re: [firebird-support] Find rarely used or unused indices

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Check your database with Database Analyst from HQbird (trial will be 
enough) and look at the list of Useless indices (indices with only 1 key 
for all values) and Bad indices (with a few keys for many values).


Then, you need to run tracing of your database in action, for example, 
with HQBird PerfMon with option Show plan text, and analyse log database 
for indices included in plans - essentially, build a list of them (it 
should be done manually).


Then perform
select rdb$index_name from rdb$indices
and compare these 2 lists, then figure out which indices are not used.

In general, this idea is not so good as it appears - if index has good 
selectivity, Firebird usually uses it very effectively, until you have 
some abandoned parts in the database, where you never send SQL queries.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com







Hi

What is the best way to go about to find out about this?

I have several projects that are using the FB database and I would 
like to cleanup and remove all the indices that are just taking up 
space and thus indirectly reduces performance?


Best regards

jardar

Re: [firebird-support] help to repair a corrupted database

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Gfix visits each page of the database, so 90 minutes is not so long if 
your disk subsystem is not based SSD.


In general, follow these steps
http://ib-aid.com/en/articles/how-to-repair-a-corrupt-firebird-database/

Check firebird.log too - gfix reports there its findings.

Regards,
Alexey Kovyazin
IBSurgeon



hi, i'm trying to repair a corrupted db for firebird 2.5.2,  the 
database file is of about 30 gb.


i have disconnected all the user, i did a copy of the file and then i 
executed the command
gfix -validate 'dbname' with the credentials of the user that created 
the db (not the sysdba)


now the gfix process is 90 minutes that is on 0 cpu usage and 1 mb of 
ram but the firebird process sometime works,


is the gfix process freezed or it's normal? can i terminate it and 
retry or maybe this can add other problems to my database file?


thanks

Re: [firebird-support] lock conflict deadlock

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Sergio,

Definitely there should transaction which produces conflict, probably 
your query does not catch it.


You can track it with Monlogger - download HQbird, install it and 
register as trial (14 days), then connect with MonLogger to your 
database and at the moment of error message click get Snapshot.


MonLogger saves all data from MON% tables for the investigation in the 
folder, and on the tab Transaction it highlights with red color those 
transactions which are potentially conflict, so you can review which 
application runs it.


Regards,
Alexey Kovyazin
IBSurgeon










Hello! I'm having a problem which I don't undestand. I'm getting a 
deadlock error in my app, but when I see the active transactions (I 
attach the select I'm using) I see all read-only transactions active.


So, the question is: How can I get a deadlock error when I have just 
read-only transactions active?



To solve the problem, I have to stop/restart Firebird.


Thanks!!


Sergio


---


select

ma.mon$attachment_id,

mt.mon$transaction_id,

ma.mon$server_pid,

ma.mon$state,

ma.mon$attachment_name,

ma.mon$user


from

mon$transactions mt

left join mon$attachments ma on ma.mon$attachment_id = 
mt.mon$attachment_id



order by mt.mon$transaction_id

Re: [firebird-support] What makes a database file size so big?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

download HQbird Standard trial (http://ib-aid.com/en/hqbird/) and 
analyze your database statistics with Database Analyst and database file 
DBInfo.


Database Analyst will give you an exact information of tables and 
indices sizes tab Tables -> sort on Size), their fragmentation due to 
versioning and blobs.
DBinfo will give you information about distribution of pages with 
numbers and percentage of page types.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com






Hi,
I use firebird 2.5 classic server on windows operating system.
Because our database file is quite big (18GB) and it starts to cause
(because of its size) some maintenance inconvenience we decided to try
to shrink it. After checking database statistics we deleted "useless"
records in tables that took the most space, we updated old useless blob
fields with null values. After backup and restore it turned out that new
database is only 3GB smaller than original. We checked the statistics
and according to results we have 2,3GB raw data in database file.
Restore only metadata creates database of 70MB size, restoring without
active indexes results with file 500MB smaller than original. I'm very
curious - so what makes our database file so big? Sum of clear metadata
and raw data is about 2,4GB, and what with other 12GB? Is anything we
can do to shrink our big database file (drop useless indexes or
something like that)?

Thank you in advance for your answer.

Best regards,
Małgorzata Barchańska

--
Pozdrawiam
Małgorzata Barchańska

Fast Data Sp. z o.o.
Aleja Zwycięstwa 96/98, 81-451 Gdynia
Tel.: (58) 732 72 01
http://www.fastdata.com.pl/
NIP : 5862214954,REGON : 220595046
KRS : 304310, Kapitał zakładowy 50 000 zł

Re: [firebird-support] How do find duplicates in a table?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

It is faster to use RDB$DB_KEY to find and delete duplicates.

See more details here
http://ib-aid.com/en/articles/how-to-find-and-delete-duplicate-records-in-firebird-database/

Regards,
Alexey Kovyazin
IBSurgeon




I had finally figured it out just before your reply

SELECT DISTINCT P.SOC_SEC_NO,
(SELECT COUNT(*)
FROM PERSON P2
WHERE P2.SOC_SEC_NO = P.SOC_SEC_NO) AS CNT
FROM PERSON P
WHERE P.SOC_SEC_NO IS NOT NULL
GROUP BY 1
HAVING (SELECT COUNT(*)
FROM PERSON P3
WHERE P3.SOC_SEC_NO = P.SOC_SEC_NO) > 1

This appears to work. See anything that I should change?

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Thursday, February 04, 2016 1:25 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How do find duplicates in a table?

04.02.2016 20:09, 'stwizard' stwiz...@att.net [firebird-support] wrote:
> How do I form a SQL Select statement that will return which records in
> my PERSON table have duplicate SOC_SEC_NO.

RTFM GROUP BY, HAVING, COUNT().

--
WBR, SD.

Re: [firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

There should a reason why optimizer chooses the wrong plan.
Try to recalculate indices statistics.

Also, for general performance, use optimized Firebird configuration:
http://ib-aid.com/en/optimized-firebird-configuration/

Regards,
Alexey Kovyazin
IBSurgeon HQbird
www.ib-aid.com

I am using Firebird 2.5.5 x64 on Windows 8.1 x64.  I wrote an 
application access a Firebird database.  So far so good.



One day, I notice a function running query accessing the database 
become extremely slow.  After I debug, I found the query is the cause:



SELECT MAX(A.PostDate) MaxPostDate

FROM SL_CS A, SL_CSDTL B

WHERE A.DocKey=B.DocKey

  AND A.Code='300-10001'

  AND B.ItemCode='OCC'

  AND B.UOM='UNIT'

  AND A.Cancelled='F'

  AND B.UnitPrice<>0


Further investigation and I found an interesting problem.  If the 
database file size is below 8GB, the query works normal.  Once the 
database file size grow beyond 8GB, the query become very slow.



Here is the plan before 8GB:


PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (RDB$PRIMARY212))


Adapted Plan

PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (INTEG_2872))


-- Performance info --

Prepare time = 0ms

Execute time = 312ms

Avg fetch time = 312.00 ms

Current memory = 14,858,352

Max memory = 14,972,720

Memory buffers = 2,048

Reads from disk to cache = 42,429

Writes from cache to disk = 0

Fetches from cache = 360,726


And the plan after grow to 8GB:


PLAN JOIN (A INDEX (SL_CS_CODE), B INDEX (SL_CSDTL_DOCKEY,
SL_CSDTL_ITEMCODE))


-- Performance info --

Prepare time = 0ms

Execute time = 2m 0s 875ms

Avg fetch time = 120,875.00 ms

Current memory = 14,943,040

Max memory = 15,005,768

Memory buffers = 2,048

Reads from disk to cache = 44,818

Writes from cache to disk = 0

Fetches from cache = 11,454,178

Both plan works on same tables with same row count.  Increasing of 
database file size is due to insertion on other tables.  I believe the 
performance issue was due to "Fetches from cache" increasing 
dramatically on 2nd plan.



Is this an issue related to Windows file system? Firebird service or 
my query?

Re: [firebird-support] Firebird Download Malware

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

>The following file is affected:
>http://sourceforge.net/projects/firebird/files/firebird-win64/2.5.5-Release/Firebird-2.5.5.26952_0_x64.exe/download

I can confirm that Security Essential also reported this exe as 
Trojan.Win32/Varpes.J!plock


Regards,
Alexey Kovyazin
IBSurgeon HQbird
www.ib-aid.com





The following file is affected:
http://sourceforge.net/projects/firebird/files/firebird-win64/2.5.5-Release/Firebird-2.5.5.26952_0_x64.exe/download

This seems to be a false alarm though.
According to Virustotal, 54 different scan engines say the file is
clean:
https://www.virustotal.com/de/file/e4ce70fd1be17b07fd26554ef322ee14f65fef8d398aaa707df115530aa0f410/analysis/

Funnily, the Microsoft scan engine at Virustotal also considers it as
clean.

Cheers,
Christian

Re: [firebird-support] Bad performance of Firebird in Windows Server 2012

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Eduardo,

Can you please give links to these articles?

Regards,
Alexey Kovyazin
IBSurgeon




I searched in Google and found that, for instance, MS suggests not to 
install SQL Server in a domain controller, but for security reasons. 
In some articles, MS even recommends to disable disk write cache when 
installing SQL Server.


I searched information about others RDBMS and generally they recommend 
to disable disk write cache to ensure database integrity. This seems 
reasonably. That´s why I am surprised about the difference in performance.


Regards

Eduardo

Re: [firebird-support] Bad performance of Firebird in Windows Server 2012

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Eduardo,

Did you read this?
"Enhanced caching controller systems disable on-disk cache and provide a 
functional battery-backed caching solution."


The idea of this article that battery-backed cache solutions should be 
used, instead of drives with simple cache.
It does not tell that cache is bad, just highlights the potential risk 
of data loss if caching without BBU is used.


So, essentially you need to buy advanced disk controller and properly 
setup it - that's true.


Regards,
Alexey Kovyazin
IBSurgeon



There is an interesting (and quite long) article about SQL Server and 
disk cache in MS site.


https://support.microsoft.com/en-us/kb/234656

They talk about "enhanced caching controllers" and they say things 
like the following:


  * /- SQL Server installations are targeted at systems that provide
caching controllers. These controllers disable the on-disk caches
and provide stable media caches to satisfy SQL Server I/O
requirements./
  * /- Your SQL Server installation depends on the controller's
ability to disable the on-disk cache and to provide a stable I/O
cache./
  * /- Note: If you have any question about the caching status of your
disk drive, contact the manufacturer and obtain the appropriate
utility or jumper settings to disable write caching operations./

You even can see a list of different HD drive types (IDE, ATA, SATA, 
SCSI) with instructions to disable disk cache. In any of them you will 
see the following phrase:


  * /- Disk caching should be disabled in order to use the drive with
SQL Server./


 Mensaje original 
*Asunto: *Re: [firebird-support] Bad performance of Firebird in 
Windows Server 2012
*De: *Alexey Kovyazin a...@ib-aid.com [firebird-support] 
<firebird-support@yahoogroups.com>

*Para: *firebird-support@yahoogroups.com
*Fecha: *31/12/2015 15:22


Hi Eduardo,

Can you please give links to these articles?

Regards,
Alexey Kovyazin
IBSurgeon




I searched in Google and found that, for instance, MS suggests not 
to install SQL Server in a domain controller, but for security 
reasons. In some articles, MS even recommends to disable disk write 
cache when installing SQL Server.


I searched information about others RDBMS and generally they 
recommend to disable disk write cache to ensure database integrity. 
This seems reasonably. That´s why I am surprised about the 
difference in performance.


Regards

Eduardo





<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=emailclient> 
	Este correo electrónico se ha enviado desde un equipo libre de virus 
y protegido por Avast.
www.avast.com 
<https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=emailclient> 

Re: [firebird-support] Firebird 3.x timings

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

We are running Firebird Performance tests everyday (using oltp-emulation 
test), for 2.5 SC and 3.0 SS respectively:

http://ib-aid.com/en/logs-2-5/
http://ib-aid.com/en/logs-3-0/

These tests are running to catch fluctuations from one build to another, 
but they also show that 3.0 SS is 20-25% better than 2.5 SC.


Keep in mind that test is designed to simulate parallel write load.

Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com



Is FB 3.x much faster than FB 2.5.x? I'm looking for some timing and 
stability comparisons.


Thanks.

Re: [firebird-support] UPDATE to same record causing heavy disk I/O

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Caroline,

It could be a chain of many record versions.

If you have such database handy, run
gstat -a -r   > stat.txt

and load it to HQbird Database Analyst (trial version will be enough), 
then open tab Tables and sort on Max Versions column.


Check how many versions on the table you are updating and what is the 
length of Max Versions (the longest chain of versions here).


If you will see a long chain of versions, it means that you are updating 
the same record while some other writeable transaction is active, or 
some old transaction was rollabacked with mark on TIP, so it makes 
Firebird to preserve versions of updated record.


In general, avoid multiple updates of the same record - replace them 
with INSERTs and some scheduled delete, right before sweep.


Regards,
Alexey Kovyazin
IBSurgeon



Since I began using Firebird, I have kept my transactions (type 
concurrency) very short and then call COMMIT immediately afterward.  
This has worked very well.


I recently had the need to perform more complex processing and what I 
did was to keep everything short and modular.  But I am now seeing 
that my design in this specific case was flawed.


I am updating different parts of the same record repeatedly and I 
believe that this is causing multiple back versions which causing 
excessive disk write I/O and slowing things down terribly:


a) begin a transaction, update FIELD_1 of MYTABLE, COMMIT, and end 
transaction.
b) begin a transaction, update FIELD_2 of MYTABLE, COMMIT, and end 
transaction.
c) begin a transaction, update FIELD_3 of MYTABLE, COMMIT, and end 
transaction.
d) begin a transaction, update FIELD_4 of MYTABLE, COMMIT, and end 
transaction.
e) begin a transaction, update FIELD_5 of MYTABLE, COMMIT, and end 
transaction.
Note: other tables are inserted and updated during during transactions 
a-e but those tables are not presenting any problems.  The problem is 
with MYTABLE.


Of course, I normally update all fields in one transaction but in this 
particular case, determining the contents of each field is a complex 
process that requires manipulation and analysis of the the data 
provided by a number of other Firebird SELECT queries to the database.


I am averaging about 300 transactions per minute during this process 
that may last 12 hours and during that time, things get terribly slow.


So can someone confirm my suspicions, will each of the 5 transactions 
above to the same row of data cause 5 new 'back versions'?


Like I said, I have always kept transactions very short.  I am 
thinking of something like this instead:


a) begin a transaction, update FIELD_1 of MYTABLE.
b) update FIELD_2 of MYTABLE.
c) update FIELD_3 of MYTABLE.
d) update FIELD_4 of MYTABLE.
e) update FIELD_5 of MYTABLE, COMMIT, and end transaction.
If something fails anywhere in between, I would ROLLBACK this single 
transaction.


Keeping my transactions shorter and more modular as above is easier 
from a development point of view but I have the back version 
performance issue.  Although the second method means a much longer 
transaction, I won't have back versions to deal with.  Do you think 
that this approach would be better?


Thank you

P.S.  Sweeping the database does not help with the performance 
problem, the only temporary solution to regain performance is to 
backup using GBAK and restore.

Re: [firebird-support] FB lock files

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Rudi,

You did not provide all required information to give you a reasonable 
answer.
At least, post fb_lock_print -d  output.


Regards,
Alexey Kovyazin
Try HQbird now! www.hqbird.com






++

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] Replication SW experience

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Try IBReplicator.

Regards,
Alexey Kovyazin
IBSurgeon





Hi all,
we are preparing new project with bidirectional replication.

Customer idea is to have master database in head quoter
and slave databases on production plant (to solve network performance
and availability issues).

Witch replication SW you can recommend for this bidirectional replication?
What is typical issues and weakness of this SW.

Thank you.

Slavek

--
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
--
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
--
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:skopa...@elektlabs.cz
http://www.elektlabs.cz

Re: [firebird-support] Firebird for a web application

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Steve,

What kind of monitoring are you doing with Nagios?
Do you mean this plugin? 
http://exchange.nagios.org/directory/Plugins/Databases/check_firebird-2Epy/details


Normally, Firebird monitoring should include:
1) Transactions monitoring, including long-running and stuck transactions
2) # of simultaneous connections, statements, transactions, and 
associated counters (memory, IO)

3) Temp files size and quantity
4) Lock table parameters monitoring
5) Firebird.log monitoring
6) Indices health monitoring: activated/non-activated, non-restored
7) Database files size monitoring, including delta files in case of 
nbackup lock

and some others.

Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com





Hi Mirco,

We have written multiple java EE web applications with Firebird on 
linux as the database.  Usually we will use Glassfish (or Payara) as 
the app server and run Firebird in classic or superclassic form.  We 
will usually use Nagios to monitor the servers and databases.


My only complaint is that sometimes we can get the lock manager bogged 
down when we are really pounding a database with a large amount of 
small transactions.  Otherwise we are overall pretty happy.


Good luck!

-steve


On Wed, Nov 18, 2015 at 2:42 PM, mirco.malag...@ingeniaonline.it 
 [firebird-support] 
> wrote:


Hi all here,

I want to ask some question about using Firebird in a production
environment for a web application.

My experience is more then 12 years with Firebird for our client
server application and have to say is a very good experience.

Now that application is being ported to web by a complete rewrite.

From an architectural point the database is single instance and
multi-tenant, using logical tenant ids.

The application is a rather standard JavaEE one.

This means that all of our customers will have all their data
together and obviously my main concerns are reliability,
availability, performance and maintainability.

The application is mostly interactive in an OLTP fashion, with
little or no datawarehousing.

We expect to have a production database of more or less 200 GB in
size.

The system will be Linux-based hosted somewhere by our provider.

We now have to choose between Firebird and other engines, so I
have to asses:

- tools available for effective remote monitoring, analysis,
tuning and troubleshooting

  I'm told that here standard names are nagios, cacti, SNMP and others

- experience by someone that had worked or is working on system of
this kind


My intention is to stay with Firebird because my experience is
positive and like to stay in an environment that take simplicity
as a value, but I want to do what will need to be done well, anyway.


Any help is greatly appreciated.


Mirco

Re: [firebird-support] Interbase 5.6 (Windows 98) not available on network

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Virgo,

You should be able to connect to this database with your Firebird 
(except 3.0).
There could be incompatibilities, of course, like reserved words (YEAR, 
TYPE), but in general it should work for simple SELECT * FROM table.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com


It is offtopic question, but I dont really know where to ask
and here are probably people, who know someting about older Interbase
versions too.

Customer is interested in using data from existing Interbase 5.6
database in our application (it is currently entered manually). But the
database is on Windows 98 computer (because specific hardware connected
to it) and that Interbase does not seem to be available from network at
all. Could it be, that depending of the Interbase licence network
connections are not available at all? There was some licence file in
Interbase directory, that seemed to indicate 2 user limit, but nothing
else.

--
Virgo Pärna
virgo.pa...@mail.ee

[firebird-support] Firebird Language Reference - Call For Reviewers

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

 Hi All,


The Firebird Docs team is calling for people who are fluent in both 
English and Firebird's SQL to assist with reviewing the English text of 
the Firebird 2.5 Language Reference manual.


Of 10 chapters and 8 Appendices, 4.5 chapters are still untouched by 
reviewers. It would be good to have the reviewing done to coordinate 
with the forthcoming 2.5.5 release but correctness takes priority.
Still to do are: half of the DDL chapter, DML, PSQL, Functions and 
Variables, Transactions.


If you feel able to help with the review, please subscribe to the 
[firebird-docs] list (send any email to 
firebird-docs-requ...@lists.sourceforge.net) and make yourself and your 
preferred topics known.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com

Re: [firebird-support] Running gfix and gbak from windows service

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

this is a known bug with Services API in 2.5.3 and 2.5.5.
We had the same problem with our HQbird FBDataGuard, and with our report 
it was fixed in 255.


Use 2.5.5 - it has this fix, and it is in pre-release state, and in 
general it's much more stable than 2.5.3 and 2.5.4.


Regards,
Alexey Kovyazin
IBSurgeon
www.ib-aid.com


We using both gfix and gbak from windows service to perform routine 
maintenance operations


such as moving db to shutdown mode performing check and then backup 
then online mode



This works fine on Firebird 2.5.2 however it does not work on 2.5.3 & 
2.5.4



The gfix or gbak just start running then hangs for hours doing nothing 
till I terminate the process



The problem seems to exists on windows 7 & 8.1 don't know about other 
OS's



I tried running the service using user account instead of the system 
account but it did not help



Does anyone has a suggestion or work around ?

Re: [firebird-support] Re: Firebird replication help needed

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Try  IBReplicator (commercial) instead.

Regards,
Alexey Kovyazin
IBSurgeon

I've downloaded the FB Replicator and tryed it on my database. When I 
want to add it as a souce database  I enter path to the DB user pass 
and click save, but it gives me the following error:



ISC ERROR CODE: 335544323
ERROR MESSAGE:
file is not a valid database

The problem is that it is ;). After 4-5 attampts it gives me another 
error with something about a closed DataSet. What should I do? Is 
there any documentation about it? I googled the error and ... nothing.


Thank you

Re: [firebird-support] Securing database against corruption on systems that suddenly get turned off

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

In addition to FW On and UPS, I suggest to look at our tool FBDataGuard 
(http://ib-aid.com/en/fbdataguard), which is specifically designed to 
protect databases against corruptions (among other features).
FBDataGuard stores a copy of raw metadata in the repository outside 
database, and if corruption happens, this repository will be used to 
recover database with our recovery tool (FirstAID). In our experience 
such approach increases chances to recover up to 99%.


Regards,
Alexey Kovyazin
IBSurgeon





Hi,

We have FirebirdSQL (2.1) running on a client computer (Windows). This 
client has some problems with his power grid which causes the PC to 
suddenly and unexpectedly shut down in infrequent intervals (usually 
once or twice per day). Most of the time this doesn’t cause any 
problems, but once in a while this causes the database to be corrupted.


I’m not at all surprised that the database gets corrupted like this 
(as turning off a computer without shutting everything down gracefully 
can have that effect).


Are there any options available that can make a database more sturdy 
against these kinds of corruptions?


Cheers

Glenn Thomas


Denne epost har blitt kontrollert for virus av Comendo Norge AS 

This e-mail has been scanned for viruses by Comendo Norge AS 

Re: [firebird-support] Firebird 2.5 Language Reference

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Translation was completed several weeks ago, now Helen Borrie performs 
proof-reading and editing of the text.

Recent preview version of LR looks very promising.

Regards,
Alexey Kovyazin
IBSurgeon



Hello everybody

Do you know what has happened with the translation of the document 
"Firebird 2.5 Language Reference" from Russian to English?


They had requested for donation with the promise that their work would 
be ready in February 2015.


The donation was made, now it is September 2015, but without news 
about the document translated.


I want to translate from English to Spanish (and for free, I don't 
want to receive money for that). I don't know much Russian and the 
Google translator is not good from Russian to Spanish.


Greetings.

Walter.

Re: [firebird-support] Firebird 2.5 Language Reference

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Translation was completed several weeks ago, now Helen Borrie performs 
proof-reading and editing of the text.

Recent preview version of LR looks very promising.

Regards,
Alexey Kovyazin
IBSurgeon


Hello everybody

Do you know what has happened with the translation of the document 
"Firebird 2.5 Language Reference" from Russian to English?


They had requested for donation with the promise that their work would 
be ready in February 2015.


The donation was made, now it is September 2015, but without news 
about the document translated.


I want to translate from English to Spanish (and for free, I don't 
want to receive money for that). I don't know much Russian and the 
Google translator is not good from Russian to Spanish.


Greetings.

Walter.

Re: [firebird-support] What is better: UPDATE or DELETE + INSERT?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

See page 20-21 here
http://ru.scribd.com/doc/20128182/The-InterBase-and-Firebird-Developer-Magazine-Issue-2-2005

Regards,
Alexey Kovyazin
IBSurgeon


Hello Sean, and everybody

That's a very interesting and very important theme: "the new record 
version will only include the updated fields -- not a full copy of the 
row"


Do you know where I can find a document, web-page, book or video that 
explains that? If it has images or graphics, much better.


Thanks in advance.

Greetings.

Walter.

Re: [firebird-support] HUGE Log File

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Usually such huge log is related with a database corruption.

Can you please send here small excerpt from the most recent records of 
firebird.log (10-15 lines)?

Regards,
Alexey Kovyazin
IBSurgeon

Re: [firebird-support] Growing security2.fdb

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Neil,

Run DBInfo from IBSurgeon Free tools and check what pages are there.
I suppose there are many TIPS, which indicate transactions.
Verify it with gstat -h transaction markers values.

Regards,
Alexey Kovyazin
IBSurgeon



Hello

Something I have noticed with Firebird v2.1 with Windows 32 & 64 bit 
Superserver & Classic and is that the security2.fdb file grows 
substantially and keeps on growing.


We use just a single user for database access.

I have one system at the moment where it is 50Meg but have seen it at 
200 meg or 300 meg or even larger.


Is this a problem, should it do this, why does it do this?

Any ideas?

Cheers,

Neil Pickles - n...@csy.co.uk 

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

[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]" 
<firebird-support@yahoogroups.com>

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] 
<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. ** <http://www.planatec.es>
telf: +34 964 340 560 <tel:%

Re: [firebird-support] About firebird performance

[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]
>:

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] Firebird backup on virtual machine

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

 I can do regular backup with gbak but i look for some smarter solution 
to.


To backup any of the databases with VM snapshots or similar mechanisms a 
series of special preparation and post-backup steps must be fulfilled, 
or database should be in archivelog (Oracle) mode.

You can use nbackup to lock database before VM snapshot, in this case 
only delta will be at risk.

However, nbackup-only solution is not smart, since it does check an 
integrity of database, as gbak does.
We recommend to use combination of nbackup and gbak.

Regards,
Alexey Kovyazin
IBSurgeon







++

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] FB 2.5 Lock conflict on no wait transaction

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Hernado,

You are the third person during several days with this question.
Can you please post the full error text?
Otherwise it is not possible to provide any help.

Regards,
Alexey



Hi to all,


From several days ago we are having trouble with Lock conflict on no 
wait transaction on a Firebird 2.5 server thas was working fine since 
several months ago on a Windows 2008 Server.



We use gfix with -list option but it seems not to be any transaction 
pending. The gfix -commit  all command has no efect. The only way to 
unlock the database is restarting the firebird service.



There have not been changes on harware neither software in the server.


Any help will be aprreciated.


Regards,


Hernando.

Re: [firebird-support] Re: DeadLock Conflict

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Please provide full error text and detailed description of the problem.

Regards,
Alexey Kovyazin
IBSurgeon


Hi all

I experience the same problem.
Is there a tutorial to track theese kind of problems?

tks
Razvan

Re: [firebird-support] DeadLock Conflict

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Probably you mean Lock conflict, not a true deadlock (full error text 
will help).
If you see transaction number in the message, you can track it using 
MON$ tables, and then find out which connection has started it.


Regards,
Alexey Kovyazin
IBSurgeon




Hi All

If possible at all, how can I trace which workstation (Firebird Client) is
holding a record hostage and thus causing a deadlock conflict?

Regards

Stef van der Merwe

email s...@autotech.co.za

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

Re: [firebird-support] Problem with FB database that freezes

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,


OAT is an active transaction. You can see it alive if you analyze MON$ 
snaphot (you can use trial of our FBMonLogger).


So, disconnects are not related with Next-OAT gap - because this gap is 
caused by some open transaction which you can easily identify.


However, disconnects can lead to forced rollback, and as a result, OIT 
could stuck, and it will lead to increased gap OIT-OST, which is also 
not good.


For more details about OIT, OST and OAT read this ppt
http://www.slideshare.net/ibsurgeon/3-how-transactionswork

Regards,
Alexey Kovyazin
IBSurgeon





Hi there,


Can the connection errors in the log contribute to the increasing gap 
between the OAT and NT?



Regards,


-Eduardo

Re: [firebird-support] Corrupted database

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

It is a typical corruption usually caused by Firebird abnormal termination due 
to power outage.

You can fix it with IBSurgeon FirstAID more details at www.ib-aid.com

Regards,
Alexey Kovyazin
IBSurgeon

16 июня 2015 г. 6:50 пользователь Nagy Szilveszter nagy_szilvesz...@yahoo.com 
[firebird-support] firebird-support@yahoogroups.com написал:

  

 Hello,

 one of my firebird databases got corrupt in the last couple of days. It's a 
 pretty big one the FDB file is 8 GB.


 The error I observed in the applications logfiles. It happens when the users 
 try to run a specific SP. The error message is:

 # -902 # database file appears corrupt (/data/firebird/vec.fdb) wrong page 
 type page 112600 is of wrong type (expected 5, found 7) At procedure 
 ''SCOR_CANDIDAT'' line: 38, col: 3

 The 38th line of this SP is an UPDATE command on a huge table.

 I tried to fetch that table and it shows the same error at line 115904. (I 
 have 125556 records in the table...at least this is what i calculated from ID 
 value of corrupted line subtracted from generator value)


 I tried to backup the database to see what happens. The same output comes 
 from gbak:

 gbak: ERROR:database file appears corrupt (/data/firebird/vec.fdb)
 gbak: ERROR:    wrong page type
 gbak: ERROR:    page 112600 is of wrong type (expected 5, found 7)
 gbak: ERROR:gds_$receive failed
 gbak:Exiting before completion due to errors


 The FireBird Server version is: 
 firebird-superclassic-2.5.2.26539.0-1.el6.x86_64

 Running on a 64bit CentOS Linux.



 Can anyone help me repair it?



 Thank you,
 Szilveszter


 
 
 Posted by: Nagy Szilveszter nagy_szilvesz...@yahoo.com
 
 table cellspacing=4px style=margin-top: 10px; margin-bottom: 10px; 
 c/blockquote/div

Re: [firebird-support] How do i access firbird database to manipulate the databases

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

You can use IBDataPump free tool. It allows data pumping between exact 
databases or using custom Select queries.


Regards,
Alexey Kovyazin
IBSurgeon


Hi there,

I am new to firebird and need to manipulate or merge two firebird 
databases. The tables should be the same format but I need to be able 
to merge both so it shows both tables data on it. Or is there a way of 
manipulating the database so i can add the extra patients. Please let 
me know how I would be able to do so.


Please let me know.

Thanks

Regards,

Hasnain Najafi
Tel: 07949 037182
[Computer And Data Services Logo 200px][AssuredVendorLogo 200px]
Computer  Data Services Ltd, Registered 08243376, London EC1V 4PW. 
This e-mail (including any attachments) is confidential. It may be 
read, copied and used only by the intended recipient. If you are not 
the intended recipient you should not copy it or use it for any 
purpose or disclose its contents to any other person. If you have 
received this message in error, please notify us and remove it from 
your system. We cannot accept liability for any damage you incur as a 
result of virus infection.


Boots UK Limited, Registered 928555, Nottingham NG2 3AA This e-mail 
(including any attachments) is confidential. It may be read, copied 
and used only by the intended recipient. If you are not the intended 
recipient you should not copy it or use it for any purpose or disclose 
its contents to any other person. If you have received this message in 
error, please notify us and remove it from your system. We cannot 
accept liability for any damage you incur as a result of virus infection.


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

Re: [firebird-support] Memory usage excess / leak in FBServer 2.5.4

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Jojakim,

Can you please do the following - download FBMonLogger trial from 
http://ib-aid.com/en/fbmonlogger/

install it, run it, connect to the database and make snapshot  of MON$.
It will grab all information from MON$ tables and store it to the disk 
(you will be requested to select folder at the first step).

Then please zip it and send to supp...@ib-aid.com for the investigation.

Regards,
Alexey Kovyazin
IBSurgeon


Hello,

I'm facing a problem where the firebird server v2.5.4 on win 2012 
server in superserver mode is leaking memory. Currently I'm at ~5GB usage.


Don't have any idea how to track down the problem.

Thanks for any advice.

--

joja

select * from mon$memory_usage where mon$stat_group=0;

MON$STAT_ID 1

MON$STAT_GROUP 0

MON$MEMORY_USED 4570256632

MON$MEMORY_ALLOCATED 5314478080

MON$MAX_MEMORY_USED 4570448304

MON$MAX_MEMORY_ALLOCATED 5314678784

select * from mon$database;

MON$DATABASE_NAME D:\P2_DB\FIREBIRD\DBA9627.FDB

MON$PAGE_SIZE 8192

MON$ODS_MAJOR 11

MON$ODS_MINOR 2

MON$OLDEST_TRANSACTION 35186254

MON$OLDEST_ACTIVE 35186255

MON$OLDEST_SNAPSHOT 35186255

MON$NEXT_TRANSACTION 35187662

MON$PAGE_BUFFERS  16384

MON$SQL_DIALECT 3

MON$SHUTDOWN_MODE 0

MON$SWEEP_INTERVAL 2

MON$READ_ONLY 0

MON$FORCED_WRITES 1

MON$RESERVE_SPACE 1

MON$CREATION_DATE 2015-04-15 08:58:37.1050

MON$PAGES 250031

MON$STAT_ID 1

MON$BACKUP_STATE 0

show version;

ISQL Version: WI-V2.5.4.26856 Firebird 2.5 Server version:

Firebird/x86-64/Windows NT (access method), version WI-V2.5.4.26856 
Firebird 2.5


Firebird/x86-64/Windows NT (remote server), version WI-V2.5.4.26856 
Firebird 2.5/tcp (SV-W12-L900)/P12


Firebird/x86-64/Windows NT (remote interface), version 
WI-V2.5.4.26856 Firebird 2.5/tcp (SV-W12-L900)/P12


on disk structure version 11.2

Re: [firebird-support] GBAK - validation error for column X, value *** null ***

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Josef,

Such error is the consequence of the corruption - in the original 
database there was a NULL in the field (most likely, all fields in that 
record are NULL).

gbak does not check constraints when reading.

The best way to fix it is to find record with NULLs in the original 
database and delete it, then repeat backup/restore.

If you don't have original records, it can be a problem.
Try to restore database till the privileges error and then pump data 
from partially restored database to the new empty database with the same 
structure - you can use free tool IBDataPump for it.
If problems continue, you can export data using our proprietary 
IBBackupSurgeon tool (http://ib-aid.com/en/ibbackupsurgeon/), which can 
export data on low-level.


Regards,
Alexey Kovyazin
IBSurgeon




Hi!

What would be a proper way of dealing with GBAK restore error
'validation error for column X, value *** null ***'? This error
appears in one table, where X is the primary key. The database is
Firebird 2.5.4 in the superserver mode.

1) How do I properly restore this backup? Neither -INACTIVE nor
-NO_VALIDITY (nor both at the same time) help, the restore stops as soon
as the first error is encountered. -ONE_AT_A_TIME gets me to the end of
table data, but then I get another error at the restoring privilege for
user Y action cancelled by trigger (3) to preserve data integrity
table/procedure has non-SQL security class defined.

2) Why doesn't GBAK complain during backup? I would expect that if a
backup is successfully made, I will be able to restore it.

Thanks,

Josef

Re: [firebird-support] Re: How to determine size of database structures?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Bruce,

To view size of tables and indices you need to use our IBAnalyst tool  
(http://ib-aid.com/en/ibanalyst/).


Regards,
Alexey Kovyazin
IBSurgeon




Thank you Alexey.

This is nice tool, however it does not show how much space is taken by 
given table and its indices... I am sure this can be deduced by 
reading gstat output file but I don't know how...

Re: [firebird-support] How to determine size of database structures?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Bruce,

DBInfo from IBSurgeon Free Tools 
(http://ib-aid.com/en/ibsurgeon-free-tools/) will give you database 
pages statistics.


Regards,
Alexey Kovyazin
IBSurgeon




Hi guys.

I know that I need to use gstat, and I know that I have detailed 
description about it here: Gstat Examples And Interpretation 
http://www.firebirdsql.org/manual/gstat-example-header.html





image http://www.firebirdsql.org/manual/gstat-example-header.html


Gstat Examples And Interpretation 
http://www.firebirdsql.org/manual/gstat-example-header.html
Gstat Examples And Interpretation Database Header Analyse Entire 
Database Analyse Data Pages Only Analyse Index Pages Only Selecting T...


View on www.firebirdsql.org 
http://www.firebirdsql.org/manual/gstat-example-header.html


Preview by Yahoo


However, the description does not say how to determine how much space 
on hard drive is taken by tables and indices. Somewhere I've read that 
to calculate size of a table I need to multiply the DB page size with 
data pages reported by gstat for each table. Is that a correct method?


Could you tell me how to calculate how much space is taken by indicies 
based on gstat output file?


Regards.

Re: [firebird-support] Profiler for 2.1?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Tim,

2.1 series was discontinued, so nobody will invest in tools for it.
Instead of this, consider migration - 2.1 is pretty close to 2.5, where 
TraceAPI is available.


Regards,
Alexey Kovyazin
IBSurgeon




I've just discovered MON$CALL_STACK, and it seems possible that one
could use this to produce a Monte Carlo type profiler - query it once
every so many seconds, and built up a tree of how often each path
through nested procedures has been hit, thus pointing at which paths
through the code are taking the time.

Do any of the available tools have this feature? - the output I'm after
is a graphical drill-down calling tree like the ones you get from
profilers for conventional languages.

--
Tim Ward

Re: [firebird-support] nbackup problem : Database is already in the physical backup mode

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Hugo,

So if the computer is shutdown during a nbackup the database is locked 
in this way indefinelty?




Yes, it's one of the most prevalent problems with nbackup.
To prevent it you need to monitor delta file age and size - manually, or 
automatically using our FBDataGuard tool.





What do you suggest I should do with JayBird when the exception is thrown?
Execute alter database end backup?



Yes, it should merge delta file back to the main database and unlock it.

Regards,
Alexey Kovyazin
IBSurgeon

Re: [firebird-support] Re: Awaiting Garbage Collector

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Walter,


 I have just one doubt, and it is with page 46.

Do you mean Record versions visibility?

Regards,
Alexey

Re: [firebird-support] Re: Awaiting Garbage Collector

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Walter,

On slide 46 of http://www.slideshare.net/ibsurgeon/3-how-transactionswork
we consider how transaction 20 view record versions.
It's important to note that transaction 20 is a snapshot.

Tx16 did the INSERT?
Yes, it created original version of Record 1.

Tx12 can do a COMMIT although it had started before Tx16 and Tx18 is 
still active?


Sure, why not?

Tx25 can change what Tx14 did although Tx14 is still active?

Yes, it looks a bit confusing, but this is a snapshot view which 
highlights that Tx 20 CANNOT see record versions from a) active 
transactions, b) transactions with Number  20.


It means that for snapshot transaction 20 all transactions above its 
number look like active, and as a result, it cannot view record versions 
from both from Tx 14, which was active at the moment when snapshot copy 
of TIP for Tx25 was taken, and it also cannot view record version from 
Tx 25, which, obviously was created after Tx 14 was committed. But, 
again, for  Tx 20 these both record versions are invisible. Tx20, as a 
snapshot, thinks that Tx is active, but in reality it was committed.


Regards,
Alexey Kovyazin
IBSurgeon







Hi Alexey

Yes, that's right, I can not understand well the page 46. I suppose 
that R1 means record version 1, R2 means record version 2, etc.


Tx16 did the INSERT?
Tx12 can do a COMMIT although it had started before Tx16 and Tx18 is 
still active?

Tx25 can change what Tx14 did although Tx14 is still active?

These things had confused me.

Of course, I think that I am not understanding rightly the graph, 
that's why some words can help to do it clear.

Re: [firebird-support] Re: Awaiting Garbage Collector

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

Unfortunately, your experiments are not useful at all, since you are 
using complex GUI tools, which run background queries in the frames of 
implicit transactions to get metadata information, etc.
If you really want to do clean experiments with Firebird transactions, 
use only isql.exe, and run queries to your tables and MON$ tables there.


Also, look into this presentation
http://www.slideshare.net/ibsurgeon/3-how-transactionswork

Regards,
Alexey Kovyazin
IBSurgeon




Regarding the Awaiting GC issue I've done another test:


1- Connect to the database in a test environment, isolated from the 
rest of users from production environment. with IBExpert or another 
database manager. Only one connection.


2- Monitor tha database with Sinatica Monitor to get number of active 
transactions, attachments and statements. At this moment only 1 
connection is showed in Sinatica. 2 statements that are querys to 
system tables, I supose form IBExpert connection, 0 awaiting GC and 1 
awaiting Sweep.


3- Run a query SELECT * FROM CALENDAR WHERE EVENT_ID=132465 which only 
returns a record. I did not COMMIT that.


4- Now SINATICA shows how AWAITING GC is increasing by 1 or 2 every 
second. In a minute it shows more than 100 Awaiting GC.


5- COMMIT and now SINATICA shows AWAITING GC is again 0.

I did the same thing with EMS SQL MANAGER and the result is the same. 
Leaving an interesting transaction on a single record table generates 
a big amount of garbage.


Is that normal?

[firebird-support] ANN Firebird 2015 Tour: SQL optimization

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi All,

We invite all Firebird developers and administrators to 1-day seminar in 
the frames of Firebird 2015 Tour.  Firebird 2015 Tour is devoted to the 
SQL performance optimization:  SQL plans, queries and database 
statistics, query tuning techniques and deep understanding of undercover 
work of Firebird.


Firebird 2015 Tour is organized by IBSurgeon and IBPhoenix, with support 
of Firebird Developers core team.
Locations and dates of Firebird 2015 Tour (see details in the 
appropriate sections below):


April 24 - Seligenstadt, Germany
May 19 – Prague, Czech Republic
June 5 – Moscow, Russia

Topics of Firebird 2015 Tour seminar:

1)Performance of SELECT queries
 a.PLANs: what are INDEX, SORT, ORDER, NATURAL and how to manage them
 b.Execution statistics interpretation, data paths, cache hits
 c.Tracking slow and resource-consuming resources through MON$ and 
Trace API

 d.Firebird configuration parameters
 e.Best practices, tips and tricks to speed up queries

2)Performance of INSERT queries
 a.Typical errors which lead to slow INSERTs
 b.How indices, triggers and database structure affect INSERT 
performance

 c.Transactions and INSERTs
 d.Best practices to speed up INSERTs

3)Performance of UPDATE and DELETE queries
 a.General approach for updates and deletes
 b.How indices, triggers and database structure affect UPDATE and 
DELETE performance
 c.Update Conflicts, transactions and their resolution, DELETE, 
SELECT WITH LOCK

 d.Real-life example of UPDATEs algorithm without conflicts
4)Multi-user performance optimization
 a.Locks, mutexes and races for resources
 b.Selecting Firebird architectures and tuning their parameters for 
multi-user environments


Locations details for Firebird 2015 Tour

April 24, Seligenstadt, Germany

Seminar will take place in Hotel Columbus 
(www.hotel-columbus.de/contact.php/), April 24, 2015.

Important! All topics are presented in English!

Seminar's schedule:
9-30-10-00 Registration
10-00 - 17-00 Seminar (with Lunch and Coffee breaks)

Attendee's package includes seminar’s materials, lunch and coffee breaks.

The early bird registration fee is EUR 49 (till April 5, volume 
discounts are available).

https://secure.shareit.com/shareit/checkout.html?productid=300604679

May 19, Prague, Czech Republic

Seminar will take in the Hotel Extoll Inn 
(http://extolinn.cz/en/contact.htm),  May 19, 2015.

 Important! All topics are presented in English!
Seminar's  schedule:

9-30-10-00 Registration
10-00 - 17-00 Seminar 10-00 - 17-00 Seminar (with Lunch and Coffee breaks)

Attendee's package includes seminar’s materials, lunch and coffee breaks.

The early bird registration fee is EUR 39 (till April 25, volume 
discounts are available)

https://secure.shareit.com/shareit/checkout.html?productid=300660565

June 5, Moscow, Russia

Seminar will take place in TechnoPark «Slava» 
(www.technopark-slava.ru/contacts),  June 5, 2015.

Important! All topics are presented in Russian!

Seminar's  schedule:
9-30-10-00 Registration
10-00 - 17-00 Seminar (with Lunch and Coffee breaks)
Attendee's package includes seminar’s materials and coffee breaks.

The early bird registration fee is EUR 39 (roubles 2800) (till April 25).
https://secure.shareit.com/shareit/checkout.html?productid=300604678

Для получения российского счета на оплату напишите sa...@ibase.ru


Questions?

Feel free to ask any questions at firebird2...@ib-aid.com


Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com

Re: [firebird-support] Import - slower and slower commits

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

There is a fix in 254 snapshot for similar problem.
Download fresh 2.5.4 Firebird snapshot and try it.

Regards,
Alexey Kovyazin
IBSurgeon
Firebird Recovery service:
http://ib-aid.com/en/firebird-interbase-recovery-service/
Database Performance optimization:
http://ib-aid.com/en/firebird-interbase-performance-optimization-service/



Hi Group,

I have a huge table to import - tens millions of rows.
From my experience, it is not good practice to commit job after 
importing everything, because import of rows can become slower and 
slower.

So, i decided to make commit after every 50.000 rows. So far so good.
Now I am at 70.000.000 rows imported.
Import of 50.000 rows last about 35 seconds = 1.428 records / second - 
excellent.
Commit of these 50.000 rows now takes about 85 seconds, with tendency 
to last longer and loner. So my import probably will never finish :( .
Before I stop everything, and start again by splitting import process 
in few steps, can someone explain why this is happening ?


Regards,

Zoran

Re: [firebird-support] 2.07 SS 32bit, OS 64bit, mem4GB work bad ?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

All architectures of 32 bit Firebird will work bad at 64 bit Windows 
with mem4GB, fixed in 2.52 only



It is fixed in 2.1.5, 2.5.2 and 3.0.
2.0 does not have this fix, end of life was announced.

The problem is described here
http://dyemanov.blogspot.ru/2012/03/firebird-vs-windows-file-system-caching.html


In essence, Windows will try to cache whole database in memory, due to 
file access flag.


Regards,
Alexey Kovyazin
IBSurgeon




Hi to All !



In 
http://www.slideshare.net/ibsurgeon/resolving-firebird-performance-problems





http://www.slideshare.net/ibsurgeon/resolving-firebird-performance-problems 


�

View on www.slideshare.net 
http://www.slideshare.net/ibsurgeon/resolving-firebird-performance-problems 



Preview by Yahoo

page 7:

All architectures of 32 bit Firebird will work bad at 64 bit Windows 
with mem4GB, fixed in 2.52 only



This means database will crash, or only not more/enough speed ?

Experiences ?


My partner would like to change the server's mem 4 Gb to 8 GB in the 
next week, but .. ?


The 64 bit Firebird not option now, it's only my plans, 4-6 months later.


Thanks in advance.

Re: [firebird-support] Bad surprise on performance

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Andre,

 - get some sleep

This is the key to find a solution, never optimize anything in a bad mood :)

WinSrv2008R2 is a perfect Windows Server, when properly configured.

Regards,
Alexey









++

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/