1

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 |
+----+-------------+----------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
  • Please provide EXPLAIN SELECT ..., preferably for both versions. And SHOW CREATE TABLE. – Rick James May 7 at 1:34
  • I see new.; is this inside a TRIGGER?? – Rick James May 7 at 1:39
  • I went to new. based off the publish notes stating the usual INSERT ... VALUES(...) opt_alias ON DUPLICATE KEY UPDATE syntax was depreciated, and recommended going to the syntax above: dev.mysql.com/worklog/task/?id=6312. I'll post an EXPLAIN SELECT, does it help trouble shoot despite the error being on an insert command? – miliardo18 May 7 at 21:14
1

Sorry, but I am going to be blunt.

Abandon the use of collecting data into ever-growing strings (communists, JSON, TEXT, etc). This goes especially for stock quote historical data. It works much better to use one row per quote.

(No, I can't explain why it slowed down. However, if you can provide a reproducible test case, please file a bug report at bugs.mysql.com )

More...

I see some FULLTEXT files; but there is no FT index in this table, correct?

Since a PRIMARY KEY is a UNIQUE KEY, this is redundant and should be removed:

UNIQUE KEY `symbol` (`symbol`)
| improve this answer | |
  • seconded on both points. FYI no default value changes that could of explained this. release note maybe json validation library upgrade but that's a guess and you should try to get a bug report done. – danblack May 7 at 5:18
  • @Rick I appreciate the bluntness... The string isn't ever growing, part of the computations my program does is remove data from the string that is older than 12 months. The data received from the API is in JSON format, so I try to leave it in a raw format in the database. – miliardo18 May 7 at 21:20
  • @danblack I poured through the notes too and didn't find anything relevant. I tried altering the table to not take a json string (assuming maybe the indexing of the json string was causing an issue) and it has the same results. – miliardo18 May 7 at 21:22
  • @miliardo18 - I added to my Answer. (But I still have no explanation.) – Rick James May 7 at 21:59
  • @RickJames Thanks, I appreciate your help with this. I'm going to reach out to Oracle on their forums and see how to report this as a bug. I'll post what I find here. I appreciate your time! – miliardo18 May 8 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.