1 / 106

Very Large Databases

Very Large Databases. Administration. @ murilocmiranda http://www.sql.pt/ murilo.miranda@gmail.com. AGENDA. AGENDA. What is a VLDB? Typical Troubles OS Config Instance Config DB Config Maintenance. VLDB??. VLDB??. There’s no official definition. VLDB??.

davis
Download Presentation

Very Large Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Very Large Databases Administration @murilocmiranda http://www.sql.pt/ murilo.miranda@gmail.com

  2. AGENDA

  3. AGENDA What is a VLDB? Typical Troubles OS Config Instance Config DB Config Maintenance

  4. VLDB??

  5. VLDB?? There’s no official definition.

  6. VLDB?? There’s no official definition. Typically occupying TB range.

  7. VLDB?? There’s no official definition. Typically occupying TB range. Billions of rows.

  8. VLDB?? There’s no official definition. Typically occupying TB range. Billions of rows. Typically: OLAP or OLTP with large amount of users.

  9. 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.

  10. SQL vs. VLDB

  11. SQL vs. VLDB Maximum database size

  12. SQL vs. VLDB Maximum database size 524,272 TB

  13. 16 TB SQL vs. VLDB Maximum data file size 2 TB Maximum log file size A limit of 32.767 files which can be distributed between 32.767 filegroups.

  14. Typical Troubles

  15. Typical Troubles Maintenance

  16. Typical Troubles Backups Maintenance

  17. Typical Troubles Backups Maintenance Indexes

  18. Typical Troubles Backups Maintenance Indexes Statistics

  19. Typical Troubles Backups Maintenance Indexes Statistics Disaster Recovery

  20. Typical Troubles Performance Backups Maintenance Indexes Statistics Disaster Recovery

  21. OS CONFIG

  22. OS CONFIG • Perform Volume Maintenance

  23. Turning on Instant Initialization to speed up data file growth • and restores. OS CONFIG

  24. OS CONFIG • Storage Layout

  25. Plan an efficient storage layout. OS CONFIG

  26. Plan an efficient storage layout. OS CONFIG • Normally, the more spread, the more effective.

  27. Plan an efficient storage layout. OS CONFIG • Normally, the more spread, the more effective. • Suggestion: SQL BIN SQL DATA SQL IDX SQL LOGS SQL TMP

  28. OS CONFIG • Mountpoints

  29. Mountpoints could be a good strategy. OS CONFIG

  30. Mountpoints could be a good strategy. OS CONFIG Mountpointsare persistent directories that point to disk volumes.

  31. Pros: OS CONFIG • Scalable. • Save drive letters (limited to 26). • Easy to add. • No need to restart SQL Server.

  32. Cons: OS CONFIG • Looks like a simple folder. • Need a different approach to monitor.

  33. OS CONFIG So, if you don’t know the server….

  34. OS CONFIG • Partition Alignment

  35. Setting the partition offset properly can improve up to 30% the performance. OS CONFIG

  36. Setting the partition offset properly can improve up to 30% the performance. OS CONFIG • Partition alignment increases throughput (bytes/sec) and reduce disk queues.

  37. Setting the partition offset properly can improve up to 30% the performance. OS CONFIG • 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.

  38. 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.

  39. OS CONFIG This offset is associated with hidden sectors, which basically store partition information.

  40. 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.

  41. 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

  42. OS CONFIG Example: Stripe Unit Size: 64Kb* Allocation Unit Size: 64Kb Optimal values * Defined by storage team.

  43. OS CONFIG Example: Stripe Unit Size: 64Kb* Allocation Unit Size: 64Kb Optimal values Data (Alloc. Unit Size) Stripe Size * Defined by storage team.

  44. OS CONFIG Optimal solution: Data (Alloc. Unit Size) Stripe Size

  45. 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

  46. 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!

  47. OS CONFIG Anti-Virus in servers… is really a need?

  48. OS CONFIG • Cost money to license. • Maintenance costs. • Can cause problems in Prod. • Can’t protect to zero-day exploits.

  49. OS CONFIG What can we do instead?

  50. OS CONFIG • Keep the servers patched. • Configure the firewall properly. • Restrict server’s access. • You can install AV… in workstations!

More Related