Commented by: @hvlad

It is not clear what bytes did you measured ? Disk IO ? Network IO ?

Seems you are talk about network IO from the app side. If so, could you try to use fbclient instead of native protocol implementation ?

As for performance - you have all results at hands and could compare, for example, number of runs within 4 minutes.

Commented by: Andrew Dimech (adimech)

The counter counts all I/O activity generated by the process to include file, network and device I/Os.

The Stored Procedures execution time seems more efficient in Engine 2.5 RC2. They run under 2seconds and stay like that through out the test, where as in Engine 2.1.1 the execution time takes long and longer through out the test. It starts from around 2 seconds and steadily increases to around 5 seconds at the end of the 4minute test.

Our concern is that such a high I/O output in Engine 2.5 will impact server performance in general. Hence also effecting other systems on the server.

The I/O involved here is more DISK I/O rather than Network I/O since we are using an embedded version of Firebird. Any reasons why is it so high? and what else can we do?

Thanks

Commented by: @hvlad

So, you comparing FB embedded 2.1 agaist FB embedded 2.5, correct ?
As FB embedded in v2.5 based on CS code it have default page cache size = 75 buffers (it was 2048 in v2.1, based on SS).
I recommend you to raise page cache size and compare numbers again.

Note, it is not recommended to set page cache size to high values for CS, at least in multy-user environment.
So, if you''ll have comparable IO numbers with larger cache size it will be good idea to not use big values in production (typically 75-1024 pages).

Commented by: Andrew Dimech (adimech)

Yes, we're comparing the embedded versions.

Here are the results based on changes in the default page cache size:

DefaultDbCachePages: 1024

IO Read Bytes/Sec 2,714,516
IO Write Bytes/Sec 4,701,461

DefaultDbCachePages: 2048

IO Read Bytes/Sec 2,870,665
IO Write Bytes/Sec 4,894,382

IO performance is better in Reads by around 1,000,000 / sec compared to having the page cache size to 75. However it is still way off compared to Engine 2.1 with just an average of 164,250 / sec

IO performance went worse with these modifications.

Anything else you can suggest? as we cannot consider upgrading with these numbers.

Thanks for your quick responses.

Commented by: @hvlad

Compare execution statistics, it is possible that 2.5 choosed different execution plan

Commented by: Andrew Dimech (adimech)

Execution Plan seems the same.

Have you ever benchmarked the IO read/writes between Engine 2.1 and 2.5? and is there a way to know where is all the IO coming from?

Thanks

Commented by: @hvlad

Sure, we did the tests ;)
Could you compare execution statistics ?
Or show us the query which have such big difference in IO at 2.1 and 2.5

Commented by: Andrew Dimech (adimech)

Added Database Schema

Commented by: @hvlad

What queries should i run to see the issue ?

Commented by: Andrew Dimech (adimech)

I run all of them in on method in this order:

GETMESSAGEUPDATESHDRFA
GETMESSAGEUPDATESADDRFA
GETMESSAGEUPDATESATTACHFA
GETMESSAGEUPDATESOWNERSFA
GETMESSAGEUPDATESTAGSFA
GETDELETEDMESSAGEUPDATESFA
GETTAGUPDATESFA

Their execution times is very fast. We are concerned with the IO they generate.

Commented by: @hvlad

All these procedures could be run without input parameters ?

Commented by: @hvlad

I ask you for a simple thing - provide reproducible example.
How can i said something looking at set of procedures on empty db ?

Commented by: Andrew Dimech (adimech)

Attached is a script which executes the stored procedures mentioned.

How can I send the database with data since its about 50MB?

Thanks once again.

Commented by: @hvlad

> How can I send the database with data since its about 50MB?

Could you pack (zip\rar\7z) it and send it to me directly ? (hvlad at users sf net)
Just cut-off file extension, else SF will not pass it

Or put it for download somewhere. You can send me url privately if you wish.

Commented by: @hvlad

The procedure GETTAGUPDATESFA have a few SORT's in its plan.
Firebird performs sorting first in memory (up to TempCacheLimit setting) and then using temporary files.
In SS TempCacheLimit is 64MB by default.
In CS this setting have default value of 8MB.
As v2.5 embedded is based on CS, it used 8MB for sorting.
Your procedure needs near 18MB for two of sortings and thus used temporary files if run by v2.5 embedded engine.

You can
a) raise TempCacheLimit setting in firebird.conf, or
b) create indices to avoid MERGE JOIN in plan, or
c) do nothing as temporary files usually cached by OS and you will not see big performance degradation (in this case)

Commented by: Andrew Dimech (adimech)

And what is the default TempCacheLimit for Embedded Engine 2.1?

Commented by: @hvlad

64MB of couse.

Because, as i already said, on Windows Embedded was based on SuperServer before v2.5 and is based on ClassicServer stating from v2.5

Commented by: Andrew Dimech (adimech)

Thanks for your help and patience. We confirm that when we increased the TempCacheLimit to 64MB, IO Dropped again.

Do you have any suggestions as to which columns should be indexed?

Commented by: @hvlad

This is support question and better use corresponding list for it.

Anyway, consider this index for GETTAGUPDATESFA procedure :

CREATE INDEX ARC_TAGS_MSG_UPDATES_IDX2 ON ARC_TAGS_MSG_UPDATES (MSGID);

It eliminates almost all SORT's in GETTAGUPDATESFA

Commented by: @hvlad

Nothing to fix