Aug
10
2018
--

Tuning Autovacuum in PostgreSQL and Autovacuum Internals

Tuning Autovacuum in PostgreSQL

The performance of a PostgreSQL database can be compromised by dead tuples, since they continue to occupy space and can lead to bloat. We provided an introduction to VACUUM and bloat in an earlier blog post. Now, though, it’s time to look at autovacuum for postgres, and the internals you to know to maintain a high performance PostgreSQL database needed by demanding applications.

What is autovacuum ?

Autovacuum is one of the background utility processes that starts automatically when you start PostgreSQL. As you see in the following log, the postmaster (parent PostgreSQL process) with pid 2862 has started the autovacuum launcher process with pid 2868. To start autovacuum, you must have the parameter autovacuum set to ON. In fact, you should not set it to OFF in a production system unless you are 100% sure about what you are doing and its implications.

avi@percona:~$ps -eaf | egrep "/post|autovacuum"
postgres  2862     1  0 Jun17 pts/0    00:00:11 /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/data
postgres  2868  2862  0 Jun17 ?        00:00:10 postgres: autovacuum launcher process
postgres 15427  4398  0 18:35 pts/1    00:00:00 grep -E --color=auto /post|autovacuum

Why is autovacuum needed ? 

We need VACUUM to remove dead tuples, so that the space occupied by dead tuples can be re-used by the table for future inserts/updates. To know more about dead tuples and bloat, please read our previous blog post. We also need ANALYZE on the table that updates the table statistics, so that the optimizer can choose optimal execution plans for an SQL statement. It is the autovacuum in postgres that is responsible for performing both vacuum and analyze on tables.

There exists another background process in postgres called Stats Collector that tracks the usage and activity information. The information collected by this process is used by autovacuum launcher to identify the list of candidate tables for autovacuum. PostgreSQL identifies the tables needing vacuum or analyze automatically, but only when autovacuum is enabled. This ensures that postgres heals itself and stops the database from developing more bloat/fragmentation.

Parameters needed to enable autovacuum in PostgreSQL are :

autovacuum = on  # ( ON by default )
track_counts = on # ( ON by default )

track_counts

  is used by the stats collector. Without that in place, autovacuum cannot access the candidate tables.

Logging autovacuum

Eventually, you may want to log the tables on which autovacuum spends more time. In that case, set the parameter log_autovacuum_min_duration to a value (defaults to milliseconds), so that any autovacuum that runs for more than this value is logged to the PostgreSQL log file. This may help tune your table level autovacuum settings appropriately.

# Setting this parameter to 0 logs every autovacuum to the log file.
log_autovacuum_min_duration = '250ms' # Or 1s, 1min, 1h, 1d

Here is an example log of autovacuum vacuum and analyze

< 2018-08-06 07:22:35.040 EDT > LOG: automatic vacuum of table "vactest.scott.employee": index scans: 0
pages: 0 removed, 1190 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 110008 removed, 110008 remain, 0 are dead but not yet removable
buffer usage: 2402 hits, 2 misses, 0 dirtied
avg read rate: 0.057 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.02u sec elapsed 0.27 sec
< 2018-08-06 07:22:35.199 EDT > LOG: automatic analyze of table "vactest.scott.employee" system usage: CPU 0.00s/0.02u sec elapsed 0.15 sec

When does PostgreSQL run autovacuum on a table ? 

As discussed earlier, autovacuum in postgres refers to both automatic VACUUM and ANALYZE and not just VACUUM. An automatic vacuum or analyze runs on a table depending on the following mathematic equations.

The formula for calculating the effective table level autovacuum threshold is :

Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

With the equation above, it is clear that if the actual number of dead tuples in a table exceeds this effective threshold, due to updates and deletes, that table becomes a candidate for autovacuum vacuum.

Autovacuum ANALYZE threshold for a table = autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold

The above equation says that any table with a total number of inserts/deletes/updates exceeding this threshold—since last analyze—is eligible for an autovacuum analyze.

Let’s understand these parameters in detail.

  • autovacuum_vacuum_scale_factor Or autovacuum_analyze_scale_factor : Fraction of the table records that will be added to the formula. For example, a value of 0.2 equals to 20% of the table records.
  • autovacuum_vacuum_threshold Or autovacuum_analyze_threshold : Minimum number of obsolete records or dml’s needed to trigger an autovacuum.

Let’s consider a table: percona.employee with 1000 records and the following autovacuum parameters.

autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50

Using the above mentioned mathematical formulae as reference,

Table : percona.employee becomes a candidate for autovacuum Vacuum when,
Total number of Obsolete records = (0.2 * 1000) + 50 = 250

Table : percona.employee becomes a candidate for autovacuum ANALYZE when,
Total number of Inserts/Deletes/Updates = (0.1 * 1000) + 50 = 150

Tuning Autovacuum in PostgreSQL

We need to understand that these are global settings. These settings are applicable to all the databases in the instance. This means, regardless of the table size, if the above formula is reached, a table is eligible for autovacuum vacuum or analyze.

Is this a problem ?

Consider a table with ten records versus a table with a million records. Even though the table with a million records may be involved in transactions far more often, the frequency at which a vacuum or an analyze runs automatically could be greater for the table with just ten records.

Consequently, PostgreSQL allows you to configure individual table level autovacuum settings that bypass global settings.

ALTER TABLE scott.employee SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100);

Output Log
----------
avi@percona:~$psql -d percona
psql (10.4)
Type "help" for help.
percona=# ALTER TABLE scott.employee SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100);
ALTER TABLE

The above setting runs autovacuum vacuum on the table scott.employee only once there is more than 100 obsolete records.

How do we identify the tables that need their autovacuum settings tuned ? 

In order to tune autovacuum for tables individually, you must know the number of inserts/deletes/updates on a table for an interval. You can also view the postgres catalog view : pg_stat_user_tables to get that information.

percona=# SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples"
FROM pg_stat_user_tables
WHERE schemaname = 'scott' and relname = 'employee';
 inserts | updates | deletes | live_tuples | dead_tuples
---------+---------+---------+-------------+-------------
      30 |      40 |       9 |          21 |          39
(1 row)

As observed in the above log, taking a snapshot of this data for a certain interval should help you understand the frequency of DMLs on each table. In turn, this should help you with tuning your autovacuum settings for individual tables.

How many autovacuum processes can run at a time ? 

There cannot be more than autovacuum_max_workers number of autovacuum processes running at a time, across the instance/cluster that may contain more than one database. Autovacuum launcher background process starts a worker process for a table that needs a vacuum or an analyze. If there are four databases with autovacuum_max_workers set to 3, then, the 4th database has to wait until one of the existing worker process gets free.

Before starting the next autovacuum, it waits for autovacuum_naptime, the default is 1 min on most of the versions. If you have three databases, the next autovacuum waits for 60/3 seconds. So, the wait time before starting next autovacuum is always (autovacuum_naptime/N) where N is the total number of databases in the instance.

Does increasing autovacuum_max_workers alone increase the number of autovacuum processes that can run in parallel ?
NO. This is explained better in next few lines.

Is VACUUM IO intensive? 

Autovacuum can be considered as a cleanup. As discussed earlier, we have 1 worker process per table. Autovacuum reads 8KB (default block_size) pages of a table from disk and modifies/writes to the pages containing dead tuples. This involves both read and write IO. Thus, this could be an IO intensive operation, when there is an autovacuum running on a huge table with many dead tuples, during a peak transaction time. To avoid this issue, we have a few parameters that are set to minimize the impact on IO due to vacuum.

The following are the parameters used to tune autovacuum IO

  • autovacuum_vacuum_cost_limit : total cost limit autovacuum could reach (combined by all autovacuum jobs).
  • autovacuum_vacuum_cost_delay : autovacuum will sleep for these many milliseconds when a cleanup reaching autovacuum_vacuum_cost_limit cost is done.
  • vacuum_cost_page_hit : Cost of reading a page that is already in shared buffers and doesn’t need a disk read.
  • vacuum_cost_page_miss : Cost of fetching a page that is not in shared buffers.
  • vacuum_cost_page_dirty : Cost of writing to each page when dead tuples are found in it.
Default Values for the parameters discussed above.
------------------------------------------------------
autovacuum_vacuum_cost_limit = -1 (So, it defaults to vacuum_cost_limit) = 200
autovacuum_vacuum_cost_delay = 20ms
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

Consider autovacuum VACUUM running on the table percona.employee.

Let’s imagine what can happen in 1 second. (1 second = 1000 milliseconds)

In a best case scenario where read latency is 0 milliseconds, autovacuum can wake up and go for sleep 50 times (1000 milliseconds / 20 ms) because the delay between wake-ups needs to be 20 milliseconds.

1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay

Since the cost associated per reading a page in shared_buffers is 1, in every wake up 200 pages can be read, and in 50 wake-ups 50*200 pages can be read.

If all the pages with dead tuples are found in shared buffers, with an autovacuum_vacuum_cost_delay of 20ms, then it can read: ((200 / vacuum_cost_page_hit) * 8) KB in each round that needs to wait forautovacuum_vacuum_cost_delay amount of time.

Thus, at the most, an autovacuum can read : 50 * 200 * 8 KB = 78.13 MB per second (if blocks are already found in shared_buffers), considering the block_size as 8192 bytes.

If the blocks are not in shared buffers and need to fetched from disk, an autovacuum can read : 50 * ((200 / vacuum_cost_page_miss) * 8) KB = 7.81 MB per second.

All the information we have seen above is for read IO.

Now, in order to delete dead tuples from a page/block, the cost of a write operation is : vacuum_cost_page_dirty, set to 20 by default.

At the most, an autovacuum can write/dirty : 50 * ((200 / vacuum_cost_page_dirty) * 8) KB = 3.9 MB per second.

Generally, this cost is equally divided to all the autovacuum_max_workers number of autovacuum processes running in the Instance. So, increasing the autovacuum_max_workers may delay the autovacuum execution for the currently running autovacuum workers. And increasing the autovacuum_vacuum_cost_limit may cause IO bottlenecks. An important point to note is that this behaviour can be overridden by setting the storage parameters of individual tables, which would subsequently ignore the global settings.

postgres=# alter table percona.employee set (autovacuum_vacuum_cost_limit = 500);
ALTER TABLE
postgres=# alter table percona.employee set (autovacuum_vacuum_cost_delay = 10);
ALTER TABLE
postgres=#
postgres=# \d+ percona.employee
Table "percona.employee"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
Options: autovacuum_vacuum_threshold=10000, autovacuum_vacuum_cost_limit=500, autovacuum_vacuum_cost_delay=10

Thus, on a busy OLTP database, always have a strategy to implement manual VACUUM on tables that are frequently hit with DMLs, during a low peak window. You may have as many parallel vacuum jobs as possible when you run it manually after setting relevant autovacuum_* settings. For this reason, a scheduled manual Vacuum Job is always recommended alongside finely tuned autovacuum settings.

The post Tuning Autovacuum in PostgreSQL and Autovacuum Internals appeared first on Percona Database Performance Blog.

Aug
10
2018
--

This Week in Data with Colin Charles 48: Coinbase Powered by MongoDB and Prometheus Graduates in the CNCF

Colin Charles

Colin CharlesJoin Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

The call for submitting a talk to Percona Live Europe 2018 is closing today, and while there may be a short extension, have you already got your talk submitted? I suggest doing so ASAP!

I’m sure many of you have heard of cryptocurrencies, the blockchain, and so on. But how many of you realiize that Coinbase, an application that handles cryptocurrency trades, matching book orders, and more, is powered by MongoDB? With the hype and growth in interest in late 2017, Coinbase has had to scale. They gave an excellent talk at MongoDB World, titled MongoDB & Crypto Mania (the video is worth a watch), and they’ve also written a blog post, How we’re scaling our platform for spikes in customer demand. They even went driver hacking (the Ruby driver for MongoDB)!

It is great to see there be a weekly review of happenings in the Vitess world.

PingCap and TiDB have been to many Percona Live events to present, and recently hired Morgan Tocker. Morgan has migrated his blog from MySQL to TiDB. Read more about his experience in, This blog, now Powered by WordPress + TiDB. Reminds me of the early days of Galera Cluster and showing how Drupal could be powered by it!

Releases

Link List

  • Sys Schema MySQL 5.7+ – blogger from Wipro, focusing on an introduction to the sys schema on MySQL (note: still not available in the MariaDB Server fork).
  • Prometheus Graduates in the CNCF, so is considered a mature project. Criteria for graduation is such that “projects must demonstrate thriving adoption, a documented, structured governance process, and a strong commitment to community sustainability and inclusivity.” Percona benefits from Prometheus in Percona Monitoring & Management (PMM), so we should celebrate this milestone!
  • Replicating from MySQL 8.0 to MySQL 5.7
  • A while ago in this column, we linked to Shlomi Noach’s excellent post on MySQL High Availability at GitHub. We were also introduced to GitHub Load Balancer (GLB), which they ran on top of HAProxy. However back then, GLB wasn’t open; now you can get GLB Director: GLB: GitHub’s open source load balancer. The project describes GLB Director as: “… a Layer 4 load balancer which scales a single IP address across a large number of physical machines while attempting to minimise connection disruption during any change in servers. GLB Director does not replace services like haproxy and nginx, but rather is a layer in front of these services (or any TCP service) that allows them to scale across multiple physical machines without requiring each machine to have unique IP addresses.”
  • F1 Query: Declarative Querying at Scale – a well-written paper.

Upcoming Appearances

Feedback

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

 

The post This Week in Data with Colin Charles 48: Coinbase Powered by MongoDB and Prometheus Graduates in the CNCF appeared first on Percona Database Performance Blog.

Aug
09
2018
--

Dropbox is crashing despite beating Wall Street expectations, announces COO Dennis Woodside is leaving

Back when Dennis Woodside joined Dropbox as its chief operating officer more than four years ago, the company was trying to justify the $10 billion valuation it had hit in its rapid rise as a Web 2.0 darling. Now, Dropbox is a public company with a nearly $14 billion valuation, and it once again showed Wall Street that it’s able to beat expectations with a now more robust enterprise business alongside its consumer roots.

Dropbox’s second quarter results came in ahead of Wall Street’s expectations on both the earnings and revenue front. The company also announced that Dennis Woodside will be leaving the company. Woodside joined at a time when Dropbox was starting to figure out its enterprise business, which it was able to grow and transform into a strong case for Wall Street that it could finally be a successful publicly traded company. The IPO was indeed successful, with the company’s shares soaring more than 40 percent in its debut, so it makes sense that Woodside has essentially accomplished his job by getting it into a business ready for Wall Street.

“I think as a team we accomplished a ton over the last four and a half years,” Woodside said in an interview. “When I joined they were a couple hundred million in revenue and a little under 500 people. [CEO] Drew [Houston] and Arash [Ferdowsi] have built a great business, since then we’ve scaled globally. Close to half our revenue is outside the U.S., we have well over 300,000 teams for our Dropbox business product, which was nascent there. These are accomplishments of the team, and I’m pretty proud.”

The stock initially exploded in extended trading by rising more than 7 percent, though even prior to the market close and the company reporting its earnings, the stock had risen as much as 10 percent. But following that spike, Dropbox shares are now down around 5 percent. Dropbox is one of a number of SaaS companies that have gone public in recent months, including DocuSign, that have seen considerable success. While Dropbox has managed to make its case with a strong enterprise business, the company was born with consumer roots and has tried to carry over that simplicity with the enterprise products it rolls out, like its collaboration tool Dropbox Paper.

Here’s a quick rundown of the numbers:

  • Q2 Revenue: Up 27 percent year-over-year to $339.2 million, compared to estimates of $331 million in revenue.
  • Q2 GAAP Gross Margin: 73.6 percent, as compared to 65.4 percent in the same period last year.
  • Q2 adjusted earnings: 11 cents per share compared, compared to estimates of 7 cents per share.
  • Paid users: 11.9 million paying users, up from 9.9 million in the same quarter last year.
  • ARPU: $116.66, compared to $111.19 same quarter last year.

So, not only is Dropbox able to show that it can continue to grow that revenue, the actual value of its users is also going up. That’s important, because Dropbox has to show that it can continue to acquire higher-value customers — meaning it’s gradually moving up the Fortune 100 chain and getting larger and more established companies on board that can offer it bigger and bigger contracts. It also gives it the room to make larger strategic moves, like migrating onto its own architecture late last year, which, in the long run could turn out to drastically improve the margins on its business.

“We did talk earlier in the quarter about our investment over the last couple years in SMR technology, an innovative storage technology that allows us to optimize cost and performance,” Woodside said. “We continue to innovate ways that allow us to drive better performance, and that drives better economics.”

The company is still looking to make significant moves in the form of new hires, including recently announcing that it has a new VP of product and VP of product marketing, Adam Nash and Naman Khan, respectively. Dropbox’s new team under CEO Drew Houston are tasked with continuing the company’s path to cracking into larger enterprises, which can give it a much more predictable and robust business alongside the average consumers that pay to host their files online and access them from pretty much anywhere.

In addition, there are a couple executive changes as Woodside transitions out. Yamini Rangan, currently VP of Business Strategy & Operations, will become Chief Customer Officer reporting to Houston, and comms VP Lin-Hua Wu will also report to Houston.

Dropbox had its first quarterly earnings check-in and slid past the expectations that Wall Street had, though its GAAP gross margin slipped a little bit and may have offered a slight negative signal for the company. But since then, Dropbox’s stock hasn’t had any major missteps, giving it more credibility on the public markets — and more resources to attract and retain talent with compensation packages linked to that stock.

“Our retention has been quite strong,” Woodside said. “We see strong retention characteristics across the customer set we have, whether it’s large or small. Obviously larger companies have more opportunity to expand over time, so our expansion metrics are quite strong in customers of over several hundred employees. But even among small businesses, Dropbox is the kind of product that has gravity. Once you start using it and start sharing it, it becomes a place where your business is small or large is managing all its content, it tends to be a sticky experience.”

Aug
09
2018
--

Blissfully grabs $3.5 million seed investment to help companies get their SaaS in gear

Blissfully, a New York City startup that helps companies understand their SaaS usage inside their organizations, announced it has received a $3.5 million seed round.

The investment was led by Hummer Winblad Venture Partners. Hubspot, Founder Collective, and several unnamed pre-seed investors also participated. They got a $1.5 million pre-seed investment, bringing the total so far to $5 million, according the company.

Company co-founder and CEO Ariel Diaz says Blissfully actually helped him and his co-founder solve a problem they were having tracking the SaaS usage at their previous startups. Like many companies, they were using spreadsheets to track this information and they found it was untenable as the company grew beyond 30 or 40 people. They figured there had to be a better way, so they built one.

Their product is much more than simply a database of the SaaS products in use inside an organization. It can integrate with existing company systems like single sign-on tools such as Okta and OneLogIn, financial reporting systems and G Suite login information. “We are trying to automate as much of the data collection as possible to discover what you’re using, who’s using it and how much you are spending,” he said.

Blissfully SaaS report. Screenshot: Blissfully

Their scans often turn up products customers thought they had canceled or those that IT had asked employees to stop using. More than finding Shadow IT, the product also gives insight to overall SaaS spend, which many companies have trouble getting a grip on. They can find most usage with a scan. Some data such as customized contract information may have to be manually entered into the system, he says.

Hubspot CEO Brian Halligan, whose company is one of the investors in this round, sees a growing need for this kind of tool. “The widespread growth of SaaS across companies of all sizes is a leading indicator of the market need for Blissfully. As business’ investments in SaaS increase, they lose visibility into issues ranging from spending to security,” Halligan said in a statement.

The company offers a freemium and pay model and is available in the G Suite Marketplace. If you go for the free version, you can scan your systems for SaaS usage, but if you want to do more complex integrations with company systems, you have to pay. They currently have 10 employees and 500 customers with a mix of paying and free.

One interesting aspect of the Blissfully tool is that it is built entirely using Serverless architecture on AWS Lambda.

Aug
09
2018
--

Prometheus monitoring tool joins Kubernetes as CNCF’s latest ‘graduated’ project

The Cloud Native Computing Foundation (CNCF) may not be a household name, but it houses some important open source projects including Kubernetes, the fast-growing container orchestration tool. Today, CNCF announced that the Prometheus monitoring and alerting tool had joined Kubernetes as the second “graduated” project in the organization’s history.

The announcement was made at PromCon, the project’s dedicated conference being held in Munich this week. According to Chris Aniszczyk, CTO and COO at CNCF, a graduated project reflects the overall maturity where it has reached a tipping point in terms of diversity of contribution, community and adoption.

For Prometheus that means 20 active maintainers, more than 1,000 contributors and more than 13,000 commits. Its contributors include the likes of DigitalOcean, Weaveworks, ShowMax and Uber.

CNCF projects start in the sandbox, move onto incubation and finally to graduation. To achieve graduation level, they need to adopt the CNCF Code of Conduct, have passed an independent security audit and defined a community governance structure. Finally it needs to show an “ongoing commitment to code quality and security best practices,” according to the organization.

Aniszczyk says the tool consists of a time series database combined with a query language that lets developers search for issues or anomalies in their system and get analytics back based on their queries. Not surprisingly, it is especially well suited to containers.

Like Kubernetes, the project that became Prometheus has its roots inside Google. Google was one of the first companies to work with containers and developed Borg (the Kubernetes predecessor) and Borgmon (the Prometheus predecessor). While Borg’s job was to manage container orchestration, Borgmon’s job was to monitor the process and give engineers feedback and insight into what was happening to the containers as they moved through their lifecycle.

While its roots go back to Borgmon, Prometheus as we know it today was developed by a couple of former Google engineers at SoundCloud in 2012. It joined Kubernetes as the second CNCF project in May 2016, and appropriately is the second graduate.

The Cloud Native Computing Foundation’s role in all of this to help promote cloud native computing, the notion that you can manage your infrastructure wherever it lives in a common way, greatly reducing the complexity of managing on-prem and cloud resources. It is part of the Linux Foundation and boasts some of the biggest names in tech as members.

Aug
09
2018
--

IBM teams with Maersk on new blockchain shipping solution

IBM and shipping giant Maersk having been working together for the last year developing a blockchain-based shipping solution called TradeLens. Today they moved the project from Beta into limited availability.

Marie Wieck, GM for IBM Blockchain says the product provides a way to digitize every step of the global trade workflow, transforming it into a real-time communication and visual data sharing tool.

TradeLens was developed jointly by the two companies with IBM providing the underlying blockchain technology and Maersk bringing the worldwide shipping expertise. It involves three components: the blockchain, which provides a mechanism for tracking goods from factory or field to delivery, APIs for others to build new applications on top of the platform these two companies have built, and a set of standards to facilitate data sharing among the different entities in the workflow such as customs, ports and shipping companies.

Wieck says the blockchain really changes how companies have traditionally tracked shipped goods. While many of the entities in the system have digitized the process, the data they have has been trapped in silos and previous attempts at sharing like EDI have been limited. “The challenge is they tend to think of a linear flow and you really only have visibility one [level] up and one down in your value chain,” she said.

The blockchain provides a couple of obvious advantages over previous methods. For starters, she says it’s safer because data is distributed, making it much more secure with digital encryption built in. The greatest advantage though is the visibility it provides. Every participant can check any aspect of the flow in real time, or an auditor or other authority can easily track the entire process from start to finish by clicking on a block in the blockchain instead of requesting data from each entity manually.

While she says it won’t entirely prevent fraud, it does help reduce it by putting more eyeballs onto the process. “If you had fraudulent data at start, blockchain won’t help prevent that. What it does help with is that you have multiple people validating every data set and you get greater visibility when something doesn’t look right,” she said.

As for the APIs, she sees the system becoming a shipping information platform. Developers can build on top of that, taking advantage of the data in the system to build even greater efficiencies. The standards help pull it together and align with APIs, such as providing a standard Bill of Lading. They are starting by incorporating existing industry standards, but are also looking for gaps that slow things down to add new standard approaches that would benefit everyone in the system.

So far, the companies have 94 entities in 300 locations around the world using TradeLens including customs authorities, ports, cargo shippers and logistics companies. They are opening the program to limited availability today with the goal of a full launch by the end of this year.

Wieck ultimately sees TradeLens as a way to facilitate trade by building in trust, the end of goal of any blockchain product. “By virtue of already having an early adopter program, and having coverage of 300 trading locations around the world, it is a very good basis for the global exchange of information. And I personally think visibility creates trust, and that can help in a myriad of ways,” she said.

Aug
09
2018
--

Lock Down: Enforcing AppArmor with Percona XtraDB Cluster

Enforcing AppArmor with Percona XtraDB Cluster

Recently, I wrote a blog post showing how to enforce SELinux with Percona XtraDB Cluster (PXC). The Linux distributions derived from RedHat use SELinux. There is another major mandatory discretionary access control (DAC) system, AppArmor. Ubuntu, for example, installs AppArmor by default. If you are concerned by computer security and use PXC on Ubuntu, you should enforce AppArmor. This post will guide you through the steps of creating a profile for PXC and enabling it. If you don’t want to waste time, you can just grab my profile, it seems to work fine. Adapt it to your environment if you are using non-standard paths. Look at the section “Copy the profile” for how to install it. For the brave, let’s go!

Install the tools

In order to do anything with AppArmor, we need to install the tools. On Ubuntu 18.04, I did:

apt install apparmor-utils

The apparmor-utils package provides the tools we need to generate a skeleton profile and parse the system logs.

Create a skeleton profile

AppArmor is fairly different from SELinux. Instead of attaching security tags to resources, you specify what a given binary can access, and how, in a text file. Also, processes can inherit permissions from their parent. We will only create a profile for the mysqld_safe script and it will cover the mysqld process and the SST scripts as they are executed under it. You create the skeleton profile like this:

root@BlogApparmor2:~# aa-autodep /usr/bin/mysqld_safe
Writing updated profile for /usr/bin/mysqld_safe.

On Ubuntu 18.04, there seems to be a bug. I reported it and apparently I am not the only one with the issue. If you get a “KeyError” error with the above command, try:

root@BlogApparmor2:~# echo "#include <abstractions>" > /etc/apparmor.d/scripts
root@BlogApparmor2:~# aa-autodep /usr/bin/mysqld_safe

The aa-autodep command creates the profile “usr.bin.mysqld_safe” in the /etc/apparmor.d directory. The initial content is:

root@BlogApparmor2:~# cat /etc/apparmor.d/usr.bin.mysqld_safe
# Last Modified: Wed Jul 25 18:56:31 2018
#include <tunables/global>
/usr/bin/mysqld_safe flags=(complain) {
  #include <abstractions/base>
  #include <abstractions/bash>
  /bin/dash ix,
  /lib/x86_64-linux-gnu/ld-*.so mr,
  /usr/bin/mysqld_safe r,
}

I suggest you add, ahead of time, things you know are needed. In my case, I added:

/etc/mysql/** r,
/usr/bin/innobackupex mrix,
/usr/bin/wsrep_sst_xtrabackup-v2 mrix,
/usr/lib/galera3/* r,
/usr/lib/mysql/plugin/* r,
/usr/sbin/mysqld mrix,
/var/log/mysqld.log w,
owner /tmp/** rw,
owner /var/lib/mysql/** rwk,

This will save time on redundant questions later. Those entries are permissions granted to mysqld_safe. For example,

/etc/mysql** r

  allows to read everything in

/etc/mysql

  and its subdirectories. These lines need to go right after the

/usr/bin/mysqld_safe r,

  line. Once done, parse and load the profile with:

root@BlogApparmor2:~# apparmor_parser -r /etc/apparmor.d/usr.bin.mysqld_safe

Get a well behaved SST script

If you read my previous blog post on SELinux, you may recall the

wsrep_sst_xtrabackup-v2

  script does not behave well, security wise. The Percona developers have released a fixed version but it may not be available yet in a packaged form. In the meantime, you can download it from github.

Start iterating

My initial thought was to put the profile in complain mode, generate activity and parse the logs with aa-logprof to get entries to add to the profile. Likely there is something I am doing wrong but in complain mode, aa-logprof detects nothing. In order to get something I had to enforce the profile with:

root@BlogApparmor2:~# aa-enforce /etc/apparmor.d/usr.bin.mysqld_safe

Then, I iterated many times—like more than 20—over the following sequence:

  1. rm -rf /var/lib/mysql/* # optional
  2. systemctl start mysql &
  3. tail -f /var/log/mysqld.log /var/log/kern.log
  4. systemctl stop mysql
  5. ps fax | egrep ‘mysqld_safe|mysqld’ | grep -v grep | awk ‘{print $1}’ | xargs kill -9 # sometimes
  6. aa-logprof
  7. if something was not right, jump back to step 1

See the next section for how to run aa-logprof. Once that sequence worked well, I tried SST (joiner/donor) roles and IST.

Parse the logs with aa-logprof

Now, the interesting part begins, parsing the logs. Simply begin the process with:

root@BlogApparmor2:~#  aa-logprof

and answer the questions. Be careful, I made many mistakes before I got it right, remember I am more a DBA than a Sysadmin. For example, you’ll get questions like:

Profile:  /usr/sbin/mysqld
Path:     /etc/hosts.allow
New Mode: r
Severity: unknown
 [1 - #include <abstractions/lxc/container-base>]
  2 - #include <abstractions/lxc/start-container>
  3 - /etc/hosts.allow r,
(A)llow / [(D)eny] / (I)gnore / (G)lob / Glob with (E)xtension / (N)ew / Audi(t) / Abo(r)t / (F)inish

AppArmor asks you how it should provide read access to the

/etc/hosts.allow

  file. If you answer right away with “A”, it will add

#include <abstractions/lxc/container-base>

 to the profile. With all the dependencies pulled by the lxc-related includes, you basically end up allowing nearly everything. You must first press “3” to get:

Profile:  /usr/sbin/mysqld
Path:     /etc/hosts.allow
New Mode: r
Severity: unknown
  1 - #include <abstractions/lxc/container-base>
  2 - #include <abstractions/lxc/start-container>
 [3 - /etc/hosts.allow r,]
(A)llow / [(D)eny] / (I)gnore / (G)lob / Glob with (E)xtension / (N)ew / Audi(t) / Abo(r)t / (F)inish

Notice the “[ ]” have moved to the bottom entry and then, press “A”. You’ll also get questions like:

Profile:  /usr/bin/mysqld_safe
Execute:  /bin/sed
Severity: unknown
(I)nherit / (C)hild / (N)amed / (X) ix On / (D)eny / Abo(r)t / (F)inish

For such a question, my answer is “I” for inherit. After a while, you’ll get through all the questions and you’ll be asked to save the profile:

The following local profiles were changed. Would you like to save them?
 [1 - /usr/bin/mysqld_safe]
(S)ave Changes / Save Selec(t)ed Profile / [(V)iew Changes] / View Changes b/w (C)lean profiles / Abo(r)t
Writing updated profile for /usr/bin/mysqld_safe.

Revise the profile

Do not hesitate to edit the profile if you see, for example, many similar file entries which could be replaced by a “*” or “**”. If you manually modify the profile, you need to parse it to load your changes:

root@BlogApparmor2:~# apparmor_parser -r /etc/apparmor.d/usr.bin.mysqld_safe

Copy the profile

Once you have a server running with AppArmor enforced on PXC, simply copy the profile to the other servers and parse it. For example:

root@BlogApparmor3:~# cd /etc/apparmor.d
root@BlogApparmor3:/etc/apparmor.d# scp ubuntu@10.0.4.76:/etc/apparmor.d/usr.bin.mysqld_safe .
ubuntu@10.0.4.76's password:
usr.bin.mysqld_safe                                   100% 2285     3.0MB/s   00:00
root@BlogApparmor3:/etc/apparmor.d# aa-enforce usr.bin.mysqld_safe
Setting /etc/apparmor.d/usr.bin.mysqld_safe to enforce mode.

You can always verify if the profile is enforced with:

root@BlogApparmor3:/etc/apparmor.d# aa-status
apparmor module is loaded.
42 profiles are loaded.
20 profiles are in enforce mode.
 /sbin/dhclient
 ...
 /usr/bin/mysqld_safe
 ...
 man_groff

Once enforced, I strongly advise to monitor the log files on a regular basis to see if anything has been overlooked. Similarly if you encounter a strange and unexpected behavior with PXC. Have the habit of checking the logs, it might save a lot of frustrating work.

Conclusion

As we have just seen, enabling AppArmor with PXC is not a difficult task, it just requires some patience. AppArmor is an essential component of a layered security approach. It achieves similar goals as the other well known DAC framework, SELinux. With the rising security concerns and the storage of sensitive data in databases, there are compelling reasons to enforce a DAC framework. I hope these two posts will help DBAs and Sysadmins to configure and enable DAC for PXC.

The post Lock Down: Enforcing AppArmor with Percona XtraDB Cluster appeared first on Percona Database Performance Blog.

Aug
09
2018
--

Webinar Thursday Aug 9: Migrating to AWS Aurora, Monitoring AWS Aurora with PMM

Monitoring Amazon Aurora with PMM

Monitoring Amazon Aurora with PMMPlease join Autodesk’s Senior Database Engineer, Vineet Khanna, and Percona’s Sr. MySQL DBA, Tate McDaniel as they present Migrating to Aurora and Monitoring with PMM on Thursday, August 9th, 2018, at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).

Amazon Web Services (AWS) Aurora is one of the most popular cloud-based RDBMS solutions. The main reason for Aurora’s success is because it’s based on InnoDB storage engine.

In this session, we will talk about how you can efficiently plan for migration to Aurora using Terraform and Percona products and solutions. We will share our Terraform code for launching AWS Aurora clusters, look at tricks for checking data consistency, verify migration paths and effectively monitor the environment using PMM.

The topics in this session include:

  • Why AWS Aurora? What is the future of AWS Aurora?
  • Build Aurora Infrastructure
  • Using Terraform (Without Data)
  • Restore Using Terraform & Percona XtraBackup (Using AWS S3 Bucket)
  • Verify data consistency
  • Aurora migration
  • 1:1 migration
  • Many:1 migration using Percona Server multi-source replication
  • Show benchmarks and PMM dashboard
  • Demo

Register Now

 

Vineet KhannaVineet Khanna, Senior Database Engineer, Autodesk

Vineet Khanna, Senior Database Engineer at Autodesk, has 10+ years of experience as a MySQL DBA. His main professional interests are managing complex database environments, improving database performance, architecting High Availability solutions for MySQL. He has handled database environments of organizations like Chegg, Zendesk, Adobe.

 

Tate McDanielTate Mcdaniel, Sr. MySQL DBA

Tate joined Percona in June 2017 as a Remote MySQL DBA. He holds a Bachelors degree in Information Systems and Decision Strategies from LSU. He has 10+ years of experience working with MySQL and operations management. His great love is application query tuning. In his off time, he races sailboats, travels the Caribbean by sailboat, and
drives all over in an RV.

The post Webinar Thursday Aug 9: Migrating to AWS Aurora, Monitoring AWS Aurora with PMM appeared first on Percona Database Performance Blog.

Aug
09
2018
--

How to Change Settings for PMM Deployed via Docker

change settings for PMM deployed docker

When deployed through Docker Percona Monitoring and Management (PMM) uses environment variables for its configuration

For example, if you want to adjust metrics resolution you can pass

-e METRICS_RESOLUTION=Ns

  as  an option to the

docker run

  command:

docker run -d \
  -p 80:80 \
  --volumes-from pmm-data \
  --name pmm-server \
  --restart always \
  -e METRICS_RESOLUTION=2s \
  percona/pmm-server:latest

You would think if you want to change the setting for existing installation you can just stop the container with

docker stop

  and when you want to start, passing new environment variable with

docker start

Unfortunately, this is not going to work as

docker start

 does not support changing environment variables, at least not at the time of writing. I assume the idea is to keep container immutable and if you want container with different properties—like environment variables—you should run a new container instead. Here’s how.

Stop and Rename the old container, just in case you want to go back

docker stop pmm-server
docker rename pmm-server pmm-server-old

Refresh the container with the latest version

docker pull percona/pmm-server:latest

Do not miss this step!  When you destroy and recreate the container, all the updates you have done through PMM Web interface will be lost. What’s more, the software version will be reset to the one in the Docker image. Running an old PMM version with a data volume modified by a new PMM version may cause unpredictable results. This could include data loss.

Run the container with the new settings, for example changing METRICS_RESOLUTION

docker run -d \
  -p 80:80 \
  --volumes-from pmm-data \
  --name pmm-server \
  --restart always \
  -e METRICS_RESOLUTION=5s \
  percona/pmm-server:latest

After you’re happy with your new container deployment you can remove the old container

docker rm pmm-server-old

That’s it! You should have running the latest PMM version with updated configuration settings.

The post How to Change Settings for PMM Deployed via Docker appeared first on Percona Database Performance Blog.

Aug
08
2018
--

Dropbox hires a new VP of product and VP of product marketing

After a largely successful IPO, Dropbox is adding another couple of hires today as it looks to continue its consumer-slash-enterprise growth playbook: bringing on a new VP of product in former CEO and president of Wealthfront Adam Nash; and a new VP of product marketing and global campaigns in Naman Khan.

Both have extensive experience from products that span multiple different verticals, with Nash previously working at LinkedIn and eBay and Khan spending time with Microsoft Office and Autodesk. The company went public earlier this year to a pretty successful IPO, though the stock hasn’t seen any dramatic fireworks, and has accumulated more than 500 million registered users in its decade-plus life. But it’s also gone through a kind of transition as it starts expanding into more enterprise-focused collaboration tools as it looks to woo businesses, which represent a substantial opportunity for growth for the company that started off as a dead-simple file-sharing service.

Previously an entrepreneur-in-residence for Greylock, Nash is now going to oversee a wide range of products that span consumer-focused file storage and sharing services all the way up to its Google Docs competitor Paper — each of which has a kind of consumer-born aesthetic that’s targeting use cases within enterprises, whether that’s building tools to get documents into its service or to actually helping teams spec out products within a kind of continuous document like Paper. But as it focuses on simplicity, Dropbox has to take care not to end up feature-creeping its way out of what made it successful initially, so the final product decisions may be a bit different. Naman will also inherit that challenge of marketing a consumer-oriented product that’s targeting businesses.

As Dropbox looks to continue to mature as a public company, it has to ensure that it still brings on talent that understands where it’s going now as it tries to wrangle larger enterprise customers that have a complex set of needs beyond just the typical consumer. Going public certainly helps with that credibility a little bit, but it’s hires like these that will determine what kinds of products actually make it out the door and the messaging that goes with them — and whether larger enterprises will actually adopt them.

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