Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16883

MariaBackup very slow after upgrading from 10.2.14 to 10.3.8

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.3.8
    • N/A
    • Backup
    • Fedora Server 27, Galera cluster with 3 nodes, DB size ~ 1.4TB

    Description

      We have a 3 node Galera cluster and a separate backup server where we run MariaBackup. The backup worked well with version 10.2.14. After upgrading the 4 servers to MariaDB / MariaBackup 10.3.8 the backup is much slower (as you can see in the attached log files).
      On the backup server we run this from a script:

      /usr/bin/ssh backup@$IP "/usr/bin/sudo /usr/local/bin/backup-mysql.sh" | mbstream -x -C $BACKUPDIR/$1/$WEEKDAY

      /usr/bin/mariabackup --host=$IP --user=backup --password=xxxx --use-memory=10G --prepare --target-dir $BACKUPDIR/$1/$WEEKDAY

      and this is called on the DB server:

      /usr/bin/mariabackup --backup --use-memory=10G --galera-info --ftwrl-wait-threshold=40 --ftwrl-wait-query-type=all --ftwrl-wait-timeout=180 --kill-long-queries-timeout=20 --kill-long-query-type=all --user=backup --password=xxxx --stream=xbstream --target-dir /var/tmp

      Attachments

        1. 10_2_14.log
          250 kB
        2. 10_3_8.log
          1.51 MB
        3. backup-DB.sh
          0.7 kB
        4. backup-mysql.sh
          0.3 kB
        5. my.cnf
          3 kB

        Issue Links

          Activity

            peterw17 Peter Warmer added a comment -

            Hello winstone,
            because our servers were upgraded from 10_2_14 to 10_3_8, I don't have a 10_2_14 anymore. For 10_3_8 I get for the backup last night:

            root@dbcluster02:/var/backup/DB/db02#du --summarize Tue/
            1542557240 Tue/

            and the current disk usage of the DB is:

            root@dbcluster02:/var/backup/DB/db02#du --summarize /var/lib/mysql/
            1551101564 /var/lib/mysql/

            peterw17 Peter Warmer added a comment - Hello winstone, because our servers were upgraded from 10_2_14 to 10_3_8, I don't have a 10_2_14 anymore. For 10_3_8 I get for the backup last night: root@dbcluster02:/var/backup/DB/db02#du --summarize Tue/ 1542557240 Tue/ and the current disk usage of the DB is: root@dbcluster02:/var/backup/DB/db02#du --summarize /var/lib/mysql/ 1551101564 /var/lib/mysql/
            stijn Stijn added a comment -

            We had a similar issue with mysqldump being 2-3 times slower after the upgrade (issue https://jira.mariadb.org/browse/MDEV-14772 ). The fix was to use "stdbuf -o 10MB" in front of the backup command.

            stijn Stijn added a comment - We had a similar issue with mysqldump being 2-3 times slower after the upgrade (issue https://jira.mariadb.org/browse/MDEV-14772 ). The fix was to use "stdbuf -o 10MB" in front of the backup command.
            peterw17 Peter Warmer added a comment -

            Hello Stijn,
            thank you very much for your hint. Unfortunately the "stdbuf -o 10MB" did not speed up our backup, it took nearly the same time as before.

            peterw17 Peter Warmer added a comment - Hello Stijn, thank you very much for your hint. Unfortunately the "stdbuf -o 10MB" did not speed up our backup, it took nearly the same time as before.

            hello stijn, excuse me for delayed feedback. You are operating evidently with increasing data volumes. I intended to try to reproduce with loaded data near to you size,
            but unfortunately on environment with network bottlenecks, and in addition such huge data load is performing very slowly.
            So, I started measuring mariabackup time on smaller size DBs.
            From test results however it's seen that there is not diffrence in times when get the backup with or without --stream=xbstream option ,
            but if run backup with options --rsync --parallel= the performance time become several times better .
            I checked for regression on the best time, and it's was not seen .

             10.2.14-MariaDB  
             mariabackup  on 80G
             
            *+Backup  without stream option+*          Duration: 1 hours 29 minutes 15 seconds
            Prepare                                Duration: 0 hours 0 minutes 1 seconds
             
             
             
            *+Backup  with --stream=xbstream option+*
                                                                   Duration: 1 hours 29 minutes 25 seconds
            *+mdbstrem  operation  +*            Duration: 0 hours 26 minutes 47 seconds
             
             
             
             
            +*Backup done  with options --rsync  --parallel=6*+   Duration: 0 hours 39 minutes 13 seconds
            

             10.3.10-MariaDB 
            +*Backup done  with options --rsync  --parallel=6*+  
             
            Backup done                  Duration: 0 hours 35 minutes 3 seconds
            Backup prepare done     Duration: 0 hours 0 minutes 2 seconds
            
            

            winstone Zdravelina Sokolovska (Inactive) added a comment - hello stijn , excuse me for delayed feedback. You are operating evidently with increasing data volumes. I intended to try to reproduce with loaded data near to you size, but unfortunately on environment with network bottlenecks, and in addition such huge data load is performing very slowly. So, I started measuring mariabackup time on smaller size DBs. From test results however it's seen that there is not diffrence in times when get the backup with or without --stream=xbstream option , but if run backup with options --rsync --parallel= the performance time become several times better . I checked for regression on the best time, and it's was not seen . 10.2.14-MariaDB mariabackup on 80G   *+Backup without stream option+* Duration: 1 hours 29 minutes 15 seconds Prepare Duration: 0 hours 0 minutes 1 seconds       *+Backup with --stream=xbstream option+* Duration: 1 hours 29 minutes 25 seconds *+mdbstrem operation +* Duration: 0 hours 26 minutes 47 seconds         +*Backup done with options --rsync --parallel=6*+ Duration: 0 hours 39 minutes 13 seconds 10.3.10-MariaDB +*Backup done with options --rsync --parallel=6*+   Backup done Duration: 0 hours 35 minutes 3 seconds Backup prepare done Duration: 0 hours 0 minutes 2 seconds
            wlad Vladislav Vaintroub added a comment - - edited

            I'm going to close this one, because nobody could localize the problem. Backup time in your case can depend on many factors, such as network load and database load.

            Check if something will change if you use --innodb_checksum_algorithm=innodb (for mariabackup), it could have an impact since MDEV-16457 changed the default. use --parallel to speedup the backup time. Backup when database load is low. Those are generic tips, since there is nothing specific known about your case. And to know anything specific, we'd need some kind of performance data, which you maybe could collect with perf (while doing on-disk backup, to isolate it from external networking problems). IF you have some performance data, you can attach it to the bug report, and we can look further.

            As I mentioned, there is practically no difference in backup from 10.2 to 10.3, so the difference you see will mostly likely be something external.

            wlad Vladislav Vaintroub added a comment - - edited I'm going to close this one, because nobody could localize the problem. Backup time in your case can depend on many factors, such as network load and database load. Check if something will change if you use --innodb_checksum_algorithm=innodb (for mariabackup), it could have an impact since MDEV-16457 changed the default. use --parallel to speedup the backup time. Backup when database load is low. Those are generic tips, since there is nothing specific known about your case. And to know anything specific, we'd need some kind of performance data, which you maybe could collect with perf (while doing on-disk backup, to isolate it from external networking problems). IF you have some performance data, you can attach it to the bug report, and we can look further. As I mentioned, there is practically no difference in backup from 10.2 to 10.3, so the difference you see will mostly likely be something external.

            People

              wlad Vladislav Vaintroub
              peterw17 Peter Warmer
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.