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

2016-04-13 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
>>Hey,
>>not sure how you can survive with superserver :)
>>I can't see that working with our kind of load (realtime-data-processing, 
>>reports, mostly write IOPS)
>>It's a long time ago (Fb 1.5) since we used superserver but we didn't have 
>>the best time with it back then.
 
I do not know what is your database for
but we have monitoring database where we move all older than 3 day data to 
monthly archive databases.
Then any raports created are fast on working database and slow down in archive.
Archive databases are on different server. Then we never do large raport on big 
data on production server.
>>But currently this is not about switching the server-version. More about 
>>undestanding why the server is not using the provided hardware :)
>>Also it reads kinda strange if you talk about high load, but always have low 
>>cpu usage :)
 
i have high load with low cpu.
When i mean load i think about IO.
I have many record modifications/insert but cpu near to never increase above 
50% of core.
Maybe you have much more load i have 17 000 000 new records stored into 
database and one new record cause 1 to many updates to existing records.
if we calc than we have 196 record per second (if i cal this correctly) - i do 
not know what is your load to compare.
 
All work fast - and only system slow down, when backup start.
But backup is fast because we do backup to RAM disk and then automatically we 
move this backup to another server where automatically db is restored to check 
if db is healthy
 
regards,
Karol Bieniaszewski

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

2016-04-12 Thread thetr...@yahoo.com [firebird-support] 
Hey,
not sure how you can survive with superserver :)
I can't see that working with our kind of load (realtime-data-processing, 
reports, mostly write IOPS)
It's a long time ago (Fb 1.5) since we used superserver but we didn't have the 
best time with it back then.

But currently this is not about switching the server-version. More about 
undestanding why the server is not using the provided hardware :)

Also it reads kinda strange if you talk about high load, but always have low 
cpu usage :)
 

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

 Hi,
  
 just curious – why not superserver?
 I do not know what your system do and if it is cpu intensive –
 but i always use superserver because of big cache and this is for me biggest 
speed up.
 I use superserver in environment with ~400 connections (near constant) and 
high load without problem.
  
 PS. i wait for FB3.0 and all my servers will gain another speed up because of 
multicore use.
 But i have always low cpu usage also in high load system – and i do not know 
why you can got 80% CPU load – maybe this is some issue or some “weekness” of 
classic?
  
 regards,
 Karol Bieniaszewski
  
 From: mailto:firebird-support@yahoogroups.com 
mailto:firebird-support@yahoogroups.com
 Sent: Monday, April 11, 2016 2:55 PM
 To: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com
 Subject: [firebird-support] [FB 2.1] Firebird engine seems to slow down on 
high load without utilizing hardware


  

   
 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.
< p>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, A cquire 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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

2016-04-12 Thread liviusliv...@poczta.onet.pl [firebird-support] 
Hi,

just curious – why not superserver?
I do not know what your system do and if it is cpu intensive – 
but i always use superserver because of big cache and this is for me biggest 
speed up.
I use superserver in environment with ~400 connections (near constant) and high 
load without problem.

PS. i wait for FB3.0 and all my servers will gain another speed up because of 
multicore use.
But i have always low cpu usage also in high load system – and i do not know 
why you can got 80% CPU load – maybe this is some issue or some “weekness” of 
classic?

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, April 11, 2016 2:55 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high 
load without utilizing hardware

  

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.

< p>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, A cquire 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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

2016-04-12 Thread 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] 
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 setting shows an 
AVG hash length which looks fine.

As you might know, Classic having a dedicated process per connection model 
somehow needs a (global) mechanism to synchronize/protect shared data 
structures across these processes via IPC. This is what the lock manager and 
the lock table is used for.

> 2) As far as we know, we can't influence Mutex wait directly (it's just
> informational). But do you think that's the reason the underlying hardware is
> not utilized?

I don't think you are disk IO bound. Means, I'm not convinced that faster IO 
will help. Somehow backed by the high mutex wait. Under normal operations you 
see 100-500 IOPS with some room for further increase as shown in the 1700 IOPS 
backup use case. Don't know how random disk IO is in this two scenarios. Any 
chance to run some sort of disk IO benchmarks or do you already know your upper 
limits for your SAN IOPS wise?

> 
> 
> We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last year,
> waiting for it to get ready.
> With 2.5.x we tested around a long time now, but never found a real reason to
> upgrade - since it's a reasonable amount of work for us. When you say it
> improves the lock contention, this sound pretty good. But again the question,
> do you think lock contention is limiting our system?

Dmitry, Vlad etc. will correct me (in case he is following the thread), but I 
recall that in 2.5, especially in SuperClassic being multi-threaded per worker 
process compared to Classic, now also allows specific(?) lock manager 
operations in parallel to regular request processing. In general I remember a 
mentioned improvement of ~25% in a TPC-C style workload with SuperClassic 
compared to Classic.

> 
> 
> First and foremost, we would really like to find the bottleneck. We just don't
> have the know-how to imagine something like "Fb 2.1 Engine is limiting us
> because of ..." and without that knowledge it's hard to take actions like
> upgrading to 2.5.
> 
> 
> We'll try to collect information about the garbage we create :) We do run
> "Sinatica Monitoring" on the server, which shows us "Awaiting Gargabe
> Collection" Transactions. Is that the information you'r looking for?

I'm not familiar with Sinatica. Perhaps the periodic MON$ queries (how frequent 
are they executed by Sinatica?) also produce some sort of overhead, cause each 
MON$ table query in context of a new physical transaction results in a stable 
view of current activity. Possibly not neglectable with > 400 connections.

The most easiest way to get insights on your record garbage is, e.g.:

* Run gstat -r
* Run a tool from IBSurgeon (can't recall the name, Alexey?)
* Run a tool from Upscene (FB TraceManager)

> 
> Maybe to avoid confusion, we don't have normal "Spikes" .. the system just
> starts to slow down and this state remains until the server-load is gone 
> (after
> midnight, when software is not used anymore).



--
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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

2016-04-12 Thread 'Thomas Steinmaurer' t...@iblogmanager.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?

I think, disk IO isn't the limiting factor in that environment.



--
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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

2016-04-12 Thread 'Thomas Steinmaurer' t...@iblogmanager.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.

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



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

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


>> 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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

2016-04-12 Thread 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] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware

2016-04-12 Thread thetr...@yahoo.com [firebird-support] 
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)
 

 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)?
 2) As far as we know, we can't influence Mutex wait directly (it's just 
informational). But do you think that's the reason the underlying hardware is 
not utilized?
 

 We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last year, 
waiting for it to get ready.
 With 2.5.x we tested around a long time now, but never found a real reason to 
upgrade - since it's a reasonable amount of work for us. When you say it 
improves the lock contention, this sound pretty good. But again the question, 
do you think lock contention is limiting our system?
 

 First and foremost, we would really like to find the bottleneck. We just don't 
have the know-how to imagine something like "Fb 2.1 Engine is limiting us 
because of ..." and without that knowledge it's hard to take actions like 
upgrading to 2.5.
 

 We'll try to collect information about the garbage we create :) We do run 
"Sinatica Monitoring" on the server, which shows us "Awaiting Gargabe 
Collection" Transactions. Is that the information you'r looking for?
 

 Maybe to avoid confusion, we don't have normal "Spikes" .. the system just 
starts to slow down and this state remains until the server-load is gone (after 
midnight, when software is not used anymore).
 

 Best Regards,
 

 Patrick Friessnegg
 Synesc GmbH

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

2016-04-12 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-04-11 13:55, thetr...@yahoo.com [firebird-support] wrote:
> hi,
>
> recently we had some strange performance issues with our Firebird DB
> server.
>
> 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

At minimum update to Firebird 2.1.7, several bugs including security 
issues were fixed in 2.1.6 and 2.1.7.

Also consider to investigate upgrading to Firebird 2.5.x.


Mark

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

2016-04-11 Thread Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
Patrick,

> 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

fb_lock_print is reporting a hash slots value of 15077 but you show a 
setting of 20011. Mutex wait looks high to me.

Some ideas:
* Increase the hash slots value to 30011
* Get a picture on how many garbage (record versions) you create. AFAIR 
it is the -r switch of gstat which gives you that information. Sudden 
spikes in the statement response time could be related to co-operative 
garbage collection in Classic/SuperClassic, where basically the 
statement synchronously removes garbage of out-dated record versions
* Consider upgrading to 2.5. 2.1.7 is end-of-life and 2.5 improved in 
the area of lock contention in Classic/SuperClassic substantially.


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

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