Why does MySQL keep restarting?

November 28, 2005 2:40 PM

MySQL Hell: I've spent most of my vacation trying to figure out why MySQL has gone nuts. It's part of a Movable Type install with tons of posts, comments, and log entries, and it's always run smoothly before. The problem is that it'll bomb on random queries, seemingly every minute or two, since status queries always show uptime in tens of seconds. Repairing the larger tables and restarting the server has done nothing, and no major changes have been made. What's wrong? Is this a *nix issue, a MySQL issue, or an MT issue? And how do I go about telling the difference, or fixing any of them?

The hosting company's tech support, while generally useless ("not our job", they say) at least reassured me that we're running within MySQL's capabilities. Still, when I try to truncate MT's log table (via SSH), it loses the connection after two or three minutes.

85% of my web work has been on SQL Server and in ASP, but I know my way around a command line and have a good grasp of database theory. Yet I absolutely can not figure this out. Please help!

posted by paul_smatatoes to computers & internet (32 comments total)

This is going to sound like a stupid question, but "how's your disk space?" The last time I had problems with MySQL hanging was because I was keeping a binary log and it had filled up the partition it was on.
posted by sbutler at 2:55 PM on November 28, 2005

And also by 'bombing', do you mean hanging / timing out, or are you getting any sort of error message at all?
posted by planetthoughtful at 2:56 PM on November 28, 2005

I don't have it in front of me now, but it should be ok...especially since I dumped about 50mb of mt log data earlier today. It couldn't run over that again so quickly, could it?
posted by paul_smatatoes at 2:56 PM on November 28, 2005

The only error messages are random ones from MT and MySQL itself, when I try to administer it. MT will return errors relating to comments when I try to rebuild indexes, or very brief, unhelpful messages about mt.pm when I try to view logs. MySQL loses connections and dies mid-repair, without any verbose comments.
posted by paul_smatatoes at 2:58 PM on November 28, 2005

Ask your hosting company for MySQL's error log.
posted by SpecialK at 3:00 PM on November 28, 2005

I don't have it in front of me now, but it should be ok...especially since I dumped about 50mb of mt log data earlier today. It couldn't run over that again so quickly, could it?

I'm not talking about MT's log data, I'm talking about the server's data. Do "SHOW MASTER LOGS" and take a look at the file sizes. You can purge them with "PURGE MASTER LOGS ...".

To check your disk space, type "df -h" at a command line.
posted by sbutler at 3:01 PM on November 28, 2005

And actually, yeah, it could if your hosting company has disk limits on your account, and they don't update immediately. So that could be one thing ... can you ask them to crank up debugging on the MySQL server?

(this is why I host all my websites myself...)
posted by SpecialK at 3:02 PM on November 28, 2005

i can get root access and check out the logs myself...what am I looking for, specifically? the most helpful-seeming entry is one about increasing allocated memory.
posted by paul_smatatoes at 3:02 PM on November 28, 2005

Also, deleting stuff from tables doesn't always free up space on the disk. If you've deleted a lot of rows and want to reclaim the space then you should run an "OPTIMIZE TABLE ..."
posted by sbutler at 3:03 PM on November 28, 2005

that's the thing, tho, sbutler: when I try to optimize, say, the mt_log table it craps out and says "MySQL server has gone away".
posted by paul_smatatoes at 3:08 PM on November 28, 2005

ps: 13G avail, so I don't think it's disk space
posted by paul_smatatoes at 3:10 PM on November 28, 2005

"df -h" is pretty self-explainatory, it tells you how much space is available on each volume and what it's percentage of usage is. Do you have any volumes that are 99% full?

Most default MySQL installs will keep a binary log of statements run in their data directory. For example, my data directory (/var/lib/mysql) looks like this:

manwe mysql # ls -alhtotal 384Kdrwxr-x---   6 mysql mysql 4.0K Nov  6 00:46 .drwxr-xr-x  22 root  root  4.0K Oct 31 19:40 ..drwx------   2 mysql mysql 4.0K Oct 31 19:42 jarma-rw-rw----   1 mysql mysql  12K Nov  5 10:43 manwe-bin.000005-rw-rw----   1 mysql mysql   79 Nov  5 10:46 manwe-bin.000006-rw-rw----   1 mysql mysql  51K Nov  5 11:00 manwe-bin.000007-rw-rw----   1 mysql mysql 277K Nov 25 20:00 manwe-bin.000008-rw-rw----   1 mysql mysql   76 Nov  6 00:46 manwe-bin.indexdrwx------   2 mysql mysql 4.0K Oct 31 19:48 mysqldrwx------   2 mysql mysql 4.0K Oct 31 19:46 pmadbdrwx------   2 mysql mysql 4.0K Oct 31 19:46 test

All those "manwe-bin.x" lines are binary logs. When they get large I have to purge them with "PURGE MASTER LOGS ..." like I said above. If you've just done a large import of data then these files could be eating up space.
posted by sbutler at 3:13 PM on November 28, 2005

ps: 13G avail, so I don't think it's disk space

Well alright then. Never mind.
posted by sbutler at 3:14 PM on November 28, 2005

I have error log files in "/var/log/mysql" called "mysql.err", "mysql.log" and "mysqld.err". Do these exist for you, and more importantly, do then contain anything? (type "tail mysql.err" for just the last 10 lines)
posted by sbutler at 3:21 PM on November 28, 2005

what if ps -A shows about 16 instances of mysqld? are a bunch of those ghost threads or are do they a normal part of mysql?
posted by paul_smatatoes at 3:23 PM on November 28, 2005

That's pretty normal, paul_smatatoes.
posted by evariste at 3:27 PM on November 28, 2005

no .err files for mysql, just a couple files labeled mysqld.log.[number]...here's the result of tail mysqld.log:

InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
051128 15:26:15 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 9407122
InnoDB: Doing recovery: scanned up to log sequence number 0 9407122
051128 15:26:15 InnoDB: Flushing modified pages from the buffer pool...
051128 15:26:15 InnoDB: Started
/usr/libexec/mysqld: ready for connections

over and over again...
posted by paul_smatatoes at 3:27 PM on November 28, 2005

(oh, man, finally a chance to use the block, and I blew it...not my day, man...)
posted by paul_smatatoes at 3:28 PM on November 28, 2005

errors relating to comments when I try to rebuild indexes, or very brief, unhelpful messages about mt.pm when I try to view logs.

The exact error messages might be useful to us, can you try to get them again, and then paste them in here?
posted by evariste at 3:28 PM on November 28, 2005

sigh...that'd be the <code> block, as you may have guessed
posted by paul_smatatoes at 3:28 PM on November 28, 2005

There's some information in the reference manual about MySQL crashing. The first thing they recomend is shutting down the server (run ps -A and make sure all children die) and running "myisamchk --silent --force */*.MYI" from the data directory.
posted by sbutler at 3:33 PM on November 28, 2005

Can you tail a bit more of the log? Say, 20 lines?
posted by evariste at 3:34 PM on November 28, 2005

First things first, what does the .err log say? It should be in the data directory with the binary logs, ala /var/db/mysql/machinename.err. It should be mentioning something if it's crashing.

What version of MySQL? Are you using the official binaries or did you compile it yourself? What OS/architecture? How big is the process growing? Are you using FULLTEXT indexes or anything else exotic? Is there anything ominous looking in dmesg?

"Increasing allocated memory" is odd, are you sure you got that one right? You should really paste the specific error if you have one :)

My initial reaction would be to make sure you're not telling it to grow too big. 32bit systems can limit individual processes anywhere from 500M-2G, regardless of available physical memory and swap, and hitting the limit isn't something a process is likely to handle well.
posted by Freaky at 3:36 PM on November 28, 2005

ok, "innodb_additional_mem_pool_size" is what I should be increasing, according to the logs, not allocated memory. But I think it's not that far away, philosophically.

also, locate doesn't find any .err logs anywhere, but i'm rebuilding the table right now to see if there are any recent ones.

here's a sample MT error: "Build error in template 'New Index': Error in <MTPaginate> tag: Error in <MTEntryIfAllowComments> tag:"

If I take out MTPaginate, I get a different error, but still applying to comments.

And I can't shut down MySQL for some reason.
posted by paul_smatatoes at 4:14 PM on November 28, 2005

nope, still no ".err" files.
posted by paul_smatatoes at 4:14 PM on November 28, 2005

Under your my.cnf file (maybe in /etc/mysql) there's a section called "[mysqld_safe]". Mine reads:

[mysqld_safe]
err-log = /var/log/mysql/mysql.err

Also, there's another error log you can enable in the "[mysqld]" section. This line is:

log-error = /var/log/mysql/mysqld.err

But this won't help you if you can't shutdown mysql. If you're feeling adventurous (and have recent backups) I'd say do this:

1) mysqladmin -u root -p shutdown
2) wait a bit, checking ps for running processes...
3) killall -TERM mysqld
4) wait a bit, checking ps for running processes...
5) killall -KILL mysqld

That last one could cause problems with data corruption, but it's what the server would do anyway if you rebooted it right now.
posted by sbutler at 4:35 PM on November 28, 2005

Is it possible that it's been failing like that forever and you didn't notice until now?
posted by ph00dz at 6:31 PM on November 28, 2005

There's also the possibility that the database has grown too large. A MyISAM table is limited to 4GB in size. This APPEARS to be an InnoDB table, so that limit isn't applicable. It still may be a MyISAM table.

What I can guess from the logfiles is that it is trying to reconstruct the database from the transaction logs, and it is dying doing that. Your best bet is to try and get the logfile off of the machine, and then putting that somewhere that people can help you.
posted by stovenator at 8:13 PM on November 28, 2005

Ok, everything in MT's database is MyISAM, not InnoDB. How can I find out what's wrong with InnoDB, and why it keeps crashing?
posted by paul_smatatoes at 8:21 AM on November 29, 2005

stovenator: MyISAM tables >3.22 actually have a limit of 65536 terabytes, but unless you're using fixed length rows some tweaking is required. Either way hitting said limit shouldn't produce a server crash; you'll get "Table is full" errors.

innodb_additional_mem_pool_size specifies the size of the buffer InnoDB allocates for various internal structures; it grows dynamically if necessary, spewing a warning so you know it could use increasing. The default is 1MB, but hitting it shouldn't break anything. It's odd to be hitting it if you're not actually using Inno though. Add skip-innodb to the options file or --skip-innodb to the startup script and see if the crashes go away.
posted by Freaky at 9:02 AM on November 29, 2005

skip-innodb just resulted in a lot of mysql_connection() errors, and specifying an additional_mem_pool_size resulted in even faster crashing. i'm completely baffled...
posted by paul_smatatoes at 9:43 AM on November 29, 2005

Who's your web host? I work with the MT team and we might be able to help track this down. Definitely sounds like a MySQL problem.
posted by anildash at 3:33 PM on December 7, 2005