Find the answer to your Linux question:
Results 1 to 4 of 4
Hello. I have a bash script with a line like this in it: Code: mysqldump -uusername -ppassword --master-data=2 --single-transaction --quick --add-drop-table --add-drop-database somedatabase | gzip > ./somedatabase.sql.gz The problem is, ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Dec 2007
    Posts
    9

    Running mysqldumps in parallel?


    Hello. I have a bash script with a line like this in it:

    Code:
    mysqldump -uusername -ppassword --master-data=2 --single-transaction --quick --add-drop-table --add-drop-database somedatabase | gzip > ./somedatabase.sql.gz
    The problem is, I have about 16 databases I'm dumping like this, but it's dumping them sequentually. How do I do all of these dumps in parallel?

    Code:
    mysqldump -uusername -ppassword --master-data=2 --single-transaction --quick --add-drop-table --add-drop-database somedatabase | gzip > ./somedatabase.sql.gz &
    Doing the above doesn't seem to work. The job just appears to hang and doesn't go anywhere. I'm guessing this is because gzip is being backgrounded and mysqldump doesn't like that because it has nowhere to put the output to.

    So, does putting in a & after a command work in a bash script at all? The thought was, if I want to run them in parallel, should I do this?

    Code:
    #!/bin/bash
    ./scripts/dumpthis &
    ./scripts/dumpthat &
    ./scripts/dumpsomemore &
    Where "dumpthis" is just the first snippet of code above.

    Ideas?

  2. #2
    Trusted Penguin Irithori's Avatar
    Join Date
    May 2009
    Location
    Munich
    Posts
    3,422
    Dumping the 16 databases in parallel *only* makes sense, if they are on 16 different raids/drives AND also the dump directory(s) are separated.

    Otherwise you will just cause massive IO load and end up with potentially all your databases locked at once (depending what database engine you use)

    What are you trying to achieve?
    You must always face the curtain with a bow.

  3. #3
    Just Joined!
    Join Date
    Dec 2007
    Posts
    9
    Thanks for the fast reply.

    The database engine is mySQL.

    Now, the reason for this is that the database dumps are taking about 10 hours to run. If there's a way I can shorten that by running them all at once instead of one after another, that would be great.

    I don't care about massive I/O load, I just want the database dumps to take less than 8 hours to run.

  4. $spacer_open
    $spacer_close
  5. #4
    Trusted Penguin Irithori's Avatar
    Join Date
    May 2009
    Location
    Munich
    Posts
    3,422
    Maybe I should be more clear:
    If all the DBs are on the same disc, parallel dumps would fight for one ressource: IO
    Effectively blocking each other.
    The dumps will take much longer than 10h.

    About tuning mysql:
    Depends on the DB engine (innodb, myisam, etc), the typical workload, etc
    So you need to find the right settings for your setup.

    But in general:
    - 2 machines with 8 DBs each might be a start
    - DBs love raid10s consisting of a lot of small and fast discs like 146GB 15k
    - assigning DBs to different raids will enable the possibilty for parallel dumps
    You must always face the curtain with a bow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •