Jonathan Hurley created AMBARI-13570:
----------------------------------------
Summary: Reduce Load On Database By Caching Alerts
Key: AMBARI-13570
URL: https://issues.apache.org/jira/browse/AMBARI-13570
Project: Ambari
Issue Type: Task
Components: ambari-server
Affects Versions: 2.2.0
Reporter: Jonathan Hurley
Assignee: Jonathan Hurley
Fix For: 2.2.0
Alert-related SQL queries and updates are responsible for the majority of calls
to the database in most deployments. This is due to the fact that alerts update
their timestamp and latest text on every alert, regardless of state change.
Some initial numbers to share. In my environment, without alert caching, I see
over about ~10 minutes:
{code}
+----------------------------------------------------+-------+
| SUBSTRING(argument,1,50) | count |
+----------------------------------------------------+-------+
| SELECT DISTINCT task_id FROM host_role_command WHE | 554 |
| SELECT DISTINCT t0.task_id FROM host_role_command | 554 |
| SELECT COUNT(task_id) FROM host_role_command WHERE | 554 |
| SELECT t1.alert_id AS a1, t1.latest_text AS a2, t1 | 379 |
| UPDATE alert_current SET latest_timestamp = 144588 | 362 |
| SELECT definition_id, cluster_id, component_name, | 101 |
| SELECT `metainfo_key`, `metainfo_value` FROM metai | 87 |
| SELECT alert_id, alert_instance, alert_label, aler | 56 |
{code}
With the key values being:
{code}
| SELECT t1.alert_id AS a1, t1.latest_text AS a2, t1 | 379 |
| UPDATE alert_current SET latest_timestamp = 144588 | 362 |
| SELECT definition_id, cluster_id, component_name, | 101 |
| SELECT alert_id, alert_instance, alert_label, aler | 56 |
{code}
After enabling caching:
{code}
+----------------------------------------------------+-------+
| SUBSTRING(argument,1,50) | count |
+----------------------------------------------------+-------+
| SELECT DISTINCT task_id FROM host_role_command WHE | 530 |
| SELECT DISTINCT t0.task_id FROM host_role_command | 530 |
| SELECT COUNT(task_id) FROM host_role_command WHERE | 530 |
| SELECT definition_id, cluster_id, component_name, | 100 |
| SELECT `metainfo_key`, `metainfo_value` FROM metai | 87 |
| SELECT alert_id, alert_instance, alert_label, aler | 56 |
| SELECT t1.alert_id AS a1, t1.latest_text AS a2, t1 | 51 |
| SELECT id, component, host_task_id, physical_task_ | 43 |
| SELECT t1.alert_id, t1.latest_text, t1.latest_time | 8 |
{code}
With the key values being:
{code}
| SELECT definition_id, cluster_id, component_name, | 100 |
| SELECT alert_id, alert_instance, alert_label, aler | 56 |
| SELECT t1.alert_id AS a1, t1.latest_text AS a2, t1 | 51 |
| SELECT t1.alert_id, t1.latest_text, t1.latest_time | 8 |
{code}
Alert calls before: 898
Alert calls after: 215
Although these calls also include the initial loading of data, it's still an
improvement of about 76% for the alert calls. For all calls in general, it's an
improvement of about 33%.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
