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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
