Help! MySQL problem!

[judas]

Our main MySQL server has started locking up for no readily apparent reason. No new software has been installed on the server, nor any configuration changes made.

It's running on Windows Server 2003 and until yesterday had been as stable as a stable thing nailed to the floor.

There are no error messages and the only stuff in the event log seems to relate to complaining on re-start after I've had to kill the process.

I know it's a bit of a longshot given the lack of useful info, but anyone got any ideas on what could cause these lockups?

[jeremyc]

judas said:

I know it's a bit of a longshot given the lack of useful info, but anyone got any ideas on what could cause these lockups?

Database size - maybe it has grown and just crept over some (arbitrary ) boundary?*

*DISCLAIMER: I know nothing about Windows Server 2003 and even less about MySQL.

Edited by jeremyc on Thursday 16th November 15:34

[NickFRP]

has it had any recent updates done do you use SUS?

[judas]

No recent updates to my knowledge.

SUS?

[NickFRP]

Sus is a server which runs updates to other mahicnes. whats the process you stop which locks the server?

[judas]

NickFRP said:

Sus is a server which runs updates to other mahicnes. whats the process you stop which locks the server?

The server itself isn't locking up - just the MySQL DB service. Trying to stop it via the services control panel just results in a 'this service did not respond' type message. I'm having to use the task manager to kill the process and then restart it.

[ThePassenger]

judas said:

No recent updates to my knowledge.

SUS?

Software Update Service I think is the long winded name for it.

Check memory & CPU utilisation on the machine, also check for potential disk errors appearing.
Also what are you using the DB for? If it's feeding some form of query tool see if you can see what query is being run when it 'locks' as it could be that the DB has grown to the point where a crufty SELECT statement is grabbing great gods of data and needs refining (this will appear to lock the DB but it's really just screaming for mercy as someone just SELECT *'d a terrabyte+ of data or something).

[judas]

This is the weird bit: CPU and memory usage are normal. Task manager is showing the MySQL process as using 0% once it's frozen - so it's not screaming for mercy.

It's currently only serving out website content for seventy or so sites and there aren't any horrendous queries or huge gobs of data being requested - at least not that I know of! Total size of all DBs is only 323mb so it's not like it's size that's the issue.

[jamieboy]

judas said:

There are no error messages and the only stuff in the event log seems to relate to complaining on re-start after I've had to kill the process.

What is there in the mySQL logs?

[judas]

jamieboy said:

judas said:

There are no error messages and the only stuff in the event log seems to relate to complaining on re-start after I've had to kill the process.

What is there in the mySQL logs?

MySQL logs said:

061116 15:32:24 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...
061116 15:32:25 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 306215760.
InnoDB: Doing recovery: scanned up to log sequence number 0 306215760
061116 15:32:25 InnoDB: Flushing modified pages from the buffer pool...
061116 15:32:25 InnoDB: Started; log sequence number 0 306215760

Windows event logs just had a whole load of complaints about having to force close threads.

Event Viewer said:

C:Program FilesMySQLMySQL Server 4.1 inmysqld-nt: Forcing close of thread 1098 user: 'xxxxxx'

[ThePassenger]

jamieboy said:

judas said:

There are no error messages and the only stuff in the event log seems to relate to complaining on re-start after I've had to kill the process.

What is there in the mySQL logs?

Aye, MySQL's logs would be a good pointer. Also what's in the event viewer on 2k3 before/after the freeze.

I'd also suggest logging on to MySQL and running some form of data/table check on the database, simplest way might be via PHPMyAdmin's Repair/check system. As if your terminating the process who knows what state the DB is actually in at the moment.

[jamieboy]

judas said:

jamieboy said:

What is there in the mySQL logs?

MySQL logs said:

061116 15:32:24 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...
061116 15:32:25 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 306215760.
InnoDB: Doing recovery: scanned up to log sequence number 0 306215760
061116 15:32:25 InnoDB: Flushing modified pages from the buffer pool...
061116 15:32:25 InnoDB: Started; log sequence number 0 306215760

I'm guessing that's from the mySQL error log - have you enabled the mySQL General Query log? Not sure which versions support it, but it should record every client request so you can see if anything unusual is happening before the service keels over.

[judas]

I'm sure the general query logo isn't on - not even sure how to turn it on Sounds like it would be very useful, though I dread to think how quickly it would grow to an unmanageable size.

[_dobbo_]

judas said:

I'm sure the general query logo isn't on - not even sure how to turn it on Sounds like it would be very useful, though I dread to think how quickly it would grow to an unmanageable size.

Would have to be pretty big to be unmanageable, I work daily with 1GB+ log files, assuming your PC isn't man enough to open it just use a file splitter to isolate the last bit when it has broken.

[jamieboy]

judas said:

I'm sure the general query logo isn't on - not even sure how to turn it on Sounds like it would be very useful, though I dread to think how quickly it would grow to an unmanageable size.

This page http://dev.mysql.com/doc/refman/5.0/e talks a bit about it, you just specify a startup parameter for the service. You're right that it'll make some big files, and it'll also cause a performance hit on the box while it's logging. I've never used it in anger because we've never put a mySQL box into production, but I'd be inclined to run it in a test environment first - even just running some simple VBS test scripts against it should give enough load to get an idea of how badly it'll hurt your production box.