May
08
2023
--

How To Start Logical Replication in PostgreSQL for Specific Tables Based on a pg_dump

logical replication in Postgresql

In simpler terms, logical replication in PostgreSQL is a way to copy data from one database to another in a more flexible and customizable manner compared to physical replication. Instead of copying the entire database, logical replication focuses on replicating changes made to individual rows or transactions. To set up logical replication, you create a publication on the source database and a subscription on the target database. When you enable the subscription, a process called the logical replication launcher identifies it and starts an application worker process. The worker then synchronizes the tables by launching table-sync workers, which copy the changes from the source to the target.

This blog post will examine an alternative method for conducting the initial data synchronization in PostgreSQL. Instead of utilizing the table synchronization process by accelerating the synchronization process through a data dump, we will explore the usage of pg_dump and pg_restore.

Lab environment – The lab environment contains below servers:

  • One primary and two replicas running on Postgres version 12
  • One server running on PostgreSQL version 15
  Server Details   IP Address   PostgreSQL Version
  Primary   10.0.0.25   12
  Replica1   10.0.0.80   12
  Replica2   10.0.0.40   12
  Logical replica (Target Server)   10.0.0.221   15

In this lab, I’ve set up two replicas to make this approach work. I needed to pause the WAL replay process, so having two replicas, including replica2, helps ensure that ongoing replication, which may be important for meeting business requirements, isn’t affected by this approach.

Step 1: Create a publication on the primary server
The first step is to create a publication on the primary server, which defines the tables that will be replicated. Connect to the primary server using the PostgreSQL command line tool psql, and execute the following commands

percona=# create publication my_pub;
CREATE PUBLICATION
percona=# alter publication my_pub add table table1;
ALTER PUBLICATION

These commands create a publication named my_pub and add the table table1 to it. The publication is now ready to replicate changes made to table1 on the primary server.

Step 2: Create a logical replication slot on the primary server
Next, we need to create a logical replication slot on the primary server to capture the changes made to the table1. Execute the following command in the psql console on the primary server:

percona=# SELECT pg_create_logical_replication_slot('my_pub_slot', 'pgoutput');
pg_create_logical_replication_slot
------------------------------------
(my_pub_slot,0/1C8BEB58)

This command creates a logical replication slot named my_pub_slot using the pgoutput output plugin, which is a built-in output plugin for logical replication in PostgreSQL. The slot will capture the changes made to the table1 and store them for replication.

Step 3: Create a subscription on the target server
On the target logical replication server, we need to create a subscription that connects to the primary server and replicates the changes made to table1. Connect to the target server using psql, and execute the following commands. Note that we have omitted the password from the command, as it is located in the .pgpass file

percona=# CREATE SUBSCRIPTION my_pub_subscription CONNECTION 'host=10.0.0.25 port=5432 dbname=percona' PUBLICATION my_pub WITH (copy_data = false, create_slot=false, enabled=false, slot_name=my_pub_slot);
CREATE SUBSCRIPTION

This command creates a subscription named my_pub_subscription that connects to the primary server at IP address 10.0.0.25, using the Percona database, and replicates the publication my_pub, which includes table1. The subscription is created with the following options:

copy_data=false: This option specifies that we will not copy the data from the publisher using logical replication, but instead, we will use the pg_dump tool to copy the data later.
create_slot=false: This option specifies that we will not create a replication slot on the target server, as we have already created a logical replication slot on the primary server.
enabled=false: This option specifies that the subscription will not be enabled immediately, as we want to perform some additional steps before enabling it.
slot_name=my_pub_slot: This option specifies the name of the logical replication slot that we created on the primary server.

Step 4: Pause replication on replica2::10.0.0.40 to pg_dump a data snapshot for table1 and restore it on the target logical replication node

On replica2:

postgres@ip-10-0-0-40:~$ psql -d percona -c 'select pg_wal_replay_pause() '
pg_wal_replay_pause
---------------------
(1 row)
postgres@ip-10-0-0-40:~$ psql -d percona -c 'select pg_is_wal_replay_paused() '
pg_is_wal_replay_paused
-------------------------
t
(1 row)

NOTE: For additional details on how to use and apply pg_dump, I suggest taking a look at my colleague’s blog posts, which covers several topics, including simple backup and restore, PostgreSQL upgrade using pg_dump and pg_restore, PostgreSQL upgrade using pg_dumpall, and working with PostgreSQL dump manifests.

Now replication is paused on replica2, and transactions on the primary node won’t be replicated to replica2. To confirm whether the replay is paused on replica2, we insert a new record into table1 on the primary server, increasing the total count to 1,000,001. We then check the record count on replica1 and replica2. Replica1 shows the expected count of 1,000,001, while replica2 has a replay paused status of “t” and a record count of 1,000,000, indicating that the newly inserted record has not been replicated to it due to the pause in replication.

On primary server:  Count and insert records in table1.

postgres@ip-10-0-0-25:~$ psql -d percona -tc 'select count(*) from table1'
1000000
percona=# insert into table1(name,age) values('KB','29');
INSERT 0 1
percona=# select count(*) from table1;
count
---------
1000001
(1 row)

We can verify whether the newly inserted record has been replicated to replica1 and replica2. Since replication is paused on replica2, it will not have the record we inserted.

Replica1:

postgres@ip-10-0-0-80:~$ psql -d percona -U postgres -tc 'select count(*) from table1;'
1000001

Replica2: 

postgres@ip-10-0-0-40:~$ psql -p 5434 -d percona -U postgres -tc 'select pg_is_wal_replay_paused();'
t
postgres@ip-10-0-0-40:~$ psql -p 5434 -d percona -U postgres -tc 'select count(*) from table1;'
1000000

 

Step 5: Note the replay LSN for replica2 (e.g., 0/1C8BEC40) on the primary

postgres@ip-10-0-0-25:~$ psql -d percona -c 'select client_addr, application_name, replay_lsn from pg_stat_replication where application_name = 'replica2';'
client_addr | application_name | replay_lsn
-------------+------------------+------------
10.0.0.40   | replica2          | 0/1C8BEC40
(2 rows)

 

Step 6: Dump only the data for table1 from replica2 as we already have the schema on the target node

postgres@ip-10-0-0-40:~$ pg_dump -d percona --data-only --table=table1 -Fc > table1_dmp.db

 

Step 7: Restore the data on the target node

pg_restore -h 10.0.0.221 -U postgres -d percona table1_dmp.db

NOTE: The custom compression format employed by pg_dump has several advantages, such as reducing the size of dump files and speeding up backup and restore processes. It also enables restoring multiple tables simultaneously by utilizing the jobs switch. However, when restoring data from a dump file, it’s essential to include constraints, if they exist, on the target tables after restoring the data. This can be done by using the section=post-data switch.

Step 8: Resume the logical replication worker process to capture continuous data changes for table1

On the primary node, there may be more data in table1 compared to replica2 and the target logical replication node. Once we enable logical replication, the delta data (i.e., the changes that occur after the logical replication is set up) will be replicated by the logical replication worker to the target server. This ensures that any new changes made to table1 on the primary node will be accurately replicated to the target server through logical replication.

postgres@ip-10-0-0-25:~$ psql -d percona -c 'select count(*) from table1'
count
---------
1000100
(1 row)

 

Step 9: On the target server, advance the replication origin on the target to the location from replay_lsn (gathered from replica2) in Step5 for the logical replica target

percona=# select roident, subname, roname from pg_subscription sub, pg_replication_origin ro where 'pg_' || sub.oid = ro.roname;
roident | subname              | roname
---------+----------------------+----------
1        | my_pub_subscription | pg_16407
(1 row)
percona=# select pg_replication_origin_advance('pg_16407', '0/1C8BEC40');
pg_replication_origin_advance
-------------------------------
(1 row)

pg_replication_origin_advance is used for replication progress for the given node to the given location. This is primarily useful for setting up the initial location or setting a new location after configuration changes and similar.

Step 10: Enable the subscription

percona=# ALTER SUBSCRIPTION my_pub_subscription ENABLE;
ALTER SUBSCRIPTION

After successfully enabling the subscription, we can observe that the changes in table1 are being logically replicated to the target server.

postgres@ip-10-0-0-221:~$ psql -d percona

psql (15.2 (Ubuntu 15.2-1.pgdg22.04+1))

Type "help" for help.

percona=# SELECT COUNT(*) FROM table1;
-[ RECORD 1 ]--
count | 1000100

This confirms that the data in table1 is being replicated from the source to the target server through logical replication. Going forward, any changes made to table1 on the source server will be replicated to the target server as well. It’s important to note that resuming replication for replica2 to ensure that the changes are properly applied. This can be done using the pg_wal_replay_resume() function in PostgreSQL:

postgres=# SELECT pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)

When considering the implementation of this method for sizable tables, it is essential to keep a close eye on the replication slot age to avert any potential wraparound complications. Furthermore, refrain from utilizing the count(*) function on extensive tables, as it can consume a lot of resources and take up a significant amount of time.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

May
08
2023
--

Percona Monitoring and Management 2.37, Percona Distribution for MongoDB 5.0.17: Release Roundup May 8, 2023

Percona Releases

It’s time for the release roundup!

Percona is a leading provider of unbiased, performance-first, open source database solutions that allow organizations to easily, securely, and affordably maintain business agility, minimize risks, and stay competitive, free from vendor lock-in.

Our Release Roundups showcase the latest Percona software updates, tools, and features to help you manage and deploy our software. It offers highlights, critical information, links to the full release notes, and direct links to the software or service itself to download.

Today’s post includes those releases and updates that have come out since April 24, 2023. Take a look!

Percona Monitoring and Management 2.37

On May 2, 2023, Percona Monitoring and Management 2.37 (PMM) was released. It is an open source database monitoring, management, and observability solution for MySQL, PostgreSQL, and MongoDB. This release of PMM starts the series of enhancements that will improve how you work with Services and Nodes in PMM. With this first set of changes, we’re revamping the Inventory page to give you more context on your inventory objects and more actionable information for exploring and fixing possible issues. Also, this release introduces three new experimental dashboards for Kubernetes monitoring, along with other new features and improvements.

For more in-depth information, check out Roma Novikov’s blog covering all things Percona Monitoring and Management 2.37.

Download Percona Monitoring and Management 2.37

Percona Distribution for MongoDB 5.0.17

Percona Distribution for MongoDB 5.0.17 was released on May 4, 2023. A freely available MongoDB database alternative, it gives you a single solution that combines enterprise components from the open source community, designed and tested to work together. Release highlights include bug fixes and improvements provided by MongoDB and included in Percona Server for MongoDB 5.0.17-14, as well as improvements in Percona Backup for MongoDB 2.1.0, such as the general availability of incremental physical backups, selective backup and restore of sharded collections, the support of parallel download of data chunks from the S3 storage, and improved deletion of old backups and point-in-time recovery oplog chunks.

Download Percona Distribution for MongoDB 5.0.17

Percona Server for MongoDB 5.0.17-14

On May 4, 2023, Percona Server for MongoDB 5.0.17-14 was released. It is an enhanced, source-available, and highly-scalable database that is a fully-compatible drop-in replacement for MongoDB 5.0.16 Community Edition and MongoDB 5.0.17 Community Edition. It supports protocols and drivers of both MongoDB 5.0.16 and 5.0.17 and includes the improvements and bug fixes of MongoDB Community Edition 5.0.16 and 5.0.17.

Download Percona Server for MongoDB 5.0.17-14

Percona Operator for PostgreSQL 2.1.0 (Tech preview)

Percona Operator for PostgreSQL 2.1.0 (Tech preview) was released on May 4, 2023. It helps create and manage highly available, enterprise-ready PostgreSQL clusters on Kubernetes. It is 100% open source, free from vendor lock-in, usage restrictions, and expensive contracts, and includes enterprise-ready features: backup/restore, high availability, replication, logging, and more.

Release Highlights include:

  • PostgreSQL 15 is now officially supported by the Operator with the new exciting features it brings to developers.
  • UX improvements related to Custom Resource have been added in this release, including the handy pgpg-backup, and pg-restore short names useful to quickly query the cluster state with the kubectl get command and additional information in the status fields, which now show nameendpointstatus, and age.

Download Percona Operator for PostgreSQL 2.1.0

That’s it for this roundup, and be sure to follow us on Twitter to stay up-to-date on the most recent releases! Percona is a leader in providing best-of-breed enterprise-class support, consulting, managed services, training, and software for MySQL, MongoDB, PostgreSQL, MariaDB, and other open source databases in on-premises and cloud environments, and is trusted by global brands to unify, monitor, manage,
secure, and optimize their database environments.

May
08
2023
--

Percona Monitoring and Management 2.37: The Improved Inventory View, New Experimental Dashboards for Kubernetes Monitoring, and More!

Percona Monitoring and Management 2.37

We are excited to announce the release of Percona Monitoring and Management (PMM) V2.37, which includes a revamped Inventory view, new experimental dashboards for Kubernetes monitoring, as well as many other improvements and new features. See the full list of changes in the release notes.

To get started with PMM 2.37, check out the PMM Quickstart guide. Please consult the upgrade instructions if you’re upgrading from a previous version.

The improved Inventory view

This release of PMM starts the series of enhancements that will improve how you work with Services and Nodes in PMM. With this first set of changes, we’re revamping the Inventory page to give you more context on your inventory objects and more actionable information for exploring and fixing possible issues.

Let’s review all of these changes separately. 

Improved design

The new Inventory page now provides a much clearer design and emphasizes useful information while downplaying reference details like service labels and IDs.

PMM inventory
We also solved one of the most frequent questions from users: “On what Node is this service running?”

To answer this question, you would have had to search by ID in a few places and be familiar with PMM’s internal architecture. Now, we are showing this information in a dedicated Node Name column on this page.

Database and monitoring status

At the heart of our redesign thinking process laid one important question: “How can our users assess the health of their database by just looking at their list of Services?

To answer this, we’ve added some monitoring capabilities into the mix. The new Status column on the Inventory page shows the status of your databases based on metrics coming directly from them.

We’ve also tackled a trust question: How can you make sure that the Service status we’re exposing is accurate?

This is why we’ve added the Monitoring column. This summarizes the status of all the Agents related to a Service and also offers quick access to the list of Agents.

Database and monitoring status

Quick access to other sections

To simplify access to related sections under Monitoring, we’ve also added links to the Service Overview Dashboard and Query Analytics. You will find them in the Action section. 

Detailed section

We’ve also moved all secondary information, like parameters and labels, to an expandable section available for each Service and Node. 

What’s next for the Inventory?

We will continue to improve the presentation of PMM’s Inventory view with Node-monitoring status and Node Agents lists. We also plan to add more connection points from the Inventory to other important PMM features like Advisors, Alerting, and backups.

In addition, the new Inventory view will be even more helpful with the upcoming structure based on database clusters.

Stay tuned! 

New Experimental dashboards for Kubernetes monitoring

Important

These experimental dashboards are subject to change. It is recommended to use these dashboards for testing purposes only.

We are pleased to announce the release of PMM V2.37, which introduces three new experimental dashboards:

  • Kubernetes Cluster Summary
  • Kubernetes Pods Status
  • Kubernetes Volumes

These dashboards are designed to provide valuable insights into the status and performance of your Kubernetes cluster, pods, and volumes, helping you to identify and troubleshoot issues quickly and easily.

We welcome your feedback as we continue to enhance PMM with these new dashboards. Please leave your comments/feedback on Percona Forum.

Experimental Kubernetes Cluster Summary dashboard

Experimental Kubernetes Cluster Summary dashboard

Kubernetes Cluster Summary provides a comprehensive overview of your Kubernetes cluster, including:

  • Components
  • Node
  • Pod
  • PVC status
  • CPU
  • Memory overview, and more.

This dashboard displays all workloads running in the cluster, enabling you to take action and optimize its performance.

Experimental Kubernetes Pods Status dashboard

Experimental Kubernetes Pods Status dashboard

Kubernetes Pods Status dashboard provides detailed information about the state and performance of your pods, including CPU, Memory, and Network metrics.

This dashboard can help you quickly pinpoint any issues affecting your pods and ensure they continue to operate smoothly.

Experimental Kubernetes Volume dashboard

Experimental Kubernetes Volume dashboard

The Kubernetes Volumes dashboard provides insights into your Kubernetes volumes, including capacity and usage, in real time. With this dashboard, you can easily monitor the performance and usage of your volumes and take proactive measures to ensure their performance.

To learn more about these new experimental dashboards and how to use them, refer to the documentation.

Here are the steps to create a new folder and move all experimental dashboards to the new folder for quick access and internal use:

Note

You should have at least an Editor role to create a new folder and move all experimental dashboards.

  1. Navigate to the Main menu and hover on the Dashboards icon.
  2. Click New folder.
  3. Provide a name for your folder, and then select Create.
  4. Navigate to Dashboards from the Main menu and click Browse.
  5. Select the dashboard that you want to move and click Move.
  6. On the Choose Dashboard dialogue box, from the dropdown under Folder option, choose the folder where you want to move the dashboard.
  7. To apply your changes, select Save Dashboard.

What is next?

Experimental dashboards will be enhanced through UX improvements and the introduction of new metrics.

Thanks to Community and Perconians

At Percona, we are grateful for our supportive community and dedicated team, who work together to shape the future of PMM. If you would like to be a part of this community, you can join us on our forums to request new features, share your feedback, and ask for support. We value the input of our community and welcome all members to participate in the ongoing development of PMM.

 

See PMM in action now!

May
02
2023
--

How To Use pt-secure-collect for Capturing Data in a Secure Way From the OS and Database System

How To Use pt-secure-collect

Sometimes crucial data sharing is avoided because of compliance rules, organizational policies, or numerous security concerns. The common use cases involve sharing pt-mysql-summary, pt-stalk, and other OS-related details to assist Support Engineers or any other third-party team troubleshoot database-related issues.

In this context, pt-secure-collect is a very important utility from Percona, which helps capture the required information securely and also provides aid in masking the existing information.

Pt-secure-collect helps in collecting, sanitizing, and encrypting data from various sources. By default, this utility collects the output with the help of pt-stalk, pt-summary, and pt-mysql-summary.

Let’s see how this tool works.

Installation

The tool can be installed via the Percona official repositories:

sudo yum install percona-toolkit

Another option for downloading pt-secure-collect is either via the Percona Toolkit or directly installing the specific tool.

shell> sudo wget https://downloads.percona.com/downloads/percona-toolkit/3.5.2/binary/redhat/7/x86_64/percona-toolkit-3.5.2-2.el7.x86_64.rpm 
shell> sudo yum install percona-toolkit-3.5.2-2.el7.x86_64.rpm

OR

shell> sudo wget percona.com/get/pt-secure-collect 
shell> sudo chmod +x pt-secure-collect

 Now, let’s run our first command to capture the OS/Database-related details from the tool.

shell> ./pt-secure-collect collect --bin-dir=/usr/bin/ --temp-dir=/home/vagrant/pt/ --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=Root@1234
Encryption password

Output:

INFO[2023-04-22 06:54:10] Temp directory is "/home/vagrant/pt/"
INFO[2023-04-22 06:54:10] Creating output file "/home/vagrant/pt/pt-stalk_2023-04-22_06_54_10.out"  
INFO[2023-04-22 06:54:10] Running pt-stalk --no-stalk --iterations=2 --sleep=30 --host=localhost --dest=/home/vagrant/pt/ --port=3306 --user=root --password=********  
INFO[2023-04-22 06:55:42] Creating output file "/home/vagrant/pt/pt-summary_2023-04-22_06_55_42.out"  
INFO[2023-04-22 06:55:42] Running pt-summary                            
INFO[2023-04-22 06:55:48] Creating output file "/home/vagrant/pt/pt-mysql-summary_2023-04-22_06_55_48.out"  
INFO[2023-04-22 06:55:48] Running pt-mysql-summary --host=localhost --port=3306 --user=root --password=********  
INFO[2023-04-22 06:56:01] Sanitizing output collected data              
INFO[2023-04-22 06:56:17] Creating tar file "/home/vagrant/pt/pt.tar.gz"  
INFO[2023-04-22 06:56:17] Encrypting "/home/vagrant/pt/pt.tar.gz" file into "/home/vagrant/pt/pt.tar.gz.aes"  
INFO[2023-04-22 06:56:17] Skipping encrypted file "pt.tar.gz.aes"   

So, here the above command collected the data from the “pt*” tools securely. By default, it encrypts the data and asks for the encryption password as well. However, we can skip that part by mentioning this option “ –no-encrypt”  option. 

Options:-

--bin-dir => Directory having the Percona Toolkit binaries (pt* tools). 
--temp-dir => Temporary directory used for the data collection.

Note – In order to run the command successfully all prerequisites binaries of (pt-stalk, pt-summary, and pt-mysql-summary) must be present and included in the command.

Let’s decrypt the file and observe the captured details:

shell> ./pt-secure-collect decrypt /home/vagrant/pt/pt.tar.gz.aes  --outfile=/home/vagrant/pt/pt.tar.gz
Encryption password:
INFO[2023-04-22 07:01:55] Decrypting file "/home/vagrant/pt/pt.tar.gz.aes" into "/home/vagrant/pt/pt.tar.gz" 

Note – Here, we need to provide the password which we used at the time of encryption.

--outfile => Write the output to this file. If omitted, the output file name will be the same as the input file, adding the .aes extension.

Now, inside the path, we can see the unencrypted file. Followed by this, we can uncompress the file to see the contents.

shell> /home/vagrant/pt 
-rw-------. 1 vagrant vagrant 500K Apr 22 07:01 pt.tar.gz

shell> tar -xzvf pt.tar.gz

Let’s look at a couple of examples where the sensitive data has been altered or masked.

  • With pt-secure-collect:
Hostname | hostname 
log_error | /var/log/hostname 
Config File | /etc/hostname 
pid-file        = /var/run/mysqld/hostname 
log-error     = /var/log/hostname 
socket        = /var/lib/mysql/hostname

  • Without pt-secure-collect:
Hostname | localhost.localdomain 
log_error | /var/log/mysqld.log 
Config File | /etc/my.cnf 
pid-file       = /var/run/mysqld/mysqld.pid 
log-error     = /var/log/mysqld.log 
socket        = /var/lib/mysql/mysql.sock

Note – We can clearly see some differences in the both types of outputs. With pt-secure-collection the above information was just replaced with some random value(“hostname”).

Now, let’s see how we can sanitize an existing file “pt-mysql-summary.out” and mask the critical information that ends with the below output section.

shell> ./pt-secure-collect sanitize --input-file=/home/vagrant/pt-mysql-summary.out > /home/vagrant/pt-mysql-summary_sanitize.out

Output:

Hostname | hostname 
Pidfile | /var/run/mysqld/hostname (exists) 
log_error | /var/log/hostname 
Config File | /etc/hostname 
pid-file        = /var/run/mysqld/hostname 
log-error     = /var/log/hostname 
socket        = /var/lib/mysql/hostname 
log-error     = /var/log/mariadb/hostname
pid-file        = /var/run/mariadb/hostname

You may also control the information which you want to skip from masking with settings with option –no-sanitize-hostnames and  –no-sanitize-queries.

Here, we see one more example where the critical information, such as “hostname” details inside the OS log file (“/var/log/messages”), is masked/replaced by some other value.

shell> sudo ./pt-secure-collect sanitize --input-file=/var/log/messages > /home/vagrant/messages_sanitize.out

 

Output (without pt-secure-collect):

Apr 23 03:37:13 localhost pmm-agent: #033[31mERRO#033[0m[2023-04-23T03:37:13.547+00:00] time="2023-04-23T03:37:13Z" level=error msg="Error opening connection to ProxySQL: dial tcp 127.0.0.1:6032: connect: connection refused" source="exporter.go:169"  #033[31magentID#033[0m=/agent_id/04dd6ad8-5c2e-4c52-a624-eb3bc7357651 #033[31mcomponent#033[0m=agent-process #033[31mtype#033[0m=proxysql_exporter

Output (with pt-secure-collect):

Apr 23 03:37:13 localhost pmm-agent: #033[31mERRO#033[0m[2023-04-23T03:37:13.547+00:00] time="2023-04-23T03:37:13Z" level=error msg="Error opening connection to ProxySQL: dial tcp hostname:6032: connect: connection refused" source="hostname:169"  #033[31magentID#033[0m=/agent_id/04dd6ad8-5c2e-4c52-a624-eb3bc7357651 #033[31mcomponent#033[0m=agent-process #033[31mtype#033[0m=proxysql_exporte

 

Summary

With the help of this tool, both OS and database-level information/logs can be encrypted or masked with some different values to hide the sensitive data. This tool comes in handy while dealing with critical data troubleshooting with any third-party stakeholders and also maintains security/compliance-related practices.

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually.

 

Learn more about Percona Toolkit

May
02
2023
--

ChatGPT vs. MySQL DBA Challenge

ChatGPT vs MySQL

Given the increasing popularity of ChatGPT in solving questions of humankind, I decided to take a better look at the current state of AI. At the same time that I see database engineers relying on the tool, sites such as StackOverflow are banning ChatGPT. As the question comes from time to time, is my profession at risk of being extinct due to AI? As my mentor said when I started working in the tech area, the answer is: it depends. I say this because, at this moment in time, my answer is a big No. As we will see right after, the answers are quite generic, but they are at least in the right direction. Nothing, however, prohibits the following AI models from being smarter and providing precise answers.

Let’s look at some questions I did that a MySQL DBA usually needs to answer in an interview process.

Questions

Q: I have a MySQL server with 500 GB of RAM; my data set is 100 GB. How large my InnoDB buffer pool needs to be?

ChatGPT: The InnoDB buffer pool is used by MySQL to cache frequently accessed data in memory. It is recommended to set the buffer pool size to a value that is large enough to hold your entire dataset in memory. Since your dataset is 100 GB and you have 500 GB of RAM, you can allocate a significant portion of the available memory to the InnoDB buffer pool. A commonly recommended value for the buffer pool size is between 50% to 75% of the available memory. In your case, a buffer pool size of around 375 GB (75% of 500 GB) should be a good starting point. However, the actual value that you should use will depend on the specific characteristics of your workload. Keep in mind that setting the buffer pool size too high may result in other processes on your server competing for memory, which can impact performance. It is also important to monitor your server’s memory usage regularly to ensure that it is not being exhausted by the buffer pool.

So this answer was inaccurate and evasive. Since my data set is 100GB, I would like to see ChatGPT explicitly mention that a good starting point would be 100GB. If we expand the cache concept more, the buffer pool could be even less if the working set  (hot data) is smaller.

Q: I have a MySQL server, and my application is writing at a rate of 100 MB/hour in my redo logs. How large does my innodb_redo_log_capacity need to be?

ChatGPT: The innodb_redo_log_capacity parameter specifies the maximum size of the InnoDB redo log buffer, which is used to store changes made to the database before they are written to disk. The size of the redo log buffer should be large enough to handle the maximum amount of changes that can occur within the time it takes to write the changes to disk. To calculate the required size of your redo log buffer, you can use the following formula:

innodb_redo_log_capacity = (max_rate_of_change * max_redo_log_write_time) / redo_log_block_size

In your case, you have provided the maximum rate of change, which is 100 MB/hour. You will need to estimate the maximum time it takes to write the changes to disk and the redo log block size. Assuming a redo log block size of 512 bytes (which is the default value for innodb_log_block_size), and a maximum time of 10 minutes (0.16667 hours) to write the changes to disk, the calculation would be as follows: […]

I didn’t paste the full answer on purpose because it was very wrong. The innodb_redo_log_capacity is a setting from the MySQL server, while the innodb_log_block_size is a setting from MariaDB. Also, the redo log capacity is not the same as the redo log buffer, so it mixes different technologies and concepts. If you want to know more about how to calculate a good redo log size, check out this blog post:

MySQL 8.0 Dynamic Redo Log Sizing

Q: I have the following table in my MySQL server:

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Q: How do I optimize the following query:

SELECT * FROM joinit WHERE g = 10;

ChatGPT: To optimize the query SELECT * FROM joinit WHERE g = 10;, you can follow these steps:

Add an index to the g column: Since the query is filtering by the g column, adding an index on that column will improve the query’s performance. You can add the index using the following command:

ALTER TABLE joinit ADD INDEX (g);

[…]

The answer had a few more comments, but the fact that ChatGPT gave the first answer the “ADD INDEX” command is impressive. 

Q: I have a MySQL server with 10 CPUs. What is the ideal number for my innodb_thread_concurrency?

ChatGPT: The ideal value for the innodb_thread_concurrency setting in MySQL depends on several factors, including the workload on the server, the number of CPU cores, and the amount of available memory. In general, the recommended value for innodb_thread_concurrency on a server with 10 CPUs is typically between 16 and 32. However, it’s important to note that the optimal value may vary depending on your specific workload and hardware configuration.  To determine the ideal value for your server, you can start by setting the innodb_thread_concurrency value to a moderate number (e.g., 16) and monitoring the server’s performance. You can then gradually increase or decrease the value to optimize the server’s performance based on your workload.[…]

The answer here follows a general rule of thumb. If you are interested in more details about this setting, you can check the following blog post:

InnoDB Thread Concurrency

Also, during the review of this blog post, one of my colleagues was not convinced by the suggestion (which is great!). He said:

Indeed but 16 to 32 in a 10 core server may lead to context switch contention so the number IMO is not right, a good starting point is setting a number smaller than the amount of CPUs. So the actual answer is highly dependent on the workload (i.e. concurrent threads running, duration of queries, etc)

As we can see, the decision is not only based on a formula or documentation. Much of the knowledge relies on the experience and lessons learned during the problems faced. Also, the debate with other engineers allows you to gather other points of view, which improves the solution to a point that is nowhere near ChatGPT can reach.

Let’s make it harder now.

Q: How can I refresh statistics for my spatial indexes in MySQL?

ChatGPT: In MySQL, you can refresh the statistics for your spatial indexes by using the ANALYZE TABLE statement. […]

This answer is wrong. Only when the table is rebuilt the statistics for spatial indexes are refreshed. Also, there is a documented bug:

OPTIMIZE TABLE does not sort R-tree indexes, such as spatial indexes on POINT columns. (Bug #23578)

Q: I have installed MySQL 8 in Ubuntu 22.04 and moved my datadir to another location. I adjusted my datadir in my.cnf file, but my database is not starting. What could it be?

ChatGPT: If you have moved the MySQL data directory to another location and updated the datadir parameter in the my.cnf configuration file, but MySQL is still not starting, there might be other configuration parameters that need to be updated.

[…]

The answer here is generic. However, if I modify my question to make it more accurate:

Q: I have installed MySQL 8 in Ubuntu 22.04 and moved my datadir to another location. I adjusted my datadir in my.cnf file, but my database is not starting. The datadir is correct. What could it be?

Then I get a more plausible answer:

ChatGPT: If the data directory is correct and there are no errors in the MySQL error log file, it’s possible that the file permissions are not set correctly for the new data directory.[…]

Q: I have the following iostat sample:

2020_10_06_14_22_25-iostat:Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
2020_10_06_14_21_55-iostat:sda               2.32     0.84  386.08 1004.05  5570.21  7514.67    18.83     4.45    12.05    0.79    1.14   0.49  8.16
2020_10_06_14_21_55-iostat:sda               0.00     0.00  113.00 1535.00  1808.00 13727.50    18.85     1.61    10.98    0.93    0.98   0.42  8.40
2020_10_06_14_21_55-iostat:sda               0.00     1.00   79.00 1494.00  1264.00  7203.00    10.77     2.04    20.66    0.65    0.67   0.45  1.30
2020_10_06_14_21_55-iostat:sda               0.00     0.00  132.00 1500.00  2112.00 14508.50    20.37     5.87    30.15    0.68    1.19   0.47  6.10

Q: Am I having I/O issues?

ChatGPT: To determine if you are having I/O issues, you need to analyze the %util column. This column shows the percentage of time that the disk was busy during the sampling period. A high %util value indicates that the disk is busy for a significant portion of the time, which could indicate an I/O bottleneck. In your sample, the %util values range from 1.30 to 8.40, which are relatively low values and may not necessarily indicate I/O issues.

The answer could be better. The answer does not consider the queue or latency of the sample, which could indicate a disk with issues. Basically, ChatGPT is taking into consideration only the %util column to conclude the answer.

Q: I have a server with 2 NUMA cores. Which setting can I use in my Percona Server to have better memory usage?

ChatGPT: If you have a server with 2 NUMA cores, you can use the numa-interleave option in Percona Server to improve memory usage.

[…]

This answer applies only to older versions of the Percona Server. Nowadays, you can use the innodb_numa_interleave. I found it relevant to add it here because, as we can see, the data set used to feed the AI needs to be always refreshed to provide relevant answers.

Conclusion

The answers provided by ChatGPT are accurate, but in general, there is too much generic information that could not help to work on specific issues. Also, the data set used by ChatGPT is very important because we might receive outdated or misleading information. We cannot take the answer blindly, and the fact we need to check the answer requires previous knowledge in the area. 

Still, ChatGPT can provide a good starting point, especially for those starting with MySQL and Linux.

Many may say that I need to provide precise questions to obtain my desired answer. That is correct. However, working with our customers, most of the time during an emergency, the only information they have is that the application is slow or not connecting to the database. So, in the real world, asking a precise question is difficult if you are not a database expert. Being able to know the right question is half of the path to finding the right answer. And to finish with this post, I did one last question to ChatGPT:

Q: Is ChatGPT going to replace a MySQL DBA job?

Answer: No, ChatGPT is an AI language model developed by OpenAI and is not designed to replace a MySQL DBA job. While ChatGPT can provide some level of assistance and guidance for MySQL-related questions, it cannot replace the knowledge, expertise, and decision-making abilities of a human MySQL DBA. A MySQL DBA is responsible for maintaining, securing, and optimizing MySQL database systems, which involves a range of complex tasks and decision-making processes that require human expertise and experience.

So, as for now, we are safe 😀 !

Jokes and fears aside, I don’t believe technology is designed to replace but rather to help us to be more effective and to dedicate our time to tasks that really bring value to our customers. I’m positive and excited to see what is coming for the next years! And if ChatGPT does not give you a proper answer, feel free to contact us on social media or our forum

Useful resources

Finally, you can reach us through social networks, our forum, or access our material using the links presented below:

May
01
2023
--

Talking Drupal #397 – Semantic Versioning

Today we are talking about Semantic Versioning with Mike Miles.

For show notes visit: www.talkingDrupal.com/397

Topics

  • What is Semantic Versioning
  • Why is it important
  • How does Drupal 8 map to Semantic Versioning
    • 8.x
  • What about betas, alphas, rcs
  • How does it help dev teams stay organized
  • When did you start thinking about Semantic Versioning
  • Talk at NERD Summit
  • Benefits of Semantic Versioning
  • Other than the basics, how does your team use Semantic Versioning
  • How do you move existing projects over to Semantic Versioning
  • If someone wants to start using Semantic Versioning where should they look

Resources

Guests

Mike Miles – mike-miles.com @mikemiles86

Hosts

Nic Laflin – www.nLighteneddevelopment.com @nicxvan John Picozzi – www.epam.com @johnpicozzi Jordan Graham – @jordanlgraham

MOTW Correspondent

Martin Anderson-Clutz – @mandclu Same Page Preview Shows your content authors what their content will look like, while they’re creating it.

May
01
2023
--

Save Money in AWS RDS: Don’t Trust the Defaults

aws rds

Default settings can help you get started quickly – but they can also cost you performance and a higher cloud bill at the end of the month. Want to save money on your AWS RDS bill? I’ll show you some MySQL settings to tune to get better performance, and cost savings, with AWS RDS.

Recently I was engaged in a MySQL Performance Audit for a customer to help troubleshoot performance issues that led to downtime during periods of high traffic on their AWS RDS MySQL instances. During heavy loads, they would see messages about their InnoDB settings in the error logs:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 4460ms. The settings might not be optimal. (flushed=140, during the time.)

This message is normally a side effect of a storage subsystem that is not capable of keeping up with the number of writes (e.g., IOPs) required by MySQL. This is “Hey MySQL, try to write less. I can’t keep up,” which is a common situation when innodb_io_capacity_max is set too high.

After some time of receiving these messages, eventually, they hit performance issues to the point that the server becomes unresponsive for a few minutes. After that, things went back to normal.

Let’s look at the problem and try to gather some context information.

Investigating AWS RDS performance issues

We had a db.m5.8xlarge instance type (32vCPU – 128GB of RAM) with a gp2 storage of 5TB, which should provide up to 10000 IOPS (this is the maximum capacity allowed by gp2), running MySQL 5.7. This is a pretty decent setup, and I don’t see many customers needing to write this many sustained IOPS.

The innodb_io_capacity_max parameter was set to 2000, so the hardware should be able to deliver that many IOPS without major issues. However, gp2 suffers from a tricky way of calculating credits and usage that may drive erroneous conclusions about the real capacity of the storage. Reviewing the CloudWatch graphics, we only had roughly 8-9k IOPS (reads and writes) used during spikes.

AWS RDS MySQL

writeops

While the IO utilization was quite high, there should be some room to get more IOPS, but we were still seeing errors. What caught my attention was the self-healing condition shown by MySQL after a few minutes.

Normally, the common solution that was actually discussed during our kick-off call was, “Well, there is always the chance to move to Provisioned IOPS, but that is quite expensive.” Yes, this is true, io2 volumes are expensive, and honestly, I think they should be used only where really high IO capacity at expected latencies is required, and this didn’t seem to be the case.

Otherwise, most of the environments can adapt to gp2/gp3 volumes; for that matter, you need to provision a big enough volume and get enough IOPS.

Finding the “smoking gun” with pt-mysql-summary

Not too long ago, my colleague Yves Trudeau and I worked on a series of posts debating how to configure an instance for write-intensive workloads. A quick look at the pt-mysql-summary output shows something really interesting when approaching the issue out of the busy period of load:

# InnoDB #####################################################
                  Version | 5.7.38
         Buffer Pool Size | 93.0G
         Buffer Pool Fill | 100%
        Buffer Pool Dirty | 1%
           File Per Table | ON
                Page Size | 16k
            Log File Size | 2 * 128.0M = 256.0M
          Log Buffer Size | 8M
             Flush Method | O_DIRECT
      Flush Log At Commit | 1
               XA Support | ON
                Checksums | ON
              Doublewrite | ON
          R/W I/O Threads | 4 4
             I/O Capacity | 200
       Thread Concurrency | 0
      Concurrency Tickets | 5000
       Commit Concurrency | 0
      Txn Isolation Level | REPEATABLE-READ
        Adaptive Flushing | ON
      Adaptive Checkpoint | 
           Checkpoint Age | 78M
             InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue

 

Wait, what? 256M of redo logs and a Checkpoint Age of only 78M? That is quite conservative, considering a 93GB buffer pool size. I guess we should assume bigger redo logs for such a big buffer pool. Bingo! We have a smoking gun here.

Additionally, full ACID features were enabled, this is innodb_flush_log_at_trx_commit=1 and sync_binlog=1, which adds a lot of write overhead to every operation because, during the commit stage, everything is flushed to disk (or to gp2 in this case).

Considering a spike of load running a lot of writing queries, hitting the max checkpoint age in this setup is a very likely situation.

Basically, MySQL will perform flushing operations at a certain rate depending on several factors. This rate is normally close to innodb_io_capacity (200 by default); if the number of writes starts to approach to max checkpoint age, then the adaptive flushing algorithm will start to push up to innodb_io_capacity_max (2000 by default) to try to keep the free space in the redo logs far from the max checkpoint age limit.

If we keep pushing, we can eventually reach the max checkpoint age, which will drive the system to the synchronous state, meaning that a sort of furious flushing operations will happen beyond innodb_io_capacity_max and all writing operations will be paused (freezing writes) until there is free room in the redo logs to keep writing.

This was exactly what was happening on this server. We calculated roughly how many writes were being performed per hour, and then we recommended increasing the size of redo log files to 2x2GB each (4GB total). In practical terms, it was 3.7G due to some rounding that RDS does, so we got:

# InnoDB #####################################################
                  Version | 5.7.38
         Buffer Pool Size | 92.0G
         Buffer Pool Fill | 100%
        Buffer Pool Dirty | 2%
           File Per Table | ON
                Page Size | 16k
            Log File Size | 2 * 1.9G = 3.7G
          Log Buffer Size | 8M
             Flush Method | O_DIRECT

 

Then we also increased the innodb_io_capacity_max to 4000, so we let the adaptive flushing algorithm increase writes with some more room. Results in CloudWatch show we were right:

 

AWS RDS Cloud MySQL

The reduction during the last couple of weeks is more than 50% of IOPS, which is pretty decent now, and we haven’t changed the hardware at all. Actually, it was possible to reduce the storage size to 3TB and avoid moving to expensive io2 (provisioned IOPS) storage.

Conclusions

RDS normally works very well out of the box; most of the configurations are properly set for the type of instance provisioned. Still, I’ve found that the RDS default size of the redo logs being this small is silly, and people using a fully managed solution would expect not to worry about some common tuning.

MySQL 8.0 implemented innodb_dedicated_server that auto sizes innodb_log_file_size and innodb_log_files_in_group (now replaced by innodb_redo_log_capacity) as a function of InnoDB Buffer Pool size using a pretty simple, but effective, algorithm, and I guess it shouldn’t be hard for AWS team to implement it. We’ve done some research, and it seems RDS is not pushing this login into the 8.0 versions, which sounds strange to have such a default for innodb_redo_log_capacity

In the meantime, checking how RDS MySQL is configured with default parameters is something we all should review to avoid the typical “throwing more hardware solution” – and, by extension, spending more money.

Percona Consultants have decades of experience solving complex database performance issues and design challenges. They’ll work with you to understand your goals and objectives and provide the best, unbiased solutions for your database environment.

 

Learn more about Percona Consulting

 

A personalized Percona Database Performance Audit will help uncover potential performance killers in your current configuration.

 

Get your personalized audit

Apr
28
2023
--

Add More Security to Your Percona Server for MongoDB With AWS IAM integration!

MongoDB With AWS IAM Integration

Did you notice that Percona Server for MongoDB 6.0.5-4 was released just a few days ago? This time around, it introduced improvements to the way we handle master key rotation for data at rest encryption as well as AWS IAM integration.

One key to rule them all — improvements to master key rotation

With the improvements introduced in Percona Server for MongoDB 6.0.5-4, one key path can be used for all servers in a clustered environment. This allows us to use one vault key namespace for all nodes in a deployment while at the same time preserving key versions and allowing each node to perform key rotation without impact to the other nodes.

Changes introduced with Percona Server for MongoDB 6.0.5-4 now allow using the same key for all the members of a replica set if the user chooses so, without impact on functionality.

Why should you care about AWS IAM integration?

With all the systems users need to access daily, password management becomes a more pressing issue. The introduction of IAM systems to an enterprise has become somewhat of a security standard in large enterprises.

Our users approached us about integration with AWS IAM, commonly used in their organizations. It’s an integration missing from MongoDB Community Edition (CE) that is important for compliance with enterprise security policies of many companies. Integration with AWS IAM allows:

MongoDB AWS IAM integration

To set up this integration, follow the steps outlined in our documentation, and configure either the user or the role authentication. This will allow AWS Security Token Service (STS) to play the part of Identity Provider (IDP) in a SAML 2.0-based federation.

Your feedback matters!

We take pride in being open to feedback in Percona. Please do not hesitate to contact us via the community forums or this contact form.

What’s next

We are looking into the problems affecting large size datastores that are a pain to our users. Please let us know if there are any particular issues you are struggling with in MongoDB; we are always open to suggestions!

Learn more about Percona Server for MongoDB

Apr
28
2023
--

Open Source vs. Proprietary Database Software: What To Choose?

open source

For starters, yes, Percona has chosen sides. We are a company of open source proponents. We’re also dedicated and active participants in the global open source community. 

But the intent here is to be informative, not to declare a winner between the two. Both open source and proprietary options have advantages. At the same time, it’s hard to ignore key differences and how they affect customers. We also know that enterprise-grade features matter, so please read on. In election parlance, there might be a surprise third-party candidate that will win your vote.

Let’s start with a simple introductory comparison: With proprietary (closed source) database software, the public does not have access to the source code; only the company that owns it and those given access can modify it. With open source database software, anyone in the general public can access the source code, read it, and modify it.

Dispelling a couple of myths

Before expanding on the comparison, let’s dispel the most common myths about open source software:

Myth #1: Open source is less secure.

Sure, without the right protections, open source software can be vulnerable, but those protections exist and can be implemented. People assume that because the code is public, attackers can have at it and easily wreak havoc. That’s not so. 

With the code open to all, it’s truly transparent (DevOps.com, April 2021); security is scrutinized, and vulnerabilities are addressed by experts globally. Those most involved with open source software know the reality about security. Since the early 2000s, research has repeatedly shown that open source software is no more vulnerable than proprietary software. Those on the front lines echo those findings. A 2021 Red Hat survey showed that 89% of IT leaders see enterprise open source software as equally or more secure than proprietary software.

In contrast to the abundant support of the open source community, with proprietary systems, the software vendor must address and fix the problem, which often means providing a patch. Further, the developers of proprietary software aren’t always transparent about vulnerabilities, so software users might not know about threats until it’s too late. 

And about attackers: They often use hacking programs instead of attacking the code directly. They don’t need access to examine the code for hacking purposes (TA Digital, July 2021). Attackers find and exploit vulnerabilities in proprietary software all the time.

Myth #2: Proprietary databases are better and therefore more suitable for large enterprises.

Again, in the case of proprietary software, the developers and DBAs come from within one company or a limited team. Conversely, with open source, a global community contributes. 

The transparency of the open source model makes input inclusive and creates an abundance of scrutiny and support (Forbes, January 2022). The multitude of checks and balances places a premium on how the code affects performance. 

Another part of this myth is that open source can’t be enterprise-grade. With the right expertise, you can have an enterprise-grade open source solution. Here’s a small sampling of leading corporations that use open source software: Amazon, Audi, BMW, Google, IBM, and Facebook (CodeSnail, August 2022). The list goes on and on.

Now, myths aside, let’s get down to the brass tacks of database comparisons. Each of our subjects has its pros and cons.

Proprietary database software

Proprietary software can be beneficial for addressing immediate and/or focused database concerns. Sometimes a vendor will have an innovative way of solving a problem when there aren’t alternatives available on the market. A company will enter a relationship with that vendor because the vendor’s solution addresses present business objectives. Additionally, a single-vendor relationship can eliminate complexity; in some cases, the vendor’s solution can simplify the environment and ensure that all components work together. 

The benefits described above often are attributed solely — and inaccurately —  to proprietary software only. For some, proprietary is synonymous with business/enterprise-grade. Consequently, some organizations might not even consider using open source software, or they’ll quickly dismiss it because of a perceived lack of expertise, support, etc.

Those same customers, and others, often aren’t aware that proprietary software can limit creative options and the ability to scale. Those limitations can increasingly draw from a customer’s tech budget. As business objectives change, along with industry standards and technological advances, a customer can be stuck with software and upgrades that make more sense for the vendor’s bottom line than for addressing the customer’s changing needs. For example, the vendor might push a cloud-based solution when the customer prefers to keep its infrastructure on-premises. 

Additionally, with proprietary software, there can be drawbacks related to certifications. When you deploy software in a proprietary arrangement, the vendor might certify it only against a specific database or set of databases. Your apps, therefore, must run on one particular server.

Being stuck with a single vendor and its software can result in vendor lock-in that makes you susceptible to price hikes, paying for bundled technology with components you don’t need, and an inability to change software and infrastructure to meet unique business needs.

Open source database software

Upstream open source projects are free to download and use. There are no licensing or purchasing fees for reusing, modifying, or distributing the software. Beyond the obvious cost-efficiency, many IT leaders consider the quality of open source software on par with that of proprietary software. In fact, 32% of IT leaders in a Red Hat survey consider open source enterprise software to be of higher quality. 

Free of licensing restrictions and escalating costs that can come with proprietary software, developers can download open source software and use it to create new applications. Those freedoms help companies optimize limited tech budgets. They can more easily scale infrastructure — up or down — to meet economic conditions and changing business objectives. 

And there is the aforementioned online open source community. Whereas proprietary products exist at the whim of a single vendor, a strong open source community can help ensure a project carries on even if challenges arise for some of the project’s supporting companies. Additionally, with open source, companies can deploy their databases anywhere — in cloud, on-premises, or hybrid environments — and move them at any time.

A lack of readily available support and expertise, however, can offset the potential savings of open source database software. There must be careful implementation of the right protection to avoid vulnerabilities. And to achieve database objectives across the enterprise, a company that uses open source software often must either bolster its on-staff expertise or turn to outside support. Either option can be costly.

The best of both worlds — enterprise-grade open source software

Undoubtedly, you liked some of the attributes from each side. So how do you choose?

You don’t have to. There’s a third-party candidate offering the best of both worlds — open source database software with enterprise-grade features.

This option couples the cost-efficiency and scalability of open source with the simplicity (task-focused), cohesiveness (components work together), and security of proprietary software. With the right extensions and add-ons to make it enterprise-grade, an open source solution can replicate the applications a company uses and can handle the performance requirements of the company’s most critical workloads. A flexible, open source enterprise setup enables deployment and operation on-premises, in the cloud, or in a hybrid environment.

It’s important, however, to emphasize these words of caution: The phrase “enterprise-grade” is used a lot, but few vendors provide open source software that meets the demanding mix of enterprise needs related to integration, productivity, scalability, and security. And even when those needs are met, they’re soon to evolve. Therefore, enterprise-grade software — like community versions — still requires support. When seeking such support, it’s important to find a vendor that provides multi-database support, technology-agnostic expertise, and a flexible contract.

The search can be challenging, but vendors who provide true enterprise-grade open source software do exist. We happen to know of one.

You can learn more about the differences between open source and proprietary database software in The Ultimate Guide to Open Source Databases.

When you’re choosing a database, consider Percona

Percona is dedicated to making databases and applications run better through a combination of expertise and open source software. Our enterprise-grade distributions include the following:

  • Percona Distribution for MySQL: This single solution delivers optimized performance, greater scalability and availability, and enhanced backups — for even the most demanding workloads.
  • Percona Distribution for PostgreSQL: Put the best and most critical enterprise components from the open source community to work for you — in a single distribution, designed and tested to work together.
  • Percona Distribution for MongoDB: Ensure data availability while improving security and simplifying the development of new applications — in the most demanding public, private, and hybrid cloud environments.

Percona backs its enterprise-grade distributions with varying levels of support. We’ll provide support that best fits the needs of your company or organization — without a restrictive contract.

 

Watch Webinar: Optimize Costs With Open Source Software and Support

 

Get Open Source Support Services from Percona

 

Learn more:

Apr
26
2023
--

Open Source Software Security: Is Open Source Software Safe?

is open source software safe

Even though open source software is firmly in the mainstream, used widely by businesses, governments, and everyone who owns a cell phone or computer, the question repeatedly arises: “Is open source software safe?” Broadly speaking, the answer is a resounding yes. But it’s worth examining what we mean by “safe,” contrasting open source software with proprietary software, and discussing when you should use caution.

Defining “safe” for software

Let’s start by defining what we mean by “safe” because it’s a non-specific term that might mean different things to different people.

Safe, here, encompasses security, stability, sustainability, and compliance.

Specifically, does open source software meet a reasonable security expectation comparable to or better than proprietary software? Is open source software as stable as other software? Is it sustainable, that is – will the software continue to be developed and maintained long-term, predictably, so you can depend on it? And, finally, does open source software carry any risks around legal compliance?

Finally, let’s clarify what we’re discussing with the phrase “open source software.” Anybody can slap an open source license on some software and put it online. Our bar is higher than that. We’re not addressing hobby projects or those that don’t have an active community.

When discussing open source software, we’re talking about ongoing projects with a healthy community and substantial adoption. We will talk about how to assess that when choosing a project.

Let’s start with the big one, security.

Is open source software secure?

When a project’s source code is available to all, the question of security isn’t far behind. How can something be secure if anyone can examine the code and look for security flaws?

Would-be attackers can comb through source code to find security flaws. Sometimes they do! But it also allows “white hat” types to examine open source projects to try to find and fix vulnerabilities before attackers find them and use them. It allows organizations to identify potential vulnerabilities, report them, and apply fixes without depending on a single vendor.

The relative security of open source software has been examined repeatedly by researchers since the early 2000s. Open source software contains no more flaws on average than proprietary software. In some cases, it may have fewer vulnerabilities.

Security through obscurity – expecting software to be more secure if attackers can’t see the source code – doesn’t work. Attackers find and exploit vulnerabilities in proprietary software all the time. The Log4Shell vulnerability in Apache Log4j’s software made big headlines in 2021, but it wasn’t alone. Consider ProxyShell – a set of vulnerabilities in Microsoft Exchange that could result in Remote Code Execution (RCE).

That’s just one example. You can peruse Microsoft’s acknowledgments of security reports for a long list of vulnerabilities discovered by various researchers who found vulnerabilities in its software without access to source code. 

So, is open source software secure? In absolute terms, no software should be considered free of vulnerabilities. But, in relative terms, we’d say yes. Open source software is secure relative to proprietary software – and in some instances, we’d say more secure than proprietary software.

In all instances, open source software allows anyone to examine the software and attempt to provide fixes if they discover a vulnerability. Open source software does not depend on a single vendor that controls the software entirely.

Is open source software stable?

Users may also wonder if open source software is stable, whether it’s safe to use open source software in production environments, and that sort of thing.

Again, the answer is yes, but with a few caveats worth calling out. Let’s start with some prime examples of open source software being used where stability is crucial.

Watch the “Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment” video sessions

Open source software powers the Internet. Linux, for example, is the most widely used operating system to run services you use daily. All the major cloud providers use Linux, your cell phone company is likely using Linux to deliver phone calls, it’s used by streaming services, social media companies, and so forth. If you’re an Android user, it’s in your phone.

That’s just the most obvious example. Open source databases, like MySQL and PostgreSQL, are among the most widely used and popular databases for workloads large and small.

There’s also WordPress and Drupal, content management systems (CMSes) that power millions of websites worldwide. (WordPress, in fact, powers this blog and uses a MySQL database to store its content.)

On the smaller side, you have tools like curl and SQLite embedded in millions of devices for various uses. Open source has even gone to Mars.

Caveats of open source software

One could write a book on the successful use of open source and how well it stacks up, stability-wise, next to proprietary software. What are the caveats?

You need to evaluate open source software the same way you’d evaluate any software. Look at how it’s produced, and the health of its community or vendor, and put it to the test in a proof-of-concept (POC) or otherwise evaluate it to verify it suits your needs.

The health of the community is a broad topic, I won’t try to explore it here fully. But, in short, check out the history of the project. See how many contributors it has, whether it has vendors who support it if you need support, and make sure it’s still being maintained.

If you examine Linux, MySQL, PostgreSQL, Kubernetes, WordPress, Apache Kafka, and thousands of other projects, you’ll find projects with long histories, widespread adoption, and vendors who will provide support above and beyond just supplying the software.

That brings us to sustainability.

Is open source software sustainable?

Sustainable is a phrase used a lot to describe environmentally friendly concerns. But when we say “sustainable” here, we’re talking about whether the development process that produces the software is sustainable. To put it another way: Can we depend on that software to be here tomorrow, next month, or next year? Even longer?

This question isn’t unique to open source software! The same forces that cause software companies to go out of business or cancel projects can impact open source. 

Proprietary software goes away all the time, particularly in the age of Software-as-a-Service. Consider all the projects in Google’s graveyard, like Google Reader, Stadia, G+, and too many messaging apps to even try to recount.

Maintainers aren’t suppliers

However, open source has an added wrinkle, and we want to discuss it head-on. Open source projects are often powered by maintainers who aren’t paid directly to work on those projects. 

Maintainers are not the same thing as suppliers and vendors. An open source project is not necessarily the same thing as a product. 

For example, many of the Apache Software Foundation (ASF) projects have contributors from many different companies. Some may be paid to work on the project full time, and others may contribute as part of their day job where the software is used in their work, but they have other responsibilities. 

So if you evaluate an open source project to use in your business, you need to do some due diligence about the project’s health to verify that it has the longevity you want. Again, this is similar to doing due diligence on a software vendor. 

How to evaluate open source projects

You can feel confident that Microsoft will be around in 10 years and still support Windows and SQL Server. Likewise, Linux and PostgreSQL will almost certainly be around in 10 years. Apple is unlikely to go out of business and drop iOS anytime soon. WordPress has been chugging along for years and years and powers a huge chunk of the Internet, and it’ll still be used for blogs well into the future. 

Open source data management software survey

On the other hand, you can look at a lot of proprietary software that has hit end of life when its vendor was acquired or management changed. Microsoft killed off VisualBasic while it was still popular, for example. Twitter snapped up Vine and then shuttered. Adobe has (largely) retired Flash, though you’ll find few people to mourn Flash and quite a few who were happy to see it go.

Open source software can reach its end of life too. The ASF, for example, has its “Attic” – a process and home for ASF projects that have become obsolete or failed to maintain a large enough community of maintainers.

How can you know if an open source project will be around for the long haul and receive updates? 

A good rule of thumb? Look for widely adopted open source software with a good track record, and it’s even better if multiple vendors work on and support the software

If it can be picked up and developed by multiple vendors, it’s a much safer bet. MySQL and PostgreSQL, for example, are great examples of projects with product equivalents with support options equivalent to proprietary software without the downsides of being proprietary.

What about open source software compliance?

Finally, the question on many people’s minds is whether open source software is safe from a compliance perspective. That is, does open source software introduce any legal requirements?

I’m not a lawyer, nor do I play one on TV, so this isn’t to be confused with legal advice. If you need a genuine legal opinion, you’ll definitely want to consult a lawyer – the same as if you wanted legal advice on an End User License Agreement (EULA) with proprietary software. 

That said – licenses that meet the Open Source Definition (OSD) from the Open Source Initiative (OSI) have conditions triggered on distribution rather than use. If you install and run the software but don’t distribute it, then you don’t have any requirements to meet. Distribution is when you need to verify compliance.

What is open source distribution?

What is distribution? If your organization conveys software to other entities, that generally counts as distribution. For example, if your organization makes an electronic device with embedded software under open source licenses and sells them to customers, that’s distribution. Depending on the license, you may need to include a notice about the software, or you may need to make the source code available to customers on request. 

At least one open source license, the Affero GNU Public License (AGPL), extends the distribution concept to include interaction over a network. So, if you’re using AGPL’ed software in a SaaS offering, that may require you to distribute the source code or provide a mechanism for distributing the source code to users of that SaaS. 

So, if your organization ships software under open source licenses, then you need to have a plan to comply with the license requirements. If you simply use open source software, maybe you have a bunch of servers running Linux and an open source database like MySQL, but don’t distribute the software? Then you don’t have any special requirements to worry about.

The most popular open source licenses

The OSI has approved quite a few licenses as OSD-compliant, but in practice, you’ll see only a handful of them in use. Most open source software uses one of four or five permissive licenses (Apache 2.0, MIT, BSD 2, or BSD 3 being most likely) or one of the reciprocal GPL variants. 

These licenses are well-understood. You can find ample guidance on working with them. 

The Ultimate Guide to Open Source Databases

EULAs, on the other hand, are non-standard and ever-changing. If you use Apple software, for instance, you’re probably familiar with having to agree to EULA changes every time you update your software. If you use proprietary enterprise software, it probably has restrictions and compliance requirements to keep track of as you deploy it. 

The good news about EULAs is that you don’t have to worry about modification or distribution – because you’re not allowed to do that, you don’t need to ask what to do if you make a modification and want to distribute it. Problem solved! 

So… is it safe?

The real answer is, of course, the disappointing but realistic “it depends.” Open source software is not inherently unsafe or less safe than proprietary software.

 

Percona Database Software Solutions

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com