MySQL Forums
Forum List  »  Performance

large multiple table update taking a very long time, help!
Posted by: Jason Hines
Date: April 18, 2009 05:08PM

Hi: I am running a multiple table update in two scenarios. Scenario one only takes 1.5 hours while scenario two is taking what seems like forever.

I need to do selects based on class_id, and i need to do a complete table scan (i.e. i need to work with all records). By doing the update instead of joins i was able to double my performance, but since scenario two is not working I am currently stuck and I am on a tight deadline.

If you know of another query to accomplish the same concept, or what is wrong with this one, please share. I am not a mysql expert.

The servers I have available are quad core cpus with 16GB ram.

Each table is very large at 70,143,307 records. Tables are created with following options: ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci




scenario one:

mysql> desc table1;
+----------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+----------------+
| field1 | bigint(20) unsigned | NO | MUL | NULL | |
| field2 | bigint(20) unsigned | NO | MUL | 0 | |
| field3 | text | YES | | NULL | |
| field4 | bigint(20) | NO | MUL | 0 | |
| field5 | bigint(20) | NO | | 0 | |
| field6 | bigint(20) | NO | | 0 | |
| field7 | bigint(20) | NO | | 0 | |
| field8 | bigint(20) | NO | MUL | 0 | |
| field9 | bigint(20) | NO | | 0 | |
| field10 | bigint(20) | NO | MUL | 0 | |
| field11 | bigint(20) | NO | | 0 | |
| field12 | bigint(20) | NO | | 0 | |
| field13 | bigint(20) | NO | MUL | 0 | |
| field14 | bigint(20) | NO | | 0 | |
| field15 | bigint(20) | NO | MUL | 0 | |
| field16 | bigint(20) | NO | MUL | 0 | |
| field17 | bigint(20) | NO | | 0 | |
| field18 | bigint(20) | NO | | 0 | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| class_id | int(10) unsigned | YES | MUL | NULL | |
+----------------------+---------------------+------+-----+---------+----------------+

mysql> desc table2;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| FK_id | int(10) unsigned | NO | | NULL | |
| class_id | int(10) unsigned | NO | MUL | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+------------+------------------+------+-----+---------+----------------+

UPDATE IGNORE `table1`, `table2` SET `table1`.`class_id` = `table2`.`class_id` WHERE `table1`.`id` = `table2`.`FK_id`;




scenario two:

mysql> desc table3;
+------------------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+-------------------+----------------+
| field1 | smallint(6) | NO | MUL | NULL | |
| field2 | varchar(10) | NO | MUL | NULL | |
| field3 | bigint(20) unsigned | YES | MUL | NULL | |
| field4 | tinytext | YES | MUL | NULL | |
| field5 | text | YES | | NULL | |
| field6 | varchar(16) | YES | | NULL | |
| field7 | varchar(16) | YES | | NULL | |
| field8 | varchar(16) | YES | | NULL | |
| field9 | varchar(2) | YES | | NULL | |
| field10 | tinytext | YES | | NULL | |
| field11 | varchar(16) | YES | | NULL | |
| field12 | varchar(2) | YES | | NULL | |
| field13 | varchar(6) | YES | | NULL | |
| field14 | tinytext | YES | MUL | NULL | |
| field15 | varchar(2) | YES | MUL | NULL | |
| field16 | bigint(10) | YES | | NULL | |
| field17 | bigint(10) | YES | | NULL | |
| field18 | text | YES | | NULL | |
| field19 | float | YES | | NULL | |
| field20 | float | YES | | NULL | |
| field21 | tinytext | YES | | NULL | |
| field22 | int(11) | YES | | NULL | |
| field23 | text | YES | | NULL | |
| field24 | text | YES | | NULL | |
| field25 | text | YES | | NULL | |
| field26 | text | YES | | NULL | |
| field27 | text | YES | | NULL | |
| field28 | text | YES | | NULL | |
| field29 | text | YES | | NULL | |
| field30 | text | YES | | NULL | |
| field31 | text | YES | | NULL | |
| field32 | text | YES | | NULL | |
| field33 | text | YES | | NULL | |
| field34 | tinyint(1) | YES | | 0 | |
| field35 | timestamp | YES | | CURRENT_TIMESTAMP | |
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| class_id | int(10) unsigned | YES | MUL | NULL | |
+------------------+---------------------+------+-----+-------------------+----------------+

mysql> desc table4;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| FK_id | int(10) unsigned | NO | | NULL | |
| class_id | int(10) unsigned | NO | MUL | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+----------------+------------------+------+-----+---------+----------------+

UPDATE IGNORE `table3`, `table4` SET `table3`.`class_id` = `table4`.`class_id` WHERE `table3`.`id` = `table4`.`FK_id`;

In this scenario the query runs for about 20 hours before it even starts to update reference tables.




Some server variables:

[mysqld]
skip-locking
max_connections = 1000
port = 3306
socket = /var/lib/mysql/mysql.sock
key_buffer_size = 4096M
max_allowed_packet = 4096M
group_concat_max_len = 2048M
table_cache = 2048
sort_buffer_size = 4096M
read_buffer_size = 4096M
read_rnd_buffer_size = 4096M
myisam_sort_buffer_size = 2048M
thread_cache_size = 16
query_cache_size = 2048M
thread_concurrency = 16
bulk_insert_buffer_size = 2048M
log-bin = mysql-bin
server-id = 1
skip-bdb

Options: ReplyQuote


Subject
Views
Written By
Posted
large multiple table update taking a very long time, help!
4276
April 18, 2009 05:08PM


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.