VoltDB.NET: Optimizing your Connection Settings

written by Seb Coursol on March 15, 2011 with no comments

The .NET connection object comes with a host of settings to help you control access to your VoltDB database and optimize performance. In this quick review, we detail each available option, as well as how to use a connection string for portable deployment of your application between environments.

While there is no specific “Provider” support in .NET for VoltDB, the connection settings have been built following the standard DbConnectionStringBuilder support, allowing you to manage a connection to a VoltDB cluster just the same way you would manage connection to ODBC or standard databases.

Most importantly, you have the ability to separate your environment-specific connection settings from your code, so you do not need to re-compile your application when deploying it to your production environment.

ConnectionSettings and ConnectionStrings

When developing your application, you may want to use a ConnectionSettings instance: strongly-typed property support and Intellisense documentation will make it easier for you to explore the various possibilities.

The ConnectionSettings class can be initialized from a connection string parameter, for instance:

ConnectionSettings settings = new ConnectionSettings("hosts=192.168.1.200;port=21213;txnq=5000;");

Which is equivalent to the following through implicit conversion support:

ConnectionSettings settings = (ConnectionSettings)"hosts=192.168.1.200;port=21213;txnq=5000;");

Or in the case that will interest you during development, you can also do the following, using the parameter-less constructor:

ConnectionSettings settings = new ConnectionSettings()
{
HostList = "192.168.1.200",
Port = 21213,
MaxOutstandingTxns = 5000
};

In this case, you simply list out the properties you want to define differently than the default (non-specified properties will simply use default settings).
Note that the three examples listed above are entirely equivalent.

As you noticed from the code above, properties on the ConnectionSettings class are ‘mirrored’ with specific keywords called ‘synonyms’ that can be interchangeably used in the connection string. For instance, the 4 following connection strings are equivalent:

"HostList=192.168.1.200,192.168.1.203"
"Host List=192.168.1.200,192.168.1.203"
"hosts=192.168.1.200,192.168.1.203"
"servers=192.168.1.200,192.168.1.203"

Thus, you can use the property’s specific name (HostList), display name (Host List – with the space) or any of the valid synonyms (hosts, servers, etc.) to refer to a specific property in your connection string.

Detailed Settings Review

Group: Connection

This group contains settings related to basic/key connection settings.


Name ServiceType
Display Name Service Type
Synonyms type, service
Type ServiceType enumeration: { Database, Export }
Default Database
Whether to connect to the cluster for ‘Database’ services (running procedures and queries) or ‘Export’ services (exporting data from Export tables). At this time, only the ‘Database’ service is supported.

Name HostList
Display Name Host List
Synonyms hosts, servers, server list, cluster, ips
Type string (comma or space separated list of host names or IP addresses)
Default localhost
Single node (or list) to connect to. You may provide either IP addresses or host names.
Note: When providing a host name, multiple IP addresses might be available, either on the same Ethernet interface (or if the server has multiple interfaces). You can control whether to attempt the connection through the “first” (random) IP address available for the host name or all available interfaces through the AllowMultipleHostConnections advanced setting. If you need to be specific about which IP addresses to connect to on multi-interface machines, you should consider each machine to be a different node and list the specific IP addresses you want (instead of host names).

Name Port
Display Name Port
Synonyms portnumber,p
Type int (1..65535)
Default 21212
Port number to which to connect. For administrative mode requests, you must specifically connect to the cluster’s admin port.

Name ConnectionTimeout
Display Name Connect Timeout
Synonyms connection timeout
Type int
Default 5000
Connection timeout in milliseconds. Use -1 (or any negative value) for “infinite”.

Name DefaultCommandTimeout
Display Name Default Command Timeout
Synonyms command timeout
Type int
Default 5000
Default command timeout in milliseconds. Use -1 (or any negative value) for “infinite”. May be overridden at the application level for specific Procedure wrappers or at the call level.

Group: Security

This group contains settings related to authentication details.


Name UserID
Display Name User Id
Synonyms uid, username, user name, user
Type string
Default (empty)
The user name to use for authentication – leave empty if no security has been setup on the cluster.

Name Password
Display Name Password
Synonyms pwd, pass
Type string
Default (empty)
The password to use for authentication – leave empty if no security has been setup on the cluster.

Name PersistSecurityInfo
Display Name Persist Security Info
Synonyms unsecured, unsecured credentials
Type bool
Default false
When set to ‘false’ (more secure), casting the ConnectionSettings object will yield a connection string containing the password in clear. When ‘true’, all settings are copied, except for the password.

Name AllowSystemCalls
Display Name Root Access
Synonyms allow system calls, root, system
Type bool
Default false
When set to ‘true’, the client application will be able to access system procedure calls, such as Snapshot operations, server-side statistics (Table/Invocation/Indexes, etc. – not to be confused with connection statistics), catalog updates, etc. When set to ‘false’, all access to system procedure (otherwise known as “@” procedures) will be denied.

Name AllowAdhocQueries
Display Name Allow Adhoc Queries
Synonyms adhoc
Type bool
Default false
When set to ‘true’, the client application will be able to run ad-hoc queries against the cluster. Because all ad-hoc queries are run as multi-partition transactions and have possible extremely negative performance impact, it is strongly recommended this setting not be turned on in production deployments.

Group: Advanced

This group contains advanced settings such as components flags and performance tuning options.


Name MaxOutstandingTxns
Display Name Maximum Outstanding Trnasactions
Synonyms transaction queue, txnq, maxq
Type int (positive value)
Default 3000
Maximum number of queued transactions before blocking (pausing) execution requests. Depending on your payload, this number can be quite large, or small (the faster your transactions, the higher this number may be). You can avoid performance degradation due to server ‘fire-hosing’ by decreasing this value.Once the number of queued requests has reached the maximum, new requests wait for executions to complete before proceeding. This has no incidence on the command timeout, which can conceivably have negative side effects (requests waiting in the queue longer than their desired timeout), however such issues would be indicative of inadequate performance tuning or capacity planning (the alternative implementation would have possibly a large percentage of request timing out without ever being sent to the server – either case is problematic!).

Name TraceEnabled
Display Name Logging
Synonyms log, trace, tracing
Type bool
Default false
When set to ‘true’, the connection will issue Tracing messages that can be logged to a file, database or syslog target (for instance) through a TraceListener. While this can be useful for debugging purposes, it has an extremely negative impact on performance and should not be used in production deployments.

Name StatisticsEnabled
Display Name Statistics
Synonyms stats, perf, performance
Type bool
Default false
When set to ‘true’, the connection will monitor query execution performance (throughput, latency and network traffic), and provide a framework for periodic reviews by client applications. Impact on performance is minimal and this option is safe for use in production deployments.

Group: Pooling

This group contains advanced settings controlling pooling settings for multi-node connections.

Name AllowMultipleHostConnections
Display Name Allow Multiple Host Connections
Synonyms massconnect
Type bool
Default false
When set to ‘true’, the connection will attempt to connect to every available IP address for each given host name. Can be useful to connect to servers with multiple Ethernet interfaces. When set to ‘false’, the connection will pick the first IPv4 address available (random) for a given host name and ignore all others. This setting only applies to connections made to named hosts: if you need to be specific about which IP address you want to connect to, simply list those specific IP addresses.

Name ConnectToAllOrNone
Display Name Connect to All or None
Synonyms fullconnect
Type bool
Default false
By design, multi-node (“cluster”) connections will internally manage sub-node connections, attempting reconnection in the background when a specific host/IP becomes unavailable. When this setting is set to ‘false’, a cluster connection will hold to that principle and make its best attempt to connect to all provided hosts/IPs, but will proceed forward if at least 1 single connection was successful. When set to ‘true’, the connection will demand that every host be available at connection time before any request can be accepted.

Name LoadBalancingBatchSize
Display Name Load Balancing Batch Size
Synonyms balancing, multiplexing
Type int (positive value)
Default 100
Number of execution requests (queries) to send to each child connection in a round-robin fashion. A large number maximizes network usage on open sockets and can improve performance, however, in case of a transient failure a possibly higher number of request will be lost on the failed connection. Too large a number can cause specific child connections to be fire-hosed or backlogged while other connections are idle. A number between 100 and MaxOutstandingTxns is usually best.

Contextual Immutability

It is important to note that, while you can change the connection settings however many times you want (i.e. the class is not marked “Immutable” – this is a .NET 4.0 attribute anyways), once it is attached to a connection, you will not be able to modify those settings: the connection effectively performs a snapshot copy of the provided settings, so that even if you clung to a ConnectionSettings reference to modify properties after the fact, you would modify your local copy – not the connection’s actual settings.

Thus you can’t start with a connection without System access and decide later that you want that access turned on: you would need a new connection to achieve this (Nothing prevents you however to use your original ConnectionSettings instance as a template from which you would derive a new System-Access-enabled connection, of course!)

Considerations

Adequate tuning of your application is critical to get the most out of the performance VoltDB has to offer and achieve hundreds of thousands, or millions of request per second. We will review next weeks some best practices to ensure your application is designed to make this possible (chief among our tips: designing for asynchronous, massively parallel query processing), but in the meantime, here are a few things to consider, for performance, and peace of mind:

  • Pre-production tuning with Statistics. Use the connection Statistics to tune your application during development and testing. Watch your latency like a hawk, more even than your throughput. You will have your priorities, of course (your specific application might not care if a transaction has an average latency of 500ms), but if you see a specific transaction that usually takes 12ms on a quiet system suddenly run in 500ms while your throughput is maxed out, you are experiencing fire-hosing: it is time for you to review your partitioning strategy and server deployment.
  • In-Production monitoring. Statitics tracking, unlike tracing is a very lightweight process and turning this on in production isn’t necessarily a bad idea. While it isn’t as practical as having Windows Performance counters (those are in the plans, and certainly if one of you want to work on it, there is very little work to do to support it!), you can still log your stats to a file or other such system for review of your historical performance, getting valuable insight over time as to your cluster’s performance, from the perspective of the client applications connecting to it.
  • Watch your timeout. Watch out for demanding command timeout settings: the timeout you pick is purely a client-side setting that allows you to ensure that a given client will wait no longer than a given number of milliseconds for a response (with the provision regarding maxed out system – see comments on the MaxOutstandingTxns setting). But because this timeout does not travel to the server-side, hitting your limit is equivalent to performing a “fire & forgetafter [x ms]” request on the database, except ‘forget’ isn’t something the cluster will do: only your client application.There is nothing wrong with that, except you absolutely have to design your stored procedures and entire application framework to deal with that fact: a request that the client “forgets” about will still run on the server, and modify data inside the database. Think about a simple counter increment call that times out: you will return to the client instead of freezing him, but will not want to re-run the increment transaction, otherwise you would double-count that request!
    The default timeout is set such that it will rarely ever trigger except for really poorly tuned applications and under-capacity deployments.
  • Avoid greediness! Watching a throughput counter go through the roof is quite exhilarating, and you’ll get even more chances to get there when you boost MaxOutstandingTxns beyond the default value. This can yield amazing results for a “perfect storm” scenario like the Voter benchmark application where – even though each request wraps 3 SQL statements – processing is minimal and extremely fast: even a workstation will have no problem clocking at over 100,000 request per second (that’s 300K SQL statements per second, yes, it is that good)!
    But it might not be the case for your application, and there lies the trap – two traps actually:

    1. If you fire-hose the cluster, you will start seeing your latency go through the roof too, while you gain little, if anything in throughput. If you must increase your throughput – and know your procedures are perfectly tuned-up – you will want to revise your partitioning strategy and/or deploy more servers
    2. You can reach levels of fire-hosing where the cluster will start believing a rogue client is bombarding it with requests but not pulling out the responses back (or fast enough anyways). The library’s architecture is such that even if you flood the connection with request the background response processing will still run continuously, however, if you push abuse too far, you will find yourself banned as the server disconnect you!
  • Use Cluster connections. Performance-wise, this gives you virtually nothing in traditional deployments where K-safety will mean that for every transaction multiple servers will perform a specific partitioned request and thus communicate between each other: your ultimate bottleneck is the network bandwidth between those servers. But passing on that, using a cluster connection will give you something better: resilience!If you connect to a single node, losing this node on the cluster would also mean your client gets entirely disconnected (and if you hard-coded the node IP in your settings, it probably also means you’ll have to manually scramble to resolve the situation and connect your client to a different node in the cluster that is still alive).If instead you connect with a list of IPs, the connection will internally manage node failures and reconnect in the background while your application keeps running undisturbed! Any request launched on a failed connection will return back to you with a failure message so you can properly deal with the end-user, but other requests will be sent to whatever connection is still alive, totally oblivious to the fact the connection is “in recovery”.In terms of interface, while cluster connections offer a couple additional ancillary methods that you would rarely ever use but for debugging, they are otherwise absolutely identical to single-node connections, making deployment to cluster or single-node connectivity entirely transparent.
  • Use Connectionstrings! Don’t use ConnectionSettings, period! They might be enjoyable with Intellisense, but the last thing you want to do is have to re-compile and re-deploy your application just to change your settings to accommodate for tuning results or hardware/network changes. You can stuff your connection string into your app.config’s ConnectionString section (or anywhere else you like, for that matter), and re-configuring your application will become a 2-second operation. Your IT staff will love you for it! If you are unfamiliar with application configuration, check out MSDN here.

What’s New & Next?

Our thanks to Michael Giagnocavo for his help getting support for the DECIMAL data type completed – the last true “loose end” we had in the library, though many improvements can still be added, I’m sure. Make sure you download the updated version (links below).

For our next article we’ll review key principles on optimal asynchronous designs to maximize your VoltDB.NET application performance, with a sample on how to use VoltDB to back a session store behind an ASP.NET MVC website – a sample you can take away and modify to develop your own website or mobile application web-service – stay tuned!

Where to download?

Get the full source code through SVN at:

http://svnmirror.voltdb.com/clientapi/csharp/.

Or get the latest build with .CHM documentation and Intellisense support .XML documentation from the downloads page:

http://voltdb.com/community/downloads.php

Seb Coursol
Sr. Technical Consultant
VoltDB