Re: [firebird-support] Cache Performance Options
Hi Karol, what Windows 2008 memory problem are You refering to that needs 2.5.3? Thanks Zsazsi 2014-05-13 7:23 GMT+02:00 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com: Hi, Your database is not big but your settings are low. Change your FB server to 64bit and increase your dbbuffer or in fbconfig defaultdbcasche setting and use superserver. Check your database data page size. I always set it to 16KB. Your database utylize memory in this pattern: Dbpage size * dbbuffers Or if dbbuffer is 0 Db page size * defaultdbcachepages You must add to this sort buffer for any connection. You should also download shnapshot of firebird 2.5.3.xxx to fix windows 2008 memory problem Regards, Karol Bieniasxewski Wysłane z mojego HTC - Reply message - Od: Joshua Hartmann jhartm...@computekdental.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: pon., maj 12, 2014 21:32 I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. The server is QEMU/KVM virtual server running Microsoft Server 2008 R2. The host machine is running Ubuntu Server 12.04. Xeon 1275 4 cores allocated to the virtual server 12GB memory allocated to the virtual server (32GB total on the host machine) 750GB Momentus Hybrid SSD/HDD's (RAID 0+1 1.5TB total usable) The database is ~30GB At first, they were running in classic server mode (FB version 2.1.3 x86) on a physical server 2003 machine with 4GB of memory. That machine's HDD's started to fail so we just moved it to the new virtual 2008 server. We let the software company do the install and they set it to classic mode (version 2.1.3 x86) but left all options at default. We noticed the server would start pretty fast but then throughout the day slow down. When checking the system's resources, I noticed it was maxing out on RAM and moving to the page file which explained the slowness. However, task manager did not display that memory was actually being used by the fbserver.exe processes. I asked the developer to switch it to superserver because I read its better for shared cache and since all the clients are accessing the same data (appointment lists, patient lists, etc) I thought it would speed things up. It didn't change the memory usage and fbserver.exe still only reported using about 60MB. So I started reading through articles online and found a bunch of optimization and cache settings. I changed the database options for async, set page buffers at 60k, and turned off auto sweeping. I also changed the firebird.conf settings. Specifically, the MaxFileSystemCache. None of it made any difference. Next, I upgraded to 2.5.2 (still x86) and its made a little difference. I still notice the fbserver using quite a bit of disk IO though and its still not very fast. I guess my questions are: In my situation, should I be using all file system cache or firebird cache? Essentially, what should I set my page buffers and FileSystemCacheThreshole to? I've already set my FileSystemCache to 90%. Should I enable or disable async? Should I enable auto sweeping or not? Would performance be faster if we upgraded to the 64bit version of 2.5.2? Thanks for any advice you can give. -Josh
Re: [firebird-support] Cache Performance Options
Hi Karol, I can see nothing related, The only problem that shows up was fixed in 2.5.2 already. http://tracker.firebirdsql.org/browse/CORE-3791 Do You know something more specific? Regards Zsazsi 2014-05-13 8:56 GMT+02:00 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com: Hi, Write in google firebird and windows 2008 cache problem and you will see Regards, Karol Bieniaszewski - Reply message - Od: Zsazsi m-zs...@freemail.hu [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: wt., maj 13, 2014 08:44 Hi Karol, what Windows 2008 memory problem are You refering to that needs 2.5.3? Thanks Zsazsi
Re: [firebird-support] Cache Performance Options
Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. -Josh On Tue, May 13, 2014 at 12:18 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. We noticed the server would start pretty fast but then throughout the day slow down. Thanks for any advice you can give. A slow database can have lots of reasons, Josh. I take it that the 'large dental office' is amongst the largest companies that use the application in question, either in terms of database size or number of simultaneous users? Your observation that things slow down gradually, indicates that one possibility is that the developer hasn't thought thoroughly enough about transactions (a vital part of Firebird). Try running gstat when the database is slow, maybe you will see a large gap between oldest and next transactions. HTH, Set
Re: [firebird-support] Cache Performance Options
Karol, I have set my page buffers at 130,000 because I cannot go any higher. Still, the firebird process won't use more than 600MB of memory and I assume this is due to 32bit. Would upgrading to 64bit allow me to use more page buffers? Right now, I have my page buffers on that specific database set at 130,000 and my FileSystemCache Threshold set at 9,999,. This means it should always be using file system cache, if I'm reading the conf file correctly. From your recommendations, I should: Upgrade to 64bit version 2.5.3 and increase my page size to 16k If 64bit allows it, should I also increase my page buffers? Also, I assume async write-caching is okay. Is that correct? Finally, is it okay to disable auto sweeping and do it manually twice a day during slow times? Thanks for your advice, Josh On Mon, May 12, 2014 at 11:23 PM, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, Your database is not big but your settings are low. Change your FB server to 64bit and increase your dbbuffer or in fbconfig defaultdbcasche setting and use superserver. Check your database data page size. I always set it to 16KB. Your database utylize memory in this pattern: Dbpage size * dbbuffers Or if dbbuffer is 0 Db page size * defaultdbcachepages You must add to this sort buffer for any connection. You should also download shnapshot of firebird 2.5.3.xxx to fix windows 2008 memory problem Regards, Karol Bieniasxewski Wysłane z mojego HTC - Reply message - Od: Joshua Hartmann jhartm...@computekdental.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: pon., maj 12, 2014 21:32 I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. The server is QEMU/KVM virtual server running Microsoft Server 2008 R2. The host machine is running Ubuntu Server 12.04. Xeon 1275 4 cores allocated to the virtual server 12GB memory allocated to the virtual server (32GB total on the host machine) 750GB Momentus Hybrid SSD/HDD's (RAID 0+1 1.5TB total usable) The database is ~30GB At first, they were running in classic server mode (FB version 2.1.3 x86) on a physical server 2003 machine with 4GB of memory. That machine's HDD's started to fail so we just moved it to the new virtual 2008 server. We let the software company do the install and they set it to classic mode (version 2.1.3 x86) but left all options at default. We noticed the server would start pretty fast but then throughout the day slow down. When checking the system's resources, I noticed it was maxing out on RAM and moving to the page file which explained the slowness. However, task manager did not display that memory was actually being used by the fbserver.exe processes. I asked the developer to switch it to superserver because I read its better for shared cache and since all the clients are accessing the same data (appointment lists, patient lists, etc) I thought it would speed things up. It didn't change the memory usage and fbserver.exe still only reported using about 60MB. So I started reading through articles online and found a bunch of optimization and cache settings. I changed the database options for async, set page buffers at 60k, and turned off auto sweeping. I also changed the firebird.conf settings. Specifically, the MaxFileSystemCache. None of it made any difference. Next, I upgraded to 2.5.2 (still x86) and its made a little difference. I still notice the fbserver using quite a bit of disk IO though and its still not very fast. I guess my questions are: In my situation, should I be using all file system cache or firebird cache? Essentially, what should I set my page buffers and FileSystemCacheThreshole to? I've already set my FileSystemCache to 90%. Should I enable or disable async? Should I enable auto sweeping or not? Would performance be faster if we upgraded to the 64bit version of 2.5.2? Thanks for any advice you can give. -Josh
Re: [firebird-support] Cache Performance Options
That is exactly what I'm doing right now. Its the only way to keep them going. I shut them down at lunch, run a sweep, then when they get back in, things are much faster. -Josh On Tue, May 13, 2014 at 8:35 AM, 'Fabiano - Desenvolvimento SCI' fabi...@sci10.com.br [firebird-support] firebird-support@yahoogroups.comwrote: Large gap is not caused by lack of auto sweep. It is bad system design. As you is not the system programmer, you can shut down all connections to the database during lunch time, then you can let users enter in your system again. After shutting down, run a manual sweep (gfix –sweep). If you see a increase in performance, your system is bad transaction designed. *De:* firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] *Enviada em:* terça-feira, 13 de maio de 2014 11:31 *Para:* firebird-support@yahoogroups.com *Assunto:* Re: [firebird-support] Cache Performance Options Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. -Josh On Tue, May 13, 2014 at 12:18 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. We noticed the server would start pretty fast but then throughout the day slow down. Thanks for any advice you can give. A slow database can have lots of reasons, Josh. I take it that the 'large dental office' is amongst the largest companies that use the application in question, either in terms of database size or number of simultaneous users? Your observation that things slow down gradually, indicates that one possibility is that the developer hasn't thought thoroughly enough about transactions (a vital part of Firebird). Try running gstat when the database is slow, maybe you will see a large gap between oldest and next transactions. HTH, Set
RE: [firebird-support] Cache Performance Options
Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. Please provide the gstat header details for the database. Also you should also have a look at the SysInternals RAMMap tool, it provides insight in the application and system process which are consuming memory. This includes the OS file cache, which is the source of many problems with x64 version of Windows, and the reason why the amount of page cache activity is so high. Sean
RE: [firebird-support] Cache Performance Options
Database header page information: Flags 0 Checksum12345 Generation 715138 Page size 4096 ODS version 11.2 Oldest transaction 676463 Oldest active 676464 Oldest snapshot 662552 Next transaction715100 Bumped transaction 1 Sequence number 0 Next attachment ID 3104 Implementation ID 16 Shadow count0 Page buffers13 Next header page0 Database dialect3 Creation date May 9, 2014 21:03:42 Attributes Variable header data: Sweep interval: 0 *END* This is after I did a manual sweep two hours ago. -Josh *Josh Hartmann* Senior Engineer COMPUtek Dental Systems P: 970-224-4022 F: 970-224-4001 *From:* firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] *Sent:* Tue., May. 13, 2014 3:39 PM *To:* firebird-support@yahoogroups.com *Subject:* RE: [firebird-support] Cache Performance Options Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. Please provide the gstat header details for the database. Also you should also have a look at the SysInternals RAMMap tool, it provides insight in the application and system process which are consuming memory. This includes the OS file cache, which is the source of many problems with x64 version of Windows, and the reason why the amount of page cache activity is so high. Sean [Non-text portions of this message have been removed]
