Galera Cluster Schema Changes, Row Based Replication and Data Inconsistency

| Jan 24, 2018
Galera Cluster is a virtually synchronous multi-master replication plug-in. When using Galera Cluster application can write to any node and transactions are then applied to all serves via row-based replication events.

This is built-in Mysql row-based replication which supports replication with differing table definitions between Master and Slave.
So, when using row-based repplication source and target table do not have to be identical. A table on master can have more or fewer columns or use (more...)

Global variable wsrep_OSU_method=RSU ignored on MariaDB Galera Cluster 10.0.32

| Jan 20, 2018
Galera Cluster is great solution for application clustering but it has some limitations. One of the most critical limitations is how Galera Cluster performs model changes.

From Galera Cluster documentation:
What’s happening is a side effect of a multi-master cluster with several appliers. 
The cluster needs to control when a DDL statement ends in relation to other transactions,
in order to deterministically detect conflicts and schedule parallel appliers.
Effectively, the DDL statement (more...)

HASH GROUP BY not used when using more that 354 aggregate functions

| Nov 13, 2017
Few days ago we had performance problem with one of our main application views. This was complex view that used a lot of aggregate function. Functions were used to transpose rows into columns.

When developer added few more aggregate functions for a new columns, query performance changed significantly and we had performance problem.

After quick analysis I have noticed one change in the execution plan.

HASH GROUP BY aggregation was replaced with less performant SORT (more...)

Beware of intensive slow query logging when using – log_queries_not_using_indexes

| Oct 21, 2017
MySQL slow query log is great for identifying slow queries that are good candidates for optimisation. Slow query logging is disabled by default, but it is activated by DBA's or developers on most environments.

You can use slow query log to record all the traffic but be careful with this action as logging all traffic could be very I/O intensive and could have negative impact on general performance. It is recommended to record all traffic (more...)

Enable SSL-encryption for MariaDB Galera Cluster

| Oct 17, 2017
Imagine you have MariaDB Galera cluster with nodes running in different data centers. Data centers are not connected via secured VPN tunnel.
As database security is very important you must ensure that traffic between nodes is fully secured.

Galera Cluster supports encrypted connections between nodes using SSL protocol and in this post I want to show how to encrypt all cluster communication using SSL encryption.

Check current SSL configuration.

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';

Delete large amounts of data on Galera Cluster using pt-archiver

| Sep 28, 2017
Galera Cluster is excellent virtually synchronous multi-master database cluster. It has many benefits which you can check on GaleraCluster.
But beside benefits it has some limitations and one of them is handling large transactions.

Large replication data sets could degrade performance of whole cluster causing cluster freezing, increased memory consumption, crashing nodes, etc. To avoid this issues it is recommended to split large transactions into smaller chunks.

In this post I want to show you (more...)

Beware of ORA-19721 on 12c using Transportable Tablespace (Oracle changed behavior)

| Sep 16, 2017
Almost every big database has it's hot data which is used often, and cold data which is rarely touched. From version 9i I have used transportable tablespace feature to exclude cold (archive) data from database and keep it on cheap storage or tapes.

If someone needs to query some of archive tables it was very easy to plug in tablespace for few days and after archive data is not needed anymore tablespace could be easily (more...)

Using In-Memory Option with SQL Plan Baselines, SQL Profiles and SQL Hints

| Mar 6, 2017
Oracle database In-Memory option was introduced in patchset. It is great feature to improve performance of analytic queries. For mixed workload OLTP environments In-Memory option could improve performance of analytic queries without significant negative affect on quick OLTP queries or DML operations.

So you have decided that In-Memory option could be great for you and now you want to implement this option for your critical production database.

But in your code (more...)

Reduce Hard Parse time using SQL Profile

| Nov 3, 2016
Few days ago we had concurrency problem with "cursor: pin S wait on X" wait event. This wait event is mostly associated with parsing in some form.

After quick diagnosis I’ve found problematic query. It was fairly complex query which was executed very often with average 0.20 seconds of execution time. As this query was using bind variables, Oracle reused existing plan and problems with "cursor: pin S wait on X" wait (more...)

Using Adaptive Cursors Sharing with SQL Plan Baselines

| Jun 28, 2016
We have several databases where automatic capturing of sql plan baselines is enabled for a few schemas.

Execution of some queries deeply depend on variables where is not always the best to reuse same execution plan for all executions. For those queries I want to avoid using literals and inefficient execution plans. Also, I want to use SQL plan baselines as I have automatic capturing enabled.

Question is, can I make Adaptive Cursor Sharing to (more...)

Slow full table scan due to row chaining

| Feb 24, 2016
Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.

This was the statement:

select count(1)
from CLIENT k
where k.expires is null;

I've used fake names for table name and columns.
Database version:

Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)

Detecting Soft Corruption on 12c – V$NONLOGGED_BLOCK, ORA-01578/ORA-26040

| Feb 20, 2016
Last week we have created standby database in our dev environment and performed some ETL actions on primary side. Loading data or rebuilding indexes was performed with NOLOGGING option. After few days we noticed lots ORA-01578/ORA-26040 errors.
Corruption happened because we forgot to enable force logging.

As this was new dev database there wasn’t backup, but maybe not everything was lost. If only corrupted segments are indexes we could easily rebuild them.

Then I’ve learnt (more...)

Unindexed Foreign Keys on empty/unused table and locks

| Dec 17, 2015
It is widely known that unindexed foreign keys can be performance issue. Unindexed foreign keys on child tables can cause table locks or performance problems in general.
There are many articles on this subject so I won't go in details.

My plan is to show simple demo case where empty child table with unindexed foreign key column can cause big problems.

Imagine that you have highly active table (supplier) with lots DML operations from many (more...)

Confusion and problems with lost+found directory in MySQL/Galera cluster configuration

| Oct 7, 2015
The lost+found directory is filesystem directory created at root level of mounted drive for ext file systems. It is used by file system check tools (fsck) for file recoveries.

In MySql world it can cause confusion or possible problems with synchronisation in Galera cluster configuration.

Let’s check some examples.

I have MySQL database with datadir=/data in configuration file. I have deleted lost+found directory and restarted MySQL service.

When I list my databases this is result:


How to Pass Arguments to OS Shell Script from Oracle Database

| May 10, 2015
Imagine you have several Oracle databases on the same host under same OS user.

In scripts directory you have shell script that kills OS processes.
Idea is to call OS script from database procedure and kill problematic process using shell script.

Script will run simple query to get process id and kill that process.

But how to assure that this script will execute in correct environment for correct database?

One way is to create one (more...)

ASM not starting with ORA-00845 – how to fix ASM parameter file

| May 9, 2015
Few days ago I saw great post from Norman Dunbar on how to fix a broken ASM spfile.

With version 11gR2 ASM spfile can be stored in ASM diskgroup and by default Oracle Installer will put it there. So if you want to create pfile from spfile your ASM instance should be up and running.

If you have incorrect parameter in ASM spfile which is blocking ASM to start than you have slight problem. You (more...)

Restore to Restore Point on Standard Edition (no Flashback technology)

| Feb 28, 2015
Restore points and Flashback database are nice features introduced in 10g database that provide efficient point in time recovery to reverse unwanted data changes.

But what if you have Standard Edition database:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;
alter database flashback on
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

In Standard Edition you don’t have Flashback Database feature, but you can still create restore points and (more...)

MariaDB – Measure Replicaton Lag and Check / Fix Replication Inconsistencies using Percona tools

| Feb 5, 2015
Percona Toolkit is collection of command-line tools to perform many MySQL tasks like creating backups, finding duplicate indexes, managing replication, etc.

In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools:

I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.

Install Percona Toolkit on both nodes:

$ sudo wget

$ (more...)

MariaDB(MySQL) Master-Master Replication

| Feb 1, 2015
The simplest and probably most common replication method is master-slave replication. Basically, data is replicated from master database to the slave. In case of master database failure you must get the slave database up-to-date before failover and then promote slave to be new master.

Another method is to set up replication in both directions called master-master replication. But you must be aware that this setup brings some potential issues as data changes are happening on (more...)

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

| Dec 22, 2014
Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)