Re: [firebird-support] index vs page size

[Nick Upson n...@telensa.com [firebird-support]]

Thank you



Kind Regards,
Nick

Nick Upson | Principal Operations Engineer
office +44 01799 588800 support hotline +44 1799 399200
DDI +44 01799 588621

telensa.com | @telensa 
| Linkedin



From: firebird-support@yahoogroups.com  on 
behalf of Ann Harrison aharri...@ibphoenix.com [firebird-support] 

Sent: 21 June 2019 12:31
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] index vs page size




On Jun 20, 2019, at 11:10 AM, Nick Upson 
n...@telensa.com [firebird-support] 
mailto:firebird-support@yahoogroups.com>> 
wrote:

Hi

I have a large 100Gb+ firebird 2.5 database and I'm trying to deal with some 
performance issues.

One indicator I have found is that several large tables have indexes with a 
depth of 3 (and one of 4). The guidance I have seen says to increase the page 
size until the depth is below 3 but the pagesize is already maxed out at 16k. 
Can anyone suggest other actions I could take.

The guidance should have been three or less, not less than three.  One index 
with four levels should not be a crisis if you have adequate page cache.

Good luck,

Ann




Kind Regards,
Nick

Nick Upson | Principal Operations Engineer
office +44 01799 588800 support hotline +44 1799 399200
DDI +44 01799 588621

telensa.com | @telensa 
| Linkedin


IMPORTANT NOTICE: This e-mail message is intended to be received only by 
persons entitled to receive the confidential information it may contain. E-mail 
messages to clients of Telensa may contain information that is confidential and 
legally privileged. Please do not read, copy, forward, or store this message 
unless you are an intended recipient of it. If you have received this message 
in error, please forward it to the sender and delete it completely from your 
computer system.


CAUTION. This email originated from outside of Telensa. Do not click links or 
open attachments unless you recognise the sender and know the content is safe.
IMPORTANT NOTICE: This e-mail message is intended to be received only by 
persons entitled to receive the confidential information it may contain. E-mail 
messages to clients of Telensa may contain information that is confidential and 
legally privileged. Please do not read, copy, forward, or store this message 
unless you are an intended recipient of it. If you have received this message 
in error, please forward it to the sender and delete it completely from your 
computer system.

[firebird-support] index vs page size

[Nick Upson n...@telensa.com [firebird-support]]

Hi

I have a large 100Gb+ firebird 2.5 database and I'm trying to deal with some 
performance issues.

One indicator I have found is that several large tables have indexes with a 
depth of 3 (and one of 4). The guidance I have seen says to increase the page 
size until the depth is below 3 but the pagesize is already maxed out at 16k. 
Can anyone suggest other actions I could take.


Kind Regards,
Nick

Nick Upson | Principal Operations Engineer
office +44 01799 588800 support hotline +44 1799 399200
DDI +44 01799 588621

telensa.com | @telensa 
| Linkedin


IMPORTANT NOTICE: This e-mail message is intended to be received only by 
persons entitled to receive the confidential information it may contain. E-mail 
messages to clients of Telensa may contain information that is confidential and 
legally privileged. Please do not read, copy, forward, or store this message 
unless you are an intended recipient of it. If you have received this message 
in error, please forward it to the sender and delete it completely from your 
computer system.

Re: [firebird-support] rw vs ro connections

[Nick Upson n...@telensa.com [firebird-support]]

I should have been more specific,

the python code is done as

   ro_transaction = DB.trans(fdb.ISOLATION_LEVEL_READ_COMMITED_RO)
cur = ro_transaction.cursor()
else:
cur = DB.cursor()

I don't see any reason this should perform better but colleagues disagree


Kind Regards,
Nick

Nick Upson | Principal Operations Engineer
office +44 01799 588800 support hotline +44 1799 399200
DDI +44 01799 588621

telensa.com<http://www.telensa.com/> | @telensa<http://www.twitter.com/telensa> 
| Linkedin<http://www.linkedin.com/company/telensa>



From: firebird-support@yahoogroups.com  on 
behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 

Sent: 19 June 2019 16:59
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] rw vs ro connections

19.06.2019 17:51, Nick Upson n...@telensa.com [firebird-support] wrote:
> Should I expect any performance gain if I change my rw connections to ro, 
> given that the
> query is always of the form "select * from ..."

   At first, there is no such things as "ro connection".
   At second, ro transactions have no performance advantage.


--
   WBR, SD.






++

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

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

++


Yahoo Groups Links



CAUTION. This email originated from outside of Telensa. Do not click links or 
open attachments unless you recognise the sender and know the content is safe.
IMPORTANT NOTICE: This e-mail message is intended to be received only by 
persons entitled to receive the confidential information it may contain. E-mail 
messages to clients of Telensa may contain information that is confidential and 
legally privileged. Please do not read, copy, forward, or store this message 
unless you are an intended recipient of it. If you have received this message 
in error, please forward it to the sender and delete it completely from your 
computer system.

[firebird-support] rw vs ro connections

[Nick Upson n...@telensa.com [firebird-support]]

Hi

Should I expect any performance gain if I change my rw connections to ro, given 
that the query is always of the form "select * from ..."


Kind Regards,
Nick

Nick Upson | Principal Operations Engineer
office +44 01799 588800 support hotline +44 1799 399200
DDI +44 01799 588621

telensa.com | @telensa 
| Linkedin


IMPORTANT NOTICE: This e-mail message is intended to be received only by 
persons entitled to receive the confidential information it may contain. E-mail 
messages to clients of Telensa may contain information that is confidential and 
legally privileged. Please do not read, copy, forward, or store this message 
unless you are an intended recipient of it. If you have received this message 
in error, please forward it to the sender and delete it completely from your 
computer system.

Re: [firebird-support] permissions nbackup

[Nick Upson n...@telensa.com [firebird-support]]

yes, I can place the call to nbackup in a crontab for firebird or root,
that seems to be the only way

On 14 August 2017 at 16:51, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] <firebird-support@yahoogroups.com> wrote:

> 14.08.2017 17:44, Nick Upson n...@telensa.com [firebird-support] wrote:
> > because this is from cron, so key-based auth is out and I don't want to
> put passwords /
> > keys on the server
>
>According to google you don't need password for cron jobs:
> https://stackoverflow.com/questions/8475694/how-to-
> specify-in-crontab-by-what-user-to-run-script
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] permissions nbackup

[Nick Upson n...@telensa.com [firebird-support]]

because this is from cron, so key-based auth is out and I don't want to put
passwords / keys on the server

I can run from a root cron, with possible su to firebird, but was hoping
for something better

On 14 August 2017 at 16:41, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] <firebird-support@yahoogroups.com> wrote:

> 14.08.2017 17:21, Nick Upson n...@telensa.com [firebird-support] wrote:
> > if I do nbackup . it fails, telling me I cannot access the
> security2.fdb (as a normal
> > user)
>
>Why can't you use su to change current user to root or firebird?
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] permissions nbackup

[Nick Upson n...@telensa.com [firebird-support]]

if I do nbackup . it fails, telling me I cannot access the
security2.fdb (as a normal user)

I would have expected the file to have group rw enabled so that permitted
users can be a member of group firebird to do this


On 14 August 2017 at 16:16, Tomasz Tyrakowski t.tyrakow...@sol-system.pl
[firebird-support] <firebird-support@yahoogroups.com> wrote:

>
>
> On 14.08.2017 at 15:51, Nick Upson n...@telensa.com [firebird-support]
> wrote:
> > I'm looking at using nbackup but the file permissions on security2.fdb
> mean
> > only firebird or root can do so, is that the way it is supposed to be?
>
> Definitely. You wouldn't like any third party to access the password /
> security database directly, would you?
> On the other hand, in what way does it stop you from using nbackup? I
> suppose what you actually need to do is to backup / restore a normal
> database, not the system-wide security db.
>
> regards
> Tomasz
> 
>

[firebird-support] permissions nbackup

[Nick Upson n...@telensa.com [firebird-support]]

Hi

I'm looking at using nbackup but the file permissions on security2.fdb mean
only firebird or root can do so, is that the way it is supposed to be?


*# ls -lart  /var/lib/firebird/system/security2.fdb*
*-rw--- 1 firebird firebird 1171456 Aug 14 14:25
/var/lib/firebird/system/security2.fdb*

[firebird-support] NODELAY

[Nick Upson n...@telensa.com [firebird-support]]

Hi

I noticed this (below) on a recent thread

  * tweaking xinetd (flags = REUSE NODELAY)

I have a mainly real-time system with db response problems, does anyone
have experience of using NODELAY,

Re: [firebird-support] dropping the connection

[Nick Upson n...@telensa.com [firebird-support]]

I remember killing connection via delete from mon$statements with firebird
2.0 or 2.1. Could the fact that it doesn't work for me now be down to
upgrading the client install to 2.5?

On 22 February 2017 at 09:44, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] <firebird-support@yahoogroups.com> wrote:

>
>
> On 2017-02-21 17:32, Nick Upson n...@telensa.com [firebird-support] wrote:
> > Hi
> >
> > The problem I'm trying to solve is users leaving inactive db_workbench
> > sessions attached to a database while other work is going on by other
> > processes, causing a large OIT gap and the consequent problems. The
> > solution needs to be automatic and unattended.
> >
> > db_workbench doesn't have a timeout setting (I asked)
> >
> > deleting the row from mon$statements doesn't kill the transaction;
> > deleting the row from mon$transactions or mon$attachments gives
> > "operation not supported"
> >
> > I could automate a kill of the fb_inetserver process but I would
> > rather something with less risk
> >
> > I figure other people have had this and solved it before, I hope.
> >
> > currently using firebird 2.1 on centos, shortly moving to 2.5
>
> Killing connections by deleting from MON$STATEMENTS requires Firebird
> 2.5 and an ODS 11.2 database (so you will also need to backup and
> restore the database when migrating to Firebird 2.5!).
>
> Mark
>
> 
>

[firebird-support] dropping the connection

[Nick Upson n...@telensa.com [firebird-support]]

Hi

The problem I'm trying to solve is users leaving inactive db_workbench
sessions attached to a database while other work is going on by other
processes, causing a large OIT gap and the consequent problems. The
solution needs to be automatic and unattended.

db_workbench doesn't have a timeout setting (I asked)

deleting the row from mon$statements doesn't kill the transaction;
deleting the row from mon$transactions or mon$attachments gives "operation
not supported"

I could automate a kill of the fb_inetserver process but I would rather
something with less risk

I figure other people have had this and solved it before, I hope.

currently using firebird 2.1 on centos, shortly moving to 2.5

Re: [firebird-support] gbak error

[Nick Upson n...@telensa.com [firebird-support]]

On 31 August 2016 at 18:16, Steve Wiser
st...@specializedbusinesssoftware.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

> Can you show us the permissions of the directory that you are trying to
> write to?  And also of the database file that you are trying to back up?
>

​the directory is 777 and the database is 550 owned by firebird​, group unb


>
> And is there an existing fbk that you are trying to overwrite or is it a
> new file?
>

​new file​


>
> And what user and group are you using to perform the gbak along with the
> flags.
>

​user and group are called unb, the firebird user is also a member of group
unb,  gbak -backup -user sysdba -password x filename



>
> Does anything show up in dmesg that would indicate some kind of I/O error
> or hard disk starting to go bad?
>

​no, and its a VM

This was all working and then it didn't, no changes detected anywhere ​


Nick Upson,
Principal Operations Engineer, Telensa Ltd.
Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200

Re: [firebird-support] gbak error

[Nick Upson n...@telensa.com [firebird-support]]

its possible that the file is created and removed quickly enough that I
don't see it

Nick Upson,
Principal Operations Engineer, Telensa Ltd.
Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200

On 31 August 2016 at 17:38, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
> Nick,
>
> > it either happens on creation or a file is started and then removed very
> > quickly
>
> "then removed very quickly"???
>
> Please elaborate.
>
>
> Sean
>
> 
>

Re: [firebird-support] gbak error

[Nick Upson n...@telensa.com [firebird-support]]

it either happens on creation or a file is started and then removed very
quickly

Nick Upson,
Principal Operations Engineer, Telensa Ltd.
Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200

On 31 August 2016 at 16:57, Ann Harrison aharri...@ibphoenix.com
[firebird-support] <firebird-support@yahoogroups.com> wrote:

>
>
> Does the error happen on file creation or after some data has been written?
>
>
>
> On Aug 30, 2016, at 4:36 PM, Nick Upson n...@telensa.com [firebird-support]
> <firebird-support@yahoogroups.com> wrote:
>
> no, not a file name collision and plenty of disk space
>
> this just started happening, no change to installed s/w
>
> Nick Upson,
> Principal Operations Engineer, Telensa Ltd.
> Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200
>
> On 30 August 2016 at 21:14, Helen Borrie hele...@iinet.net.au
> [firebird-support] <firebird-support@yahoogroups.com> wrote:
>
>>
>>
>> Hello Nick,
>>
>> > I'm running fb2.1 on centos5, recently a system has started
>> > throwing this error when asked to do a backup, the gbak parameters
>> > are unchanged, permissions all seem ok, any suggestions what I might
>> check
>>
>> > gbak: ERROR:Unexpected I/O error while writing to backup file
>> > gbak:Exiting before completion due to errors
>>
>> "gbak parameters are unchanged." Could be you already have a file in
>> that location with the same name.
>>
>> HB
>>
>>
> 
>

Re: [firebird-support] gbak error

[Nick Upson n...@telensa.com [firebird-support]]

no, not a file name collision and plenty of disk space

this just started happening, no change to installed s/w

Nick Upson,
Principal Operations Engineer, Telensa Ltd.
Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200

On 30 August 2016 at 21:14, Helen Borrie hele...@iinet.net.au
[firebird-support]  wrote:

>
>
> Hello Nick,
>
> > I'm running fb2.1 on centos5, recently a system has started
> > throwing this error when asked to do a backup, the gbak parameters
> > are unchanged, permissions all seem ok, any suggestions what I might
> check
>
> > gbak: ERROR:Unexpected I/O error while writing to backup file
> > gbak:Exiting before completion due to errors
>
> "gbak parameters are unchanged." Could be you already have a file in
> that location with the same name.
>
> HB
>
> 
>

Re: [firebird-support] gbak error

[Nick Upson n...@telensa.com [firebird-support]]

it all seems ok, files have been created in other ways

Nick Upson,
Principal Operations Engineer, Telensa Ltd.
Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200

On 30 August 2016 at 19:26, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
>
>
> > I'm running fb2.1 on centos5, recently a system has started throwing
> this
> > error when asked to do a backup, the gbak parameters are unchanged,
> > permissions all seem ok, any suggestions what I might check
> >
> > gbak: ERROR:Unexpected I/O error while writing to backup file
> > gbak:Exiting before completion due to errors
>
> Check integrity of storage subsystem.
>
>
> Sean
>
> 
>

Re: [firebird-support] When do triggers take effect?

[Nick Upson n...@telensa.com [firebird-support]]

you need to commit the trigger creation / modification & then have any
other relevant transaction commit

Nick Upson,
Principal Operations Engineer, Telensa Ltd.
Direct +44 (0) 1799 533252, Support Hotline +44 (0) 1799 399200

On 6 June 2016 at 15:59, Tim Ward t...@telensa.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Using Database Workbench I added a trigger to a table, but it didn't
> seen to do anything (in particular didn't throw an exception which it
> should have done when I deliberately created an invalid record).
>
> After restarting DBWB the trigger behaved as expected.
>
> Do triggers not take effect on existing connections, or something?
>
> --
> Tim Ward
>
> 
>

Re: [firebird-support] 2 servers acessing one database

[Nick Upson n...@telensa.com [firebird-support]]

On 16 December 2015 at 15:19, Rudi Feijó rudi.fe...@multidadosti.com.br
[firebird-support]  wrote:

> Thanks for the reply Karol
>
> What I mean is using 2 servers to connect to the same DB.
> This DB is in a shared disk that both servers can access.
> So I would have, for example, 100 clients connecting on server 1, another
> 100 clients on server 2, but both accessing the same shared database
>

​you can have all those clients accessing the database, but not in that
way. The database should NOT be on a shared drive, all access goes through
the firebird server process on the machine that owns the disk containing
the database. You can force the setup to be as you describe but you will
rapidly get a corrupt database​


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] Another ordering question?

[Nick Upson n...@telensa.com [firebird-support]]

without a "where" clause firebird has to read the entire table (to
determine if each row is valid for the current transaction) and sort the
results

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 9 September 2015 at 13:00, Tim Ward t...@telensa.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> SELECT FIRST 1  FROM MYTAB ORDER BY MYKEY1, MYKEY2;
>
> PLAN (MYTAB ORDER PK_MYTAB)
>
> Current memory = 3437420
> Delta memory = 152
> Max memory = 3586952
> Elapsed time= 0.04 sec
> Buffers = 150
> Reads = 232
> Writes 0
> Fetches = 132082
>
> There may be several thousand records in the table, but the primary key
> is defined as (MYKEY1, MYKEY2) and the plan says it's using it, so why
> the 232 disk reads? - one might have guessed 2 would be enough, one for
> the index and one for the record. (The key fields are both 32 bit
> integers. Yes, I do have plans to replace them by a generator-generated
> key, but I can't see why that would make a difference to this particular
> performance issue.)
>
> This table is being used as a FIFO queue. Would the most likely
> explanation be lack of garbage collection, so it's having to scan lots
> of deleted records before finding the first real one, or are there other
> possibilities? I must say that
>
> Oldest transaction 390433636
> Oldest active 390433637
> Oldest snapshot 390433637
> Next transaction 391092651
>
> doesn't look too encouraging, so I'd better investigate that.
>
> --
> Tim Ward
>
> 
>

Re: [firebird-support] 2.5 architecture

[Nick Upson n...@telensa.com [firebird-support]]

On 28 August 2015 at 14:43, Thomas Steinmaurer t...@iblogmanager.com
[firebird-support] firebird-support@yahoogroups.com wrote:

  http://www.firebirdsql.org/file/fb25_architecture_comparison.pdf

 IMHO, this one clearly states that there are 3 architectures in 2.5
 available.


​superserver  classic not applicable, embedded is sc, and sc itself = 1
​


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

[firebird-support] firebird nessus

[Nick Upson n...@telensa.com [firebird-support]]

Hi

firebird 2.1 centos, I have systems in a company who are scanning with
nessus and this causes the firebird log below. Although they do not have
access to the database I'm concerned what is happening. Has anyone seen
this before? Can I avoid it?


gaxgpap345vuSun Jul 12 17:27:11 2015
INET/inet_error: read errno = 104


gaxgpap345vuSun Jul 12 17:27:14 2015
*** DUMP ***


gaxgpap345vuSun Jul 12 17:27:14 2015
Tag=-1 Offset=13 Length=26 Eof=0



gaxgpap345vuSun Jul 12 17:27:14 2015
Clump 1 at offset 0: SCAN CHECK04


gaxgpap345vuSun Jul 12 17:27:14 2015
Fatal exception during clumplet dump: Invalid clumplet buffer
structure: buffer end before end of clumplet - clumplet too long


gaxgpap345vuSun Jul 12 17:27:14 2015
Plain dump starting with offset 13: 0anessusscan0600

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] Issues migrating from FB 1.5 to 2.5

[Nick Upson n...@telensa.com [firebird-support]]

Hi

I believe its due to tighter syntax checking in 2 over 1.5

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 22 June 2015 at 16:25, srotyli...@gmail.com [firebird-support] 
firebird-support@yahoogroups.com wrote:



 I have an app that's been working fine for years on an XP box with FB 1.5.
 I upgraded to a newer box with Win 7 and installed FB 2.5. The app works
 great for about 95% but I get the following error under certain conditions:


 Application Error

 General SQL Error

 Column XXX.YYY cannot be repeated in INSERT statement.

 At line 6 column 78


 Is this due to a change in the way strings or INSERTs are handled ? Are
 there any solutions (I do not have access to the application source).


 Thanks in advance for any advice.

  

[firebird-support] firebird.log contents

[Nick Upson n...@telensa.com [firebird-support]]

Hi

this is from firebird 2.1.5, centos 5. I've never seen these messages
before, anyone?


myserverSun May 10 20:30:18 2015
INET/inet_error: read errno = 104


myserverSun May 10 20:30:23 2015
SERVER/process_packet: connection rejected for nessus.-1.-1


myserverSun May 10 20:30:23 2015
SERVER/process_packet: connect reject, server exiting


myserverSun May 10 20:30:29 2015
INET/inet_error: read errno = 104


myserverSun May 10 20:30:50 2015
*** DUMP ***


myserverSun May 10 20:30:50 2015
Tag=-1 Offset=13 Length=26 Eof=0


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

Your mention of memory  paging got me thinking. I expected the output
below to show the memory was fully used such that more RAM would help
but unless I'm reading this wrong only 5.3Gb is being used and 2.4 GB is
unused so more RAM will be no help, can anyone confirm or put me right

$ cat /proc/meminfo
MemTotal:  8309036 kB
MemFree:307408 kB
Buffers: 59296 kB
Cached:7433812 kB
SwapCached: 32 kB
Active:5365212 kB
Inactive:  2470416 kB
HighTotal: 7471040 kB
HighFree:18724 kB
LowTotal:   837996 kB
LowFree:288684 kB
SwapTotal: 4095992 kB
SwapFree:  4095788 kB
Dirty:  186968 kB
Writeback:   0 kB
AnonPages:  342384 kB
Mapped: 140812 kB
Slab:   148064 kB
PageTables:   6824 kB
NFS_Unstable:0 kB
Bounce:  0 kB
CommitLimit:   8250508 kB
Committed_AS:  2795520 kB
VmallocTotal:   116728 kB
VmallocUsed:  5788 kB
VmallocChunk:   110588 kB
HugePages_Total: 0
HugePages_Free:  0
HugePages_Rsvd:  0
Hugepagesize: 2048 kB


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 5 May 2015 at 15:21, Ann Harrison aharri...@ibphoenix.com
[firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Nick,


 I have a system that is slowing down the longer it stays running and I'd
 like to know why.

 ...


 Is there any evidence I can gather before I reboot the system which I
 expect (from past experience) will return the system to the better
 performance


 Hmmm   If rebooting will solve the problem and you don't have a very
 long running transaction, then I doubt that garbage accumulation or
 collection is the source of the problem.
 Nor is there anything else wrong with your physical database -
 fragmentation or whatever.
 I'd look at memory usage - using both Firebird and OS tools.  Look for
 paging before you reboot.
 After you reboot, track memory usage daily or more often.   It doesn't
 take much of a leak - or unfortunate caching - to built up over month of 27
 tps.

 Accelerating the move to 2.5.4 would also be a good idea, both because it
 may correct the problem and because it probably offers better diagnostic
 tools for this sort of problem.

 Good luck,

 Ann

  

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

Hi Steve

there is a significant time in iowait and this is a VM, so a disk
contention issue at the hyper-visor level ?

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 8 May 2015 at 14:55, Steve Wiser st...@specializedbusinesssoftware.com
[firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Nick,

 My interpretation of those stats is that you are now out of memory as I
 always look at the Swap Used.  You aren't using any.  My experience with
 Linux is that the OS will take as much memory as it can/needs so it can
 look like you are maxed out on RAM usage or something, but you really need
 to pay attention to the swap used.

 What I would be interested in seeing is that when you run your gbak backup
 go ahead and also run top.  Are you seeing a lot of CPU time being spent in
 IOWait?  If so that would mean that the CPU is being starved of data and is
 waiting around -- usually because the disk isn't fast enough.  We normally
 only see this on virtual hosts though.

 -steve


 --
 Steve Wiser
 President
 Specialized Business Software
 6325 Cochran Road, Unit 1
 Solon, OH 44139

 www.specializedbusinesssoftware.com
 www.docunym.com
 (440) 542-9145 - fax (440) 542-9143
 Toll Free: (866) 328-4936


 On Fri, May 8, 2015 at 6:32 AM, Nick Upson n...@telensa.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Your mention of memory  paging got me thinking. I expected the output
 below to show the memory was fully used such that more RAM would help
 but unless I'm reading this wrong only 5.3Gb is being used and 2.4 GB is
 unused so more RAM will be no help, can anyone confirm or put me right

 $ cat /proc/meminfo
 MemTotal:  8309036 kB
 MemFree:307408 kB
 Buffers: 59296 kB
 Cached:7433812 kB
 SwapCached: 32 kB
 Active:5365212 kB
 Inactive:  2470416 kB
 HighTotal: 7471040 kB
 HighFree:18724 kB
 LowTotal:   837996 kB
 LowFree:288684 kB
 SwapTotal: 4095992 kB
 SwapFree:  4095788 kB
 Dirty:  186968 kB
 Writeback:   0 kB
 AnonPages:  342384 kB
 Mapped: 140812 kB
 Slab:   148064 kB
 PageTables:   6824 kB
 NFS_Unstable:0 kB
 Bounce:  0 kB
 CommitLimit:   8250508 kB
 Committed_AS:  2795520 kB
 VmallocTotal:   116728 kB
 VmallocUsed:  5788 kB
 VmallocChunk:   110588 kB
 HugePages_Total: 0
 HugePages_Free:  0
 HugePages_Rsvd:  0
 Hugepagesize: 2048 kB


 Nick Upson, Telensa Ltd, Senior Operations Network Engineer
 direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

 On 5 May 2015 at 15:21, Ann Harrison aharri...@ibphoenix.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Nick,


 I have a system that is slowing down the longer it stays running and I'd
 like to know why.

 ...


 Is there any evidence I can gather before I reboot the system which I
 expect (from past experience) will return the system to the better
 performance


 Hmmm   If rebooting will solve the problem and you don't have a very
 long running transaction, then I doubt that garbage accumulation or
 collection is the source of the problem.
 Nor is there anything else wrong with your physical database -
 fragmentation or whatever.
 I'd look at memory usage - using both Firebird and OS tools.  Look for
 paging before you reboot.
 After you reboot, track memory usage daily or more often.   It doesn't
 take much of a leak - or unfortunate caching - to built up over month of 27
 tps.

 Accelerating the move to 2.5.4 would also be a good idea, both because
 it may correct the problem and because it probably offers better diagnostic
 tools for this sort of problem.

 Good luck,

 Ann



  

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

Hi Steve

no not AWS

We've done tests and the disk does not appear to be under load, maybe we
are just not seeing it

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 8 May 2015 at 16:25, Steve Wiser st...@specializedbusinesssoftware.com
[firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Nick,

 Oops, I noticed on my first email that I said now out of memory when I
 should have said not out of memory..

 Are you by chance using Amazon Web Services?  If so I would say maybe your
 EBS volume was not prewarmed.

 Either way, it does sound to me that your CPU is waiting for the disk to
 send data over, usually because the shared disk is under load.

 -steve


 --
 Steve Wiser
 President
 Specialized Business Software
 6325 Cochran Road, Unit 1
 Solon, OH 44139

 www.specializedbusinesssoftware.com
 www.docunym.com
 (440) 542-9145 - fax (440) 542-9143
 Toll Free: (866) 328-4936


 On Fri, May 8, 2015 at 10:26 AM, Nick Upson n...@telensa.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Steve

 there is a significant time in iowait and this is a VM, so a disk
 contention issue at the hyper-visor level ?

 Nick Upson, Telensa Ltd, Senior Operations Network Engineer
 direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

 On 8 May 2015 at 14:55, Steve Wiser st...@specializedbusinesssoftware.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Nick,

 My interpretation of those stats is that you are now out of memory as I
 always look at the Swap Used.  You aren't using any.  My experience with
 Linux is that the OS will take as much memory as it can/needs so it can
 look like you are maxed out on RAM usage or something, but you really need
 to pay attention to the swap used.

 What I would be interested in seeing is that when you run your gbak
 backup go ahead and also run top.  Are you seeing a lot of CPU time being
 spent in IOWait?  If so that would mean that the CPU is being starved of
 data and is waiting around -- usually because the disk isn't fast enough.
 We normally only see this on virtual hosts though.

 -steve


 --
 Steve Wiser
 President
 Specialized Business Software
 6325 Cochran Road, Unit 1
 Solon, OH 44139

 www.specializedbusinesssoftware.com
 www.docunym.com
 (440) 542-9145 - fax (440) 542-9143
 Toll Free: (866) 328-4936


 On Fri, May 8, 2015 at 6:32 AM, Nick Upson n...@telensa.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Your mention of memory  paging got me thinking. I expected the output
 below to show the memory was fully used such that more RAM would help
 but unless I'm reading this wrong only 5.3Gb is being used and 2.4 GB
 is unused so more RAM will be no help, can anyone confirm or put me right

 $ cat /proc/meminfo
 MemTotal:  8309036 kB
 MemFree:307408 kB
 Buffers: 59296 kB
 Cached:7433812 kB
 SwapCached: 32 kB
 Active:5365212 kB
 Inactive:  2470416 kB
 HighTotal: 7471040 kB
 HighFree:18724 kB
 LowTotal:   837996 kB
 LowFree:288684 kB
 SwapTotal: 4095992 kB
 SwapFree:  4095788 kB
 Dirty:  186968 kB
 Writeback:   0 kB
 AnonPages:  342384 kB
 Mapped: 140812 kB
 Slab:   148064 kB
 PageTables:   6824 kB
 NFS_Unstable:0 kB
 Bounce:  0 kB
 CommitLimit:   8250508 kB
 Committed_AS:  2795520 kB
 VmallocTotal:   116728 kB
 VmallocUsed:  5788 kB
 VmallocChunk:   110588 kB
 HugePages_Total: 0
 HugePages_Free:  0
 HugePages_Rsvd:  0
 Hugepagesize: 2048 kB


 Nick Upson, Telensa Ltd, Senior Operations Network Engineer
 direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

 On 5 May 2015 at 15:21, Ann Harrison aharri...@ibphoenix.com
 [firebird-support] firebird-support@yahoogroups.com wrote:



 Hi Nick,


 I have a system that is slowing down the longer it stays running and
 I'd like to know why.

 ...


 Is there any evidence I can gather before I reboot the system which I
 expect (from past experience) will return the system to the better
 performance


 Hmmm   If rebooting will solve the problem and you don't have a
 very long running transaction, then I doubt that garbage accumulation or
 collection is the source of the problem.
 Nor is there anything else wrong with your physical database -
 fragmentation or whatever.
 I'd look at memory usage - using both Firebird and OS tools.  Look for
 paging before you reboot.
 After you reboot, track memory usage daily or more often.   It doesn't
 take much of a leak - or unfortunate caching - to built up over month of 
 27
 tps.

 Accelerating the move to 2.5.4 would also be a good idea, both because
 it may correct the problem and because it probably offers better 
 diagnostic
 tools for this sort of problem.

 Good luck,

 Ann





  

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

On 5 May 2015 at 12:23, Fabiano Kureck - Desenvolvimento SCI
fabi...@sci10.com.br [firebird-support] firebird-support@yahoogroups.com
wrote:

 In my experience, GC collected by the gbak is different from sweep.
 I also have a problem with only my big customer (35Gb database) when the
 system become slow and slow over time. The solution was do a gbak with
 Garbage collect and then a sweep after.

 First time you do this it will take probably a couple of hours. Sweep
 moves the transaction counter, gbak no (at least no in every situation).
 For testing purpose you can run a sweep now and the backup will take a lot
 less time this night. Give a try.


​I already do a sweep weekly, I also tried gbak -gc followed by a sweep the
total time was even longer​


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

yes, large transaction gap was the first thing I checked

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 5 May 2015 at 12:01, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] firebird-support@yahoogroups.com wrote:



 On 5-5-2015 12:15, Nick Upson n...@telensa.com [firebird-support] wrote:
  I have a system that is slowing down the longer it stays running and I'd
  like to know why.
 
  The system is running firebird 2.1.5 on centos 5 with an average of 27
  transactions per second and has now been running for 112 days. The data
  throughput is unchanged, data is removed from the db as fast as its
  added so the database remains roughly the same size at 130Gb.
 
  for example: on 1st Feb the backup took 4 hr 35 min, last night took
  9hrs 30 min
 
  Is this a known 2.1 issue (move to 2.5 is in the planning stages)?
 
  Is there anything I can do to prevent or improve this situation?
 
  Is there any evidence I can gather before I reboot the system which I
  expect (from past experience) will return the system to the better
  performance

 Have you checked for the existence of long running transactions (a high
 transaction gap)?

 Mark
 --
 Mark Rotteveel
  

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

On 5 May 2015 at 12:09, Fabiano Kureck - Desenvolvimento SCI
fabi...@sci10.com.br [firebird-support] firebird-support@yahoogroups.com
wrote:

 Did you run a Sweep before backup?


​no but then the backup does gc, I'm interested in why the suggestion, I
can try it​


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] large system slows over time

[Nick Upson n...@telensa.com [firebird-support]]

On 5 May 2015 at 12:12, 'Thomas Steinmaurer' t...@iblogmanager.com
[firebird-support] firebird-support@yahoogroups.com wrote:

 How does your backup call look like?


​gbak, -user sysdba -pass x -backup hostname:/opt/pathtodb
/pathto/backuparea
​


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] garbage collect

[Nick Upson n...@telensa.com [firebird-support]]

great thanks

to make sure I've got this right

starting with the same database I can do either

a) gbak with gc

b) gbak without gc and gfix -sweep

and I will have the same final result


Extending this further, how can identify which tables contain (or did
contain) the garbage.

As well as handling it better I'm trying to see if we can cause less of it




Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 5 April 2015 at 18:07, Ann Harrison aharri...@ibphoenix.com
[firebird-support] firebird-support@yahoogroups.com wrote:




 On Apr 5, 2015, at 11:23 AM, Nick Upson n...@telensa.com [firebird-support]
 firebird-support@yahoogroups.com wrote:


 is there any effective difference between doing gbak with gc and
 gbak without gc and then gfix -sweep


 The only differences (that I can think of) are caused by concurrent
 actions. If you garbage collect during backup, the sweep can remove new
 garbage created while the backup ran.

 Imagine that garbage collection takes enough time for fine thousand
 transactions to complete and each of those transactions makes three record
 versions obsolete. Neither gbak nor sweep can remove record versions made
 obsolete by the completion of a concurrent transaction.

 So if you backup without garbage collection, your backup is quicker, sweep
 starts sooner and takes longer so those 15,000 old record versions continue
 moldering in your database.

 If you backup with garbage collection and then sweep, the backup is
 slower, but the sweep starts later and can remove those unlovable record
 versions.  Sort of a wash, rinse, repeat thing - if that phrase means
 anything to those who aren't elderly
 Americans.

 Cheers,


 Ann
 From sunny Sint Maarten.

   

[firebird-support] garbage collect

[Nick Upson n...@telensa.com [firebird-support]]

Hi

I'd like to do  my backup without gc and then do the gc separately -

is there any effective difference between doing gbak with gc and
gbak without gc and then gfix -sweep


Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

Re: [firebird-support] Re: Is there still a difference between sweep and gabage collection?

[Nick Upson n...@telensa.com [firebird-support]]

so

if I run gbak -g every night I should also run gfix​, but if I run gbak
without -g I don't need gfix (but it helps a little) ?

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 20 February 2015 at 18:36, Ann Harrison aharri...@ibphoenix.com
[firebird-support] firebird-support@yahoogroups.com wrote:




 Thomas,

 Gbak backup removes all unnecessary back versions and all rolled back
 changes, unless it is run with the -g switch. That switch is provided for
 those cases where a database cleanup is not needed. Examples include the
 case when the database will immediately be replaced by a restored version,
 upgrading the ODS, for example, or when a sweep has just been run.

 What gbak does not do that sweep does it change the identity of the oldest
 interesting transaction. Unlike the oldest active transaction, the oldest
 interesting transaction is not critical for performance, but having it
 correct does make things slightly better. The oldest interesting
 transaction is the first transaction, still visible in the database, that
 did not commit. Any data created by older transactions is known to have
 been created by committed transactions. A transaction reading it doesn't
 have the check the state of the creating transaction. When accessing data
 created by a more recent transaction, the reader must test a bit in a bit
 vector to determine the state of the creating transaction. Pretty cheap.

 During a sweep, gfix notes the oldest transaction whose work it could not
 clean up. At the end of the sweep, that becomes the oldest interesting
 transaction.

 In the absence of the -g switch or the attachment parameter that
 implements that switch, any process that reads all the data in a database
 removes all unnecessary old or rolled back record versions, either itself
 or through the proxy of the garbage collect thread. Only gfix changes the
 recorded oldest interesting transaction.

 Cheers,

 Ann

  

Re: [firebird-support] Three trailing spaces?

[Nick Upson n...@telensa.com [firebird-support]]

if you use isql or dbworkbench to do the same query, that will tell you if
the problem is php or firebird​

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 12 December 2014 at 13:55, Tim Ward t...@telensa.com [firebird-support] 
firebird-support@yahoogroups.com wrote:



 I've got a column defined as

 MODACTION D_MODACTION ,

 where the domain is defined as

 CREATE DOMAIN D_MODACTION AS
 CHAR(1) CHARACTER SET ASCII
 NOT NULL
 CHECK (VALUE IN ( 'A', 'U', 'D', 'M', 'S' ))
 COLLATE ASCII;

 I've got a record which appears to have 'A' in this field.

 But when I do a SELECT * FROM ... (from PHP using ibase_fetch_assoc) I
 don't get the string

 'A'

 I get the string

 'A '

 (three spaces after the A). Yes this may be a PHP question, but just in
 case it isn't, any ideas?

 --
 Tim Ward

  

Re: [firebird-support] Export data to CSV file from stored procedure?

[Nick Upson n...@telensa.com [firebird-support]]

you could define an external table with 1 field, construct a csv within the
SP ​and write to the table

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200


On 20 August 2014 17:19, conver...@gmail.com [firebird-support] 
firebird-support@yahoogroups.com wrote:



 Hi there,

 As subject, is it possible to export a query result set to an external csv
 file from a Firebird stored procedure? I know this can be done with
 IBExpert but we are looking for a pure firebird sql solution. We are using
 Firebrd 2.5.


 Any pointers on how to do this will be much appreciated.


 Regards,


 -Ed


  

Re: [firebird-support] Database Forensics

[Nick Upson n...@telensa.com [firebird-support]]

you will need to copy the database to your hard disk, you can't operate
with a database on a read-only media unless the database is prepared for
this (seems unlikely)​

Nick Upson, Telensa Ltd, Operations Network Engineer
direct +44 (0) 1799 533252, support +44 (0) 1799 399200



On 10 July 2014 18:34, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] firebird-support@yahoogroups.com wrote:



 On 9-7-2014 21:34, 'Edward Mendez' emendez...@nc.rr.com
 [firebird-support] wrote:
  I have a situation where I might have to open up a FIREBIRD Database
  file that I didn’t create or maintain. I probably will not know the
  exact version of FIREBIRD this file was used with. It can be anything
  from FIREBIRD 2.1.4 up to 2.5.2
 
  The database file will be given to me on a read only external HD. It
  will be accessed from a windows 7 PC. Everything I will need must
  already be preinstalled on this PC before taking possession of this
  Database File. (i.e. this PC will not be connected to the internet in
  order to install new software.) I can have an installer reside on a
  folder for install if I need it.

  One question I have was that if I install a FB 2.5.2 Server on this
  windows PC would I be able to open a Database that has been created
  using a 2.1.4 server?

 Yes, a Firebird 2.5 can open database from Firebird 2.1. Upgrading the
 database by backup and restore is a best practice (it upgrades the ODS
 so new features are available), but it is not strictly necessary. With
 Firebird 3 this is going to change!

  Are there any programs out there that can open up a Firebird DB locally
  without a server/embedded installed?

 At a minimum you will need a program that uses Firebird embedded.

  My plan was to…
 
  1)Keep a copy of the msi installers for Firebird server 2.1.4/5  2.52
  somewhere on the Disk of this PC.

 That sounds like the wisest course of action.

  2)Install a copy of IBEXPERT free edition on the PC to view the
 data/schema
 
  3)Install FBExport  FBCopy in case I need to export the data into
  another format.
 
  Is there something I might have missed or something that will work
  better than what I plan to use.

 Can't think of anything else right now.

 However, the big question is: what is it you need to do with the
 database? If the database is damaged and the needs to be repaired, then
 Firebird will not be able to (correctly) read the file, and you'd sooner
 need something like IBSurgeon FirstAID (assuming simple fixes with gfix
 won't work).

 Mark

 --
 Mark Rotteveel