Thursday, March 24, 2016

Use SQL Server Resource Governor to throttle IO (slow down the backup)

It's practical to limit backup speed specially in VM environment, which could create IO storms during backup time. (Enterprise edition ONLY feature)

-- Create Windows user DEV-PC\MaintUser with password "Password", and grant full privilege on following folders:
-- \MSSQL\Log
--  Backup Dest Folders

USE [master]
GO
CREATE LOGIN [DEV-PC\MaintUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DEV-PC\MaintUser]
GO


USE [master]
GO
CREATE CREDENTIAL [MaintUser] WITH IDENTITY = N'DEV-PC\MaintUser', SECRET = N'Password'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MaintUser',@credential_name=N'MaintUser', 
@enabled=1, 
@description=N'User to run maintenance job with IO Throttling'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MaintUser', @subsystem_id=11
GO


-- Create resource pool & workload groups
CREATE RESOURCE POOL MaintPlanPool
GO
CREATE WORKLOAD GROUP MaintPlanGroup using MaintPlanPool
GO

-- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
     IF ORIGINAL_LOGIN() = 'DEV-PC\MaintUser'
     BEGIN
          RETURN 'MaintPlanGroup'
     END

     RETURN 'default'
END
GO

-- Set the classifier function and enable RG
ALTER RESOURCE GOVERNOR 
  WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

ALTER RESOURCE POOL MaintPlanPool 
  WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=100)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO



Sunday, March 13, 2016

Quick way to install RPMs for Oracle 12c installation

https://docs.oracle.com/database/121/LADBI/pre_install.htm#LADBI7534
 
Packages for Oracle Linux 7 and Red Hat Enterprise Linux 7
The following packages (or later versions) must be installed:

binutils-2.23.52.0.1-12.el7.x86_64 
compat-libcap1-1.10-3.el7.x86_64
compat-libstdc++-33-3.2.3-71.el7.i686 
compat-libstdc++-33-3.2.3-71.el7.x86_64
gcc-4.8.2-3.el7.x86_64 
gcc-c++-4.8.2-3.el7.x86_64 
glibc-2.17-36.el7.i686 
glibc-2.17-36.el7.x86_64 
glibc-devel-2.17-36.el7.i686 
glibc-devel-2.17-36.el7.x86_64 
ksh
libaio-0.3.109-9.el7.i686 
libaio-0.3.109-9.el7.x86_64 
libaio-devel-0.3.109-9.el7.i686 
libaio-devel-0.3.109-9.el7.x86_64 
libgcc-4.8.2-3.el7.i686 
libgcc-4.8.2-3.el7.x86_64 
libstdc++-4.8.2-3.el7.i686 
libstdc++-4.8.2-3.el7.x86_64 
libstdc++-devel-4.8.2-3.el7.i686 
libstdc++-devel-4.8.2-3.el7.x86_64 
libXi-1.7.2-1.el7.i686 
libXi-1.7.2-1.el7.x86_64 
libXtst-1.2.2-1.el7.i686 
libXtst-1.2.2-1.el7.x86_64 
make-3.82-19.el7.x86_64 
sysstat-10.1.5-1.el7.x86_64 
 
=======================================
 
yum install binutils.x86_64 \
compat-libcap1.x86_64 \
compat-libstdc++-33.i686  \
compat-libstdc++-33.x86_64 \
gcc.x86_64  \
gcc-c++.x86_64  \
glibc.i686  \
glibc.x86_64  \
glibc-devel.i686  \
glibc-devel.x86_64  \
ksh \
libaio.i686  \
libaio.x86_64  \
libaio-devel.i686  \
libaio-devel.x86_64  \
libgcc.i686  \
libgcc.x86_64  \
libstdc++.i686  \
libstdc++.x86_64  \
libstdc++-devel.i686  \
libstdc++-devel.x86_64  \
libXi.i686  \
libXi.x86_64  \
libXtst.i686  \
libXtst.x86_64  \
make.x86_64  \
sysstat.x86_64

Saturday, March 12, 2016

Number of MySQL table columns affects binlog size with row-based reolication


Introduced5.6.2
Command-Line Format--binlog-row-image=image_type
System VariableNamebinlog_row_image=image_type
Variable ScopeGlobal, Session
Dynamic VariableYes
Permitted ValuesTypeenumeration
Defaultfull
Valid Valuesfull (Log all columns)
minimal (Log only changed columns, and columns needed to identify rows)
noblob (Log all columns, except for unneeded BLOB and TEXT columns)
In MySQL row-based replication, each row change event contains two images, a before image whose columns are matched against when searching for the row to be updated, and an after image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required.
Note
When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log.
For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.
In MySQL 5.6, you can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:
  • full: Log all columns in both the before image and the after image.
  • minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image that are actually changed.
  • noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.
Note
This variable is not supported by MySQL Cluster; setting it has no effect on the logging of NDB tables. (Bug #16316828)
The default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images. If you need to replicate from a MySQL 5.6 (or later) master to a slave running a previous version of MySQL, the master should always use this value.
When using minimal or noblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:
  • All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.
  • The tables must have identical primary key definitions.
(In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.)
If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the master and slave silently diverge, thus breaking consistency.
Setting this variable has no effect when the binary logging format is STATEMENT. When binlog_format is MIXED, the setting for binlog_row_image is applied to changes that are logged using row-based format, but this setting no effect on changes logged as statements.
Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.   

================================================================

 Length Name
    ------ ----
  72251750 server1-bin.000001
   4381411 server1-bin.000002 <-- update t1 set last_modified=last_modified + interval 1 second;
    788494 server1-bin.000003 <-- update t2 set last_modified=last_modified + interval 1 second;
    229497 server1-bin.000004 <-- create table t3 (id integer auto_increment primary key, flag char(1));
    458175 server1-bin.000005 <-- update t3 set flag='y';
       810 server1-bin.000006 <-- alter table t3 add column c1 char(200) default 'a';alter table t3 add column c2 char(200) default 'b';alter table t3 add column c3 char(200) default 'c';
    850512 server1-bin.000007 <-- update t3 set c1='c',c2='b',c3='a';
    850512 server1-bin.000008 <-- update t3 set flag='z';
       194 server1-bin.000009
       297 server1-bin.index


mysql> create database test;
mysql> use test
mysql> create table t1 (id integer, c1 char(200), c2 char(200), c3 char(200), last_modified timestamp(6));
mysql> alter table t1 modify column id int auto_increment primary key;
mysql> create table t2 (id integer auto_increment primary key, last_modified timestamp(6));

mysql> flush logs;


mysql> update t1 set last_modified=last_modified + interval 1 second;
Query OK, 32550 rows affected (0.54 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t2 set last_modified=last_modified + interval 1 second;
Query OK, 32689 rows affected (0.36 sec)
Rows matched: 32689  Changed: 32689  Warnings: 0
mysql> flush logs;


mysql> create table t3 (id integer auto_increment primary key, flag char(1));
mysql> insert into t3 (flag) select 'x' from t1;
Query OK, 32550 rows affected (0.33 sec)
Records: 32550  Duplicates: 0  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='y';
Query OK, 32550 rows affected (0.87 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;


mysql> alter table t3 add column c1 char(200) default 'a';
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c2 char(200) default 'b';
Query OK, 0 rows affected (0.98 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c3 char(200) default 'c';
Query OK, 0 rows affected (1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> flush logs;


mysql> select * from t3 limit 1\G
*************************** 1. row ***************************
  id: 1flag: y
  c1: a
  c2: b
  c3: c
1 row in set (0.00 sec)

mysql> update t3 set c1='a',c2='b',c3='c';
Query OK, 0 rows affected (0.11 sec)
Rows matched: 32550  Changed: 0  Warnings: 0
mysql> update t3 set c1='c',c2='b',c3='a';
Query OK, 32550 rows affected (1.02 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='z';
Query OK, 32550 rows affected (0.35 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

Tuesday, March 8, 2016

Use Python to compare files (Use case: Rename Oracle redo-logs in asymmetric data guard setup or ASM)

Data Files:



Output: 


Source Code:

import sys
import csv
import os.path

def convert(sourcecsv, targetcsv):
    if not os.path.isfile(sourcecsv):
        print ("File [%s] is not invalid. Quiting..." % (sourcecsv))
        return
    if not os.path.isfile(targetcsv):
        print ("File [%s] is not invalid. Quiting..." % (targetcsv))
        return
    sourcelist = []
    targetlist = []
    validlist = []
    with open(sourcecsv,'r') as f:
        reader = csv.reader(f, delimiter = ',')
        for row in reader:
            if len(row) > 0 :
                sourcelist.append([int(row[0].strip()),row[1].strip()])

    with open(targetcsv,'r') as f:
        reader = csv.reader(f, delimiter = ',')
        for row in reader:
            if len(row) > 0 :
                targetlist.append([int(row[0].strip()),row[1].strip()])

    # source the list based on group#    sourcelist.sort()
    targetlist.sort()

    for (gid1,member1) in sourcelist:
        for (gid2, member2) in targetlist:
            if gid1 == gid2:
                 if member1[1] == ':':
                     if member1[0:1] == member2[0:1]:
                         validlist.append([gid1,member1,member2])
                 else:
                     if member1[1:].split('/')[0] == member2[1:].split('/')[0]:
                         validlist.append([gid1,member1,member2])


    for (gid,member1,member2) in validlist:
        print("alter database rename file '%s' to '%s';" % (member1,member2))
        sourcelist.remove([gid,member1])
        targetlist.remove([gid,member2])

    print('\n')
    for (gid,member1) in sourcelist:
        print("Redo log without match in source file: {0:d}, {1}".format(gid,member1))

    print('\n')
    for (gid,member2) in targetlist:
        print("Redo log without match in target file: {0:d}, {1}".format(gid,member2))

if __name__ == "__main__":
   if len(sys.argv) != 3:
       print("Usage: python %s source_redo_file.csv target_redo_file.csv" % (sys.argv[0]))
       print("These CSV files in format:group#,member")
       print("Reference SQL: select group#||','||member from v$logfile order by group#")
       exit(1)
   convert(sys.argv[1],sys.argv[2])

Sunday, March 6, 2016

Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)

https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223


The following table covers the different SQL Server operations.


File type
Operation
READ pattern
WRITE pattern
Threads used
I/O type
Data File
Normal Activity
8KiB up to 128KiB
8KiB up to 128KiB
Based on MaxDOP
Random
Checkpoint
N/A
64KiB up to 128 KiB
# of Sockets in Computer
Random
LazyWriter
N/A
64KiB up to 128 KiB
1 per NUMA Node
Random
Bulk Insert
N/A
8KiB up to 128 KiB
Based on MaxDOP
Sequential
Backup
1 MB
1 MB
Based on MaxDOP
Sequential
Restore
64KiB
64KiB
Based on MaxDOP
Sequential
DBCC Checkdb w/ no repair option
8KiB up to 64KiB
N/A
Based on MaxDOP
Sequential
Rebuild Index
See Read Ahead
8KiB – 128 KiB
Based on MaxDOP
Sequential
ReadAhead
Up to 512 KiB
N/A
Based on MaxDOP
Sequential
Log File
Normal Activity
512 bytes - 64KiB
512 bytes - 64KiB
one log writer thread per soft NUMA node with a cap of 4
Sequential

Parameters Explained:


  • -b8K (Block size is 8KiB)
  • -d300 (5 Minutes)
  • -h (Disable both software caching and hardware write caching)
  • -L (Measure latency statistics)
  • -o32 (32 outstanding IO requests)
  • -r (Random IO)
  • -w40 (40% writes, 60% READ)
  • -t8 (8 threads)
  • -c1G (A 1 GiBfile to be created)
  • .\datafile.dat (File path and name to create for test)


D:\temp\Diskspd-v2.0.15\amd64fre>.\diskspd.exe -b8K -d300 -h -L -o32 -r -w40 -t8 -c1G .\datafile.dat

Command Line: .\diskspd.exe -b8K -d300 -h -L -o32 -r -w40 -t8 -c1G .\datafile.dat

Input parameters:

        timespan:   1
        -------------
        duration: 300s
        warm up time: 5s
        cool down time: 0s
        measuring latency
        random seed: 0
        path: '.\datafile.dat'
                think time: 0ms
                burst size: 0
                software and hardware write cache disabled
                performing mix test (write/read ratio: 40/100)
                block size: 8192
                using random I/O (alignment: 8192)
                number of outstanding I/O operations: 32
                thread stride size: 0
                threads per file: 8
                using I/O Completion Ports
                IO priority: normal



Results for timespan 1:
*******************************************************************************

actual test time:       300.01s
thread count:           8
proc count:             1

CPU |  Usage |  User  |  Kernel |  Idle
-------------------------------------------
   0|  35.31%|   2.49%|   32.82%|  64.69%
-------------------------------------------
avg.|  35.31%|   2.49%|   32.82%|  64.69%

Total IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |      1148518400 |       140200 |       3.65 |     467.33 |   68.764 |   177.241 | .\datafile.dat (1024MB)
     1 |      1105862656 |       134993 |       3.52 |     449.97 |   71.264 |   178.528 | .\datafile.dat (1024MB)
     2 |      1061683200 |       129600 |       3.37 |     431.99 |   74.395 |   184.924 | .\datafile.dat (1024MB)
     3 |      1089929216 |       133048 |       3.46 |     443.49 |   72.456 |   184.804 | .\datafile.dat (1024MB)
     4 |      1099988992 |       134276 |       3.50 |     447.58 |   71.656 |   181.355 | .\datafile.dat (1024MB)
     5 |      1115602944 |       136182 |       3.55 |     453.93 |   70.492 |   175.891 | .\datafile.dat (1024MB)
     6 |      1076895744 |       131457 |       3.42 |     438.18 |   73.068 |   180.855 | .\datafile.dat (1024MB)
     7 |      1126612992 |       137526 |       3.58 |     458.41 |   69.888 |   177.912 | .\datafile.dat (1024MB)
-----------------------------------------------------------------------------------------------------
total:        8825094144 |      1077282 |      28.05 |    3590.88 |   71.458 |   180.165

Read IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |       690298880 |        84265 |       2.19 |     280.88 |   68.483 |   176.451 | .\datafile.dat (1024MB)
     1 |       665673728 |        81259 |       2.12 |     270.86 |   70.630 |   177.404 | .\datafile.dat (1024MB)
     2 |       637288448 |        77794 |       2.03 |     259.31 |   73.995 |   184.461 | .\datafile.dat (1024MB)
     3 |       652034048 |        79594 |       2.07 |     265.31 |   72.569 |   185.260 | .\datafile.dat (1024MB)
     4 |       659439616 |        80498 |       2.10 |     268.32 |   71.175 |   180.766 | .\datafile.dat (1024MB)
     5 |       667975680 |        81540 |       2.12 |     271.80 |   70.073 |   175.241 | .\datafile.dat (1024MB)
     6 |       646021120 |        78860 |       2.05 |     262.86 |   73.246 |   181.189 | .\datafile.dat (1024MB)
     7 |       676167680 |        82540 |       2.15 |     275.13 |   70.110 |   178.506 | .\datafile.dat (1024MB)
-----------------------------------------------------------------------------------------------------
total:        5294899200 |       646350 |      16.83 |    2154.46 |   71.244 |   179.886

Write IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |       458219520 |        55935 |       1.46 |     186.45 |   69.187 |   178.424 | .\datafile.dat (1024MB)
     1 |       440188928 |        53734 |       1.40 |     179.11 |   72.222 |   180.210 | .\datafile.dat (1024MB)
     2 |       424394752 |        51806 |       1.35 |     172.68 |   74.996 |   185.616 | .\datafile.dat (1024MB)
     3 |       437895168 |        53454 |       1.39 |     178.18 |   72.288 |   184.124 | .\datafile.dat (1024MB)
     4 |       440549376 |        53778 |       1.40 |     179.26 |   72.377 |   182.230 | .\datafile.dat (1024MB)
     5 |       447627264 |        54642 |       1.42 |     182.14 |   71.116 |   176.855 | .\datafile.dat (1024MB)
     6 |       430874624 |        52597 |       1.37 |     175.32 |   72.800 |   180.354 | .\datafile.dat (1024MB)
     7 |       450445312 |        54986 |       1.43 |     183.28 |   69.556 |   177.017 | .\datafile.dat (1024MB)
-----------------------------------------------------------------------------------------------------
total:        3530194944 |       430932 |      11.22 |    1436.42 |   71.779 |   180.584


  %-ile |  Read (ms) | Write (ms) | Total (ms)
----------------------------------------------
    min |      0.034 |      0.036 |      0.034
   25th |      0.622 |      0.624 |      0.622
   50th |      1.174 |      1.179 |      1.176
   75th |     47.547 |     47.779 |     47.652
   90th |    190.024 |    196.566 |    192.592
   95th |    470.770 |    475.655 |    472.954
   99th |    935.253 |    935.162 |    935.253
3-nines |   1366.195 |   1379.733 |   1377.489
4-nines |   1741.597 |   1726.357 |   1733.603
5-nines |   2158.595 |   1897.107 |   2121.649
6-nines |   2452.415 |   2219.306 |   2229.552
7-nines |   2452.415 |   2219.306 |   2452.415
8-nines |   2452.415 |   2219.306 |   2452.415
    max |   2452.415 |   2219.306 |   2452.415