Tag Archive for: Database

Shannon Flynn

6 Ways to Optimize Your Database for Performance

/in /by

Knowing how to optimize your organization’s database for maximum performance can lead to greater efficiency, productivity, and user satisfaction. While it may seem challenging at first, there are a few easy performance tuning tips that you can get started with.

1. Use Indexing

Indexing is one of the core ways to give databases a performance boost. There are different ways of approaching indexing, but they all have the same goal: decreasing query wait time by making it easier to find and access data.

Indexes have a search key attached to a value or data reference. The index file will direct a query to a record, “bucket” of data, or group of data, depending on the indexing method used. Choosing a good indexing method for your specific needs will reduce strain on your system by making it much easier for data to be located, since a uniform, systematic organization is applied to the entire database.

2. Avoid Using Loops

Many coders learn early on that loops can be both useful and dangerous. It is all too easy to accidentally create an infinite loop and crash your whole system.

Loops are problematic when it comes to database performance because they often are looping redundantly. That is not to say that loops should never be used; they are useful sometimes. It simply depends on the specific case, and removing or minimizing unnecessary loops will help increase performance.

For example, having SQL queries inside of loops is not generally advised, because the system is running the same query numerous times rather than just once. A good rule of thumb is that the more data you have in a loop, the slower it is going to be.

3. Get a Stronger CPU

This fix is a classic in computer science. A CPU with better specs will increase system performance. There are ways, like those above, to increase performance within your system’s organization and coding. However, if you find that your database is consistently struggling to keep up, your hardware might be in need of an upgrade.

Even if the CPU you have seems like it should be sufficient, a CPU that is more powerful than your minimum requirements will be able to handle waves of queries with ease. The more data you are working with and the more queries you need to manage, the stronger your CPU needs to be.

4. Defragment Data

Data defragmentation is a common solution for performance issues. When data gets accessed, written, and rewritten many times, it can get fragmented from all that copying. It is good practice to go in and clean things up on a regular basis.

One symptom of fragmented data is clogged memory, where tables are taking up more room than they should. Crammed memory, as discussed below, is another common cause of a low-performing database.

5. Optimize Queries

There are many ways to go about optimizing queries, depending on the indexing method and the specific needs of your database. When queries aren’t being handled efficiently, the whole system can get backed up, leading to longer wait times for query results. Causes may include duplicate or overlapping indexes and keys or queries that return data that isn’t relevant.

Optimizing queries can be a complex process, but there are some easy steps you can take to work out the best plan for your database and identify its specific inefficiencies.

6. Optimize Memory

Another hardware fix that may help under-performing databases is additional memory space. Databases need some memory “wiggle room” to operate quickly. When your memory is nearly or completely full, things get backed up while the system struggles to find room for creating temporary files and moving things around. It’s a bit like trying to reorganize a living room that is packed floor-to-ceiling with boxes. You need plenty of empty floor space to maneuver and shuffle things around.

Databases work the same way. Increasing your database’s memory capacity will allow it more flexibility and operating room, reducing stress on the system so it can run more efficiently.

Keep It Simple

Many of the tips above focus on simplifying and cleaning up your database. Keep your coding as straightforward and easy-to-navigate as possible. Databases are all about accessing information, so your main priority should simply be to have a well-organized system. Keeping these tips in mind will help you do just that and get your database running at top-notch performance!

Benjamin Aunkofer

CAP Theorem

/in , , , , /by

Understanding databases for storing, updating and analyzing data requires the understanding of the CAP Theorem. This is the second article of the article series Data Warehousing Basics.

Understanding NoSQL Databases by the CAP Theorem

CAP theorem – or Brewer’s theorem – was introduced by the computer scientist Eric Brewer at Symposium on Principles of Distributed computing in 2000. The CAP stands for Consistency, Availability and Partition tolerance.

  • Consistency: Every read receives the most recent writes or an error. Once a client writes a value to any server and gets a response, it is expected to get afresh and valid value back from any server or node of the database cluster it reads from.
    Be aware that the definition of consistency for CAP means something different than to ACID (relational consistency).
  • Availability: The database is not allowed to be unavailable because it is busy with requests. Every request received by a non-failing node in the system must result in a response. Whether you want to read or write you will get some response back. If the server has not crashed, it is not allowed to ignore the client’s requests.
  • Partition tolerance: Databases which store big data will use a cluster of nodes that distribute the connections evenly over the whole cluster. If this system has partition tolerance, it will continue to operate despite a number of messages being delayed or even lost by the network between the cluster nodes.

CAP theorem applies the logic that  for a distributed system it is only possible to simultaneously provide  two out of the above three guarantees. Eric Brewer, the father of the CAP theorem, proved that we are limited to two of three characteristics, “by explicitly handling partitions, designers can optimize consistency and availability, thereby achieving some trade-off of all three.” (Brewer, E., 2012).

CAP Theorem Triangle

To recap, with the CAP theorem in relation to Big Data distributed solutions (such as NoSQL databases), it is important to reiterate the fact, that in such distributed systems it is not possible to guarantee all three characteristics (Availability, Consistency, and Partition Tolerance) at the same time.

Database systems designed to fulfill traditional ACID guarantees like relational database (management) systems (RDBMS) choose consistency over availability, whereas NoSQL databases are mostly systems designed referring to the BASE philosophy which prefer availability over consistency.

The CAP Theorem in the real world

Lets look at some examples to understand the CAP Theorem further and provewe cannot create database systems which are being consistent, partition tolerant as well as always available simultaniously.

AP – Availability + Partition Tolerance

If we have achieved Availability (our databases will always respond to our requests) as well as Partition Tolerance (all nodes of the database will work even if they cannot communicate), it will immediately mean that we cannot provide Consistency as all nodes will go out of sync as soon as we write new information to one of the nodes. The nodes will continue to accept the database transactions each separately, but they cannot transfer the transaction between each other keeping them in synchronization. We therefore cannot fully guarantee the system consistency. When the partition is resolved, the AP databases typically resync the nodes to repair all inconsistencies in the system.

A well-known real world example of an AP system is the Domain Name System (DNS). This central network component is responsible for resolving domain names into IP addresses and focuses on the two properties of availability and failure tolerance. Thanks to the large number of servers, the system is available almost without exception. If a single DNS server fails,another one takes over. According to the CAP theorem, DNS is not consistent: If a DNS entry is changed, e.g. when a new domain has been registered or deleted, it can take a few days before this change is passed on to the entire system hierarchy and can be seen by all clients.

CA – Consistency + Availability

Guarantee of full Consistency and Availability is practically impossible to achieve in a system which distributes data over several nodes. We can have databases over more than one node online and available, and we keep the data consistent between these nodes, but the nature of computer networks (LAN, WAN) is that the connection can get interrupted, meaning we cannot guarantee the Partition Tolerance and therefor not the reliability of having the whole database service online at all times.

Database management systems based on the relational database models (RDBMS) are a good example of CA systems. These database systems are primarily characterized by a high level of consistency and strive for the highest possible availability. In case of doubt, however, availability can decrease in favor of consistency. Reliability by distributing data over partitions in order to make data reachable in any case – even if computer or network failure – meanwhile plays a subordinate role.

CP – Consistency + Partition Tolerance

If the Consistency of data is given – which means that the data between two or more nodes always contain the up-to-date information – and Partition Tolerance is given as well – which means that we are avoiding any desynchronization of our data between all nodes, then we will lose Availability as soon as only one a partition occurs between any two nodes In most distributed systems, high availability is one of the most important properties, which is why CP systems tend to be a rarity in practice. These systems prove their worth particularly in the financial sector: banking applications that must reliably debit and transfer amounts of money on the account side are dependent on consistency and reliability by consistent redundancies to always be able to rule out incorrect postings – even in the event of disruptions in the data traffic. If consistency and reliability is not guaranteed, the system might be unavailable for the users.

CAP Theorem Venn Diagram

Conclusion

The CAP Theorem is still an important topic to understand for data engineers and data scientists, but many modern databases enable us to switch between the possibilities within the CAP Theorem. For example, the Cosmos DB von Microsoft Azure offers many granular options to switch between the consistency, availability and partition tolerance . A common misunderstanding of the CAP theorem that it´s none-absoluteness: “All three properties are more continuous than binary. Availability is continuous from 0 to 100 percent, there are many levels of consistency, and even partitions have nuances. Exploring these nuances requires pushing the traditional way of dealing with partitions, which is the fundamental challenge. Because partitions are rare, CAP should allow perfect C and A most of the time, but when partitions are present or perceived, a strategy is in order.” (Brewer, E., 2012).

Benjamin Aunkofer

Data Warehousing Basiscs

/in , , , , , , , , , /by

Data Warehousing is applied Big Data Management and a key success factor in almost every company. Without a data warehouse, no company today can control its processes and make the right decisions on a strategic level as there would be a lack of data transparency for all decision makers. Bigger comanies even have multiple data warehouses for different purposes.

In this series of articles I would like to explain what a data warehouse actually is and how it is set up. However, I would also like to explain basic topics regarding Data Engineering and concepts about databases and data flows.

To do this, we tick off the following points step by step:

 

Edward Huskin

In-memory Data Grid vs. Distributed Cache: Which is Best?

/in /by

Distributed caching has been a boon for IT professionals in the past due to its ability to make data always available even when offline. However, with the growing popularity of the Internet of Things (IoT) and the increasing amounts of data businesses need to process daily, distributed caching is slowly being overshadowed by a newer and more robust technology solution—the in-memory data grid (IMDG).

Distributed caches allow organizations to combine the amount of memory of computers within a network, boosting performance at minimum cost because there’s no need to purchase more disk storage or more high-end computers. Essentially, a data cache is distributed among all networked computers so that applications can use all available memory when needed. Memory is pooled into a single data store or data cache to provide faster access to data. Distributed caches are typically housed in a single physical server kept on site.

The main challenge of distributed caching today is that in-memory data grids can do distributed caching—and much more. What used to be complicated tasks for data analysts and IT professionals has been made simpler and more accessible to the layman. Data analytics, in particular, has become vital for businesses, especially in the areas of marketing and customer service. Nowadays, there are solutions available that present data via graphs and other visualizations to make data mining and analysis less complicated and quicker. The in-memory data grid is one such solution, and is one that’s gradually gaining popularity in the business intelligence (BI) space.

In-memory computing has almost pushed the distributed cache to a realm of obsolescence, so much so, that the remaining organizations that gold onto it as a solution are those that are afraid to embrace digital transformation or those that do not have the resources. However, this doesn’t mean that the distributed cache is less important in the history of computing. In its heyday, distributed caching helped solve a lot of IT infrastructure problems for a number of businesses and industries, and it did all of that at minimal cost.

Distributed Cache for High Availability

The main goal of the distributed cache is to make data always available, which is most useful for companies that require constant access to data, such as mobile applications that store information like user profiles or historical data. Common use cases for distributed caching include payment computations, external web service calls, and dynamic data like number of views or followers. The main draw, however, is how it allows users to access cached data whether the user is online or offline, which, in today’s always-connected world, is a major benefit. Distributed caches take note of frequently accessed data and keep them in process memory so there’s no need to repeatedly access disk storage to get to that data.

Typically, distributed caches offered simplicity through simple “put” and “get” operations through distributed key/value stores. They’re flexible enough, however, to handle more complicated processes through read-through and write-through instances that allow caches to read and write values to and from disk. Depending on the implementation, it can also handle ACID transactions, data replication, and active backups. Ultimately, distributed caching can help handle large, unpredictable amounts of data without sacrificing read consistency.

In-memory Data Grid for High Speed and Much More

The in-memory data grid (IMDG) is not just a storage solution; it’s a powerful computing solution that has the capability to do distributed caching and more. Designed to use RAM and eliminate the need for constant access to disk-based storage, an IMDG is able to process complex data for large-scale implementations at high speeds. Similar to distributed caching, it “distributes” the workload to a multitude of computers within a network, not only combining available RAM but also the computing power of all available computers.

An IMDG runs specialized software on each computer to enable this and to minimize movement of data to and from disk and within the network. Limiting physical disk access eliminates the bottlenecks usually caused by disk-based storage, since using disk in data processing means using an intermediary physical server to move data from one storage system to another. Consistent data synchronicity is also a highlight of the IMDG. This addresses challenges brought about by the complexity of data retrieval and updating, helping to speed up application development. An IMDG also allows both the application and its data to collocate in a single memory space to minimize latency.

Overall, the IMDG is a cost-effective solution because it all but eliminates the complexities and challenges involved in handling disk-based storage. It’s also highly scalable because its architecture is designed to scale horizontally. IMDG implementations can be scaled by simply adding new nodes to an existing cluster of server nodes.

In-memory Computing for Business

Businesses that have adopted in-memory solutions currently enjoy the platform’s relative simplicity and ease of use. Self-service is the ultimate goal of in-memory computing solutions, and this design philosophy is helping typical users transition into “power users” that expect high performance and more sophisticated features and capabilities.

The rise of in-memory computing may be a telltale sign of the distributed cache’s eventual exit, but it still retains its use, especially for organizations that are just looking to address current needs. It might not be an effective solution in the long run, however, as the future leans toward hybrid data and in-memory computing platforms that are more than just data management solutions.