Bug #19496 Poor performance under FreeBSD 6/AMD64, leading to crash
Submitted: 2 May 2006 22:10 Modified: 24 May 2006 5:09
Reporter: Morgan Tocker Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:4.1.18 OS:FreeBSD (FreeBSD 6.0/AMD64)
Assigned to: Bugs System CPU Architecture:Any

[2 May 2006 22:10] Morgan Tocker
Description:
FreeBSD 6.0 AMD64 builds from build host (bsd60-64) passed testing.

There are reports about performance problems with more than 16 threads and crashes with 256 threads. Vadim verified the MySQL server crashes with 256 threads on FreeBSD 6.0 AMD64.

How to repeat:
Unable to repeat (no login to build host)
[4 May 2006 20:13] Morgan Tocker
I wrote a quick testcase, but I'm unable to test it as yet (requires php installed with pcntl functions).

My way of testing this on my localhost (linux, not FreeBSD) was to see the effect thread thrashing had on queries/second on mysqld, and I was watching the queries/second with mytop.

I'm expecting thrashing occurs quickly on BSD 6.0/AMD64

use test;

-- setup the test by creating some generic data

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
	a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	b varchar(50) NOT NULL
);

INSERT into t1 (b) VALUES ( md5(NOW()) );

INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;
INSERT INTO t1 (b) SELECT md5(CONCAT(b, RAND())) FROM t1;

<?php

$max   = 50;
$runs = 100;

for ($i=1;$i<=$max;$i++) {

	$pid=pcntl_fork();
	
	if ($pid == -1) {
		die("could not fork");
	} else if ($pid) {
		// run for loop again shortly
	} else {

		print "X";

		for($n=0;$n<$runs;$n++) {
		
			print "@";
			$link = mysql_connect("bsd60-64", "mtocker");
			mysql_select_db('test', $link);

			mysql_query("INSERT into t1 (b) VALUES ( md5(NOW()) )", $link);
			$id = mysql_insert_id($link);

			// update
			mysql_query("UPDATE t1 SET b = md5(CONCAT(NOW(), a)) where a = $id", $link);
			// now delete
			mysql_query("DELETE FROM t1 WHERE a = $id", $link);
			
			mysql_close($link);
		}

	}
}

?>
[5 May 2006 18:17] John David Duncan
Maybe this is related to FreeBSD bug 95418 ?

http://www.freebsd.org/cgi/query-pr.cgi?pr=amd64/95418
[13 May 2006 5:22] Vadim Tkachenko
I observer crash on my FreeBSD 6 / amd64 box with 256 threads

How to repeat:
I'm using sysbench 0.4.6 (sysbench.sf.net)
1. Prepare table:
./sysbench --test=oltp --oltp-table-size=1000000 --mysql-table-engine=innodb --mysql-engine-trx=yes --mysql-db=test prepare
2. run with 256 threads:
./sysbench --max-time=120 --max-requests=100000000 --init-rng=1 --test=oltp --mysql-user=root --num-threads=256 --oltp-test-mode=simple --oltp-table-size=1000000  --mysql-db=test run

and crash during benchmark
[19 May 2006 6:31] Greg Lehey
This looks like it might be related to BUG#12251, which we can't reproduce.  This one can be reproduced, so I'll look at it first.
[23 May 2006 5:48] Greg Lehey
The dump from Vadim's recipe looks nothing like those from BUG#12251.  Most threads are waiting, but the thread that causes the crash has:

#0  0x0000000800c2381f in pthread_mutexattr_init () from /usr/lib/libpthread.so.2

#1  0x0000000800c23b8a in pthread_mutexattr_init () from /usr/lib/libpthread.so.2

#2  0x0000000800c13a87 in pthread_create () from /usr/lib/libpthread.so.2

#3  0x00000000004808aa in ?? ()

#4  0x0000000000483609 in ?? ()

#5  0x0000000000404aae in ?? ()

#6  0x00000008008cc000 in ?? ()

#7  0x0000000000000000 in ?? ()

#8  0x0000000000000005 in ?? ()

#9  0x00007fffffffe0c0 in ?? ()

#10 0x00007fffffffe10c in ?? ()

#11 0x00007fffffffe16f in ?? ()

#12 0x00007fffffffe1ba in ?? ()

#13 0x00007fffffffe20a in ?? ()

Down to frame 5 (maybe frame 6) looks OK, but after that it's just junk that goes on at least 800 stack frames deep.  It's possible that this is just another indication of stack corruption, but at the first guess the bugs are not related.
[24 May 2006 1:47] Greg Lehey
There's some reason to believe that this is a FreeBSD problem and not a MySQL problem, though it's too early to be certain.  The following FreeBSD Problem Reports have been mentioned:

http://www.freebsd.org/cgi/query-pr.cgi?pr=amd64/95418 refers to a segmentation violation caused (apparently) by an optimizer bug.  Although not mentioned earlier in this bug report, a number of tests have been made with un-optimized binaries, and they still show the problem,.  On the other hand, it's not clear that the bug report is accurate: it's unclear how this problem can be a compiler bug.

http://www.freebsd.org/cgi/query-pr.cgi?pr=amd64/95127 describes a crash with very similar stack traces to what we've seen in this bug report.  It's due to an uninitialized list, and a patch has been committed to FreeBSD 6-STABLE:

--- thr_kern.c  3 Jan 2006 15:34:27 -0000       1.120
+++ thr_kern.c  16 Feb 2006 01:33:36 -0000      1.121
@@ -1337,6 +1337,7 @@
 
        if (free_kseg_count <= MAX_CACHED_KSEGS)
                return; 
+       TAILQ_INIT(&worklist);
        crit = _kse_critical_enter();
        KSE_LOCK_ACQUIRE(curthread->kse, &kse_lock);
        while (free_kseg_count > MAX_CACHED_KSEGS) {

I'm currently investigating whether this is the real cause of the present problem.
[24 May 2006 5:09] Greg Lehey
This problem is due to the same bug as FreeBSD PR 95127 (http://www.freebsd.org/cgi/query-pr.cgi?pr=amd64/95127).  So is FreeBSD PR 95418(http://www.freebsd.org/cgi/query-pr.cgi?pr=amd64/95418).  Compiling without optimization appears to drive the bug into hiding, but after applying the patch from PR 95127, the problem no longer occurs with optimized code.

This is not a MySQL bug.  Follow the instructions in FreeBSD PR 95127 to fix the problem, or simply install FreeBSD 6.1 or later.