PostgreSQL Tech-Feed (en)
Artificially Delayed Replication with PostgreSQL
While working on our project titled “PostgreSQL for Dolphins and Sea Lions,” I pored over the PostgreSQL documentation on replication. What struck me was that PostgreSQL does NOT support artificially delayed replication—at least according to this documentation.
After I’d already been thinking about how one might possibly cobble together artificially delayed replication using PostgreSQL’s built-in tools, I decided to give my go-to search engine another try—and lo and behold, on the second attempt, it finally …
PostgreSQL Point-in-Time Recovery with Oops-Queries
While working on the topic “PostgreSQL for Dolphins and Sea Lions,” I discovered that in the PostgreSQL universe, the topic of point-in-time recovery tends to be sidestepped when it comes to specific statements or transactions. And when examples are provided, they primarily focus on DDL statements rather than DML statements.
If you want to recover to some previous point in time (say, right before the junior DBA dropped your main transaction table), just specify the required stopping point. You can specify …
Database Index Optimizer
Recently, a client asked me if the “time-consuming” task of checking indexes could be left to an index optimizer. Of course it can…
What exactly do we want to check?
- Tables without a Primary Key
- Duplicate indexes
- Partially redundant indexes
- Unused indexes
MariaDB, MySQL, and Percona Server
Tables without a Primary Key
SQL> SELECT DISTINCT t.table_schema, t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND …MariaDB/MySQL Environment MyEnv 3.0.0 has been released
FromDual has the pleasure to announce the release of the new version 3.0.0 of its popular MariaDB, MySQL and PostgreSQL multi-instance environment MyEnv.
The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.
In the inconceivable case that you find a bug in the MyEnv please report it to us by sending an email.
Any feedback, statements and testimonials are welcome as well! Please send them to us.
Upgrade from 2.x to 3.0
Please check the MyEnv Installation …
FromDual Performance Monitor 2.2.1 has been released
FromDual has the pleasure to announce the release of the new version 2.2.1 of its popular Database Performance Monitor for MariaDB, Galera Cluster, MySQL and PostgreSQL fpmmm.
The FromDual Performance Monitor enables Database and System Administrators to monitor and understand what is going on inside their databases and on the machines where the databases reside.
More information you can find here: FromDual Performance Monitor.
Download
The new FromDual Performance Monitor can be downloaded from our Sofware …
What is the quickest way to load data into the database?
We had some really exciting problems to solve for the last customer! Especially because the database wasn’t exactly small.
Here are some key data: CPU: 2 sockets x 24 cores x 2 threads = 96 vCores, 756 G RAM, 2 x 10 Tbyte PCIe SSD in RAID-10 and 7 Tbyte data, several thousand clients, rapidly growing.
The current throughput: 1 M SELECT/min, 56 k INSERT/min, 44 k UPDATE/min, 7 k DELETE/min averaged over 30 days. With a strong upward trend. Application and queries not consistently optimised. Database …
How much space does NULL need?
The last time I consulted a customer, he came up to me beaming with joy and said that he had taken my advice and changed all the primary key columns from BIGINT (8 bytes) to INT (4 bytes) and that had made a big difference! His MySQL 8.4 database is now 750 Gbyte smaller (from 5.5 Tbyte). Nice!
And yes, I know that contradicts the recommendations of some of my PostgreSQL colleagues (here and here). In the MySQL world, more emphasis is placed on such things (source):
Use the most efficient (smallest) data …
Someone is deleting my shared memory segments!
When we work with PostgreSQL under our myEnv, we regularly get shared memory segment errors. Example:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5433" failed:
FATAL: could not open shared memory segment "/PostgreSQL.4220847662":
No such file or directory
or we see similar messages in the PostgreSQL error log:
ERROR: could not open shared memory segment "/PostgreSQL.4220847662":
No such file or directory
Because I am a MariaDB/MySQL admin, I am not very familiar with …
Load CSV files into the database
Recently, I wanted to display the places of residence of the members of my club on a map for a personal gimmick (IGOC members). I knew the addresses of the club members. But not the coordinates of their places of residence.
So I went in search of the coordinates and found what I was looking for at the Federal Office of Topography (swisstopo).
The data is available there as a CSV file. Details here: Swiss town coordinates.
How do I load this data into a database?
Loading the data with MariaDB/MySQL
MariaDB …
MariaDB/MySQL Environment MyEnv 2.1.1 has been released
FromDual has the pleasure to announce the release of the new version 2.1.1 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.
The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.
In the inconceivable case that you find a bug in MyEnv please report it to us by sending an email.
Any feedback, statements and testimonials are welcome as well! Please send them to us.
Upgrade from 1.1.x to 2.0
Please look at the MyEnv 2.0.0 …
