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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

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

-- 

*Andreas Zeller*

Geschäftsleitung
lux-medien.com 
Carl-Friedrich-Gauss-Str. 56
47475 Kamp-Lintfort

Office: +49 2151 32 66 628
Fax: +49 2151 32 66 721
Mobil: +49 163 27 9 1979







++

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

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.

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi Sean,

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.

Andreas


On 27.02.2017 00:25, Andreas Zeller zel...@lux-medien.com
[firebird-support] wrote:
>  
>
> Hi Sean,
>
> that's what I am saying. It never really is 'under load'. It is just
> taking forever to select a clients data-page.
>
> I would blame bad design and shrug it off, but this was way faster on
> the ancient w2k server, so I have no idea where it gets stuck.
>
> Andreas
>
>
> On 26.02.2017 23:54, 'Leyne, Sean' s...@broadviewsoftware.com
> [firebird-support] wrote:
>>  
>>
>>
>>
>> > Sean: fb_lock_print seems to have some trouble:
>> >
>> > Unable to access lock table.
>> > operating system directive shmem_data->sh_mem_length_mapped is 0
>> > failed -Success
>> >
>> > This is what I am getting from fb_lock_print -d filename.db0
>>
>> You need to specific the full/local path to the database and as well
>> as the database filename.
>>
>> You really want to look at the fb_lock_print numbers when the
>> database/server is under load.
>>
>> Sean
>>
>
> 

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi Mark,

thanks for the info. Someone already told me that the first message is
moderated. It's the first time I joined a yahoo group for a project like
that. Most of the open source projects have mailman or some sourceforge
stuff setup and you can just post away :)

Andreas


On 27.02.2017 12:05, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] wrote:
>  
>
> On 26-2-2017 17:46, andreasmzel...@yahoo.de [firebird-support] wrote:
> > 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.
>
> You don't need an Yahoo account, subscribing to the normal mailing list
> is sufficient, however - afaik - first posts will need to be approved by
> the moderator, and that can take some time, especially on weekends.
>
> Mark
> -- 
> Mark Rotteveel
>
> 

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

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 26-2-2017 17:46, andreasmzel...@yahoo.de [firebird-support] wrote:
> 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.

You don't need an Yahoo account, subscribing to the normal mailing list 
is sufficient, however - afaik - first posts will need to be approved by 
the moderator, and that can take some time, especially on weekends.

Mark
-- 
Mark Rotteveel

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi Sean,

that's what I am saying. It never really is 'under load'. It is just
taking forever to select a clients data-page.

I would blame bad design and shrug it off, but this was way faster on
the ancient w2k server, so I have no idea where it gets stuck.

Andreas


On 26.02.2017 23:54, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] wrote:
>  
>
>
>
> > Sean: fb_lock_print seems to have some trouble:
> >
> > Unable to access lock table.
> > operating system directive shmem_data->sh_mem_length_mapped is 0
> > failed -Success
> >
> > This is what I am getting from fb_lock_print -d filename.db0
>
> You need to specific the full/local path to the database and as well
> as the database filename.
>
> You really want to look at the fb_lock_print numbers when the
> database/server is under load.
>
> Sean
>
> 

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Sean,

thanks for the response. Answers below.


On 26.02.2017 23:52, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] wrote:
>  
>
> Andreas,
>
> > - 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
>
> With Classic server you *need* to reduce the number of cached pages --
> I would recommend a value < 500 pages
>
> people keep telling me otherwise. Why would I not want firebird to
> fill up the cache with lots of pages? Am I misunderstanding firebirds
> buffer concept?
>
>  There are 3 different ‘modes’ that Firebird can run in. 
>
>  
>
>  “Classic” mode is for a large number of connections.  For that
> mode, the number of pages needs to be reduced. 
>
>  
>
>  For “SuperServer” and “SuperClassic” modes the number of pages
> can be maximized.
>
Alright. I am well aware of that. Each process has its own cache,
inheriting the value from the configuration. Cache and buffers are
configured accordingly. Thanks for the heads up though.
>
>  
>
>  
>
> why would it do that? It is entirely under our control when and if the
> server restarts or shuts down. I agree with the reasoning, I just
> don't see the scenario. Power outage would be the worst case scenario.
> And if that actually happened mid-transaction, we have nightly backups
> that would be good enough.
>
> Forced Writes = ON is HIGHLY recommended in all production use-cases.
>
> I realize that. The worst case scenario is acceptable to the client
> though.
>
>  You must be in a unique environment.
>
nope. It's just that it is a very unlikely scenario and if this actually
happens, losing just a days work is an acceptable risk. I'm not saying
that this wouldn't be bad, but all of the work done can be easily
reconstructed using e-mails and other incoming data.
>
>  
>
>  My clients/users would have my skin if I told them they had to
> redo all their work at 5pm, the end of a busy work day – just because
> I wanted to save “millisecond” for each write operation (given the
> presence of a RAID controller with battery cache) by using Forced
> Writes = Off and the power went out (someone bumped a power switch).
>
I can see that. But in this particular scenario, we're talking 1-2
seconds per mouse-click. Depending on the operation. if you slow down
everything (!) like that, it is going to be a lot more productive for
them to get some work done every day than losing a days work around
every two years :) but generally you are right.

This is the first time we encountered a firebird database, so this is
new ground to all of us.

Lock tables seem to be empty though. That is what google told me about
the error.

I really am lost. fb_config tells me that the config directory is the
one I picked. However, firebird still uses /tmp/firebird as a temp
directory, regardless of what I set as envvar or in the config.

So I am obviously concerned that none of the settings I make do have any
effect.

How can I check out if any of that had any effect?

Andreas

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Sean: fb_lock_print seems to have some trouble:
> 
> Unable to access lock table.
> operating system directive shmem_data->sh_mem_length_mapped is 0
> failed -Success
> 
> This is what I am getting from fb_lock_print -d filename.db0

You need to specific the full/local path to the database and as well as the 
database filename.

You really want to look at the fb_lock_print numbers when the database/server 
is under load.


Sean

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Andreas,

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

With Classic server you *need* to reduce the number of cached pages -- I would 
recommend a value < 500 pages
people keep telling me otherwise. Why would I not want firebird to fill up the 
cache with lots of pages? Am I misunderstanding firebirds buffer concept?

 There are 3 different ‘modes’ that Firebird can run in.

 “Classic” mode is for a large number of connections.  For that mode, the 
number of pages needs to be reduced.

 For “SuperServer” and “SuperClassic” modes the number of pages can be 
maximized.


why would it do that? It is entirely under our control when and if the server 
restarts or shuts down. I agree with the reasoning, I just don't see the 
scenario. Power outage would be the worst case scenario. And if that actually 
happened mid-transaction, we have nightly backups that would be good enough.


Forced Writes = ON is HIGHLY recommended in all production use-cases.
I realize that. The worst case scenario is acceptable to the client though.

 You must be in a unique environment.

 My clients/users would have my skin if I told them they had to redo all 
their work at 5pm, the end of a busy work day – just because I wanted to save 
“millisecond” for each write operation (given the presence of a RAID controller 
with battery cache) by using Forced Writes = Off and the power went out 
(someone bumped a power switch).



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

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

You should look at the fb_lock_print details, it is possible that you are 
experience issue with the external lock manager.
Thanks for this information. I will check it out as soon as possible. Wasn't 
even aware there is a lock manager.

Andreas



Sean

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

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

Hi,

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

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


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


Regards,
Alexey Kovyazin
IBSurgeon


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

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

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

The following things have already been done:

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

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

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

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

Here are a few things that I have personally noticed:

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

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

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

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

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

Versions:

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

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

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

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

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

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

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

of course. The server is horribly bored. 16 CPUs, 12 GB RAM and still
the system takes forever during selects with just one client connected
to it.

CPU usage for the spawned process is less than 3%, memory usage below 1%.

I/O wasn't even on my radar. It's RAID0, dedicated controller, XFS,
single client.

What are your suggesting?

Andreas


On 26.02.2017 21:31, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] wrote:
> 26.02.2017 21:07, Andreas Zeller zel...@lux-medien.com [firebird-support] 
> wrote:
>> I am still curious about how this might affect performance?
>Did you monitor resource usage? CPU, RAM, IO?
>
>







++

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

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

26.02.2017 21:07, Andreas Zeller zel...@lux-medien.com [firebird-support] wrote:
> I am still curious about how this might affect performance?

   Did you monitor resource usage? CPU, RAM, IO?


-- 
   WBR, SD.






++

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

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi Dimitry, hi Sean,

>Did you check obvious things: direct and reverse DNS resolution for server 
> host on 
> client host and both hosts on server host?
>
>
I didn't realize those were obvious things, but connections are being
made via the IP. So there's no name-resolution delay. I just checked on
the server wether the NIS resolution works. It does work both ways
without the slightest delay.

I am still curious about how this might affect performance?

Sean: fb_lock_print seems to have some trouble:

Unable to access lock table.
operating system directive shmem_data->sh_mem_length_mapped is 0 failed
-Success

This is what I am getting from fb_lock_print -d filename.db0

I'll google that in the meantime.

Thanks guys,

Andreas






++

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi Sean,

first off, thanks for the quick response. answers below.


On 26.02.2017 20:14, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] wrote:
>  
>
> Andreas,
>
> > - 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
>
> With Classic server you *need* to reduce the number of cached pages --
> I would recommend a value < 500 pages
>
people keep telling me otherwise. Why would I not want firebird to fill
up the cache with lots of pages? Am I misunderstanding firebirds buffer
concept?
>
>
>
> > - switchted to async writes because we have redundancy and data security
> > on hardware level (also via gfix)
>
> Hardware redundancy and security will not protect if the hosts/OS
> restarts or Firebird abends without warning.
>
why would it do that? It is entirely under our control when and if the
server restarts or shuts down. I agree with the reasoning, I just don't
see the scenario. Power outage would be the worst case scenario. And if
that actually happened mid-transaction, we have nightly backups that
would be good enough.
>
>
> Forced Writes = ON is HIGHLY recommended in all production use-cases.
>
I realize that. The worst case scenario is acceptable to the client though.
>
>
>
> > After all of this didn't really seem to do much, we had to pull out
> the big guns:
>
> > Performance improved a bit, but still not the way we would like it
> to be.
>
> You should look at the fb_lock_print details, it is possible that you
> are experience issue with the external lock manager.
>
Thanks for this information. I will check it out as soon as possible.
Wasn't even aware there is a lock manager.

Andreas
>
>
>
> Sean
>
> 

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

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

26.02.2017 12:55, Andreas Zeller zel...@lux-medien.com [firebird-support] wrote:
> 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.

   Did you check obvious things: direct and reverse DNS resolution for server 
host on 
client host and both hosts on server host?


-- 
   WBR, SD.






++

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

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

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

Andreas,

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

With Classic server you *need* to reduce the number of cached pages -- I would 
recommend a value < 500 pages


> - switchted to async writes because we have redundancy and data security
> on hardware level (also via gfix)

Hardware redundancy and security will not protect if the hosts/OS restarts or 
Firebird abends without warning.

Forced Writes = ON is HIGHLY recommended in all production use-cases.


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

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

You should look at the fb_lock_print details, it is possible that you are 
experience issue with the external lock manager.


Sean

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

[andreasmzel...@yahoo.de [firebird-support]]

 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



 

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

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi everyone,


I'll try and provide as many details as possible, so this message is
going to be a little bit longer, but here goes:


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


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


The following things have already been done:


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


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


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


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


Here are a few things that I have personally noticed:


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


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


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


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


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


Versions:


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


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


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


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


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


Andreas
-- 


*Andreas Zeller*


Geschäftsleitung
lux-medien.com 
Carl-Friedrich-Gauss-Str. 56
47475 Kamp-Lintfort


Office: +49 2151 32 66 628
Fax: +49 2151 32 66 721
Mobil: +49 163 27 9 1979