DataDog – the marriage of ASH, Query Stats and UI

June 30th, 2022
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 ...


Uncategorized

Are some MySQL wait events actually CPU time ?

June 29th, 2022
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 ...


Uncategorized

JDBC Request: Cannot load JDBC driver class’com.mysql.jdbc.Driver’ solution

June 22nd, 2022
  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.  


Uncategorized

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" ( 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 ...


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

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