3

Running mysqldump without piping to file such as:

mysqldump -u root -pPASS db 

is really slow on windows. If I do

mysqldump -u root -pPASS db > db.sql

it runs fast.

This might not seem like a big deal as I can always pipe to a file. BUT I am using mysqldump from a script and I just want the raw output so I don't have to load the file in memory or save a temp file.

Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Is there a way to fix this? This is for mysql 5.6.x

The full command I run is:

mysqldump --user=$db_username --password=$db_password --tz-utc=false --single-transaction --routines --triggers db

but I have tried a lot of combos and nothing makes it fast.

4
+50

Based on the bounty message Looking for an answer drawing from credible and/or official sources., the only thing I could do is just mention that mysqldump is already buffering as little as possible. Why can I say that ???

By default, the option --opt is enabled. The MySQL Documentation says this about --opt:

This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

Please note that --quick is already enabled because --opt is enabled.

What does --quick do ???

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

Since --quick is already enabled, each row is being dumped directly to stdout without buffering. You may have to just bite the bullet and disable --quick by using --skip-quick. What that will do is buffer a whole table into RAM (which you didn't want to do in the first place) and then spill it out to stdout.

In your question, you said something interesting

Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Since you said that, you have reached a critical moment

kxm

I can say that mysqldump is not really the problem. The problem is the OS and/or hardware.

Evidently, the computer you are using for Windows is not performant for screen I/O.

Since MacOS runs like Linux, I would tend to blame Windows OS. My guess is that even with a far better computer (faster CPU, more RAM), mysqldump may still not perform better if Windows cannot buffer screen I/O effectively.

Your fix for this would be to tune Windows. Godspeed, Spiderman !!!

  • The problem has something to do with php/apache. On php 5.3.8 and php 7.3 mysqldump using the same methods works very fast. Any other places to look? I am serving the mysqldump through passthru function in php – Chris Muench Mar 8 '19 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.