postgresql

Alexey Lesovsky: pgCenter - stress free Postgres stats.

planet postgresql - 2018-10-02(火) 23:16:00
pgCenter has seen a new release with several significant changes. If you used pgCenter before, you might have noticed that I stopped issuing updates for a while. This is because I was working on this new release with several new features and overall improvements that will make the use of the tool easier and will allow additional functionality to make your life easier when working with Postgres stats.



This new version has been completely rewritten using Golang.

Why Go? To me, Golang is much simpler than pure C, but, at the same time, it is an amazing and powerful language. I had a few requests that I was aiming to address in this release and frankly, it was much easier to implement these using Golang.

With this release pgCenter became quite comprehensive toolbox for working with Postgres stats.

In this 0.5.0 version I’ve added two new key tools - for recording stats into history files and building reports based on those files. People who are familiar with pgCenter, often asked me about these features, and now they are available and ready to use.

Additional features
There are also a few additional minor changes that were made in this version of pgCenter:
  • pgCenter’s functionality is now split into subcommands and looks like Git or Perf style. pgCenter obtained new functions that have different aims and distributed across several sub-commands with different execution options and parameters.
  • pgCenter uses lib/pq library for handling the connections to Postgres, and one of the advantages is the built-in support of .pgpass file and environment variables. And the cherry on the cake is that you can override connections settings using PGOPTIONS variable.
pgCenter top changes
As mentioned earlier, pgCenter split into several sub-commands, the “top” is now a dedicated sub-command, so in a new version it has seen some improvements:
  • Activity statistics are shown by default. In previous versions, database statistics were default, but majority of pgCenter users often switched to activity stats, so in this version act
[...]
カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Foreign Key to partitioned table

planet postgresql - 2018-10-02(火) 18:02:48
One of the long standing limitations of partitions is that you can't have foreign keys pointing to them. Let's see if I can make it possible to have some kind of constraint that would do the same thing as fkey. First, obviously, we need partitioned table with some data: =$ CREATE TABLE USERS ( id […]
カテゴリー: postgresql

Don Seiler: Sequence Caching: Oracle vs. PostgreSQL

planet postgresql - 2018-10-02(火) 14:00:00

Many RDBMSes use sequence caching to pre-generate sequence values in advance and store them in memory, allowing them to return new values quickly. If you are doing a lot of inserts that each call the sequence to get the next value, sequence caching is a good thing. Having to wait for the sequence to generate a value on every call could slow things down greatly.

When I made the move from Oracle to PostgreSQL, I noticed some interesting differences in sequence caching behavior and wanted to share them here for those that may be curious about them.

All examples below were conducted in Oracle 12.2.0.1 (12c Enterprise Edition) and PostgreSQL 9.6.9 (via PGDG Yum Repository).

Cache Value Sharing

In Oracle, when a sequence cache is generated, all sessions access the same cache. However in PostgreSQL, each session gets its own cache. We demonstrate this with a couple of quick-and-easy examples below.

Oracle

First let’s create our sequence, using a cache size of 25.

SQL> CREATE SEQUENCE dts_seq 2 INCREMENT BY 1 3 CACHE 25; Sequence created.

Now I can open a couple of sessions and call nextval on that sequence to increment it from two different sessions:

-- First session SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 1 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 2 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 3 -- Second session SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 4 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 5 SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 6

It’s even more clear when alternating between the two sessions:

-- Session A SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 151 -- Session B SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 152 -- Session A SQL> SELECT dts_seq.nextval FROM dual; NEXTVAL ---------- 153 -- Session B SQL> SELECT dts_seq.nextval FROM dual[...]
カテゴリー: postgresql

Bruce Momjian: Switchover/Failover and Session Migration

planet postgresql - 2018-10-02(火) 07:45:01

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to the new primary. If there is a switchover, there are options for client migration. Assuming you are using streaming replication, only one server can accept writes. Therefore, for client migration, you can either:

  • Force all clients on the old primary to exit, then promote the new primary
  • Wait for all clients on the old primary to exit, then promote the new primary

If you choose force, it will disrupt applications; they must be designed to handle disconnections and potentially reconfigure their sessions, e.g., session variables, cursors, open transactions. If you choose wait, how do you handle clients that want to connect while you are waiting for the existing clients to disconnect? Minor upgrades, which require a restart of the database server, have similar issues.

The only clean solution is to use multi-master replication so new clients can connect to the new primary while waiting for old-primary clients to finish and disconnect. However, it is operationally expensive to support multi-master just to minimize switchover disruption.

カテゴリー: postgresql

Keith Fiske: Per-Table Autovacuum Tuning

planet postgresql - 2018-10-02(火) 03:43:06

A pattern that seems to drive my blog posts definitely seems to be the frequency of client questions. And that is definitely the case here again. Vacuum tuning to manage bloat and transaction id wraparound on production systems has been a hot topic and lately this has even been getting down to tuning autovacuum on the individual table basis. I’ve already discussed bloat pretty extensively in previous posts. While I’d like to get into the details of transaction ID wraparound, that really isn’t the focus of this post, so I’ll defer you to the documentation.

One setting I will discuss first though is autovacuum_freeze_max_age. Basically when any table’s max XID value reaches this, a more aggressive “emergency” autovacuum is kicked off. If many tables hit this at the same time (a common occurrence with data-warehouses that have many large, sparsely written tables), that can kick off some pretty high and long lasting IO caused by these autovacuums. I highly recommend increasing autovacuum_freeze_max_age from the default value of 200 million to 1 billion. However, I don’t recommend doing this unless you have some monitoring in place to watch for tables reaching both autovacuum_freeze_max_age and wraparound, the latter of which can shut your database down for some pretty extensive downtime.

So ideally, we want autovacuum running often enough on its own so you never encounter any of those bad situations above. The next thing I’d recommend tuning on any database before getting down to the table-level methods, no matter its size or traffic, are the default settings that control when autovacuum initially kicks in.

autovacuum_analyze_scale_factor = .10 autovacuum_analyze_threshold = 50 autovacuum_vacuum_scale_factor = .20 autovacuum_vacuum_threshold = 50

In my opinion, the defaults on these are not ideal for any situation. The scales are too high and the thresholds are too low. The scale factor settings are percentage values that say, “When this percentage of the table’s rows have changed (updates/deletes), run vacuum or ana

[...]
カテゴリー: postgresql

Andreas 'ads' Scherbaum: PGConf.EU 2018 - Speaker Interviews

planet postgresql - 2018-10-01(月) 19:00:00

Like in previous years, we conduct interviews with our speakers ahead of this year's PostgreSQL Conference Europe.

The first interviews are already online: pgconf.eu 2018 - Speaker Interviews

If you are speaking in Lisbon, and haven't already submitted your answers, please check your mailbox. There should be an email asking you if you want to participate.

カテゴリー: postgresql

Paul Ramsey: PostGIS Code Sprint 2018 #2

planet postgresql - 2018-10-01(月) 17:00:00

An important topic of conversation this sprint was what kinds of core PostgreSQL features might make PostGIS better in the future?

Parallel GIST Scan

The PostGIS spatial index is built using the PostgreSQL GIST index infrastructure, so anything that makes GIST scans faster is a win for us. This would be a big win for folks with large tables (and thus deep trees) and who run scans that return a lot of indexed tuples.

Faster GIST Index Building

B-Tree index builds are accellerated by pre-sorting the inputs; could the same trick be used in building GIST indexes? Again, for large tables, GIST index building is slower than B-Tree and “faster” is the #1 feature all existing users want.

Multi-Threading in Functions

This isn’t a feature request, so much as a request for clarification and assurance: PostGIS calls out to other libraries, like GEOS, and it’s possible we could make some of our algorithms there faster via parallel processing. If we do our parallel processing within a function call, so the PostgreSQL thread of execution isn’t doing anything until we return, is it OK for us to do threading? We use pthreads, or maybe OpenMP.

Compressed Datum Slicing

“Detoast datum slice” doesn’t actually get around to the slicing step until after the datum is decompressed, which can make some queries quite slow. We already try to read boxes from the headers of our objects, and for large objects that means decompressing the whole thing: it would be nice to only decompress the first few bytes. I have an ugly patch I will be testing to try and get committed.

Forced Inlining

A problem we have with PostgreSQL right now is that we cannot effectively cost our functions due to the current inlining behaviour on our wrapper functions like ST_Intersects(). When raised on the list, the hackers came to a tentative conclusion that improving the value caching behaviour would be a good way to avoid having inlining in incorrect places. It sounded like subtle and difficult work, and nobody jumped to it.

We propose leaving the current inlining

[...]
カテゴリー: postgresql

Paul Ramsey: PostGIS Code Sprint 2018 #1

planet postgresql - 2018-09-30(日) 17:00:00

When I tell people I am heading to an open source “code sprint”, which I try to do at least once a year, they ask me “what do you do there?”

When I tell them, “talk, mostly”, they are usually disappointed. There’s a picture, which is not unearned, of programmers bent over their laptops, quietly tapping away. And that happens, but the real value, even when there is lots of tapping, is in the high-bandwidth, face-to-face communication.

So, inevitably I will be asked what I coded, this week at the PostGIS Code Sprint and I will answer… “uhhhhh”. I did a branch of PostgreSQL that will do partial decompression of compressed tuples, but didn’t get around to testing it. I tested some work that others had done. But mostly, we talked.

PostGIS 3

Why move to PostGIS 3 for the next release? Not necessarily because we will have any earth-shattering features, but to carry out a number of larger changes. Unlike most PostgreSQL extensions, PostGIS has a lot of legacy from past releases and has added, removed and renamed functions over time. These things are disruptive, and we’d like to do some of the known disruptive things at one time.

Split Vector and Raster

When we brought raster into PostGIS, we included it in the “postgis” extension, so if you CREATE EXTENSION postgis you get both vector and raster features. The rationale was that if we left it optional, packagers wouldn’t build it, and thus most people wouldn’t have access to the functionality, so it wouldn’t get used, so we’d be maintaining unused garbage code.

Even being included in the extension, by and large people haven’t used it much, and the demand from packagers and other users to have a “thin” PostGIS with only vector functionality have finally prevailed: when you ALTER EXTENSION postgis UPDATE TO '3.0.0' the raster functions will be unbundled from the extension. They can then be re-bundled into a “postgis_raster” dependent package and either dropped or kept around depending on user preference.

Remove postgis.so Minor Version

For users in production, working with

[...]
カテゴリー: postgresql

Jobin Augustine: High Availability for Enterprise-Grade PostgreSQL environments

planet postgresql - 2018-09-29(土) 02:09:33

High availability (HA) and database replication is a major topic of discussion for database technologists. There are a number of informed choices to be made to optimize PostgreSQL replication so that you achieve HA. In this post we introduce an overview of the topic, cover some options available to achieve high availability in PostgreSQL. We’ll then focus in on just one way to implement HA for postgres, using Patroni.  

In our previous blog posts, we have discussed the features available to build a secured PostgreSQL environment and the tools available to help you set up a reliable backup strategy. The series of articles is designed to provide a flavor of how you might go about building an enterprise-grade PostgreSQL environment using open source tools. If you’d like to see this implemented, then please do check our our webinar presentation of October 10 – we think you might find it both useful and intriguing! 

Replication in PostgreSQL

The first step towards achieving high availability is making sure you don’t rely on a single database server: your data should be replicated to at least one standby replica/slave. Database replication can be done using the two options available with PostgreSQL community software:

  1. Streaming replication
  2. Logical replication & logical decoding

When we setup streaming replication, a standby replica connects to the master (primary) and streams WAL records from it. Streaming replication is considered to be one of the safest and fastest methods for replication in PostgreSQL. A standby server becomes an exact replica of the primary with potentially minimal lag between primary and standby even on very busy transactional servers. PostgreSQL allows you to build synchronous and asynchronous replication while in streaming replication. Synchronous replication ensures that a client is given a success message only when the change is not only committed to the master but also successfully replicated on the standby server as well. As standby servers can accept read requests from clients, we can ma

[...]
カテゴリー: postgresql

Achilleas Mantzios: The “O” in ORDBMS: PostgreSQL Inheritance

planet postgresql - 2018-09-28(金) 18:58:00

In this blog entry we’ll go through PostgreSQL inheritance, traditionally one of PostgreSQL’s top features since the early releases. Some typical uses of inheritance in PostgreSQL are:

  • table partitioning
  • multi-tenancy

PostgreSQL until version 10 implemented table partitioning using inheritance. PostgreSQL 10 provides a new way of declarative partitioning. PostgreSQL partitioning using inheritance is a pretty mature technology, well documented and tested, however inheritance in PostgreSQL from a data model perspective is (in my opinion) not so widespread, therefore we’ll concentrate on more classic use cases in this blog. We saw from the previous blog (multi-tenancy options for PostgreSQL) that one of the methods to achieve multi-tenancy is to use separate tables and then consolidate them via a view. We also saw the drawbacks of this design. In this blog we will enhance this design using inheritance.

Introduction to Inheritance

Looking back at the multi-tenancy method implemented with separates tables and views we recall that its major disadvantage is the inability to do inserts/updates/deletes. The moment we try an update on the rental view we’ll get this ERROR:

ERROR: cannot insert into view "rental" DETAIL: Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

So, we would need to create a trigger or a rule on the rental view specifying a function to handle the insert/update/delete. The alternative is to use inheritance. Let us change the schema of the previous blog:

template1=# create database rentaldb_hier; template1=# \c rentaldb_hier rentaldb_hier=# create schema boats; rentaldb_hier=# create schema cars;

Now let’s create the main parent table:

rentaldb_hier=# CREATE TABLE rental ( id integer NOT NULL, customerid integer NOT NULL, vehicleno text, datestart date NOT NULL, dateend date );

In OO terms this table corresponds to the super

[...]
カテゴリー: postgresql

Paul Ramsey: 5x Faster Spatial Join with this One Weird Trick

planet postgresql - 2018-09-28(金) 17:00:00

My go-to performance test for PostGIS is the point-in-polygon spatial join: given a collection of polygons of variables sizes and a collection of points, count up how many points are within each polygon. It’s a nice way of testing indexing, point-in-polygon calculations and general overhead.

Setup

First download some polygons and some points.

Load the shapes into your database.

shp2pgsql -s 4326 -D -I ne_10m_admin_0_countries.shp countries | psql performance shp2pgsql -s 4326 -D -I ne_10m_populated_places.shp places | psql performance

Now we are ready with 255 countries and 7343 places.

One thing to note about the countries is that they are quite large objects, with 149 of them having enough vertices to be stored in TOAST tuples.

SELECT count(*) FROM countries WHERE ST_NPoints(geom) > (8192 / 16); Baseline Performance

Now we can run the baseline performance test.

SELECT count(*), c.name FROM countries c JOIN places p ON ST_Intersects(c.geom, p.geom) GROUP BY c.name;

On my laptop, this query takes 25 seconds.

If you stick the process into a profiler while running it you’ll find that over 20 of those seconds are spent in the pglz_decompress function. Not doing spatial algorithms or computational geometry, just decompressing the geometry before handing it on to the actual processing.

Among the things we talked about this week at our PostGIS code sprint have been clever ways to avoid this overhead:

  • Patch PostgreSQL to allow partial decompression of geometries.
  • Enrich our serialization formation to include a unique hash key at the front of geometries.

These are cool have-your-cake-and-eat-too ways to both retain compression for large geometries and be faster when feeding them into the point-in-polygon machinery.

However, they ignore a more brutal and easily testable approach to avoiding decompression: just don’t compress in the first place.

One Weird Trick

PostGIS uses the “main” storage option for it’s geometry type. The main option tries to keep ge

[...]
カテゴリー: postgresql

Álvaro Herrera: PostgreSQL 11: Patch Reviewers for Partitioning Patches

planet postgresql - 2018-09-28(金) 15:06:42

We seldom credit patch reviewers. I decided to pay a little homage to those silent heroes for a few of them: here’s the list of people who were credited as having reviewed the patches mentioned in my previous article for PostgreSQL 11. The number in front is the number of times they were credited as reviewers.

7 Amit Langote, Robert Haas 5 Dilip Kumar, Jesper Pedersen, Rajkumar Raghuwanshi 4 Peter Eisentraut 3 Amul Sul, David Rowley, Rafia Sabih, Simon Riggs, Thomas Munro 2 Antonin Houska, Ashutosh Bapat, Kyotaro Horiguchi 1 Álvaro Herrera, Amit Kapila, Amit Khandekar, Etsuro Fujita, Jaime Casanova, Keith Fiske, Konstantin Knizhnik, Pascal Legrand, Pavan Deolasee, Rajkumar Raghuanshi, Rushabh Lathia, Sven Kunze, Thom Brown, Yugo Nagata
カテゴリー: postgresql

Gary Sahota: Fun with SQL: Unions in Postgres

planet postgresql - 2018-09-28(金) 02:40:00

Before joining the Citus Data team to work on the Postgres extension that transforms Postgres into a distributed database, I checked out the Citus Data blog to learn a bit more. And found all sorts of material for Postgres beginners as well as power users, with one of my favorites being the “Fun with SQL” series. After I joined, I jumped at the chance to write a Fun with SQL post, and the only question was what to write about. I chose unions.

In SQL, the UNION and UNION ALL operators help take multiple tables and combine the results into a single table of all matching columns. This operator is extremely useful if you want the results to come back as a single set of records.

You can use UNION to enhance your SQL queries. In past posts we’ve also covered topics like common functions in Postgres, window functions in Postgres and CTEs (common table expressions) in Postgres.

When do you Union in Postgres?

The UNION operator is often used when multiple tables have the same structure but have been split for some reason (usually for performance considerations or archival purposes).

Before we begin, some basic requirements you should know are:

  • All individual queries must have the same number of columns and compatible data types.
  • Mismatched queries will not execute.
  • Validating consistency when building your query is important, so there are no surprises after.
  • By default, UNION only returns distinct values.

One example might be movies where you have a different table for each year of releases. Or if you have different types of users and email address, such as within a CRM like Copper where you have leads and contacts. Your leads table may look like:

leads | FirstName | LastName | Email | ----------------+---------------+------------------------ | Jennifer | Matthews | jennifer@acmecorp.com | | Tom | Jones | tom@acmecorp.com | | Daniel | Farina | farina@citusdata.com | contacts | FirstName | LastName | Email | ---------[...]
カテゴリー: postgresql

Magnus Hagander: Nordic PGDay 2019 - Location announced and call for papers open

planet postgresql - 2018-09-27(木) 21:33:11

Nordic PGDay 2019 will be returning to Copenhagen, Denmark on March 19, 2019. This year, the conference is held right on the waterfront in the Copenhagen Marriott Hotel.

Our call for papers is now open, accepting proposals until the end of the year. We are looking for speakers both from the Nordic region, the rest of Europe, and the whole world. Technical details, case studies, good ideas or bad ideas -- all are good ideas for topics. All speakers get free entrance, so it's also a good excuse to come visit Denmark!

Registration and call for sponsors will open shortly!

カテゴリー: postgresql

第 153 回理事会議事録 (2018-09)

www.postgresql.jp news - 2018-09-27(木) 19:04:35
第 153 回理事会議事録 (2018-09) anzai 2018/09/27 (木) - 19:04
カテゴリー: postgresql

Joshua Otwell: Understanding Check Constraints in PostgreSQL

planet postgresql - 2018-09-27(木) 18:43:48

Managing data is a big challenge. As our world turns, data continues to be widespread, abundant, and intensive. Therefore, we must take measures to handle the influx.

Validating every single piece of data 'by hand' around the clock is simply impractical. What a fantastic dream. But, after all, it is just that. A dream. Bad data is bad data. No matter how you slice it or dice it (pun intended). It is a problem from the onset, leading to even more issues.

Modern databases handle much of the heavy lifting for us. Many provide built-in solutions to assist in managing this particular area of data.

A sure way to control the data entered into a table's column is with a data type. Need a column with decimal numbers, having a total digit count of 4, with 2 of those after the decimal?

Sure thing! No problem at all.

NUMERIC(4,2), a viable option, is guarding that column like a watchdog. Can character text values slip in there? Not a snowball's chance.

PostgreSQL offers a multitude of data types. Chances are, one already exists to satisfy your need(s). If not, you can create your own. (See: PostgreSQL CREATE TYPE)

Yet, data types alone are not enough. You cannot assure the most specific requirements are covered and conform to such broad structuring. Compliance rules and some sort of 'standard' are typically required when designing a schema.

Suppose in that same NUMERIC(4,2) column, you only want values greater than 25.25 but less than 74.33? In the event, value 88.22 is stored, the data type is not at fault. By allowing 4 total digits, with 2 at most after the decimal, it is doing its job. Lay the blame elsewhere.

How do we win on this front when it comes to controlling the data allowed in our database? Data consistency is of utmost priority and is integral for any sound data solution. On the (off) chance you controlled the collected data from the onset of its origination source, consistency would likely be less of an issue.

But, a perfect world only exists (maybe) in one of those many fantasy novels I love to read.

Unfort

[...]
カテゴリー: postgresql

Christophe Pettus: Find the value of all database sequences

planet postgresql - 2018-09-27(木) 03:06:42

Upon occasion, you want to get the current value of all the sequences in the database. For example, you may have fields that are integer rather than bigint, and you’re concerned how close you are to overflowing one of them (since sequences are bigint and will happily crash through the size of a 32-bit integer).

Unfortunately, currval() requires that you have actually accessed the sequence value using nextval() in the current session… but you don’t want to increment the value, just test it!

Here’s a cheap and cheerful PL/pgSQL function that returns all of the current sequence values:

CREATE OR REPLACE FUNCTION sequence_values() RETURNS TABLE(name text, value bigint) AS $sequence_values$ DECLARE nsp_name TEXT; seq_name TEXT; BEGIN FOR nsp_name, seq_name IN SELECT nspname::text, relname::text FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE relkind='S' LOOP RETURN QUERY EXECUTE 'SELECT ''' || nsp_name || '.' || seq_name || '''::text, last_value FROM "' || nsp_name || '"."' || seq_name || '"'; END LOOP; END; $sequence_values$ LANGUAGE plpgsql;
カテゴリー: postgresql

Dan Langille: Adding and populating CONFLICTS

planet postgresql - 2018-09-26(水) 21:49:51
This post serves two goals: document the CONFLICTS implementation within FreshPorts show how you can use triggers to update a secondary table The FreeBSD ports tree / package collection has a method for resolving conflicts. This feature allows a port maintainer to specify what, if any, packages conflict with a given port. The reasons why [...]
カテゴリー: postgresql

Dimitri Fontaine: A history lesson on SQL joins (in Postgres)

planet postgresql - 2018-09-26(水) 03:41:00

Our beloved Structured Query Language may be the lingua franca for relational databases—but like many languages, SQL is in a state of constant evolution. The first releases of SQL didn’t even have a notation for joins. At the time, SQL only supported inner joins.

Cross Joins and Where Filters

As a result, back in early eighties, the only way to express a join condition between tables would be in the WHERE clause.

select companies.name, campaigns.name, campaigns.monthly_budget from companies, campaigns where companies.id = campaigns.company_id and company_id = 5;

In that query, the PostgreSQL planner realizes that we are doing an INNER JOIN in between the companies table and the campaigns table, even though what we are actually writing is a CROSS JOIN in between the tables. In PostgreSQL documentation chapter Table Expressions we can read more about the CROSS JOIN operation:

For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in T1 followed by all columns in T2. If the tables have N and M rows respectively, the joined table will have N * M rows.

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

In my opinion, it is so rare that what you want to achieve actually is a cross join that cross joins should fail your SQL unit testing. After all, code should always express the intentions of the programmer, and if your intention is not to implement a CROSS JOIN then using a comma separated list of tables in your FROM clause looks deadly suspicious.

Inner And Outer Joins in SQL

The previous SQL query can be written properly with an inner join syntax:

select companies.name, campaigns.name, campaigns.monthly_budget from companies inner join campaigns on companies.id = campaigns.company_id where company_id = 5;

Lucky for us, the PostgreSQL planner is smart enough to realize that both queries are specifying the same res

[...]
カテゴリー: postgresql

Pavel Trukhanov: USE, RED and real world PgBouncer monitoring

planet postgresql - 2018-09-26(水) 02:57:45

Brendan Gregg’s USE (Utilization, Saturation, Errors) method for monitoring is quite known. There are even some monitoring dashboard templates shared on the Internet. There’s also Tom Wilkie’s RED (Rate, Errors, Durations) method, which is suggested to be better suited to monitor microservices than USE.

We, at okmeter.io, recently updated our PgBouncer monitoring plugin and while doing that we’ve tried to comb everything and we used USE and RED as frameworks to do so.

Why we needed both and not just stuck with USE, as it is more commonly known? To answer that we need to understand their applicability first. While they are know, I don’t think they are widely systematically applied in practice of covering IT-systems with monitoring.

USE

Using Brendan Gregg’s own words:

For every resource, check utilization, saturation, and errors.

Where resource is all and any physical server functional component (CPUs, disks, busses, …). But also some software resources as well, or software imposed limits/resource controls (containers, cgroups, etc).

Utilization: the average time that the resource was busy servicing work. So CPU utilization or disk IO utilisation of 90% means that it is idle, not doing work only 10% of the time, and busy 90% of time. But also, for such resources as memory, where one can’t apply the idea of “non idle percentage of time”, one could measure the proportion of a resource that is used.

Anyways, 100% utilization means no more “work” can be accepted, either at all, i.e. when memory is full, it is full, you can’t do anything about it. Or it’s 100% utilized only now, at the moment (as with CPU), and new work could be put into a waiting list, queue or something. And these two scenarios are covered by the corresponding remaining two USE metrics:

Saturation: the degree to which the resource has extra work which it can’t service, often queued.

Errors: the count of error events, i.e. such as “resource is busy”, “Cannot allocate memory”, “Not enough space”. While those usually do not produce performance impact

[...]
カテゴリー: postgresql

ページ