Log Buffer #527: A Carnival of the Vanities for DBAs

Posted in: Log Buffer, Technical Track

This Log Buffer edition covers Cloud, Oracle, MySQL, and PostgreSQL.

Cloud:

New – Amazon EC2 Instances with Up to 8 NVIDIA Tesla V100 GPUs (P3)

Amazon Elasticsearch Service now supports VPC

Cray Supercomputers are coming to Azure

Fv2 VMs are now available, the fastest VMs on Azure

GCP Podcast hits 100 episodes — here are the 10 most popular episodes

Oracle:

nVision Performance Tuning: 3. Indexing of Ledger, Budget, and Summary Ledger Tables on Non-Engineered Oracle Systems

In searching for patches for Oracle GoldenGate you have to also look for Oracle Database patches.

CTAS-Statement-Texte in 12.2 Trace-Files nicht mehr gekürzt

Manufacturer Replaces the Google Search Appliance with Mindbreeze for Intranet and Website Search: From Racking to Go-Live in 37 Days

MRP process getting terminated with error ORA-10485

MySQL:

MySQL 8.0 Atomic DDL – behavior change

MySQL Enterprise Monitor 4.0.1 has been released

The Perfect Server – Ubuntu 17.10 (Artful Aardvark) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3.1

Migrating to MySQL 8.0 for WordPress – episode 3: query optimization

JSON Output of the pmm-admin list Command

PostgreSQL:

Now Available – Amazon Aurora with PostgreSQL Compatibility

In the last log buffer Jeremy Schneider touched on backup and recovery in PostgreSQL. Next he will highlight a few articles about SQL optimization and query tuning below.

If you’ve ever heard Lukas Eder speak, then you know he loooves SQL. And he’s not only smart but also funny as a speaker. His LinkedIn job title is “Minister of Bringing Sanity Back to Java / SQL Development”. This month he posted a brilliant article on his blog digging into the internals of how optimizers transform SQL statements – not only PostgreSQL, but also MySQL, Oracle, DB2 and SQL Server!! Well worth the read.

Coming back to PostgreSQL specifically, newcomers are often a little bewildered by the many choices of index structures. We all know B-Tree, but what does “GIN” mean and should I ever use “GiST” or “BRIN” indexes? Craig Kerstiens to the rescue again – with an excellent article on the Citus blog giving descriptions and common use cases for all of these new and interesting index types!

Somewhat related to indexes, anyone who has worked on Oracle for some length of time has probably encountered SQL Work Areas. Even with the magic of PGA_AGGREGATE_TARGET today we sometimes need to go back to a manual workarea_size_policy now and then. In PostgreSQL this is called “work_mem” and Pat Shaughnessy recently published an article on dzone explaining it.

It should be no surprise that PostgreSQL maintains both object statistics and server activity statistics. The object statistics are
used for query optimization and persisted in the catalog. Server activity stats are more like runtime information from server startup or since the last reset. The PostgreSQL documentation isn’t explicit about all of the differences between these two classes of statistics but back in 2014 Tomas Vondra wrote a great article digging in and explaining. This article is still relevant today on PostgreSQL 10 and worth reading again.

Finally, there are two recent articles about testing that are worth highlighting even though neither was written specifically about
PostgreSQL. First, Charity Majors spoke at the Seattle Scaleability Meetup on October 19th and gave a very fun and compelling talk about release engineering.

The heart of her talk was summarized in an article she recently posted on opensource.com called “Testing inproduction: Yes, you can (and should).” She knows what she’s talking about. Second, Colin Mccabe at Confluent recently wrote a great piece on the same subject called “How Apache Kafka is Tested” and the point (of course) is that we can all learn from what they are sharing.

The shared theme in both of these articles is not to disparage existing ideas for testing but rather to point out that tremendous new advancements have been made in recent years which build on the existing body of knowledge. Especially as we increasingly build larger and more distributed systems. Charity said in an earlier article, “You can’t hold the entire system in your head or reason about it; you will live or die by the thoroughness of your instrumentation and observability tooling.”

email

Interested in working with Fahd? Schedule a tech call.

About the Author

I have been in love with Oracle blogging since 2007. This blogging, coupled with extensive participation in Oracle forums, plus Oracle related speaking engagements, various Oracle certifications, teaching, and working in the trenches with Oracle technologies has enabled me to receive the Oracle ACE award. I was the first ever Pakistani to get that award. From Oracle Open World SF to Foresight 20:20 Perth. I have been expressing my love for Exadata. For the last few years, I am loving the data at Pythian, and proudly writing their log buffer carnivals.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *