“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
If you think so, check out shell script "ioh.sh" from github at https://github.com/khailey/ioh
Introduction and Goals
The goal of ioh.sh is to measure both the throughput and latency of the different code layers when using NFS mounts on a ZFS appliance. The ZFS appliance code layers inspected with the script are I/O from the disks, ZFS layer and the NFS layer. For each of these layers the script measures the throughput, latency and average I/O size. Some of the layers are further broken down into other layers. For example NFS writes are broken down into data sync, file sync and non-sync operations and NFS ...
khailey
Uncategorized
I always forget IP address range coverage rules and forget where to look.
It's the wiki!
https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing
and for good reference here is the table:
/32 is for a single address
/24 is for a range in the last place x.x.x.0
/16 is for a range in the last 2 places x.x.0.0
khailey
Uncategorized
How do you determine if the Oracle SQL optimizer has created a sub-optimal execution plan? re-run statistics and see what happens? wait for Oracle to find other execution plans? What if neither method is helping? Do you read the execution plan? What do you look at? Differences in actual vs estimated? How successful is that? Look for full table scans? Do you look at the 10053 trace? How much time and effort does that take? What do you look at in the 10053 trace. Do you have a systematic methodology that works in almost all cases?
Well there is a method that is reliable ...
khailey
Uncategorized
http://www.oaktable.net/blog/oak-table-world-2017-oracle-open-world
The Oak Table members will be discussing their latest technical obsessions and research on Monday and Tuesday, (Oct. 2nd and 3rd, 2017). The truth is, folks- The Oak Table experts are an AWESOME group, (if I don’t say so myself! :)) as we could have easily done another day of incredible sessions, but alas, two days is all we have available for this year’s event.
khailey
Uncategorized
While at Delphix, we did a lot of storage benchmarking. The I/O response times of Delphix depends, as one would logically imagine, heavily on the underlying disks. Sure Delphix can cache a lot ( with 1 TB of ram and 3x compression that's 3TB and that 3TB can be shared by 10 or a 100 copies being the equivalent to 30TB or 300TB of databases) but really there will always be important I/O coming from the storage subsystem.
Now Delphix mainly runs databases loads, so the best test for storage that is hooked up to Delphix is to benchmark the storage I/O ...
khailey
Uncategorized
'
photo by Thomas Hawk
https://github.com/khailey/pgmon
$ ./pgmon.sh
Usage: pgmon.sh [username] [password] [host] <sid=postgres> <port=5432> <runtime=3600>
$ ./pgmon.sh kyle kyle mymachine.com
psql -t -h mymachine.com -p 5432 -U kyle postgres < /tmp/MONITOR/tmp/mymachine.com:postgres_collect.pipe &
RUN_TIME=-1
COLLECT_LIST=
FAST_SAMPLE=wts
TARGET=mymachine.com:postgres
DEBUG=0
Connected, starting collect at Wed Mar 8 12:05:12 PST 2017
starting stats collecting
SET
AAS| blks_hit | blks_read | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
1 | 38281 | 1 | 628629 ...
khailey
Uncategorized
pgload.jmx is JMX file you can load into Jmeter and run a substantial load on a Postgres database. Should work just as well on Oracle if you change the test SQL from "Select 1" to "select 1 from dual"
Install jmeter on our machine . On my mac, I did
brew install jmeter
You will need the Postgres driver. I used
postgresql-9.4-1202.jdbc4.jar from https://jdbc.postgresql.org/download.html
Put it in the classpath in jmeter
To use this file, save it as pgload.jmx and then open it up with Jmeter. Change the database URL with host, port, database name, and fill in your username and password.
The database URL looks like jdbc:postgresql://machine.com:5432/database
where "machine.com" is ...
khailey
Uncategorized
Recent Comments