Query running from past two days.
Posted by: sandeep thakur
Date: October 03, 2013 12:15AM
Date: October 03, 2013 12:15AM
Hi,
I've two table in MyISAM engine. I've executed an update query, but still its state is like "sending data" from past 24+ hrs. Below are the table desc and record details. Can someone help me out in order to get rid of this issue.
++++++
Table1:-
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| s_id | bigint(20) | NO | | NULL | |
| sb_id | int(11) | NO | | NULL | |
| val_1 | varchar(100) | NO | MUL | NULL | |
| old_id | varchar(100) | NO | | NULL | |
| filepath | varchar(255) | NO | | NULL | |
| in_t1 | tinyint(4) | NO | MUL | 0 | |
| val2 | varchar(250) | NO | | NULL | |
| size | varchar(255) | NO | | 0 | |
| pr_tbl | varchar(100) | NO | | n | |
+---------------+--------------+------+-----+---------+----------------+
Total Records:- 12486272
distinct val_1:- 902326
Table2:-
+--------------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------------+------+-----+---------+-------+
| key | binary(36) | NO | MUL | NULL | |
| name | text | NO | | NULL | |
| val_1 | char(32) | NO | MUL | NULL | |
| val_1_1 | char(40) | NO | | NULL | |
| val_1_p | varchar(256) | NO | | NULL | |
| size | bigint(20) unsigned | NO | | NULL | |
| mf | text | YES | | NULL | |
| h_t | text | YES | | NULL | |
| val_p | text | NO | | NULL | |
+--------------+--------------------------------+------+-----+---------+-------+
Total records:- 68934522
Query:-
update table1 u join table2 f on u.val_1 = f.val_1 set u.size = f.size;
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:63180
db: test
Command: Query
Time: 160797
State: Sending data
Info: update table1 u join table2 f on u.val_1 = f.val_1 set u.size = f.size
*************************** 2. row ***************************
Variables:-
mysql> show variables like "%key%";
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| delay_key_write | ON |
| foreign_key_checks | ON |
| have_rtree_keys | YES |
| key_buffer_size | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_seeks_for_key | 4294967295 |
| ssl_key | |
+--------------------------+------------+
9 rows in set (0.00 sec)
mysql> show variables like "%read%";
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 17 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| net_read_timeout | 30 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| pseudo_thread_id | 119 |
| read_buffer_size | 1048576 |
| read_only | OFF |
| read_rnd_buffer_size | 8388608 |
| thread_cache_size | 128 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
+-----------------------------------------+---------------------------+
21 rows in set (0.00 sec)
--------------
mysql Ver 14.14 Distrib 5.5.33, for Win64 (x86)
Connection id: 119
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.5.33-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Uptime: 1 day 20 hours 50 min 24 sec
Threads: 20 Questions: 41224 Slow queries: 2 Opens: 52 Flush tables: 1 Open tables: 4 Queries per second avg: 0.255
--------------
++++++
I've two table in MyISAM engine. I've executed an update query, but still its state is like "sending data" from past 24+ hrs. Below are the table desc and record details. Can someone help me out in order to get rid of this issue.
++++++
Table1:-
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| s_id | bigint(20) | NO | | NULL | |
| sb_id | int(11) | NO | | NULL | |
| val_1 | varchar(100) | NO | MUL | NULL | |
| old_id | varchar(100) | NO | | NULL | |
| filepath | varchar(255) | NO | | NULL | |
| in_t1 | tinyint(4) | NO | MUL | 0 | |
| val2 | varchar(250) | NO | | NULL | |
| size | varchar(255) | NO | | 0 | |
| pr_tbl | varchar(100) | NO | | n | |
+---------------+--------------+------+-----+---------+----------------+
Total Records:- 12486272
distinct val_1:- 902326
Table2:-
+--------------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------------+------+-----+---------+-------+
| key | binary(36) | NO | MUL | NULL | |
| name | text | NO | | NULL | |
| val_1 | char(32) | NO | MUL | NULL | |
| val_1_1 | char(40) | NO | | NULL | |
| val_1_p | varchar(256) | NO | | NULL | |
| size | bigint(20) unsigned | NO | | NULL | |
| mf | text | YES | | NULL | |
| h_t | text | YES | | NULL | |
| val_p | text | NO | | NULL | |
+--------------+--------------------------------+------+-----+---------+-------+
Total records:- 68934522
Query:-
update table1 u join table2 f on u.val_1 = f.val_1 set u.size = f.size;
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:63180
db: test
Command: Query
Time: 160797
State: Sending data
Info: update table1 u join table2 f on u.val_1 = f.val_1 set u.size = f.size
*************************** 2. row ***************************
Variables:-
mysql> show variables like "%key%";
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| delay_key_write | ON |
| foreign_key_checks | ON |
| have_rtree_keys | YES |
| key_buffer_size | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_seeks_for_key | 4294967295 |
| ssl_key | |
+--------------------------+------------+
9 rows in set (0.00 sec)
mysql> show variables like "%read%";
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 17 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| net_read_timeout | 30 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| pseudo_thread_id | 119 |
| read_buffer_size | 1048576 |
| read_only | OFF |
| read_rnd_buffer_size | 8388608 |
| thread_cache_size | 128 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
+-----------------------------------------+---------------------------+
21 rows in set (0.00 sec)
--------------
mysql Ver 14.14 Distrib 5.5.33, for Win64 (x86)
Connection id: 119
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.5.33-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: cp850
Conn. characterset: cp850
TCP port: 3306
Uptime: 1 day 20 hours 50 min 24 sec
Threads: 20 Questions: 41224 Slow queries: 2 Opens: 52 Flush tables: 1 Open tables: 4 Queries per second avg: 0.255
--------------
++++++
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.