[firebird-support] Performance issues moving to new server with later Firebird version
We have a 127GB Firebird database that was running on Windows Server 2008 x64 with 16GB of RAM and Firebird SuperClassic v2.5.2.26539. The database has a page size of 8192 and buffers of 6144, and ODS version 11.2. Due to time constraints I could not do a backup+restore on the new server, which is Windows 2012 with 32GB RAM and Firebird SuperClassic v2.5.5.26952. However, most activity on this new server with the old database is much slower than the old server. I'm only seeing about 1.5GB Private Bytes in use, jumping occasionally to 2.5GB with a working set of 1.1GB and Virtual Size of 2.7 (as per Process Explorer). The only changes that I have made to the stock Firebird.conf are as follows: DefaultDbCachePages = 384 FileSystemCacheSize = 50 TempBlockSize = 2048576 TempCacheLimit = 567108864 The database is comprised of many images up to 1.5MB in size, but they are not accessed very often. The actual data size outside of these images is probably 1/3 of the total DB size. There are typically about 20 connections to the database servicing up to 140 users, with a mix of read and write transactions, as well as some reporting. The header statistics look like this: Database header page information: Flags 0 Checksum12345 Generation 106862144 Page size 8192 ODS version 11.2 Oldest transaction 103869407 Oldest active 103869408 Oldest snapshot 103869408 Next transaction103870442 Bumped transaction 1 Sequence number 0 Next attachment ID 2991646 Implementation ID 26 Shadow count0 Page buffers6144 Next header page0 Database dialect3 Creation date Feb 2, 2014 22:06:05 Attributes force write Variable header data: Sweep interval: 0 *END* I'm looking for some guidance as to how changes that should be made to the Firebird configuration file and to the database when I do a backup/restore. For instance, I'm sure that I want to jump to 16KB pages, but what about buffers? There are other services that run on this server, so I cannot use all resources for the database. However, I believe it is provisioned well enough that I can certainly use half of the resources - processor and memory - without affecting the application server and web server that are running on the same box. Thank you for your time, Bob M..
Re: [firebird-support] Performance issues moving to new server with later Firebird version
Hi Bob, In general, we recommend to apply the same configuration as at the old server (even if it looks not very good), and compare the performance - if there will be a problem, obviously it will be related with the new hardware/OS. If there will be the same performance, then you can tune firebird.conf. If you don't know exactly how changes will work, change one parameter at a time. Also, you can get optimized configuration file from our web-site http://ib-aid.com/en/optimized-firebird-configuration/ And, look at the hardware guide: http://ib-aid.com/en/articles/firebird-hardware-guide/ Regards, Alexey Kovyazin IBSurgeon HQbird We have a 127GB Firebird database that was running on Windows Server 2008 x64 with 16GB of RAM and Firebird SuperClassic v2.5.2.26539. The database has a page size of 8192 and buffers of 6144, and ODS version 11.2. Due to time constraints I could not do a backup+restore on the new server, which is Windows 2012 with 32GB RAM and Firebird SuperClassic v2.5.5.26952. However, most activity on this new server with the old database is much slower than the old server. I'm only seeing about 1.5GB Private Bytes in use, jumping occasionally to 2.5GB with a working set of 1.1GB and Virtual Size of 2.7 (as per Process Explorer). The only changes that I have made to the stock Firebird.conf are as follows: DefaultDbCachePages = 384 FileSystemCacheSize = 50 TempBlockSize = 2048576 TempCacheLimit = 567108864 The database is comprised of many images up to 1.5MB in size, but they are not accessed very often. The actual data size outside of these images is probably 1/3 of the total DB size. There are typically about 20 connections to the database servicing up to 140 users, with a mix of read and write transactions, as well as some reporting. The header statistics look like this: Database header page information: Flags 0 Checksum12345 Generation 106862144 Page size 8192 ODS version 11.2 Oldest transaction 103869407 Oldest active 103869408 Oldest snapshot 103869408 Next transaction103870442 Bumped transaction 1 Sequence number 0 Next attachment ID 2991646 Implementation ID 26 Shadow count0 Page buffers6144 Next header page0 Database dialect3 Creation date Feb 2, 2014 22:06:05 Attributes force write Variable header data: Sweep interval: 0 *END* I'm looking for some guidance as to how changes that should be made to the Firebird configuration file and to the database when I do a backup/restore. For instance, I'm sure that I want to jump to 16KB pages, but what about buffers? There are other services that run on this server, so I cannot use all resources for the database. However, I believe it is provisioned well enough that I can certainly use half of the resources - processor and memory - without affecting the application server and web server that are running on the same box. Thank you for your time, Bob M..
Re: [firebird-support] Performance issues moving to new server with later Firebird version
Hi, 1. you buffer settings is low. you can simple calc numbers like page_size*buffer_Size*IFTHEN(Architecture=SUPERSERVER, 1, CONNECTIONS_NUMBER) + Temp_Cache_Limit*CONNECTIONS_NUMBER i write from memory then someone can fix some mistakes 2. second what is your CPU usage when you compare this two servers old and new? 3. What is your I/O when you test? 4. How do you checked that new server is slower? Some query slow down - query plan changed ... I'm very big fan of superserver architecture and in your env(I see that you have ~1,4 transaction/s) i do not see any reason to not use it and gain performance bost because of big single cache. But only if you have not cpu intensive work - superserver can use only one core. But to compare two servers you must show us some numbers and config settings like HDD,SSD, RAID0, 1, 5, batery pack.. server kind, system version ... If you have many blobs then 16KB page size is better - if this blobs are mostly bigger than 16KB regards, Karol Bieniaszewski We have a 127GB Firebird database that was running on Windows Server 2008 x64 with 16GB of RAM and Firebird SuperClassic v2.5.2.26539. The database has a page size of 8192 and buffers of 6144, and ODS version 11.2. Due to time constraints I could not do a backup+restore on the new server, which is Windows 2012 with 32GB RAM and Firebird SuperClassic v2.5.5.26952. However, most activity on this new server with the old database is much slower than the old server. I'm only seeing about 1.5GB Private Bytes in use, jumping occasionally to 2.5GB with a working set of 1.1GB and Virtual Size of 2.7 (as per Process Explorer). The only changes that I have made to the stock Firebird.conf are as follows: DefaultDbCachePages = 384 FileSystemCacheSize = 50 TempBlockSiz e = 2048576 TempCacheLimit = 567108864 The database is comprised of many images up to 1.5MB in size, but they are not accessed very often. The actual data size outside of these images is probably 1/3 of the total DB size. There are typically about 20 connections to the database servicing up to 140 users, with a mix of read and write transactions, as well as some reporting. The header statistics look like this: Database header page information: Flags 0 Checksum 12345 &n bsp; Generation 106862144 Page size 8192 ODS version 11.2 Oldest transaction 103869407 Oldest active 103869408 Oldest snapshot 103869408 Next transaction 103870442 Bumped transaction 1 & nbsp; Sequence number 0 Next attachment ID 2991646 Implementation ID 26 Shadow count 0 Page buffers 6144 Next header page 0 Database dialect 3 Creation date Feb 2, 2014 22:06:05 Attributes &nb sp; force write Variable header data: Sweep interval: 0 *END* I'm looking for some guidance as to how changes that should be made to the Firebird configuration file and to the database when I do a backup/restore. For instance, I'm sure that I want to jump to 16KB pages, but what about buffers? There are other services that run on this server, so I cannot use all resources for the database. However, I believe it is provisioned well enough that I can certainly use half of the resources - processor and memory - without affecting the application server and web server that are running on the same box. Thank you for your time, Bob M..