Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.2
-
None
-
OS : Ubuntu 20.04.6 LTS
Processor : 2 OCPU's
Model name : AMD EPYC 7J13 64-Core Processor
RAM : 16 GB
Description
Update query calls for v11.4.2 have very high response time when compared to v10.1.25.
Similar Environment has been configured for both versions - v11.4.2 & v10.1.25.
Please find below profiling results where sample update statement is consuming 0.45 millisec for v10.1.25 and 30.17 millisec for v11.4.2.
v10.1.25:
| Query_ID | Duration | Query |
| 1 | 0.00044663 | UPDATE Statement; |
SHOW PROFILE FOR QUERY 1:
| Status | Duration |
| starting | 0.000077 |
| checking permissions | 0.000007 |
| Opening tables | 0.000021 |
| After opening tables | 0.000005 |
| System lock | 0.000004 |
| Table lock | 0.000003 |
| init | 0.000043 |
| updating | 0.000213 |
| end | 0.000011 |
| query end | 0.000024 |
| closing tables | 0.000004 |
| Unlocking tables | 0.000006 |
| freeing items | 0.000006 |
| updating status | 0.000021 |
| cleaning up | 0.000003 |
15 rows in set (0.00 sec)
v11.4.2:
| Query_ID | Duration | Query |
| 1 | 0.03017175 | UPDATE Statement |
SHOW PROFILE FOR QUERY 1:
| Status | Duration |
| Starting | 0.000068 |
| checking permissions | 0.000006 |
| Opening tables | 0.028674 |
| checking permissions | 0.000015 |
| After opening tables | 0.000004 |
| init for update | 0.000009 |
| init | 0.000016 |
| Updating main table | 0.000007 |
| init | 0.000017 |
| System lock | 0.000005 |
| table lock | 0.000001 |
| init | 0.000024 |
| init for update | 0.000054 |
| Updating | 0.000025 |
| End of update loop | 0.000006 |
| End of update loop | 0.000001 |
| Query end | 0.000002 |
| Commit | 0.000042 |
| Query end | 0.000011 |
| closing tables | 0.000002 |
| Unlocking tables | 0.000002 |
| closing tables | 0.000002 |
| Query end | 0.000697 |
| Starting cleanup | 0.000004 |
| Freeing items | 0.000004 |
| Updating status | 0.000029 |
| Reset for next command | 0.000446 |
27 rows in set (0.000 sec)
Below workarounds were tried to check potential performance improvement for update call latency:
1. table_open_cache increased from 1200 to 3500.
2. table_definition_cache increased from 1200 to 3500.
3. Configured the Open Files limit for hard and soft to 65535.
4. Increased innodb_log_buffer_size from 8388608 to 16777216.
5. Enabled below values in config file:
- optimizer_switch = 'rowid_filter=off'
- innodb_file_per_table = ON
- innodb_default_row_format = dynamic
- innodb_flush_neighbors = 0
- innodb_io_capacity = 1000
- innodb_io_capacity_max = 10000
6. Upgraded tcmalloc library to 2.15 on v11.4.2 server and removed external-locking option from mysql startup option.
7. Changed the values for optimizer_search_depth from 62 to 0.
8. Changed the values for Handler_read_prev from 3005619 to 0 and Handler_read_rnd from 72214157 to 0.
9. Set optimizer_switch to 'index_condition_pushdown=off'.
10. Enabled below values in config file: - innodb_file_per_table = ON
- innodb_default_row_format = dynamic
- innodb_stats_on_metadata = OFF
11. Added read, write io threads: - innodb_read_io_threads = 8
- innodb_write_io_threads = 8