MySQL Forums
Forum List  »  MyISAM

large table indexing problem
Posted by: geonjae yu
Date: January 26, 2012 08:12PM

Hi,

I had run the alter table command to add index on a large table - MyISAM, about 70million rows, 12GB file size - 2 day ago.
but, this command is running for 171735 seconds as below.
Connect 172299 copy to tmp table alter table Results_a04_20120118 add index idxCpc(cpcCode)

most cpu is idling, 10GB free memory... load average 1.00, 1.00, 1.00

Strangely, when I restart mysql, running time is shortened considerably.

Partitioning is not used.

Please help me!!

MySQL Server version is 5.1.55 Source distribution.

The system is
2.13Gh x 2 cpu(4cores), total 8 core,
64GB memory,
2TB SATA x 10 (RAID 5)

my.cnf is
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld_safe]
open-files-limit = 8192
[mysqld]
skip-name-resolve
skip-external-locking
skip-character-set-client-handshake
default-storage-engine = MyISAM
character-set-server = utf8
collation-server = utf8_general_ci
event_scheduler = on
general-log = 0
slow-query-log = 0
long_query_time = 5
back_log = 100
max_connections = 500
max_connect_errors = 500
connect_timeout = 30
wait_timeout = 30
max_allowed_packet = 64M
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 16M
read_buffer_size = 16M
sort_buffer_size = 32M
read_rnd_buffer_size = 32M
thread_cache_size = 16
thread_concurrency = 16
table_cache = 2048
query_cache_type = 1
query_cache_size = 128M
concurrent_insert = 1
key_buffer_size = 6G
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 1G
delay-key-write = OFF
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 32M
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_log_buffer_size = 1M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_lock_wait_timeout = 30
innodb_max_dirty_pages_pct = 90
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 2G
sort_buffer_size = 1G
read_buffer = 1G
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout



Edited 5 time(s). Last edit at 01/26/2012 06:30PM by geonjae yu.

Options: ReplyQuote


Subject
Views
Written By
Posted
large table indexing problem
3528
January 26, 2012 08:12PM
1800
January 27, 2012 08:38PM
1693
January 29, 2012 07:39PM


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.