Benchmarking InfiniSQL

About InfiniSQL

InfiniSQL is a NewSQL-style relational database created from the ground up. It is open source and has a home at GitHub. The motivation for InfiniSQL is to provide the most robust and scalable transaction data collection and analysis platform in existence. This platform is designed to surpass the capabilities of everything from the latest and greatest of No and NewSQL to the legacy big Mainframe and UNIX platforms, and all offerings in between.

C++ is the primary language used to create InfiniSQL, and it currently runs on Linux x86_64. It is in furious development, and at a relatively early stage. Being alpha software it is not yet suitable for production use. But it is primed for involvement from early adopters, hackers, and anybody else who wants to help bring out the potential of this project.

InfiniSQL is especially designed to excel at performing complex transactions which contain records on multiple discrete servers. This is a very difficult workload to make scale--and InfiniSQL, in it's yet early state--is head and shoulders better than anything else.

How it Works

InfiniSQL is a relational database which currently supports a sub-set of SQL-92. All data is stored in memory. All table and index data is spread evenly across any number of server nodes. Clients connect to any node and are able to access data from all nodes: it appears as a single database. No external sharding is required to get InfiniSQL to scale. For data manipulation, it supports the properties Atomicity, Consistency, and Isolation. These are the first three properties of ACID. Since InfiniSQL data is currently stored only in memory, and has no redundancy capabilities as yet, it lacks Durability. This is only a temporary condition, as Durability will be a feature added in the near future. But it means that, technically, InfiniSQL is not ACID-capable in its current state.

InfiniSQL understands the PostgreSQL Frontend/Backend Protocol. This means that any client that can communicate with a PostgreSQL database should be able to communicate with InfiniSQL. The PostgreSQL team has no involvement in InfiniSQL. These are completely independent projects, but InfiniSQL gratefully makes use of their high quality, mature cross-platform drivers. InfiniSQL supports ad hoc SQL queries in addition to stored procedures. Stored procedures are recommended for performance reasons, but the same SQL capabilities are available through both mechanisms. As InfiniSQL development progresses, so will its SQL support.

More thorough documentation is available online.

Benchmarking

This benchmark effort measured InfiniSQL against its main objectives based on currently available capabilities. These objectives are to scale gracefully across multiple commodity systems for transactions involving records on multiple hosts. This workload is perhaps the hardest for distributed databases to make scale.

Environment

InfiniSQL version v0.1.1-alpha was used for the results reported herein.  The repository also contains the scripts and patches used for testing and reporting. Documentation for repeating the benchmark is included in the user guide. As of this writing, v0.1.2-alpha is the latest non-HEAD release, and the documentation has been written assuming that version is used. There are no performance differences between the two releases, so the same results can be achieved using either.

Hardware consisted of 24 servers in Internap's Bare Metal Cloud environment, with the following specifications: 

• 12 each of client machines and server machines
• Centos 6.4 x86_64 Linux OS
• 1 x Intel Xeon E31230 CPU, 4 cores, 3.30GHz, hyper-threading enabled
• 8GB RAM, DDR3 1600MHz, in 2x4GB modules
• 1TB SATA HDD
• 2 x Intel 82579LM Gigabit Ethernet interfaces

Each machine was located in the same physical data center facility. Each host's corresponding NIC was on a VLAN shared with the rest of the systems: there were no routing hops between them, and each host had two paths to all of the others. Client-server communication was over one VLAN, and intra-cluster communication used the other. This gave each type of traffic 1Gbps full duplex capacity.

Workload

The workload was based upon (but not identical to) that used by the PostgreSQL benchmarking tool pgbench.

The InfiniSQL benchmark database had the following schema definition:

table "pgbench_accounts"
	column "aid", 64bit integer, unique index not null
	column "bid", 64bit integer
	column "abalance", 64bit integer
	column "filler", char[72]

table "pgbench_branches"
	column "bid", 64bit integer, unique index not null
	column "bbalance", 64bit integer
	column "filler", char[80]

table "pgbench_tellers"
	column "tid", 64bit integer, unique index not null
	column "bid", 64bit integer,
	column "tbalance", 64bit integer
	column "filler", char[72]

Each transaction consisted of the following statements executed against the schema. Yes, these are really transactions, the statements all occur after a transaction object is started, and before a commit (or rollback) is issued:

UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

The pgbench tool replaces each parameter, :delta, :aid, :tid, and :bid, with random integer values.

This test is derived from the default test used by the pgbench utility, which in turn,  is derived from TPC-B. The differences between InfiniSQL's benchmark test and that of pgbench, are as follows:

• InfiniSQL benchmark used a stored procedure containing all SQL statements in the transaction, whereas pgbench by default sends each statement individually within the transaction. The difference is mainly a bunch of network round-trips saved using the stored procedure.
• InfiniSQL benchmark did not include an insert to a log table, which pgbench uses by default. This is because such a high rate of inserts puts pressure on system memory such that test runs in this particular environment couldn't last very long. This still included 3 updates and 1 select. 
• In the default pgbench benchmark, the ratio of branches to tellers to accounts is 1:10:10000. In the InfiniSQL benchmark, the ratio of all 3 was 1:1:1, with 1,000,000 records in each corresponding table. In each test, the values for each type is randomized from 1 to MAX. InfiniSQL currently rolls back transactions that try to lock a record which is already locked. This is a short-coming which will be resolved soon, but for the time being, that ratio of record types would result in a very large number of rejected transactions for InfiniSQL. Keeping in mind that the benchmark goal is to measure transaction which involve records on multiple nodes, this modification still accomplishes the objective.

Method

For each transaction, a random value between 1 and 1,000,000 is selected for each of the values types bid, tid and aid. InfiniSQL distributes each record in those tables evenly across nodes based on hash partitioning. This means that each transaction includes 3 updates and 1 select that very likely occur on 3 distinct servers in the cluster. This is a brutal workload for distributed databases.

For each test, traffic was distributed evenly across clients and server systems. The benchmark harness launched one or more pgbench processes on each client system. Each pgbench process connected to a single server node with 200 TCP connections. Each subsequent process connected to a different node in round-robin fashion. Each pgbench process was configured to run the above-described transaction continuously on each of its connections for 180 seconds and to report the per-second rate at the end of each run. This process is entirely repeatable, and the scripts and steps necessary to do so are described in the user guide.

Results

One fact which bears emphasis is that the upper limit of InfiniSQL scalability was not reached during this benchmark test. Only 24 systems were put to use. This limitation had to do with budget constraints and not because of a limit in InfiniSQL, which currently is a self-funded open source project. The server quantity was sufficient to prove InfiniSQL's ability to scale, but not enough to find its upper limits. The results described here reflect the best that InfiniSQL could do based on available hardware, and are not practical limits of the software. More servers undoubtedly would give higher numbers, in other words.

100,800 Connections, Over 500,000 Transactions Per Second

The first test result to share is the maximum number of connections reached and throughput with 12 server nodes. This was the maximum tested value based on the existing hardware. Further testing could have continued, but I stopped at 100,800 connections. Those were generated by 504 pgbench processes each with 200 connections. Mean throughput for the duration of this test was 528,700 transactions per second. Peak second throughput was 542,000. The number of concurrent connections very likely could have gone much higher--but I moved on to testing other things at that point. This is a clear demonstration that InfiniSQL supports massive concurrency.

Maximum Throughput As Nodes Increase

Another test discovered the maximum throughput as server nodes increased. Connection levels were modulated until optimal throughput was achieved. 

Server Nodes	Median Throughput/s	Max Second Throughput
1	84,400	89,500
2	109,400	116,600
4	203,700	208,100
6	292,500	298,900
8	370,600	383,400
10	450,900	468,000
12	534,750	547,900

View fullsize

Median sustained (blue line) and max (red line) throughput by node quantity.

A couple of things are clear: 

1. InfiniSQL scalability as nodes increase is near linear
2. Performance is very stable--median and max throughput values are very close

Maximum median throughput per second was 534,750, and the peak second was 547,900.

Connection and Throughput Stability

How stable is InfiniSQL as connections are added? 

View fullsize

12 servers. Connections by throughput. 

Clients	Median Throughput/s
2400	316100
4800	375500
7200	412100
9600	446400
12000	468900
14400	470700
16800	489550
19200	495900
21600	501100
24000	495900
26400	505200
28800	504450
31200	502500
33600	513700
36000	508200
38400	500900
40800	520150
43200	516700
45600	519900
48000	526200
55200	528500
57600	534750
60000	528600
62400	531400
64800	529500
67200	527100
72000	528050
74400	524600
96000	531600
100800	528700

InfiniSQL has amazing stability as connections are added. At 100,800, I gave up adding more. The point is proven. There is a maximum upper limit somewhere, but finding it is for another test, another time. InfiniSQL capacity became nearly saturated at around 20,000 connections. From that point on, throughput only increased by a maximum of about 7%. The maximum throughput was at 57,600 concurrent connections with 534,750 median transactions per second. Performance stayed very stable from test run to run.

Summary

InfiniSQL lives up to its goals based on its current capabilities. Scalability is near-linear as nodes increase for the most brutal type of workload for distributed databases.  The kind of throughput numbers achieved by InfiniSQL are matched only by other distributed systems which perform very simple workloads in comparison. InfiniSQL performs like a massive key-store while executing complex distributed transactions. While still in early development, InfiniSQL is meeting the goals which have motivated its creation. Don't take my word for it--the source is available, and the test methods are documented and repeatable. Read, download, pound on it, hack it, and get involved!