MySQL Bugs: #40462: Agent does not flush queries after they have been sent to the dashboard

Bug #40462	Agent does not flush queries after they have been sent to the dashboard
Submitted:	31 Oct 2008 19:23	Modified:	28 Jan 2011 22:23
Reporter:	Diego Medina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S2 (Serious)
Version:	2.0.0.7082	OS:	Any
Assigned to:	Jan Kneschke	CPU Architecture:	Any
Tags:	windmill

Description:
The agent keeps all the canonical queries that go through the proxy in memory.

This is related to bug# 40260 http://bugs.mysql.com/bug.php?id=40260

How to repeat:
1- Install and start the agent and monitor
2- Configure the Query Analysis to enable Query Sample, Example and Explain
3- Monitor the memory usage of the agent
4- Send some queries through the agent (an app like sugarCRM would do)
5- Notice that the agent's memory usage goes up
6- Do not send any more queries and notice that the memory usage will not go down.

Suggested fix:
implement the "snapshot" reporting of quan that the server already
supports.

Bug#54081 was marked as a duplicate of this one.

related to bug 54304

Any updates?

Diego Medina writes: 
Tested 	2.3.1.2038  and the queries are still in the agent memory. I verified this by using 

 kill -SIGUSR2 `cat /Applications/mysql/enterprise/2.3/agent/mysql-monitor-agent.pid ` 

and grepping the log file in /tmp/ showed the queries there.

Attachment: 10521_mysql-monitor-agent-items.dump-20101117-152213.txt (text/plain), 101.11 KiB.

Diego Medina writes: 
This is the dump after the queries were sent to tomcat, I will also attach the mysql agent log in debug

Diego Medina writes: 
Agent debug log

Diego Medina writes: 
jira workflow

A simpler version of the above script that doesn't cause extra disk-io. The goal is just to create 2048 distinct queries that don't normalize to the same query.

#!/bin/bash

MYSQLCLIENT=/opt/local/bin/mysql5
HOST=127.0.0.1
PORT=4040

MYSQL="${MYSQLCLIENT} --host=${HOST} --port=${PORT}"

set -e

for x in {1..1023}; do
    ${MYSQL} -e "SELECT $x AS t$x;" > /dev/null;
    echo "SELECT $x AS t$x";
done;

for x in {1..1023}; do
    ${MYSQL} -e "SELECT $x AS c$x;" > /dev/null;
    echo "SELECT $x AS c$x";
done;

The queries stay in the item-hash (and can be seen in the dump-files) until the next data-collection cycle of mysql::statementsummary and friends. That is about 60seconds.

Attachment: 10533_mysql-monitor-agent-items.dump-20101208-112604.txt (text/plain), 85.08 KiB.

Attachment: 10534_mysql-monitor-agent-items.dump-20101208-112634.txt (text/plain), 85.08 KiB.

Attachment: 10535_mysql-monitor-agent-items.dump-20101208-112704.txt (text/plain), 85.08 KiB.

Attachment: 10536_mysql-monitor-agent-items.dump-20101208-112734.txt (text/plain), 85.08 KiB.

Attachment: 10537_mysql-monitor-agent-items.dump-20101208-112934.txt (text/plain), 85.08 KiB.

Attachment: 10538_mysql-monitor-agent-items.dump-20101208-113004.txt (text/plain), 85.08 KiB.

Attachment: 10539_mysql-monitor-agent-items.dump-20101208-113034.txt (text/plain), 85.08 KiB.

Attachment: 10540_mysql-monitor-agent.log.gz (application/x-gzip, text), 693.89 KiB.

Diego Medina writes: 
See EM-3001 for work progress

Jan Kneschke writes: 
As a side-effect of the fix for EM-3001 this bug may be fixed too by:

revno: 2003
fixes bug(s): http://bugs.mysql.com/42105
committer: jan@mysql.com
branch nick: trunk
timestamp: Fri 2011-01-14 18:57:25 +0100
message:
  fetch all QUAN data from the Lua script over to the item-hash at list-instances(mysql::statementsummary) time (fixes #42104/EM-3001)
  
  * all QUAN classes are fetched at the same time
  * data is consistent 
  * data removal is predictible

Diego Medina writes: 
verified fixed on 2.3.2.2051

Added to 2.3.2 change log:

Reduced memory usage for the &merlin_agent;. Formerly, it retained in
memory the canonical queries that it sent to the proxy.