MySQL Forums
Forum List  »  MyISAM

Slow Update on large table myisam
Posted by: PEDRO BOZA VILLALOBOS
Date: January 15, 2014 11:45PM

Hi!
I am using mysql x64 in a PC with 16 GB of RAM on a x64 OS. Using 1 harddrive for the OS and APPs and a 2nd hardrive for the mysql data.

Also have two myisam tables:
One with 5 million records using 1.2 GB of disk space.
Second with 1 million records using 400 MB of disk space.

I need to run this update

update salarios2, nacimientos set salarios2.aaaanac = nacimientos.aaaanac, salarios2.mmnac = nacimientos.mmnac, salarios2.sexo = nacimientos.sexo
where salarios2.cedula = nacimientos.cedula;

The update take forever until timeout limit reach, actually 600 seconds.

This is the tables structures:
CREATE TABLE `nacimientos` (
 `cedula` int(9) unsigned NOT NULL,
 `cedulap` int(9) unsigned NOT NULL,
 `cedulam` int(9) unsigned NOT NULL,
 `aaaanac` smallint(4) unsigned NOT NULL DEFAULT '0',
 `mmnac` tinyint(4) DEFAULT '0',
 `ddnac` tinyint(4) DEFAULT '0',
 `sexo` tinyint(4) NOT NULL DEFAULT '0',
 `nacionalidad` tinyint(4) NOT NULL,
 `defuncion` enum('0','1') NOT NULL DEFAULT '0',
 `ape1` varchar(13) DEFAULT NULL,
 `ape2` varchar(13) DEFAULT NULL,
 `nombre` varchar(30) DEFAULT NULL,
 `nompadre` varchar(29) DEFAULT NULL,
 `nommadre` varchar(29) DEFAULT NULL,
 `lugarnac` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`cedula`),
 KEY `ape1` (`ape1`),
 KEY `ape2` (`ape2`),
 KEY `nombre` (`nombre`),
 KEY `cedulap` (`cedulap`),
 KEY `cedulam` (`cedulam`),
 KEY `sexo` (`sexo`),
 KEY `aaaanac` (`aaaanac`),
 KEY `defuncion` (`defuncion`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `salarios2` (
 `patrono` bigint(18) unsigned NOT NULL,
 `nompatrono` tinytext NOT NULL,
 `cedula` bigint(13) unsigned NOT NULL DEFAULT '0',
 `nombre` tinytext NOT NULL,
 `ape1` tinytext NOT NULL,
 `ape2` tinytext NOT NULL,
 `salario` int(8) unsigned NOT NULL DEFAULT '0',
 `provincia` tinyint(3) NOT NULL DEFAULT '0',
 `canton` tinyint(4) NOT NULL DEFAULT '0',
 `distrito` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `aaaanac` smallint(4) unsigned NOT NULL DEFAULT '0',
 `mmnac` tinyint(4) NOT NULL DEFAULT '0',
 `sexo` tinyint(4) NOT NULL DEFAULT '0',
 `tipopat` tinyint(4) NOT NULL DEFAULT '1',
 `ttel` tinyint(4) NOT NULL DEFAULT '0',
 `ttelf` tinyint(1) NOT NULL DEFAULT '0',
 `ttelc` tinyint(1) NOT NULL DEFAULT '0',
 `gam` tinyint(4) NOT NULL DEFAULT '0',
 `periodos` smallint(3) unsigned NOT NULL DEFAULT '0',
 KEY `cedula` (`cedula`),
 KEY `patrono` (`patrono`),
 KEY `aaaanac` (`aaaanac`),
 KEY `salario` (`salario`),
 KEY `mmnac` (`mmnac`),
 KEY `tipopat` (`tipopat`),
 KEY `ttel` (`ttel`),
 KEY `sexo` (`sexo`),
 KEY `provincia` (`provincia`),
 KEY `canton` (`canton`),
 KEY `distrito` (`distrito`),
 KEY `gam` (`gam`),
 KEY `periodos` (`periodos`),
 KEY `ttelf` (`ttelf`),
 KEY `ttelc` (`ttelc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And here is the my.ini file

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
datadir="e:/MySQLData/data\"
character-set-server=utf8
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="BOZAPE-SERVER.log"
slow-query-log=1
slow_query_log_file="BOZAPE-SERVER-slow.log"
long_query_time=10
log-error="BOZAPE-SERVER.err"
max_connections=500
query_cache_size=512M
table_open_cache=2048
tmp_table_size=32M
thread_cache_size=50
myisam_max_sort_file_size=100G
myisam_sort_buffer_size = 1G
key_buffer_size=2G
read_buffer_size=64K
read_rnd_buffer_size = 512M
sort_buffer_size=256K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=69M
innodb_log_file_size=48M
innodb_thread_concurrency=9
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=16M
max_connect_errors=100
open_files_limit=65535
query_cache_type=1
sort_buffer_size=256K
table_definition_cache=1024
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
query_cache_limit = 2G
log-queries-not-using-indexes
max_heap_table_size = 32M
tmpdir = "G:/mysql_temp"

Any idea on how to improve this update.

Thanks!

P.D. I think didnt miss anything xD

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Update on large table myisam
3701
January 15, 2014 11:45PM
1854
January 16, 2014 08:10PM
2163
January 16, 2014 10:59PM


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.