High CPU caused by PI monitoring on MySQL hosts (4373853)

High CPU caused by PI monitoring on MySQL hosts

Foglight for MySQL PI has higher CPU usage on target MySQL or MariaDB hosts after the MySQL PI Cartridge to version 6.1.0.11

The CPU usage goes lower if they disable SQLPI monitor function.

The collection changes in the fix defect FOG-6928 of the 6.1.0.11 MySQL PI cartridge caused the query to become more expensive as the history table becomes larger and more expensive to run. In testing it was found that

• when performance_schema.events_waits_history table has 50000 rows query executions is about 15-20 sec.
• when the table is larger than 100000 rows then query takes more than 90 sec and times out

It appears that the size of performance_schema.events_waits_history has an impact on the query that the PI engine collects data. In databases that have a large number of rows (e.g. 100,000) in the table, it causes a long time to return results.

Users can run this query to obtain the total number of rows from the table.

select COUNT(1) from performance_schema.events_waits_history;
 

•  

RESOLUTION 1

Adjust the MySQL PI requisites to compensate for the larger table size by changing the performance_schema_events_waits_history_size parameter value. In the prerequisites Quest recommends to set this parameter to 100 (save 100 rows for each thread).

Change performance_schema_events_waits_history_size from 100 to 40.

• The tradeoff reducing parameter value is less wait events will be collected so it should be minimum reduced till performance is acceptable
• Less wait events means there'll be only 40 events per thread stored in performance_schema as opposed to 10 (default value) or 100 (foglight suggested pre-requisite value).
• The suggestion to reduce it from 100 to 40 is due to performance impact on the environment. 

RESOLUTION 2

Enhancement FOG-6943 has been logged to as a PI query timeout option into the MySQL PI agent status properties and is available in the 7.1.0.10 and higher releases of the Foglight for MySQL PI cartridge. 

FOG-6943