OTRS Too Many Connections

Running OTRS on MySQL you might get seldom ‘Too Many Connections’ Error.

This happened recently on my company’s OTRS implementation; I’m just recording findings, nothing to blame neither OTRS nor MySQL!

Facts

After several years of successfully running OTRS on CENTOS/MySQL (MyISAM storage engine) we suddenly started facing daily customers/agents complaining about not being able to use the system any more while getting ‘Too Many Connections’ error. Outage lasted tens of minutes and disappeared without any intervention.

During outage we were unable to open MySQL Workbrench to check through Client Connections what was happening.

Findings

After several such events we decided to leave open an instance of MySQL Workbrench with Client Connection active. When next event occurred we noticed that there was a client connection running a long lasting query with state ‘Copy data to tmp table’ while all other connections were in ‘Locked’ state. Please notice that MyISAM:

• Has only full table lock granularity;
• Caches only indexes, not data
• Data cache is left at OS operating mercy

Running the query once again we noticed: - it took no time to complete - after clearing the query cache (RESET QUERY CACHE;) it took 5 seconds - after clearing OS disk cache (CentOS) through echo 3 > /proc/sys/vm/drop_caches (see Clear Disk Cache ) the query took over 180 seconds.

Conclusion

Due to some facts (…?) at some point there was a query triggered by a client action, query that lasts very long. During this period one of the most used table becomes locked so any subsequent queries will become locked waiting the initial query to finish. OTRS client, noticing no response from the system, will try refreshing the page or clicking once again some link which in turn will trigger server side opening of a new MySQL connection till the default 151 connections is hit.

Solution

After increasing the OS memory from 6 GB to 12 GB and key_buffer_size to 3 GB (a little bit to defensive) we encountered no more errors.

Some useful MySQL queries

SHOW STATUS LIKE 'key%';
SHOW VARIABLES LIKE 'key%';

SHOW VARIABLES LIKE 'max_heap_table_size';

SHOW STATUS LIKE '%tmp%';

RESET QUERY CACHE;

show engines;

PGgxIGlkPSJydW5uaW5nb3Ryc29ubXlzcWx5b3VtaWdodGdldHNlbGRvbXRvb21hbnljb25u ZWN0aW9uc2Vycm9yLiI+UnVubmluZyBPVFJTIG9uIE15U1FMIHlvdSBtaWdodCBnZXQgc2Vs ZG9tICYjODIxNjtUb28gTWFueSBDb25uZWN0aW9ucyYjODIxNzsgRXJyb3IuPC9oMT4NCg0K PHA+VGhpcyBoYXBwZW5lZCByZWNlbnRseSBvbiBteSBjb21wYW55JiM4MjE3O3MgT1RSUyBp bXBsZW1lbnRhdGlvbjsgSSYjODIxNzttIGp1c3QgcmVjb3JkaW5nIGZpbmRpbmdzLCBub3Ro aW5nIHRvIGJsYW1lIG5laXRoZXIgT1RSUyBub3IgTXlTUUwhPC9wPg0KDQo8aDIgaWQ9ImZh Y3RzIj5GYWN0czwvaDI+DQoNCjxwPkFmdGVyIHNldmVyYWwgeWVhcnMgb2Ygc3VjY2Vzc2Z1 bGx5IHJ1bm5pbmcgT1RSUyBvbiBDRU5UT1MvTXlTUUwgKE15SVNBTSBzdG9yYWdlIGVuZ2lu ZSkgd2Ugc3VkZGVubHkgc3RhcnRlZCBmYWNpbmcgZGFpbHkgY3VzdG9tZXJzL2FnZW50cyBj b21wbGFpbmluZyBhYm91dCBub3QgYmVpbmcgYWJsZSB0byB1c2UgdGhlIHN5c3RlbSBhbnkg bW9yZSB3aGlsZSBnZXR0aW5nICYjODIxNjtUb28gTWFueSBDb25uZWN0aW9ucyYjODIxNzsg ZXJyb3IuIE91dGFnZSBsYXN0ZWQgdGVucyBvZiBtaW51dGVzIGFuZCBkaXNhcHBlYXJlZCB3 aXRob3V0IGFueSBpbnRlcnZlbnRpb24uPC9wPg0KDQo8cD5EdXJpbmcgb3V0YWdlIHdlIHdl cmUgdW5hYmxlIHRvIG9wZW4gTXlTUUwgV29ya2JyZW5jaCB0byBjaGVjayB0aHJvdWdoIENs aWVudCBDb25uZWN0aW9ucyB3aGF0IHdhcyBoYXBwZW5pbmcuPC9wPg0KDQo8aDIgaWQ9ImZp bmRpbmdzIj5GaW5kaW5nczwvaDI+DQoNCjxwPkFmdGVyIHNldmVyYWwgc3VjaCBldmVudHMg d2UgZGVjaWRlZCB0byBsZWF2ZSBvcGVuIGFuIGluc3RhbmNlIG9mIE15U1FMIFdvcmticmVu Y2ggd2l0aCBDbGllbnQgQ29ubmVjdGlvbiBhY3RpdmUuIFdoZW4gbmV4dCBldmVudCBvY2N1 cnJlZCB3ZSBub3RpY2VkIHRoYXQgdGhlcmUgd2FzIGEgY2xpZW50IGNvbm5lY3Rpb24gcnVu bmluZyBhIGxvbmcgbGFzdGluZyBxdWVyeSB3aXRoIHN0YXRlICYjODIxNjtDb3B5IGRhdGEg dG8gdG1wIHRhYmxlJiM4MjE3OyB3aGlsZSBhbGwgb3RoZXIgY29ubmVjdGlvbnMgd2VyZSBp biAmIzgyMTY7TG9ja2VkJiM4MjE3OyBzdGF0ZS4NClBsZWFzZSBub3RpY2UgdGhhdCBNeUlT QU06PC9wPg0KDQo8dWw+DQo8bGk+SGFzIG9ubHkgZnVsbCB0YWJsZSBsb2NrIGdyYW51bGFy aXR5OzwvbGk+DQo8bGk+Q2FjaGVzIG9ubHkgaW5kZXhlcywgbm90IGRhdGE8L2xpPg0KPGxp PkRhdGEgY2FjaGUgaXMgbGVmdCBhdCBPUyBvcGVyYXRpbmcgbWVyY3k8L2xpPg0KPC91bD4N Cg0KPHA+UnVubmluZyB0aGUgcXVlcnkgb25jZSBhZ2FpbiB3ZSBub3RpY2VkOg0KLSBpdCB0 b29rIG5vIHRpbWUgdG8gY29tcGxldGUNCi0gYWZ0ZXIgY2xlYXJpbmcgdGhlIHF1ZXJ5IGNh Y2hlICg8Y29kZT5SRVNFVCBRVUVSWSBDQUNIRTs8L2NvZGU+KSBpdCB0b29rIDUgc2Vjb25k cw0KLSBhZnRlciBjbGVhcmluZyBPUyBkaXNrIGNhY2hlIChDZW50T1MpIHRocm91Z2ggPGNv ZGU+ZWNobyAzICZndDsgL3Byb2Mvc3lzL3ZtL2Ryb3BfY2FjaGVzPC9jb2RlPiAoc2VlIDxh IGhyZWY9Imh0dHA6Ly9zdGFja292ZXJmbG93LmNvbS9xdWVzdGlvbnMvOTU1MTgzOC9ob3ct dG8tcHVyZ2UtZGlzay1pLW8tY2FjaGVzLW9uLWxpbnV4IiB0aXRsZT0iQ2xlYXIgRGlzayBD YWNoZSI+Q2xlYXIgRGlzayBDYWNoZTwvYT4gKSB0aGUgcXVlcnkgdG9vayBvdmVyIDE4MCBz ZWNvbmRzLjwvcD4NCg0KPGgyIGlkPSJjb25jbHVzaW9uIj5Db25jbHVzaW9uPC9oMj4NCg0K PHA+RHVlIHRvIHNvbWUgZmFjdHMgKCYjODIzMDs/KSBhdCBzb21lIHBvaW50IHRoZXJlIHdh cyBhIHF1ZXJ5IHRyaWdnZXJlZCBieSBhIGNsaWVudCBhY3Rpb24sIHF1ZXJ5IHRoYXQgbGFz dHMgdmVyeSBsb25nLiBEdXJpbmcgdGhpcyBwZXJpb2Qgb25lIG9mIHRoZSBtb3N0IHVzZWQg dGFibGUgYmVjb21lcyBsb2NrZWQgc28gYW55IHN1YnNlcXVlbnQgcXVlcmllcyB3aWxsIGJl Y29tZSBsb2NrZWQgd2FpdGluZyB0aGUgaW5pdGlhbCBxdWVyeSB0byBmaW5pc2guIE9UUlMg Y2xpZW50LCBub3RpY2luZyBubyByZXNwb25zZSBmcm9tIHRoZSBzeXN0ZW0sIHdpbGwgdHJ5 IHJlZnJlc2hpbmcgdGhlIHBhZ2Ugb3IgY2xpY2tpbmcgb25jZSBhZ2FpbiBzb21lIGxpbmsg d2hpY2ggaW4gdHVybiB3aWxsIHRyaWdnZXIgc2VydmVyIHNpZGUgb3BlbmluZyBvZiBhIG5l dyBNeVNRTCBjb25uZWN0aW9uIHRpbGwgdGhlIGRlZmF1bHQgMTUxIGNvbm5lY3Rpb25zIGlz IGhpdC48L3A+DQoNCjxoMiBpZD0ic29sdXRpb24iPlNvbHV0aW9uPC9oMj4NCg0KPHA+QWZ0 ZXIgaW5jcmVhc2luZyB0aGUgT1MgbWVtb3J5IGZyb20gNiBHQiB0byAxMiBHQiBhbmQgPGNv ZGU+a2V5X2J1ZmZlcl9zaXplPC9jb2RlPiB0byAzIEdCIChhIGxpdHRsZSBiaXQgdG8gZGVm ZW5zaXZlKSB3ZSBlbmNvdW50ZXJlZCBubyBtb3JlIGVycm9ycy48L3A+DQoNCjxoMiBpZD0i c29tZXVzZWZ1bG15c3FscXVlcmllcyI+U29tZSB1c2VmdWwgTXlTUUwgcXVlcmllczwvaDI+ DQoNCjxwcmU+PGNvZGUgY2xhc3M9InNxbCI+U0hPVyBTVEFUVVMgTElLRSAna2V5JSc7DQpT SE9XIFZBUklBQkxFUyBMSUtFICdrZXklJzsNCg0KU0hPVyBWQVJJQUJMRVMgTElLRSAnbWF4 X2hlYXBfdGFibGVfc2l6ZSc7DQoNClNIT1cgU1RBVFVTIExJS0UgJyV0bXAlJzsNCg0KUkVT RVQgUVVFUlkgQ0FDSEU7DQoNCnNob3cgZW5naW5lczsNCg0KPC9jb2RlPjwvcHJlPg0K