MySQL Forums
Forum List  »  Performance

Create index speed
Posted by: Pavel Vasek
Date: January 19, 2005 03:34PM

Hi all,
I do not understand what actually happens on my computer. :(
W2003 Server 4xIntel Xeon2.8Ghz CPUs, 2G RAM
MySQL 4.1.9 standard Windows installation.
table definition:
CREATE TABLE `hdd2` (
`ModelpointId` int(10) unsigned NOT NULL default '0',
'TimestepId` datetime NOT NULL default '0000-00-00 00:00:00',
`Var1` float default NULL,
`Var2` float default NULL,
`Var3` float default NULL,
`Var4` float default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Table contains 2GB of data. (77695872 rows)

when I run command:
"alter table hdd2 add index aaa (ModelpointId);"
MySQL copy all 2GB to temporary table and creates index successfully within cca 6 minutes.

I hope that this time is OK for this table, but when I run command:
"alter table hdd2 add primary key (ModelpointId, TimestepId);"
It takes 3.5 hours.
I do not believe that it is ok, is it?
Actually MySQL copy cca 900MB to temporary table and creates cca 600MB temporary index file in relatively short time.
After that server is doing "nothing" (CPUs are near 0%, memory usage slowly increases but HDDs are working) for those 3.5 hours.
After that time indexes are created successfully.

I play with MySQL settings like:
myisam_max_sort_file_size=1M
myisam_max_extra_sort_file_size=1M
myisam_sort_buffer_size=1G
key_buffer_size=672M
read_buffer_size=2M
read_rnd_buffer_size=8M
sort_buffer_size=2M
myisam_repair_threads = 2
skip-bdb
skip-innodb

But no matter what "show variables" says (even same extreme settings)
everything goes the same way (as described)

Anybody with similar experience?
Can anybody guess what is actually wrong with my setup?
Any advice how to setup MySQL reasonably?

Thank you for spending time reading this long question :)

Options: ReplyQuote


Subject
Views
Written By
Posted
Create index speed
3524
January 19, 2005 03:34PM


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.