MySQL Bugs: #13084: MySQL under OS X 10.4.n in 64 Bit mode causes server crash (not just mysqld)

Bug #13084	MySQL under OS X 10.4.n in 64 Bit mode causes server crash (not just mysqld)
Submitted:	9 Sep 2005 10:30	Modified:	6 Mar 2006 17:50
Reporter:	Bruce Dembecki	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.13 and 4.1.14	OS:	MacOS (OS X 10.4.2)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
When using MYSQL 64 bit binaries on Apple OS X 10.4.2 (with all the latest patches applied) I set InnoDB to use 6Gbytes of memory (or some other high number) and then try to run mysqlimport to populate an empty table with 4Gbytes of data. I wait and after a while the server crashes... when I say server I mean the whole Operating system freezes... Apple use a watchdog time so after 5 minutes of being crashed the machine resets it's power supply and it reboots.

I use innodb_file_per_table so I have some idea how far the process goes before it freezes... invariable the .idb file is just over 2Gbytes in size... which would suggest InnoDb has grabbed a little more than 2Gbytes of the meory it allocated itself.

My impression is that when mysqld actually tries to use more then 2GBytes of the memory it allocated itself, the OS crashes.

This does not occur if I use the 64 bit biary and allocated InnoDB less than 2Gbytes of memory.. the import completes... Obviously it also does not occur if I use the 32 bit binary (and allocated less than 2GBytes of memory)

How to repeat:
Launch mysql using innodb_file_per_table and allocate InnoDB 6 Gbytes of memory. Import 4Gbytes+ of data using mysqlimport (or lad data in file etc). Wait a few minutes.

I am happy to provide my data if it helps (but it's a big file)

Suggested fix:
Don't know if it's you or Apple... someone needs to figure it out so it doesn't crash

We could go to Linux on our PPC Mac boxes... but you don't have a 64 bit PowerPC build for Linux.. why is that? Yellow Dog makes a PPC Linux that works with our hardware...

Bruce,

please post 'top' and vmstat 10 during the import, just before the freeze.

Also post SHOW INNODB STATUS just before the freeze.

InnoDB does allocate all that 6 GB of memory for its buffer pool immediately at a mysqld startup. The freeze cannot come from memory allocation itself.

From Red Hat ES 4 we got a report that showed file I/O speed to slow down to 1/10 when the OS file cache size exceeded 4 GB. Maybe also OS X has some bug in the handling of a large (> 2 GB) file cache.

This definitely looks like an OS X bug, not a MySQL bug. It might also have something to do with how the 64-bit version of mysqld is built and linked, or with OS libraries installed in your computer.

Regards,

Heikki

First up further testing.. if I allocate 10G to the myisam key_buffer and import the data into myisam, no freeze and the table imports all 9.9M rows without issue. My Create table and my.cnf are over on support issue # 5134.

vm_stat says (this vm_stats was started before I shutdown mysql, changed the memory settings to increase InnoDB's memory to 6G and started mysqld again and then ran the import till the server crashed):   

[data3:~] root# vm_stat 10
Mach Virtual Memory Statistics: (page size of 4096 bytes, cache hits 55%)
  free active inac wire   faults     copy zerofill reactive  pageins  pageout
 48067 412630 3601365 132242 154154173  6358066 58189020   962542    14917      497
 47725 412796 3601537 132246     1999      265      500        0        0        0
 47429 412852 3601772 132251     4016      637     1037        0        0        0
 47087 413020 3601941 132256     3183      266     1187        0        0        0
 47142 413008 3601900 132254     1311        2      709        0        0        0
1037392 413021 2611628 132263     2407       61     1372        0        0        0
1037200 413031 2611675 132398     5390      749     1526        0        0        0
1037122 413081 2611705 132396     1677       40      775        0       13        0
1318271 136484 2610439 129110     2639      340      892        0       19        0
1318271 136484 2610439 129110     1483        0      785        0        0        0
1317972 136516 2610576 129240     2167       41      913        0        0        0
1318079 136516 2610597 129112     1848       74      830        0        1        0
1318157 136490 2610446 129211     3337      157     1293        0        0        0
1318145 136500 2610450 129209     2925      114     1099        0       13        0
1094797 136652 2831394 131461   551374     7366   515058        0       96        0
1094794 136654 2831394 131462     1605        0      801        0        0        0
1094794 136654 2831394 131462     1303        0      711        0        0        0
1619029 136671 2307261 131343     2662      152      998        0       20        0
1818996 136675 2107287 131346     3266      157     1315        0        0        0
2558143 136668 1368145 131348     2067       23     1135        0        0        0
2558389 136643 1368053 131219     1542        0      807        0        0        0
Mach Virtual Memory Statistics: (page size of 4096 bytes, cache hits 56%)
  free active inac wire   faults     copy zerofill reactive  pageins  pageout
2558041 136655 1368133 131475 154754897  6368563 58723758   962542    15094      497
2558039 136655 1368135 131475      942        0      486        0        0        0
2513333 136873 1412389 131709    18773     1011    13936        0       19        0
2444099 136887 1481321 131997    24061      206    21304        0        0        0
2361655 136887 1563459 132303    23785       11    22760        0        0        0
2275002 136887 1649600 132815    33240       11    31498        0        0        0
2197033 136887 1727137 133247    38009       11    36739        0        0        0
2128174 136887 1795157 133574    47433        0    46487        0        0        0
2061814 136886 1861774 133830    48430       86    46895        0        0        0
2001970 136890 1920659 134273    51410      161    49055        0        0        0
1934341 136890 1988609 134464    52862        0    52106        0        0        0
1867076 136890 2055458 134880    56051       11    54798        0        0        0
1822651 136890 2099576 135187    49267       11    47842        0        0        0
1754895 136890 2167035 135484    48720        0    47798        0        0        0
1695270 136889 2226306 135839    50610       86    49113        0        0        0
1646310 136893 2275013 136088    44884      160    42426        0        0        0
1597071 136893 2324002 136338    54636       11    53668        0        0        0
1545438 136893 2375366 136607    57421       11    56471        0        0        0
1503849 136893 2416748 136814    43529        0    42361        0        0        0
1447597 136893 2472803 137011    59352        0    58536        0        0        0
1403443 136892 2516630 137339    70191       86    68723        0        0        0
Mach Virtual Memory Statistics: (page size of 4096 bytes, cache hits 56%)
  free active inac wire   faults     copy zerofill reactive  pageins  pageout
1364114 136896 2555882 137412 155685579  6370596 59621671   962542    15113      497
1320052 136896 2599648 137708    56665        0    55625        0        0        0
1266840 136896 2652062 137994    58263       11    57122        0        0        0
1219640 136896 2699515 138253    64706        0    63691        0        0        0
1175867 136896 2743062 138479    40413        0    39341        0        0        0
1122640 136895 2796019 138750    55035      106    53467        0        0        0
1078819 136899 2839614 138972    58695      246    55741        0        0        0
1033953 136899 2884042 139154    41544       55    39986        0        0        0
986413 136899 2931567 139425    61537       77    59832        0        0        0
939613 136899 2978127 139665    61668       99    59801        0        0        0
899865 136901 3017672 139866    46175      121    44025        0        0        0
845467 136900 3071785 140152    56953      170    54776        0        0        0
803167 136903 3113877 140357    61279      186    59091        0        0        0
754983 136903 3161821 140597    58444       11    57338        0        0        0
718842 136903 3197780 140779    48962       11    47768        0        0        0
669979 136903 3246410 141012    59870       11    58764        0        0        0
615031 136903 3301064 141306    57844       22    56724        0        0        0
574198 136902 3341432 141516    41147       72    39623        0        0        0
528894 136905 3386764 141741    59165      180    56772        0        0        0
463854 136905 3450986 142047    47080       22    45937        0        0        0
428945 136905 3486236 142218    38216       22    37020        0        0        0
Mach Virtual Memory Statistics: (page size of 4096 bytes, cache hits 56%)
  free active inac wire   faults     copy zerofill reactive  pageins  pageout
370757 136905 3544128 142514 156807835  6372040 60711434   962542    15113      497
334705 136905 3580004 142690    58083        0    57191        0        0        0
291701 136905 3622924 142774    39284       78    37975        0        1        0

The last top screen was:

Processes:  65 total, 2 running, 1 stuck, 62 sleeping... 182 threads   12:57:05
Load Avg:  0.81, 0.68, 0.42     CPU usage:  30.9% user, 13.2% sys, 55.9% idle
SharedLibs: num =  131, resident = 14.8M code, 1.91M data, 7.64M LinkEdit
MemRegions: num = 63518, resident = 1.44G + 19.4M private, 26.6M shared
PhysMem:   558M wired,  534M active, 13.9G inactive, 15.0G used, 1.03G free
VM: 7.20G + 87.4M   15114(0) pageins, 497(0) pageouts 

  PID COMMAND      %CPU   TIME   #TH #PRTS #MREGS RPRVT  RSHRD  RSIZE  VSIZE
27890 mysqld      75.4%  5:13.58  11    47    72   863M  7.52M  3.55G+  776M 
27722 top          6.1%  0:39.24   1    29    27  3.46M  2.28M  3.91M  29.5M 
    0 kernel_tas   4.3%  2:10:39  45     2  6403  50.8M-    0K   386M+ 1.67G-
   47 hwmond       0.4%  1:46:50   8    91    45   448K  2.52M  3.03M  31.0M 
  549 mysqld       0.2% 55:03.27   9    42    67   224M  7.52M   227M  2.55G
   86 SecurityAg   0.1% 31:37.47   1    68    99  1.66M  10.9M  9.14M- 90.4M
   34 configd      0.0% 17:16.77   3   142    57   460K  2.93M  1.96M  28.9M
   72 WindowServ   0.0% 12:08.54   3    80    50   508K  9.23M  8.19M  63.4M
  238 ARDAgent     0.0%  0:03.18   6    94    71   844K  5.77M  3.05M  67.3M
   48 mDNSRespon   0.0%  3:29.82   3    34    26   312K  2.59M  1.25M  27.9M

Show innodb status last said:

data3 testing: show innodb status\G
*************************** 1. row ***************************
Status: 
=====================================
050912 12:56:49 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 667, signal count 660
Mutex spin waits 2462, rounds 18484, OS waits 575
RW-shared spins 100, OS waits 48; RW-excl spins 4, OS waits 4
------------
TRANSACTIONS
------------
Trx id counter 0 3330
Purge done for trx's n:o < 0 2820 undo n:o < 0 0
History list length 1
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 50661376
MySQL thread id 4, query id 14 172.20.42.47 bruce
show innodb status
---TRANSACTION 0 3329, ACTIVE 418 sec, OS thread id 50569216 inserting, thread declared inside InnoDB 392
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, undo log entries 4080252
MySQL thread id 3, query id 10 localhost engineering
LOAD DATA   INFILE '/var/tmp/deletedMessage.txt' INTO TABLE deletedMessage IGNORE 0 LINES
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
26 OS file reads, 82021 OS file writes, 10223 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 441.73 writes/s, 33.22 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 12451141, used cells 818, node heap has 2 buffer(s)
0.89 hash searches/s, 9063.44 non-hash searches/s
---
LOG
---
Log sequence number 3 3884909917
Log flushed up to   3 3878232108
Last checkpoint at  3 3497206014
1 pending log writes, 0 pending chkp writes
1184 log i/o's done, 2.89 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 7056592552; in additional pool allocated 10652416
Buffer pool size   384000
Free buffers       210378
Database pages     173620
Modified db pages  42170
Pending reads 0
Pending writes: LRU 0, flush list 27, single page 0
Pages read 64, created 173556, written 357022
0.00 reads/s, 388.51 creates/s, 1107.77 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread id 50452480, state: sleeping
Number of rows inserted 4080252, updated 0, deleted 0, read 0
8285.41 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.10 sec)

data3 testing: show innodb status\G

This looks like an OS X bug - we filed a bug report at the Apple Bug reporter at
https://bugreport.apple.com about it it. The bug ID is  #4280976.

This turned out to be a bug in OS X.  Apple has fixed it, as of OS X 10.4.5.  I have feedback from the original bug submitter, that he tested the Apple OS X 10.4.5 Developers' preview release on Feb 6 2006, and the hang & crash did not happen.

I have marked this as Not a Bug, because it was unrelated to MySQL.  However, there was an actual serious bug which caused a hang & crash in OS X.