Setting up Always ON (SQL Server)

April 16th, 2022
  There is a great little  docker setup that starts Always On (SQL Servers instance replication not to be confused with their article i.e. table replication). The docker setup creates a primary and a secondary. Find it here https://github.com/rafaelrodrigues3092/docker-mssql-alwayson Just do git clone https://github.com/rafaelrodrigues3092/docker-mssql-alwayson cd docker-mssql-alwayson docker compose up and voila you'll have a primary SQL Server with a Secondary where the "SALES" database is replicated. To connect I use "sqlcli.sh" . It's much nicer that sqlcmd but sqlcli has the drawback that it doesn't handle @variables. For that I use Azure Studio For this docker ...


Uncategorized

introducing network latency on Docker (SQL Server Always On testing)

April 13th, 2022
I am testing out Always On replication on SQL Server with a cool docker setup that includes primary and secondary https://github.com/rafaelrodrigues3092/docker-mssql-alwayson When I created data on the primary there was never any lag on the secondary so I wanted to make sure my lag values were correct and wanted to introduce network latency between the primary and secondary to introduce lag time. For this there is a neat tool called "tc" that can introduced lag. For example on either or both of the docker containers , I can set up lag with # add lag tc qdisc add dev eth0 root netem delay 100ms # ...


Uncategorized

MySQL : events_waits_current – who is waiting and who is on CPU

March 25th, 2022
Anyone familiar with Oracle performance tuning knows about ASH , aka active session history. ASH saves a history of every session that is active every collection, which by default is once per second. Once per second is fast enough to give a clear view of database load. The ASH data comes from v$session which is a structure that contains information on every connection and shows which connections are active, what SQL they are running (SQL_ID) and if they are runnable on CPU or waiting on a wait_event for some resource like an I/O to complete or a lock or latch. It's ...


Uncategorized

MySQL -> what to do with NESTING_EVENT_ID

March 24th, 2022
How does one use  NESTING_EVENT_ID? Below I've selected from  events_waits_history_long for a single thread, but none of the  NESTING_EVENT_ID values show up in EVENT_ID or END_EVENT_ID. To simplify things , I'm just showing the data for one THREAD_ID listed in order of TIMER_START. mysql> select THREAD_ID, EVENT_ID, END_EVENT_ID, NESTING_EVENT_ID, ...


Uncategorized

Protected: DataDog – the marriage of ASH, Query Stats and UI

March 23rd, 2022
Enter your password to view comments.
This content is password protected. To view it please enter your password below: Password:


Uncategorized

MySQL waits and CPU

March 23rd, 2022
Question: how do I know that one user is waiting for a lock and another  user is burning CPU on MySQL? i.e. how can I create active session history on MySQL like on Oracle? Two simple cases I'm looking at session waiting on lock The wait event is " wait/io/table/sql/handler" which happens for a number of potential reasons -> I can join to information_schema views INNODB_LOCK_WAITS & INNODB_TRX but this is heavy, and I'd rather only access those after I know there is a lock waiter. session burning CPU How do I know from performance schema views that the connection is active and burning CPU? It has a wait event that shows up wait/synch/mutex/sql/THD::LOCK_thd_data on Oracle to ...


Uncategorized

Postgres performance gone south but nothing changed !

July 14th, 2021
Postgres seems pretty cool as open source databases go. I'm finding a much greater affinity for Postgres than say MySQL. On the other hand, coming from an Oracle background there are a few things that come across as strange on Postgres. Most of the weirdness for me centers around MVCC, Here is one new surprise for me. I'm running a steady workload on a set of tables with inserts, updates, deletes and selects. This is putting a decent load on the database. Then at 20:45, I simply do: \set AUTOCOMMIT off \echo :AUTOCOMMIT create table toto ( id int); insert into toto values (1); commit; select * from toto for ...


Uncategorized

Covering Indexes in Postgres

June 19th, 2020
Covering indexes are where all the columns needed are in the index and, in theory, only the index needs to be accessed. This seemingly straight forward strategy is much tricker on Postgres. Let's take TPC-H query 6: select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1996-01-01' and l_shipdate < cast(date '1996-01-01' + interval '1 year' as date) and l_discount between 0.06 - ...


Uncategorized

Granularity impact on visualization

May 22nd, 2020
The longer the timeline we show in the graph, either the more points we show or the larger granularity we use. For example if I show 1 hour of data, then I can show 60 points of 1 minute each granularity. If I showed a day at 1 minute each then that would be 1440 points. Say we wanted to limit the points to less than 300 points so our UI stays responsive no matter the time scale. The for 24 hours I can show 5 minute granularity which is  288 points If  I show 25 hours  that's 300 points at 5 minute granularity so we ...


Uncategorized

Where to add Index on Postgres

March 30th, 2020
  Summary Goal in this post is to layout a methodology that can be implemented to scan the explain plans and identify opportunities to optimize the sql execution with additional indexes. The missing index opportunities outlined here are Indexes that would avoid full table scan with a predicate filter that filters out most of the returned rows Joins that are missing index on the join field Index range scans that could be made more efficient by addition of a field to existing index Also for future consideration, but not yet outline here Recommending covering index where the addition of fields to an index avoids table access all together Indexes that ...


Uncategorized