SMF Support > Server Performance and Configuration

mysqldump is hanging all of a sudden ?!

(1/2) > >>

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

[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?

--- End quote ---

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

--- End quote ---

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?

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?

--- End quote ---

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

--- End quote ---

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?

[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]

Navigation

[0] Message Index

[#] Next page