Professional Web Applications Themes

mysqldump interrupts our service - MySQL

We have pretty good server hardware: 5 SCSI disks, Raid5, but the daily backup of our database lasts about 15-20 minutes and it interrupts our service for this duration: /usr/bin/mysqldump -F -q --opt -u boincsrv -p"XX" cape5 | gzip --fast > /backup/cape5_`date +%d-%m-%Y`mysqldump.gz When I look at the files, the db's size is about 12 GB. Is such a long duration normal? Is there a better way to make a backup such that the normal operation of the db is not interrupted? Best Bernhard...

  1. #1

    Default mysqldump interrupts our service


    We have pretty good server hardware: 5 SCSI disks, Raid5,
    but the daily backup of our database lasts about 15-20
    minutes and it interrupts our service for this duration:

    /usr/bin/mysqldump -F -q --opt -u boincsrv -p"XX" cape5 | gzip --fast >
    /backup/cape5_`date +%d-%m-%Y`mysqldump.gz

    When I look at the files, the db's size is about 12 GB. Is such
    a long duration normal? Is there a better way to make a backup
    such that the normal operation of the db is not interrupted?

    Best
    Bernhard


    Bernhard Guest

  2. Moderated Post

    Default Re: mysqldump interrupts our service

    Removed by Administrator
    Axel Guest
    Moderated Post

  3. #3

    Default Re: mysqldump interrupts our service

    Bernhard Kornberger wrote: 

    I think 15-20 minutes for a 12GB db is pretty good, actually.

    But this subject was discussed in a fair amount of detail in this very
    newsgroup within the past 2-3 days. You can te your database.
    When it comes time to back it up, stop the tion and backup the slave.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: mysqldump interrupts our service

    Bernhard Kornberger wrote:
     


    For comparison, our 6.1GB of databases takes almost exactly 5 minutes to
    backup using mysqldump (though no compression is done).

    This is with a 4x15krpm SAS RAID1+0 array.



    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  5. #5

    Default Re: mysqldump interrupts our service

    Axel Schwenke wrote:
     
    >[/ref]

    Sorry Bernhard, you have a long way to go to get to "pretty good". :)
     

    There are applications and systems where a few millisecond delay could bite, I
    do not believe this is one of them. Due to delays in presentation from a web
    app, most people will never notice a few millisecond delay. For most small
    shops with small apps/db's (a 12GB db is very small) RAID-5 is more
    cost-effective and due to the inherent delays of "the internet" (especially over
    dial-up) I would recommend it in a heartbeat.

    I have never seen in any database I have had to work with over the past 7-8
    years where RAID/0+1 or other "fast write" configurations made any difference in
    processing. But then again, mine have all been SAN-based with hundreds of
    spindles in the RAID configuration and tons of CACHE. In these scenarios, I
    doubt you will ever see any performance degradation. I haven't. The only way to
    notice is to have your app single-threaded for writes only. That is not the way
    systems work. They are being presented with hundreds of I/O request scattered
    across the spindles, the heads are constantly "thrashing" to write this data
    over here, read that data over there etc... In a REAL operating environment,
    due to the randomness of the request, I have yet to see ***anyone*** actually
    quantify any difference at all.

    What databases have I worked on? A shop that had > 3000 production databases
    (Oracle, SQLServer, DB2 - sorry we rejected MySQL), a shop with > 300
    life-dependent databases (medical records ASP environment) just to name 2. We
    ran extensive test using RAID/0+1 and RAID5 and saw virtually no difference in
    read/write performance. A lot has to do with the app and the hardware. Maybe
    if you continue to use a toy for a server, then you might see some difference,
    but I doubt it.

    There are always trade-offs between speed, cost, quality. Pick any two.

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  6. #6

    Default Re: mysqldump interrupts our service

    Michael Austin <com> wrote: 
    >
    > There are applications and systems where a few millisecond delay could bite, I
    > do not believe this is one of them. Due to delays in presentation from a web
    > app, most people will never notice a few millisecond delay. For most small
    > shops with small apps/db's (a 12GB db is very small) RAID-5 is more
    > cost-effective and due to the inherent delays of "the internet" (especially over
    > dial-up) I would recommend it in a heartbeat.[/ref]

    12GB is indeed very small, actually smaller than any hard disk you can
    buy today. So if you want to save cost but keep reliability, you should
    go for RAID-1 on two disks.

    There is another point: RAID-10 can be done efficiently in software.
    For RAID-5 you better have a hardware XOR-Unit - read: RAID controller.
     

    The cache is the key point here. In a SAN environment you usually have
    a lot of cache. The cache allows the disk controller to bundle writes,
    so the short writes eventually become full stripe writes. Also physical
    disks are bundled in rather small RAID groups (4 disks are common).
    So if you look at the total disk space in a SAN it's more like a
    RAID-50 or Concat-of-RAID-5 than a normal RAID-5.

    Now lets count disk I/O for RAID-5 vs. RAID-10 with 4 disks for a short
    write. Lets assume we have 1 sector to write:

    RAID-10: write the sector to two disks. Done.

    RAID-5: read the associated sector from 2 disks. Write the new sector
    to disk #3, calculate XOR of all 3 sectors and write to disk #4.

    Now what's the difference:

    - we have two (# of disks - 2) additional reads for RAID-5
    - the parity write has to wait for the reads to complete,
    so the overall latency doubles (at least) for RAID-5
    - all disks are busy with either reading or writing for RAID-5
    - with RAID-10 two disks (# of disks - 2) are unused and can do
    other I/O

    The RAID-5 overhead is reduced if you can write full stripes - you
    save the reads completely. So if your RDBMS does paged I/O (reading/
    writing complete pages only) and you can manage to have the page size
    being a multiple of the RAID stripe size - then you're fine.
    Unfortunately MySQL doesn't work so (except InnoDB).

    Also RAID-5 is more efficient if you use small RAID groups; but then
    again it doesn't buy you much in terms of netto disk space per buck.
     

    [snip]

    I ran some MySQL databases on different disk configurations already.
    Also there was a SAN involved (HP XP48 - relabled HDS hardware).
    The storage cabinet was configured for RAID-5 with 4 disks in each
    RAID group. And it performed fine. Unfortunately I couldn't test
    different RAID levels.

    OTOH there were a lot of small x86 servers also. Hardware RAID
    controllers from AMI and ICP/Vortex. Here RAID-5 vs. RAID-10 was
    a big difference. Mostly due to

    - only few MB cache (battery backed RAM) on the controller
    - only one RAID group


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  7. #7

    Default Re: mysqldump interrupts our service

    On Thu, 23 Nov 2006 16:13:34 GMT, Michael Austin wrote:
     [/ref]
    >
    >
    > Sorry Bernhard, you have a long way to go to get to "pretty good". :)
    >

    >
    >
    > There are applications and systems where a few millisecond delay
    > could bite, I do not believe this is one of them. Due to delays in
    > presentation from a web app, most people will never notice a few
    > millisecond delay. For most small shops with small apps/db's (a
    > 12GB db is very small) RAID-5 is more cost-effective and due to the
    > inherent delays of "the internet" (especially over dial-up) I would
    > recommend it in a heartbeat.
    >
    > I have never seen in any database I have had to work with over the
    > past 7-8 years where RAID/0+1 or other "fast write" configurations
    > made any difference in processing.[/ref]

    IIRC, the original poster was asking about delays in making a backup,
    not the transaction processing. This has bearing because while a new
    miliseconds between friends doesn't matter much to a web application, it
    have a bit to do with how long it takes to write 12 GB of data out to
    the same array it's reading the data from via mysqldump *and* doing a
    compression on the data in between chunks. It makes me wonder how much
    speed would be gained from simply throwing another drive in the box, on
    another chain/controller/whatever, and dumping to a plain file on that
    empty disk, and doing the compression after the dump is entirely
    completed.

    But that may be a personal quirk. I USUALLY try solving an I/O issue
    by segregating data, and throwing drive arms at the problem. It's
    generally a pretty cheap answer until the box is pretty close to maxed
    out anyway. And by then, you needed a new box anyway. (:

    --
    186,000 Miles per Second. It's not just a good idea. IT'S THE LAW.
    Peter Guest

Similar Threads

  1. #20745 [Com]: socket_accept() in blocking mode doesn't handle signal interrupts
    By antoine dot bajolet at tdf dot fr in forum PHP Bugs
    Replies: 0
    Last Post: March 12th, 06:54 PM
  2. Need mysqldump help
    By SergioQ in forum MySQL
    Replies: 0
    Last Post: September 25th, 06:06 PM
  3. Replies: 6
    Last Post: September 22nd, 02:38 AM
  4. [PHP] mysqldump
    By Marios Adamantopoulos in forum PHP Development
    Replies: 2
    Last Post: July 25th, 05:53 PM
  5. Replies: 0
    Last Post: July 22nd, 02:45 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139