Description:
Server runs 4 x 2.4ghz xeons, 4gigs of RAM. My.cnf settings cause MySQL to take about 2gigs of RAM on startup. Within 28hours MySQL has taken ownership of all 4gigs of RAM. Machine happily operates with free memory floating at 5-10Mb and finally crashes anywhere between 24-72 hours after the initial startup.
Using source compiled mysql-4.1.3 with gcc-3.3.3-r6 and glibc-2.3.3. Have also tried mysql-standard binaries but same things happens.. in my eyes the free memory depletes even quicker with mysql-standard binaries.
Log entry on crash is consistent,
---
040712 10:27:28 Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
mysqld got signal 11;
This could be because.........
key_buffer_size=134217728
read_buffer_size=2093056
max_used_connections=83
max_connections=500
threads_connected=35
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2201644 K
back trace rarely yields much more than
0x814f0dd handle_segfault + 669
0x40038d5d _end + 935216941
0x4020c79a _end + 937132394
here is a more 'useful' backtrace
0x814f0dd handle_segfault + 669
0x40038d5d _end + 935216941
0x4020c79a _end + 937132394
0x8181d7a _Z11open_tablesP3THDP13st_table_listPj + 202
0x8182055 _Z20open_and_lock_tablesP3THDP13st_table_list + 37
0x8164b64 _Z21mysql_execute_commandP3THD + 9748
0x81681bc _Z11mysql_parseP3THDPcj + 268
0x816139d _Z16dispatch_command19enum_server_commandP3THDPcj + 1021
0x8160f56 _Z10do_commandP3THD + 134
0x81606f8 handle_one_connection + 872
0x40033dfb _end + 935196619
0x40255f8a _end + 937433434
Database consists of 46 innodb, 20 heap, 17myisam
I might add, I often (60%) get a segfault of mysqlclient upon exit, chance of segfault seems to increase the longer mysqlclient has been open.
Server uses 3 UDF's that I wrote but same thing happens when UDF's are unloaded and code which would use them is commented out.
We have two boxes of the above configuration running the exact same queries. The same thing happens on both.
Only difference between boxes is that one runs 2.6.7-rc3 the other runs 2.6.7
We have a third box running 2.4.26 w/ glibc-2.3.2-r9 gcc-3.3.2-r5 which MIGHT be experiencing similar problems however this box serves a different purpose, and hence a different set of queries.
The two 2.6.7 boxes act as slaves to the 2.4.26 box for a single myisam table. Replication runs without a hitch until the segv.
Please advise if more information is required.
How to repeat:
Impossible to say. Segmentation fault always happens at different place, with a different query causing the final crash each time.
Rough outline of systems operation is as follows.
table(1) = memory table with 10fields
Program #1
-----------
Insert lots of data into 1 of 2 memory tables. user_lock obtained on the table(1) which will be used. Switch tables at the end of each insert. Each inserts consists of roughly 500 rows and is inserted using MySQL extended insert - insert into table alues (1,2,3),(1,2,3)etc
Program #2
-----------
Multithreaded program with one thread per field of table(1). Extract field from table(1) grouped on another field of table(1) and dump into another memory table with column just for that field and the results of a sum(). Then,
replace into innodb_table select fields from memory_table left join innodb_table.
So to cut a long story short, my program takes a table of raw data and then extracts each field for merging with an innodb table who's job is it to hold cumulated statistics based on a field.
Suggested fix:
Stop mysql from using all my memory.
