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