Infrastructure at your Service
By Franck Pachot . Delphix is a tool for easy cloning of databases. The idea is that all is automated: the user can create a clone, rewind or refresh it with one click. However, I was suprised that the following common scenario is not managed by the Delphix engine: You clone from production, say Oracle 12c You upgrade the clone, say Oracle 19c You test there You refresh the clone from production, obviously being back…
By Franck Pachot . With Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,… How would you do it in PostgreSQL? I’m taking an example from a Yugabyte slack question, as Yugabyte SQL layer is the PostgreSQL one. CREATE TABLE employees ( employee_no integer PRIMARY KEY, name text,…
By Franck Pachot . This is a little demo, easy to copy-paste, if you want to play with Kubernetes on your laptop. And, not a simple “Hello World” but a real database running here, able to scale up and down with full availability. Install Virtualbox I use Oracle VirtuaBox because I’m a big fan of Oracle products, especially when they are good and free. However, you can use Hyper-V (then just skip this paragraph and…
By Franck Pachot . Should CPU-intensive logic be done in the DB or in application server? Here was a answer found in Reddit: Comment from discussion xpiv’s comment from discussion "Should CPU-intensive logic be done in the DB or in application server?". Reducing the CPU usage on the database server is a major cost saver with commercial databases, like Oracle Enterprise Edition where you pay license per core. But even in Open Source, on-premises or…
By Franck Pachot . In the NoSQL times, it was common to hear thinks like “SQL is bad”, “joins are bad”, “foreign keys are bad”. Just because people didn’t know how to use them, or they were running on a database system with a poor implementation of it. MySQL was very popular because easy to install, but lacking on many optimization features that you find in other open source or commercial databases. Sometimes, I even…
By Franck Pachot . In the previous post I have run PostgreSQL on AWS m6gd.2xlarge (ARM Graviton2 processor). I didn’t precise the compilation option and this post will give more details following this feedback: @FranckPachot thanks for sharing the results. Our own testing showed similar perf when using the default gcc7. However, with newer compiler and use of LSE, the graviton2 would provide higher performance : https://t.co/x9OgFqiPPy — NB (@N_B__N_B) March 9, 2021 First, the…
By Franck Pachot . On the AWS free tier, you can run a t2.micro instance for 750 hours per month during the first 12 month after sign-up date. And currently, until June 2021, you can also run a T4g.micro. But be careful, when the free trial ends, or if your usage exceeds the free trial restrictions, you’ll pay the standard pay-as-you-go rates. This is a good occasion to test the Graviton2 ARM processors, and you…
By Franck Pachot . In a recent tweet I wanted to highlight the importance of knowing what you measure with pgbench, because looking at “Transactions per second” without knowing if you are in shared buffer cache hits, or filesystem cache hit, or storage cache hit, or physical read… is just meaningless: https://twitter.com/FranckPachot/status/1358761092307169282?s=20 The “scale” with the default pgbench tables stores about 15MB per scale value, mostly in the pgbench_accounts table and indexes. The default select-only…
This Blog is about some findings on Microsoft’s new Flexible Server offering for PostgreSQL which is in Customer Preview at the moment. Findings about the infrastructure The Microsoft Flexible Server for PostgreSQL is using Ubuntu as OS infrastructure, I think it is worth to be noticed that Microsoft’s new offering is switching from Windows to Linux. postgres=> select version(); version —————————————————————————————————————– PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit Ubuntu upgrade…
Patroni is one the most famous tool that can be used to setup a high avalaibilty for PostgreSQL. In this blog I am describing a test I did. Below the environment we will use The etcd cluster will contains 3 servers etcd-server1: 192.168.56.100 etcd-server2: 192.168.56.101 etcd-server3: 192.168.56.106 HAProxy and Keepalived will be configured in this 2 servers. haproxy-server1: 192.168.56.102 haproxy-server2: 192.168.56.103 A VIP will be configured haproxyvip 192.168.56.108 We have 2 databases servers pgserver1: 192.168.56.104…
