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] Off-Topic: Firebird future

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Hello Stefan,


If Firebird users want to have GUI too, we should consider the options.
The situation now is really different than several years ago, so including
a free GUI to Firebird umbrella now is really a option. For example, at
Firebird Conference there was presentation about RedExpert - GPL license,
multi platform, developed by contributor of Firebird and (since last week)
Platinum sponsor.
I tried its beta on Linux and Windows and it worked far better than thing
included into Postgresql and abandoned FlameRobin.

Of course, we should discuss all aspects before making the decision.

Regards,
Alexey Kovyazin
IBSurgeon



пн, 21 окт. 2019 г., 19:24 Stefan Heymann li...@stefanheymann.de
[firebird-support] :

>
>
>
> > Official or not, we need a simple, up to date, Firebird only, native
> > GUI.
>
> I don't get the point. There are GUI tools readily available
> (IBExpert, Upscene, etc.). If you want to have it for free, like in
> free beer, you can sit down, write it and publish it (that's how free
> software is made ...). The Firebird Project just doesn't have the
> resources to do it. We need the available developers for the database
> core and not for a fancy GUI tool.
>
> Best Regards
>
> Stefan Heymann
>
> 
>

Re: [firebird-support] Scaling Firebird - Azure

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Hello,

Azure simply does not deliver what it promises - simple test
www.ib-aid.com/dbtest gives results for Azure premium ssd in the lowest
25%, simple CrystalDiskMarks shows awful results too.
Your server is yours, but Azure promises Enterprise grade performance,
without worries abour RAID, ssd, etc - but what we get is far from it, as
well as from money paid for it. On other side, Google Cloud shows
reasonable results in these tests, so why pay for the worst option.
Amazon EC2 with ssd also looks better than Azure in terms of performance,
but seems to be much more expensive than Digital Ocean, Hetzner and even
Google Cloud.



Regards,
Alexey Kovyazin
IBSurgeon

ср, 9 окт. 2019 г., 10:49 'Louis van Alphen' lo...@nucleo.co.za
[firebird-support] :

>
>
> I think it is unfair to say Azure sucks. I have had huge performance
> issues with FB on a normal server with server grade SSDs. My desktop PC
> with spindle HDD outperforms it by an order of magnitude. To date it has
> not been resolved but I suspect it has to do with the RAID controller. The
> same server is very fast with MSSQL.
>
> From: firebird-support@yahoogroups.com [mailto:
> firebird-supp...@yahoogroups..com]
> Sent: Wednesday, 09 October, 2019 08:17
> To: Nagy Szilveszter nagy_szilvesz...@yahoo.com [firebird-support] <
> firebird-support@yahoogroups.com>
> Subject: Re: [firebird-support] Scaling Firebird - Azure
>
> Hello,
>
> In short words, Azure sucks, its so called Premium SSD is worse than the
> cheapest consumer grade ssd.
>
> Try Google Cloud or Amazon, or Hetzner.
>
> Regards,
>
> Alexey Kovyazin
>
> IBSurgeon
>
> пн, 7 окт. 2019 г., 0:20 Rune Horneland rune.hornel...@kravia.net  rune.hornel...@kravia.net> [firebird-support] <
> firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com>
> >:
>
> Hi,
>
> We are running Firebird 2 in an Azure VM. It can only take so much in
> terms of concurrent connections.
>
> What top-level advice would you give to scale this?
>
> We are connecting to it from a .NET core Middleware using Azure VMs.
>
> The architecture of the middleware is quite monolithic. We are considering
> rewriting it with Microservices and Azure functions or similar architecure,
> but are unsure how we could scale the Firebird DB or connections itself.
>
> Multiple casehandlers in our company use it via a Delphi-based Windows
> application at the other end, with a vendor maintaining the Firebird DB and
> Windows app development, so we are locked into using Firebird.
>
> RUNE HORNELAND
> CTO
>
> [Non-text portions of this message have been removed]
>
> 
>

Re: [firebird-support] Scaling Firebird - Azure

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Hello,

In short words, Azure sucks, its so called Premium SSD is worse than the
cheapest consumer grade ssd.

Try Google Cloud or Amazon, or Hetzner.

Regards,
Alexey Kovyazin
IBSurgeon




пн, 7 окт. 2019 г., 0:20 Rune Horneland rune.hornel...@kravia.net
[firebird-support] :

>
>
> Hi,
> We are running Firebird 2 in an Azure VM. It can only take so much in
> terms of concurrent connections.
> What top-level advice would you give to scale this?
>
> We are connecting to it from a .NET core Middleware using Azure VMs.
> The architecture of the middleware is quite monolithic. We are considering
> rewriting it with Microservices and Azure functions or similar architecure,
> but are unsure how we could scale the Firebird DB or connections itself.
> Multiple casehandlers in our company use it via a Delphi-based Windows
> application at the other end, with a vendor maintaining the Firebird DB and
> Windows app development, so we are locked into using Firebird.
>
>
> *RUNE HORNELAND*CTO
>
> 
>

Re: [firebird-support] Re: Does gbak use WireCompression?

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Martin,

Vlad head read my mind and answered :)
Try remote restore through service manager, it is much faster.

Regards,
Alexey Kovyazin

вт, 17 сент. 2019 г., 9:59 hv...@users.sourceforge.net [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> ---In firebird-support@yahoogroups.com,  wrote :
>
> > I often perform restores on remote servers via WAN. Since this sometimes
> takes a long time, I wonder if gbak > takes into account the parameter
> WireCompression. What needs to be set as where?
>
>   gbak is usual client application, it needs no special actions to use
> WireCompression. Just put zlib1.dll near the fbclient.dll and set
> WireCompression = true at client side (using firebird.conf or DPB). Make
> sure zlib1.dll is present at server side also.
>
>   But much more efficient way to do restore via slow network is remote
> backup\restore (see CORE-2666).
> Read doc\README.services_extension chapter (4) for details.
>
> Regards,
> Vlad
>
>
> 
>

Re: [firebird-support] Does gbak use WireCompression?

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Hello Martin,

Can you please provide the exact command you are using to do such restore?

Regards,
Alexey Kovyazin
IBSurgeon


пн, 16 сент. 2019 г., 18:46 Köditz, Martin martin.koed...@it-syn.de
[firebird-support] :

>
>
> Hello everybody,
>
>
>
> I often perform restores on remote servers via WAN. Since this sometimes
> takes a long time, I wonder if gbak takes into account the parameter
> WireCompression. What needs to be set as where?
>
>
>
> Regards
>
> Martin
> 
>

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 
<mailto:a...@ib-aid.com> [firebird-support] 
<mailto:firebird-support@yahoogroups.com>> 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
<mailto: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 <http://www.ib-aid.com>

On 07.11.2018 19:58, 'Martijn Tonies (Upscene Productions)'
m.ton...@upscene.com <mailto: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 erro

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 
<mailto: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 <http://www.ib-aid.com>

   On 07.11.2018 19:58, 'Martijn Tonies (Upscene Productions)'
   m.ton...@upscene.com <mailto: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 <mailto: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
   <mailto: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] Can not create backup of Firebird database because of the errors

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

Hello,

This seems to be a corruption.
Run gfix -v -full too.

More details here
https://ib-aid.com/en/articles/internal-gds-software-consistency-check/

Regards,
Alexey Kovyazin
IBSurgeon

On 20.09.2018 11:35, Georgiy Kovalov g...@liconic.com [firebird-support] 
wrote:


Hello,

At the time of backup firebird database (gbak -g -ig) I have the 
following error:


|gbak:writing data for table ORDERS
gbak: ERROR:message length error (encountered 532, expected 528)
gbak: ERROR:gds_$receive failed
gbak:Exiting before completion due to errors
|

When I'm using*gfix*with different parameters (-v -full, -mend, 
-ignore), I have the message:


|Summary of validation errors
 Number of index page errors : 540
|

In*firebird.log*file I see the lines:

|PC (Server) Thu Sep 20 08:37:01 2018
 Database: E:\...GDB
 Index 2 is corrupt on page 134706 level 1. File: 
..\..\..\src\jrd\validation.cpp, line: 1699
  in table COMPONENTS (197)
|

However, the database works OK without problems.

Please help me to fix the error and make a backup. (I need the backup 
to migrate to on 64bit server).


Thank you!

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

* <mailto:ariane.cut...@multidadosti.com.br> 
rudi.fe...@multidadosti..com.br


* <http://www.multidadosti.com.br/> www.multidadosti.com.br
* <http://www.whatsappmailing.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] gbak of encrypted database

['Alexey Kovyazin (ak)' a...@ib-aid.com [firebird-support]]

Hello,

 gbak does not support encryption in the standard Firebird 3.0
distribution.

In IBSurgeon's plugin implementation there is a gbak support,  with
transparent encryption of backups with the same key.
https://ib-aid.com/en/firebird-encryption-plugin-framework/

And restored database from the encrypted backup is also encrypted,
otherwise it will be a security hole.

If you're implementing your own plugin,  you can implement whatever you
need.

Regards,
Alexey Kovyazin
IBSurgeon



вт, 1 мая 2018 г., 3:24 Hamish Moffatt ham...@risingsoftware.com
[firebird-support] :

>
>
> If a db is encrypted with Firebird 3 encryption, is a backup made with
> gbak also encrypted?
>
>
> https://www.firebirdsql.org/file/community/conference-2016/encrypting-firebird-databases.pdf
> implies you need to encrypt your backup afterwards so that means no.
>
> Will a restored db from that backup be encrypted automatically or would
> you have to run 'ALTER DATABASE ENCRYPT ...' on the new db afterwards if
> you want it encrypted also?
>
> thanks
>
> Hamish
>
> 
>

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 : <mailto:cynthia.w...@exes.com.sg>
vonica@madsoft.com.sg

Website : <http://www.qne.com.sg/> www.madsoft.com.sg

Connect with us: <https://www.facebook.com/madsoft.sg>
<http://www.facebook.com/madsoft.sg> madsoft.sg
<http://www.twitter.com/madsoftsg> <http://www.twitter.com/madsoftsg>
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<http://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
* <mailto:ariane.cut...@multidadosti.com.br> 
rudi.fe...@multidadosti.com.br

* <http://www.multidadosti.com.br/> www.multidadosti.com.br
<http://www.timesheet.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/>
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>
mailto:ariane.cut...@multidadosti.com.br>
RFrfmcbfs> <mailto:rudi.fe...@multidadosti.com.br>
rudi.fe...@multidadosti.com.br
RFrfmcbfs> * < <http://www.multidadosti.com.br/>
http://www.multidadosti.com.br/> <http://www.multidadosti.com.br>
www.multidadosti.com.br
RFrfmcbfs> < <http://www.timesheet.com.br/> http://www.timesheet.com.br/>
<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> < <mailto:rudi.fe...@multidadosti.com.br>
rudi.fe...@multidadosti.com.br>
RFrfmcbfs> 

RFrfmcbfs>
++

RFrfmcbfs> Visit <http://www.firebirdsql.org> 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/>
http://www.ibphoenix.com/resources/documents/

RFrfmcbfs>
++
RFrfmcbfs> 

RFrfmcbfs> Yahoo Groups Links

<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=emailclient>

Livre de vírus.
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=emailclient> 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 
<https://www.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[2]: [firebird-support] How to change cpu utilization in Firebird engine?

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

Hi,
There is no sense to tune or upgrade CPU or other hardware without prior 
analysis of slowness reasons.
In 95% the reason is in non-optimal queries plans or absense of indices. 
--
Regards,
Alexey Kovyazin
IBSurgeon суббота, 07 января 2017г., 11:26 +03:00 от trsk...@yahoo.com 
[firebird-support]  firebird-support@yahoogroups.com :

> 
>Thanks for your clarification.
>
>I was planning to upgrade my cpu with a used Xeon 2683 V3 (price on my country 
>is about the same with I7 6700K), but it has 14 cores & 35MB L3 cache.
>
>So, I guess, a single connection in Firebird 3.0 will running poorly on Xeon 
>2683 V3, it will only utilized about 7% cpu. I have to re evaluate again this 
>plan.
>
>Btw, if there are 6 connection on 4 cores, how is cpu utilization calculate?
>
>Thanks & regards,
>Anto.
>

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

[firebird-support] Re: FB Conference 2016

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

Hi Andre!
Thank you! Have a good vacation! 
We will definetely do more conferences and meet there!
--
Regards,
Alexey Kovyazin
IBSurgeon пятница, 07 октября 2016г., 09:53 +02:00 от André Knappstein  
andre.knappst...@beta-eigenheim.de :

>Hello Alexey,
>hello Dmitry,
>
>I  hope  the  conference is going well, and that all of you personally
>also are well!
>
>I've  been  looking forward to the conference so much, but some months
>ago it turned out, that - starting from today - this would be the only
>chance  for  my  wife  and  me  to  have  at least one week of holiday
>together.
>
>So I could unfortunately not come to Prague this time.
>
>But I am looking forward to the next conferences, and maybe there will
>be a chance for a firebird tour with seminar or similar.
>
>Have a great time in Prague!
>
>best regards from Germany,
>André Knappstein
>
>
>mit freundlichen Grüßen,
>
>ppa. André Knappstein
>EDV und Controlling
>~~
>beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
>Hafenweg 4
>59192 Bergkamen-Rünthe
>
>Telefon: +49 2389 9240 140
>Telefax: +49 2389 9240 150
>e-mail:  andre.knappst...@beta-eigenheim.de
>
>Amtsgericht Hamm Nr. B 420
>Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus
>
>USt-IDNr.: DE 125215402
>

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#153

Re: [firebird-support] Firebird 3 performances

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

Hi,

Try to test current snapshot of release 3.0.1. It seems that the 
released version 3.0.0 has some performance problems.


Regards,
Alexey Kovyazin
IBSurgeon


Hello,

I am testing Firebird 3 in order to upgrade an application from 
Firebird 2.5 and I would like to know if there are known cases of 
Firebird 3 performances that are worse than Firebird 2.5. During my 
tests, it seems that bulk insertions take longer in Firebird 3.



*Franco Curnis*

Area Tecnica

**

*INTESA*Progetti Software

Via Alberto da Giussano 35

20833 Giussano MB - *Tel*. 0362 35

www.intesanet.it <http://www.intesanet.it/>  - i...@intesanet.it 
<mailto:i...@intesanet.it>

Re: [firebird-support] Slow Firebird performance in Azure with durable data disks (e.g., Premium Storage)

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

Hi Antti,
Try to lock database with nbackup and check the peformance with locked database 
- in this case all writes will go to delta file sequentially.
If your assumption is true and Premium Storage does not work good with random 
writes, you will see less decrease of performance.
Also, there are other options to deal with such performance problem - please 
contact me directly.
--
Regards,
Alexey Kovyazin
IBSurgeon вторник, 30 августа 2016г., 19:18 +03:00 от Antti Nivala 
antti.niv...@m-files.com [firebird-support]  firebird-support@yahoogroups.com :

> 
>Hi,
> 
>We've experimented with the performance of Firebird in virtual machines in 
>Microsoft's Azure cloud. With Azure VMs, any durable data needs to be placed 
>in "data disks", and essentially that means network-connected storage that is 
>inherently
 slower than e.g. local SSD disks.
> 
>When testing our application's operations with a Firebird database that's on 
>such a "data disk", the performance is roughly 6x slower than when the 
>database is on the VM's local, temporary-only disk. The temporary disks 
>obviously are not
 good for real use so this is for performance comparison only.
> 
>When we do the same test with SQL Server (e.g., SQL Server Express Edition), 
>the performance difference between the temporary local disks and the durable 
>data disks is significantly smaller. SQL Server seems to perform pretty much 
>as fast
 with the data disk with our application's operations.
> 
>The way I'm explaining this difference to myself is that Firebird's way of 
>making the transactions durable is not well suited for this kind of 
>environment where the disk has latency. As far as I understand, Firebird needs 
>to make a lot
 of page writes to the disk to different locations (as the transaction is 
touching multiple tables), and this is probably poison in a situation where the 
disk has latency. On the other hand, SQL Server, as far as I understand, only 
needs to write to its transaction
 log file to make the transaction durable, which is probably an advantage in 
this kind of hardware setup.
> 
>Do you think these general observations/reasons for the slow performance with 
>Firebird with Azure data disks are correct?
> 
>Is there anything obvious that we could do to make Firebird work fast in Azure 
>VMs (requiring durability of the transactions, of course, so turning forced 
>writes off doesn't sound like an option)?
> 
>I can't think of anything myself, but I wanted to ask before completely 
>disregarding the option of running Firebird in Azure VMs.
> 
>More info on Azure Premium Storage data disks here:
>https://azure.microsoft.com/en-us/documentation/articles/storage-premium-storage/
> 
>Antti
> 
>

Re: [firebird-support] Delete causes server to hang

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

Hi Thomas,

9 records versions is not that big amount to slow down Firebird 
completely for 30 seconds.


In this old article below we made tests with updating/deleting 1mln of 
records, but it was much faster than you describe.

http://ib-aid.com/en/articles/negative-impact-of-indices-to-insert-update-and-delete-performance-in-firebird-sql/

However, I suspect that deleting of records could be done regularly, and 
there could be long running writeable transaction which retains old 
record versions, so Firebird need to clean not 90k, but much more of them.


Anyway, right now there is not enough information - to investigate it, run
gstat -r -t < tablename > stat.txt
before and after delete,
and review number of records versions and max versions, to figure out 
what is the actual amount of records versions to be cleaned inside the 
database.



Regards,
Alexey Kovyazin
IBSurgeon http://ib-aid.com





Hey Helen

Thanks for hour reply, let me see if i can clarify the problem, the 
actual delete of the 90.000 rows is not the problem, this completes in 
10-30 seconds. That is just fine, performance does not matter here.


I have 2 web servers, on each of them, there is a background thread 
that inserts into the request table, these two threads are the only 
ones that insert into that table.
Every hour the previous mentioned delete procedure is in executed from 
an application server.


However when the procedure is executed every executing user-request on 
the webserver is waiting for the database server to respond, and the 
number of attachments spike on the database server, resulting i 
connection pool full exceptions.
this image shows how the attachment count spikes, 95% of the 
attachments are from the two webservers.

https://dl.dropboxusercontent.com/u/937321/attachement_spike.PNG

None of the user-requests on the webserver 
selects/inserts/updates/deletes from the request table, information 
about the request is handled over to the background thread that 
inserts a row into the table.


From my perspective it looks like the delete of the 90.000 rows 
results in a complete hang/standstill on the database server. In those 
few seconds i looks like FB does not finish any statements.


Thomas

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

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

Hi Fabian,
Firebird caches only actually used pages.
The small cache means that your application touches the small part of the 
database.
--
Regards,
Alexey Kovyazin
IBSurgeon
PS It is difficult and wrong to give you any direct advice without all details 
in hands - it can lead to worse performance than with default parameters.
среда, 01 июня 2016г., 04:16 +03:00 от Fabian Ernesto Chocron 
fabia...@itbizolutions.com.au [firebird-support]  
firebird-support@yahoogroups.com :

> 
>Hi All
>
>We are having trouble setting up the database cache size on FB 3.0 
>running on Windows 2008 R2 64 bits with 32 GB ram.
>
>The problem we have is we cannot get the server to allocate the ram for 
>the cache as we intend. With FB 2.54 we had the DB cache set very high, 
>close to 1 GB per database, all running in RAM memory. With FB 3.0 we 
>read it can allocate much more RAM to the cache, but it appears the 
>server is allocating very small amount of Ram when the first user 
>connects to the DB, and as we connect more users to the DB the ram 
>consumption increases slowly. The setting we are playing with are:
>
>On firebird.conf
>
>FileSystemCacheThreshold = 0
>FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB 
>ram.)
>
>On databases.conf
>
>MyTestDB = c:\Temp\MyDb.fdb
>{
>DefaultDbCachePages = 458752
>}
>
>Any ideas what could be wrong? Or what settings would give us maximum 
>RAM usage for the DB cache (we dont want file system cache meaning HDD 
>cache, we want to have the DB in RAM for the purpose of reading the DB)
>
>Cheers,
>Fabian
>
>

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] Question about few Firebird details

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

Hi Dmitry,

You also should not answer too late in the evening - OIT, not OAT :)

Also, in case of really large inserts/updates the potential rollback 
will be done through the TIP anyway, so your comment make sense only for 
relatively small amount of records inserted/updated - and with such 
small amount the positive effect of NO_AUTO_UNDO will be too small.


Regards,
Alexey

> 23.05.2016 21:05, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
> wrote:
>> 44. Use NO_AUTO_UNDO - what happend when i do rollback?
> You will get hard rollback, frozen OAT counter and unavoidable run of 
> sweep.
>







++

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: 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..

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

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

Patrick,
Definetely you need to compare real life loads.
--
Regards,
Alexey Kovyazin
IBSurgeon среда, 13 апреля 2016г., 04:47 +03:00 от " thetr...@yahoo.com 
[firebird-support]" < firebird-support@yahoogroups.com> :

> 
>
>Hey Alexey,
>thanks you for our input. I think what you say is correct, and we reviewed our 
>disk setup again.
>We are utilizing mechnical discs so it's kinda hard to compare SSD performance 
>to them.
>But they should provide enought IOPS for our load.
>
>Unfortunatly we can't just switch to a single SSD, since we would loose 
>replication and failover systems the SAN provides which is a critical demand 
>for us. I'm afraid for now we have to stick with it, until we have some facts 
>to proof that the SAN Setup is our limiting factor. And data is not should 
>that for me currently.
>
>On a sidenode, we do own a server with SSD setup, but in tests we couldn't get 
>a noticable performance gain through increasement of IOs this way. (tests was 
>generic and not real world load unfortunatly)
>
>Best Regards,
>Patrick
>
>---In firebird-support@yahoogroups.com,  wrote :
>
>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
>

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

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

Hi,
You wrote:
>The thing is, sure this numbers look really low. But >the system never uses 
>it. The monitoring of the >SAN show's that this load's are never used
You are confusing the reason and the result.
Monitoring shows low numbers because spinning drives cannot provide fast enough 
random reads. 
>From Sintatica, every 20 Minutes a Peak in GC for >~15.000 transactions
Sinatica uses really strange terms to describe transactions behaviour, and 
since it is an abandonen tool, nobody can explain how they are aligned with 
real situation.
--
Regards,
Alexey Kovyazin
IBSurgeon среда, 13 апреля 2016г., 05:08 +03:00 от " thetr...@yahoo.com 
[firebird-support]" < firebird-support@yahoogroups.com> :

> 
>Hey Thomas,
>thanks for your extensive reply.
>Unfortunatly we'r still bound to some old 32bit UDF functionality which we 
>can't get in 64bit. 
>I think you know about the use of SuperClassic with 32bit Server - 2GB RAM 
>Limit :)
>It's not impossible, but also not really a fast route we can go. But for sure 
>again a reason to talk about moving the switch to 2.5.
>
>We did ran some some disk IO benchmarks (with AS SSD) today, and in times of 
>SSD kinda depressing :D
>The thing is, sure this numbers look really low. But the system never uses it. 
>The monitoring of the SAN show's that this load's are never used. The 
>Single-4k-read is worring me, but i lean towards that our 500 proceses are 
>more like the 64-thread test. But even then, we only messured 100 Iops reading 
>on livesystem.
>
>Sequential Read speed: ~ 450 MB / s
>Sequential Write speed: ~500 MB / s
>4k read: 196 Iops
>4k write: 1376 Iops
>4k-64 thread read: 15945 Iops
>4k-64 thread write: 7361 Iops
>
>
>Garbage Info still needs to be collected.
>But first signs show that this indeed could be a potential problem.
>From Sintatica, every 20 Minutes a Peak in GC for ~15.000 transactions. This 
>get's fixed by the server in the relative small amount of time (i think < 1 
>minute), since it's really only a single peak in the graph everytime.
>When the GC stop increasing and the server starts to collect it, we see an 
>increase of concurrent running transactions (= transactions are longer open 
>and processed slower).
>
>We don't have data from the live system yet to see if this behaviour kind of 
>"snowballs" when there is really high load on the server.
>
>Best Regards,
>
>---In firebird-support@yahoogroups.com,  wrote :
>
>Hi Patrick,
>
>>> Hi Thomas, nice to get a response from you. We already met in ~2010 in Linz 
>>> at
>>> your office :)
>>> (ex. SEM GmbH, later Playmonitor GmbH)
>>
>I know. XING (Big Brother) is watching you. Nice to see that you are still 
>running with Firebird. ;-)
>
>
>>> First, sorry for posting a mixed state of informations. The config settings 
>>> i
>>> postet are the current settings.
>>> But the Lock-Table-Header was from last saturday (day of total system 
>>> crash) -
>>> we changed Hash Slot Value since than, but it didn't work. New Table looks
>>> like:
>>> 
>>> 
>>> LOCK_HEADER BLOCK
>>> Version: 16, Active owner:  0, Length: 134247728, Used: 55790260
>>> Semmask: 0x0, Flags: 0x0001
>>> Enqs: 1806423519, Converts: 4553851, Rejects: 5134185, Blocks: 56585419
>>> Deadlock scans: 82, Deadlocks:  0, Scan interval:  10
>>> Acquires: 2058846891, Acquire blocks: 321584126, Spin count:   0
>>> Mutex wait: 15.6%
>>> Hash slots: 20011, Hash lengths (min/avg/max):0/   7/  18
>>> Remove node:  0, Insert queue:  0, Insert prior:  0
>>> Owners (297): forward: 385160, backward: 38086352
>>> Free owners (43): forward: 52978748, backward: 20505128
>>> Free locks (41802): forward: 180712, backward: 3620136
>>> Free requests (-1097572396): forward: 46948676, backward: 13681252
>>> Lock Ordering: Enabled
>>> 
>>> 
>>> The Min/Avg/Max hash lengths look better now, but as you mentioned the Mutex
>>> wait is worring us too.
>>> We have 2 direct questions about that.
>>> 
>>> 
>>> 1) What are the negative effects of increasing Hash-Slots (too high)?
>>
>It somehow defines the initial size of a hash table which is used for lock(ed) 
>object lookup by a key (= hash value), ideally with constant O(1) run-time 
>complexity. If the hash table is too small, due to a too small value for hash 
>slots, it starts to degenerate into a linked/linear list per hash slot. Worst 
>case resulting in O(n) complexity for lookups. The above 20011

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

Thomas,


I doubt, Firebird is IO-bound (limited by disk IO).



Sorry, I don't understand your comment, can you please clarify what you 
mean?


Regards,
Alexey

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] secondary server attachments cannot validate databases during gfix

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

Hi Adam,

gfix does not validate databases online till 2.5.4.
Update 250 to 255 and try again, or perform recovery operations in 
exclusive mode.


Use these sequence to repair
https://ib-aid.com/en/articles/how-to-repair-a-corrupt-firebird-database/


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


Hello Group,

I have a server running Firebird 2.5.0 in Classic mode on Windows.

There is a database that appears corrupted (internal Firebird 
consistency check ...).


I have got all users out and renamed the file and attempted:
gfix -v localhost:C:\data\corrupt.fdb -user sysdba -password masterke


It is failing with the following message:


bad parameters on attach or create database
-secondary server attachments cannot validate databases


Using Process Explorer, I can see that there are no other processes 
with a handle to this file. (It is also renamed, and the clients are 
pointing at the alias which doesn't presently exist)



I suspect that whatever the corruption is, it is confusing gfix into 
believing that there are other active connections. Is there a way 
around this?



Thanks

Adam

Re: [firebird-support] firebird and transaction behaviour questions

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

Hi,

> That's true, but according to ODBC specs driver should start transaction


automatically as needed.

>> Is this the case if you are doing reads only?

> Yes.

Why would you use transaction for select statements?




To isolate statements from uncommitted changes.

http://www.slideshare.net/ibsurgeon/3-how-transactionswork

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

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

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] 


*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&utm_source=link&utm_campaign=sig-email&utm_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&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> 

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] 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 
<mailto:mirco.malag...@ingeniaonline.it> [firebird-support] 
<mailto:firebird-support@yahoogroups.com>> 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

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

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 
<http://www.comendo.no>
This e-mail has been scanned for viruses by Comendo Norge AS 
<http://www.comendo.no>

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 <mailto: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]" 


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

Hi Hector,

I discovered something else quite interesting:

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


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


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

Regards,
Alexey Kovyazin
IBSurgeon






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




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


Hi Hector,

Thanks for statistics, it clarifies situation.

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

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

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

Regards,
Alexey






He again Alexey,

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

gstat over the table reported this:

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

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

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

Any help would be appretiated.


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

Hi Hector,

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

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

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

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


Regards,
Alexey Kovyazin
IBSurgeon







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

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

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

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

Could anyone help?

-- 
--

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





--
Planatec Software S.L.

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 
<mailto:a...@ib-aid.com> [firebird-support] 
<mailto: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
<mailto:a...@ib-aid.com> [firebird-support]
mailto:firebird-support@yahoogroups.com>>:

Hi Hector,

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

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

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

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


Regards,
Alexey Kovyazin
IBSurgeon







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

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

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

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

Could anyone help?

-- 
--

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





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




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

Re: [firebird-support] About firebird performance

[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 
<mailto:a...@ib-aid.com> [firebird-support] 
<mailto:firebird-support@yahoogroups.com>>:


Hi Hector,

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

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

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

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


Regards,
Alexey Kovyazin
IBSurgeon







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

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

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

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

Could anyone help?

-- 
--

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





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