TAS internal MySQL memory usage increases significantly after upgrade to 4.0

Products

• VMware Tanzu Application Service

Issue/Introduction

The issue is observed after upgrade to TAS 4.0 (with internal MySQL from 5.7 to 8.0), and only when "Internal MySQL > Server activity logging" is allowed. In this scenario, no matter how much memory is assigned to MySQL instances, mysqld process memory usage keeps growing until entire VM memory usage close to 100%.

Environment

• TAS 4.0 (internal MySQL 8.0)
• "Internal MySQL > Server activity logging" is allowed.

Cause

A memory leak issue with audit logging plugin has been observed and reported to Percona, it is being tracked at PS-9369. 

The audit plugin defect did NOT affect MySQL 5.7 which was used in TAS 2.#, once upgrade to TAS 4.0 with MySQL 8.0, it appears audit logging plugin tracks queries leading to this component “leaking” queries in an internal data structure.

Diego / BBS is largely affected by this audit log issue for two reasons:

1. BBS uses a long-lived database connections
2. BBS heavily uses server-side prepared statements for the bulk of its queries

Components like locket has an upper lifetime of 1 hour for a connection, thus the connection is recycled quickly and memory eventually released before a significant amount of memory leak. BBS has no upper bound on connection lifetime and will continue reusing connections as long as the database server is healthy. 

Components like UAA, Credhub, CAPI do not heavily use server-side prepared statements so they are not affected by this issue. Java MySQL connectors, in general, tend to prefer client-side “prepared statements” for historical and other performance reasons and avoid this class of issue entirely. 

Resolution

The issue has been fixed in Percona MySQL and the fix is included in TPCF v4.0.31 and v6.0.11.

As temporary workaround until the memory leak issue with audit logging plugin being fixed at Percona side

• Turn off the plugin by setting "Internal MySQL > Server activity logging" to "disallow".
• Or `monit restart bbs` when it reaches high memory usage.