I apologize in advance if a similar question has been asked. I've been digging through Google and StackExchange trying to find an answer but now I'm stuck and stumped. I am still relatively new in the world of DB Management.
Backgorund
I created a local program that connects to my database to store information pulled from internet API's. Normally, it would update 8,000+ records in around 9-10 minutes (including computation time in my program). However, after updating my MySql server to version 8.0.20, I noticed my run times have skyrocketed to 29-30 minutes.
My program updates 2 tables each iteration and I wrapped the execution block of code in a stop watch timer class to debug run speeds. My database is set up as follows:
+------------------------+
| diagnostic |
| fundamentals |
| historicalData |
| messages |
| info |
+------------------------+
5 rows in set (0.00 sec)
Initially, the first few hundred records will all update quickly with relatively similar speeds:
Sample output from software:
API CONNECTION: 0.760000 seconds (<- Response time from the API)
-DB Fetch time: 0.177000 seconds (<- Query that selects data from the info table)
-EXECUTION SPEED: 0.186000 seconds (<- Query to update the info table query)
-EXECUTION SPEED: 0.843000 seconds (<- Query to updates the historicalData table)
Updated batch in: 3.716000 seconds (<- Total time)
After about 800 records, the problem starts to compound:
Sample output from software:
API CONNECTION: 0.747000 seconds (<- Response time from the API)
-DB Fetch time: 0.153000 seconds (<- Query that selects data from the info table)
-EXECUTION SPEED: 0.310000 seconds (<- Query to update the info table query)
-EXECUTION SPEED: 7.858000 seconds (<- Query to updates the historicalData table)
Updated batch in: 10.612000 seconds (<- Total time)
The slowest speeds have been 15-20 seconds per query while updating the historicalData table.
(I went with this approach to diagnose speeds because the data in the queries as so long it would slow down MySQL Workbench and be insanely difficult to reproduce.)
Given the above, I focused on the historicalData table:
mysql> show columns in historicalData;
+-----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| symbol | char(10) | NO | PRI | NULL | |
| 12_month | json | YES | | NULL | |
| 6_month | json | YES | | NULL | |
| 3_month | json | YES | | NULL | |
| 5_day | json | YES | | NULL | |
| my_last_updated | mediumtext | YES | | NULL | |
+-----------------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
The query syntax being used are the same between both info and historicalData tables, which are programmatically generated. For example, this is a query used to updated historicalData:
INSERT INTO `historicalData` (`symbol`, `5_day`, `12_month`, `6_month`, `3_month`, `my_last_updated`)
VALUES ( ## Appropriate values, including a very long JSON string as `12_month` that would spam out the question on StackExchange ## )
AS new(`symbol`, `5_day`, `12_month`, `6_month`, `3_month`, `my_last_updated`)
ON DUPLICATE KEY UPDATE `symbol`=new.symbol,`5_day`=new.5_day,`12_month`=new.12_month,`6_month`=new.6_month,`3_month`=new.3_month,`my_last_updated`=new.my_last_updated;
Memory:
My server runs 8G of ram on a 3.4 processor, and doesn't run any other services aside from MySql.
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 5497159680
Dictionary memory allocated 672373
Buffer pool size 327680
Free buffers 199182
Database pages 128470
Old database pages 47382
Modified db pages 2099
Pending reads 0
Pending writes: LRU 0, flush list 6, single page 0
Pages made young 767195, not young 205843
0.00 youngs/s, 0.00 non-youngs/s
Pages read 183858, created 554254, written 1094269
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 46 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 128470, unzip_LRU len: 0
I/O sum[6180]:cur[96], unzip sum[0]:cur[0]
sys.innodb_buffer_stats_by_table:
+-----------------------------------------------+------------+------------+-------+--------------+-----------+-------------+
| object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+-----------------------------------------------+------------+------------+-------+--------------+-----------+-------------+
| diagnostic | 416.00 KiB | 326.05 KiB | 26 | 0 | 22 | 74 |
| fts_0000000000000af9_0000000000000a25_index_1 | 16.00 KiB | 5.32 KiB | 1 | 0 | 1 | 85 |
| fts_0000000000000af9_0000000000000a25_index_2 | 544.00 KiB | 251.52 KiB | 34 | 0 | 34 | 4580 |
| fts_0000000000000af9_0000000000000a25_index_3 | 176.00 KiB | 136.38 KiB | 11 | 0 | 11 | 2499 |
| fts_0000000000000af9_0000000000000a25_index_4 | 160.00 KiB | 118.54 KiB | 10 | 0 | 10 | 2257 |
| fts_0000000000000af9_0000000000000a25_index_5 | 224.00 KiB | 176.48 KiB | 14 | 0 | 14 | 3282 |
| fts_0000000000000af9_0000000000000a25_index_6 | 96.00 KiB | 60.51 KiB | 6 | 0 | 6 | 1168 |
| fts_0000000000000af9_0000000000000a2c_index_1 | 16.00 KiB | 5.07 KiB | 1 | 0 | 1 | 79 |
| fts_0000000000000af9_0000000000000a2c_index_2 | 288.00 KiB | 238.78 KiB | 18 | 0 | 18 | 4336 |
| fts_0000000000000af9_0000000000000a2c_index_3 | 176.00 KiB | 136.38 KiB | 11 | 0 | 11 | 2499 |
| fts_0000000000000af9_0000000000000a2c_index_4 | 160.00 KiB | 118.54 KiB | 10 | 0 | 10 | 2257 |
| fts_0000000000000af9_0000000000000a2c_index_5 | 224.00 KiB | 176.48 KiB | 14 | 0 | 14 | 3282 |
| fts_0000000000000af9_0000000000000a2c_index_6 | 96.00 KiB | 60.51 KiB | 6 | 0 | 6 | 1168 |
| fts_0000000000000af9_being_deleted | 16.00 KiB | 0 bytes | 1 | 0 | 1 | 0 |
| fts_0000000000000af9_being_deleted_cache | 16.00 KiB | 0 bytes | 1 | 0 | 1 | 0 |
| fts_0000000000000af9_config | 16.00 KiB | 233 bytes | 1 | 0 | 1 | 6 |
| fts_0000000000000af9_deleted | 16.00 KiB | 0 bytes | 1 | 0 | 1 | 0 |
| fts_0000000000000af9_deleted_cache | 16.00 KiB | 0 bytes | 1 | 0 | 1 | 0 |
| fundamentals | 2.31 MiB | 2.06 MiB | 148 | 0 | 148 | 6301 |
| historicalData | 3.30 MiB | 2.73 MiB | 211 | 198 | 83 | 8502 |
| messages | 64.00 KiB | 31.69 KiB | 4 | 0 | 2 | 87 |
| info | 3.53 MiB | 3.17 MiB | 226 | 205 | 212 | 8459 |
+-----------------------------------------------+------------+------------+-------+--------------+-----------+-------------+
Sample from mysql-slow.log
# Time: 2020-05-06T00:31:52.558595Z
# User@Host: me[me] @ host [IP Address] Id: 383
# Query_time: 13.655712 Lock_time: 0.039945 Rows_sent: 0 Rows_examined: 0
SET timestamp=1588725098;
INSERT INTO `historicalData` (`symbol`, `5_day`, `12_month`, `6_month`, `3_month`, `my_last_updated`) VALUES ... etc etc
What I've tried:
Short of rolling back to 8.0.19, I've tried:
- Restarting the server (duh)
- Updated INSERT ON DUPLICATE KEY query to conform to 8.0.20 (previous query VALUES was depreciated)
- Increasing buffer pool memory (Run times are always the same, regardless if it's set to 20%, 50%, or 70% of system memory)
- Increasing memory buffer pool instances (no change)
- Removed partitions in historicalData (reduced time by 1-2 minutes)
- Changed columns from Json to medium text (no change)
- Reducing the payload to historicalData (for example, writing the query to updating just columns
12_month,my_last_updated) (no change) - Drastically, I reinstalled MySQL 8.0.20 (no change)
I'm not sure what else to try and I'm a little frustrated this happened when updating to 8.0.20. Everything ran smoothly until the day I updated to 8.0.20 (literally the first instance the program ran after the update these symptoms appeared). I hope that someone here may have had the same or similar problem or have an idea how to bring my the performance back to around where I was with 8.0.19.
EDIT As requested, below is the explain data
EXPLAIN INSERT... ON DUPLICATE KEY...
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | INSERT | historicalData | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
SHOW CREATE TABLE historicalData;
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| historicalData | CREATE TABLE `historicalData` (
`symbol` char(10) NOT NULL,
`12_month` json DEFAULT NULL,
`6_month` json DEFAULT NULL,
`3_month` json DEFAULT NULL,
`5_day` json DEFAULT NULL,
`my_last_updated` mediumtext,
PRIMARY KEY (`symbol`),
UNIQUE KEY `symbol` (`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> explain select count(*) from historicalData;
+----+-------------+----------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| 1 | SIMPLE | historicalData | NULL | index | NULL | symbol | 10 | NULL | 1 | 100.00 | Using index |
+----+-------------+----------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
EXPLAIN SELECT ..., preferably for both versions. AndSHOW CREATE TABLE.new.; is this inside aTRIGGER??new.based off the publish notes stating the usualINSERT ... VALUES(...) opt_alias ON DUPLICATE KEY UPDATEsyntax was depreciated, and recommended going to the syntax above: dev.mysql.com/worklog/task/?id=6312. I'll post anEXPLAIN SELECT, does it help trouble shoot despite the error being on aninsertcommand?