MySQL Forums
Forum List  »  MyISAM

Query running from past two days.
Posted by: sandeep thakur
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
--------------
++++++

Options: ReplyQuote


Subject
Views
Written By
Posted
Query running from past two days.
2700
October 03, 2013 12:15AM
1447
October 05, 2013 09:38AM


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.