Infrastructure at your Service

SQL Archives - Page 2 of 3 - Blog dbi services

Daniel Westermann

Parallel sequential scans are coming to PostgreSQL

By | Database Administration & Monitoring | No Comments

It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up: max_parallel_degree: The maximum degree of parallelism for an individual parallel operation. parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a parallel worker process to another process. The default is 0.1 parallel_setup_cost: Sets the planner’s estimate…

Read More
Daniel Westermann

Monitoring tools for PostgreSQL – POWA

By | Database Administration & Monitoring, Database management | No Comments

The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the file system layer pg_qualstats: gathers statistics of predicates found in where statements and join clauses btree_gist: provides GiST index operator…

Read More
Daniel Westermann

Monitoring tools for PostgreSQL – pgcluu

By | Database Administration & Monitoring | No Comments

The last posts introduced the logging system, pg_stat_statements and pg_activity. All of these can be used to monitor sql statements the PostgreSQL server is executing. In this post I’ll look into pgcluu: PostgreSQL Cluster utilization! This is a more complete monitoring solution as it is not only focused on sql statements but gives you information about the database cluster itself and other useful stuff. All you need to run pgcluu is a modern perl distribution…

Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (3) – pg_activity

By | Database Administration & Monitoring | 7 Comments

The last posts looked at how the logging system and the pg_stat_statements extension can be used to monitor sql statements in PostgreSQL. This post will introduce pg_activity which is very similar to htop. There are some dependencies which need to be installed before we can start installing pg_activity. The first one is python. As I am on a redhat based distribution this is quite easy: [root@oel7 ~] yum install -y python Then we need to…

Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (2) – pg_stat_statements

By | Database Administration & Monitoring | No Comments

The last post looked into how you can monitor queries using the logging system. This post will introduce pg_stat_statements. pg_stat_statements is a module that needs to be loaded and is not available in the default configuration. Loading it is quite easy. Create the extension as usual: postgres@oel7:/home/postgres/ [PG6] sqh Null display is “NULL”. Timing is on. psql (9.5alpha2) Type “help” for help. (postgres@[local]:4448) [postgres] > create extension pg_stat_statements; CREATE EXTENSION Time: 281.765 ms (postgres@[local]:4448) [postgres]…

Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (1) – the logging system

By | Database Administration & Monitoring | No Comments

When developing an application as well as when the application is in production there is the need to identify long running queries. In Oracle one tool you might use for that is the SQL Monitor. In this post I’ll look into what PostgreSQL provides in this area. PostgreSQL has a very strong logging system. This system can be used to log many, many server messages as well as information about sql queries. To enable to…

Read More
Morgan Patou

Alfresco: some useful database queries

By | Application integration & Middleware, Entreprise content management | 30 Comments

In my previous post, I talked about the Lifecycle of Alfresco Nodes. You may have noticed that I tried to insert in my explanations some elements that are specific to databases (tables, fields, aso…). These elements are quite essential to prepare a post like this one: more database oriented. I already explained what exactly are the consequences on the database side when a node is removed and I will try in this post to share some useful…

Read More
Stéphane Haby

Azure DocumentD8: Microsoft goes to NoSQL

By | Technology Survey | No Comments

Azure DocumentDB is a documentary database service and as like its name suggests, it is aMicrosoft Azure Cloud service. Since the last summer, developers have access to this new service, and now it is available for all. The Redmond Company offers for the first time a “NoSQL” database. This is not the only service that has come; it is associated to Azure Search, a full text search service.   Why NoSQL? Is first and foremost…

Read More