Database Scaling

4 Key excerpts on "Database Scaling"

• 

  eBook - ePub

  CompTIA DataSys+ Study Guide

  Exam DS0-001

  • Mike Chapple, Sharif Nijim(Authors)
  • 2023(Publication Date)
  • Sybex

    (Publisher)

  Clear scalability requirements are vital because as the amount of data or the number of users and transactions increase, performance degrades if the database cannot keep up with the demands. As performance degrades, users will experience slow query response times and sluggish applications. You will impact your availability goals if the increased load causes the database to fail. Your goal in meeting scalability requirements is maintaining user satisfaction, regardless of database load. Depending on your needs, you can achieve your scalability objectives with vertical or horizontal scaling.

  Vertical scaling refers to modifying a single server to handle changes in database load. Suppose the amount of data or the number of transactions exceeds the capabilities of the existing database server. In that case, you can scale vertically by adding more processing power, memory, and storage to handle the increase. Figure 4.9 illustrates adding an additional CPU to the database server to accommodate the increase in users.

  FIGURE 4.9

  Vertical scaling

  While vertical scaling is effective and relatively easy to accomplish, the approach has some limitations. The most impactful limitation is that vertical scaling incurs downtime. For example, to achieve the scaling illustration in Figure 4.9 , you have to shut down the database, shut down the server, add the CPU, start the server, and finally start the database. While this approach may be sufficient for a small-scale database, the downtime required may be unacceptable for a mission-critical service. For large, global applications, the load requirements may exceed the capabilities of a single database server.

  Horizontal scaling refers to adding or removing nodes in a server cluster to respond to changes in database load. Horizontal scaling is more operationally complex than vertical scaling, as the horizontal approach requires sophisticated clustering software to manage the addition and removal of database nodes. Despite the increase in complexity, a significant advantage of horizontal scaling is that it allows for greater flexibility than vertical scaling.

  For example, suppose the two-node database cluster in Figure 4.7 is sufficient to meet the average load coming from your application. However, you need to augment the combined capacity of the two servers to handle peak demand. Figure 4.10

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Azure SQL Managed Database Administration

  Efficiently manage and modernize data in the cloud using Azure SQL, 3rd Edition

  • Ahmad Osama, Shashikant Shakya(Authors)
  • 2021(Publication Date)
  • Packt Publishing

    (Publisher)

  7. Scalability

  You can easily scale up or scale down an SQL database or managed instance, either automatically or manually. There are two types of scaling: vertical and horizontal .

  Vertical scaling refers to switching to a higher or lower service tier or vertically partitioning data, which is done to store different schemas on different databases. Horizontal scaling refers to dividing data from a single table into different individual databases. This chapter will teach you how to autoscale SQL databases and shard a database. The chapter also talks about how to run cross-database queries. By the end of this chapter, you will be able to:

  • Perform vertical and horizontal scaling.
  • Create and maintain SQL database shards.
  • Run cross-database elastic queries.
  • Scale a managed instance.

  This chapter covers how to vertically and horizontally scale your system to optimize the performance of your application.

  Vertical scaling

  Vertical scaling refers to increasing or decreasing the resources of an SQL database or managed instance. The resources here refer to DTUs for DTU-based purchasing models and vCores for vCore-based purchasing models.

  Vertical scaling can be of two types: scale-up or scale-down service tiers, or vertical partitions.

  Scale-up or scale-down service tiers

  Scaling up a service tier refers to switching to a higher service tier; for example, switching from General Purpose to Business Critical or switching from General Purpose GP_Gen5_2 to General Purpose GP_Gen5_4 .

  Scaling down a service tier refers to switching to a lower service tier; for example, switching from General Purpose GP_Gen5_4 to General Purpose GP_Gen5_2 , or switching from Business Critical to General Purpose.

  Scaling up a service tier allows you to maintain or improve database performance during peak business hours and scaling down a service tier allows you to save costs during off-peak business hours.

  Service tiers can be changed on the fly with near-zero downtime. When a service tier change request is sent to Azure SQL Database, it first creates a copy of the database in the requested service tier and switches to the database in the new service tier once the copy is ready.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  High-Performance Parallel Database Processing and Grid Databases

  • David Taniar, Clement H. C. Leung, Wenny Rahayu, Sushant Goel(Authors)
  • 2008(Publication Date)
  • Wiley

    (Publisher)

  The size of the database may also increase proportionally to the transactions’ arrival rate. In transaction scale up, N -times as many users are submitting N -times as many requests or transactions against an N -times larger database. This kind of scale up is relevant in transaction processing systems where the transactions are small updates. To illustrate transaction scale up, consider the following example: Assume it takes 10 minutes to complete 100 transactions on a single processor. If the number of transactions to be processed is increased to 300 transactions, and the number of processors used is also increased to 3 processors, the elapsed time remains the same; if it is 10 minutes, then a linear scale up has been achieved (scale up D 1). If, for some reason, even though the number of processors is already increased to 3 it takes longer than 10 minutes, say 15 minutes, to process the 300 transactions, then the scale up becomes 0.67, which is less than 1, and hence a sublinear scale up is obtained. Transaction processing is especially well adapted for parallel processing, since different transactions can run concurrently and independently on separate proces- sors, and each transaction takes a small amount of time, even if the database grows. Data Scale Up Data scale up refers to the increase in size of the database, and the task is a large job whose runtime depends on the size of the database. For example, when sorting a table whose size is proportional to the size of the database, the size of the database is the measure of the size of the problem. This is typically found in online analyti- cal processing (OLAP) in data warehousing, where the fact table is normally very large compared with all the dimension tables combined. To illustrate data scale up, we use the following example: Suppose the fact table of a data warehouse occupies around 90% of the space in the database. Assume

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Big Data

  Concepts, Technology, and Architecture

  • Balamurugan Balusamy, Nandhini Abirami R, Seifedine Kadry, Amir H. Gandomi(Authors)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  Scaling out, the horizontal scalability, adds new servers or components to meet the demand. The additional component added is termed as node. Big data technologies work on the basis of scaling out storage. Horizontal scaling enables the system to scale wider to meet the increasing demand. Scaling out storage uses low cost commodity hardware and storage components. The components can be added as required without much complexity. Multiple components connect together to work as a single entity.
• What is a NewSQL database?
  A NewSQL database is designed to provide scalable performance similar to that of NoSQL systems combining the ACID (atomicity, consistency, isolation, and durability), properties of a traditional database management system.