Monitor Agent creates massive slow queries
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.