Biblioteca

How to make Firebird work 20% faster at Windows Server 2016 in less than 1 minute

More and more users upgrade to Windows Server 2016 from old versions, and some of them have noticed that Windows Server 2016 works slower than previous versions, even on the same hardware with the same database.

We have made several tests on Windows Server 2016 and Firebird, and found, among other things,  the option, which dramatically influences the performance of the Windows Server 2016: the power plan.

By default, Windows 2016 has «Balanced» power plan, which automatically balances performance with energy consumption on capable hardware. Well, sounds not dangerous, but in fact, Balanced power plan at Windows Server 2016 greatly suppresses CPU performance and decrease the overall performance of the server.

It is possible to switch Power plan on the fly, no need to reboot. See the effect of enabling «High Performance» power plan on the server with Firebird database, which serves 70 connections:

​

Effect of High-Performance Power Plan for CPU utilization

Ok, this can be just a CPU utilization, what about overall performance? We have run several tests, similar to TPCC (OLTP application simulation) and got these numbers:

Power Plan TPC-C Throughput (the higher the better)
Balanced 17029
High Performance 21226

This test is highly disk-bounded, so the difference is only 20%, for CPU-intensive operations the improvement can be even higher.

Also, we have performed the test of restore (with gbak -c) of 144Gb Firebird 2.5 database with different power plans:

Power Plan Restore time (the less the better) 
Balanced 4 hours 7 minutes
High Performance 2 hours 35 minutes

The single «energy-saving» option almost doubled the performance of restore! Why we have such a noticeable performance increase? The answer is in indices: during restore, they intensively use CPU to sort values.

Look at the timestamps values from gbak restore (in seconds) for 5 biggest indices.

With balanced power plan gbak:

gbak: 9529.339 1230.343 5028900 346529     activating and creating deferred index INDMF_TAG_PLC_DATA_DT_D
gbak: 10787.610 1258.270 5028909 233365     activating and creating deferred index INDMF_TAG_PLC_DATA_TAG_D
gbak: 11824.329 1036.718 5028907 216488     activating and creating deferred index INDMF_TAG_PLC_DATA_LU_D
gbak: 12571.012 746.683 5028907 265160     activating and creating deferred index INDMF_TAG_PLC_DATA_C1
gbak: 13304.334 733.321 5028908 359393     activating and creating deferred index PKMNF2_DOC_DOWNTIME
Total time: 3775 seconds

With High-Performance Power Plan

gbak: 5301.785 615.045 5028900 346529 activating and creating deferred index INDMF_TAG_PLC_DATA_DT_D

gbak: 5924.667 622.882 5028909 233365     activating and creating deferred index INDMF_TAG_PLC_DATA_TAG_D
gbak: 6569.815 645.147 5028907 216488     activating and creating deferred index INDMF_TAG_PLC_DATA_LU_D
gbak: 7186.322 616.506 5028907 265160     activating and creating deferred index INDMF_TAG_PLC_DATA_C1
gbak: 7894.340 708.018 5028908 359393     activating and creating deferred index PKMNF2_DOC_DOWNTIME
Total time: 2593 seconds

Summary

So, this simple setting in Control Panel-> Hardware->Power Options can greatly improve Firebird database performance on Windows Server 2016.
Please check it and enable High-Performance Power Plan immediately!

More reading about Firebird database performance:

Special thanks to Oleg Matveev for the help with preparation of this article!