Bash script to execute a MariaDB query multiple times

This simple bash script will execute a query 100 times against a MySQL instance. It also uses the time command to report how long the entire process took. I use this for some very simple bench-marking.

The query used here creates a temporary table and inserts 100K rows into it. You need the sequence engine installed.

PWD="secret";
time (for i in {1..100} ; do  mysql -h 127.0.0.1 -u root -p$PWD -P3001 -D tmp -e "CREATE TEMPORARY TABLE tmp$i (id INTEGER NOT NULL PRIMARY KEY); INSERT INTO tmp$i SELECT * FROM seq_1_to_100000;";  done);

Output will look something like below..

real	0m50.205s
user	0m0.492s
sys	0m0.453s

TSQL: Restore a multiple file backup

Just a follow up post using the backup files created in TSQL: Backup a database to multiple files. Here’s the script I used…

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
 
-- Single file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\single_file_backup.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Two file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\two_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\two_files_file2.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Five file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\five_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file2.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file3.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file4.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file5.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Ten file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\ten_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file2.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file3.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file4.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file5.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file6.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file7.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file8.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file9.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file10.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

The results are less than exciting…

Files 1 2 5 10
CPU time (ms) 39577 37595 35162 32790
Elapsed time (ms) 1478788 1443965 1450444 1454009

I’ve sexed up my results a bit on these graphs by leaving the restore times in ms…

tsql multiple backup files sql server1 TSQL: Restore a multiple file backup

 

Have a more sensible scale and the truth is less sexy (minutes instead of ms)…

tsql restore multiple files minutes1 TSQL: Restore a multiple file backup

Now I’m aware this isn’t the best of tests. I had a quick glance through the documentation and I couldn’t see any indication that the RESTORE command was multi-threaded. I keep an eye on sys.dm_exec_requests and this didn’t indicate this either. I guess a better test might be to see if having multiple data files has any significant effect. Fill your boots if you fancy having a go!

 

 

 

 


Copy date stamped backups with a regex & scp

Lets assume you have a directory of date stamped backups you want to scp to another location…

backup_20141003.tar.gz
backup_20141004.tar.gz
backup_20141005.tar.gz
backup_20141006.tar.gz
backup_20141007.tar.gz
backup_20141008.tar.gz
backup_20141009.tar.gz

scp can accept a regex like below to do this as a one-liner. Note the quotes are required for the expression to work.

scp "user@hostname:/data/backup/backup_2014100[3-9].tar.gz" /path/to/location/

You should see something like this output…

backup_20141003.tar.gz                                                                                                                            100%   22GB  55.5MB/s   06:43    
backup_20141004.tar.gz                                                                                                                            100%   22GB  12.0MB/s   31:13    
backup_20141005.tar.gz                                                                                                                            100%   22GB  12.3MB/s   30:41    
backup_20141006.tar.gz                                                                                                                            100%   22GB   7.5MB/s   49:52    
backup_20141007.tar.gz                                                                                                                             77%   17GB  38.7MB/s   02:12 ETA

TSQL: Backup a database to multiple files

I wanted to see how much I could reduce backup times by specifying multiple files in the BACKUP TSQL command. Here’s a script I wrote to do this and I present a summary of the results below. The times are based on a database that produced a backup file(s) of approximately 51GB. You mileages will vary here based on a whole bunch of factors. Therefore consider these results illustrative and do your own testing.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
 
-- Single file
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\single_file_backup.bak';
GO
 
-- Two files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\two_files_file1.bak',
DISK = '\\path\to\backup\location\two_files_file2.bak'
GO
 
-- Five files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\five_files_file1.bak',
DISK = '\\path\to\backup\location\five_files_file2.bak',
DISK = '\\path\to\backup\location\five_files_file3.bak',
DISK = '\\path\to\backup\location\five_files_file4.bak',
DISK = '\\path\to\backup\location\five_files_file5.bak'
GO
 
-- Ten files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\ten_files_file1.bak',
DISK = '\\path\to\backup\location\ten_files_file2.bak',
DISK = '\\path\to\backup\location\ten_files_file3.bak',
DISK = '\\path\to\backup\location\ten_files_file4.bak',
DISK = '\\path\to\backup\location\ten_files_file5.bak',
DISK = '\\path\to\backup\location\ten_files_file6.bak',
DISK = '\\path\to\backup\location\ten_files_file7.bak',
DISK = '\\path\to\backup\location\ten_files_file8.bak',
DISK = '\\path\to\backup\location\ten_files_file9.bak',
DISK = '\\path\to\backup\location\ten_files_file10.bak'
GO
 
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Results are as follows…

Files 1 2 5 10
CPU time (ms) 16878 13527 16159 21745
Elapsed time (ms) 1983023 1545460 1372302 1237149

tsql multiple backup files sql server TSQL: Backup a database to multiple files


Check a html page with check_http

With the check_http Nagios plugin we can check that a url returns an OK status code as well as verifying the page contains a certain string of text. The usage format is a s follows…

/usr/local/nagios/libexec/check_http -H hostname  -r search_string

For example…

/usr/local/nagios/libexec/check_http -H www.youdidwhatwithtsql.com -r "wordpress"

If you want to make the check case-insensitive then change to…

/usr/local/nagios/libexec/check_http -H www.youdidwhatwithtsql.com -R "wordpress"

Happy monitoring!