mysql (aws forced update) from 5.6 to 5.7 causing slower queries

Question

Premise: I have a db (was running well on mysql 5.6) that got upgraded to 5.7 and is causing performance issues. The front end gets aroun 20k requests per minute (consistent). Each request generates an IODKU into one of the tables.

RDS hardware: db.m5.large

On 5.6, CPU: 30%, Avg DB Connections: 20 On 5.7, CPU: 75%, Avg DB Connections: 255.

For the following query:

show global status like 'threads%';

I keep refreshing, and For the most part I can see

Threads_cached 0
Threads_connected 257 Threads_created 203899
Threads_running 255

But In between, for a short bit, it will also go to

Threads_cached 30
Threads_connected 3
Threads_created 206709
Threads_running 1

I have spend around 3 days, reading, and changing different parameter group values. See below my changes, links I based my actions on, results, etc.

=============

optimizer_switch

https://dba.stackexchange.com/questions/193262/after-mysql-upgrade-to-5-7-optimizer-not-using-index-on-large-in-clause

https://support-acquia.force.com/s/article/360061350614-Resolving-unexpected-slow-database-queries-after-MySQL-5-7-upgrade

changes mentioned in this link did not make much difference.

=========

innodb_flush_log_at_trx_commit

https://dba.stackexchange.com/questions/174527/poor-mysql-5-7-performance-compared-to-mysql-5-6

originally set to 1. Changing to 2 did not make any difference

=======

query_cache_size

original: 1048576. Changing to 67108864 Did not make any difference.

=======

query_cache_type

shows as OFF. Changing to 1.Seemed to worsen. So put it back to OFF. Had to restart.

========

innodb_io_capacity default: 200. Changing to 400 and even 800. Did not make any difference.

====== innodb_flush_neighbors default: 1. changing to 0. Did not make any difference.

======

thread_cache_size

https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/ default: 14. Changing to 32. Did not make any difference.

=======

tmp_table_size default: 16777216. Changing to 33554432. Did not make any difference.

====== innodb_read_io_threads, innodb_write_io_threads default: 4. changing to 8. Needs reboot. Made things worse. Putting back to 4. 

=========== Pretty much all links from google search on this regard are now "pink" on my browser (meaning already visited), and I am kinda at a loss. Any help is appreciated.

UPDATE#1:

Enabled Slow_query_log. Waited for a while, and this is what is in the log.

/rdsdbbin/mysql/bin/mysqld, Version: 5.7.38-log (Source distribution). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument /rdsdbbin/mysql/bin/mysqld, Version: 5.7.38-log (Source distribution). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument ----------------------- END OF LOG ----------------------

UPDATE 2

For each Request The following happens.

Query#1

SELECT m.id AS uID,m.tkn,m.email FROM memberTable m WHERE m.tkn='$subTkn'

For memberTable, id (primary) and tkn(indexed) have indexes.

Then after some processing, the following Query#2 happens.

INSERT into dataTable (memberID,status,packetTime ) VALUES ('76418','1','2022-12-25 22:10:33') ON DUPLICATE KEY UPDATE memberID='76418',status='1',packetTime='2022-12-25 22:10:33'

For dataTable, memberID(primary) is indexed.

UPDATE#3

EXPLAIN SELECT m.id AS uID,m.tkn,m.email FROM memberTable m WHERE m.tkn='$subTkn'

id      select_type table   partitions  type    possible_keys   key     key_len ref     rows    filtered    Extra
76418   SIMPLE      m       NULL        const   tkn             tkn     42      const   1       100.00      NULL    



EXPLAIN INSERT into dataTable (memberID,status,packetTime ) VALUES ('76418','1','2022-12-25 22:10:33') ON DUPLICATE KEY UPDATE memberID='76418',status='1',packetTime='2022-12-25 22:10:33'

id  select_type table       partitions  type    possible_keys   key     key_len ref     rows    filtered    Extra
1   INSERT  dataTable   NULL        ALL     NULL            NULL    NULL    NULL    NULL    NULL        NULL

UPDATE 4

show variables like "transact%";

transaction_alloc_block_size    8192    
transaction_allow_batching  OFF 
transaction_isolation   REPEATABLE-READ 
transaction_prealloc_size   4096    
transaction_read_only   OFF 
transaction_write_set_extraction    OFF 

optimizer_switch

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off,prefer_ordering_index=on

Answer 1

For those who reach this part of the island, here is how my issue was resolved. I had a continuous barrage of queries that were IODKU. Apparently, this is causing row locks in innoDB. Had to change the code, and query to UPDATE (unless insert is required), and all seems back to normal.

Thanks, Bill, DimitryC for asking insightful questions.

For Rick:

1. show create table dataTable

CREATE TABLE `dataTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `memberID` bigint(15) NOT NULL,
  `action` int(1) NOT NULL DEFAULT '0',
  `timeOfLastEvent` timestamp NULL DEFAULT NULL,
  `timeSinceLastEvent` int(11) DEFAULT NULL,

  ....../*Few additional columns, mostly INT or DECIMAL with defaults set to 0*/..........
 
  `status` int(1) NOT NULL DEFAULT '1',
  `packetTime` timestamp NULL DEFAULT NULL COMMENT 'UTC/GMT time when data is received',
  PRIMARY KEY (`id`),
  UNIQUE KEY `memberID` (`memberID`)
) ENGINE=InnoDB AUTO_INCREMENT=28051225912 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2. You were doing IODKU when UPDATE was all that was needed?

NO, My use case was that new inserts could happen, so I used IODKU. But this was very rare compared to the updates. So, I now have two separate modules, a new one to handle inserts if needed.

Another reason that pointed towards this: If you look at my original post, you will see the IODKU that I am using. I posted the EXPLAIN as requested by Dimitry, and you can see that IODKU was not using indexes, where as update did use index. So, not really knowing if that would fix the issue, I changed to simple UPDATE, and the db load just got back to normal levels as mysql5.6 days. Now, I dont know if this is the right solution, or whether I got lucky.

3. Does the SELECT have a FOR UPDATE?

NO, I was not using a select for update. I was blindly using IODKU, as it was working without any issues on 5.6.

4. SAMPLE IODKU code is same as in the question.

    INSERT into dataTable (memberID,status,packetTime )
          VALUES ('76418','1','2022-12-25 22:10:33')
          ON DUPLICATE KEY UPDATE 
             memberID='76418',
             status='1',
             packetTime='2022-12-25 22:10:33'
   

5. UPDATED code.

    UPDATE dataTable SET
      status='1',
      packetTime='2022-12-25 22:10:33'
    where memberID='76418'`
   

6. When I ran "SHOW ENGINE INNODB STATUS" WITH the IODKU, I could see a lot of

    ---TRANSACTION 114650874007, ACTIVE 3 sec inserting
    mysql tables in use 1, locked 1
    12 lock struct(s), heap size 1136, 5 row lock(s)
    MySQL thread id 49055080, OS thread handle 22829943932672, query id 200466639 172.31.55.242 application update
      INSERT into dataTable (memberID,status,packetTime)
      VALUES ('10425151','1','2022-12-27 21:01:24')
      ON DUPLICATE KEY UPDATE
        memberID='10425151',
        status='1',
        packetTime='2022-12-25 21:01:24'
   

But with just UPDATE, the engine status query is much shorter, and none of the above entries.

Hope this helps, Rick.

And thanks to the two who voted to close this question. Very helpful.