GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

--parallel only for InnoDB ? MyISAM still single threaded ?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • --parallel only for InnoDB ? MyISAM still single threaded ?

    Hi I've been testing XtraBackup 1.6.4-314 with MariaDB 5.2.10 for a single MyISAM database and the parallel option with 12 threads on Core i7 3930K 6C/12T based CentOS 6.2 64bit server was only 2 seconds faster than with single thread for innobackupex-1.5.1 ?

    From the output, am I correct to assume parallel is only applicable to InnoDB tables and MyISAM table back up is still single threaded ?

    18,935MB sized sbtest database

    single thread



    /usr/bin/innobackupex-1.5.1 --databases=sbtest /backupInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.120107 18:36:32 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120107 18:36:32 innobackupex-1.5.1: Connected to database with mysql child process (pid=5950)120107 18:36:38 innobackupex-1.5.1: Connection to database server closedIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex-1.5.1 prints "completed OK!".innobackupex-1.5.1: Using mysql Ver 14.16 Distrib 5.2.10-MariaDB, for unknown-linux-gnu (x86_64) using readline 5.1innobackupex-1.5.1: Using mysql server version This software comes with ABSOLUTELY NO WARRANTY. This is free software,innobackupex-1.5.1: Created backup directory /backup/2012-01-07_18-36-38120107 18:36:38 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120107 18:36:38 innobackupex-1.5.1: Connected to database with mysql child process (pid=5974)120107 18:36:40 innobackupex-1.5.1: Connection to database server closed120107 18:36:40 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --target-dir=/backup/2012-01-07_18-36-38innobackupex-1.5.1: Waiting for ibbackup (pid=5980) to suspendinnobackupex-1.5.1: Suspend file '/backup/2012-01-07_18-36-38/xtrabackup_suspended'xtrabackup version 1.6.4 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 314)xtrabackup: uses posix_fadvise().xtrabackup: cd to /var/lib/mysqlInnoDB: Warning: innodb_log_block_size has been changed from its default value. (###EXPERIMENTAL### operation)InnoDB: The log block size is set to 4096.xtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:2G:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 536870912xtrabackup: use O_DIRECT>> log scanned up to (262181863)[01] Copying ./ibdata1 to /backup/2012-01-07_18-36-38/ibdata1>> log scanned up to (262181863)[01] ...done>> log scanned up to (262181863)120107 18:36:52 innobackupex-1.5.1: Continuing after ibbackup has suspended120107 18:36:52 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120107 18:36:52 innobackupex-1.5.1: Connected to database with mysql child process (pid=5994)120107 18:36:54 innobackupex-1.5.1: Starting to lock all tables...>> log scanned up to (262181863)>> log scanned up to (262181863)120107 18:37:04 innobackupex-1.5.1: All tables locked and flushed to disk120107 18:37:04 innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex-1.5.1: subdirectories of '/var/lib/mysql'innobackupex-1.5.1: Backing up files '/var/lib/mysql/sbtest/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par }' (145 files)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)120107 18:38:21 innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex-1.5.1: Resuming ibbackupxtrabackup: The latest check point (for incremental): '262181863'>> log scanned up to (262181863)xtrabackup: Stopping log copying thread.xtrabackup: Transaction log of lsn (262181863) to (262181863) was copied.120107 18:38:23 innobackupex-1.5.1: All tables unlocked120107 18:38:23 innobackupex-1.5.1: Connection to database server closedinnobackupex-1.5.1: Backup created in directory '/backup/2012-01-07_18-36-38'innobackupex-1.5.1: MySQL binlog position: filename '', position 120107 18:38:23 innobackupex-1.5.1: completed OK!


    vs

    parallel=12



    /usr/bin/innobackupex-1.5.1 --parallel=12 --databases=sbtest /backupInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.120107 18:48:42 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120107 18:48:42 innobackupex-1.5.1: Connected to database with mysql child process (pid=6598)120107 18:48:48 innobackupex-1.5.1: Connection to database server closedIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex-1.5.1 prints "completed OK!".innobackupex-1.5.1: Using mysql Ver 14.16 Distrib 5.2.10-MariaDB, for unknown-linux-gnu (x86_64) using readline 5.1innobackupex-1.5.1: Using mysql server version This software comes with ABSOLUTELY NO WARRANTY. This is free software,innobackupex-1.5.1: Created backup directory /backup/2012-01-07_18-48-48120107 18:48:48 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120107 18:48:48 innobackupex-1.5.1: Connected to database with mysql child process (pid=6622)120107 18:48:50 innobackupex-1.5.1: Connection to database server closed120107 18:48:50 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --target-dir=/backup/2012-01-07_18-48-48 --parallel=12innobackupex-1.5.1: Waiting for ibbackup (pid=6628) to suspendinnobackupex-1.5.1: Suspend file '/backup/2012-01-07_18-48-48/xtrabackup_suspended'xtrabackup version 1.6.4 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 314)xtrabackup: uses posix_fadvise().xtrabackup: cd to /var/lib/mysqlInnoDB: Warning: innodb_log_block_size has been changed from its default value. (###EXPERIMENTAL### operation)InnoDB: The log block size is set to 4096.xtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:2G:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 536870912xtrabackup: use O_DIRECT>> log scanned up to (262181863)xtrabackup: Starting 12 threads for parallel data files transfer[03] Copying ./ibdata1 to /backup/2012-01-07_18-48-48/ibdata1>> log scanned up to (262181863)[03] ...done>> log scanned up to (262181863)120107 18:49:02 innobackupex-1.5.1: Continuing after ibbackup has suspended120107 18:49:02 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120107 18:49:02 innobackupex-1.5.1: Connected to database with mysql child process (pid=6653)120107 18:49:04 innobackupex-1.5.1: Starting to lock all tables...>> log scanned up to (262181863)>> log scanned up to (262181863)120107 18:49:14 innobackupex-1.5.1: All tables locked and flushed to disk120107 18:49:14 innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex-1.5.1: subdirectories of '/var/lib/mysql'innobackupex-1.5.1: Backing up files '/var/lib/mysql/sbtest/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par }' (145 files)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)>> log scanned up to (262181863)120107 18:50:28 innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex-1.5.1: Resuming ibbackupxtrabackup: The latest check point (for incremental): '262181863'>> log scanned up to (262181863)xtrabackup: Stopping log copying thread.xtrabackup: Transaction log of lsn (262181863) to (262181863) was copied.120107 18:50:30 innobackupex-1.5.1: All tables unlocked120107 18:50:30 innobackupex-1.5.1: Connection to database server closedinnobackupex-1.5.1: Backup created in directory '/backup/2012-01-07_18-48-48'innobackupex-1.5.1: MySQL binlog position: filename '', position 120107 18:50:30 innobackupex-1.5.1: completed OK!

  • #2
    looks like it is indeed for innodb only


    /usr/bin/innobackupex-1.5.1 --parallel=12 --databases=sbtest /backupInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.120109 22:24:26 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120109 22:24:26 innobackupex-1.5.1: Connected to database with mysql child process (pid=7152)120109 22:24:32 innobackupex-1.5.1: Connection to database server closedIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex-1.5.1 prints "completed OK!".innobackupex-1.5.1: Using mysql Ver 14.16 Distrib 5.2.10-MariaDB, for unknown-linux-gnu (x86_64) using readline 5.1innobackupex-1.5.1: Using mysql server version This software comes with ABSOLUTELY NO WARRANTY. This is free software,innobackupex-1.5.1: Created backup directory /backup/2012-01-09_22-24-32120109 22:24:32 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120109 22:24:32 innobackupex-1.5.1: Connected to database with mysql child process (pid=7175)120109 22:24:34 innobackupex-1.5.1: Connection to database server closed120109 22:24:34 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --backup --suspend-at-end --target-dir=/backup/2012-01-09_22-24-32 --parallel=12innobackupex-1.5.1: Waiting for ibbackup (pid=7181) to suspendinnobackupex-1.5.1: Suspend file '/backup/2012-01-09_22-24-32/xtrabackup_suspended'xtrabackup version 1.6.4 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 314)xtrabackup: uses posix_fadvise().xtrabackup: cd to /var/lib/mysqlInnoDB: Warning: innodb_log_block_size has been changed from its default value. (###EXPERIMENTAL### operation)InnoDB: The log block size is set to 4096.xtrabackup: Target instance is assumed as followings.xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path = ibdata1:2G:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 536870912xtrabackup: use O_DIRECT120109 22:24:34 InnoDB: Warning: allocated tablespace 11, old maximum was 9>> log scanned up to (92650355384)xtrabackup: Starting 12 threads for parallel data files transfer[02] Copying ./ibdata1 to /backup/2012-01-09_22-24-32/ibdata1[01] Copying ./percona/checksums.ibd to /backup/2012-01-09_22-24-32/percona/checksums.ibd[04] Copying ./sbtest/sbtest38.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest38.ibd[03] Copying ./sbtest/sbtest16.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest16.ibd[07] Copying ./sbtest/sbtest26.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest26.ibd[09] Copying ./sbtest/sbtest48.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest48.ibd[06] Copying ./sbtest/sbtest41.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest41.ibd[11] Copying ./sbtest/sbtest5.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest5.ibd[12] Copying ./sbtest/sbtest36.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest36.ibd[05] Copying ./sbtest/sbtest34.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest34.ibd[08] Copying ./sbtest/sbtest9.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest9.ibd[10] Copying ./sbtest/sbtest29.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest29.ibd[01] ...done[01] Copying ./sbtest/sbtest27.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest27.ibd>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)[03] ...done[12] ...done[09] ...done[03] Copying ./sbtest/sbtest21.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest21.ibd[12] Copying ./sbtest/sbtest11.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest11.ibd[09] Copying ./sbtest/sbtest45.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest45.ibd[07] ...done[07] Copying ./sbtest/sbtest4.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest4.ibd[10] ...done[10] Copying ./sbtest/sbtest8.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest8.ibd[11] ...done[11] Copying ./sbtest/sbtest32.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest32.ibd[06] ...done[06] Copying ./sbtest/sbtest23.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest23.ibd[08] ...done[08] Copying ./sbtest/sbtest1.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest1.ibd[01] ...done[01] Copying ./sbtest/sbtest30.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest30.ibd[05] ...done[05] Copying ./sbtest/sbtest19.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest19.ibd[04] ...done[04] Copying ./sbtest/sbtest43.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest43.ibd>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)[09] ...done[09] Copying ./sbtest/sbtest40.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest40.ibd[08] ...done[08] Copying ./sbtest/sbtest7.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest7.ibd[07] ...done[07] Copying ./sbtest/sbtest37.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest37.ibd[11] ...done[11] Copying ./sbtest/sbtest24.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest24.ibd[12] ...done[12] Copying ./sbtest/sbtest22.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest22.ibd[06] ...done[06] Copying ./sbtest/sbtest6.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest6.ibd[01] ...done[01] Copying ./sbtest/sbtest2.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest2.ibd[10] ...done[10] Copying ./sbtest/sbtest10.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest10.ibd[05] ...done[05] Copying ./sbtest/sbtest12.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest12.ibd[03] ...done[03] Copying ./sbtest/sbtest25.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest25.ibd[04] ...done[04] Copying ./sbtest/sbtest46.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest46.ibd>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)[08] ...done[08] Copying ./sbtest/sbtest39.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest39.ibd[09] ...done[09] Copying ./sbtest/sbtest42.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest42.ibd[06] ...done[06] Copying ./sbtest/sbtest20.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest20.ibd[01] ...done[01] Copying ./sbtest/sbtest15.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest15.ibd[07] ...done[07] Copying ./sbtest/sbtest14.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest14.ibd[12] ...done[12] Copying ./sbtest/sbtest44.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest44.ibd[11] ...done[11] Copying ./sbtest/sbtest35.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest35.ibd[05] ...done[05] Copying ./sbtest/sbtest47.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest47.ibd[10] ...done[10] Copying ./sbtest/sbtest3.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest3.ibd[03] ...done[03] Copying ./sbtest/sbtest33.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest33.ibd[04] ...done[04] Copying ./sbtest/sbtest31.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest31.ibd>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)>> log scanned up to (92650355384)[08] ...done[08] Copying ./sbtest/sbtest18.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest18.ibd[09] ...done[09] Copying ./sbtest/sbtest13.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest13.ibd[06] ...done[06] Copying ./sbtest/sbtest17.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest17.ibd[07] ...done[07] Copying ./sbtest/sbtest28.ibd to /backup/2012-01-09_22-24-32/sbtest/sbtest28.ibd[01] ...done[12] ...done[11] ...done[05] ...done[10] ...done[03] ...done[04] ...done>> log scanned up to (92650355384)[02] ...done>> log scanned up to (92650355384)[06] ...done[08] ...done[09] ...done[07] ...done120109 22:26:12 innobackupex-1.5.1: Continuing after ibbackup has suspended120109 22:26:12 innobackupex-1.5.1: Starting mysql with options: --unbuffered --120109 22:26:12 innobackupex-1.5.1: Connected to database with mysql child process (pid=7211)120109 22:26:14 innobackupex-1.5.1: Starting to lock all tables...>> log scanned up to (92650355384)>> log scanned up to (92650355384)120109 22:26:24 innobackupex-1.5.1: All tables locked and flushed to disk120109 22:26:24 innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex-1.5.1: subdirectories of '/var/lib/mysql'innobackupex-1.5.1: Backing up files '/var/lib/mysql/sbtest/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par }' (49 files)120109 22:26:24 innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex-1.5.1: Resuming ibbackupxtrabackup: The latest check point (for incremental): '92650355384'>> log scanned up to (92650355384)xtrabackup: Stopping log copying thread.xtrabackup: Transaction log of lsn (92650355384) to (92650355384) was copied.120109 22:26:26 innobackupex-1.5.1: All tables unlocked120109 22:26:26 innobackupex-1.5.1: Connection to database server closedinnobackupex-1.5.1: Backup created in directory '/backup/2012-01-09_22-24-32'innobackupex-1.5.1: MySQL binlog position: filename '', position 120109 22:26:26 innobackupex-1.5.1: completed OK!

    Comment


    • #3
      Yes, that is correct, there is no parallelization in the Perl wrapper script, which is what copies the MyISAM files. If you have a significant amount of MyISAM data, frankly I would back it up separately from the InnoDB data.

      Comment

      Working...
      X