Transcript Very Large Databases

@murilocmiranda
http://www.sql.pt/
[email protected]
AGENDA
1.
2.
3.
4.
5.
6.
What is a VLDB?
Typical Troubles
OS Config
Instance Config
DB Config
Maintenance
AGENDA
VLDB??
There’s no official definition.
VLDB??
There’s no official definition.
Typically occupying TB range.
VLDB??
There’s no official definition.
Typically occupying TB range.
Billions of rows.
VLDB??
There’s no official definition.
Typically occupying TB range.
Billions of rows.
Typically: OLAP or OLTP with large
amount of users.
VLDB??
Wikipedia…
A very large database, or VLDB, is a database that contains an
extremely high number of tuples (database rows), or occupies an
extremely large physical filesystem storage space. The most common
definition of VLDB is a database that occupies more than 1 terabyte
or contains several billion rows, although naturally this definition
changes over time.
VLDB??
SQL VS. VLDB
Maximum database size
SQL VS. VLDB
Maximum database size
524,272 TB
SQL VS. VLDB
Maximum data file size
Maximum log file size
16 TB
2 TB
A limit of 32.767 files which can be distributed between 32.767 filegroups.
SQL VS. VLDB
TYPICAL TROUBLES
Maintenance
TYPICAL TROUBLES
Backups
Maintenance
TYPICAL TROUBLES
Backups
Maintenance
Indexes
TYPICAL TROUBLES
Backups
Maintenance
Indexes
Statistics
TYPICAL TROUBLES
Backups
Maintenance
Indexes
Statistics
Disaster Recovery
TYPICAL TROUBLES
Performance
Backups
Maintenance
Indexes
Statistics
Disaster Recovery
TYPICAL TROUBLES
OS CONFIG
Perform Volume Maintenance
OS CONFIG
Turning on Instant Initialization to speed
up data file growth
and restores.
OS CONFIG
Storage Layout
OS CONFIG
Plan an efficient storage layout.
OS CONFIG
Plan an efficient storage layout.
Normally, the more spread, the more
effective.
OS CONFIG
Plan an efficient storage layout.
Normally, the more spread, the more
effective.
Suggestion:
SQL BIN SQL DATA SQL IDX SQL LOGS
OS CONFIG
SQL TMP
Mountpoints
OS CONFIG
Mountpoints could be a good strategy.
OS CONFIG
Mountpoints could be a good strategy.
Mountpoints are persistent
directories that point to disk volumes.
OS CONFIG
Pros:
•
•
•
•
Scalable.
Save drive letters (limited to 26).
Easy to add.
No need to restart SQL Server.
OS CONFIG
Cons:
• Looks like a simple folder.
• Need a different approach to monitor.
OS CONFIG
So, if you don’t know the server….
OS CONFIG
Partition Alignment
OS CONFIG
Setting the partition offset properly can
improve up to 30% the performance.
OS CONFIG
Setting the partition offset properly can
improve up to 30% the performance.
Partition alignment increases throughput
(bytes/sec) and reduce disk queues.
OS CONFIG
Setting the partition offset properly can
improve up to 30% the performance.
Partition alignment increases throughput
(bytes/sec) and reduce disk queues.
A partition that is track misaligned will occasionally cause
2 I/O operations instead of one.
OS CONFIG
Unless performed at the time of partition
creation, the default alignment offset (31,5 Kb)
will result in unaligned partitions on versions of
Windows up to and including Windows Server
2003.
OS CONFIG
This offset is associated with hidden sectors,
which basically store partition information.
OS CONFIG
This offset is associated with hidden sectors,
which basically store partition information.
Considering that:
- Each disk sector has 512 bytes.
- Win. 2003 has 63 hidden sectors.
OS CONFIG
This offset is associated with hidden sectors,
which basically store partition information.
Considering that:
- Each disk sector has 512 bytes.
- Win. 2003 has 63 hidden sectors.
512 * 63 = 31,5 Kb
OS CONFIG
Example:
Optimal
values
Stripe Unit Size:
64Kb*
Allocation Unit Size: 64Kb
OS CONFIG
* Defined by storage team.
Example:
Optimal
values
Stripe Unit Size:
64Kb*
Allocation Unit Size: 64Kb
Data (Alloc. Unit Size)
OS CONFIG
Stripe Size
* Defined by storage team.
Optimal solution:
Data (Alloc. Unit Size)
Stripe Size
OS CONFIG
Best Practice:
-
Set an offset of 1024 Kb.
-
-
This value works for mostly disks out there.
Allocation Unit Size = Stripe Unit Size.
The rule:
Offset / Allocation unit = INTEGER
Eg: 1024/64=16
OS CONFIG
WARNIG
Some I/O subsystem vendors intercepting what
Windows is trying to do and are still creating
partitions with the incorrect offset – Even for
Windows 2008+.
ALWAYS check!
Anti-Virus in servers…
is really a need?
OS CONFIG
•
•
•
•
Cost money to license.
Maintenance costs.
Can cause problems in Prod.
Can’t protect to zero-day exploits.
OS CONFIG
What can we do instead?
OS CONFIG
•
•
•
•
Keep the servers patched.
Configure the firewall properly.
Restrict server’s access.
You can install AV… in workstations!
OS CONFIG
What’s the big problem for
SQL Server?
OS CONFIG
• One more app fighting for resources.
• SQL Server files can be locked.
OS CONFIG
How can AV and SQL Server
live together?
OS CONFIG
Add exceptions!
OS CONFIG
Basically the AV should ignore:
•
•
•
•
•
•
•
SQL Server data and log files (.mdf, .ndf and .ldf).
Backup files (.bak and .trn).
Full-text Catalog files.
Trace files (.trc).
ERRORLOG files.
SQL Server binaries folder.
Filestream folder.
OS CONFIG
More on: http://support.microsoft.com/kb/309422
INSTANCE CONFIG
Memory
INSTANCE CONFIG
Memory
This is a very open subject.
INSTANCE CONFIG
Memory
This is a very open subject.
There are lots of discussions about that…
INSTANCE CONFIG
Memory
This is a very open subject.
There are lots of discussions about that…
There’s no perfect formula, because the correct awnser is….
INSTANCE CONFIG
Memory
This is a very open subject.
There are lots of discussions about that…
There’s no perfect formula, because the correct answer is….
… it depends !!
INSTANCE CONFIG
Memory
An efficient general rule…
Baseline: 1 GB for the OS
Up to 16 GB available
• 1 GB for each 4 GB
More than 16 GB
• 1 GB for every 8 GB
INSTANCE CONFIG
Memory
This is for 64 bit servers…
For 32 bit, here is a good article to follow:
http://www.eraofdata.com/understanding-and-configuring-sql-servers-memory-settings/
INSTANCE CONFIG
TempDB
INSTANCE CONFIG
TempDB
Two common behaviors:
INSTANCE CONFIG
TempDB
Two common behaviors:
• Ignore.
• Overvalue.
INSTANCE CONFIG
TempDB
As per Brent Ozar:
“TempDb is the SQL’s public toilet”
INSTANCE CONFIG
TempDB
And this is true!
INSTANCE CONFIG
TempDB
INSTANCE CONFIG
TempDB
There’s a myth:
• tempdb should always have one data file per processor core.
INSTANCE CONFIG
TempDB
There’s a myth:
• tempdb should always have one data file per processor core.
Again….
INSTANCE CONFIG
TempDB
There’s a myth:
• tempdb should always have one data file per processor core.
Again…. It depends!
INSTANCE CONFIG
TempDB
Execute large operations, like a sort or store a huge temporary table,
may be slowed down because of the round-robin operation.
The more files, the more costly.
INSTANCE CONFIG
TempDB
Common wait types on TempDB:
• PAGELATCH_*: Contention for In-memory allocation bitmaps.
• PAGEIOLATCH_*: Contention at the I/O subsystem level.
INSTANCE CONFIG
TempDB
How many tempdb data files should we have?
INSTANCE CONFIG
TempDB
How many tempdb data files should we have?
A recommended approach is:
• Up to 8 cores:
Number of files = Number of cores.
• More than 8 cores:
1. Add 8 files.
2. Monitor PAGELATCH_*.
3. Add 4 more files at a time, if necessary.
INSTANCE CONFIG
TempDB
Other TempDB best practices:
• Isolate the TempDB in a different storage system.
• Depending of the load, you might need to separate LDF and
M(N)DF.
• Use a fast drive (SSD :).
• Set an initial size, equally to all the files.
• Set the auto-growth accordingly.
• If you have a heavy operation using constantly the TempDB,
consider create a staging table into your own database.
INSTANCE CONFIG
TempDB
From SQL Server 2012, local disk TempDB in SQL
Server cluster.
INSTANCE CONFIG
TempDB
From SQL Server 2012, local disk TempDB in SQL
Server cluster.
• More flexibility.
• Use PCIe bus instead of HBA, and have more throughput.
• Data and Log are in SAN, TempDB locally: Avoid congestion
or contention on a shared storage network or array.
INSTANCE CONFIG
DB CONFIG
• Don’t rely on auto-grow.
• You can manage file growth and control the free disk
space and avoids performance problems.
DB CONFIG
• Don’t rely on auto-grow.
• You can manage file growth and control the free disk
space and avoids performance problems.
• Have page checksums turned on.
• To detect damaged pages.
DB CONFIG
• Don’t rely on auto-grow.
• You can manage file growth and control the free disk
space and avoids performance problems.
• Have page checksums turned on.
• To detect damaged pages.
• Make sure auto-stats update is turned on.
• For OLTP consider turning auto-stats update off only
for heavily updated tables, and schedule a job that
periodically updates the statistics for those tables.
DB CONFIG
DB CONFIG
• Make sure you’re managing the transaction log
correctly:
• Full recovery requires log backups.
• No advantage in have multiple log files.
• Control the file growth or this could cause VLF
fragmentation.
• Performance issues.
• Slow backup time.
• Don’t set the log file growth size to a multiple of 4 in older
SQL Server versions.
•
http://connect.microsoft.com/SQLServer/feedback/details/481594/log-growth-not-workingproperly-with-specific-growth-sizes-vlfs-also-not-created-appropriately
DB CONFIG
MAINTENACE
Few questions…
MAINTENANCE
How to meet your SLAs dealing with a TB database?
Is data-loss acceptable?
What about the recovery time?
Are you able to UPDATE STATS, do INDEX MAINTENANCE
and run a INTEGRITY CHECK in time and WITHOUT PROBLEMS?
MAINTENANCE
DISASTER RECOVERY
MAINTENANCE
First of all, think in a Disaster Recovery plan!
SQL Server is not Oracle, we have “free” included options:
• Log Shipping (HA and DR)
• Database Mirroring (HA and DR)
•
DB Snapshot advantage
• Replication (HA, DR and LB)
• AlwaysOn (HA, DR and LB)
• We can still be safe with a storage level replication.
MAINTENANCE
Partition
Compress
Clean
MAINTENANCE
Partition, Compress and Clean
Using the partitioning feature you can devise the maintenance.
MAINTENANCE
Partition, Compress and Clean
Using the partitioning feature you can devise the maintenance.
• You can use the DBCC CHECKFILEGROUP command.
•
DBCC CHECKFILEGROUP and DBCC CHECKDB are. The main difference is that
DBCC CHECKFILEGROUP is limited to the single specified filegroup and required
tables.
MAINTENANCE
Partition, Compress and Clean
Using the partitioning feature you can devise the maintenance.
• Devising a filegroup architecture allows piecemeal restores with
low TTR
• Online piecemeal restore:
•
•
After the PRIMARY FG restore the DB can be online.
The tables will come available while each FG is restored.
• Design the database accordingly:
•
•
Keep the necessary into the PRIMARY FG.
• Configuration tables, indispensable data, etc…
Think in the consistency: keep related tables in the same FG.
MAINTENANCE
Partition, Compress and Clean
Compress backups Vs. Compress Data
• Backup compression:
• More CPU usage to backup/restore (avg ~20%).
• Less time to backup/restore (avg ~40%).
• Good compression ratio.
•
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
• A backup set will not be able to contain both compressed
and uncompressed backups.
• No advantage with TDE enabled.
MAINTENANCE
Partition, Compress and Clean
Compress backups Vs. Compress Data
• Data compression (ROW and PAGE):
• TDE and Data Compression play together!
• Backup and Data Compression can coexist!
MAINTENANCE
Partition, Compress and Clean
Purge and Archive the data
• Purging data:
• If data is needed no more…
• Save storage.
• Faster backups.
• Improves the performance.
MAINTENANCE
Partition, Compress and Clean
Purge and Archive the data
• Archiving data:
• If data is still needed…
• Isolate in a different FG.
• Set as Read-Only: Avoids locking.
• For faster scans: 100% fill factor.
• Update statistics with FULLSCAN.
• You can adapt the backup strategy.
• You can adapt the backup strategy using Partial Backups.
MAINTENANCE
•
This allows you to exclude read-only filegroups.
More about DBCC CHECKDB
• CHECKDB takes time and uses resources.
• Run a DBCC CHECKDB using the WITH PHYSICAL_ONLY option.
•
•
Limits the checking to the integrity of the physical structure of the page and
record headers and the allocation consistency of the database.
Faster, but a full CHECKDB is required periodically.
MAINTENANCE
More about DBCC CHECKDB
• We can divide up the consistency checking over several days, Paul
Randal’s prescription is:
•
Divide tables in two buckets (bigger ones and the rest)
• On Sunday:
• Run a DBCC CHECKALLOC
• Run a DBCC CHECKCATALOG
• Run a DBCC CHECKTABLE on each table in the first bucket
• On Monday, Tuesday, Wednesday:
• Run a DBCC CHECKTABLE on each table in the 2nd, 3rd, 4th buckets,
respectively
• On Thursday:
• Run a DBCC CHECKALLOC
• Run a DBCC CHECKTABLE on each table in the 5th bucket
• On Friday and Saturday:
• Run a DBCC CHECKTABLE on each table in the 6th and 7th buckets,
respectively
MAINTENANCE
More on: http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-consistency-checking-options-for-a-vldb/
More about BACKUPS
• Besides doing PARTIAL BACKUPS we have more options…
• A MULTISTREAM BACKUP is an option to run faster:
DB
File 1
E:
File 2
F:
File 3
G:
MAINTENANCE
More about BACKUPS
• To make sure it will be well stored, we can use a MIRROR.
File 1
File 2
File 3
DB
File 1
E:
File 2
F:
File 3
G:
MAINTENANCE
More about BACKUPS
• If storing to the network:
• Use a separate network card to avoid network congestion.
• Don’t forget about T-LOG backups!
• Create a good backup strategy.
• Verify the backups periodically.
MAINTENANCE
INDEXES MAINTENANCE
• Only rebuild/defrag indexes that are really fragmented (avoid
unnecessary work in short maintenance windows)
• If you defrag instead of rebuild, make sure you manually update
stats.
• Be wary of doing large index maintenance jobs if you use log shipping or
DBM
• They contribute to large log backups
• Index rebuilds are always full-logged when DBM is present
MAINTENANCE
QUESTIONS?
OBRIGADO!
@murilocmiranda
http://www.sql.pt/
[email protected]