MySQL Forums :: Performance :: large multiple table update taking a very long time, help!


Advanced Search

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! 4154 Jason Hines 04/18/2009 05:08PM
Re: large multiple table update taking a very long time, help! 2062 Rick James 04/18/2009 06:18PM
Re: large multiple table update taking a very long time, help! 2117 Jason Hines 04/18/2009 08:36PM
Re: large multiple table update taking a very long time, help! 1964 Jason Hines 04/18/2009 08:39PM
Re: large multiple table update taking a very long time, help! 1973 Jason Hines 04/19/2009 07:59AM
Re: large multiple table update taking a very long time, help! 2292 Rick James 04/19/2009 11:50AM
Re: large multiple table update taking a very long time, help! 2533 Jason Hines 04/19/2009 12:45PM


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.