Did you know that MySQL 5.7 has a native JSON data type?

MySQL introduced a native JSON data type in MySQL 5.7. So like an integer, a char, or a real, there became a way to store an entire JSON document in a column in a table of a database—and this document in a column could be roughly a gigabyte in size! The server would make sure it was a valid JSON document and then save it in a binary format that’s optimized for searching. This new data type has probably been responsible for more upgrades of MySQL than any other feature.
The data type also comes with over 20 functions. These functions will extract key-value pairs from the document, update data, provide metadata about the data, output non-JSON columns in JSON format, and more. And it’s much easier on the psyche than REGEX.

Source: What you need to know about JSON in MySQL | Opensource.com

Well, I guess I really do need to update to MySQL 5.7 and keep track of what’s going on in version 8 development.

From the MySQL 5.7 docs on the JSON data type:

As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
  • Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

I’ve got a number of project that would benefit from this right now.

After 3+ Years Amazon RDS Cloud Database Service Achieves “General Availability”

The Amazon Relational Database Service (RDS) was designed to simplify one of the most complex of all common IT activities: managing and scaling a relational database while providing fast, predictable performance and high availability.
RDS in Action
In the 3.5 years since we launched Amazon RDS, a lot has happened. Amazon RDS is now being used in mission-critical deployments by tens of thousands of businesses of all sizes. We now process trillions of I/O requests each month for these customers. We’re seeing strong adoption in enterprises such as Samsung and Unilever, web-scale applications like Flipboard and Airbnb, and large-scale organizations like NASA JPL and Obama for America.

via Amazon Web Services Blog: Amazon RDS: 3.5 years, 3 Engines, 9 Regions, 50+ Features and Tens of Thousands of Customers.

As part of recent rebuild of Classcaster, I shifted the MySQL database for the system to Amazon RDS. I found the process of importing an existing database to be straight forward and was up and running in no time. Since it is just an instance of MySQL running in the Amazon cloud, I administer it as I do the other my other MySQL databases running on AWS EC2 instance using SQLyog on Mac and Windows and MySQL Workbench on Linux .

As far as performance goes, RDS seems a bit more responsive than the AWS EC2 hosted databases I run. It is important to note that it is possible to knock it over by overloading the connection pool . Logging and backups are handled well and access to these from the RDS dashboard is pretty good. Although I haven’t tried it yet, the features exist to scale the database as needed. I may take advantage of some of this if I decide to move our main databases to RDS.

Overall, I’d recommend RDS as a good way to get a database up and running quickly and to provide a stable backed for your systems.

 

I do like the latest version of Workbench, especially its real time monitor features, so I’m likely to move to it on all platforms.Powered by Hackadelic Sliding Notes 1.6.5
As I discovered while dealing with one of those way too frequent brute force attacks against WordPress.Powered by Hackadelic Sliding Notes 1.6.5