Whitney  Durgan

Whitney Durgan

1623173220

JSON Column Type In MySQL 5.7.32 And Lucee CFML 5.3.7.47

Over the weekend, I took my first look at the JSON column type in MySQL 5.7. The JSON column type introduces a Document-like feature-set on top of the traditional relational database framework. In that post, I looked at storing Entity-Attribute-Values (EAV) in a JSON field. In this post, I wanted to look at an approach that we use at InVision in which an “invitation” to the system can be associated with Prototypes and Boards. Currently, we maintain these associations with a comma-delimited list of IDs (which is a garbage approach); and, I wanted to see if it would feel less like garbage if I used a JSON column type. To explore this, I’m using MySQL 5.7.32 and Lucee CFML 5.3.7.47.

CAUTION: This Should Have Always Been a Normalized Set Of Tables

With InVision, when you invite a user to the platform, you can start associating the pending invitation with other entities in the system such that when the recipient ultimately accepts the invitation, they are automatically associated with each of the aforementioned entities. Today, this is implemented in a rather naive fashion in which foreign entity IDs are stored as a comma-delimited list in the invitation record. Imagine a table that looks something like this (truncated):

  • email - The recipient of the invitation.
  • prototypeIDList - The comma-delimited list of prototypes associated with the invite.
  • boardIDList - The comma-delimited list of boards associated with the invite.

This type of structure leads to all sorts of problems:

  • We run into field-length overflows when too many IDs are added to the list.
  • We have to parse the ID-list as a String when searching for associations.
  • We can’t index the IDs.
  • We end up leaving “dead IDs” in the various lists because it’s overly complicated to remove IDs when the relevant entities are deleted.

#sql #mysql #json #lucee

What is GEEK

Buddha Community

JSON Column Type In MySQL 5.7.32 And Lucee CFML 5.3.7.47
Whitney  Durgan

Whitney Durgan

1623173220

JSON Column Type In MySQL 5.7.32 And Lucee CFML 5.3.7.47

Over the weekend, I took my first look at the JSON column type in MySQL 5.7. The JSON column type introduces a Document-like feature-set on top of the traditional relational database framework. In that post, I looked at storing Entity-Attribute-Values (EAV) in a JSON field. In this post, I wanted to look at an approach that we use at InVision in which an “invitation” to the system can be associated with Prototypes and Boards. Currently, we maintain these associations with a comma-delimited list of IDs (which is a garbage approach); and, I wanted to see if it would feel less like garbage if I used a JSON column type. To explore this, I’m using MySQL 5.7.32 and Lucee CFML 5.3.7.47.

CAUTION: This Should Have Always Been a Normalized Set Of Tables

With InVision, when you invite a user to the platform, you can start associating the pending invitation with other entities in the system such that when the recipient ultimately accepts the invitation, they are automatically associated with each of the aforementioned entities. Today, this is implemented in a rather naive fashion in which foreign entity IDs are stored as a comma-delimited list in the invitation record. Imagine a table that looks something like this (truncated):

  • email - The recipient of the invitation.
  • prototypeIDList - The comma-delimited list of prototypes associated with the invite.
  • boardIDList - The comma-delimited list of boards associated with the invite.

This type of structure leads to all sorts of problems:

  • We run into field-length overflows when too many IDs are added to the list.
  • We have to parse the ID-list as a String when searching for associations.
  • We can’t index the IDs.
  • We end up leaving “dead IDs” in the various lists because it’s overly complicated to remove IDs when the relevant entities are deleted.

#sql #mysql #json #lucee

Whitney  Durgan

Whitney Durgan

1623180540

Virtual Indexed Columns In MySQL 5.7.32 And Lucee CFML 5.3.7.47

As I mentioned in an earlier postInVision is upgrading some of its MySQL servers to 5.7.32 (for Long-Term Support, LTS). This upgrade brings with it some excited features like the JSON column type. It also unlocks the ability to add virtual columns to a table which can be derived from existing columns; and, as needed, get stored in a secondary index on said table. When I read about this feature, I immediately thought of the ability to derive email domain from a user’s email address. This is something that our data scientists and product teams are always asking about. As such, I wanted to sit down and see what a virtual “email domain column” might look like in MySQL 5.7.32 and Lucee CFML 5.3.7.47.

To lay the foundation for this exploration, let’s create a super simple user_account table that contains basic login information for a set of users. To start with, we’re just going to include email and password:

CREATE TABLE `user_account` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`email` varchar(75) NOT NULL,
	`password` varchar(35) NOT NULL,
	`createdAt` datetime NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `byEmail` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#sql #mysql #lucee

Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Devyn  Reilly

Devyn Reilly

1618900707

Setting MySQL Configuration Variables – MySQL 5.7 vs MySQL 8.0

MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.

We will explain three different ways for setting the configuration variables based on your use-case. Configuration variables that can be set at run time are called Dynamic variables and those that need a MySQL server restart to take effect are called Non-Dynamic variables.

Setting MySQL Configuration Variables

#mysql #mysql 5.7 #mysql 8.0 #mysql server

Whitney  Durgan

Whitney Durgan

1618911221

Setting MySQL Configuration Variables - MySQL 5.7 vs MySQL 8.0

In this article, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.

MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.

We will explain three different ways for setting the configuration variables based on your use-case. Configuration variables that can be set at run-time are called Dynamic variables and those that need a MySQL server restart to take effect are called Non-Dynamic variables.

#mysql #mysql 5.7 #mysql server #mysql 8.0