How does one check if there are missing indexes in a database?
Is there a programatic way to do it?
Explain Plan
Most blogs on the subject will say look at the explain plan for your query.
Basic concept is to look for full table scans in the explain plan and the look and see if there is a predicate filter (i.e. SQL text of form “where table.field=value”) with a full table scan or a full table scan in a join ( i.e. tableA.field = tableB.field). There are other considerations, things to check, and other places for indexes, but this is a good start.
Explain ...
khailey
Uncategorized
trying to connect to SQL Server from my Mac so install sqlcmd:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install mssql-tools
then get error
$ sqlcmd -S kylelfsqls.xxxxxx.us-west-2.amazonaws.com,1433 -U kylelf
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed].
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
Segmentation fault: 11
Did't find much info google, so blogging here.
Ran
ln -s /usr/local/opt/openssl@1.1 /usr/local/opt/openssl
and it worked
khailey
Uncategorized
Goal was to create a empty table and poplulate it with optimizer table and index stats from a poplulated table to try and get the optimizer to give the same explain plan and costs on the empty table as the populated table. The purpose of this was to be able to modify the empty table by adding indexes quickly and cheaply and still be able to get correct optimizer costs. This worked in the case of table access but unfortunately didn't work when bring in an index.
Create procedure to populate test table
DROP PROCEDURE IF EXISTS populate_data;
DELIMITER //
CREATE PROCEDURE populate_data(max_val int)
BEGIN
DECLARE ...
khailey
Uncategorized
Good reference on subject: https://blog.orapub.com/20150210/what-is-light-green-oracle-database-cpu-wait-time.html
Below I'm comparing AAS from ASH verses SYSMETRICs specificially
ASH CPU in AAS vs SYSMETRIC CPU in AAS
ASH total AAS vs SYSMETRIC total AAS derived from DB TIME
ASH CPU is consistently lower that SYSMETRIC CPU and that could be because waits burn CPU but show up as waits and not CPU in ASH.
On the other hand, not sure why total AAS from SYSMETRIC is consistently smaller than total AAS from ASH.
Def v_secs=60 -- bucket size
select DBTT.begin_time ,ASH_CPU, DBT_CPU , ASH_AAS, DBT_AAS from
(
select to_char(begin_time,'MON DD YYYY HH24:MI') begin_time,
...
khailey
Uncategorized
UPDATE: All slots for this study have been filled.
On the other and would love your feedback. Please send any ideas about what you'd like to see in RDS performance monitoring to me at kylelf at amazon.com
Thanks
Kyle
Have you ever used Amazon RDS console to manage performance on an RDS database and had ideas on how to make it better? The Amazon UX Research team for AWS is collaborating with me to recruit for an upcoming user research study with Amazon Relational Database Service (RDS) performance monitoring. Sessions will take place between Monday, April 22, 2019 – Friday, April 26, 2019. In this study Amazon is looking to ...
khailey
Uncategorized
Honeycomb.io turns out to be a nice solution for collecting, retrieving and displaying multi-dimensional time series data, i.e. the kind of data you get from sampling.
For example, in the database world we have Active Session History (ASH) which at it's core tracks
when - timestamp
who - user
command - what SQL are they running
state - are they runnable on CPU or are they waiting and if waiting what are they waiting for like I/O, Lock, latch, buffer space, etc
Collecting this information is pretty easy to store in a relational database as I did when creating S-ASH (Simulated ASH) and Marcin Przepiorowski built upon over ...
khailey
Uncategorized
Amazon RDS Performance Insights (PI) doesn't have a single pane of glass dashboard for clusters, yet. Currently PI has a dashboard that has to be looked at for each instance in a cluster.
On the other hand one can create a simple cluster dashboard using Cloudwatch.
PI, when enabled, automatically sends three metrics to Cloudwatch every minute.
These metrics are
DBLoad
DBLoadCPU
DBLoadNonCPU
DBLoad = DBLoadCPU + DBLoadNonCPU
These metrics are measured in units of Average Active Sessions (AAS). AAS is like the run queue on the UNIX top command except at the database level. AAS is the average number of SQL queries running concurrently in the database. In the ...
khailey
Uncategorized
“delayed commit ok initiated" - is a thread state in Aurora MySQL which indicates the thread has started the async commit process but is waiting for it to be ack’d. You will not find this thread state in MySQL as MySQL does not use our async commit protocal, it is Aurora MySQL specific. This is usually the genuine commit time of a transaction.
This is a "state" and not a wait.
khailey
Uncategorized
Installing CLI on LINUX
1. install PIP
https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-linux.html#awscli-install-linux-pip
curl -O https://bootstrap.pypa.io/get-pip.py
python get-pip.py --user
2. install AWS CLI
https://docs.aws.amazon.com/cli/latest/userguide/installing.html
pip install awscli --upgrade --user
3. configure
aws configure
For “aws configure” you will need
AWS Access Key ID:
AWS Secret Access Key:
Which you can get by going to the AWS console, going to IMS and creating access key.
Running example
Once “aws” is configured you can run the CLI like
aws \
pi get-resource-metrics \
--region us-east-1 \
--service-type RDS \
--identifier db-xxxxxx \
--metric-queries "{\"Metric\": \"db.load.avg\"}" \
--start-time `expr \`date +%s\` - 60480000 ` \
--end-time `date +%s` \
--period-in-seconds 86400
That “—identifier” is for one of my databases, so you will have to change ...
khailey
Uncategorized
Thanks to Jeremiah Wilton for the following info:
This wait event indicates that there is a thread which is waiting on an InnoDB record lock. Check your database for conflicting workloads. More information on InnoDB locking can be found here: https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html
In other words, record-level lock conflicts are happening. More than one connection is trying to update the last_login for a particular id in the_table at the same time. Those connections are conflicting and serializing on the record lock for that id. Here's a query that can help you identify the blocker and waiter for InnoDB record locks in MySQL-family engines. Run ...
khailey
Uncategorized
Recent Comments