[SOLVED] What are reasons of using Super server mode by default?

[gsbelarus]

UPDATED after changes were made by Vlad:

Mode	Page size	Buffers	PW	switches	Time	Note
Classic	32768	500	16		2:08:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Super	8192	5_000_000	16		2:24:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M

I have conducted a test:

1. WI-V5.0.1.1375 Firebird 5.0 65d5aa3
2. OS Windows Server 2019
3. Database size 334 GB
4. BK file size 119 GB
5. Server with 128 GB RAM, 20 cores CPU and fast NVMe drive

firebird.conf

Until overridden by the values specified in the table below, the following settings were applied to the firebird.conf.:

MaxParallelWorkers = 64
TempBlockSize = 1M
TempCacheLimit = 16384M
MaxIdentifierByteLength = 31
DataTypeCompatibility = 2.5
WireCrypt = Disabled
RemoteServicePort = 3056
IpcName = FIREBIRD5
AuthServer = Legacy_Auth
AuthClient = Legacy_Auth
UserManager = Legacy_UserManager

Command

C:\Program Files\FB5>gbak -r "G:\XXXXXXXX.bk" "G:\XXXXXXXX.fdb" -user sysdba -pas XXXXXXXX -se localhost/3056:service_mgr -p NNNN -bu MMMM -par VVVV

Results

Mode	Page size	Buffers	PW	switches	Time	Note
Classic	32768	500	16		2:07:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Classic	16384	500	18		2:13:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Classic	8192	500	16		2:20:00
Classic	8192	500	16		2:22:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Super	8192	500	16		5:20:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Super	8192	5_000_000	16		5:30:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Super	8192	4_000_000	1	-par 16	5:45:00
Super	16384	100_000	1	-par 16	5:54:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Super	8192	500	16		6:00:00
Super	8192	5_000_000	1	-par 16	6:03:00	FileSystemCacheThreshold = 100M, TempCacheLimit = 32000M
Super	16384	100_000	1	-par 16	6:20:00

• PW -- Parallel Workers parameter in the firebird.conf

Conclusion

The Super Server is not only less robust and reliable than the Classic Server, but it also performs much worse. So, why do we offer it as the default mode? This greatly harms Firebird because many users do not bother to make adjustments in the Firebird configuration file, and therefore end up with a worse experience.

[alexeykovyazin]

Hello Andrei,

First of all, you did not conducted a test: you just create useless ticket which distracts attention of Firebird developers from the real problems.

In your ticket, there is lack of very important details to reproduce a test:

1. Full Firebird Configuration files used in testing, including full Firebird version
2. Full restore log with enabled statistics (switches -ST TDRW -y logname.txt)
3. Details of Operating System used
4. Details of structure of database tested - output of gstat -r ... (it would be good also to have some justification why 8K page size is used, which is totally inappropriate for this database size).
5. Complete test plan - the selection of page buffers = 500 for SuperServer is absolutely inappropriate. There are other very important parameters - enable/disable file cache of operating system? TempCacheLimit size?
6. Optionally, it would be good to see results of simple INSERT?UPDATE/DELETE test https://ib-aid.com/en/simple-insert-update-delete-test-for-firebird/ with a lot of test results to compare.

If you are now aware about principles how to calculate the configuration, please use Configuration Calculator For Firebird to create optimized configurations for the specified hardware, for Super and Classic/SuperClassic.

If you really want to bring the value to Firebird development, please repeat the tests with optimized configurations, supply all requested information, and submit it for investigation. I will bet a bottle of best zubrowka that SuperServer will win.

Regards,
Alexey Kovyazin
President Of Firebird Foundation

[sim1984]

First, you should familiarize yourself with how the ParallelWorkers parameter works in SuperServer and in Classic. You know that it is per-process, right?

For the purity of the experiment, it is better to return this parameter to the default value of 1. And compare using the -par switch. Well, they also told you about the page cache, it must be meaningfully selected taking into account other parameters, and not just tried two extreme values.

[mrotteveel]

I have converted this to a discussion, because as Alexey points out, this wasn't really an issue/ticket.

[gsbelarus]

First, you should familiarize yourself with how the ParallelWorkers parameter works in SuperServer and in Classic. You know that it is per-process, right?

For the purity of the experiment, it is better to return this parameter to the default value of 1. And compare using the -par switch. Well, they also told you about the page cache, it must be meaningfully selected taking into account other parameters, and not just tried two extreme values.

What buffers number isn't "extreme" in this case? 100_000? 10_000? I thought the reason to use SuperServer is to have buffer large enough to fit all frequent data.

We tried 16K, 32K pages as soon as they were introduced and never had a positive effect on our real life cases.

But, any way, I will try it right now.

So,

Parallel Workers = 1
-par 16
-page_size 16384
-buffers 100_000

Let's see how it will behave.

[hvlad]

Set TempCacheLimit to 1G at least (for SS only!).
And use verbose log with stats (point 2 from Alexey mesage)
Also, consider to use or not file system caching (currenlty you going to not use it)

[aafemt]

If database restore is a main task for your server then Classic may be a right choice but usually Firebird is employed for much wider variety of operations.

[gsbelarus]

I see. I explain why backup/restore is a crucial point for us. The database is used with large enterprise, where we are allowed only 2-3 maintenance windows per year of 8-12 hours each. Every hour of unexpected downtime could cost USD 100K+. It is obvious that management is very concerned about how fast operability could be restored in case of unexpected events.

We have fbackup every two hours on a separate server. But, we are considering every scenario and in our history there were cases when database got corrupted and we needed to gfix it and then do full backup restore.

Once it was because of a bug in FB 3.0 SuperServer and we never tried and wouldn't ever try SuperServer again, because we had great problems that time. When there are up to 400 simultaneous connections it is hard task to say 400 workers that they need to reenter some data or recheck their whole daily work.

PS: BTW, 2:20 for the database restoring time we get with Classic looks great! Now it takes around 8 hours with FB 3.0.9.

[aafemt]

For such critical database you should consider hot standby instead.

[gsbelarus]

agree, but we are on FB 3 and replication was introduced in FB 4? Am I right?

[aafemt]

No. Replication is available in HQBird staring from 2.5 IIRC.

[alexeykovyazin]

Just to clarify, downtime cost is 1000000 USD/hour, but you cannot invest neither in professional tools nor into support?

[gsbelarus]

@hvlad I have turned on file system cache for the SuperServer, but result is the same.

I used switch -st, but there aren't statistics in the output file. There is a content:

gbak: WARNING:function BIN_AND is not defined
gbak: WARNING:    module name or entrypoint could not be found
gbak: WARNING:    function BIN_OR is not defined
gbak: WARNING:    module name or entrypoint could not be found
gbak: WARNING:    function ABS is not defined
gbak: WARNING:    module name or entrypoint could not be found
gbak: WARNING:function ABS is not defined
gbak: WARNING:    module name or entrypoint could not be found
gbak: WARNING:    function BIN_AND is not defined
gbak: WARNING:    module name or entrypoint could not be found

is -st supposed to work with -se?

The whole command line is here:

gbak -r "G:\XXXXXX.bk" "G:\XXXXXX.fdb" -user sysdba -pas XXXXXXX -se localhost/3056:service_mgr -p 16384 -bu 100000 -par 16 -ST TDRW -y "G:\XXXXXXXX.txt"

[hvlad]

Statistics is a part of verbose output, you missed the -verbose switch.
Also, to make any conclusion we need to compare verbose outputs of 'fast' and 'slow' cases, i.e. stats of just one of them makes no much meanings.

[gsbelarus]

ok. I will try to recollect the data during the weekend.

[aafemt]

And while recollecting data perform restore in Super mode first and in Classic mode then. To eliminate possibility that your SSD is busy with housekeeping.

[sim1984]

Max parallel workers = 64

Seriously? Is this parameter written exactly like that?
Maybe after all

MaxParallelWorkers = 64

???

[gsbelarus]

[gsbelarus]

Above I put an excerpt in a readable format. Of course, every parameter named in a proper way in the firebird.conf file.

[sim1984]

I don't really believe it. Because my measurements do not correlate with yours in any way. My multi-threaded SS restores the database at about the same speed as the classic one.

[alexeykovyazin]

Hello All,

I would recommend for everybody stop discussing until the all requested information will be provided, in order to have a productive discussion.

Regards,
Alexey

[gsbelarus]

I'm unclear on why it's necessary to limit the number of Parallel Workers to 1 when utilizing the Super Server. This seems counterintuitive and warrants clarification within the firebird.conf settings.

[hvlad]

It is not required

[gsbelarus]

So, I'm going to do two tests with a verbose output.

Classic
page 8192
buffers 500
parallel workers 16

Super
page 8192
buffers 200_000
parallel workers 1, switch -par 16 in the gbak's command line

All other parameters are equal. File system cache is enabled. Temp cache limit is set to 32GB.

Any objections?

[aafemt]

As I said: test Super first and Classic then. Number of buffers also must be the same for proper testing.

[hvlad]

Use the same value of ParallelWorkers and the same command line for each run.
No need to add more confusion that we already have :)
As for file system cache - set UseFileSystemCache = true and leave FileSystemCacheThreshold commented out.

[hvlad]

Number of buffers also must be the same for proper testing.

?????

[aafemt]

Do these parallel attachments use separate data caches in Classic? I had impression that in the case of internal attachments (not only restore, but sweep, GC etc) a single classic process works as "mini-super" sharing the cache between these attachments and not spawn separate classic processes for them.

[mrotteveel]

@aafemt You're missing that gbak itself creates multiple attachments when restoring data, which means in the case of Classic, each attachment has its own cache. Only when the indexes are built is one attachment used which then uses multiple threads for rebuilding.

[aafemt]

@mrotteveel Are you sure that it is done during restore and not backup only?

[mrotteveel]

Yes, unless https://github.com/FirebirdSQL/firebird/blob/v5.0-release/doc/README.gbak is wrong

[hvlad]

@aafemt you have wrong impressions.

[gsbelarus]

Now, I get confused. So, I'm going to set these values in firebird.conf:

UseFileSystemCache = true
#FileSystemCacheThreshold = 100M
ParallelWorkers = 16
MaxParallelWorkers = 64

and use the same command line for SuperServer and Classic testing.

I'll use page 8192 (As I have tested above there is no significant difference between 8192 and 16384, so I stick with what we use for last 25 years).

I'll use 500 buffers.

For both runs, I'll grab the stats.

[mrotteveel]

I could set even more, but default threshold is 64K and this would turn off file system caching.

If UseFileSystemCache is set to true explicitly, then FileSystemCacheThreshold is ignored (the docs are unclear about the effects if neither is set explicitly, but I think that UseFileSystemCache is inferred to true, and FileSystemCacheThreshold is then ignored).

[gsbelarus]

ok. I will use both UseFileSystemCache=true and FileSystemCacheThreshold=100M it should enable file system cache in every possible scenario.

[hvlad]

If you use 500 buffers for CS, then you may think about 500*par buffers for SS for more-or-less fair comparison.
Actually, restore doesn't require too big buffer cache if file system cache is used.

[hvlad]

@gsbelarus you could also consider the moon phase...

[gsbelarus]

well, I'll do three runs

Classic 500 pages
Super 500 pages
Super 50_000 pages

[reevespaul]

On Fri, 05 Apr 2024 04:43:24 -0700 Mark Rotteveel ***@***.***> wrote: > I could set even more, but default threshold is 64K and this would turn > off file system caching. If `UseFileSystemCache` is set to `true` explicitly, then `FileSystemCacheThreshold` is ignored (the docs are unclear about the effects if neither is set explicitly, but I think that `UseFileSystemCache` is inferred to true, and `FileSystemCacheThreshold` is then ignored).

No - it is the other way round. If UseFileSystemCache is commented and DBCachePages exceeds FileSystemCacheThreshold then the file system cache is disabled. This goes against the expectation that the uncommented value is the default. Paul -- Paul Reeves https://www.ibphoenix.com Supporting users of Firebird

[hvlad]

Firebird 5.0.1.1738 x64
Windows 10
RAM 32GB, CPU i7-12700
SSD Samsung 980 Pro (NVMe)
Backup size 60GB
Database size 87GB

firebird.conf
UseFileSystemCache = true
TempCacheLimit = 4G
MaxParallelWorkers = 64

ServerMode = Super
gbak -se inet://service_mgr -rep xxx.fbk xxx.FDB -verbint 1000000 -st tdrw -y xxx.log -bu 100000 -par 16
769 sec

ServerMode = Classic
gbak -se inet://service_mgr -rep xxx.fbk xxx.FDB -verbint 1000000 -st tdrw -y xxx.log -bu 1000 -par 16
876 sec

[hvlad]

It is the biggest backup I have at hand

[gsbelarus]

So, I did five additional runs on the same machine. Before every run, I had the service restarted. The firebird.conf looks like:

	Line  190: UdfAccess = Restrict UDF
	Line  286: UseFileSystemCache = true
	Line  313: FileSystemCacheThreshold = 100M
	Line  405: TempBlockSize = 1M
	Line  419: TempCacheLimit = 32000M
	Line  433: MaxIdentifierByteLength = 31
	Line  489: AuthServer = Legacy_Auth
	Line  494: AuthClient = Legacy_Auth #Windows clients
	Line  508: UserManager = Legacy_UserManager
	Line  743: DataTypeCompatibility = 2.5
	Line  771: WireCrypt = Disabled
	Line  844: RemoteServicePort = 3056
	Line 1073: MaxParallelWorkers = 64
	Line 1085: ParallelWorkers = 16
	Line 1136: IpcName = FIREBIRD5

All other parameters are commented, i.e., have default values.

I have put results in the table at the beginning of the thread. Duplicating them here:

Classic, 500 pages, 32768 page size: 2:07:00
Classic, 500 pages, 8192 page size: 2:22:00
Super, 500 pages, 8192 page size, 5:20:00
Super, 5_000_000 pages, 8192 page size, 5:30:00
Super, 5_000_000 pages. 8192 page size, PW = 1, -par 16, 6:03:00

The statistics per run:

classic_500.txt
classic_500_page_32768.txt
super_500.txt
super_5000000.txt
super_5000000_pw_1_par_16.txt

[hvlad]

Thanks, it allows me to identify and fix most significant source of contention in SS that happens when FK is created.
The fix is committed into master now. Some quick results for the one of the "slowest" index:

alter index RDB$FOREIGN214 active;

	no fix	with fix
CS	166	165
SS	656	179

[hvlad]

Committed into v5 also. Please, try next snapshot build.

[gsbelarus]

Thanks! I'll try it.

[sim1984]

Thanks! I'll try it.

Are there any new test results after the patch?

[gsbelarus]

New results are at the top.