We are having a big problem of high CPU usage of MySQL process in our database server, after investigation we have found that a big part of slow queries are related to a specific table and a big number of queries have the same structure just the field targetPk is changing from query to another
SELECT item_t1.PK
FROM product2division item_t0
JOIN division item_t1 ON item_t0.SourcePK = item_t1.PK
WHERE ( item_t0.Qualifier = 'Product2Division'
AND item_t0.TargetPK = 8799116853249
AND item_t0.LanguagePK IS NULL)
AND (item_t1.TypePkString=8796130967634 )
order by item_t0.RSequenceNumber ASC , item_t0.PK ASC
running
explain
SELECT item_t1.PK
FROM product2division item_t0
JOIN division item_t1 ON item_t0.SourcePK = item_t1.PK
WHERE ( item_t0.Qualifier = 'Product2Division'
AND item_t0.TargetPK = 8799116853249
AND item_t0.LanguagePK IS NULL)
AND (item_t1.TypePkString=8796130967634 )
order by item_t0.RSequenceNumber ASC , item_t0.PK ASC \G
i get this result :
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: item_t0
partitions: NULL
type: ref
possible_keys: linksource_20002,qualifier_20002,linktarget_20002
key: qualifier_20002
key_len: 767
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: item_t1
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: hybris.item_t0.SourcePK
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
i don't want to do any move before being sure about what i'm doing , do you think adding an index in the column item_t0.TargetPK will optimize the queries, as it is the one who is changing value from query to another ? or something else ?
Update
i know i had to do that before, but i figured out that the index targetPk is already implemented
i don't know what to do next ! HEALP
*************************** 6. row ***************************
Table: product2division
Non_unique: 1
Key_name: linktarget_20002
Seq_in_index: 1
Column_name: TargetPK
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
6 rows in set (0.05 sec)
here are all indexes in the product2division table :
Key_name: linktarget_20002
Seq_in_index: 1
Column_name: TargetPK
Key_name: qualifier_20002
Seq_in_index: 1
Column_name: Qualifier
Key_name: seqnr_20002
Seq_in_index: 1
Column_name: SequenceNumber
Key_name: rseqnr_20002
Seq_in_index: 1
Column_name: RSequenceNumber
Key_name: linksource_20002
Seq_in_index: 1
Column_name: SourcePK
Key_name: PRIMARY
Seq_in_index: 1
Column_name: PK
show create table output
mysql> show create table product2division \G
*************************** 1. row ***************************
Table: product2division
Create Table: CREATE TABLE `product2division` (
`hjmpTS` bigint DEFAULT NULL,
`TypePkString` bigint NOT NULL,
`PK` bigint NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime DEFAULT NULL,
`OwnerPkString` bigint DEFAULT NULL,
`aCLTS` bigint DEFAULT '0',
`propTS` bigint DEFAULT '0',
`Qualifier` varchar(255) NOT NULL,
`SourcePK` bigint NOT NULL,
`TargetPK` bigint NOT NULL,
`RSequenceNumber` int DEFAULT '0',
`SequenceNumber` int DEFAULT '0',
`languagepk` bigint DEFAULT NULL,
`sealed` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`PK`),
KEY `linksource_20002` (`SourcePK`),
KEY `rseqnr_20002` (`RSequenceNumber`),
KEY `seqnr_20002` (`SequenceNumber`),
KEY `qualifier_20002` (`Qualifier`),
KEY `linktarget_20002` (`TargetPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
second table
SHOW CREATE TABLE division \G
*************************** 1. row ***************************
Table: division
Create Table: CREATE TABLE `division` (
`hjmpTS` bigint DEFAULT NULL,
`TypePkString` bigint NOT NULL,
`PK` bigint NOT NULL,
`createdTS` datetime NOT NULL,
`p_isconstruction` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
my.cnf file attributes :
innodb_log_files_in_group = 3
innodb_fast_shutdown = 0
innodb_buffer_pool_size = 7168M
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_lock_wait_timeout = 50
join_buffer_size = 8M
max_connections = 250
max_heap_table_size = 48M
sort_buffer_size = 48M
table_definition_cache = 600
thread_cache_size = 8
thread_stack = 192K
tmp_table_size = 64M
wait_timeout = 28800
# * MyISAM settings
myisam-recover-options = BACKUP
read_buffer_size = 4M
low_priority_updates = 1
read_rnd_buffer_size = 1M
key_buffer_size = 16M
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 16M
Thanks in advance !!
PK
implyPRIMARY KEY
?