maria-discuss team mailing list archive

[Daniel Bartholomew <dbart@xxxxxxxxxxxxxxxx>]

The following question has been posted to the Knowledgebase:

https://kb.askmonty.org/en/slow-performance-for-some-queries/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Slow Performance for some queries
Hi there,

I have discovered some very low performing queries on our MariaDB
version 5.3.7. The query shows up with "string results in query cache"

The query:

explain select distinct
        `config`.`agent_id` AS `agent_id`,
        `config`.`agent_name` AS `agent_name`,
        `agent`.`agent_version` AS `agent_version`,
        `config`.`agent_os` AS `agent_os`,
        `agent`.`agent_os_version` AS `agent_os_version`,
        `config`.`pconfig_line` AS `pconfig_line`,
        `config`.`appclass` AS `appclass`,
        `config`.`instance` AS `instance`,
        `config`.`parameter` AS `parameter`,
        `config`.`value` AS `value`,
        substring_index(substring_index(`config`.`variable`, '/', '4ö'),
                '/',
                -(1)) AS `NT_Drive`,
        `config`.`agent_config_date` AS `agent_config_date`,
        `config`.`rule_type_name` AS `rule_type_name`
    from
        (`agent_config_detail_view` `config`
        left join `agent` ON ((`agent`.`agent_id` =
`config`.`agent_id`))) where
        (((`config`.`rule_type_id` = 'ccbe914e30c6ec6e3aa5c4780a3525d6')
            or (`config`.`rule_type_id` =
'0091efee7f9b5f0e27211eb7226d0a23') or (`config`.`rule_type_id` =
'6311ae17c1ee52b36e68aaf4ad066387')) and ((`config`.`variable` =
'/AgentSetup/FILESYSTEM.filterList') or (`config`.`variable` =
'/AgentSetup/FILESYSTEM.filterType') or (`config`.`variable` like
'/AS/EVENTSPRING/PARAM_SETTINGS/THRESHOLDS/FILESYSTEM%') or
(`config`.`variable` like
'/RecoveryActions/NT_LOGICAL_DISKS/%/LDldFreeSpacePercent/Mode')) and
(`config`.`agent_config_date` > ((select max(`s`.`agent_config_date`)
from `agent_config` `s`) - interval 24 hour)))
    order by `config`.`agent_name` , `config`.`appclass`




Here is the explain for the query in question:

id	select_type	table	type
possible_keys	key	key_len	ref	rows
Extra 1	PRIMARY	a	ALL	PRIMARY
NULL	NULL	NULL	7083	"Using temporary; Using
filesort" 1	PRIMARY	agent	eq_ref
PRIMARY	PRIMARY	34	new_budm.a.agent_id	1
1	PRIMARY	b	ref
PRIMARY,fk_agent_config_agent1,IX_agent_config_date
fk_agent_config_agent1	34	new_budm.a.agent_id
3	"Using where" 1	PRIMARY	c	ref
agent_config_rule_fk_agent_config_id,agent_config_rule_fk_rule_id
agent_config_rule_fk_agent_config_id	34
new_budm.b.agent_config_id	947 1	PRIMARY	d
eq_ref	PRIMARY,IX_rule_variable	PRIMARY	34
new_budm.c.fk_rule_id	1	"Using where" 1
PRIMARY	e	ref
fk_rule_rule_type_rule_type1,fk_rule_rule_type_rule
fk_rule_rule_type_rule	34	new_budm.c.fk_rule_id
1	"Using where" 1	PRIMARY	f	eq_ref
PRIMARY	PRIMARY	34
new_budm.e.fk_rule_type_id	1 2	SUBQUERY
NULL	NULL	NULL	NULL	NULL	NULL
NULL	"Select tables optimized away" The query cache
(innodb_buffer_pool_size) is set to 8192M.

Other queries are running fine (perfomance can always be better).

Thank you and regards, Patrick
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Thanks.

-- 
Daniel Bartholomew
  Google+ - http://gplus.to/dbart
  Twitter - http://twitter.com/daniel_bart

MariaDB: An Enhanced Drop-in Replacement for MySQL
        Website - http://mariadb.org
        Twitter - http://twitter.com/mariadb
        Google+ - http://google.com/+mariadb
       Facebook - http://fb.com/MariaDB.dbms
  Knowledgebase - http://kb.askmonty.org
  Monty Program - http://montyprogram.com