MySQL Bugs: #29446: Specifying a myisam_sort_buffer > 4GB on 64 bit machines not possible.

Bug #29446	Specifying a myisam_sort_buffer > 4GB on 64 bit machines not possible.
Submitted:	29 Jun 2007 14:34	Modified:	25 Oct 2007 18:18
Reporter:	Horst Hunger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows (64 bit)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
A myisam_sort_buffer with 5GB allocated only 938MB, while a value of 3.5GB allocates 3.5GB. Seems to have a problem with the 4GB bound.

How to repeat:
Put the attached test program into mysql-test/t, create the result file and execute "perl mysql-test-run.pl <test program>".
The program also generates a "top.log" generated by the top command in mysql-test.

Thank you for the report.

Verified on Solaris with next difference:

mysql> SET myisam_sort_buffer_size = 5000*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%sort%';                
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| max_length_for_sort_data  | 1024       | 
| max_sort_length           | 1024       | 
| myisam_max_sort_file_size | 6242172928 | 
| myisam_sort_buffer_size   | 4294967295 | 
| sort_buffer_size          | 65500      | 
+---------------------------+------------+
5 rows in set (0.00 sec)

mysql> select 4294967295/(1024*1024);
+------------------------+
| 4294967295/(1024*1024) |
+------------------------+
|              4096.0000 | 
+------------------------+
1 row in set (0.00 sec)

Fixed in current 5.1 tree for all 64 bit unix versions (ie where sizeof(ulong) = 64)
On windows 64 bit, many buffer sizes are still restricted to 4G

In 5.2 we should go through all buffer variables in include/ and sql/ and change them to size_t. We should test if 64 bit is supported and use ulonglong for buffer sizes with my_getopt on 64 bit systems.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34875

ChangeSet@1.2516, 2007-10-04 12:34:00+04:00, kaa@polly.(none) +4 -0
  Issue a warning if a user sets an option or a variable to a value that is greater than a defined maximum for the option/variable.
  
  This is for bug #29446 "Specifying a myisam_sort_buffer > 4GB on 64 bit machines not possible". Support for myisam_sort_buffer_size > 4 GB on 64-bit Windows will be looked at later in 5.2.

Bug #31350 was marked as duplicate of this one.

Pushed into 5.1.23-beta

Pushed into 5.0.52

Bug#5731, Bug#29419, Bug#29446 are related; this note appears in all three reports.

Noted in 5.0.52, 5.1.23 changelogs (and also 5.2.6 to note the changes explicitly because of discussion in these bug reports about work still to be done in 5.2).

Several buffer-size system variables were either being handled 
incorrectly for large values (for settings larger than 4GB, they were
truncated to values less than 4GB without a warning), or were limited
unnecessarily to 4GB even on 64-bit systems.

The following changes were made in 5.0.52: 

For key_buffer_size, values larger than 4GB are allowed on 64-bit 
platforms (except Windows, for which large values are truncated to
4GB with a warning). 

For join_buffer_size, sort_buffer_size, and myisam_sort_buffer_size,
values are limited to 4GB on all platforms. Larger values are
truncated to 4GB with a warning. 

The following changes were made in 5.1.23/5.2.6:

For key_buffer_size, values larger than 4GB are allowed on 64-bit 
platforms.  

For join_buffer_size, sort_buffer_size, and myisam_sort_buffer_size,
values larger than 4GB are allowed on 64-bit platforms (except
Windows, for which large values are truncated to 4GB with a warning). 

The following change was made in all versions:

Settings for read_buffer_size and read_rnd_buffer_size
are limited to 2GB on all platforms. Larger values are truncated to
2GB with a warning.

5.2.6 changes will appear in 6.0.3 instead.