High mysql lock when enabling custom search attribute with large number of workflow

Community Support
1

I am using Mysql for Visibility store with enabling advanced search attribute

Whenever a workflow is running, it creates a query like this:

INSERT INTO custom_search_attributes (
			namespace_id, run_id, search_attributes, _version
		) VALUES ('4989d4b2-6814-470f-b7b4-012a3b869e6b', '0198113a-fcea-76e8-b4bc-903066f4a063', '{\"BuildIds\":[\"unversioned\",\"unversioned:356c8c9a4707f996228198f1b1f61fd2\",\"unversioned:e3c4e67c302df47dfb7607478e82e839\",\"unversioned:2c3743053e73629030f82ac06f7821bc\"],\"TemporalNamespaceDivision\":\"TemporalScheduler\",\"TemporalSchedulePaused\":false}', 119579357) **ON DUPLICATE KEY UPDATE** search_attributes = IF(_version < VALUES(_version), VALUES(search_attributes), search_attributes), _version = IF(_version < VALUES(_version), VALUES(_version), _version)

However, after reaching 30 million records in custom_search_attributes table, the lock for each insert is very large, which impacts the overall of mysql instance. Mysql crashed with this log:

InnoDB: ###### Diagnostic info printed to the standard error stream
2017-11-16T13:18:39.650036Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung

Expected Behavior

Custom search attribute for mysql should not have very large lock and cause mysql to crash

Actual Behavior

When number of records is over 30 million, each insert into custom_search_attributes run really slow with high lock time

Specifications

  • Version: 1.28.1
  • Platform: self host on K8S
2

Mysql crashed with this log

Don’t think there is anything on Temporal side for this. Would look into MySQL to see if can increase some timeout value when under load. You can define connection info in your static config, something like:

persistence:
visibilityStore: mysql-visibility
datastores:
mysql-visibility:
sql:
// …
connectAttributes:
key1: value1
key2: value2

Overall tho I think we would recommend to think about switching to ElasticSearch/OpenSearch for visibility store, especially for larger scale workloads. It’s much more scalable and performant than MySql for visibility.