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

Jobs at RDS

April 13th, 2020
I'm personally interested in using explain plans to identify missing indexes opportunities.  Let me know directly if that is of interest to you. This could be in the roll of database engineer and design or development engineer on design and coding. My manager has a bunch of openings in all sorts of areas from database performance monitoring, recommendations, proxy https://www.amazon.jobs/en/jobs/1097253 - Software Development Manager https://www.amazon.jobs/en/jobs/1095579 - Sr. Software Development Engineer https://www.amazon.jobs/en/jobs/1109581 - Software Development Manager https://www.amazon.jobs/en/jobs/1095576 - Sr. Software Development Engineer https://www.amazon.jobs/en/jobs/1109605 - Sr. Software Development Engineer https://www.amazon.jobs/en/jobs/1109579 - Software Development Engineer https://www.amazon.jobs/en/jobs/1109580 - Software Development Engineer https://www.amazon.jobs/en/jobs/1058728 - Sr Manager, Software Dev https://www.amazon.jobs/en/jobs/908733 - Hybrid-cloud Database Architect The dev team I work with on performance monitoring and related has dev positions open   https://www.amazon.jobs/en/jobs1028502 - Software Development Engineer (Level 4) https://www.amazon.jobs/en/jobs/946258 - ...


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

Indexes on Joins for MySQL

March 24th, 2020
MySQL is simpler than the Oracle example  because MySQL only has Nested Loops and doesn't have Hash Join. Here is the query, like on the Oracle example from the last blog post select max(t1.data) from t1, t2 where t1.id = t2.id and t1.clus = 1 ;   So there are only 4 examples below which all do NL joins full table scan on T1 and T2 7.83 secs index on T1 predicate filter column 7.39 secs index on T2 join column 0.49 secs index on both T2 join column and T1 predicate filter column 0.06 secs There isn't an idea of "explain analyze" on MySQL until MySQL 8.0.18 and I did my ...


Uncategorized

Indexes for Joins on Oracle

March 23rd, 2020
Looking at ways to programmatically analyze explain plans to see if adding indexes would help. The analysis is straight forward for for full table scans with strong predicate filters. If there is a strong predicate filter on an explain plan node with a full table scan then it's a good candidate for an index. Now a similar situation exists for a join. Again if the join has a strong predicate filter on a full table scan, that is a candidate. On the other hand when the actual join fields look like they will benefit from an index, it's less obvious. In the queries below T1 and T2 have the ...


Uncategorized

Oracle Imp (import) to RDS

March 2nd, 2020
Had and old export dump file I wanted to import into RDS. I first tried to use S3 as a hosting site for the .dmp file and use rdsadmin.rdsadmin_s3_tasks.download_from_s3 to copy the file to the RDS database then import it with DBMS_DATAPUMP. Unfortunately DBMS_DATAPUMP only works with expdp files so instead of using DBMS_DATAPUMP , I got the old "imp" binary and used it with TWO_TASK to the RDS database I wanted it loaded into. For the record here were the steps  I took   First put the file into an S3 bucket called kylelf-datadump Create an IAM policy to allow Amazon RDS access to an Amazon S3 bucket https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html#oracle-s3-integration.preparing My policy ...


Uncategorized

Index Advisor ? PostgreSQL, Oracle and SQL Server : yes , MySQL : no

January 30th, 2020
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 ...


Uncategorized

SQLcmd on my Mac: Seg fault

January 23rd, 2020
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


Uncategorized

MySQL – manually updating table and index optimizer statistics

January 22nd, 2020
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 ...


Uncategorized

Oracle: compare DB Time and CPU time to ASH

April 26th, 2019
    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, ...


Uncategorized