Using Elastic Stack, Filebeat and Logstash (for log aggregation)

Uncategorized
| Nov 10, 2019

In a previous article I described how I used ElasticSearch, Filebeat and Kibana, for log aggregation (getting log information available at a centralized location).
[https://technology.amis.nl/2019/09/15/using-elastic-stack-filebeat-for-log-aggregation/]

In this article I will talk about the installation and use of Filebeat in combination with Logstash (from the Elastic Stack).

EFK

One popular centralized logging solution is the Elasticsearch, Fluentd, and Kibana (EFK) stack.

Fluentd

Fluentd is an open source data collector, which lets you unify (more...)

Mysql Data Types and Sizes for String/ Text

Uncategorized
| Oct 2, 2019
MySQL or MariaDB has several data types for handling text-based characters. There are several data types for handling smaller strings such as CHAR and VARCHAR data types. For larger text-based strings there are BLOB based data types such as TEXT. It’s worth noting at this point that the below-quoted sizes do not necessarily represent the

Docker Compose yml for WordPress

Uncategorized
| Sep 6, 2019
Create a new directory and save the above file inside it as docker-compose.yml Run docker-compose up -d to fetch the images from the docker hub and create your wordpress instance. You’ll be able to access wordpress from a browser on port 8000.

When AWS SCT Unable to Connect to MySQL in RDS or EC2

Uncategorized
| Mar 22, 2019
AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.




2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
(more...)

Proactive Oracle Support?

Uncategorized
| Mar 1, 2019

You may not be aware but Oracle have been talking about their new ProActive support with user groups. It’s basically trying to show you best practices you should be implementing before stuff goes wrong, and many other useful bits of information.

It covers a lot of areas and is really worth a look, whether you’re using Oracle Database, Linux, MySQL, EBS, JDEdwards or indeed most Oracle products.

The open URL for headline information is: https://www. (more...)

Beware (Sort-Of) Ambiguous Column Names In Sub-Selects

Uncategorized
| Apr 30, 2018
This morning I received an UPDATE statement from a developer that I was testing. It ran without errors but then I saw that it updated 5 rows when it should have only updated 3. The reason gave me a little shock so I whipped up a simple test-case to reproduce the problem.

First we create two tables:

CREATE TABLE foo (
    id int
    , name varchar(30)
);
CREATE TABLE
CREATE TABLE (more...)

Global variable wsrep_OSU_method=RSU ignored on MariaDB Galera Cluster 10.0.32

Uncategorized
| 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:http://galeracluster.com/documentation-webpages/clusterstallonalter.html
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...)

Optimizer Strategien für Subqueries für Oracle, Postgres und MySQL

Uncategorized
| Jan 2, 2018
Chris Antognini hat in seinem Blog eine sehr spannende Untersuchung zur Frage durchgeführt: wie gut kommen die Optimizer unterschiedlicher RDBMS mit relativ einfachen Subqueries klar? In der umfangreichen Untersuchung betrachtet er sechs unterschiedliche Subquery-Typen:
  • Scalar subqueries with equality predicate
  • Scalar subqueries with inequality predicate
  • Uncorrelated subqueries with either IN or EXISTS
  • Uncorrelated subqueries with either NOT IN or NOT EXISTS
  • Correlated subqueries with either IN or EXISTS
  • Correlated subqueries with either NOT IN or NOT (more...)

How Well a Query Optimizer Handles Subqueries?

Uncategorized
| Dec 31, 2017

At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence:

Simple sub-queries that are not correctly optimized were observed.

It goes without saying that such a sentence leaves a lot of questions open. After (more...)

Oracle Week 2017: Docker Concepts for Oracle/MySQL DBAs and DevOps (slides)

Uncategorized
| Nov 28, 2017

Oracle Week in Israel is a reason for celebration. Every year (and for the past 24 years), between a 1500 and 2000 Oracle professional are gathered for a 5 days conference on Oracle related educational topics. Each day is a full-day seminar on a specific topic – 9am to 4:30pm talking about something related to Oracle technologies. This year, I had 4 full-day back-to-back seminars – which is way more than the average speaker. Some (more...)

Beware of intensive slow query logging when using – log_queries_not_using_indexes

Uncategorized
| 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...)

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

Uncategorized
| 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...)

Wanted: RDBMS superpower summary for app developers

Uncategorized
| Nov 13, 2015

At last night's WWCode Cincinnati panel, I recommended that developers talk to their DBA about what advanced capabilities their RDBMS can offer, so that they don't end up reimplementing functionality in the app that are already available (better and more efficiently) in the database itself. Devs can waste a lot of effort by thinking of databases as dumb, inert data boxes.

I was asked an excellent question: "Where can a dev quickly familiarize herself with (more...)

Couchbase 4.0 and related subjects

Uncategorized
| Oct 15, 2015

I last wrote about Couchbase in November, 2012, around the time of Couchbase 2.0. One of the many new features I mentioned then was secondary indexing. Ravi Mayuram just checked in to tell me about Couchbase 4.0. One of the important new features he mentioned was what I think he said was Couchbase’s “first version” of secondary indexing. Obviously, I’m confused.

Now that you’re duly warned, let me remind you of aspects of (more...)

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

Uncategorized
| 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:

(more...)

Test MySQL on AWS quickly

Uncategorized
| Feb 19, 2015

Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. 

Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)

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

Uncategorized
| 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:
pt-heartbeat
pt-table-checksum
pt-table-sync


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 percona.com/get/percona-toolkit.deb

$ (more...)

MariaDB(MySQL) Master-Master Replication

Uncategorized
| 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...)

MySQL Locking

Uncategorized
| Jan 5, 2015
Have you ever wondered if MySQL does Row Level Locking? Or wondered why are you getting the error:

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds (more...)

Thoughts and notes, Thanksgiving weekend 2014

Uncategorized
| Nov 30, 2014

I’m taking a few weeks defocused from work, as a kind of grandpaternity leave. That said, the venue for my Dances of Infant Calming is a small-but-nice apartment in San Francisco, so a certain amount of thinking about tech industries is inevitable. I even found time last Tuesday to meet or speak with my clients at WibiData, MemSQL, Cloudera, Citus Data, and MongoDB. And thus:

1. I’ve been sloppy in my terminology around “geo-distribution”, in (more...)