{"id":13470,"date":"2018-02-12T09:28:23","date_gmt":"2018-02-12T16:28:23","guid":{"rendered":"https:\/\/ephesoft.com\/docs\/?p=13470"},"modified":"2021-01-29T13:00:17","modified_gmt":"2021-01-29T20:00:17","slug":"maximum-mariadb-connections","status":"publish","type":"post","link":"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/","title":{"rendered":"Maximum MariaDB Connections"},"content":{"rendered":"<h2><span style=\"color: #333333\"><strong>Issue<\/strong><\/span><\/h2>\n<p><span style=\"color: #333333\">The following error may occur when trying to establish a connection to MariaDB in the multi-server environment:<\/span><\/p>\n<pre><span style=\"color: #333333\">org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection : Too many Connections open<\/span><\/pre>\n<h3><span style=\"color: #333333\"><strong>Background<\/strong><\/span><\/h3>\n<p><span style=\"color: #333333\">As an example, let\u2019s take the multi-server environment that includes three servers. In this case, the number of connections that can be established with the default <strong>server.xml<\/strong> configuration will be as follows:<\/span><\/p>\n<ul>\n<li><span style=\"color: #333333\">Minimum DB connections that will always be kept open in connection pool: 45 = 3 (servers) * 15 (5 for Ephesoft application, 5 for Reports, 5 for Report_Archive)<\/span><\/li>\n<li><span style=\"color: #333333\">Maximum DB connections Ephesoft Transact is allowed to open: 900 = 3 (servers) * 300 (100 for Ephesoft application, 100 for Reports, 100 for Report_Archive)<\/span><\/li>\n<\/ul>\n<p><span style=\"color: #333333\">By default, MariaDB supports up to 150 connections at a time. That means this error can occur if Ephesoft Transact tries to open more connections than the limit set by the database.<\/span><\/p>\n<h2><span style=\"color: #333333\"><strong>Solution<\/strong><\/span><\/h2>\n<p><span style=\"color: #333333\">There are two approaches to handle this problem:\u00a0<\/span><\/p>\n<ol>\n<li><span style=\"color: #333333\"><strong>Increase the number of connections on the database server<\/strong>. You will need to calculate your environment\u2019s connection requirement and increase the connection number on the database server accordingly.<\/span><\/li>\n<li><span style=\"color: #333333\"><strong>Restrict Ephesoft Transact from opening more connections than the database server capacity<\/strong>. This can be done by adjusting the connection setting in the <strong>server.xml file<\/strong>, located at [<em>Ephesoft_Directory<\/em>]\\JavaAppServer\\conf. In the\u00a0<strong>server.xml<\/strong>, the <code>maximumPoolSize<\/code> attribute of the\u00a0<code>Resource<\/code> tag can be configured as needed to restrict Ephesoft Transact from opening a higher number of connections.<\/span><\/li>\n<\/ol>\n<p><span style=\"color: #333333\"><a style=\"color: #333333\" href=\"https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1.png\"><img decoding=\"async\" class=\"alignnone wp-image-13477\" src=\"https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1.png\" alt=\"\" width=\"512\" height=\"380\" srcset=\"https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1.png 894w, https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1-300x222.png 300w, https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1-768x570.png 768w\" sizes=\"(max-width: 512px) 100vw, 512px\" \/><\/a><\/span><\/p>\n<p><span style=\"color: #333333\"><em>Figure 1.\u00a0<strong>server.xml\u00a0<\/strong>File<\/em><\/span><\/p>\n<p><span style=\"color: #333333\">When calculating connection requirements and adjusting connections in the <strong>server.xml<\/strong> and database server, consider the following:<\/span><\/p>\n<ol>\n<li><span style=\"color: #333333\">The required number of connections increases as the number of batch instances being processed in parallel on a server increases. On average, Ephesoft Transact uses two connections per batch being processed on a server. So, if the maximum process capacity of a server is set to 12, a minimum of 24 connections to the Transact database is required for batch processing.<\/span><\/li>\n<li><span style=\"color: #333333\">Reporting requires a large number of database connections to process a large amount of reporting data in a shorter period of time. If the number of batches processed between two reporting clean-up cycles is very large, reporting will require a large number of connections to process and clean the processed records. To reduce reporting connection requirements, set the reporting cron jobs to execute more frequently.<\/span><\/li>\n<li><span style=\"color: #333333\">Connection consumption on the reporting server can shoot up to <code>maximumPoolSize<\/code> when clean-up and advanced reports jobs are executed.<\/span><\/li>\n<li><span style=\"color: #333333\">Reduce the\u00a0<code>minimumIdle<\/code> attribute value for the reports_archive database to &#8220;1&#8221;, as this database is rarely used.<\/span><\/li>\n<li><span style=\"color: #333333\">On a non-reporting server, <code>maximumPoolSize<\/code> can be set to lower values, as the connection requirement will never reach the default maximum value.<\/span><\/li>\n<li><span style=\"color: #333333\">You can configure connections in the <strong>server.xml<\/strong> so that the max connection capacity of the database server is never exceeded (by ensuring the sum of <code>maximumPoolSize<\/code> doesn\u2019t exceed the connection limit on the database server). <\/span><\/li>\n<\/ol>\n<p style=\"padding-left: 40px\"><span style=\"color: #333333\"><strong>Note:<\/strong> This type of defensive setting can decrease application performance.<\/span><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Issue The following error may occur when trying to establish a connection to MariaDB in the multi-server environment: org.springframework.transaction.CannotCreateTransactionException: Could [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12343],"tags":[1430,620,11950,1147],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v19.0 (Yoast SEO v22.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Maximum MariaDB Connections | Ephesoft Docs<\/title>\n<meta name=\"robots\" content=\"noindex, follow\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Maximum MariaDB Connections\" \/>\n<meta property=\"og:description\" content=\"Issue The following error may occur when trying to establish a connection to MariaDB in the multi-server environment: org.springframework.transaction.CannotCreateTransactionException: Could [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/\" \/>\n<meta property=\"og:site_name\" content=\"Ephesoft Docs\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-12T16:28:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-29T20:00:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/\",\"url\":\"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/\",\"name\":\"Maximum MariaDB Connections | Ephesoft Docs\",\"isPartOf\":{\"@id\":\"https:\/\/ephesoft.com\/docs\/#website\"},\"datePublished\":\"2018-02-12T16:28:23+00:00\",\"dateModified\":\"2021-01-29T20:00:17+00:00\",\"author\":{\"@id\":\"\"},\"breadcrumb\":{\"@id\":\"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/ephesoft.com\/docs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Maximum MariaDB Connections\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/ephesoft.com\/docs\/#website\",\"url\":\"https:\/\/ephesoft.com\/docs\/\",\"name\":\"Ephesoft Docs\",\"description\":\"Intelligent Document Processing Made Easy\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/ephesoft.com\/docs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Maximum MariaDB Connections | Ephesoft Docs","robots":{"index":"noindex","follow":"follow"},"og_locale":"en_US","og_type":"article","og_title":"Maximum MariaDB Connections","og_description":"Issue The following error may occur when trying to establish a connection to MariaDB in the multi-server environment: org.springframework.transaction.CannotCreateTransactionException: Could [&hellip;]","og_url":"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/","og_site_name":"Ephesoft Docs","article_published_time":"2018-02-12T16:28:23+00:00","article_modified_time":"2021-01-29T20:00:17+00:00","og_image":[{"url":"https:\/\/ephesoft.com\/docs\/wp-content\/uploads\/2018\/02\/screen1.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Written by":"","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/","url":"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/","name":"Maximum MariaDB Connections | Ephesoft Docs","isPartOf":{"@id":"https:\/\/ephesoft.com\/docs\/#website"},"datePublished":"2018-02-12T16:28:23+00:00","dateModified":"2021-01-29T20:00:17+00:00","author":{"@id":""},"breadcrumb":{"@id":"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/ephesoft.com\/docs\/maximum-mariadb-connections\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ephesoft.com\/docs\/"},{"@type":"ListItem","position":2,"name":"Maximum MariaDB Connections"}]},{"@type":"WebSite","@id":"https:\/\/ephesoft.com\/docs\/#website","url":"https:\/\/ephesoft.com\/docs\/","name":"Ephesoft Docs","description":"Intelligent Document Processing Made Easy","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ephesoft.com\/docs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":""}]}},"_links":{"self":[{"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/posts\/13470"}],"collection":[{"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/comments?post=13470"}],"version-history":[{"count":0,"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/posts\/13470\/revisions"}],"wp:attachment":[{"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/media?parent=13470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/categories?post=13470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ephesoft.com\/docs\/wp-json\/wp\/v2\/tags?post=13470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}