Forem: ScaleGrid

MongoDB ACID Transactions – Keep your Data Safe

ScaleGrid — Fri, 22 Sep 2023 11:49:06 +0000

Most popular relational databases today support “ACID” properties – Atomicity, Consistency, Isolation and Durability. Developers and DBAs (Database Administrators) working with databases have a good understanding of ACID behavior.

However, when working with NoSQL databases like MongoDB cloud, there are some key differences that are important for you to understand. MongoDB offers great flexibility in storage, schema, and scaling, but relaxes some of the ACID properties. It’s essential to understand the differences as you model your data and execute MongoDB commands.

ACID properties—Atomicity, Consistency, Isolation, Durability— are essential for data processing in database systems. They ensure transactions are either entirely successful or not executed at all, safeguarding data integrity.

With enterprises handling immense data, the need for rapid, trustworthy transactions becomes paramount. Handling real-time processing, concurrency, and business-critical transactional systems across multiple servers presents challenges. In this complex landscape, the importance of MongoDB’s support for multi-document ACID transactions continues to grow.

ACID Properties Explained

Databases treat single document transactions as a sequence of operations, ensuring the safe management of storing related data. ACID properties are at the heart of reliable processing, ensuring the database’s integrity even when concurrent transactions or disruptions occur.

MongoDB ACID Principles

Atomicity

Wikipedia defines Atomicity as follows – “In an atomic transaction, a series of database operations either all occur, or nothing occurs. A guarantee of atomicity prevents updates to the database from occurring only partially, which can cause greater problems than rejecting the whole series outright. In other words, atomicity means indivisibility and irreducibility”.

Atomicity ensures that all operations within a database transaction are treated as a single, indivisible unit. Either all the operations are executed or none are, ensuring there’s no halfway point where a transaction is left incomplete.

This principle is crucial for safeguarding the integrity of a database, especially in scenarios where system failures or crashes could occur mid-transaction. For example, in MongoDB, a write operation is atomic on the level of a single document transaction, even if the operation modifies multiple embedded documents within a single document.

In real-world applications, consider financial systems: if a bank transaction fails after debiting an account but before crediting another bank account, financial errors could occur. Atomicity ensures that such halfway scenarios are avoided. If any part of the transaction encounters an issue, the whole transaction is rolled back.

Atomicity works hand-in-hand with the other ACID properties. For instance, while atomicity ensures all-or-nothing execution, durability guarantees that once a transaction is committed, it remains so, even in the face of system failures.

Consistency

Consistency ensures that every transaction in a database brings it from one valid state to another. Before the start of a transaction, the database is in a consistent state, and it should return to this state once the transaction is completed. Any transaction, therefore, doesn’t violate predefined rules or constraints. If, for some reason, a transaction can’t meet these criteria, it’s rolled back, and the database remains unchanged.

While atomicity ensures all parts of a transaction complete or none do, consistency ensures the transaction maintains the database’s integrity. Together, they prevent databases from reaching unreliable states.

Isolation

Isolation addresses the issue of concurrency in databases. When multiple transactions occur simultaneously, isolation ensures that each one is executed in a way that it’s unaware of other concurrent transactions. This gives the illusion that transactions are processed sequentially.

Without proper isolation, simultaneous transactions could interfere with each other, leading to potential data corruption. By keeping transactions separate and unaware of each other, isolation ensures that each transaction maintains the database’s consistent state, even in a multi-user environment.

Durability

Durability promises that once a transaction is committed, it will remain so, even if the system crashes immediately afterward. The changes made during the transaction are permanent and won’t be lost due to any subsequent failures.

Post-transaction, any changes made are stored safely. This assurance is vital for trust in a DBMS. For businesses, this guarantees that once they receive a confirmation, the transaction, whether financial or otherwise, is settled and irreversible.

While atomicity ensures an all-or-nothing approach during transaction processing, durability ensures that this ‘all’ remains intact and unaffected by unforeseen system challenges, cementing the changes permanently.

What are ACID transactions?

ACID transactions are closely intertwined with Database Management Systems (DBMS). These principles are put forth to maintain a high standard of data reliability within DBMS. By adhering to ACID properties, DBMS can ensure error-free transaction processing, even when facing multiple, simultaneous operations.

In an era of massive data influx, ensuring that data operations are consistent and reliable is vital. ACID transactions prevent data anomalies, providing a framework that safeguards data, which in turn reinforces trust in data-driven decisions.

ACID transaction example

Imagine a bank transaction where a user transfers money from savings to checking. Two operations are involved: debiting the savings account and crediting the checking account. If a serious system failure or crash occurs after the debit but before the credit, without ACID compliance, the money could vanish. Atomicity ensures that both operations are completed or none are, protecting the user’s funds.

In a relational database like SQL Server, consider an e-commerce scenario where a customer places an order. The system needs to update inventory, log the transaction, and adjust the customer’s balance.

The interplay of these operations demands that either all succeed or all fail, maintaining database integrity. ACID properties, thus, are universally crucial across database platforms, not just MongoDB, to keep data validity and ensure reliable data processing.

Why are ACID transactions important?

ACID transactions serve as the backbone of data reliability in database systems. They ensure that every transaction is processed rigorously, minimizing risks of data anomalies or corruption.

For businesses, ACID transactions offer peace of mind and operational continuity. In today’s digital age, where data drives decision-making, having a trustworthy and reliable database system is paramount.

ACID transactions safeguard against potential data losses or inaccuracies which could lead to costly mistakes or misinformed strategies. By ensuring data integrity and consistency, these transactions empower businesses to confidently leverage their data, optimizing operations and driving growth.

How do Multi-Document ACID Transactions work in MongoDB?

MongoDB, known for its flexibility and scalability, has evolved to support multi-document ACID transactions, even in its NoSQL environment. Introduced in version 4.0, MongoDB’s transactions behave similarly to those in traditional relational databases, allowing multiple changes to be made in a single transaction across different collections and documents.

Utilizing snapshot isolation, presents a consistent view of data, ensuring that ongoing transactions are not affected by others. Furthermore, if any part of the transaction fails, MongoDB ensures that no changes are committed, preserving atomicity.

While this post focuses on MongoDB ACID transactions, it’s also worthwhile to understand the role of storage engines in MongoDB’s overall performance and capabilities. Different storage engines offer varying levels of ACID compliance, and your choice can significantly affect your database’s efficiency and reliability.

If you’re interested in diving deeper into how different storage engines impact MongoDB’s performance and ACID compliance, we recommend reading our detailed blog post on MongoDB Storage Engines.

Traditional Relational Database Management Systems (RDBMS) and MongoDB differ in their handling of ACID transactions. While both systems aim for the same outcomes in terms of integrity, their architectures differ. RDBMSes focus on structured related data with predefined schemas, whereas MongoDB’s document-oriented approach allows more fluid data structures.

In RDBMS, transactions usually involve structured queries across tables. In contrast, MongoDB’s transactions span collections of JSON-like documents. Despite these structural differences, MongoDB has incorporated critical ACID principles, ensuring that even in a NoSQL world, the sanctity and reliability of data are upheld, drawing parallels in transactional trustworthiness with its relational counterparts.

When should I use MongoDB multi-document transactions?

MongoDB ACID multi-document transactions should be employed when operations span multiple collections or require modifications to multiple documents concurrently. They are ideal for complex use cases where inter-related documents must be updated together, ensuring database integrity and consistency across changes. These transactions become particularly useful in scenarios like inventory management or financial operations where a chain of related data alterations is common.

Benefits and Challenges

The key benefit of using multi-document transactions is the guarantee of ACID properties across multiple operations, ensuring consistent and reliable data updates. However, they might introduce a performance overhead, especially if overused or misconfigured. It’s crucial to evaluate when the complexity of operations justifies their use, balancing the need for integrity with system performance.

Best Practices with MongoDB Database Transactions

When implementing MongoDB ACID transactions, always ensure that the four properties are upheld. Make use of session and commit functionalities to manage your transactions, ensuring that they either complete successfully or revert to the pre-transaction state. Regularly monitor the system for any inconsistencies and rectify them promptly.

Tips for optimization
Limit the scope of transactions to what’s essential, avoiding unnecessary operations.
Monitor transaction durations and keep them as short as possible to reduce contention.
Structure your database schema effectively, which can greatly improve transactional efficiency.

ScaleGrid’s DBaaS solution streamlines MongoDB ACID transactions by offering automated management tools and intuitive interfaces. The platform ensures optimal performance, monitoring, and scalability, simplifying transactional complexities. By leveraging ScaleGrid, businesses can achieve more efficient, reliable, and effortless transaction management without administrative overhead.

ACID vs BASE

ACID (Atomicity, Consistency, Isolation, Durability) and BASE (Basically Available, Soft State, Eventually Consistent) represent two fundamental philosophies in the database world. While ACID emphasizes strict reliability and consistency, often at the cost of availability and partition tolerance, BASE takes a more relaxed approach, prioritizing availability over immediate consistency. In the BASE model, databases ensure that data becomes consistent over time, accepting temporary inconsistencies.

MongoDB primarily aligns with the BASE philosophy, focusing on scalability and flexibility. However, with the introduction of multi-document ACID transactions in its recent versions, MongoDB has made significant strides towards bridging the gap between the two paradigms.

It now offers developers the choice: they can leverage BASE’s scalability and resilience for many use cases while employing MongoDB ACID transactions when strict data integrity is important. This dual capability positions MongoDB uniquely, allowing it to cater to a wide range of application requirements and use cases, from high-availability systems to business-critical transactional processes.

Summary

Throughout this article, we’ve delved deep into the essence of ACID properties, their fundamental role in DBMS, and how MongoDB has embraced these tenets, providing developers with robust tools for transaction management.

We’ve compared the traditional ACID approach to the more modern BASE philosophy, highlighting MongoDB’s unique position in bridging these two paradigms. As enterprises seek reliability and flexibility in their database solutions, understanding these principles becomes crucial.

If you want to know how MongoDB compares to Redis in terms of performance, speed, and scalability, check out our Redis vs. MongoDB Performance blog post.

Elevate Your MongoDB Experience with ScaleGrid

Experience top performance and reliability with ScaleGrid’s ACID-compliant managed MongoDB service, offering features like automated backups, performance analytics, and seamless scaling to improve data integrity.

Book a demo and discover how ScaleGrid can transform your database experience!

PostgreSQL EXPLAIN – What are the Query Costs?

ScaleGrid — Mon, 04 Apr 2022 21:25:27 +0000

Understanding the Postgres EXPLAIN cost

EXPLAIN is very useful for understanding the performance of a Postgres query. It returns the execution plan generated by PostgreSQL query planner for a given statement. The EXPLAIN command specifies whether the tables referenced in a statement will be searched using an index scan or a sequential scan.

Some of the first things you’ll notice when reviewing the output of an EXPLAIN command are the cost statistics, so it’s natural to wonder what they mean, how they’re calculated, and how they’re used.

In short, the PostgreSQL query planner is estimating how much time the query will take (in an arbitrary unit), with both a startup cost and a total cost for each operation. More on that later. When it has multiple options for executing a query, it uses these costs to choose the cheapest, and therefore hopefully fastest, option.

What unit are the costs in?

The costs are in an arbitrary unit. A common misunderstanding is that they are in milliseconds or some other unit of time, but that’s not the case.

The cost units are anchored (by default) to a single sequential page read costing 1.0 units (seq_page_cost). Each row processed adds 0.01 (cpu_tuple_cost), and each non-sequential page read adds 4.0 (random_page_cost). There are many more constants like this, all of which are configurable. That last one is a particularly common candidate, at least on modern hardware. We’ll look into that more in a bit.

Startup Costs

The first numbers you see after cost= are known as the "startup cost". This is an estimate of how long it will take to fetch the first row. As such, the startup cost of an operation includes the cost of its children.

For a sequential scan, the startup cost will generally be close to zero, as it can start fetching rows straight away. For a sort operation, it will be higher because a large proportion of the work needs to be done before rows can start being returned.

To look at an example, let’s create a simple test table with 1000 usernames:


CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username text NOT NULL);
INSERT INTO users (username)
SELECT 'person' || n
FROM generate_series(1, 1000) AS n;
ANALYZE users;

Let’s take a look at a simple query plan, with a couple of operations:


EXPLAIN SELECT * FROM users ORDER BY username;

QUERY PLAN                                                    |
--------------------------------------------------------------+
Sort  (cost=66.83..69.33 rows=1000 width=17)                  |
  Sort Key: username                                          |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17)|

In the above query plan, as expected, the estimated statement execution cost for the Seq Scan is 0.00, and for the Sort is 66.83.

Total costs

The second cost statistic, after the startup cost and the two dots, is known as the "total cost". This is an estimate of how long it will take to return all the rows.

Let’s look at that example query plan again:


QUERY PLAN                                                    |
--------------------------------------------------------------+
Sort  (cost=66.83..69.33 rows=1000 width=17)                  |
  Sort Key: username                                          |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17)|

We can see that the total cost of the Seq Scan operation is 17.00. For the Sort operation is 69.33, which is not much more than its startup cost (as expected).

Total costs usually include the cost of the operations preceding them. For example, the total cost of the Sort operation above includes that of the Seq Scan.

An important exception is LIMIT clauses, which the planner uses to estimate whether it can abort early. If it only needs a small number of rows, the conditions for which are common, it may calculate that a simpler scan choice is cheaper (likely to be faster).

For example:


EXPLAIN SELECT * FROM users LIMIT 1;

QUERY PLAN                                                    |
--------------------------------------------------------------+
Limit  (cost=0.00..0.02 rows=1 width=17)                      |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17)|

As you can see, the total cost reported on the Seq Scan node is still 17.00, but the full cost of the Limit operation is reported to be 0.02. This is because the planner expects that it will only have to process 1 row out of 1000, so the cost, in this case, is estimated to be 1000th of the total.

How the costs are calculated

In order to calculate these costs, the Postgres query planner uses both constants (some of which we’ve already seen) and metadata about the contents of the database. The metadata is often referred to as "statistics".

Statistics are gathered via ANALYZE (not to be confused with the EXPLAIN parameter of the same name), and stored in pg_statistic. They are also refreshed automatically as part of autovacuum.

These statistics include a number of very useful things, like roughly the number of rows each table has, and what the most common values in each column are.

Let’s look at a simple example, using the same query data as before:


EXPLAIN SELECT count(*) FROM users;

QUERY PLAN                                                   |
-------------------------------------------------------------+
Aggregate  (cost=19.50..19.51 rows=1 width=8)                |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=0)|

In our case, the planner's statistics suggested the data for the table was stored within 7 pages (or blocks), and that 1000 rows would be returned. The cost parameters seq_page_cost, cpu_tuple_cost, and cpu_operator_cost were left at their defaults of 1, 0.01, and 0.0025 respectively.

As such, the Seq Scan total cost was calculated as:


Total cost of Seq Scan
= (estimated sequential page reads * seq_page_cost) + (estimated rows returned * cpu_tuple_cost)
= (7 * 1) + (1000 * 0.01)
= 7 + 10.00
= 17.00

And for the Aggregate as:


Total cost of Aggregate
= (cost of Seq Scan) + (estimated rows processed * cpu_operator_cost) + (estimated rows returned * cpu_tuple_cost)
= (17.00) + (1000 * 0.0025) + (1 * 0.01) 
= 17.00 + 2.50 + 0.01
= 19.51

How the planner uses the costs

Since we know Postgres will pick the query plan with the lowest total cost, we can use that to try to understand the choices it has made. For example, if a query is not using an index that you expect it to, you can use settings like enable_seqscan to massively discourage certain query plan choices. By this point, you shouldn’t be surprised to hear that settings like this work by increasing the costs!
Row numbers are an extremely important part of cost estimation. They are used to calculate estimates for different join orders, join algorithms, scan types, and more. Row cost estimates that are out by a lot can lead to cost estimation being out by a lot, which can ultimately result in a suboptimal plan choice being made.

Using EXPLAIN ANALYZE to get a query plan

When you write SQL statements in PostgreSQL, the ANALYZE command is key to optimizing queries, making them faster and more efficient. In addition to displaying the query plan and PostgreSQL estimates, the EXPLAIN ANALYZE option performs the query (be careful with UPDATE and DELETE!), and shows the actual execution time and row count number for each step in the execution process. This is necessary for monitoring SQL performance.

You can use EXPLAIN ANALYZE to compare the estimated number of rows with the actual rows returned by each operation.

Let's look at an example, using the same data again:


QUERY PLAN                                                                                                 |
-----------------------------------------------------------------------------------------------------------+
Sort  (cost=66.83..69.33 rows=1000 width=17) (actual time=20.569..20.684 rows=1000 loops=1)                |
  Sort Key: username                                                                                       |
  Sort Method: quicksort  Memory: 102kB                                                                    |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17) (actual time=0.048..0.596 rows=1000 loops=1)|
Planning Time: 0.171 ms                                                                                    |
Execution Time: 20.793 ms                                                                                  |

We can see that the total execution cost is still 69.33, with the majority of that being the Sort operation, and 17.00 coming from the Sequential Scan. Note that the query execution time is just under 21ms.

Sequential scan vs. Index Scan

Now, let’s add an index to try to avoid that costly sort of the entire table:


CREATE INDEX people_username_idx ON users (username);

EXPLAIN ANALYZE SELECT * FROM users ORDER BY username;

QUERY PLAN                                                                                                                       |
---------------------------------------------------------------------------------------------------------------------------------+
Index Scan using people_username_idx on users  (cost=0.28..28.27 rows=1000 width=17) (actual time=0.052..1.494 rows=1000 loops=1)|
Planning Time: 0.186 ms                                                                                                          |
Execution Time: 1.686 ms                                                                                                         |

As you can see, the query planner has now chosen an Index Scan, since the total cost of that plan is 28.27 (lower than 69.33). It looks that the index scan was more efficient than the sequential scan, as the query execution time is now just under 2ms.

Helping the planner estimate more accurately

We can help the planner estimate more accurately in two ways:

Help it gather better statistics
Tune the constants it uses for the calculations

The statistics can be especially bad after a big change to the data in a table. As such, when loading a lot of data into a table, you can help Postgres out by running a manual ANALYZE on it. Statistics also do not persist over a major version upgrade, so that’s another important time to do this.

Naturally, tables also change over time, so tuning the autovacuum settings to make sure it runs frequently enough for your workload can be very helpful.

If you’re having trouble with bad estimates for a column with a skewed distribution, you may benefit from increasing the amount of information Postgres gathers by using the ALTER TABLE SET STATISTICS command, or even the default_statistics_target for the whole database.

Another common cause of bad estimates is that, by default, Postgres will assume that two columns are independent. You can fix this by asking it to gather correlation data on two columns from the same table via extended statistics.

On the constant tuning front, there are a lot of parameters you can tune to suit your hardware. Assuming you’re running on SSDs, you’ll likely at minimum want to tune your setting of random_page_cost. This defaults to 4, which is 4x more expensive than the seq_page_cost we looked at earlier. This ratio made sense on spinning disks, but on SSDs it tends to penalize random I/O too much. As such a setting closer to 1, or between 1 and 2, might make more sense. At ScaleGrid, we default to 1.

Can I remove the costs from query plans?

For many of the reasons mentioned above, most people leave the costs on when running EXPLAIN. However, should you wish, you can turn them off using the COSTS parameter.


EXPLAIN (COSTS OFF) SELECT * FROM users LIMIT 1;

QUERY PLAN             |
-----------------------+
Limit                  |
  ->  Seq Scan on users|

Conclusion

To re-cap, the costs in query plans are Postgres’ estimates for how long an SQL query will take, in an arbitrary unit.

It picks the plan with the lowest overall cost, based on some configurable constants and some statistics it has gathered.

Helping it estimate these costs more accurately is very important to help it make good choices, and keep your queries performant.

How to Migrate Redis™ Data Using Redis-Shake

ScaleGrid — Fri, 26 Nov 2021 21:26:51 +0000

Migrating ScaleGrid for Redis™* data from one server to another is a common requirement that we hear from our customers. Two of the main reasons we hear are often due to migration of hardware, or the need to split data between servers.

Typically, you want to migrate with minimal downtime while using the standard Redis tools. In this blog post, we will guide you through the process using the open-source tool Redis-Shake. Developed and maintained by the NoSQL Team in the Alibaba-Cloud Database department, Redis-Shake allows you to easily migrate Redis data between Redis clusters. While this guide is more aimed towards ScaleGrid for Redis™* deployments, this guide can be used for other types of Redis deployments as well with some slight modifications.

Without further ado, let’s get started with the migration guide!

Prerequisites

You’ll need a Linux or Windows machine to perform the migration. (If needed, this can be either the source or the target machines).

An important thing to make sure of before you continue is that Redis-Shake does not support SSL-enabled clusters as of now. Please make sure that both the source and the target clusters are not SSL-enabled.

Firewall Rule

Make sure you have created a firewall rule in order to allow your Linux/Windows machine to connect to both your source and target clusters. Refer to this link for more info on how to set this up on ScaleGrid.

Download Redis-Shake

Download and decompress Redis-Shake on the Linux/Windows machine after choosing the latest/stable version from this link. You can also use the following commands below to download the latest version as of today (v2.1.1).


wget https://github.com/alibaba/RedisShake/releases/download/release-v2.1.1-20210903/release-v2.1.1-20210903.tar.gz
tar -xvzf release-v2.1.1-20210903.tar.gz
cd release-v2.1.1-20210903/

Next up, we will show you two different use cases for Redis-Shake.

Migrating data from a Standalone and Master-Slave
Migrating data in Cluster Mode

Migrating Data - Standalone and Master-Slave

In this section, we will show you how to migrate your data in these different setups:

Standalone to Standalone
Master-Slave to Master-Slave
Standalone to Master-Slave (and vice-versa)

1. Gather info

Note down the following info from the Overview tab in the cluster details page from both your source and target cluster:

Hostname: The Connection String is in the format [hostname:port]. It should look something like this: EX-redms-00-redis-master.example.domain.io

(In the case of a Master-Slave cluster, we’ll connect to the master).

Port: 6379
Password: You can find your password and reset it from the console under Credentials

2. Edit Config File

Open your “redis-shake.conf” file in the main folder and edit the following variables:

source.type = standalone source.address = <source_hostname>:6379 source.password_raw = <source_password>

target.type = standalone target.address = <target_hostname>:6379 target.password_raw = <target_password>

3. Sync Data

Run the following command to synchronize data between the source and target cluster:

$ ./redis-shake -type sync -conf redis-shake.conf

4. Wait For Logs

Wait until you see the following information in the logs:

sync rdb done.

This means that the full synchronization has been completed and incremental synchronization begins.

5. Stop Incremental Sync

If +writeBytes=0 is seen for a long time, then that means no new data is incremented. You can stop the incremental synchronization by pressing Ctrl+C. It should look like something like this:

sync: +forwardCommands=0 +filterCommands=0 +writeBytes=0

6. Match Number of Keys

Now follow this link to our help doc which helps you connect to both clusters using redis-cli and then match the number of keys between your clusters using the info keyspace command on both clusters.

And that’s it! You’ve now successfully migrated data from one cluster to another using Redis-Shake. If you have clusters in Cluster Mode, you might want to have a look at our next section in this guide to see what you need to do if you're running that type of cluster.

Migrating Data - Cluster Mode

In this section, we will show you how to migrate data from one Redis deployment in cluster mode to another cluster.

1. Gather info

Note down the following info from the Overview tab in the cluster details page from both your source and target cluster:

Hostname: The Connection String is in the format [hostname1:port], [hostname2:port], [hostname3:port]. It should look something like this: EX-redms-03-redis-master.example.domain.io, EX-redms-06-redis-master.example.domain.io, EX-redms-09-redis-master.example.domain.io
Port: 6379
Password: You can find your password and reset it from the console under Credentials

2. Edit Config File

Open your “redis-shake.conf” file in the main folder and edit the following variables:

source.type = cluster source.address = <source_hostname1>:6379;<source_hostname2>:6379;<source_hostname3>:6379 source.password_raw = <source_password>

target.type = standalone target.address = <target_hostname1>:6379;<target_hostname2>:6379;<target_hostname3>:6379 target.password_raw = <target_password>

3. Sync Data

Run the following command to synchronize data between the source and target cluster:

$ ./redis-shake -type sync -conf redis-shake.conf

4. Wait For Logs

Wait until you see the following information in the logs:

sync rdb done.

This means that the full synchronization has been completed and incremental synchronization begins.

5. Stop Incremental Sync

If +writeBytes=0 is seen for a long time, then that means no new data is incremented. You can stop the incremental synchronization by pressing Ctrl+C. It should look like something like this:

sync: +forwardCommands=0 +filterCommands=0 +writeBytes=0

6. Match Number of Keys

And that’s it! You’ve now successfully migrated data from one cluster in Cluster Mode to another using Redis-Shake. For more info on Redis-Shake and its capabilities, visit their GitHub page to find out more and get the latest version.

*Redis is a trademark of Redis Labs Ltd. Any rights therein are reserved to Redis Labs Ltd. Any use by ScaleGrid is for referential purposes only and does not indicate any sponsorship, endorsement or affiliation between Redis and ScaleGrid.

New and Evolving PostgreSQL Enterprise Features with Recent Releases

ScaleGrid — Wed, 15 Sep 2021 20:03:11 +0000

PostgreSQL also introduced many other breakthrough features in versions 10 through 11, 12 and 13 that make it a real competitor against Oracle, such as partitioning enhancements, parallel query and logical replication. In this blog post, we will showcase some of the new and notable PostgreSQL features included in more recent versions of PostgreSQL.

New & Notable PostgreSQL Features

Partitioning Features
Indexing Features
Authentication
Other Notable Features
Summary

Partitioning Features

Declarative Partitioning

Until PostgreSQL version 9.4, there was no actual partitioning in PostgreSQL. It was achieved only through table inheritance, which offered very limited functionality and performance benefits. Much of the functionality is managed manually through triggers or SQL commands. For example, we had to use triggers to direct a row before INSERT to the correct partition. We have to create indexes for each partition separately. PostgreSQL version 10 gave birth to declarative partitioning, but we still had to create constraints and indexes for each partition.

PostgreSQL 11 comes complete with a very impressive set of new Partitioning features to both help improve performance and also to help make partitioned tables more transparent to applications.

Logical replication of partitioned tables

With PostgreSQL 13, they introduced support for logically replicating partitioned tables. Previously, you were forced to replicate partitions to your standbys individually. Now, however, you can automatically replicate all your partitions at the same time

Partitioning Methods

The currently supported partitioning methods are range, list, and hash.

Keys & Indexes

Full support for foreign keys on partitioned tables was added in PostgreSQL 12. PostgreSQL also boasts support for primary keys, indexes, and triggers on partitioned tables.

Default Partition

PostgreSQL allows the creation of a “default” partition for storing data that does not match any of the remaining partitions. Oracle users will love this feature as it is not available in Oracle Database.

Row Movement

UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions.

Partition Pruning

Improve SELECT performance through enhanced partition elimination strategies during query planning and execution. A new method of performing partition elimination has been added. This new algorithm is able to determine matching partitions by looking at the query’s WHERE clause. The previous algorithm checked each partition, in turn, to see if it could match the WHERE clause of the query. This resulted in an additional increase in planning time as the number of partitions grew.

Partition Pruning During Query Execution

As for prepared statements, query parameters are not known before the execution. Query Planner cannot eliminate partitions during the planning phase as parameters are not known. So, the executor does the partition pruning during execution to access only the partitions that match parameters.

Indexing Features

Covering Index

PostgreSQL now allows you to add non-key columns in the btree index. Since queries typically need to retrieve more columns than just the ones they search on, PostgreSQL allows you to create an index in which some columns are just “payload” and are not part of the search key. It helps in doing just index-only scans to retrieve the required rows.

Reindex Concurrently

Starting PostgreSQL 12, it’s possible to rebuild an index with REINDEX CONCURRENTLY without locking the table for read/writes, just like the Oracle REBUILD INDEX command.

Parallel Index Creation

With parallel index creation (Introduced in PostgreSQL 11, currently applicable only for b-tree indexes), indexes can be created faster up to max_parallel_workers value and setting maintenance_work_mem large enough to hold multiple copies of data. Parallel index creation can significantly reduce your index creation time.

Deduplication of Data in B-Tree Indexes

Sometimes there will be duplicate entries in an index. This means that a leaf node in a B-Tree index with at least two index entries in the same index contains the same data for all the index columns. With the addition of deduplication in PostgreSQL 13, you can group these duplicate B-Tree index entries and gather them in a group entry. The benefit of this is saving space and reduced load on disk and RAM, as you won’t have to duplicate column data. Duplicate entries also cause unwanted index bloating.

Authentication

SCRAM-SHA-256

In PostgreSQL 11 came support for SCRAM-SHA-256 password authentication. This method prevents password sniffing on untrusted connections and allows you to store your passwords in a cryptographically hashed form.

Out of all the currently supported password authentication methods, this is the most secure one.

GSSAPI

GSSAPI allows secure authentication, as well as automatic single-sign-on for systems that support it. This authentication method relies on a GSSAPI-compatible security library. The data sent over the database connection will be unencrypted unless SSL is used; however the authentication itself is secure. GSSAPI with Kerberos authentication is possible with PostgreSQL according to industry standards. When Kerberos is used, a standard principal is used in the format “servicename/hostname@realm”. Any principal included in the keytab used by the server will be accepted by the PostgreSQL server.

LDAP

Operating similarly to password authentication, this authentication method uses LDAP as the verification method. It’s only used to validate username and password pairs, hence the user must exist in the database already for the authentication to work. LDAP authentication operates in either a simple bind mode or what’s called search+bind. Search+bind allows you to use other identifiers compared to the simple bind mode, which only allows the distinguished name, domain name, or email to be used.

Certificate

The certificate authentication method uses SSL certificates to authenticate. Consequently, it’s only available for SSL connections. With certificate authentication, no password is required. The client simply needs to provide a valid and trusted certificate to be able to authenticate. The certificate’s common name will be matched with the database user name, and if a match is found, the client will be logged in.

Other notable features

Parallel Vacuum

With the release of PostgreSQL 13, improvements to the VACUUM command were implemented. The improvement in question is the added parameter option PARALLEL. With PARALLEL, you can perform index vacuum and index cleanup phases of VACUUM. This allows you to parallel vacuum multiple indexes corresponding to a single table.

Parallel Query

Most Reports queries, which generally scan a lot of data, suffer from performance degradation due to needing to scan or aggregate data from many rows even with an index scan. These queries can use only one CPU until 9.4 and run serially.

With parallel query (which offers parallel sequential scan, merge join, hash join, aggregate and other parallel query plan features), these queries can take advantage of multiple workers, and performance can improve from 2x to 10x as per documentation.

Check out this post to learn when to use parallel queries.

Native Logical Replication

PostgreSQL introduced native logical replication in version 10 to provide a more flexible replication option, unlike streaming replication and more like Oracle Streams, to replicate specific tables, columns, or rows. This can be used to split between multiple databases or consolidate from multiple databases. And can also replicate between different major versions of PostgreSQL.

Stored Procedures with Embedded Transactions

Yet another addition that makes PostgreSQL compatible for migrations from Oracle database.

We don’t have any transaction control inside the pgsql program (DO block or function) in version 9.4, and have to use a workaround like dblink to start and commit/rollback transactions.

With stored procedures, which premiered in version 11, we can now implement transaction control within any pgsql structure like while loop, for loop, or if else statement. Although stored procedures are similar to functions, they have to be invoked by CALL command and can work as independent programs.

Generated Columns

Like Oracle database, generated columns in PostgreSQL can store the data automatically computed from other columns within the row. This speeds up queries by not having to compute the value during query execution, and instead, the generated column value is computed on INSERT or UPDATE to the row.

JIT Compilation

PostgreSQL 11, 12 and 13 all support Just-in-Time (JIT) compilation, which was added back in 2018. JIT compilation is the process of turning an interpreted program evaluation into a native program. With JIT compilation, this process can be done at run time. An advantage of JIT is the possibility of generating expression-specific functions that the CPU can natively execute. In turn, the process gives you a speedup.

Page Checksums

Page checksums is a feature that helps you verify the integrity of data stored to disk. Previously, you could only enable page checksums during the initialization of a PostgreSQL cluster. PostgreSQL 12 introduced the ability to enable or disable page checksums in an offline cluster via the pg_checksums command.

Summary

As you can see from this article, PostgreSQL is an ever-evolving database system that brings many powerful features to the table. Every new version adds new exciting features that make it a real competitor to other databases such as Oracle. Its partitioning and indexing features get many new updates that add onto its already expansive toolbox.

If you are looking for a managed solution for your PostgreSQL database, feel free to check out our ScaleGrid for PostgreSQL service. We provide a free 30-day trial that allows you to use all our management features with no restrictions. Pricing starts at just $10/month and gives you access to a fully managed enterprise-grade PostgreSQL hosting solution with 24/7 support.

Redis vs. Memcached – 2021 Comparison

ScaleGrid — Fri, 07 May 2021 23:25:11 +0000

Redis stands for REmote DIctionary Server, created in 2009 by Salvatore Sanfilippo. Memcached, on the other hand, was created in 2003 by Brad Fitzpatrick. Both Redis and Memcached are:

NoSQL in-memory data structures
Written in C
Open source
Used to speed up applications
Support sub-millisecond latency

In 2014, Salvatore wrote an excellent StackOverflow post on when it makes more sense to use Memcached than Redis. In this post, we provide a current and detailed comparison between Redis and Memcached so that you can make an informed choice about their use in your application.

Infographic

This post has been condensed down into an infographic. With this infographic you can easily visualize the results of this comparison to see which one comes out on top in different scenarios. Click the image below to see the full infographic. If you want to read the comparison in text format, click here.

Documentation

To start off, Redis is much more comprehensively documented than Memcached. This makes it easier to learn, administer and use.

Database Model

Redis is primarily a key value store. While keys are binary strings, the advantage with Redis is that the value is not limited to only binary strings. They can be a variety of data structures which enable storing of complex objects and provide a rich set of operations over them. Redis also provides for extensibility via Redis modules. Redis modules are extensions that provide for additional data structures and features not available within the core feature set. Here’s an sample of few features that are now available as modules:

Document Store
Graph DBMS
Search Engine
Time Series DBMS

Memcached is a plain key value store which only supports binary strings as value.

Data Structures

As mentioned above, Redis offers multiple data structure types allowing it to be extremely flexible to use, including Strings, Hashes, Lists, Sets, Sorted Sets, Bitmaps, Bitfields, HyperLogLog, Geospatial indexes, and Streams. You can learn more about these in this Top Redis Use Cases by Core Data Structure Types article.

Image source: https://redislabs.com/redis-enterprise/data-structures/

Memcached only supports plain binary strings which are great for read-only data, so if you don't need all the bells and whistles of Redis, Memcached is a simpler database for you to use.

Database Rank & Popularity

The greater popularity of a database results in a larger community of users, more discussions and user-generated tutorials, and more help and support through third-party tools such as DBaaS platforms and analysis tools to help optimize your deployments.

Redis is the 8th most popular database in the world as of February 2021, according to DB-Engines because of its simplicity, rich data structures, and great documentation. Memcached is currently ranked as the 28th most popular database. When Redis and Memcached are ranked with respect to key-value database models, Redis stands in 1st and Memcached comes 4th. However, if you are looking only for an open-source key-value database, or one that can be deployed on-premise, Memcached comes in 2nd as both Amazon DynamoDB and Microsoft Azure Cosmos DB are both commercial databases that can only be deployed in the cloud.

Architecture

Redis and Memcached both follow client-server architecture. Clients populate the data in the server in the form of key-value.

Redis is single threaded where on the other hand Memcached has a multithreaded architecture. Memcached scales better on a system with more cores that can handle more operations if compute capacity is scaled. However, more than one Redis instance can be initiated on the same system to utilize additional cores.

Ease of Use

As explained above in the Database model section, Redis, being a multi-model database, can be used with any type of data model. In Redis, it is easy to write code as it simplifies complex tasks. Redis has advanced data structures and is not limited to simple string values. For instance, if your application stores data in sets and you want to keep track of sets in a list you can do this easily in Redis. A similar task on Memcached is not possible. But there are other ways to do the same tasks that will require more lines of code.

Memcached, on the other hand, only stores plain string values. So, the application is left to deal with the data structure complexity.

Data Partitioning

Redis supports partitioning of data across multiple node instances. Current users of Redis leverage different techniques like range partitioning, hash partitioning, and consistent hashing for data partitioning. In Redis, data partitioning can be implemented in three different ways:

Client-side partitioning
Proxy-assisted partitioning (example: twemproxy)
Server-side partitioning with query routing within the cluster nodes

Memcached also supports data partitioning across multiple nodes, and consistent hashing is a recommended approach to ensure the traffic load is evenly distributed.

Supported Languages

Redis supports almost all of the most used programming languages, from high-level to low-level languages. Memcached however supports less number of languages compared to Redis, but does support all the popular languages.

Memcached

.Net
C
C++
ColdFusion
Erlang
Java
Lisp
Lua
OCaml
Perl
PHP
Python
Ruby

Redis

C
C#
C++
Clojure
Crystal
D
Dart
Elixir
Erlang
Fancy
Go
Haskell
Haxe
Java
JavaScript (Node.js)
Lisp
Lua
MatLab
Objective-C
OCaml
Pascal
Perl
PHP
Prolog
Pure Data
Python
R
Rebol
Ruby
Rust
Scala
Scheme
Smalltalk
Swift
Tcl
Visual Basic

Transactions

Redis "transactions" are executed with the three below guarantees:

Transactions are serialized and executed sequentially
Either all of the commands, or none, are processed (atomic transactions)
Optimistic locking offers an extra guarantee using check-and-set

Redis makes sure that all only one command from one client machine is executed at once. All the commands in the transactions are executed when the “EXEC” command is called to ensure the atomicity.

Memcached, on the other hand, does not provide transaction management.

Replication

Redis offers a simple leader-follower (master-slave) replication that creates exact copies of the master instances, with these features:

The master keeps on sending data commands to the slave as long as they are connected.
If the connection breaks, the slave will follow partial resynchronization only copying the data that was missed during disconnection.
If partial resynchronization is not possible, then it will try a full resynchronization.

You can also leverage the high availability features, Redis Sentinels or Redis Cluster, for advanced failover protection.

Native Memcached does not support replication, but you can use Repcached, a free open source patch to achieve high availability for your deployment. It offers multi master replication, asynchronous data replication, and supports all Memcached commands.

Snapshots/Persistence

Snapshots are simply a read-only view of your database as it was at a certain point in time. Redis supports snapshots, and by default, Redis saves snapshots of the dataset on disk in a binary file called dump.rdb. You can manually call a snapshot, or customize the frequency or change threshold for running the operation.

Here are the two persistence options Redis supports:

RDB persistence
AOF persistence

RDB stands for “Redis Database Backup”. It is a compact, point-in-time snapshot of the database at a specific time. It takes up less space, maximizes Redis performance, and is good for disaster recovery.

AOF stands for “Append Only File”. AOF keeps track of all the commands that are executed, and in a disastrous situation, it re-execute the commands to get the data back. This method takes more space as all the commands are executed again, and is not a very durable method of snapshotting.

Memcached on the other hand does not support on disk persistence.

Server-Side Scripts

Lua is the embedded scripting language for your Redis server, available as of version 2.6, which lets you perform operations inside Redis to simplify your code and increase performance. The two main functions used to evaluate scripts using the Lua interpreter are:

EVAL
EVALSHA

When Lua script is being executed all other requests are blocked as shown in the figure below.

Redis also includes Lua scripts debugger in version 3.2 which makes writing complex scripts easier and helps in boosting performance.

Memcached does not support any server-side scripting.

Scalability

There are two techniques to horizontally scale your Redis database:

Adding shards in Redis Clusters
Adding nodes to a Redis HA (master/replica) setup

You can also vertically scale your Redis setup when you need more memory or compute. It can be done without downtime if you have a HA setup or you use Redis Cluster technology.

The Memcached server doesn’t provide a mechanism to distribute data across nodes (sharding). So, in Memcached, horizontal scalability is as simple as adding more nodes - the problem of partitioning your data into different shards will have to be done on the application/client level. There are some open source tools that can help you with this.

Communication Protocol

Redis uses TCP as a network protocol and does not support UDP.

Memcached supports both the TCP and UDP communication protocols. Data is sent to the Memcached server in two forms:

Text lines: Send commands and receive responses from the server.
Unstructured data: Receive or send value information for a given key, and data is returned in the same format provided.

Supported Cache Eviction Policies

Redis supports different types of eviction policies. Let's take a look at some.

noeviction: In “noeviction” an error is returned when memory reaches it bound.

allkeys-lru: Lru stands for “least recent used”. This policy removes the least recently used data.

allkeys-lfu: Lfu stands for “least frequently used”. This policy removes the least frequently used data.

allkeys-random: This policy removes the data randomly.

volatile-lru: Volatile data is with expiration data set. This policy removes the least recently used volatile data.

volatile-lfu: Volatile data is with expiration data set. This policy removes the least frequently used volatile data.

volatile-random: This policy removes the volatile data randomly.

volatile-ttl: “TTL” stands for time to live. This policy removes the data that have the shortest time to live.

Memcached uses LRU algorithm to evict data when space is required. It first searches for the already expired data to delete if expired data is not available the LRU algorithm is used.

Publish & Subscribe Messaging

Redis supports Pub/Sub messaging (publish and subscribe). There are three commands that are used for this purpose.

The client uses:

Subscribe
Unsubscribe

Subscribe and unsubscribe are used to get messages from a specific channel.

The server uses:

Publish

The “publish” is used to push data to the clients.

Memcached does not support publish and subscribe messaging.

Streams Support

Redis supports Kafka-like streams with 5.0 or higher version using a new data structure “Redis Streams”. Redis Streams has the concept of consumer groups, like Apache Kafka, that lets client applications consume messages in a distributed fashion, making it easy to scale and create highly available systems.

Memcached does not offer native support for Streams, but there are open source library tools like Kafcache for stream processing at low latency.

Geospatial Support

Redis has a data structure called Geospatial indexes that stores the longitude and latitude data of a location. You can perform different operations on the geospatial data, like calculating the distance between two points or finding nearby places.

Memcached does not have any special data structures to handle geospatial data.

Performance

A performance comparison between in-memory key-value data stores is more of an intellectual exercise than of any practical importance - unless you are deploying systems at such scale that this becomes interesting as a cost-saving measure. This is because such stores are IO bound and usually the network latency might play a bigger role in application perceived latency than the database latency.

A more practical performance aspect is storage efficiency - how much data can be packed in the same amount of memory. Even here, the internal data structures used by Redis vary based on the data size. So any discussion on performance between these databases should be taken with a pinch of salt.

Let’s take a look at some comparisons shown in a 2016 research paper. In this paper, the authors experiment the widely used in-memory databases to measure their performance in terms of:

The time taken to complete operations.
How efficiently they use memory during operations.

Database versions used in the paper:

Database	Version
Redis	3.0.7
Memcached	1.4.14

Write Operation

While writing data, as you can see, in the table below Memcached shows exceptional speed even after the number of records moves up to million.

The calculated time to write key-value pairs (ms)

	Number of records
Database	1,000	10,000	100,000	1,000,000
Redis	34	214	1,666	14,638
Memcached	23	100	276	2,813

Read Operation

Reading data stays almost consistent in Redis even for a million records but in Memcached as the number of records go up the time also increases a little bit.

The elapsed time to read value corresponding to a given key per database (ms)

	Number of records
Database	1,000	10,000	100,000	1,000,000
Redis	8	6	8	8
Memcached	9	14	14	30

Memory Use

While discussing memory usage, Redis is always the best as you can see in the results.

Memory usages of in-memory databases for write operation (MB)

	Number of records
Database	1,000	10,000	100,000	1,000,000
Redis	2.5	3.8	4.3	62.7
Memcached	5.3	27.2	211	264.9

As you can see Redis is better than Memcached.

Memory usages of in-memory databases for delete operation (MB)

	Number of records
Database	1,000	10,000	100,000	1,000,000
Redis	0	0	0	0
Memcached	2.2	2.1	2.2	2.2

Transport Layer Security (TLS) Support

Redis has native TLS support starting Redis 6.0. Earlier versions of Redis recommended the use of stunnel for providing TLS support.

Memcached 1.5.13 and above versions support authentication and encryption via TLS. This feature is still in experimental stages.

Authentication

Up to Redis 5.x, Redis only supported a simple password based authentication. This password was stored in plaintext on the server. Redis in version 6.0 onwards supports a fully featured ACL.

Memcached version 1.4.3 and greater has SASL support. Previously Memcached had no authentication layer.

Summary

Redis and Memcached are both great and have applications in different areas. Redis being developed later has many advanced functionalities and has great documentation and community.

mysqldump Best Practices: Part 2 - Migrations Guide

ScaleGrid — Mon, 12 Apr 2021 22:18:25 +0000

In the second and final part of our mysqldump best practices we will talk about how to handle the migration and import for stored program objects and views from your MySQL database. To read more about the prerequisites for a successful dump and restore operation for large MySQL databases, check out the first part of this 2-part blog series.

mysqldump Best Practices

Part 1 - MySQL Prerequisites
Part 2 - Migrations Guide

Importing your stored procedures, functions and triggers

By default, mysqldump imports views and triggers. However it does not import procedures, functions and events. To import procedures and functions, the --routines option should be specified, and to import events, the --events option should be specified.

1. Importing triggers

Mysqldump will attempt to dump all the triggers in your database by default. To be able to dump a table's triggers, you must have the TRIGGER privilege for the table. If the dump user does not have this privilege, triggers will be skipped and mysqldump will not throw any error. So don’t be surprised if you don't see any triggers imported to your destination database.

2. Importing events

To import events, you need to specify --events option while invoking the mysqldump utility. This option requires the EVENT privileges for those databases. Again, mysqldump will silently skip events if the dump user does not have these privileges, even if you have specified --event option when invoking mysqldump.

3. Importing functions and stored procedure

To import routines, you need to specify --routines option while invoking the mysqldump utility. This option requires the global select privileges. Even in this case, mysqldump will silently skip functions and procedures if the dump user does not have these privileges, even if you have specified --routines option when invoking mysqldump.

3.1 Importing non deterministic functions

A stored program that modifies data is called non deterministic if it does not produce repeatable results. Example rand() function. It is especially challenging to use such functions in replicated setups, as they can result in different data on source and replica. To control such possibilities, MySQL imposes certain restrictions on function creation if binary logs are enabled.

By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:

ERROR 1418 (HY000) at line 181: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_funable)

So if your function is not declared as deterministic on the source, and binary logging is enabled on your destination, you will see the above error during the restore of the dump. Hence it is important to understand the deterministic nature of your functions upfront. If you are sure that your functions are deterministic, you need to turn on the log_bin_trust_function_creators configuration on your destination before the restore operation. When enabled, MySQL allows creation of such functions even when binary logging is enabled.

4. SQL SECURITY characteristic of the stored routines and views.

MySQL allows a SQL SECURITY context to be specified while creating the store programs or views. The SQL SECURITY characteristic can be specified as DEFINER or INVOKER. If the SQL_SECURITY context is DEFINER, the routine executes using the privileges of the account named in the routine DEFINER clause. If the context is INVOKER, routine executes using the privileges of the user who invokes it. The default value is DEFINER.

If you are restoring stored routines or views, you need to ensure the definer user account exists on your destination database with appropriate grants. Otherwise you will encounter failures during restore.

Let's demonstrate this with an example related to views.

Let's suppose you have Views V1 and V2 defines as below:

CREATE definer=admin@'%' VIEW mydb.V1 AS SELECT * FROM solution_table; CREATE definer=admin@'%' VIEW mydb.V2 AS SELECT * FROM V1 where num1=10;

Note that views are dumped by default by mysqldump and if you don’t have the user ‘admin’ on your destination, you will encounter the below error during the restore operation:

Command failed with error - ERROR 1449 (HY000) at line 206 in file: '/mysql_data/mysqldump/sqldump_1582457155758.sql': The user specified as a definer ('admin'@'%') does not exist.

Note that it is not just sufficient to ensure the user exists, but the user needs to have appropriate privileges to execute the views. For example if the user admin@'%' exists on the destination, but does not have SELECT privileges on the mydb database, you will see an error message:

'/mysql_data/mysqldump/sqldump_1582456858033.sql':View 'mydb.V2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

Summary

In this 2-part blog series, we covered important prerequisites you need to handle to ensure successful migration of your data and stored programs. ScaleGrid MySQL hosting handles these guidelines to provide a smooth experience while importing your data to ScaleGrid platform. Please share with us your experience and best practices you adopt for MySQL data migrations!

Which Is The Best PostgreSQL GUI? 2021 Comparison

ScaleGrid — Wed, 17 Mar 2021 16:11:50 +0000

PostgreSQL graphical user interface (GUI) tools help open source database users to manage, manipulate, and visualize their data. In this post, we discuss the top 6 GUI tools for administering your PostgreSQL deployments. PostgreSQL is the fourth most popular database management system in the world, and heavily used in all sizes of applications from small to large. The traditional method to work with databases is using the command-line interface (CLI) tool, however, this interface presents a number of issues:

It requires a big learning curve to get the best out of the DBMS.
Console display may not be something of your liking, and it only gives very little information at a time.
It is difficult to browse databases and tables, check indexes, and monitor databases through the console.

Many still prefer CLIs over GUIs, but this set is ever so shrinking. I believe anyone who comes into programming after 2010 will tell you GUI tools increase their productivity over a CLI solution.

Why Use a GUI Tool?

Now that we understand the issues users face with the CLI, let’s take a look at the advantages of using a PostgreSQL GUI:

Shortcut keys make it easier to use, and much easier to learn for new users.
Offers great visualization to help you interpret your data.
You can remotely access and navigate another database server.
The window-based interface makes it much easier to manage your PostgreSQL data.
Easier access to files, features, and the operating system.

So, bottom line, GUI tools make PostgreSQL developers' lives easier.

Conclusion

Let’s summarize our top PostgreSQL GUI comparison. Almost everyone starts PostgreSQL with pgAdmin. It has great community support, and there are a lot of resources to help you if you face an issue. Usually, pgAdmin satisfies the needs of many developers to a great extent and thus, most developers do not look for other GUI tools. That’s why pgAdmin remains to be the most popular GUI tool.

If you are looking for an open source solution that has a better UI and visual editor, then DBeaver and OmniDB are great solutions for you. For users looking for a free lightweight GUI that supports multiple database types, HeidiSQL may be right for you. If you are looking for more features than what’s provided by an open source tool, and you’re ready to pay a good price for it, then Navicat and DataGrip are the best GUI products on the market.

While I believe one of these tools should surely support your requirements, there are other popular GUI tools for PostgreSQL that you might like, including Valentina Studio, Adminer, DB visualizer, and SQL workbench. I hope this article will help you decide which GUI tool suits your needs.

Which Is The Best PostgreSQL GUI? 2019 Comparison

Here are the top PostgreSQL GUI tools covered in our 2019 post:

pgAdmin
DBeaver
Navicat
DataGrip
OmniDB

Oracle Cloud Breakdown – Database Hosting Costs on OCI

ScaleGrid — Fri, 05 Mar 2021 00:25:01 +0000

When considering a new cloud provider, the big names come to mind - AWS, Azure, and Google Cloud. If you’re a developer, you might even be considering a dev-friendly cloud like DigitalOcean or Linode. But, did you know there’s a (relatively) new player in the cloud game? Oracle Cloud Infrastructure (OCI) is a cloud computing service introduced by, you guessed it, Oracle, to claim their stake in the ever-growing cloud services market. In this post, we’re going to compare Oracle Cloud costs vs. popular cloud providers for fully managed database hosting for MySQL, PostgreSQL, Redis™ and MongoDB® database.

Jump to the Oracle Cloud pricing comparison for:

MySQL database
PostgreSQL database
Redis™ database
MongoDB® database

An Overview on Oracle Cloud

Before we get started, let’s talk a little bit about Oracle Cloud. OCI was made generally available less than 5 years ago in 2016 under the name Oracle Bare Metal Cloud Services, only to be rebranded 2 years later as Oracle Cloud Infrastructure. In this short period of time, Oracle Cloud already has a deep portfolio of services rivaling any existing cloud on the market, including compute, storage, networking, governance, database management, load balancing and edge services to name just a few. Their growth is not just in services though, they’ve ensured their availability. With over 29 cloud regions available across the globe, they are aiming to offer proximity, data sovereignty requirements, and disaster protection for every country.

Coming late to the game, Oracle Cloud needed to make a name for itself, a new name that is, and they chose to rebrand as the high performance + lower cost alternative to AWS. In this post, we are going to focus on costs, but look out for our benchmark reports on Oracle Cloud performance vs. leading cloud providers.

So, what type of customer is Oracle Cloud targeting? As the company who dominated the on-premises infrastructure and databases market for decades, Oracle surely felt the massive transition of on-premises migrations to cloud. With the new Oracle Cloud services, Oracle is targeting “enterprises looking for higher performance, lower costs, and easier cloud migration for their existing on-premises applications.” In other words, the OCI strategy is actually highly focused on customer retention - migrating their existing on-premises customers to their cloud product vs. losing them to AWS, Azure or GCP.

Now that we have a brief history on Oracle Cloud Infrastructure, let’s dig into pricing.

Oracle Cloud Pricing

According to the Oracle website, OCI offers 44% lower compute costs for HPC vs. AWS. In this pricing comparison, we are going to see which cloud provider offers the most cost-effective pricing for fully managed database hosting at ScaleGrid, covering MySQL, PostgreSQL, Redis™ and MongoDB® database.

In order to represent as many scenarios as possible, we are going to compare OCI vs. top cloud providers across ScaleGrid standalone and 3-node replica set deployments, and over 4 common deployment sizes by RAM: 8GB, 16GB, 32GB and 64GB of RAM. Each cloud offers slightly different configurations, so any difference in RAM size will be noted for reference in the pricing comparison tables. Note, all pricing is for our all-inclusive Dedicated Hosting plans, though we additionally offer a Bring Your Own Cloud (BYOC) model that allows you to host through your own Oracle Cloud account. You can learn more about the differences in our Dedicated vs. BYOC blog post.

MySQL on OCI Pricing

We’re going to start with MySQL, the most popular open source database run on Oracle Cloud! MySQL is owned by Oracle, making it a natural fit for Oracle Cloud resources. Let’s take a look at MySQL hosting pricing across clouds at ScaleGrid:

MySQL Standalone Deployment Pricing

MySQL Standalone	8GB	16GB	32GB	64GB
OCI	$90	$180	$300 (30GB RAM)	$600 (60GB RAM)
AWS	$191	$302	$605	$1,178
Azure	$191	$302	$605	$1,178
GCP	$191 (7.5GB RAM)	$302 (15GB RAM)	$605 (30GB RAM)	$1,178 (60GB RAM)
DigitalOcean	$120	$240	$480	$700
Linode	$120	$240	$480	$700

As we can see in the above table, Oracle Cloud at ScaleGrid is by far the most cost-effective cloud for standalone MySQL deployments. On average, Oracle Cloud vs. AWS, Azure and GCP saves you 48.2% on your fully managed database hosting costs, and 25.4% for Oracle Cloud vs. DigitalOcean and Linode.

MySQL Replica Set Deployment Pricing

MySQL Replica Set	8GB	16GB	32GB	64GB
OCI	$360	$700	$1,400 (30GB RAM)	$2,400 (60GB RAM)
AWS	$560	$847	$1,696	$3,299
Azure	$560	$847	$1,696	$3,299
GCP	$560 (7.5GB RAM)	$847 (15GB RAM)	$1,696 (30GB RAM)	$3,299 (60GB RAM)
DigitalOcean	$280	$560	$1,120	$2,240
Linode	$280	$560	$1,120	$2,240

Now, when we compare MySQL replica set deployment pricing at ScaleGrid for Oracle Cloud vs. AWS, Azure and GCP, we still see an average of 24.4% lower pricing. But, when we compare MySQL on Oracle Cloud vs. Linode and DigitalOcean, Oracle Cloud is actually 21.4% more expensive.

PostgreSQL on OCI Pricing

Next, we’re going to look at PostgreSQL hosting pricing for Oracle Cloud vs. AWS, Azure, GCP, DigitalOcean and Linode at ScaleGrid:

PostgreSQL Standalone Deployment Pricing

PostgreSQL Standalone	8GB	16GB	32GB	64GB
OCI	$90	$180	$300 (30GB RAM)	$600 (60GB RAM)
AWS	$191	$302	$605	$1,178
Azure	$191	$302	$605	$1,178
GCP	$180 (7.5GB RAM)	$300 (15GB RAM)	$600 (30GB RAM)	$1,300 (60GB RAM)
DigitalOcean	$120	$240	$480	$700
Linode	$120	$240	$480	$700

Just like we saw with MySQL, standalone PostgreSQL hosting pricing on Oracle Cloud is 48.2% less expensive than AWS, Azure, or GCP on average at ScaleGrid, and 25.4% less expensive than DigitalOcean and Linode.

PostgreSQL Replica Set Deployment Pricing

PostgreSQL Replica Set	8GB	16GB	32GB	64GB
OCI	$360	$700	$1,400 (30GB RAM)	$2,400 (60GB RAM)
AWS	$560	$847	$1,696	$3,299
Azure	$560	$847	$1,696	$3,299
GCP	$700 (7.5GB RAM)	$1,400 (15GB RAM)	$2,400 (30GB RAM)	$4,800 (60GB RAM)
DigitalOcean	$280	$560	$1,120	$2,240
Linode	$280	$560	$1,120	$2,240

Again, Oracle Cloud pricing vs. AWS and Azure averages 24.4% lower for PostgreSQL replica set pricing at ScaleGrid. PostgreSQL high availability users see even greater savings at Oracle Cloud vs. GCP with 47.6% lower pricing on average. However, Linode and DigitalOcean on average are 21.4% less expensive than Oracle Cloud for PostgreSQL replica set deployments at ScaleGrid.

ScaleGrid for Redis™ on OCI Pricing

Now, let’s take a look at ScaleGrid for Redis™ hosting to see how pricing for Oracle Cloud compares to AWS, Azure, GCP, DigitalOcean and Linode.

Redis™ Standalone Deployment Pricing

Redis™ Standalone	8GB	16GB	32GB	64GB
OCI	$75 (7.2GB RAM)	$147 (15GB RAM)	$293 (28GB RAM)	$538 (54GB RAM)
AWS	$147 (7GB RAM)	$293 (14GB RAM)	$538 (28GB RAM)	$1,035 (57GB RAM)
Azure	$147 (6.3GB RAM)	$293 (15GB RAM)	$538 (29GB RAM)	$1,035 (58GB RAM)
GCP	$147 (7.5GB RAM)	$293 (15GB RAM)	$538 (30GB RAM)	$1,035 (60GB RAM)
DigitalOcean	$120 (7GB RAM)	$240 (14GB RAM)	$480 (29GB RAM)	$700 (57GB RAM)
Linode	$120 (7GB RAM)	$240 (14GB RAM)	$480 (29GB RAM)	$700 (57GB RAM)

When comparing Oracle Cloud vs. AWS, Azure and GCP pricing at ScaleGrid for Redis™, Oracle Cloud is almost half to cost, averaging 48.1% less expensive than these top cloud providers. We are also seeing the largest savings yet for DigitalOcean and Linode, where Oracle Cloud is averaging 34.6% less expensive for standalone Redis™ deployments at ScaleGrid.

Redis™ Replica Set Deployment Pricing

Redis™ Replica Set	8GB	16GB	32GB	64GB
OCI	$480 (7.2GB RAM)	$911 (15GB RAM)	$1,796 (28GB RAM)	$3,438 (54GB RAM)
AWS	$911 (7GB RAM)	$1,796 (14GB RAM)	$3,438 (28GB RAM)	$6,760 (57GB RAM)
Azure	$911 (6.3GB RAM)	$1,796 (15GB RAM)	$3,438 (29GB RAM)	$6,760 (58GB RAM)
GCP	$911 (7.5GB RAM)	$1,796 (15GB RAM)	$3,438 (30GB RAM)	$6,760 (60GB RAM)
DigitalOcean	$280 (7GB RAM)	$560 (14GB RAM)	$1,120 (29GB RAM)	$2,240 (57GB RAM)
Linode	$280 (7GB RAM)	$560 (14GB RAM)	$1,120 (29GB RAM)	$2,240 (57GB RAM)

Oracle Cloud pricing continues to undercut AWS, Azure and Google Cloud for Redis™ replica set deployments at ScaleGrid with an average of 48.4% lower pricing across these RAM sizes. But, when we compare OCI vs. Linode and DigitalOcean, it averages 62% higher pricing for Redis™ at ScaleGrid using a high availability replica set configuration.

ScaleGrid for MongoDB® Database on OCI Pricing

Finally, let’s look at ScaleGrid for MongoDB® Database. In this comparison, we are going to narrow it down to Oracle Cloud vs. DigitalOcean and Linode.

MongoDB® Database Standalone Deployment Pricing

MongoDB® Database Standalone	8GB	16GB	32GB	64GB
OCI	$175	$344	$700 (30GB RAM)	$1,119 (60GB RAM)
DigitalOcean	$145	$300	$500	$700
Linode	$145	$300	$500	$700

While MySQL, PostgreSQL and Redis standalone deployments were found to be less expensive through DigitalOcean and Linode vs. Oracle Cloud, ScaleGrid for MongoDB® database standalone actually averages 33.8% more expensive through OCI.

MongoDB® Database Replica Set Deployment Pricing

MongoDB® Database Replica Set	8GB	16GB	32GB	64GB
OCI	$486	$951	$1,527 (30GB RAM)	$2,638 (60GB RAM)
DigitalOcean	$350	$750	$1,500	$2,400
Linode	$350	$750	$1,500	$2,400

When comparing ScaleGrid for MongoDB® database replica set pricing on Oracle Cloud vs. DigitalOcean and Linode, OCI is again more expensive at an average of 19.3% higher pricing for high availability deployments.

Which Cloud Offers The Best Pricing?

OCI vs. AWS, Azure & GCP

When looking at standalone deployments, pricing for Oracle Cloud Infrastructure is 48% cheaper than AWS, Azure or Google Cloud on average for MySQL, PostgreSQL and Redis™ fully managed deployments at ScaleGrid. That is nearly half the cost!

High availability replica set deployments also show significant cost savings, where Oracle Cloud averages 32.4% lower pricing than AWS and Azure, and 40.1% less than Google Cloud for MySQL, PostgreSQL and Redis™ at ScaleGrid.

Oracle Cloud is a great alternative to AWS, Azure or Google Cloud Platform as they offer the same level of infrastructure, services, and partners to support any size business.

OCI vs. DigitalOcean & Linode

When we compare Oracle Cloud vs. DigitalOcean and Linode pricing at ScaleGrid for standalone deployments, OCI averages 25.4% less expensive for MySQL and PostgreSQL and 34.6% less expensive for Redis™. However, Linode and DigitalOcean are 33.8% less expensive than OCI for MongoDB® database at ScaleGrid.

Then, when we look at high availability replica set deployments, OCI averages roughly 20% more expensive than DigitalOcean and Linode for MySQL, PostgreSQL, and MongoDB® database plans at ScaleGrid, and 62% more expensive for Redis™.

While Oracle Cloud may provide some cost savings for standalone deployments vs. DigitalOcean and Linode, these cloud providers are really targeting different markets. Where OCI is focusing on enterprise applications, Linode and DigitalOcean cater more towards the developer market.

The best cloud for your database hosting is, first and foremost, always going to be a decision driven by your application needs. While pricing is a significant factor, it’s also important to consider performance. Be on the lookout for future posts where we’re going to share benchmark reports on OCI performance vs. these top cloud providers.

We hope that this post has been helpful in understanding cloud database pricing options at ScaleGrid! If you have any questions on our fully managed DBaaS plans for MySQL, PostgreSQL, Redis™ or MongoDB® database, get in touch with us and we will happily help you out.

How To Set Up MySQL on DigitalOcean

ScaleGrid — Fri, 05 Feb 2021 22:59:58 +0000

MySQL is the number one open source relational database management system in the world, and is used by millions of developers across all application types. DigitalOcean, a fast-growing cloud provider that’s increasing in popularity amongst the developer community, is a great host to consider for your MySQL deployments. In this article, we’re going to show you how to set up MySQL on DigitalOcean at ScaleGrid for the best possible performance in the cloud.

Building Your DigitalOcean MySQL Deployment

For this tutorial, we are using our fully managed MySQL hosting solution, as it offers the highest throughput and lowest latency on DigitalOcean. Learn more in the Best MySQL DigitalOcean Performance article to compare ScaleGrid vs. DigitalOcean Managed Databases pricing, latency and throughput performance.

The entire process should take less than 20 minutes, with just a couple minutes at the beginning to customize your MySQL deployment configuration, and a couple minutes at the end to connect to your application.

Video Tutorial: https://www.youtube.com/watch?v=ywBy-w0KiN0

After signing up for a free trial account, the first thing you’ll do is select MySQL as your database, DigitalOcean as your cloud, and Dedicated Hosting as your plan to initiate the cluster creation process:

Step 1. Enter MySQL Deployment Details

The cluster creation wizard will open to the first step where you can customize the basic details of your deployment. Enter a name for your cluster using alphanumerics, and use a unique, descriptive name so you can easily recognize your cluster later on.

Next, select the DigitalOcean data center you’d like to use for your master server from the cloud region dropdown - we recommend using the same region as your applications DigitalOcean Droplets for the best performance. Then you can select your VM size of DigitalOcean Droplet instances, and the version of MySQL you wish to use (all major versions supported). InnoDB Storage Engine is the default for all MySQL deployments. Click next.

Step 2. Enable Master-Slave Replication

In step 2, you can enable DigitalOcean MySQL master-slave replication to add high availability to your deployment. Replication is important for keeping your application available and online for your users in the event your DigitalOcean data center region goes down. While DigitalOcean is a highly reliable cloud provider with 99.99% uptime, data centers inevitably go down, so replicating your data to additional data center locations allows you to minimize the risk of data loss and downtime for your users.

If you want to continue with a standalone deployment, click next to move onto step 3 without enabling master-slave configuration.

Note - master-slave configuration is only available for paid accounts, you cannot set up a replica set deployment while you are in trial mode.

There are a couple options here to customize your MySQL master-slave setup. First, you can choose between 2 + 1 Quorum or 3 nodes for your deployment. Both offer high availability, but in the 2 + 1 Quorum setup, you have only 2 data-bearing nodes as the Quorum node serves as a decision-maker in a failover event. You can also contact support to customize your master-slave configuration to add more nodes to your deployment.

Next, you can choose between semisynchronous and asynchronous replication. Semisynchronous replication guarantees at least one slave has received all the data that the master has committed, ensuring that the failover to the right slave is lossless. In asynchronous replication, each slave syncs asynchronously to the master so failover from master to slave may result in failover to a server that is not fully synced up to the current master.

Lastly, customize the DigitalOcean data center locations you wish to use for your slave nodes. Once you’re done, click next.

Step 3. Enable SSL

In step 3, you can enable SSL for your deployment. By default, MySQL server always installs and enables SSL configuration, but it’s not enforced that clients connect using SSL. Clients can choose to connect with or without SSL as the server allows both types of connections. Learn more about configuring and managing SSL in this MySQL tutorial.

Step 4. Configure Firewalls

In the last step of the cluster creation process, enter at least one IP CIDR that will be allowed access to your MySQL deployment to set up your firewall rule. You can find your current IP CIDR at the top of the page. These can be customized later at both the cluster-level and account-level. Once you’ve entered it, click Add. Enter all the IP CIDRs you’d like, and then click next.

Step 5. Review & Create

The last page provides a short summary of your new MySQL DigitalOcean cluster. Review all of the details and click the Previous button to go back to an earlier step and modify your configurations. Once you’re ready, click Create to spin up your cluster.

And that’s it! Your deployment is now being provisioned! This takes about 15 minutes, so while you wait, you can familiarize yourself with the management tools for MySQL in the console - backups, restores, scaling, alerts, query analysis, etc.

Connect & Migrate

Once the cluster status changes from “Creating” to “Running”, you can connect your deployment. There are a couple different ways to do this - through your connection string to your app, through the command line, through WordPress, through MySQL Workbench or other popular GUI tools. All of your connection details are available in the lower half of the Overview tab of your cluster details page.

Visit the top tabs to create a MySQL database and create a MySQL database user. These can be accessed and managed anytime from the ScaleGrid console.

Migrating is also an automated process that requires just a few details from your existing deployment. Click the Import Data button on your Overview tab, and enter your Server Name, Port, Database Name(s), Username and Password. Check out our MySQL Migrations doc for more detail, and additional migration options.

Running on DigitalOcean

Once you’re up and running, your deployment can virtually operate on auto-pilot. Since ScaleGrid is a fully managed service, we handle all of your day-to-day MySQL administration and maintenance so you can focus on building your app. If anything should arise that requires your attention, such as running low on disk space, we’ll send you a notification so you can login and see if you’re ready to scale up your cluster.

You can also connect with our MySQL experts anytime through our 24/7 support to help you set up and troubleshoot your deployment.

Auditing PostgreSQL Using pgAudit

ScaleGrid — Mon, 18 Jan 2021 22:34:47 +0000

Auditing in information technology (IT) is a process of examining an organization’s IT infrastructure to ensure compliance with the requirements imposed by recognized standards or established policies. Data protection rules, such as the new GDPR regulations, are becoming increasingly stringent to protect user data, so it’s important that your database audits are set up properly to ensure both your application and user data is secure from vulnerabilities. In this blog post, we will discuss pgAudit - a tool that generates the audit logs needed to facilitate the auditing of PostgreSQL.

What is pgAudit?

The PostgreSQL Audit Extension, pgAudit, is an open source extension that logs the events in a PostgreSQL database in a detailed audit log. It uses the native PostgreSQL logging facility, so the audit logs will be part of the PostgreSQL logs. The extension is based on the 2ndQuadrant pgAudit project authored by Simon Riggs, Abhijit Menon-Sen, and Ian Barwick, and includes enhancements by David Steele from Crunchy Data.

Why pgAudit over log_statement=all?

We can log all statements in PostgreSQL just by setting log_statement=all. So why use pgAudit at all? The basic statement logging (using log_statement) will only list the operations performed against the database. It will not provide the ability to filter operations, and the logs won’t be in the proper formatting required for auditing. pgAudit additionally provides granularity for logging specific classes of statements like READ (SELECT and COPY), WRITE (INSERT, UPDATE, DELETE, etc.), DDL etc. Furthermore, it provides object level auditing where only operations on specific relations will be logged.

Another advantage of pgAudit over basic statement logging is that it provides the details of the operation performed instead of just logging the operation requested. For example, consider executing the anonymous code block using a DO statement.

 DO $$ BEGIN EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)'; END $$;

The basic statement logging will result in:

 2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: statement: DO $$ BEGIN EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)'; END $$;

pgAudit will log the same operation as:

 2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: AUDIT: SESSION,4,1,FUNCTION,DO,,,"DO $$ BEGIN EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)'; END $$;", 2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: AUDIT: SESSION,4,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT),

The above clearly indicates the pgAudit functionality that logs the operation and its internals with structured output that eases the search.

How to install pgAudit?

pgAudit is an extension that’s available for download from the PostgreSQL repository, or can be compiled and built from source. As a first step, the package needs to be downloaded and installed on the machine running PostgreSQL (this extension package is preinstalled on all ScaleGrid PostgreSQL deployments).

Once installed, it needs to be loaded into PostgreSQL. This is achieved by adding pgaudit to the shared_preload_libraries config parameter. A restart of PostgreSQL is required for this configuration change to be effective. The next step is to enable the extension on the database by running CREATE EXTENSION pgaudit.

Now that the extension is ready, we need to make sure to set the configuration parameters for the extension to start logging. This can be as simple as setting the parameter pgaudit.log to value all and the pgAudit will start logging in session mode.

Now that we know how to install and enable pgAudit, let’s discuss the two audit logging modes it offers, session and object.

Session Audit Logging

In session mode, pgAudit will log all the operations performed by a user. Setting the pgaudit.log parameter to any of the defined values, other than NONE, will enable session audit logging. The pgaudit.log parameter specifies the classes of statements that will be logged in the session mode. The possible values are: READ, WRITE, FUNCTION, ROLE, DDL, MISC, MISC_SET, ALL and NONE.

Setting the pgaudit.log parameter to ALL will log all the statements. The parameter can accept multiple classes using a comma-separated list and specific classes can be excluded with a - sign. For example, if you want to log all statements except MISC class, the value of pgaudit.log will be ALL, -MISC, -MISC_SET. You can also enable pgAudit to create a separate log entry for each relation reference in a statement by setting pgaudit.log_relation to on.

Consider an example of creating a table. The SQL statement would be:

 CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));

The corresponding audit log entries are:

 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,CREATE SEQUENCE,SEQUENCE,public.persons_id_seq,"CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));", 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,CREATE TABLE,TABLE,public.persons,"CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));", 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,CREATE INDEX,INDEX,public.persons_pkey,"CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));", 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,ALTER SEQUENCE,SEQUENCE,public.persons_id_seq,"CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));",

Object Audit Logging

In particular cases, it may be required to audit only a specific set of relations. In such cases, using session mode will only result in an unnecessarily large number of audit logs not corresponding to the required relations. Object mode is especially suited for this purpose and can audit only a specific set of relations.

Object audit logging is achieved using the PostgreSQL roles. A role can be created and assigned the permissions to access only a specific set of relations. This role should be specified in the configuration parameter pgaudit.role. Object mode supports only SELECT, INSERT, UPDATE and DELETE statements. The classes of statements that are logged depends on the permissions granted to the role. For example, if the role has permissions to perform only SELECT, then only SELECT statements will be logged.

Below is an example of object audit logging:

Create a role and grant only SELECT permissions. Set the pgaudit.role to that role and run the SELECT SQL statement:

 CREATE ROLE audit_person; GRANT SELECT ON persons TO audit_person; SET pgaudit.role = 'audit_person'; SELECT * FROM persons WHERE ID=404;

The above select statement will be logged as:

 2020-12-21 00:27:09 UTC:157.230.232.139(54900):sgpostgres@test:[21835]: LOG: AUDIT: OBJECT,10,1,READ,SELECT,TABLE,public.persons,select * from persons where ID=404;,

How to interpret the audit log entry?

So far, we have provided details on how the audit log entry looks, now let’s take a look at the audit log entry format. Each entry starts with the log_line_prefix mentioned for PostgreSQL logging, and then the rest of the output will be in CSV format. Consider the following simple audit log entry:

 2020-12-21 00:27:09 UTC:157.230.232.139(54900):sgpostgres@test:[21835]: LOG: AUDIT: OBJECT,10,1,READ,SELECT,TABLE,public.persons,select * from persons where ID=404;,

In the above entry, the value

2020-12-21 00:27:09 UTC:157.230.232.139(54900):sgpostgres@test:[21835]:

is from the log_line_prefix format %t:%r:%u@%d:[%p]: . The audit entry contents start from LOG: AUDIT: value and it follows CSV format. The value format is of the form:

AUDIT_TYPE,STATEMENT_ID,SUBSTATEMENT_ID,CLASS,COMMAND,OBJECT_TYPE,OBJECT_NAME,STATEMENT,PARAMETER

Let’s take a look at each of the fields one by one:

Field	Description	Value from example audit entry
AUDIT_TYPE	Indicates the audit mode: SESSION or OBJECT	OBJECT
STATEMENT_ID	Unique statement identifier for each session	10
SUBSTATEMENT_ID	An identifier for each sub statement within the main statement	1
CLASS	Indicates the class of statements like READ, WRITE etc that are defined values for pgaudit.log parameter.	READ
COMMAND	The command used in the SQL statement	SELECT
OBJECT_TYPE	Can be TABLE, INDEX, VIEW, etc.	TABLE
OBJECT_NAME	The fully qualified object name	public.persons
STATEMENT	The actual statement executed	select * from persons where ID=404;
PARAMETER	When the pgaudit.log_parameter is set to true, the quoted CSV of parameters is listed if present, or "none" if there are no parameters. When the pgaudit.log_parameter is not set, the value will be “<not logged>”	<not logged>

Inference

pgAudit, with all its capabilities, simplifies the process of auditing by generating the audit trail log. Though there are a few caveats, like logging of renamed objects under the same name, it is still a robust tool that provides the required functionality. However, the audit information written in logs may not be just ideal for the auditing process - the auditing process is even better when those logs can be converted to a database schema, and audit data can be loaded to the database so you can easily query the information. This is where the PostgreSQL Audit Log Analyzer (pgAudit Analyze) is helpful. For more information, refer to the github pages of pgAudit and pgAudit Analyze.

mysqldump Best Practices: Part 1 - MySQL Prerequisites

ScaleGrid — Thu, 17 Dec 2020 17:47:01 +0000

Mysqldump is a client utility that is used to perform logical backups of the MySQL database. This popular migration tool is useful for various use cases of MySQL such as:

Backup and restore of databases.
Migrating data from one server to another.
Migrating data across different managed MySQL service providers.
Migrating data between different versions of MySQL.

Mysqldump works by reading the source database objects and generating a set of SQL statements that are stored in a dump file. By replaying these statements on the destination database server, the original data is reconstructed. Since this model uses reading of the whole database and then essentially rebuilding, both dump and restore are time-consuming operations for a large database. The process might even turn cumbersome if you encounter errors during either dump or restore as it may lead you to fix the issues and re-run the operations. This is why it's important to plan well before you take up the dump and restore activity.

In this 2-part blog series, we discuss some of the common aspects you should handle upfront to ensure a successful dump and restore activity. In the first part, we focus on the prerequisites you need to take care while importing the MySQL table data and in the second part, we will talk about how to handle import for stored program objects and views.

1. Space requirements

First off, it's important to ensure that your destination database volume has sufficient space to hold the imported data. Specifically, you need to be cautious if binary logs are enabled on your destination MySQL database, as binary logs generated while importing the data might take almost equal size as the data itself. Binary logs are needed if you want to restore your data on one server and want that to be replicated. In such cases, it's a good idea to plan the destination size greater than twice the size of the source database.

It is also important to ensure sufficient space is available on the volume where you generate the mysqldump output file. Without these precautions, you may see your dump or restore failing due to insufficient space after running for a long time which is a loss of your productive time and effort.

2. Sql_mode

sql_mode settings for MySQL server determine the SQL statement syntax and data validation checks that the server performs for the operations. It is important to ensure the sql_mode of source and destination MySQL servers are compatible with each other, or you may encounter failures while restoring the dump you have taken. Let's demonstrate this with an example.

Say you have a table on your source which has a date column having entries as zero dates:


mysql> show create table sched;
-------------------------------------------------------------+
| Table | Create Table                                                                                                        |
-------------------------------------------------------------+
| sched | CREATE TABLE `sched` (
  `id` int(11) DEFAULT NULL,
  `ts` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------

mysql> select * from sched;
+------+------------+
| id   | ts         |
+------+------------+
|    1 | 2020-01-12 |
|    2 | 0000-00-00 |
+------+------------+

Suppose the strict sql_mode (and NO_ZERO_DATE) is disabled on source, but enabled on the destination - restoring such rows will result in failure such as:


ERROR 1292 (22007) at line 40: Incorrect date value: '0000-00-00' for column 'ts’' at row 2

You will typically see such issues if you are taking a compact dump by enabling the compact option as part of your mysqldump.

If compact is disabled (which is by default) then you will not face this issue as mysqldump generates following conditional statement as part of the dump:


/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

This means that during the restore sql_mode is set to 'NO_AUTO_VALUE_ON_ZERO' before restoring the table data so restore goes through fine.

3. Unique_checks and foreign_key_checks

By default (if you don't use --compact option), mysqldump also sets the following:


/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

As explained here, you can speed up restore operation by temporarily turning off the uniqueness checks during the session. For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch.

If you have FOREIGN KEY constraints in your tables, you can speed up table restore operation by turning off the foreign key checks for the duration of the restore session: For big tables, this can save a lot of disk I/O.

Disabling FOREIGN_KEY_CHECKS will also help to avoid errors due to foregin key constraint checks during the restore operation. Whenever a table with foregin key constraint is created, MySQL expects that the parent table which is referred to by the foregin key already exists. This is a problem since mysqldump utility dumps the tables in alphabetical order. Let’s take an example to demonstrate this.

On the source database, we have two tables:


CREATE TABLE `solution_table` (
  `num1` int(11) NOT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`num1`));

CREATE TABLE `ref_table` (
  `key` int(11) DEFAULT NULL,
  `ref_num` int(11) DEFAULT NULL,
  KEY `ref_num` (`ref_num`),
  CONSTRAINT `ref_num_ibfk_1` FOREIGN KEY (`ref_num`) REFERENCES `solution_table` (`num1`)
)

The table ref_table has a foreign key constraint that references the solution_table. Based on the alphabetical order, mysqldump first dumps the contents of ref_table. When this is replayed at the time of restore, it will fail with the error:


ERROR 1215 (HY000) at line 50: Cannot add foreign key constraint -

Which happens while executing the create table statement for ‘ref_table’.

In summary, be aware of the issues you may encounter, if you specify --compact option while running mysqldump.

4. Privileges required for running mysqldump

The minimum privilege required by mysqldump for dumping a database is SELECT on that database.

However, if your database has views, you will need SHOW VIEW permissions as well, as mysqldump always dumps views along with the tables of the database. Suppose you don't have SHOW VIEW permissions, then the mysqldump will fail with:

 
mysqldump: Couldn't execute 'show create table `ivew`': SHOW VIEW command denied to user ‘dumpuser’@'172.31.18.79' for table 'iview' (1142)

Another point of interest is if your dumpuser has SELECT permissions only on a particular table of the database, mysqldump will dump data only for that particular table and automatically ignores any other tables or views.

So please ensure that the user executing mysqldump has all the appropriate privileges upfront in order to avoid any surprises or failures at a later time.

5. Max_allowed_packet

The largest communication packet handled by mysql is determined by the setting max_allowed_packet. In the context of import, a communication packet is a single SQL statement sent to the MySQL server during the restore OR a single row that is sent to the client during the dump.

The default value of max_allowed_packet for mysqldump is 24MB. if mysqldump receives a packet larger than this, then you may run into the error:


mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `huge1` at row: 2.

So ensure that mysqldump uses the same or bigger value of max_allowed_packet that is configured on the source MySQL instance.

The option can be specified with the flag --max-allowed-packet=value when invoking the mysqldump.

When restoring the dump, ensure that max_allowed_packet size of your destination server is big enough to receive the packets from the dump file.

Otherwise, during restore of the dump, you will see an error message:


ERROR 2006 (HY000) at line 70: MySQL server has gone away

This error can be a little misleading as you may think that the MySQL server has shut down or crashed. But, it just means that the server has received a bigger sized packet than its configured size of max_allowed_packet. Again, the best practice is to ensure that the max_allowed_packet value for your destination server is the same as the value in the source server. This is also an important setting that can be checked and set appropriately upfront, rather than facing the errors at a later time.

In this first part of the mysqldump series, we discussed prerequisites for a successful dump and restore operation for large MySQL databases in order to help you avoid multiple attempts and unproductive time spent.

In the next part, we will discuss best practices to import the stored programs and views from your MySQL database.

Redis on Azure Performance Benchmark – ScaleGrid for Redis™ vs. Azure Cache

ScaleGrid — Fri, 27 Nov 2020 18:57:18 +0000

Redis is an advanced key-value store. In fact, it is the number one key value store and eighth most popular database in the world. It has high throughput and runs from memory, but also has the ability to persist data on disk. Redis is a great caching solution for highly demanding applications, and there are many solutions available to help you deploy and manage Redis in the cloud. In this post, we are going to compare ScaleGrid for Redis™ vs. Azure Cache for Redis performance and management features to help you pick the best managed solution for your Redis deployment.

ScaleGrid is a DBaaS provider that provides fully managed hosting not only for Redis™, but also for MongoDB® database, MySQL, and PostgreSQL. The Bring Your Own Cloud (BYOC) plan hosts the database server in your own AWS, Azure or GCP account.

Azure provides a hosted service for Redis called Azure Cache for Redis.

ScaleGrid for Redis™ vs. Azure Cache for Redis Performance Benchmark

In this benchmark, we measure the performance in throughput and latency. Throughput is measured in operations per second (ops/sec), and latency in microseconds. Check out our Benchmark Configurations section later on in this post for information on how this performance benchmark was configured.

Throughput Performance

Connections	ScaleGrid for Redis™	Azure Cache C3 Std. 6GB	Azure Cache P1 6GB	Azure Cache P2 13GB	ScaleGrid Improvement
100	134,667	16,461	19,881	38,459	439%
200	147,551	16,246	25,361	35,459	474%
300	152,341	15,872	25,346	35,045	499%
400	152,624	15,235	19,043	37,301	539%

As we can see in the above graph, ScaleGrid for Redis™ achieves approximately 5x higher throughput compared to Azure Cache for Redis when benchmarked with 100-400 connections. For example, while the Azure Cache for Redis P2 Premium 13GB plan manages around 36,000 ops/sec during all connection scenarios, ScaleGrid for Redis™ has over 130 000 ops/sec for all the scenarios.

Latency Performance

Connections	ScaleGrid for Redis™	Azure Cache C3 Std. 6GB	Azure Cache P1 6GB	Azure Cache P2 13GB	ScaleGrid Improvement
100	744	6,809	5,896	2,497	-85%
200	1,353	10,950	8,447	5,565	-84%
300	2,044	17,807	13,045	8,539	-84%
400	2,609	25,126	16,999	10,716	-85%

While Azure Cache for Redis latency increases rapidly as the number of connections grow, ScaleGrid for Redis™ steadily achieves low latency across all connection counts. On average, ScaleGrid for Redis™ has 85% lower latency than Azure Cache for Redis. This is especially noticeable when comparing Azure Cache for Redis (C3 Standard 6GB) to ScaleGrid for Redis™, where the difference is up to -99%.

Benchmark Summary

As you can tell from the above graphs, ScaleGrid for Redis™ provides significantly higher throughput and lower latency. On average we can see approximately 5x the throughput and 85% lower latency compared to equivalent sizes on Azure Cache. The ScaleGrid for Redis™ BYOC plan starts at $9 per month (720h + VM costs) and $18 per month (720h) for the Dedicated hosting plan. To learn more about how different Redis providers compare to ScaleGrid for Redis™, check out our Redis service providers comparison chart where you can learn more about ScaleGrid for Redis™.

Benchmark Configuration

Let’s take a look at the configurations we used in the performance benchmark:

Configuration	Details
Benchmark Tool	Memtier Benchmark
Azure Region for Redis	East US
Azure Region for Application	East US
Deployment Type	Master-Slave

We have benchmarked Redis™ performance for the following configurations.

Provider	Plan Size	RAM	Monthly Cost
ScaleGrid for Redis™	Dedicated Hosting Large on Azure	7GB memory	$607
Azure Cache for Redis	C3 Standard - Moderate network bandwidth	6GB memory	$328.50
Azure Cache for Redis	P1 Premium - Moderate network bandwidth	6GB memory	$404.42
Azure Cache for Redis	P2 Premium - High network bandwidth	13GB memory	$810.30

For each Redis™ server, we have benchmarked with 100, 200, 300 and 400 connections. Each connection sends 10,000 requests with 32 bytes object data size per request. We use non-SSL connections to connect Redis™ servers.

Not only can ScaleGrid provide higher throughput and lower latency, it also brings many other features such as full admin access, scheduled backups and SSH access. You can find more information about ScaleGrid for Redis™ on our website.

What to consider when choosing a Redis™ service?

So, with so many fully managed Redis™ service provider options, what are the most important features to look out for? Here is a checklist to use when choosing the right Redis hosting service for you:

Dedicated Server
Scalability
Data Persistence
Backups & Restores
High Throughput & Low Latency

Dedicated Server

Redis is a single-threaded server where data is stored in memory; therefore, having Redis running on a dedicated server is very important in a production environment. You don’t want your Redis server battling for CPU and memory resources with other services.

Scalability

Businesses grow, and the same for your data. It’s very important that your Redis service is able to perform a dynamic, in-place scale up of your Redis server with little or no downtime.

Data Persistence

Depending on your business needs, you might need to persist your Redis data on physical storage. Redis provides two persistence options: RDB and AOF.

RDB is a point-in-time snapshots of your dataset at specified intervals to a Redis Database Backup file. The file can be transferred to other Redis instances.

AOF stands for Append Only File. Redis logs every write operation which has been modified in your dataset. It is a very reliable way to persist your data.

Both RDB and AOF can be enabled at the same time and they have different trade offs. For more details about their pros and cons, you can read more on Redis Persistence page on redis.io.

Your Redis service should provide options not only to persist the data, but also to deploy Redis in master/replica or cluster mode to minimize the chance of data loss.

Backups & Restores

Any Database as a Service (DBaaS) for Redis should also provide scheduled and on-demand backups so you can ensure you always have a regular schedule of backups available, and can perform them as needed before an application event. It should also provide “restore backup” options to existing databases or to a new database instance.

High Throughput & Low Latency

Redis can provide fast caching for applications. However, sometimes network latency can bottleneck accessing data from Redis. The key is to avoid physical distancing between your application and Redis. So, you want to make sure both the application and Redis are hosted on the same cloud provider region and on the same virtual network. Your Redis service provider should have the option to deploy your Redis server on the virtual network of your choice.