-
Notifications
You must be signed in to change notification settings - Fork 437
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[BUG] 0.15.2-6, mydumper cat not finish #1372
Comments
Hi @lyvivian0077, can you test with latest prerelease? |
Hi @lyvivian0077, can you test with latest release? |
Hi @davidducos Sorry for replying so late. I have tested with
dump
start log
date
tail log
the dump is start in |
Hi @lyvivian0077, |
HI @davidducos I've check again log file. It looks like the threads are working fine. And you can find, after
log
|
Hi @lyvivian0077 , |
Hi @davidducos I am tested it again. may it can help to you. am not sure, but it looks like not all child threads are working together. another problem. in processlist, the threads are always hold the same DB. don't know why. LOG[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Sleep | 0 | | NULL |
| 108316839 | root | localhost | bbb | Sleep | 0 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 0 | | NULL |
| 108316841 | root | localhost | ddd | Sleep | 0 | | NULL |
| 108316843 | root | localhost | eee | Sleep | 0 | | NULL |
| 108316838 | root | localhost | fff | Sleep | 0 | | NULL |
| 108709396 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316840 | root | localhost | ggg | Sleep | 0 | | NULL |
| 108316835 | root | localhost | hhh | Sleep | 0 | | NULL |
| 108316403 | root | localhost | NULL | Sleep | 3814 | | NULL |
| 108316844 | root | localhost | iii | Sleep | 0 | | NULL |
| 108316837 | root | localhost | jjj | Sleep | 0 | | NULL |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
[root@backup]# date
Mon Mar 25 14:32:34 JST 2024
[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Sleep | 0 | | NULL |
| 108316839 | root | localhost | bbb | Sleep | 0 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 0 | | NULL |
| 108316841 | root | localhost | ddd | Sleep | 0 | | NULL |
| 108316843 | root | localhost | eee | Sleep | 0 | | NULL |
| 108316838 | root | localhost | fff | Sleep | 0 | | NULL |
| 108316840 | root | localhost | ggg | Sleep | 0 | | NULL |
| 108316835 | root | localhost | hhh | Sleep | 0 | | NULL |
| 108710463 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316403 | root | localhost | NULL | Sleep | 3826 | | NULL |
| 108316844 | root | localhost | iii | Sleep | 0 | | NULL |
| 108316837 | root | localhost | jjj | Sleep | 0 | | NULL |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
[root@mbackup]#
[root@backup]# date
Mon Mar 25 14:34:33 JST 2024
[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Sleep | 1 | | NULL |
| 108316839 | root | localhost | bbb | Sleep | 0 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 1 | | NULL |
| 108722109 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316841 | root | localhost | ddd | Sleep | 0 | | NULL |
| 108316843 | root | localhost | eee | Sleep | 1 | | NULL |
| 108316838 | root | localhost | fff | Sleep | 0 | | NULL |
| 108316840 | root | localhost | ggg | Sleep | 0 | | NULL |
| 108316835 | root | localhost | hhh | Sleep | 0 | | NULL |
| 108316403 | root | localhost | NULL | Sleep | 3948 | | NULL |
| 108316844 | root | localhost | iii | Sleep | 1 | | NULL |
| 108316837 | root | localhost | jjj | Sleep | 0 | | NULL |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
[root@backup]#
[root@backup]# date
Mon Mar 25 14:41:37 JST 2024
[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Query | 6 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ `asset_meta_asset_id`,`asset_meta_type`,`asset_meta_vchar`,`asset_meta_vchar_idx`,`asset_meta_vdatetime`,`asset_meta_vdatetime_idx`,`asset_meta_vinteger`,`asset_meta_vinteger_idx`,`asset_meta_vfloat`,`asset_meta_vfloat_idx`,`asset_meta_vblob`,`asset_meta_vclob` FROM `some_db3`.`some_db3_table` |
| 108316839 | root | localhost | bbb | Sleep | 0 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 0 | | NULL |
| 108316841 | root | localhost | ddd | Sleep | 0 | | NULL |
| 108316843 | root | localhost | eee | Query | 0 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ `ID`,`post_author`,`post_date`,`post_date_gmt`,`post_content`,`post_title`,`post_excerpt`,`post_status`,`comment_status`,`ping_status`,`post_password`,`post_name`,`to_ping`,`pinged`,`post_modified`,`post_modified_gmt`,`post_content_filtered`,`post_parent`,`guid`,`menu_order`,`post_type`,`post_mime_type`,`comment_count` FROM `some_db1`.`wp_posts` |
| 108316838 | root | localhost | fff | Sleep | 0 | | NULL |
| 108316840 | root | localhost | ggg | Query | 0 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `ID`,`post_author`,`post_date`,`post_date_gmt`,`post_content`,`post_title`,`post_excerpt`,`post_status`,`comment_status`,`ping_status`,`post_password`,`post_name`,`to_ping`,`pinged`,`post_modified`,`post_modified_gmt`,`post_content_filtered`,`post_parent`,`guid`,`menu_order`,`post_type`,`post_mime_type`,`comment_count` FROM `some_db2`.`wp_posts` |
| 108763557 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316835 | root | localhost | hhh | Query | 0 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `meta_id`,`post_id`,`meta_key`,`meta_value` FROM `some_db3`.`wp_postmeta` |
| 108316403 | root | localhost | NULL | Sleep | 4356 | | NULL |
| 108316844 | root | localhost | iii | Sleep | 0 | | NULL |
| 108316837 | root | localhost | jjj | Sleep | 0 | | NULL |
+-----------+------+-----------+-----------------------+---------+------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@backup]#
[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Sleep | 1 | | NULL |
| 108316839 | root | localhost | bbb | Sleep | 1 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 1 | | NULL |
| 108772834 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316841 | root | localhost | ddd | Sleep | 1 | | NULL |
| 108316843 | root | localhost | eee | Sleep | 1 | | NULL |
| 108316838 | root | localhost | fff | Sleep | 1 | | NULL |
| 108316840 | root | localhost | ggg | Sleep | 1 | | NULL |
| 108316835 | root | localhost | hhh | Sleep | 1 | | NULL |
| 108316403 | root | localhost | NULL | Sleep | 4444 | | NULL |
| 108316844 | root | localhost | iii | Query | 1 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `id`,`date_time`,`post_title`,`log_data`,`type` FROM `some_db`.`somt_table` |
| 108316837 | root | localhost | jjj | Query | 1 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `submit_time`,`form_name`,`field_name`,`field_value`,`field_order`,`file` FROM `some_db2`.`some_table` |
+-----------+------+-----------+-----------------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@backup]#
[root@backup]# date
Mon Mar 25 14:50:18 JST 2024
[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Sleep | 0 | | NULL |
| 108316839 | root | localhost | bbb | Sleep | 0 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 0 | | NULL |
| 108316841 | root | localhost | ddd | Sleep | 0 | | NULL |
| 108316843 | root | localhost | eee | Sleep | 0 | | NULL |
| 108316838 | root | localhost | fff | Sleep | 0 | | NULL |
| 108316840 | root | localhost | ggg | Sleep | 0 | | NULL |
| 108820053 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316835 | root | localhost | hhh | Sleep | 0 | | NULL |
| 108316403 | root | localhost | NULL | Sleep | 4890 | | NULL |
| 108316844 | root | localhost | iii | Sleep | 0 | | NULL |
| 108316837 | root | localhost | jjj | Sleep | 0 | | NULL |
+-----------+------+-----------+-----------------------+---------+------+-----------+-----------------------------------------------------------------------------------------+
[root@backup]#
[root@backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+-----------------------+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+-----------------------+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 108316836 | root | localhost | aaa | Sleep | 0 | | NULL |
| 109042139 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316839 | root | localhost | bbb | Sleep | 0 | | NULL |
| 108316845 | root | localhost | ccc | Sleep | 0 | | NULL |
| 108316841 | root | localhost | ddd | Sleep | 0 | | NULL |
| 108316843 | root | localhost | eee | Query | 31 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `id`,`network_id`,`site_id`,`username`,`ip`,`level`,`event_type`,`details`,`stacktrace`,`created` FROM `some_db`.`some_table` |
| 108316838 | root | localhost | fff | Sleep | 0 | | NULL |
| 108316840 | root | localhost | ggg | Query | 253 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `id`,`searchkey`,`searchkey_disp`,`ranking_at`,`page`,`saerchRank`,`shopName`,`shopURL`,`itemName`,`itemURL`,`itemImg`,`itemPrice`,`review`,`created_at` FROM `some_db2`.`some_table` |
| 108316835 | root | localhost | hhh | Sleep | 0 | | NULL |
| 108316403 | root | localhost | NULL | Sleep | 7089 | | NULL |
| 108316844 | root | localhost | iii | Query | 72 | Sending to client | SELECT /*!40001 SQL_NO_CACHE */ `ASIN`,`BrowseNode`,`LastUpdate` FROM `some_db3`.`somt_table` |
| 108316837 | root | localhost | jjj | Sleep | 1 | | NULL |
+-----------+------+-----------+-----------------------+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@backup]#
[root@backup]# mysql -e "SELECT * FROM performance_schema.threads where PROCESSLIST_USER = 'root'"
+-----------+---------------------------+------------+----------------+------------------+------------------+-----------------------+---------------------+------------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+---------------------------+------------+----------------+------------------+------------------+-----------------------+---------------------+------------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| 108316881 | thread/sql/one_connection | FOREGROUND | 108316835 | root | localhost | hhh | Sleep | 0 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24897 |
| 108316882 | thread/sql/one_connection | FOREGROUND | 108316836 | root | localhost | aaa | Query | 0 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ `ID`,`post_author`,`post_date`,`post_date_gmt`,`post_content`,`post_title`,`post_excerpt`,`post_status`,`comment_status`,`ping_status`,`post_password`,`post_name`,`to_ping`,`pinged`,`post_modified`,`post_modified_gmt`,`post_content_filtered`,`post_parent`,`guid`,`menu_order`,`post_type`,`post_mime_type`,`comment_count` FROM `some_db`.`some_table` | NULL | NULL | YES | YES | Socket | 24899 |
| 108316883 | thread/sql/one_connection | FOREGROUND | 108316837 | root | localhost | jj | Sleep | 0 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24871 |
| 108316884 | thread/sql/one_connection | FOREGROUND | 108316839 | root | localhost | bbb | Query | 0 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ `meta_id`,`post_id`,`meta_key`,`meta_value` FROM `some_db2`.`some_table` | 1 | NULL | YES | YES | Socket | 24918 |
| 108316885 | thread/sql/one_connection | FOREGROUND | 108316840 | root | localhost | ggg | Sleep | 0 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24919 |
| 108316886 | thread/sql/one_connection | FOREGROUND | 108316838 | root | localhost | fff | Sleep | 0 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24920 |
| 108316887 | thread/sql/one_connection | FOREGROUND | 108316841 | root | localhost | dddd | Sleep | 0 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24921 |
| 108316889 | thread/sql/one_connection | FOREGROUND | 108316843 | root | localhost | eee | Sleep | 0 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24923 |
| 108316890 | thread/sql/one_connection | FOREGROUND | 108316845 | root | localhost | ccc | Sleep | 0 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24924 |
| 108316891 | thread/sql/one_connection | FOREGROUND | 108316844 | root | localhost | iii | Sleep | 0 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24925 |
| 108316449 | thread/sql/one_connection | FOREGROUND | 108316403 | root | localhost | NULL | Sleep | 9176 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24802 |
| 109282542 | thread/sql/one_connection | FOREGROUND | 109282496 | root | localhost | NULL | Query | 0 | Sending data | SELECT * FROM performance_schema.threads where PROCESSLIST_USER = 'root' | NULL | NULL | YES | YES | Socket | 11395 |
+-----------+---------------------------+------------+----------------+------------------+------------------+-----------------------+---------------------+------------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
[root@backup]#
# Finally, get hanged thread
[root@mysql636 backup]# mysql -e "select * from information_schema.processlist where user = 'root' and host = 'localhost'"
+-----------+------+-----------+--------------------+---------+-------+-----------+-----------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----------+------+-----------+--------------------+---------+-------+-----------+-----------------------------------------------------------------------------------------+
| 108316836 | root | localhost | xxxxxxxxxxxx | Sleep | 755 | | NULL |
| 108316839 | root | localhost | xxxxxxxxxxxx | Sleep | 756 | | NULL |
| 108316845 | root | localhost | xxxxxxxxxxxx | Sleep | 755 | | NULL |
| 108316841 | root | localhost | xxxxxxxxxxxx | Sleep | 755 | | NULL |
| 108316843 | root | localhost | yyyyyyyyyyyyyyyyyy | Sleep | 755 | | NULL |
| 109597467 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist where user = 'root' and host = 'localhost' |
| 108316838 | root | localhost | xxxxxxxxxxxx | Sleep | 755 | | NULL |
| 108316840 | root | localhost | zzzzzzzzz | Sleep | 755 | | NULL |
| 108316835 | root | localhost | wwwwwwwwwwwwwwwwww | Sleep | 755 | | NULL |
| 108316403 | root | localhost | NULL | Sleep | 12404 | | NULL |
| 108316844 | root | localhost | xxxxxxxxxxxx | Sleep | 755 | | NULL |
| 108316837 | root | localhost | bbbbbbbbbb | Sleep | 755 | | NULL |
+-----------+------+-----------+--------------------+---------+-------+-----------+-----------------------------------------------------------------------------------------+
[root@backup]# mysql -e "SELECT * FROM performance_schema.threads where PROCESSLIST_USER = 'root'"
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
| 108316881 | thread/sql/one_connection | FOREGROUND | 108316835 | root | localhost | wwwwwwwwwwwwwwwwww | Sleep | 799 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24897 |
| 108316882 | thread/sql/one_connection | FOREGROUND | 108316836 | root | localhost | xxxxxxxxxxxx | Sleep | 799 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24899 |
| 108316883 | thread/sql/one_connection | FOREGROUND | 108316837 | root | localhost | bbbbbbbbbb | Sleep | 799 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24871 |
| 108316884 | thread/sql/one_connection | FOREGROUND | 108316839 | root | localhost | xxxxxxxxxxxx | Sleep | 800 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24918 |
| 108316885 | thread/sql/one_connection | FOREGROUND | 108316840 | root | localhost | zzzzzzzzz | Sleep | 799 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24919 |
| 108316886 | thread/sql/one_connection | FOREGROUND | 108316838 | root | localhost | xxxxxxxxxxxx | Sleep | 799 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24920 |
| 108316887 | thread/sql/one_connection | FOREGROUND | 108316841 | root | localhost | xxxxxxxxxxxx | Sleep | 799 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24921 |
| 108316889 | thread/sql/one_connection | FOREGROUND | 108316843 | root | localhost | yyyyyyyyyyyyyyyyyy | Sleep | 799 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24923 |
| 108316890 | thread/sql/one_connection | FOREGROUND | 108316845 | root | localhost | xxxxxxxxxxxx | Sleep | 799 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24924 |
| 108316891 | thread/sql/one_connection | FOREGROUND | 108316844 | root | localhost | xxxxxxxxxxxx | Sleep | 799 | NULL | NULL | 1 | NULL | YES | YES | Socket | 24925 |
| 108316449 | thread/sql/one_connection | FOREGROUND | 108316403 | root | localhost | NULL | Sleep | 12448 | NULL | NULL | NULL | NULL | YES | YES | Socket | 24802 |
| 109602478 | thread/sql/one_connection | FOREGROUND | 109602432 | root | localhost | NULL | Query | 0 | Sending data | SELECT * FROM performance_schema.threads where PROCESSLIST_USER = 'root' | NULL | NULL | YES | YES | Socket | 32055 |
+-----------+---------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+
[root@backup]#
[root@backup]# mysql -e 'show engine innodb status \G'
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2024-03-25 17:01:25 0x7faf0ae35700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1137997 srv_active, 0 srv_shutdown, 675 srv_idle
srv_master_thread log flush and writes: 1138672
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 262143156
OS WAIT ARRAY INFO: signal count 164107049
RW-shared spins 0, rounds 54247945, OS waits 1810937
RW-excl spins 0, rounds 7907680443, OS waits 165436303
RW-sx spins 75847, rounds 440439, OS waits 5158
Spin rounds per wait: 54247945.00 RW-shared, 7907680443.00 RW-excl, 5.81 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2024-03-13 09:56:38 0x7faf0b73b700 Error in foreign key constraint creation for table `some_table`.`#sql-202a_7597b2`.
A foreign key constraint of name `some_db`.`some_table`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-25 15:16:47 0x7faf88b62700
*** (1) TRANSACTION:
TRANSACTION 31776520795, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 108977248, OS thread handle 140389783811840, query id 14405673698 <IP> <HOST> update
xxxxxx
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 920135 page no 10655 n bits 224 index s_ステータス of table `some_db`.`some_table` trx id 31776520795 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex e5898de697a5e4bd9ce6a5ade7a2bae8aa8de38197e381bee38197e3819f; asc ;;
1: len 4; hex 8000000d; asc ;;
*** (2) TRANSACTION:
TRANSACTION 421956709698336, ACTIVE 1 sec fetching rows
mysql tables in use 7, locked 7
11867 lock struct(s), heap size 1089744, 154804 row lock(s)
MySQL thread id 108977289, OS thread handle 140391889643264, query id 14405672804 <IP> <HOST> Sending data
xxxx
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 920135 page no 10655 n bits 224 index s_ステータス of table `some_db`.`some_table` trx id 421956709698336 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex e5898de697a5e4bd9ce6a5ade7a2bae8aa8de38197e381bee38197e3819f; asc
1: len 4; hex 8000000d; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 920135 page no 16978 n bits 368 index dt_日付時刻 of table `some_db`.`some_table` trx id 421956709698336 lock mode S waiting
Record lock, heap no 298 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 5; hex 99b2f2f42e; asc .;;
1: len 4; hex 8004e468; asc h;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 31778699505
Purge done for trx's n:o < 31773755638 undo n:o < 0 state: running but idle
History list length 1078325
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421956709671888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709701072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709699248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709696512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709694688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709692864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709689216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709686480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709673712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709666416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709668240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709674624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709688304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709678272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709675536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709677360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709669152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709684656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709672800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709722960, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709711104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709679184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709683744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709690128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709691040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709670064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709697424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709733904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709664592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709713840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709698336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709787712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709768560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709756704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709706544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709700160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709682832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709695600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709693776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709681920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709731168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709665504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709707456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709667328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709705632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709670976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709715664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709708368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421956709691952, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31775541903, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316845, OS thread handle 140389867460352, query id 14454999167 localhost root
Trx read view will not see trx with id >= 31773755631, sees < 31773755623
---TRANSACTION 31775541829, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316841, OS thread handle 140389882058496, query id 14455001402 localhost root
Trx read view will not see trx with id >= 31773755628, sees < 31773755623
---TRANSACTION 31773755631, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316844, OS thread handle 140389783000832, query id 14455001121 localhost root
Trx read view will not see trx with id >= 31773755632, sees < 31773755623
---TRANSACTION 31773755629, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316843, OS thread handle 140389895575296, query id 14454999822 localhost root
Trx read view will not see trx with id >= 31773755630, sees < 31773755623
---TRANSACTION 31773755628, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316838, OS thread handle 140389887194880, query id 14455000794 localhost root
Trx read view will not see trx with id >= 31773755629, sees < 31773755623
---TRANSACTION 31773755627, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316840, OS thread handle 140389880436480, query id 14454998872 localhost root
Trx read view will not see trx with id >= 31773755628, sees < 31773755623
---TRANSACTION 31773755626, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316839, OS thread handle 140389784622848, query id 14454998409 localhost root
Trx read view will not see trx with id >= 31773755627, sees < 31773755623
---TRANSACTION 31773755625, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316836, OS thread handle 140389886654208, query id 14454999472 localhost root
Trx read view will not see trx with id >= 31773755626, sees < 31773755623
---TRANSACTION 31773755624, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316837, OS thread handle 140389885843200, query id 14455000456 localhost root
Trx read view will not see trx with id >= 31773755625, sees < 31773755623
---TRANSACTION 31773755623, ACTIVE 12755 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 108316835, OS thread handle 140389791921920, query id 14455000150 localhost root
Trx read view will not see trx with id >= 31773755624, sees < 31773755624
--------
FILE I/O
-------- |
A few things has changed on the latest prerelease. |
Describe the bug
from version 0.15.1-3, mydumper dump MySQL is very slow.
also tested by 0.15.2-6.
when start mydumper, it cost 20hours and not finished.
but when i use
mydumper 0.12.1, built against MySQL 5.7.34-37
, only need 30 minutes.To Reproduce
Command executed:
/usr/bin/mydumper --events --routines --triggers --success-on-1146 --statement-size=8000000 --chunk-filesize=1024 --kill-long-queries --complete-insert --use-savepoints --threads=10 --less-locking --trx-consistency-only --logfile "logfile" --compress -v 3 --outputdir="desc_path"
Log
0.15.2-6 mydumper log
0.12.1 mydumper log
Environment:
The text was updated successfully, but these errors were encountered: