Database Configuration
If you have configured Flux to run against one of the supported databases (see Configuring Flux to run with Databases), you will likely want to configure how Flux is allowed to use your database. The MAX_CONNECTIONS and CONCURRENCY_LEVEL configuration options allow you to control how many connections Flux is allowed to open and the number of workflows that will be able to run at the same time.
MAX_CONNECTIONS specifies the maximum number of database connections that Flux may have open at one time.
The CONCURRENCY_LEVEL determines how many threads of execution may be running simultaneously on the engine. A “thread of execution” can be either an individual workflow or a branch of execution within a workflow — so, for example, a concurrency level of 2 would allow either two workflows to execute, or two branches of execution within a single workflow. In other words, the concurrency level specifies the total number of execution branches that may run at once.
Note that only executing actions or triggers that are actively firing will use a concurrency throttle slot. Triggers that are waiting for a condition will not count against the concurrency throttle until the condition is satisfied and the trigger begins firing.
In normal operation, Flux will require up to five connections of its own for internal processes. In addition, many clients (like Operations Console users or API calls to the engine) will also require their own database connection to be available. If Flux cannot obtain enough database connections for this background work because clients are using all available connections, you will likely encounter problems including job starvation or even engine deadlock.
To avoid this, we recommend setting the max connections by the formula:
<max connections> = <concurrency level> + 5 + <2 * number of expected clients>
So, for example, if you want up to 20 threads of execution to be allowed to run, and you expect that you may see up to 5 client connections at any time on the engine, you would set these options as follows:
CONCURRENCY_LEVEL=20
MAX_CONNECTIONS=35
Many Flux users also avoid this problem by creating two engines in a cluster - one engine for normal execution that does not accept connections (by setting the SERVER configuration option to false), and another engine that is used exclusively for client connections. Clustering the engines will allow you to view the workflows executing on the normal engine while avoiding the danger that a client connection could interfere with normal operation.
By default, the MAX_CONNECTIONS value is 15. The CONCURRENCY_LEVEL is not set by default, which means that only one workflow execution branch can run at once.
Advanced Topic
Some customers construct workflows and error flows that contain Java code that calls back into the engine using Flux’s remote engine API. This is sometimes done to retrieve information about the workflow, or to pause, remove, and reload the workflow (this is not generally recommended as an approach — but it’s been done many times) so that it can start from the beginning. Each call to Flux’s remote engine API requires that a database connection be available in the engine to service that request. In this case, the Flux error action code is actually another client to the Flux engine. In such cases, you may have to double (or more) the number of database connections, based on your workflow design.
Connections Allowed by the Database
When you configure the MAX_CONNECTIONS for a Flux engine, Flux will not actually attempt to verify that it is able to open those connections (and assumes that the database or data source is configured correctly to handle them). This means that you must take care to ensure that the number of connections you have set in the MAX_CONNECTIONS parameter will always be available from the database or data source.
For example, if your engine has a MAX_CONNECTIONS value of 20, the database or data source must be configured to allow at least 20 connections at once. If another application is using the same database or data source, you must be sure that the application will not use connections that Flux expects to have available.
The MAX_CONNECTIONS is configured separately for every engine in the cluster. If you have four engines in a cluster, and each engine has a MAX_CONNECTIONS value of 20, the database or data source must have at least 80 connections available for Flux at all times.
Because Flux opens connections on demand, if the database or data source cannot provide a connection that Flux is requesting, you may experience deadlocks. The background work that drives the Flux engine requires new connections at various times, and if these connections cannot be opened it has the strong potential to halt engine operation. If this occurs, you must restart the engine and increase the connections your database or data source allows (or decrease the MAX_CONNECTIONS parameter in Flux).
Default MAX_CONNECTIONS by Database
Different databases are default configured with a max_connections system parameter. Changing this setting varies depending upon the database vendor. The following are some default max_connection values for different databases. Consult the database vendor’s documentation for the most current information.
| Database Vendor and Version | Default MAX_Connections | Comment |
|---|---|---|
| MySQL 5.5+ | 151 | See https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html |
| MySQL Prior to 5.5 | 100 | See https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html |
| Postgres 9.2 | 100 | See http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html |
| SQL Server 2005, 2008, and 2012 | Default is dynamically allocate up to 32,767 | From: http://msdn.microsoft.com/en-us/library/ms187030.aspx The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Because user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you do not have to change the value for this option. The default is 0, which means that unlimited user connections are allowed. To determine the maximum number of user connections that your system allows, you can execute sp_configure or query the sys.configuration catalog view. |
| Oracle (10g, 11g) | Varies by installation | To view or change the settings - sign on as a DBA and review/change the settings. Generally, this requires an Oracle instance restart. * show parameter sessions; * show parameter processes; * alter system set sessions=300 scope=spfile; * alter system set processes=300 scope=spfile; |
| Embedded Derby Database | Infinite | Embedded Derby can return an unlimited (limited by system resources of course) java.sql.Connection objects can access the database simultaneously, but all these connections need to exist within the same JVM. |
Data Sources and Max Connections
When using a data source, the data source’s maximum capacity must be equal to or greater than your engine’s MAX_CONNECTIONS. In addition, you must make sure that other applications using the same data source do not consume connections that Flux expects to have available (for example, if two engines used the same data source and each had MAX_CONNECTIONS set to 15, the data source would require a maximum capacity of at least 30).