mysqldump is hanging all of a sudden ?!

Started by ldk, August 04, 2005, 09:01:16 AM

Previous topic - Next topic

ldk

Hi all,

This probably isn't related to SMF at all but I'm wondering if someone here can help because I'm a bit clueless with this stuff!  :P

Every other day or so I SSH into my dedicated server and dump the SMF database into a file using this commend:

mysqldump -u XXXX -p XXXX > dump.sql

Then I gzip the file and download, archive it, etc. This system has been working great for many many months.

But all of a sudden mysqldump just hangs forever when I run it!  Well, I guess I can't say forever ;D but I've waited upwards of 30 minutes for it to finish.  The dump file gets to be a certain size and never grows no matter how long I wait.  So I think it's hung up somewhere.

Any ideas at all what could be causing this and how to fix it?

Thanks!

Leah
see SMF put to the test at http://www.craftster.org/

[Unknown]

It could be that the error log or some other table is unexpectedly large?

What happens if you run, in another shell:
mysqladmin proc | grep -e Sending -e Copying

Do you see any queries that are holding things up?

Also, I'd suggest the use of:

mysqldump -CceKq --single-transaction -uusername -p > dump.sql

Or:

mysqldump -CceKq --single-transaction -uusername -p | gzip -7 > dump.sql.gz

-[Unknown]

ldk

Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
It could be that the error log or some other table is unexpectedly large?

What happens if you run, in another shell:
mysqladmin proc | grep -e Sending -e Copying

Do you see any queries that are holding things up?

It doesn't catch anything with this command.

Is there a safe way to clear out the error log?

Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
mysqldump -CceKq --single-transaction -uusername -p | gzip -7 > dump.sql.gz

This also hangs. (But it's handy to know I gzip it at the same time as dumping it!)

What does the -CceKq do by the way?
see SMF put to the test at http://www.craftster.org/

ldk

Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
It could be that the error log or some other table is unexpectedly large?

What happens if you run, in another shell:
mysqladmin proc | grep -e Sending -e Copying

Do you see any queries that are holding things up?

It doesn't catch anything with this command.

Is there a safe way to clear out the error log?

Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
mysqldump -CceKq --single-transaction -uusername -p | gzip -7 > dump.sql.gz

This also hangs. (But it's handy to know how to gzip it at the same time as dumping it!)

What does the -CceKq do by the way?
see SMF put to the test at http://www.craftster.org/

[Unknown]

Nothing?  What about:

mysqladmin proc

Alone?

The CceKq options do, respectively:

C: use compression between server and client (to reduce memory usage, or bandwidth if across servers.)
c: use complete inserts, meaning with column names not just values.
e: insert multiple rows with one statement, which is a lot faster and generates a much smaller backup.
K: put in the backup commands to turn off and back on keys, to improve restoration speed.
q: dump more quickly for larger tables, more directly without a middle buffer between data and stdout.

And --single-transaction makes your backup more reliable if the forum is being used at the same time and you have any InnoDB tables.

-[Unknown]

ldk

Here's potentially another clue? I opened up the dump file and see how far it gets each time. It successfully dumps these tables:

-- Dumping data for table `dbyabbse_attachments`
-- Dumping data for table `dbyabbse_ban_groups`
-- Dumping data for table `dbyabbse_ban_items`
-- Dumping data for table `dbyabbse_board_permissions`
-- Dumping data for table `dbyabbse_boards`
-- Dumping data for table `dbyabbse_calendar`
-- Dumping data for table `dbyabbse_calendar_holidays`
-- Dumping data for table `dbyabbse_categories`
-- Dumping data for table `dbyabbse_collapsed_categories`
-- Dumping data for table `dbyabbse_fc_bans`
-- Dumping data for table `dbyabbse_fc_connections`
-- Dumping data for table `dbyabbse_fc_ignors`
-- Dumping data for table `dbyabbse_fc_messages`
-- Dumping data for table `dbyabbse_fc_rooms`

According to older backups I have, the next table should be: dbyabbse_log_actions

What's also odd is that when I show the  mySql Process List, this is at the top of the list:


| 30400366 | root      | localhost | craftster_smf | Query   | 212527 | Waiting to get writelock | OPTIMIZE TABLE `dbyabbse_log_actions`


I have no idea what any of this means. But thought it might be helpful.
see SMF put to the test at http://www.craftster.org/

[Unknown]

Hmm... that could be causing problems.  Do:

mysqladmin kill 30400366

The log_actions table is just a log, and can be cleared either from the administration panel or by TRUNCATEing it.

-[Unknown]

ldk

WOO HOO!  I have a backup!  Phew.  I can sleep again now.  :)

[Unknown] - can I hire you as my personal admin? ha ha... I wish!  Man -- I really need to take a class or buy a book on mySQL.

Can I ask you one more question...

Do you recommend, generally speaking, that I use these arguments to mysqldump:

Quote
mysqldump -CceKq --single-transaction -uusername -p

I want to be sure that my backups will have everything they need in case I need to restore from one of them.

Thank you!!
see SMF put to the test at http://www.craftster.org/

[Unknown]

With or without those arguments, you should be fine.  The most important one is --single-tansaction, which will make things better upon restoration.

-[Unknown]

Advertisement: