Monitor Agent creates massive slow queries
Posted by: Christian Molecki
Date: April 07, 2014 12:08AM

Hello,

my company analyzes the logfiles of the mysql-server.
There we can see, that the monitor agent creates a lot of entries in the slow query logfile. They are so long, that we can see the full logentry in the slow query as well in the analyzing tool.

Example:
SET timestamp=1396609157; SELECT * FROM (SELECT digest AS `digest`, schema_name AS `schema`, count_star AS `execCount`, IFNULL(sum_timer_wait * 0.000001, 0) AS `execTimeTotal`, IFNULL(min_timer_wait * 0.000001, 0) AS `execTimeMin`, IFNULL(max_timer_wait * 0.000001, 0) AS `execTimeMax`, IFNULL(sum_lock_time * 0.000001, 0) AS `lockTimeTotal`, sum_errors AS `errorCount`, sum_warnings AS `warningCount`, sum_rows_affected + sum_rows_sent AS `rowsTotal`, sum_rows_examined AS `rowsExaminedTotal`, sum_created_tmp_disk_tables AS `createdTmpDiskTables`, sum_created_tmp_tables AS `createdTmpTables`, sum_select_full_join AS `selectFullJoin`, sum_select_full_range_join AS `selectFullRangeJoin`, sum_select_range AS `selectRange`, sum_select_range_check AS `selectRangeCheck`, sum_select_scan AS `selectScan`, sum_sort_merge_passes AS `sortMergePasses`, sum_sort_range AS `sortRange`, sum_sort_rows AS `sortRows`, sum_sort_scan AS `sortScan`, sum_no_index_used AS `noIndexUsedCount`, sum_no_good_index_used AS `noGoodIndexUsedCount`, digest_text AS `normalizedText`, unix_timestamp(first_seen)*1000 AS `firstSeen`, unix_timestamp(last_seen)*1000 AS `lastSeen` FROM performance_schema.events_statements_summary_by_digest WHERE digest IS NOT NULL -- even though i want a (from,to] set based on last_seen, it's only at 1s -- precision, so if i elide a seconds worth of samples, it could miss some. -- note, then this requires the delta'izing algorithm to elide exec counts -- that haven't changed AND UNIX_TIMESTAMP(last_seen)*1000 > 1396609097000 -- AND UNIX_TIMESTAMP(last_seen)*1000 <= ? ) as digests LEFT OUTER JOIN (SELECT history.*, threads.processlist_user as `user`, threads.processlist_host as `hostFrom` FROM ( SELECT statement_history.digest AS `digest`, sql_text as `text`, current_schema as `schema`, thread_id as `connectionId`, IFNULL(statement_history.timer_wait * 0.000001, 0) as `execTime`, errors as `errors`, warnings as `warnings`, rows_sent as `rows`, no_index_used as `noIndexUsed`, no_good_index_used as `noGoodIndexUsed`,
max_times.serverStartMillis + FLOOR(timer_end / 1000000000) as exampleTimestamp, timer_end FROM performance_schema.events_statements_history_long as statement_history JOIN ( SELECT IFNULL(MAX(timer_wait), 0) AS timer_wait, digest, current_schema as `schema`, status.serverStartMillis as serverStartMillis FROM performance_schema.events_statements_history_long JOIN ( SELECT (UNIX_TIMESTAMP(now()) - CAST(variable_value AS SIGNED))*1000 as serverStartMillis FROM information_schema.global_status WHERE variable_name='uptime' ) AS status -- filter by the exampleTimestamp per row when finding the maxes, -- as we only want rows in our range considered WHERE timer_end > '407964579744677000' -- AND timer_end <= ?
-- AND 1000+status.serverStartMillis + FLOOR(timer_end / 1000000000) <= ? GROUP BY digest, current_schema ) AS max_times ON (max_times.timer_wait = statement_history.timer_wait AND max_times.digest = statement_history.digest AND max_times.schema <=> statement_history.current_schema) ) as history LEFT OUTER JOIN performance_schema.threads AS threads ON history.connectionId = threads.thread_id ) as events ON digests.digest = events.digest AND digests.schema <=> events.schema; # User@Host: agent_master[agent_master] @ localhost [127.0.0.1] Id: 11478 # Query_time: 0.000383 Lock_time: 0.000253 Rows_sent: 1 Rows_examined: 0


Is there a possibility to prevent creating logentries caused by the agent?

Best Regards
Chris



Edited 1 time(s). Last edit at 04/07/2014 12:09AM by Christian Molecki.

Options: ReplyQuote


Subject
Views
Written By
Posted
Monitor Agent creates massive slow queries
5243
April 07, 2014 12:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.