From owner-freebsd-database@FreeBSD.ORG Mon Feb 6 16:59:12 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id D92F116A423 for ; Mon, 6 Feb 2006 16:59:12 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from bayless.fiber.net (bayless.fiber.net [216.83.146.126]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6CAC443D4C for ; Mon, 6 Feb 2006 16:59:12 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from [10.1.102.220] (host-220.fbp.ore.fiber.net [216.83.157.220]) (authenticated bits=0) by bayless.fiber.net (8.13.3/8.13.3) with ESMTP id k16Gx8Oa076012 for ; Mon, 6 Feb 2006 09:59:12 -0700 (MST) (envelope-from adam@baylessfamily.org) Message-ID: <43E7806A.8000801@baylessfamily.org> Date: Mon, 06 Feb 2006 09:59:22 -0700 From: Adam Bayless User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: freebsd-database@freebsd.org Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: MySQL 5 crashing on 6.0 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 06 Feb 2006 16:59:13 -0000 I've got a brand new dual AMD opteron box with 4gigs total ram running MySQL 5.0.18. I built MySQL from ports and I'm using /usr/local/share/mysql/my-innodb-heavy-4G.cnf for my.cnf. The database is accessed by 4 client machines for an sql based spamassassin bayes database. The bayes tables are all InnoDB. The InnoDB data file is about 1.1 gigs on disk right now. MySQL is crashing and dumping a core file approximately every 4-6 hours. When it crashes mysqld_safe seems to be able to restart it just fine every time, but of course the innodb data has to be rebuilt from the logs which takes several minutes and processing mail stops while that happens. I thought initially that it must have been running into server memory limits, but I cut all of MySQL's internal limits approximately in half (in my.cnf) and it's still having the same trouble. There doesn't seem to be a certain query that causes it to crash every time, though when spamassassin runs a bayes expiration that sometimes seems to trigger the crash, other times it doesn't, though it always increases MySQL's CPU and memory usage. I've set the following in /boot/loader.conf: kern.maxdsiz="3684354560" kern.dfldsiz="3684354560" kern.maxssiz="3684354560" 'gdb -c mysqld.core' says: Program terminated with signal 11, Segmentation fault. #0 0x00000000dc9cf81f in ?? () which doesn't help me much. Maybe someone else can make some sense of that? Any ideas? Thanks, Adam From owner-freebsd-database@FreeBSD.ORG Tue Feb 7 00:22:54 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 2CE0416A420 for ; Tue, 7 Feb 2006 00:22:54 +0000 (GMT) (envelope-from 000.fbsd@quip.cz) Received: from home.quip.cz (grimm.quip.cz [213.220.192.218]) by mx1.FreeBSD.org (Postfix) with ESMTP id 65E5E43D4C for ; Tue, 7 Feb 2006 00:22:52 +0000 (GMT) (envelope-from 000.fbsd@quip.cz) Received: from [192.168.1.2] (qwork.quip.test [192.168.1.2]) by home.quip.cz (Postfix) with ESMTP id E3ED36221; Tue, 7 Feb 2006 01:22:50 +0100 (CET) Message-ID: <43E7E85A.1010606@quip.cz> Date: Tue, 07 Feb 2006 01:22:50 +0100 From: Miroslav Lachman <000.fbsd@quip.cz> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.12) Gecko/20050915 X-Accept-Language: cs, cz, en, en-us MIME-Version: 1.0 To: Adam Bayless References: <43E7806A.8000801@baylessfamily.org> In-Reply-To: <43E7806A.8000801@baylessfamily.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Cc: freebsd-database@freebsd.org Subject: Re: MySQL 5 crashing on 6.0 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 07 Feb 2006 00:22:54 -0000 Adam Bayless wrote: > > MySQL is crashing and dumping a core file approximately every 4-6 hours. > When it crashes mysqld_safe seems to be able to restart it just fine > every time, but of course the innodb data has to be rebuilt from the > logs which takes several minutes and processing mail stops while that > happens. I thought initially that it must have been running into server > memory limits, but I cut all of MySQL's internal limits approximately in > half (in my.cnf) and it's still having the same trouble. There doesn't > seem to be a certain query that causes it to crash every time, though > when spamassassin runs a bayes expiration that sometimes seems to > trigger the crash, other times it doesn't, though it always increases > MySQL's CPU and memory usage. > > I've set the following in /boot/loader.conf: > > kern.maxdsiz="3684354560" > kern.dfldsiz="3684354560" > kern.maxssiz="3684354560" > > > 'gdb -c mysqld.core' says: > > Program terminated with signal 11, Segmentation fault. > #0 0x00000000dc9cf81f in ?? () > > which doesn't help me much. Maybe someone else can make some sense of that? > > Any ideas? > > Thanks, > > Adam > Have you any useful information in mysql errorlog? (usualy /var/db/mysql/your.host.name.err) Miroslav Lachman From owner-freebsd-database@FreeBSD.ORG Tue Feb 7 14:57:48 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 2899316A420 for ; Tue, 7 Feb 2006 14:57:48 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from bayless.fiber.net (bayless.fiber.net [216.83.146.126]) by mx1.FreeBSD.org (Postfix) with ESMTP id DECC643D45 for ; Tue, 7 Feb 2006 14:57:45 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from [10.1.102.220] (host-220.fbp.ore.fiber.net [216.83.157.220]) (authenticated bits=0) by bayless.fiber.net (8.13.3/8.13.3) with ESMTP id k17EviXZ090662; Tue, 7 Feb 2006 07:57:45 -0700 (MST) (envelope-from adam@baylessfamily.org) Message-ID: <43E8B59A.30806@baylessfamily.org> Date: Tue, 07 Feb 2006 07:58:34 -0700 From: Adam Bayless User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: Miroslav Lachman <000.fbsd@quip.cz> References: <43E7806A.8000801@baylessfamily.org> <43E7E85A.1010606@quip.cz> In-Reply-To: <43E7E85A.1010606@quip.cz> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: freebsd-database@freebsd.org Subject: Re: MySQL 5 crashing on 6.0 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 07 Feb 2006 14:57:48 -0000 Miroslav Lachman thought no one was listening and belted out: > > > Have you any useful information in mysql errorlog? (usualy > /var/db/mysql/your.host.name.err) > > Miroslav Lachman > > I should have included that in my original message. Here's the latest crash. Not much in the way of error messages: 060207 06:01:41 mysqld restarted 060207 6:01:42 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060207 6:01:42 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 16 3794872210. InnoDB: Doing recovery: scanned up to log sequence number 16 3800114688 . . . InnoDB: Doing recovery: scanned up to log sequence number 16 3957401088 060207 6:02:18 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 16 3962643968 . . . InnoDB: Doing recovery: scanned up to log sequence number 16 4119930368 060207 6:04:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 16 4125173248 . . . InnoDB: Doing recovery: scanned up to log sequence number 16 4282459648 060207 6:05:47 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 16 4287702528 . . . InnoDB: Doing recovery: scanned up to log sequence number 17 97371946 060207 6:07:23 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 0, file name 060207 6:08:27 InnoDB: Started; log sequence number 17 97371946 060207 6:08:27 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.0.18-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.0.18 Thanks, Adam From owner-freebsd-database@FreeBSD.ORG Tue Feb 7 16:35:20 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id BC98C16A420 for ; Tue, 7 Feb 2006 16:35:20 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from bayless.fiber.net (bayless.fiber.net [216.83.146.126]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4C24543D46 for ; Tue, 7 Feb 2006 16:35:20 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from [10.1.102.220] (host-220.fbp.ore.fiber.net [216.83.157.220]) (authenticated bits=0) by bayless.fiber.net (8.13.3/8.13.3) with ESMTP id k17GZJgI092308; Tue, 7 Feb 2006 09:35:19 -0700 (MST) (envelope-from adam@baylessfamily.org) Message-ID: <43E8CC79.8080803@baylessfamily.org> Date: Tue, 07 Feb 2006 09:36:09 -0700 From: Adam Bayless User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: Gleb Paharenko , freebsd-database@freebsd.org References: <43E7806A.8000801@baylessfamily.org> <43E8C59B.2070307@mail.ru> In-Reply-To: <43E8C59B.2070307@mail.ru> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: Subject: Re: MySQL 5 crashing on 6.0 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 07 Feb 2006 16:35:20 -0000 Gleb Paharenko thought no one was listening and belted out: > Hello. > > Have a look here: > http://dev.mysql.com/doc/refman/5.0/en/crashing.html > > > I've been through a lot of those things. I built the port again this morning with debug enabled so we'll see if that gives any more useful info. -adam From owner-freebsd-database@FreeBSD.ORG Thu Feb 9 01:49:40 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 1AE8016A420 for ; Thu, 9 Feb 2006 01:49:40 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from bayless.fiber.net (bayless.fiber.net [216.83.146.126]) by mx1.FreeBSD.org (Postfix) with ESMTP id CA48F43D45 for ; Thu, 9 Feb 2006 01:49:39 +0000 (GMT) (envelope-from adam@baylessfamily.org) Received: from [10.1.102.220] (host-220.fbp.ore.fiber.net [216.83.157.220]) (authenticated bits=0) by bayless.fiber.net (8.13.3/8.13.3) with ESMTP id k191ncUo086026 for ; Wed, 8 Feb 2006 18:49:39 -0700 (MST) (envelope-from adam@baylessfamily.org) Message-ID: <43EA9FE9.2060800@baylessfamily.org> Date: Wed, 08 Feb 2006 18:50:33 -0700 From: Adam Bayless User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: freebsd-database@freebsd.org References: <43E7806A.8000801@baylessfamily.org> <43E8C59B.2070307@mail.ru> <43E8CC79.8080803@baylessfamily.org> In-Reply-To: <43E8CC79.8080803@baylessfamily.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: Re: MySQL 5 crashing on 6.0 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 09 Feb 2006 01:49:40 -0000 Well I ran with a --with-debug=full for 24 hours and never got a coredump. I had to reinstall the version without debug because the debug version was slowing things down too much. Is it normal for a debug version to be several times slower? Not sure what to try next. -adam Adam Bayless thought no one was listening and belted out: > Gleb Paharenko thought no one was listening and belted out: >> Hello. >> >> Have a look here: >> http://dev.mysql.com/doc/refman/5.0/en/crashing.html >> >> >> > > I've been through a lot of those things. I built the port again this morning with > debug enabled so we'll see if that gives any more useful info. > > -adam > > > _______________________________________________ > freebsd-database@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-database > To unsubscribe, send any mail to "freebsd-database-unsubscribe@freebsd.org" > > From owner-freebsd-database@FreeBSD.ORG Thu Feb 9 06:58:05 2006 Return-Path: X-Original-To: freebsd-database@FreeBSD.ORG Delivered-To: freebsd-database@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 42F2916A420 for ; Thu, 9 Feb 2006 06:58:05 +0000 (GMT) (envelope-from decibel@decibel.org) Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by mx1.FreeBSD.org (Postfix) with ESMTP id C383643D46 for ; Thu, 9 Feb 2006 06:58:02 +0000 (GMT) (envelope-from decibel@decibel.org) Received: by noel.decibel.org (Postfix, from userid 1001) id 1AC8239842; Thu, 9 Feb 2006 06:57:52 +0000 (UTC) Received: (hashcash-sendmail, from uid 1001); Thu, 9 Feb 2006 00:57:51 -0600 Date: Thu, 9 Feb 2006 00:57:51 -0600 From: "Jim C. Nasby" To: freebsd-database@FreeBSD.ORG Message-ID: <20060209065751.GA57845@decibel.org> References: <20060119011550.GN17896@decibel.org> <200601221049.k0MAnaVh089477@lurza.secnetix.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200601221049.k0MAnaVh089477@lurza.secnetix.de> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060209:freebsd-database@freebsd.org::ROn9qqFQ6BSF2fHw:000000000 0000000000000000000000007rXK Cc: Subject: Re: Horrible PostgreSQL performance with NFS X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 09 Feb 2006 06:58:05 -0000 On Sun, Jan 22, 2006 at 11:49:36AM +0100, Oliver Fromme wrote: > FWIW, there is _no_ blocksize that can be used wit dd(1) > that benchmarks disks the same way a database does. > Unless our dd(1) implemetation grew a random-seek option > that I missed. :-) > > But seriously... dd(1) is NOT a benchmark. Never ever. > Especially if you're interested in database performance. OLAP databases depend heavily on sequential scans, which are the same as dd. dd is also useful to find out what your maximum throughput is. -- Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" From owner-freebsd-database@FreeBSD.ORG Thu Feb 9 15:27:41 2006 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id CAC1816A422 for ; Thu, 9 Feb 2006 15:27:41 +0000 (GMT) (envelope-from gleban@mail.ru) Received: from gw.matrix.kiev.ua (gw.matrix.kiev.ua [213.159.235.42]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4611A43D46 for ; Thu, 9 Feb 2006 15:27:38 +0000 (GMT) (envelope-from gleban@mail.ru) Received: by gw.matrix.kiev.ua (Postfix, from userid 426) id C616D14BC96; Thu, 9 Feb 2006 17:27:36 +0200 (EET) Received: from [192.168.1.2] (unknown [192.168.1.2]) by gw.matrix.kiev.ua (Postfix) with ESMTP id ED7BD14BC89; Thu, 9 Feb 2006 17:27:35 +0200 (EET) Message-ID: <43EB5F67.5010603@mail.ru> Date: Thu, 09 Feb 2006 17:27:35 +0200 From: Gleb Paharenko User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc4 (X11/20050929) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Adam Bayless References: <43E7806A.8000801@baylessfamily.org> <43E8C59B.2070307@mail.ru> <43E8CC79.8080803@baylessfamily.org> <43EA9FE9.2060800@baylessfamily.org> In-Reply-To: <43EA9FE9.2060800@baylessfamily.org> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Spam-Checker-Version: SpamAssassin 3.0.2 (2004-11-16) on gw.matrix.kiev.ua X-Spam-Level: X-Spam-Status: No, score=0.1 required=5.0 tests=AWL,BAYES_50 autolearn=no version=3.0.2 Cc: freebsd-database@freebsd.org Subject: Re: MySQL 5 crashing on 6.0 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 09 Feb 2006 15:27:41 -0000 Hello. Big probability that this is a compiler issue. Perhaps, you should play with different versions of gcc. It is normal for debug version to be slower. See: http://dev.mysql.com/doc/refman/5.0/en/compile-and-link-options.html Adam Bayless wrote: > Well I ran with a --with-debug=full for 24 hours and never got a > coredump. I had to reinstall the version without debug because the > debug version was slowing things down too much. Is it normal for a > debug version to be several times slower? > > Not sure what to try next. > > -adam > > > Adam Bayless thought no one was listening and belted out: > >> Gleb Paharenko thought no one was listening and belted out: >> >>> Hello. >>> >>> Have a look here: >>> http://dev.mysql.com/doc/refman/5.0/en/crashing.html >>> >>> >>> >> >> I've been through a lot of those things. I built the port again this >> morning with debug enabled so we'll see if that gives any more useful >> info. >> >> -adam >> >> >> _______________________________________________ >> freebsd-database@freebsd.org mailing list >> http://lists.freebsd.org/mailman/listinfo/freebsd-database >> To unsubscribe, send any mail to >> "freebsd-database-unsubscribe@freebsd.org" >> >> > _______________________________________________ > freebsd-database@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-database > To unsubscribe, send any mail to > "freebsd-database-unsubscribe@freebsd.org" > >