adding the right index to a table in a MySQL Database

Question

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)

the output of MySQLtunner

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

RAM CPU and swap usage

Thanks in advance !!

Answer 1

"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 ?"

That is a bad reason for indexing.

What matters is how much the index will help reduce the amount of work required to compute the query. Whether your proposed index will help will mostly depend on which of these results is larger:

select count(*) from product2division item_t0  where item_t0.Qualifier  = 'Product2Division'
select count(*) from product2division item_t0  where item_t0.TargetPK  = 8799116853249 

Assuming there is even distribution of TargetPk values.

If TargetPK is unqique then the second query will always return 1 (or zero) which means there will only be a maximum of 1 row to look at in the table which is great.

Of course, if the difference is small then the decider will be down to how clustered the rows that match these filters are. If both counts are relatively high, then maybe a composite index which includes both columns could be a good idea (if there's not a lot of crossover on the rows that each filter finds).

Another possibility is that you could drive the query from the division table. This could be sensible if the TypePkString is going to return a small amount of rows and the SourcePK filter (from the join) is also relatively good at reducing the number of rows visited in product2division. For this order of tables, you would be indexing:

division (TypePkString)
product2division (SourcePK)

Answer 2

I will be concerned on cnf options only.

This four options are not related to the storage engines but to the client's sessions so my advise is to keep them in a separate paragraph of the cnf file. Their values combined and multiplied by the number of concurrent connections (max possible and max reached) impact the RAM consumption.

read_buffer_size                = 4M
read_rnd_buffer_size            = 1M
join_buffer_size                = 8M
sort_buffer_size                = 48M

48M is pretty big value for sort buffer. Probably you don't need it that large. You can start with 1M value for each of that variables. That will lead to 4M (~5M with overheads) buffers for each connection (instead of 77M now) and will decrease the RAM consumption by 5GB reached/18GB max.

Also that variable is too high for your hardware:

innodb_buffer_pool_size         = 7168M

You have 7.8GB of total RAM that is shared by OS, DB and other services on the host. The good starting point for that value is a half of total RAM amount or 4G.

innodb_buffer_pool_size         = 4G

Keep in mind that innodb_log_file_size value should be close to 1/8 of innodb_buffer_pool_size value. Its current value of 512M is good enough.

Approximate mysql RAM consumption then will be
4G + (75 * 5M) = 5G (expected)
4G + (250 * 5M) = 6G (maximum)
And 2-3G are free for OS needs and fine tuning. If your RAM consumption will be low enough you can tune your options starting with innodb_buffer_pool_size - make it 5G big instead of 4G with innodb_log_file_size corrected respectively.

You can refer to the InnoDB metrics section of the mysqltuner's report to see InnoDB buffer pool / data size ratio. If data size is close to or bigger than physical RAM you have then you will not gain too much from innodb_buffer_pool_size modification. But I see you have no slow queries and tmp tables so it wouldn't be a problem for you.

You can raise join/sort/read/rnd buffer values instead up to 6-8MB in total. Which values need to be increased depends on your queries and should be tested inplace.

Answer 3

Add these composite indexes:

item_t0:  INDEX(Qualifier, TargetPK, LanguagePK)
item_t1:  INDEX(TypePkString, PK)

It sounds like you currently have INDEX(Qualifier)? If so, remove it as redundant.

For further discussion, please provide SHOW CREATE TABLE. Datatypes, engines, indexes, etc are important for query optimization.

The formula used for "memory usage" is pessimistic. It may not be as bad as it sounds. The real question is -- if you are swapping, then something is configured too high. For further analysis, please provide the info requested here: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

The number of "joins without index" does not sound very high, once it is divided by the Uptime (more than a year).

The EXPLAIN shows "Rows: 1" for each table. The speed must be only a few milliseconds. Or was it using only a subset of the real data?

See also my index cookbook It takes much of the guessing and COUNTing out of building the best index for simple cases.

More (based on added info)

This change may avoid swapping, which will help speed:

innodb_buffer_pool_size         = 7168M --> 5G

Possibly excessive:

max_connections                 = 250

Use SHOW STATUS LIKE 'Max_used_connections'; to see how much of that 250 was actually needed since the last startup. Then lower max_connections to something realistic, but safe.

BIGINT takes 8 bytes, INT takes 4 bytes, other ints take even less. The table sizes could shrink if you consistently changed each element (TargetPK, LanguagePK, etc) across the tables. LanguagePK could probably be 1-byte TINYINT UNSIGNED (range: 0..255), thereby saving 7 bytes per use. Smaller --> a little faster, less disk space needed, less buffer_pool needed, etc.