| Bug #9239 | Large myisam_sort_buffer causing signal 11. | ||
|---|---|---|---|
| Submitted: | 17 Mar 2005 1:09 | Modified: | 30 Apr 2005 0:50 |
| Reporter: | Nathan Dabney | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.8 | OS: | Linux (SLES9/64Bit) |
| Assigned to: | CPU Architecture: | Any | |
[17 Mar 2005 1:09]
Nathan Dabney
[24 Mar 2005 6:42]
Timothy Smith
Hi, Nathan.
I tried to repeat this, but was not able to. Can you please give more details on how you expose this bug? For example, the SHOW CREATE TABLE statement, how many rows in the table, and so forth, and the exact commands you used to get a crash.
I did this:
mysql> show create table xyz\G
*************************** 1. row ***************************
Table: xyz
Create Table: CREATE TABLE `xyz` (
`id1` char(200) NOT NULL default '',
`id2` bigint(20) unsigned NOT NULL auto_increment,
`a` char(200) default NULL,
`b` char(200) default NULL,
`c` char(200) default NULL,
`d` char(200) default NULL,
`e` char(200) default NULL,
PRIMARY KEY (`id1`,`id2`),
KEY `a` (`a`),
KEY `b` (`b`),
KEY `c` (`c`),
KEY `d` (`d`),
KEY `e` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1410065408
1 row in set (0.00 sec)
mysql> select count(*) from xyz;
+----------+
| count(*) |
+----------+
| 11616768 |
+----------+
1 row in set (0.00 sec)
mysql> show variables like 'myisam_sort_%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| myisam_sort_buffer_size | 4294967296 |
+-------------------------+------------+
1 row in set (0.00 sec)
mysql> show variables like 'key_buf%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
mysql> alter table xyz disable keys;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table xyz enable keys;
Query OK, 0 rows affected (1 hour 17 min 23.88 sec)
I also tried "REPAIR TABLE xyz USE_FRM".
Neither of those caused the server to crash.
This test was done on an IA64 SGI box with 8G of RAM, using MySQL 4.1.10a.
Regards,
Timothy
[24 Mar 2005 7:33]
Nathan Dabney
Tim, Thanks for looking into this. I don't have exact data that I can give you to reproduce the error, but I do believe we have a copy of a data set in the office that we can use to verify a fix. For us, the issue shows up when we are doing index rebuilds on HUGE data sets. The indexes of which would not fit in the 4GB buffer. (I think) I really think the issue is in the way config files are parsed and the value is set internally. Note that if you set both key_buffer and myisam_sort_buffer to "4G" the myisam_sort_buffer will be *exactly* 4k larger than the key_buffer when you check via show variables. Also note that if you set the myisam_sort_buffer to the value that key_buffer reflects (in the config file) everything works OK. However, if you try and put the value from myisam_sort_buffer in the config file instead of the 4G, MySQL will refuse to start, it knows the value displayed in my bug report is bogus. It looks like myisam_sort_buffer is being set to 4k too large on accident in the parsing of the config file. I hope that is enough information to get you pointed in the right direction. I will be in the office on Monday and will check into a few other ideas I have. I can try and get a create table (filtered) to you then if you still need it. -Nathan
[25 Mar 2005 23:27]
Timothy Smith
Hi! I checked this a bit more, but still can not repeat the problem. I do get the same values as you, when I use 4G in the my.cnf (4294967296). However, the server does not crash, and if I put the above number in my.cnf, the server still starts and works fine: [tsmith@devsrv-a 41m]$ ./runserver Starting mysqld daemon with databases from /tmpbig/tsmith/41m/data [tsmith@devsrv-a 41m]$ mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.10a-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show variables like '%ffer_siz%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size | 131072 | | key_buffer_size | 4294963200 | | myisam_sort_buffer_size | 4294967296 | | preload_buffer_size | 32768 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 2097144 | +-------------------------+------------+ 9 rows in set (0.00 sec) mysql> Bye [tsmith@devsrv-a 41m]$ cat data/my.cnf [mysqld] key_buffer_size=4G myisam_sort_buffer_size=4294967296 I tried this on both a ia64 and x86_64 machine. I tried it with MySQL 4.1.10 and 4.1.8. I also tried running myisamchk directly, and it succeeded. I ran: $ myisamchk --sort_buffer_size=4G --key_buffer_size=4G -r xyz By the way, I populated the xyz table in this fashion: load data infile '/usr/share/dict/words' into xyz (id1); update xyz set a = id1, b = id1, c = id1, d = id1, e = id1; insert into xyz (id1, a, b, c, d, e) select id1, a, b, c, d, e from xyz; -- repeat the above 8 times, to get a total of 11616768 rows Regards, Timothy
[30 Mar 2005 0:50]
Timothy Smith
Nathan, I tried a few more things. I created a table with PACK_KEYS=0 to ensure a large index file that could easily fill up the sort buffer. And I still was unable to get the server to crash in the way you describe. Can you please try to repeat with the following test? CREATE TABLE `abc` ( `id1` char(255) NOT NULL default '', `id2` bigint(20) unsigned NOT NULL auto_increment, `a` char(255) NOT NULL default '', `b` char(255) NOT NULL default '', `c` char(255) NOT NULL default '', `d` char(255) NOT NULL default '', `e` char(255) NOT NULL default '', PRIMARY KEY (`id1`,`id2`), KEY `id2` (`id2`,`a`,`b`), KEY `id2_2` (`id2`,`c`,`d`), KEY `id2_3` (`id2`,`d`,`e`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1410065408 PACK_KEYS=0; load data infile '/usr/share/dict/words' into table abc (id1); update abc set a = id1, b = id1, c = id1, d = id1, e = id1; insert into abc (id1, a, b, c, d, e) select id1, a, b, c, d, e from abc; -- hit <Up><Enter> enough times to get the .MYI file well over 4G (e.g., -- my test table now has a .MYI of > 9G) Try to break myisamchk or REPAIR TABLE with settings of myisam_sort_buffer_size=4G and key_buffer_size=4G. More details of what I tested: mysql> repair table abc use_frm; +----------+--------+----------+------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+--------+----------+------------------------------------------+ | test.abc | repair | error | sort_buffer_size is to small | | test.abc | repair | warning | Number of rows changed from 0 to 3704192 | | test.abc | repair | status | OK | +----------+--------+----------+------------------------------------------+ 3 rows in set (9 hours 22 min 13.86 sec) tsmith@sgi1:/nfstmp1/tsmith/ia64> myisamchk --help | egrep '^(sort|key)_buffer' key_buffer_size 4294963200 sort_buffer_size 4294967288 tsmith@sgi1:/nfstmp1/tsmith/ia64> myisamchk -r data/test/abc - recovering (with sort) MyISAM-table 'data/test/abc' Data records: 3704192 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 tsmith@sgi1:/nfstmp1/tsmith/ia64> Regards, Timothy
[30 Apr 2005 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
