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 ...
khailey
Uncategorized
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
# ...
khailey
Uncategorized
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 ...
khailey
Uncategorized
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,
...
khailey
Uncategorized
This content is password protected. To view it please enter your password below:
Password:
khailey
Uncategorized
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 ...
khailey
Uncategorized
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 ...
khailey
Uncategorized
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 - ...
khailey
Uncategorized
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 ...
khailey
Uncategorized
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 ...
khailey
Uncategorized
Recent Comments