Firebird 3 protocol benchmark

At the 12th Firebird Developers Day, I talked about Using Firebird in high latency networks (aka. internet). Below are two slides from my presentation, where you can see the improvements in Firebird 3 wire protocol, compared to FB 2.5 and to MySQL.

Enjoy!

Obs: Left axis values are expressed in seconds. Test server was hosted in Amazon (USA) and client accessing it was located in Brazil. Ping reported latency of 219ms. The smaller the bar, the better.

fb3_1

Above graph shows the result of fetching 10.000 records from a real table used to store customers data. Red bars represents records with all the fields filled (ie: there was no fields containing nulls) and blue bars represents fetching records where some of the fields were nulls. Tests where done with and without compression.

fb3_2

The same table used in previous graph was created in MySQL InnoDB (same data). Blue bars means that wire compression was disabled, and red has compression enabled. Left side graphs has all fields filled (ie. there wasn’t null fields) and in right side graphs, some records has some null fields. 

As you see, FB 3 won  😉

I should mention that there was no blob fields in the table, and this makes a lot of difference. Fetching non-null blobs makes the fetch slower in Firebird (more roundtrips are needed).

PS: The improvements in the FB 3 wire protocol were sponsored by donations collected in the 9th edition of FDD conference, and were implemented by Dmitry Yemanov. Compression was implemented by Alex Peshkov.

 

Work with Firebird from .NET applications.

Hi everyone!

We wrote the new small guide “Using «LCPI ADO.NET Data Provider for OLE DB» with Firebird SQL Server” and offer you acquainted with technological capabilities of our .NET provider.

From this article you can learn, for example:

  • How to create multiple transactions within one connection
  • How to work with parameters in queries
  • How to execute SQL script

Your comments and suggestions are welcome!

Update of .NET Provider for OLEDB. Support of schemas implemented.

We continue to develop our .NET Provider for OLEDB. In the new build (#1434), we have implemented the methods to obtain database metadata:

  • OleDbConnection.GetOleDbSchemaTable
  • OleDbConnection.GetSchema

GetOleDbSchemaTable

Supported schemas:

  • SchemaGuids
  • DbInfoKeywords
  • DbInfoLiterals
  • All schemas of the OLEDB Provider

Distinctions compared to standard OLEDB.NET Provider:

  • The SchemaGuids schema describes all schemas including SchemaGuids, DbInfoKeywords and DbInfoLiterals.
  • The SchemaGuids schema supports restrictions for the Schema column.

GetSchema

Supported schemas:

  • MetaDataCollections
  • Restrictions
  • DataTypes
  • ReservedWords
  • DataSourceInformation
  • CharacterSets
  • Collations
  • Tables
  • Views
  • Columns
  • Procedures
  • ProcedureParameters
  • ProcedureColumns
  • Indexes

Distinctions compared to standard OLEDB.NET Provider:

  • The MetaDataCollections schema supports restrictions for the column CollectionName.
  • The Restrictions schema supports restrictions for the columns CollectionName and RestrictionName.
  • More accurate population of the DataSourceInformation schema.
  • More accurate population of the Restrictions schema.

Implementation peculiarities

  • Caching of data from its own schemas (SchemaGuids, DbInfoKeywords, DbInfoLiterals, …).
  • Coherence of information from metadata schemas with the descriptions of the resulting rowset columns and command parameters (ensured by IBProvider).
  • Different algorithms of metadata loading for Interbase and Firebird. Including – our provider takes into account the version of the server and the version of database ODS (ensured by IBProvider).
  • Caching of OLEDB metadata schemas is carried out on the IBProvider level (see a property «schema_cache»).

Other changes

  • The OleDbSchemaGuid class added with the lists of known OLEDB schema identifiers.
  • The OleDbSchemaRestriction class added with the lists of restriction indexes for known OLEDB schemas.
  • The OleDbLiteral class added.
  • The OleDbMetaDataCollectionNames class added.
  • The number of tests and debug structures ensuring the correct work of components increased.

New examples

If you are interested in working with stored procedures grouped into PACKAGE, please take a look at the new example for our .NET Provider:

  • Creation and working with PACKAGE (C#, FB3)
  • Firebird on Large Big Iron servers : 512G-2TB of ram 100.000 concurrent users

    Nikolay Samofatov wrote about his Large Iron production systems that he maintains :

    We run Firebird to power larger systems (for 12 government agencies and 3 banks).

    It has approximately 100000 end users multiplexed through 2500 (max) pooled connections.

    Here is the snapshot of nearly idle system at night:

    top - 03:20:39 up 10 days,  8:39,  7 users,  load average: 2.08, 1.87, 2.15
    Tasks: 1732 total,   1 running, 1730 sleeping,   1 stopped,   0 zombie
    Cpu(s): 11.9%us,  4.0%sy,  0.0%ni, 83.5%id,  0.0%wa,  0.0%hi,  0.6%si,  0.0%st
    Mem: 529177288k total, 378587600k used, 150589688k free, 761532k buffers Swap: 1073741816k total, 130612k used, 1073611204k free, 333281232k cached
    
    [root  mvv bin]# ps aux | grep -c rdb_inet_server
    719

    Database is on a small FusionIO drive:

    mount:
    /dev/fiob on /mnt/db type ext2 (rw,noatime)
    df -h:
    /dev/fiob             587G  423G  135G  76% /mnt/db

    Also later he mentions that he uses 2TB of RAM machines

    Chipsets that can handle 2 TB of RAM and 8 CPU sockets (<=80 cores, <=160 threads) are the largest “commodity” type hardware available now. These are the largest systems we worked on. 8 GB of lock manager space per database should be just enough for them. With the allocation error check in place we’ll have this problem solved for the next year or two until larger systems become common.

    Firebird case study from Wobe-systems GmbH

    New Firebird case study from wobe-systems GmbH, German software development house for the graphics industry.
    “…A production database of 100 GB and more containing BLOBs is nothing unusual at our customers sites…”
    “.. Firebird SQL server is at the core of our system helping our customers swift and safely through their daily work. Equipped with near zero administration and ample possibilities of scalability Firebird SQL database offers an operational reliability that does meet the requirements of industrial and time critical applications.”

    Read full article here.

    Case Study: Moscow City Hospital

    Take a look at the new case study published in the FirebirdSQL site. Interesting that they choosed Firebird over Oracle, and cost was not the only factor. Worth reading!

    There are other case studies published in the site. Make sure to read them all, and if you have an interesting case to show, contact the moderator to know how to publish it there.