Query running causing perfromance issue

Archived Topics
1

In my slow query log i see the following query running very often causing performance hang in my factory PMI clients.

I believe that it is an internal sql from either FPMI or FSQL, as i have no sql like this.

SELECT A.id , A.path , A.name , A.configchange
FROM sqlt_core A, sqlt_core B
WHERE A.id != B.id
AND A.path = B.path
AND A.name = B.name
AND A.deleted =0

[color=#0080BF]# Time: 100707 11:11:53

User@Host: automation[automation] @ [172.20.251.18]

Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 2757566

SELECT A.id, A.path, A.name, A.configchange FROM sqlt_core A, sqlt_core B WHERE A.id != B.id AND A.path=B.path AND A.n ame=B.name AND A.deleted=0;[/color]

Im my (MYSQL) system, we have 21,000+ tags and this query is running 30+ seconds and seems to be locking SQLT_Core.

can you assist me in determining where this query is coming from please ?

2

Check your sqlt_core table to see if you have any indexes defined. That query is looking for duplicates. If you indexes are defined we can add one with the path and name to speed up that query.

3

Travis,

That table has indexes already. it has 19,585 rows currently.

Indexes: Documentation
Keyname Type Unique Packed Field Cardinality Collation Null Comment
PRIMARY BTREE Yes No id 19585 A
sqlt_core_name_ndx BTREE No No name 0 A YES
sqlt_core_path_ndx BTREE No No path 0 A YES
sqlt_core_valuechange_ndx BTREE No No valuechange 0 A YES
sqlt_core_configchange_ndx BTREE No No configchange 0 A YES

Any other ideas ?

4

Post the results from

EXPLAIN SELECT A.`id` , A.`path` , A.`name` , A.`configchange` FROM sqlt_core A, sqlt_core B WHERE A.`id` != B.`id` AND A.`path` = B.`path` AND A.`name` = B.`name` AND A.`deleted` =0

5

Also, you need an index on deleted

6

After the index on deleted…

SQL result

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A ALL sqlt_core_name_ndx,sqlt_core_path_ndx,deleted NULL NULL NULL 14689 Using where
1 SIMPLE B ref sqlt_core_name_ndx,sqlt_core_path_ndx sqlt_core_name_ndx 258 fsql.A.name 3 Using where

Thank you.

7

EXPLAIN SELECT A.`id` , A.`path` , A.`name` , A.`configchange` FROM sqlt_core A, sqlt_core B WHERE A.`id` != B.`id` AND A.`path` = B.`path` AND A.`name` = B.`name` AND A.`deleted` =0
You need to index everything that is used in the where or join commands. In this case, make sure id, path, name and deleted are indexed. looks like id needs an index as well.

8

All four fields are indexed:

Indexes: Documentation
Keyname Type Unique Packed Field Cardinality Collation Null Comment
PRIMARY BTREE Yes No id 19766 A
sqlt_core_name_ndx BTREE No No name 6588 A YES
sqlt_core_path_ndx BTREE No No path 732 A YES
sqlt_core_valuechange_ndx BTREE No No valuechange 705 A YES
sqlt_core_configchange_ndx BTREE No No configchange 681 A YES
deleted_ndx BTREE No No deleted 1 A YES

9

For what its worth, this query is run by FactoryPMI (to look for duplicates) when the SQLTags system starts up. So under normal running conditions, it won’t run, and won’t be a problem. If it seems to be running more frequently, it is probably because your datasource connection is faulting out (for other reasons).

So from this perspective you’re really troubleshooting the symptom of a different problem. I suggest you look into why your datasource connection is periodically faulting out.