Hi,
I test general impact of compression and I found that for FB 2.5 and FB3
is critical to take another DB page from cache.
Also any cache miss significantly reduce performance.
With current compression and if we have a number lest say 0x3337 stored
in three columns.
BIGINT it will be uncompressed: 3x ( 6x 0x0, 0x33, 0x37). Total: 24 bytes.
BIGINT compressed: 3x ( 2 bytes to compress zeroes + 3 bytes for
uncompressable data). Total: 15 bytes.
SMALLINT uncompressed: 3x (0x33, 0x37). Total: 6 bytes.
SMALLINT compressed: 1 byte for uncompressable header + 3x (0x33, 0x37).
Total: 7 bytes.
The performance gain can vary from few percent to ten times and it is
NON LINEAR.
Conclusion of my testings:
1. Size of compressed row has significant impact on performance
2. SMALLINT will litle help but
3. VARCHAR and CHARACTER SET has much more impact. Mainly UTF8.
4. If performance is not at first place, use INTEGER because upgrade is
very problematic (you have to drop all PK, FK and indexes)
Some results from our testing that's focused to new RLE:
http://www.elektlabs.cz/fbrle/
Slavek
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:[email protected]
http://www.elektlabs.cz
On 30.6.2017 20:49, Ann Harrison [email protected]
[firebird-support] wrote:
On Fri, Jun 30, 2017 at 10:30 AM, Slavomir Skopalik
[email protected] <mailto:[email protected]>
[firebird-support] <[email protected]
<mailto:[email protected]>> wrote:
advantage of SmallInt vs INT vs BIGINT is data density (keep
record shortest as possible).
If you have one column, it is irrelevant, if you have ten columns,
you will see speed improvement.
Did you test that, or just assume it? Remember that data compression
also affects
record size.
Cheers,
Ann
