A PostgreSQL 13 Feature Deep-dive
PostgreSQL is an open-source relational database solution known for delivering powerful functionality and continually improving. The latest version, 13, continues to build on this foundation. Here are the newest additions and improvements to PostgreSQL.
Improved Indexing and Lookup Systems
If you’re working with largest databases, you’ll find that PostgreSQL 13 offers a much better experience with indexing and lookups. Some ways that this version has improved these features for big databases includes more efficient space usage, better performing indexes, and a stronger query planning functionality. You’ll also notice that queries that have aggregates or partitions involved are faster.
Newly Added Parallelized Vacuuming
The vacuuming feature in PostgreSQL, which reclaims storage space whenever you update and delete rows, has a much-requested upgrade. The latest version offers parallelized vacuuming for your database indexes. The performance for these operations is greatly improved due to this change.
You’re able to set the number of parallel workers on a workload-by-workload basis, so you can focus your resources on the indexes that need vacuumed the most. Another change to this feature is that you can now use autovacuum with data inserts.
Newly Added Incremental Sorting
Another popular user requested feature that made it into PostgreSQL 13 is incremental sorting. This function allows you to speed up your query sorting when you work with data that has been sorted earlier on. These query optimizations can make a significant impact on your database’s performance.
Another query-related improvement is the ability to use extended statistics for your query plans. This feature covers queries constructed with OR and IN/ANY.
Handle Your Duplicate Data in B-Tree Indexes
B-tree indexes in PostgreSQL received a boost through better handling of duplicate data. When you’re working with large amounts of duplication in your system, you can run into a range of performance issues. This feature decreases the space that your database indexes use and also provide a noticeable improvement in query speeds.
Expanded PostgreSQL Hash Aggregation Capabilities
PostgreSQL has an excellent hash aggregation function, but previous versions were limited in the query types that could use it. With PostgreSQL 13, you have an expanded number of grouping set and aggregate queries that benefit from this feature.
Large aggregate queries are freed from being fully in-memory with this change. Another performance boost is found in partitioned table queries. PostgreSQL 13 accomplishes this by directly joining the partitions.
Greater Visibility into PostgreSQL Activity
Databases have a lot of moving parts, and being able to effectively manage them requires as much visibility into system activity is possible. When you have greater access to this information, your organization can make better decisions about how to keep the database safe, ways to optimize performance, and methods for allocating the right number of resources.
PostgreSQL 13 delivers more monitoring capabilities. You can use EXPLAIN to look up WAL usage, keep an eye on streaming base backups, and learn more about the progress of ANALYZE operations. This version also adds a new backup feature called pg_verifybackup. As the name implies, you’re able to check the data integrity of your PostgreSQL backups.
Trusted Extensions Reduce Administrative Overhead
How much time do your database administrators spend on installing PostgreSQL extensions? They probably have a set list of extensions that they know and trust to be used on your business network, and installing it is a routine procedure that takes them away from more pressing matters.
In previous versions of PostgreSQL, only superusers could add new extensions. This limitation changed in 13, as you’re now able to designate trusted extensions. Your database superusers can set up the most commonly used extensions as trusted, allowing users to add those in on their own. A number of the built-in PostgreSQL extensions have this turned on by default, including hstore, pgcrypto, and tablefunc.
Read This Next
You Can’t Put Off a PostgreSQL v9.5 Upgrade Anymore – End of Life is Here
PostgreSQL 13 has an exciting range of new features and performance improvements that make it an excellent choice for an upgrade. Learn more about moving to PostgreSQL 13 and the upgrade process in our white paper.
The post A PostgreSQL 13 Feature Deep-dive appeared first on Datavail.
It’s the End of the Road for MySQL 5.6
There are as many reasons why MySQL is the world’s most popular, open-source, relational database as there are developers who use it.
Since its launch in 1995, it has supplied the global developer community with the database management tools needed to build and deploy today’s uber-sophisticated software applications and systems. Each subsequent iteration has added more value to the already invaluable resource. As MySQL 5.6 reaches its End of Life cycle (EoL), newer versions – 5.7 and the most recent, 8.0(more than 2yrs) – continue to make the work of MySQL users more manageable while also increasing their productivity with innovative new feature set.
Most providers including Oracle, Amazon RDS and Azure Database for MySQL offer MySQL users upgrades to 5.7 and 8.0. Datavail’s professionals can help you make the best upgrade decision for your organization’s needs.
Welcome to MySQL
Version 5.6
Released on February 5, 2013, the MySQL 5.6 version has maintained, over time, a reliable popularity among the world’s app development community. As an open-source program, it has been tuned and refined by thousands of developers. Its most recent iteration provides users with fast, reliable, and scalable database management tools that facilitate corporate and market growth.
The Oracle community elected to discontinue support for 5.6 on February 5, 2021 (although it doesn’t hit its End of Life in Amazon RDS until August 3, 2021). Note that developers will add no further security patches to the programming through the ‘extended time’ between February 5 and August 3. Users who continue using it will experience a higher risk of intrusions.
Version 5.7
The next major release, MySQL 5.7, again increased the value of the system by providing cutting-edge enhancements and additions to the foundation set by 5.6. More than three times as fast as 5.6, MySQL 5.7 also enhances a number of its features.
End of Life for this version is slated for October 2023 Best practice is always to upgrade to the most current version (8.0) if possible.
Version 8.0
MySQL 8.0 first GA was released on April 19, 2018. Not surprisingly, this newest version of the database management program enhances all the productivity elements of previous versions while eliminating outdated or obsolete features that no longer carry value. It is designed to meet developers’ demands as they are tasked with addressing new challenges generated by the next gen mobile, web-based, embedded, and cloud applications.
Upgrade to Which Version?
Because of the EoL of MySQL 5.6, users now face making a tough choice between MySQL 5.7 and 8.0. Several benchmarks were run against. one industry member ran a series of tests to determine how the two matched up. Their conclusion: MySQL 8.0 significantly outperformed 5.7:
- 8.0 remained highly efficient even when the number of threads increased in each of the read, delete, insert and update functions.
- Transaction processing times also increased significantly in 8.0 versus 5.7.
- 8.0 also adds the ability to functional index and invisible indexes.
MySQL 8.0 is loaded with tons of new features and enhancements. However, testing with applications is a must prior to the cutover as there’s no easy roll back from 8.x after upgrades.
Users of MySQL 5.6 now find themselves in a dilemma. Not only is the DB beyond support in Oracle now (and will lose support in AWS RDS in August 2021), but it’s not recommended to upgrade directly from 5.6 to 8.0. Upgrades from the General Availability (GA) versions of 5.7 are supported, and to enjoy continued support, MySQL requires that upgrades start with version 5.7.9 or higher.
For on-premises and virtual implementations, users can choose an IN-PLACE or a LOGICAL upgrade for switching up from 5.7 to 8.0. An IN-PLACE upgrade involves shutting down the MySQL 5.7 server, replacing the binaries with 8.0 binaries, and then launching the 8.0 server using the previous data directory. A LOGICAL upgrade involves SQL dump/load to the newly installed MySQL version. Replication is supported from 5.7 to 8.0 versions.
In RDS AWS, the upgrade from 5.7 to 8.0 is defined as a major upgrade because each version is a major version of the DB. For this process, users must first change the DB engine through AWS CLI, RDS API, or AWS Management console, then follow the steps to complete the upgrade.
Datavail Endorses MySQL 8.0
The database management professionals at Datavail agree with their industry colleagues that MySQL 8.0 offers users the best available features and tools to improve and enhance their organization’s security and performance. In general, the MySQL series of releases has always provided exceptional services through its robust and timely features, its capacity for granular scalability, and its easy integration with today’s native and web-based applications. Version 8.0 adds more benefits to the overall package. Datavail’s favorite 8.0 enhancements (so far):
- The document store! MySQL 8.0 resolves the SQL and NoSQL riddle by introducing X Dev API, enhancing the uses of schema-less JSON collections and SQL tables. The 8.0 upgrade makes it possible to match relational data with JSON documents within the single DBMS. The document store’s reliability, consistency, high availability, and enhanced Transparent Data Encryption (TDE) authentication protocols give experienced and new users unparalleled control over app development across languages and databases.
- SQL enhancements include Common Table Expressions (CTEs) that can be both recursive and non-recursive. By also integrating Windows functions through a standard OVER keyword, MySQL now competes easily with SQLite and PostgreSQL.
- As noted above, schema-less JSON support returns JSON data as relational data, allowing querying JSON results the same way as querying regular relational tables. Additional JSON functions in MySQL 8.0 include added utilities [JSON_STORAGE_SIZE ()] and JSON_ARRAYAGG (). These added features improve the speed of replication and remove ambiguities.
- More Enhanced HA solutions like InnoDB Cluster with Group Replication, InnoDB Replica Set.
- MySQLShelll loaded with a variety of Utilities and easy to script for DBAs and Support teams
Overall, MySQL 8.0 delivers multiple performance, cost, and development features that will improve the work and workloads of every enterprise.
AWS & Oracle & MySQL 8.0
All things considered, upgrading to MySQL 8.0 now appears to be everyone’s best option. Not only will the 5.6 version lose all support by August, but the 5.7 version will lose its support in October 2023, a scant two years away. Planning for Upgrades to MySQL 8.0 is more advisable.
Users on AWS will be pleased to know that its RDS supports DB instances running both 5.7 and each of the minor upgrade versions of 8.0 (8.0.11 through 8.0.23). When upgrading, users can specify the minor version of their choice or let the RDS select the most current default version.
Oracle users will be pleased to know that the MySQL Connector/J 5.1 series is included in Oracle’s Lifetime Support policy, although it does encourage the upgrade to the MySQL Connector/J 8.0 series. (MySQL 5.6 is also covered by Oracle Sustaining Support, although the upgrade to 8.0 is recommended.)
MySQL Deadlines: What You Need to Know for Amazon RDS & Azure
February 4, 2021
- MySQL 5.6 End of Life
April 1, 2021
- You can no longer create new MySQL 5.6 DB instances for Amazon RDS & Azure
October 2023
- MySQL 5.7 End of Life
- End of support from Amazon RDS & Azure on MySQL 5.7
April 2026
- My SQL 8.0 End of Life
- End of support from Amazon RDS & Azure on MySQL 8.0
Final Thoughts
Datavail’s licensed and accredited database administrators are standing by to answer your questions about both MySQL 5.7 and 8.0 and to assist with your upgrade to either. Their long experience with MySQL and deep skill base with database management also make them the team to manage your organization’s data management and database needs. Contact Datavail today.
The post It’s the End of the Road for MySQL 5.6 appeared first on Datavail.
Transforming Information Into Insights: Reporting, Scorecards and Dashboards
Organizations have more data at their fingertips than ever before—but they also need to transform this information into clear, actionable business insights, especially in a visual format.
Brain scientist John Medina, for example, has found that people will remember 65 percent of information presented as a picture three days later, but only 10 percent of spoken information.
The goal of business intelligence (BI) and analytics tools is to enable smarter, data-driven decisions by converting raw data into meaningful visual and text representations. In this article, we’ll talk about some of the most powerful BI and analytics tools that you have on hand: reporting, dashboards and scorecards.
What Is BI Reporting?
BI reporting uses business intelligence and analytics tools to automatically collect and analyze data and generate reports for human consumption, presenting this information in an easily readable and digestible fashion.
The contents of a BI report will differ depending on the underlying data; they may include both text summaries and visual representations. The most common types of charts and visualizations in BI reports include:
- Pie graphs, for percentages or proportional data.
- Line charts, for showing trends over time between independent and dependent variables.
- Waterfall charts, for showing how positive or negative values evolve over time.
- 3D area charts, for describing the relationship between three variables.
- Decision trees, for modeling a decision-making process.
Importantly, BI reports are usually generated on a recurring, scheduled basis; often, they’re created overnight based on the preceding day’s data, so that they can be on key decision-makers’ desks the next morning. This delimited time window distinguishes reports from other BI tools such as dashboards that operate in real time.
What Are BI Scorecards?
BI scorecards are reports that summarize your performance and progress in terms of one or more business metrics and KPIs (key performance indicators). Scorecards are effective because they provide the most salient, valuable information at a glance for decision-makers who are pressed for time—for example, the number of current active users, the number of orders in the past week or the average monthly revenue over the past six months.
What Are BI Dashboards?
BI dashboards are a data visualization tool that displays the real-time status of one or more business metrics and KPIs. The contents of a BI dashboard are usually customizable by the user and include multiple components, such as line graphs, bar graphs, pie charts, tables and status indicators. For ease of use, these components may come with tooltips, labels and text boxes that explain how to interpret the data.
Importantly, BI dashboards are often interactive: they allow users to drill down into the numbers, doing their own research and coming to additional conclusions beyond the surface-level ones presented in the dashboard panel. For example, users might apply a filter to the data based on a date range or geographic location, helping answer ad hoc queries. BI dashboards are also (near) real-time: they are connected to data sources such as databases and spreadsheets, and then refreshed and updated on a regular basis.
BI Reporting Versus Scorecards Versus Dashboards
BI reports, scorecards, and dashboards all have the goal of helping you make better business decisions and forecasts, but they go about it in different ways. Reports are typically non-interactive and based on a specific time range, while dashboards are interactive and up-to-the-minute. Scorecards are specialized reports that monitor a few select metrics, letting readers quickly understand progress on a particular KPI.
When building reports, scorecards, and dashboards as part of your BI workflow, follow good design principles such as:
- “Form follows function”: The visualizations and infographics you select for a given report or dashboard will depend on how they are intended to be used in the big picture. Should your visualization be static or interactive—explanatory or exploratory?
- Remove unnecessary elements: Explanatory visualizations (those intended to emphasize an argument) should contain only the information they need to communicate a particular point. Simplicity is key: with new reports constantly arriving on their desk, readers don’t want to wade through unnecessary details to get to the main point.
Conclusion
Reports, dashboards and scorecards are all valuable, effective components of a good data storytelling initiative. To build them, however, you’ll need the right tools at your disposal. Want to learn more about building out your organization’s BI and analytics initiative? Check out our white paper “From Raw Data to Insightful Stories: Transform Analytics into Innovation.”
The post Transforming Information Into Insights: Reporting, Scorecards and Dashboards appeared first on Datavail.
Top 3 Challenges of a Hyperion 11.2 Upgrade
Oracle Hyperion is one of the world’s leading application suites for enterprise performance management (EPM), helping tens of thousands of customers around the world run cutting-edge analyses of their business processes and uncover hidden insights.
Yet with the great power of Oracle Hyperion also comes great responsibility—in particular, the responsibility to upgrade your Hyperion deployment at regular intervals. This is especially important now due to the upcoming end of support of Hyperion 11.1.x in December 2021.
In this blog post, we’ll discuss the three most common upgrade challenges companies face when completing an upgrade. If you’re ready for solutions, jump to the end for a valuable resource you can download today.
Challenge #1: Performance Testing
Performance testing is the process of determining how a software application or infrastructure performs in response to established input. Application performance management (APM) heavily depends on performance testing: whereas APM seeks to understand and resolve the source of a software application’s performance issues, performance testing helps uncover these issues in the first place.
The types of performance testing include:
- Baseline testing to see how an application performs under normal load
- Stress testing to see how an application performs under abnormally high load
- Capacity testing to determine the number of possible concurrent users and/or transactions
- Longevity testing to see how an application performs after running for an extended period of time
- Regression testing to verify that recent changes have not affected an application’s performance
Using performance testing during an Oracle Hyperion 11.2 upgrade is essential. Hyperion is both a highly complex and vital component of your business processes. If the upgrade substantially impacts your Hyperion performance, you need to know and swiftly fix the issues, so that you aren’t left with post-upgrade regret.
Challenge #2: Minimizing Downtime
One of the reasons why businesses postpone their Hyperion upgrades as long as possible is because of the downtime that they’ll have to undergo. The longer your systems are down, the less productive and efficient your employees may be and the greater the potential for lost profits. While the impact of downtime can sometimes be lessened by scheduling tasks around the workweek, this often isn’t an option for national or global organizations with offices scattered across different time zones.
Often organizations even choose to undergo downtime voluntarily as a “solution” to root cause analysis during or after an upgrade—if all else fails, just try rebooting the machine. However, rebooting causes a ripple effect in terms of inefficiencies and lost productivity and doesn’t actually help you identify the underlying cause of a performance issue.
Challenge #3: Performing Root Cause Analysis
While we’d all like to imagine that our Hyperion upgrade will go as smoothly as possible, this is rarely the case in reality. And when things do go wrong, you need to diagnose the problem efficiently and take immediate action. That’s where root cause analysis (RCA) comes in. RCA is a term describing the tools and techniques for identifying the underlying cause of a problem or performance issue.
Hyperion unfortunately doesn’t come with good RCA tools out of the box; in many cases, users are only alerted to a problem once the service or application has already failed. Trying to put out fires during the Hyperion upgrade distracts system administrators from their work and delays users from regaining access to the tools they need for their jobs.
If you’re a seasoned Hyperion manager, these top three challenges are familiar; if not, you have a peek at what’s to come. But it’s not all bad news. To learn how Datavail can help you overcome these top three challenges, download our white paper, “3 Ways Datavail’s IP Can Jumpstart Your Hyperion Upgrade.”
Read This Next
4 Ways Datavail Prepares Companies for Oracle Hyperion EPM 11.1 End of Support
Staying on top of your enterprise software licenses and expiration dates is crucial—especially when it comes to mission-critical applications like Oracle Hyperion EPM. Download our white paper to learn how to prepare for your EPM upgrade.
The post Top 3 Challenges of a Hyperion 11.2 Upgrade appeared first on Datavail.
What Responsibilities Do Your DBAs Hold After a Migration to Amazon RDS
Platform as a Service (PaaS) offerings deliver many benefits, but you may be wondering about the role your DBAs play when you move to a Database as a Service (DBaaS) platform such as Amazon RDS for SQL Server. Unlike Infrastructure as a Service (IaaS) solutions, you don’t have access to the platform’s underlying systems. DBA responsibilities will change with this strategy, but they’re just as important as ever to the success of your business.
Benefits of Migrating SQL Workloads to Amazon RDS
Before exploring what DBAs do in an Amazon RDS environment, let’s explore the benefits of moving to this DBaaS service.
- Provisioning speed: It only takes a matter of minutes to get new databases setup. This improved agility allows you to quickly get development teams the resources they need to succeed with their projects. As your capacity requirements lessen or grow, you’re able to scale your usage and costs based on your current requirements.
- Automating essential database tasks: Many categories of database tasks are automated so DBAs don’t need to dedicate as much time to baseline operations. Amazon RDS handles high availability, disaster recovery, and routine maintenance tasks.
- Improved efficiency and technical resource allocation: Since many of the tasks associated with the underlying infrastructure are offloaded to AWS in this configuration, you can make better use of your in-house resources and improve your efficiency.
SQL Server on Amazon RDS DBA Responsibilities
DBAs are not rendered obsolete when you adopt a DBaaS solution. Instead, many of their responsibilities shift to strategic projects rather than being caught up in day-to-day duties. This change also marks a move from reactive approaches to proactive ones, as your DBA team has more time and resources to dedicate towards getting the most out of database performance. Here is an overview of the work duties that DBAs have with Amazon RDS for SQL Server.
Developing and Maintaining Database Backup and Recovery Plans
While Amazon RDS has several tools to help with the disaster recovery process, creating plans specific for your databases falls under your DBAs’ responsibilities. Once they create plans, they need to test them and verify that these recovery plans fulfill your business recovery objectives. As your systems change over time, they must update plans to account for new requirements.
Your DBAs perform the same process with high availability to maintain your business continuity. AWS provides the tools, but you need talented DBAs who can get the most out of them.
Monitoring and Optimizing Database Performance
The majority of your DBA team’s time will be spent making databases run as efficiently as possible. They monitor database performance to identify issues, opportunities for optimization based on application and usage, and workload rebalancing based on monitoring insights. As your DBAs fine-tune your databases, you can better support modernized applications, take advantage of new opportunities, and deliver a quality user experience. With more emphasis placed on optimization measures, you also increase your ROI for your database investments. You’re getting more out of the same level of technical resources you used previously without needing to pay substantially higher costs.
Protecting Databases from Breaches
Cyberattacks take many forms. Keeping up with the latest developments makes the difference between identifying threats in advance and reacting to them after they hit your system. Data breaches and loss are costly, especially if sensitive data is accessed. Depending on your industry, you could be at risk of falling out of compliance and incurring fines. Any unexpected downtime also gets in the way of meeting SLAs and other expectations.
Your DBAs are a valuable part of your cybersecurity team., They have many ways to eliminate common exploits, maintain security best practices, and make your databases a less tempting target for attackers.
Planning Database Capacity
Your DBAs will handle workload I/O, storage, and disk space capacity planning to support provisioning the SQL Server instances. With tight control over the capacity, you can reduce your total cost of ownership without needing to compromise on performance.
Creating Extract, Transform, Load Data Pipelines
For many use cases, you’re working with large volumes of data that come from multiple sources. This data needs to be moved to a centralized repository or data warehouse to get the most use out of it, which requires the right data pipelines. Your DBAs set up Extract, Transform, Load pipelines that automate the process of extracting the data, preparing it for use, and then loading it into its destination at scale. These pipelines change over time as you acquire and generate new data sources and technology.
Troubleshooting Databases Quickly and Efficiently
The DBA team may not be handling issues that impact the underlying infrastructure of the DBaaS, but they do have other support responsibilities. They need to monitor the databases and react quickly to any problems to avoid disruptions.
Now that you have a better understanding of your DBA team’s role in a DBaaS environment, explore more about migrating SQL server instances to Amazon RDS in our white paper. If you have additional questions about this process or need more technical resources to successfully migrate to this platform, contact us to discuss your requirements.
The post What Responsibilities Do Your DBAs Hold After a Migration to Amazon RDS appeared first on Datavail.
