Sorry,Recently, I am implementing a major version upgrade for several sets of databases… I have encountered a suspected bug, but oralce’s bug handling of the community version looks very slow. Can you help me see it? Thank you.
Environment :
OS:rhel 6.5 x64
MySQL:5.7.21-23
Melloc:jemalloc 5.1.0
Replication:master <=> master(MTR 4 threads) + keepalived
Problem :
The first node(primary) is running normally.
The secondary node(read only), memory usage has been rising.
The EVENT “sql/Log_event”(slave_sql), memory usage has been rising.
[HTML]
root@150203 [(none)]> select THREAD_ID, EVENT_NAME, COUNT_ALLOC, SUM_NUMBER_OF_BYTES_ALLOC/1024/1024, CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
±----------±---------------------------±------------±------------------------------------±---------------------------------------+
| THREAD_ID | EVENT_NAME | COUNT_ALLOC | SUM_NUMBER_OF_BYTES_ALLOC/1024/1024 | CURRENT_NUMBER_OF_BYTES_USED/1024/1024 |
±----------±---------------------------±------------±------------------------------------±---------------------------------------+
| 1 | memory/innodb/buf_buf_pool | 240 | 31455.00000000 | 31455.00000000 |
| 507 | memory/sql/Log_event | 18229868 | 7494.16463280 | 7494.08160686 | <<< is it normal?
| 1 | memory/innodb/hash0hash | 92 | 2356.76754761 | 897.66287231 |
| 1 | memory/innodb/os0event | 5899209 | 765.12567902 | 765.12567902 |
| 506 | memory/innodb/std | 37745532 | 1584.49591064 | 191.84428406 |
| 1 | memory/innodb/log0log | 11 | 64.01335907 | 64.01335907 |
| 508 | memory/innodb/std | 8794028 | 393.10567474 | 38.30834961 |
| 507 | memory/innodb/std | 1762132 | 73.96929932 | 8.95660400 |
| 509 | memory/mysys/IO_CACHE | 6 | 8.03128242 | 8.03128242 |
| 508 | memory/mysys/IO_CACHE | 6 | 8.03128242 | 8.03128242 |
±----------±---------------------------±------------±------------------------------------±---------------------------------------+
10 rows in set (0.03 sec)
[/HTML]
[HTML]
root@150203 [(none)]> select thread_id, user, current_allocated, total_allocated from sys.memory_by_thread_by_current_bytes;
±----------±--------------------------------±------------------±----------------+
| thread_id | user | current_allocated | total_allocated |
±----------±--------------------------------±------------------±----------------+
| 1 | sql/main | 32.42 GiB | 34.08 GiB |
| 507 | sql/slave_sql | 7.42 GiB | 12.10 GiB | <<< The slave_sql thread consumes memory constantly, is it normal?
| 506 | sql/slave_io | 198.30 MiB | 100.58 GiB |
| 38914 | root@localhost | 8.53 MiB | 21.64 MiB |
| 510 | sql/slave_worker | 8.06 MiB | 8.21 MiB |
| 511 | sql/slave_worker | 42.74 KiB | 42.90 KiB |
| 39514 | root@10.25.150.203 | 16.84 KiB | 28.59 MiB |
| 39515 | root@10.25.150.203 | 16.84 KiB | 40.66 MiB |
| 39516 | root@10.25.150.203 | 16.84 KiB | 24.54 MiB |
| 3225 | repl@10.25.150.202 | 13.05 KiB | 63.97 KiB |
| 31 | innodb/buf_dump_thread | 192 bytes | 1.74 MiB |
| 30 | innodb/srv_worker_thread | 0 bytes | 0 bytes |
| 39 | sql/signal_handler | 0 bytes | 0 bytes |
| 40 | sql/compress_gtid_table | 0 bytes | 0 bytes |
| 2 | sql/thread_timer_notifier | 0 bytes | 0 bytes |
| 3 | innodb/io_read_thread | 0 bytes | 33.24 KiB |
| 4 | innodb/io_ibuf_thread | 0 bytes | 0 bytes |
| 5 | innodb/io_read_thread | 0 bytes | 27.16 KiB |
| 6 | innodb/io_log_thread | 0 bytes | 0 bytes |
| 7 | innodb/io_read_thread | 0 bytes | 5.67 KiB |
| 8 | innodb/io_read_thread | 0 bytes | 50.03 KiB |
| 9 | innodb/io_read_thread | 0 bytes | 44.77 KiB |
| 10 | innodb/io_read_thread | 0 bytes | 11.35 KiB |
| 11 | innodb/io_read_thread | 0 bytes | 22.70 KiB |
| 12 | innodb/io_read_thread | 0 bytes | 22.70 KiB |
| 13 | innodb/io_write_thread | 0 bytes | 1.68 MiB |
| 14 | innodb/io_write_thread | 0 bytes | 384.44 KiB |
| 15 | innodb/io_write_thread | 0 bytes | 650.33 KiB |
| 16 | innodb/io_write_thread | 0 bytes | 223.01 KiB |
| 17 | innodb/io_write_thread | 0 bytes | 294.46 KiB |
| 18 | innodb/io_write_thread | 0 bytes | 218.54 KiB |
| 19 | innodb/io_write_thread | 0 bytes | 274.54 KiB |
| 20 | innodb/io_write_thread | 0 bytes | 242.21 KiB |
| 21 | innodb/page_cleaner_thread | 0 bytes | 0 bytes |
| 23 | innodb/srv_error_monitor_thread | 0 bytes | 0 bytes |
| 24 | innodb/srv_lock_timeout_thread | 0 bytes | 0 bytes |
| 25 | innodb/srv_monitor_thread | 0 bytes | 18.92 MiB |
| 26 | innodb/srv_master_thread | 0 bytes | 14.24 MiB |
| 27 | innodb/srv_worker_thread | 0 bytes | 0 bytes |
| 28 | innodb/srv_purge_thread | 0 bytes | 0 bytes |
| 29 | innodb/srv_worker_thread | 0 bytes | 0 bytes |
| 32 | innodb/dict_stats_thread | -160 bytes | 218.36 KiB |
| 509 | sql/slave_worker | -5122994 bytes | 58.06 MiB |
| 508 | sql/slave_worker | -7894888734 bytes | 28.29 GiB |
±----------±--------------------------------±------------------±----------------+
44 rows in set (0.28 sec)
[/HTML]