Slawomir Lisznianski <public@paramay.com> wrote:
> System is Linux (2.6.9, SMP 8-core), MySQL is 6.0.3 lx86_64 glibc23.

This is an ALPHA version!

> I have 32gb of system memory. I set the myisam_sort_buffer_size to 28gb.
>
> I'm trying to create two indexes in my 50GB (1 bln rows) MyISAM
> database. After a while, the CPU utilization of mysqld drops to ~1%. At
> this point, the memory footprint of the daemon is 20gb. However, the
> index creation is progressing _very_ slowly (it's been running for over
> 30 hours now).

It is running I/O bound now. If this would be a local disk, iostat -x
would show you disk utilization near 100%. With NAS the bottleneck
could be anything - network, disks, NFS implementation.

> The database files (and temp files) live on a PanFS filesystem (NFS).
> I'm almost certain the fact that my database lives on a NAS device is
> the cause of the slowness. However...

All network file systems suck. Especially with the workload created by
databases. NFS is a bad choice for MySQL.

First of all you should run SHOW PROCESSLIST on the MySQL server.
Does it show "repair with key cache"? This is the slower variant of
recovering (or creating) a myISAM index. And it needs a big key_buffer.
What you want to see is "repair by sorting".

Your myisam_sort_buffer is clearly oversized. What matters much more is
the setting of myisam_max_sort_file_size. This must be at least the
projected size of the .MYI file - or MyISAM will fall back to "repair
with key cache".

http://dev.mysql.com/doc/refman/6.0/...rt_file_ size


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/