Hi, When creating indices, MyIsam needs a big sort buffer (See MySQL Manual: " myisam_sort_buffer_size: The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. ").
I don't know how works ISAM tables, but you can try setting your sort buffer to a high value. ;) Alexis -----Original Message----- From: William Baker [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 3 de Novembro de 2003 19:37 To: [EMAIL PROTECTED] Subject: Re: performance while creating indexes Now why didn't I think of a single alter table....that should certainly improve things. I'll give it a try. bbaker > > > William Baker wrote: > >> I am using a pentium4-2GHz machine with Linux-RH9 installed and 1GB >> RAM. The database is on a dedicated SCSI drive with an Adaptec >> UltraScsi3 controller which shows 40MHz bus connecting the 10K-RPM >> disks. (Fairly new, fairly capable, low-end server grade.) >> >> I have a 2GB datafile with 10 indexes. Each of those indexes takes >> about 1.5 hrs to build (total of 15 hours). Any suggestions for >> reducing build time ... preferrably to around 10 minutes or less? I >> could even live with 20 minutes for each. (Our current system uses >> ISAM style indexed data files. It has a utility that can rebuild the >> indexes for this file in about an hour, so the performance I am >> looking for should be possible on this hardware.) I am using innodb >> and have raised buffer limits with the /etc/my.cnf method and >> verified that the new parameters were indeed loaded using "show >> variables". Indexes have several segments, ie, a typical index >> definition: >> >> CREATE UNIQUE INDEX `TRANSACT_INDEX03` ON `TRANSACT` (`LOC`, >> `PRODUCT`, `ITEM_NO`, `TRAN_DATE`, `RECNUM`) >> >> This is a generic 4.0.16 server. During the last 6 months we have >> been testing our applications and I have tried different >> configurations on different disks and OS's. The 1.5 hrs/index is >> fairly typical. >> >> Copy of my.cfg follows. >> >> bbaker >> >> >> [mysqld] >> datadir=/raid/db >> socket=/raid/mysql/mysql.sock >> >> set-variable = innodb_buffer_pool_size=128M >> set-variable = innodb_additional_mem_pool_size=20M >> innodb_log_group_home_dir=/raid/db >> innodb_log_arch_dir=/raid/db >> #set-variable = innodb_log_files_in_group=3 >> #set-variable = innodb_log_file_size=32M >> #set-variable = innodb_log_buffer_size=8M >> >> innodb_flush_log_at_trx_commit=0 >> set-variable = innodb_lock_wait_timeout=50 >> >> skip-locking >> set-variable = max_connections=200 >> set-variable = read_buffer_size=8M >> set-variable = sort_buffer=8M >> set-variable = key_buffer_size=256M >> >> [mysql.server] >> user=mysql >> basedir=/raid/db >> >> [safe_mysqld] >> >> [mysql.server] >> user=mysql >> basedir=/raid >> >> [safe_mysqld] >> err-log=/raid/mysqld.log >> pid-file=/var/run/mysqld/mysqld.pid >> >> >> >> > We add all our indicies at once with a single alter table command. > We are using MYISAM tables, though. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
