Joseph,
Thanks for the quick answer.
Very nice to know that adding an index forces a rebuild of all indices!
(Side note -- I was going to configure the database with a minimal set
of indices, and then watch to see how people use the database, and then
add indices on popular columns.)
I did not notice a lot of i/o activity, but will run vmstat for a while
and gather statistics
The machine has 1GB of RAM.
Here is the configuration:
bash-2.04$ more /etc/my.cnf
[mysqld]
datadir=/data/dp14.a/data/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
There is NO SETTING for key_buffer_size -- what value do you suggest?
Thanks,
Chris
Joseph Bueno wrote:
Chris Stoughton wrote:
I have a table with a few million rows, wth 633 columns. I want to
create 10 inidices on this table, six single-column and four
two-column inidices. The database will be loaded once and remain
static, and queried many times. Please note that this is a small
prototype for the actual database, which will have 40 times more rows.
I used two strategies to create these tables:
1. Create the indices in the create table statement
2. Load the table, and then use the create index statement for
each index.
With Strategy 1, the loading started nicely using the load data
infile command to load approx. 2000 records at a time. It took 3
seconds for each load data infile command. After a few hundred
load data infile commands, however, the time increased to two
minutes per command.
With Strategy 2, the loading went from start to finish at 3 seconds
for each load data infile command. But now, creating each index is
taking longer. Here is a log:
Fri Sep 20 15:17:43 CDT 2002 create index objId on bestTsObj (objId)
Fri Sep 20 15:35:51 CDT 2002 create index fieldId on bestTsObj (fieldId)
Fri Sep 20 15:56:02 CDT 2002 create index targetObjId on bestTsObj
(targetObjId)
Fri Sep 20 16:20:02 CDT 2002 create index ra on bestTsObj (ra)
Fri Sep 20 16:49:16 CDT 2002 create index decl on bestTsObj (decl)
Fri Sep 20 17:53:38 CDT 2002 create index u on bestTsObj (u)
Fri Sep 20 18:42:52 CDT 2002 create index g on bestTsObj (g)
Fri Sep 20 22:04:25 CDT 2002 create index r on bestTsObj (r)
Sat Sep 21 10:06:44 CDT 2002 create index i on bestTsObj (i)
Sat Sep 21 19:31:52 CDT 2002 create index z on bestTsObj (z)
Sun Sep 22 15:45:06 CDT 2002 create index ug on bestTsObj (u,g)
The first index was created in a reasonable amount of time (18
minutes), but it is taking longer to create more indices.
With both strategies, no process is swapping heavily, and mysqld is
not consuming available CPU cycles efficiently.
Can you suggest how I can build multiple indices on large tables
efficiently? Will it help to create a narrow table with only the
quantities I want to use in indices?
We have installed mysql via rpm:
bash-2.04$ mysqladmin
mysqladmin Ver 8.18 Distrib 3.23.36, for redhat-linux-gnu on i386
bash-2.04$ uname -r
2.4.18-xfs-1.1
bash-2.04$
What diagnostics do you suggest I run?
Thanks
Hi,
You should use strategy #1 since each time you create a new index, all
index are rebuilt (this is why each new index is taking longer in
strategy#2).
Since mysqld is not consuming CPU nor swapping, it is probably doing a
lot of disc I/Os. You can verify that with vmstat utility.
You didn't specify how much RAM you have and what mysql configuration
you are using (check /etc/my.cnf). You should pay special attention to
'key_buffer_size' parameter value since it has a major impact on index
performance.
Regards,
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
