SEO

System is Linux (2.6.9, SMP 8-core), MySQL is 6.0.3 lx86_64 glibc23.

I have 32gb of system memory. I set the myisam_sort_buffer_size to 28gb.
There are no users connected to this instance of the database other than me.

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).

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...

I was inspecting mysqld LWPs and I'm seeing LOTS of calls to
pread/pwrite (see below) in one of the threads. The reads/writes are
small (1Kb each) which is probably trashing the NAS optimized for bulk ops.

1) Is there a way to adjust the read/write buffer to a larger size?

2) Would using mmap feature help in this case?

Thank you


pwrite(33, "\3r"..., 1024, 17206342656) = 1024 <0.000011>
pread(33, "\2\8vYmvG"..., 1024, 2657943552) = 1024 <0.000010>
pwrite(33, "\3\7S03WdxOr"..., 1024, 17206326272) = 1024 <0.000011>
pread(33, "\3\7\'vYdjvV"..., 1024, 10466302976) = 1024 <0.031036>
pwrite(33, "\2\^WOOSto"..., 1024, 10396731392) = 1024 <0.000015>
pwrite(33, "\2\KWMLeMF"..., 1024, 17159898112) = 1024 <0.000016>
pwrite(33, "\3\EoWE"..., 1024, 17058997248) = 1024 <0.000012>
pwrite(33, "\3\tO"..., 1024, 17230129152) = 1024 <0.000012>
pwrite(33, "\3nn"..., 1024, 5174621184) = 1024 <0.000015>
pwrite(33, "\2\p"..., 1024, 17254358016) = 1024 <0.000023>
pwrite(33, "\3\VrEJXM"..., 1024, 13116532736) = 1024 <0.000012>
pwrite(33, "\3\TfE"..., 1024, 17099184128) = 1024 <0.000013>
pwrite(33, "\3\013VmJoO"..., 1024, 17058935808) = 1024 <0.000011>
pwrite(33, "\3\VmYEBv"..., 1024, 2624415744) = 1024 <0.000012>
pwrite(33, "\3\VleqtD"..., 1024, 5898042368) = 1024 <0.000012>
pwrite(33, "\3rMQHR"..., 1024, 17058927616) = 1024 <0.000012>
pwrite(33, "\2\lnNB"..., 1024, 17182015488) = 1024 <0.000012>
pwrite(33, "\3bhzL"..., 1024, 303576064) = 1024 <0.000011>
pread(33, "\3\6\tvdWtOu"..., 1024, 14297678848) = 1024 <0.117056>
pwrite(33, "\2\62~VghekP"..., 1024, 16869562368) = 1024 <0.000013>
pwrite(33, "\3nfemDR"..., 1024, 7683888128) = 1024 <0.000012>
pwrite(33, "\3\\10\244VedtYD"..., 1024, 11206996992) = 1024 <0.000015>

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/

Axel Schwenke wrote:
>> System is Linux (2.6.9, SMP 8-core), MySQL is 6.0.3 lx86_64 glibc23.
>
> This is an ALPHA version!

well, since you brought it up, it's the only version that doesn't seg
fault whenever mysqld process exceeds 4gb. I tried 5.0 and 5.1 before
trying out 6.x -- all with the same config. I experienced identical
problem to this one:

http://bugs.mysql.com/bug.php?id=9239


> Does it show "repair with key cache"?

yes

> recovering (or creating) a myISAM index. And it needs a big key_buffer.
> What you want to seek is "repair by sorting".

ok


> Your myisam_sort_buffer is clearly oversized.

does it hurt or it's just irrelevant at this point?

> What matters much more is
> the setting of myisam_max_sort_file_size.

I have it set to 50g...

mysql> show variables like "myisam%";

+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 53687091200 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 30064771072 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
+---------------------------+---------------+


mysql> show variables like "%buffer%";
+---------------------------+-------------+
| Variable_name | Value |
+---------------------------+-------------+
| key_buffer_size | 21474836480 |
| myisam_sort_buffer_size | 30064771072 |
| net_buffer_length | 1048576 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2093056 |
| read_rnd_buffer_size | 8388600 |
| sort_buffer_size | 4294967288 |
| sql_buffer_result | OFF |

> projected size of the .MYI file - or MyISAM will fall back to "repair
> with key cache".

It did fall back to repair with keycache. What else can be done? Thanks
for all your help.


Cheers,
Slawomir

First things first: please do not post & mail.
If it is personal, mail. Else post.

Slawomir Lisznianski <public@paramay.com> wrote:
> Axel Schwenke wrote:
>>> System is Linux (2.6.9, SMP 8-core), MySQL is 6.0.3 lx86_64 glibc23.
>>
>> This is an ALPHA version!
>
> well, since you brought it up, it's the only version that doesn't seg
> fault whenever mysqld process exceeds 4gb. I tried 5.0 and 5.1 before
> trying out 6.x -- all with the same config.

key_buffer (and other buffers too) had a 4GB limit even on 64-bit
platforms. This was partially lifted in 5.0.52.
See http://dev.mysql.com/doc/refman/5.0/...es-5-0-52.html

>> Your myisam_sort_buffer is clearly oversized.
>
> does it hurt or it's just irrelevant at this point?

It will be truncated to 4GB. It will not do any harm immediately. But
since it is allocated for each server thread that is creating an index,
it might make your system run out of memory.

>> What matters much more is
>> the setting of myisam_max_sort_file_size.
>
> I have it set to 50g...

Could still be too small. How big is the .MYD file of this table? Do
you have 50G free space in tmpdir anyway?

>> projected size of the .MYI file - or MyISAM will fall back to "repair
>> with key cache".
>
> It did fall back to repair with keycache. What else can be done? Thanks
> for all your help.

Unfortunately the manual is not very clear how big the temporary sort
file could grow. But I guess it is the binary image of the columns in
your index * #rows. This could be really large. Maybe even larger than
your .MYD file - i.e. if you have large VARCHAR columns that are only
partially filled.


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/

Axel Schwenke wrote:
> key_buffer (and other buffers too) had a 4GB limit even on 64-bit
> platforms. This was partially lifted in 5.0.52.
> See http://dev.mysql.com/doc/refman/5.0/...es-5-0-52.html

As I said, I tried 5.1 and it seg faulted too. 6.x is the only one
working fine.

>>> What matters much more is
>>> the setting of myisam_max_sort_file_size.
>> I have it set to 50g...
>
> Could still be too small. How big is the .MYD file of this table?

..MYD is 51G.

> Unfortunately the manual is not very clear how big the temporary sort
> file could grow. But I guess it is the binary image of the columns in
> your index * #rows.

I was creating two indexes at once:

I1, three columns: char(5), char(6), int
I2, one column: char(6)

Number of rows: 1 billion

( 1000000000 * 21bytes )/1024^3 = ~19.55gb

50gb should be more than sufficient for myisam_max_sort_file_size.

I set myisam_max_sort_file_size to 100G and index was created using
sorting method.

> This could be really large. Maybe even larger than
> your .MYD file - i.e. if you have large VARCHAR columns that are only
> partially filled.

No varchar columns.

Hi Slawomir,

Slawomir Lisznianski <public@paramay.com> wrote:
> Axel Schwenke wrote:
>> key_buffer (and other buffers too) had a 4GB limit even on 64-bit
>> platforms. This was partially lifted in 5.0.52.
>> See http://dev.mysql.com/doc/refman/5.0/...es-5-0-52.html
>
> As I said, I tried 5.1 and it seg faulted too. 6.x is the only one
> working fine.

You probably tried too old versions. This was fixed in 5.0.52 and 5.1.23.

>>>> What matters much more is
>>>> the setting of myisam_max_sort_file_size.
>>> I have it set to 50g...
>>
>> Could still be too small. How big is the .MYD file of this table?
>
> .MYD is 51G.
>
>> Unfortunately the manual is not very clear how big the temporary sort
>> file could grow. But I guess it is the binary image of the columns in
>> your index * #rows.
>
> I was creating two indexes at once:
>
> I1, three columns: char(5), char(6), int
> I2, one column: char(6)
>
> Number of rows: 1 billion

Hmm. Even with uncompressed rows and 6 bytes row pointer that boils
down to max. (5+6+4+6 + 6+6) bytes * 1G = 33GB

> 50gb should be more than sufficient for myisam_max_sort_file_size.

Ack.

> I set myisam_max_sort_file_size to 100G and index was created using
> sorting method.

OK. It could be worth filing a documentation bug. To have a better
explanation in the manual how big the sort file might grow.


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/