The latest from the MySQL community

Two weeks ago I was being drawn into the debug of artdb, the Replication hierarchy used by our Artifactory instance.

TL;DR

Artifactory overloaded the database. This was incident-handled by optimizing a number of slow queries using some covering index trickery, and by upgrading the hardware substantially.

Using the runway we bought, we found and partially fixed the following problems:

• Fixed: A number of very expensive reporting queries were sped up 16x to 20x using covering indexes, from 180s runtime to 8s-12s runtime.
• Fixed: We optimized our database and data size by completing the data lifecycle for several repo types, deleting old images.
• Fixed: Hardware upgrade lowered load even more, and sped up the queries even more.
• Fixed: …

The INFORMATION_SCHEMA database is full of information and metadata about your database(s). Columns are a necessity and their definition ensures sound storage and data integrity. Learn all about them with the COLUMNS table.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by Gerd Altmann from  …

As a Database Engineer, One of the biggest challenges in day-to-day activity is performing DDL on high-traffic and transaction-intensive tables. It will become overhead when handling a large number of servers/shards.

As a standard process, we will first deploy the changes in DEV and QA before deploying them in production.

In the sharded environment, It will become a heavy overhead to maintain the schema changes in DEV, QA, and PROD servers. Since we will have multiple servers in the sharding.

To overcome this deployment supervision, the Skeema tool will help to deploy the changes in QA, DEV, and PROD in a safe and parallel ( for Shards ) as well.

This blog focuses on the basic operation of Skeema, will have a series of blogs on Skeema

1. About the …

Quality of a machine learning model must be assessed not only during model development phase but also after a model is deployed in production. During development, a model developer must keep in mind the impact of the results produced by the model and use appropriate scoring metrics that will expose underlying issues. For example, in […]

MySQL HeatWave ML supports various scoring metrics. User needs to keep in mind the characteristics of these metrics, to use them effectively.

This architecture is fine for development, but is somewhat lacking for production: There is no way to scale out the application tier or database tier to meet increasing load. The application server and database can only be scaled up by adding resource. Both the application server and database are single points of failure (SPOF). Further, there is no way maintenance can be performed on either the application server or database without affecting availability.

This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.

I can’t thank you all enough for making OpenLampTech a successful endeavor for me. We are continuing to publish great newsletters that get read and draw in new subscribers. This week’s issue is loaded with great content so enjoy those articles you like most. Please share with others…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #43 we have content on:

• The PHP Constructor
• WordPress needs writing
• MySQL Window Functions
• Managing 1,300 WordPress sites
• A list of great free …

Update June 12, 2023: This component has been deprecated in v17 and will be removed in v18! We recommend that you instead use VTOrc with the semi_sync durability policy. Introduction # MySQL group replication is a new replication mechanism that was released in 2016. Group replication involves establishing a group of nodes that are coordinated automatically via Group Communication System (GCS) protocols, an implementation of Paxos. For a transaction to commit, a majority of the group has to agree on the order of a given transaction in the global sequence of transactions.

For anything but the most trivial software projects source control is highly recommended. Where multiple developers contribute to a project then it is essential to have proper source control so that changes to code can be properly managed/merged.