I'm so happy to be working at Datadog ( and we are hiring so ping me if interested. Looking for Python developers who are interested in databases. Next up will be work on deep Oracle monitoring).
Datadog has brought together my favorite parts of database performance monitoring
Average Active Sessions
Query metrics
Powerful UI
I've long been an evangelist of the Average Active Sessions (AAS) view of database performance but I've wanted the AAS approach married with rich SQL execution metrics like one would see in VividCortex (now part of Solarwinds).
I also love rich powerful graphics like Datadog has. I recall seeing Datadog presentations years ago at conferences and ...
khailey
Uncategorized
Sure, wait event times in any database will include some CPU time, though ideally it should be minimal. Waits should be instrumented as close to the off CPU wait event as possible, i.e. if I'm doing an IO, then I want the wait to cover just the read call:
start_wait('my_read_wait');
pread( ... );
end_wait('my_read_wait');
So my function that sets up the read will do some CPU that could get caught under the cover of the wait itself and the call to pread will use some CPU but most of the time should be waiting for the actual I/O if the I/O is coming from ...
khailey
Uncategorized
Just quick post to document a troublesome error when running Jmeter on Mysql
JDBC Request: Cannot load JDBC driver class'com.mysql.jdbc.Driver' solution
Should be pretty simple, it's just a question of putting the mysql-connector-java jar file, available at https://dev.mysql.com/downloads/connector/j/, into the classpath for Jmeter. The easiest way for me is to put this into the jmx file:
<stringProp name="TestPlan.user_define_classpath"> /Users/kyle.hailey/jars/mysql-connector-java-8.0.29.jar,/home/ec2-user/jars/mysql-connector-java-8.0.29.jar</stringProp>
Notice there is a space " " before the first jar file. Apparently the space was enough to cause the error. After removing the space , jmeter connected successfully to mysql.
khailey
Uncategorized
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" ( https://github.com/dbcli/mssql-cli/blob/main/doc/installation ) . It's much nicer that sqlcmd but sqlcli has the drawback that it doesn't handle @variables. For that I use Azure ...
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
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
Recent Comments