Introduction In this article, I’m going to explain how we can index JSON columns when using MySQL. While other relational database systems provide GIN (Generalized Inverted Index) indexes, MySQL allows you to index a virtual column that mirrors the JSON path expression you are interested in indexing. Database table Let’s assume we have the following database book table: The properties column type is json, so we can store JSON objects as book properties. Querying MySQL JSON columns without an index If we try to filter one record by its associated title attribute... Read More

The post How to index JSON columns using MySQL appeared first on Vlad Mihalcea.

Introduction In this article, we are going to see how we can enable the MySQL JDBC Driver Statement Caching mechanism, which, unfortunately, is not enabled by default. No matter what data access framework you are using, you still need to configure the JDBC Driver if you want to get the most out of the MySQL database engine. MySQL JDBC client-side PreparedStatement As I explained in this article, by default, the MySQL JDBC Driver emulates prepared statements. So, no matter if you are executing a plain Statement or a PreparedStatement, the SQL statement... Read More

The post MySQL JDBC Statement Caching appeared first on Vlad Mihalcea.

Introduction While developing a Spring Boot application is rather easy, tuning the performance of a Spring Boot application is a more challenging task, as, not only it requires you to understand how the Spring framework works behind the scenes, but you have to know what is the best way to use the underlying data access framework, like Hibernate for instance. In a previous article, I showed you how easily to optimize the performance of the Petclinic demo application. However, by default, the Petclinic Spring Boot application uses the in-memory HSQLDB database, which... Read More

The post Spring Boot performance tuning appeared first on Vlad Mihalcea.

Overview The Skinny

In this blog post we explore various options for performance tuning MySQL server for better slave replication performance.

A Tungsten Cluster relies upon the Tungsten Replicator to move events from the master node to the slaves. Once the event has been transferred to the slave as THL on disk, the slave applier will then attempt to write it to the database. The Replicator can only apply events as fast as MySQL allows. If the MySQL server is somehow slow or blocking, then the Replicator will be as well.

A properly-tuned database server in addition to infrastructure and SysAdmin best practices will go quite a long way towards high-performance slave apply.

The Question Recently, a customer asked us:

During one of our load tests, we had a peak of 60k writes/min, averaging …

Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents MySQL Performance Schema in 1 hour on Thursday, March 21st, 2019, at 10:00 am PDT (UTC-7) / 1:00 pm EDT (UTC-4).

Register Now

MySQL 8.0 Performance Schema is a mature tool, used by humans and monitoring products. It was born in 2010 as “a feature for monitoring server execution at a low level.” The tool has grown over the years with performance fixes and DBA-faced features. In this webinar, I will give …

In this blog post, I want to share a case we worked on a few days ago. I’ll show you how we approached the resolution of a MySQL performance issue and used Percona Monitoring and Management PMM to support troubleshooting. The customer had noticed a linear high CPU usage in one of their MySQL instances and was not able to figure out why as there was no much traffic hitting the app. We needed to reduce the high CPU usage on MySQL. The server is a small instance:

Models | 6xIntel(R) Xeon(R) CPU E5-2430 0 @ 2.20GHz
10GB RAM

This symptom can be caused by various different reasons. Let’s see how PMM can be used to troubleshoot the issue.

CPU

It’s important to understand where the CPU time is being consumed: user space, system space, iowait, and so on. Here we can see that CPU usage was …

Following my post MySQL 8 is not always faster than MySQL 5.7, this time I decided to test very simple read-only CPU intensive workloads, when all data fits memory. In this workload there is NO IO operations, only memory and CPU operations.

My Testing Setup

Environment specification

• Release | Ubuntu 18.04 LTS (bionic)
• Kernel | 4.15.0-20-generic
• Processors | physical = 2, cores = 28, virtual = 56, hyperthreading = yes
• Models | 56xIntel(R) Xeon(R) Gold 5120 CPU @ 2.20GHz<
• Memory Total | 376.6G
• Provider | packet.net x2.xlarge.x86 instance

I will test two workloads, sysbench oltp_read_only and oltp_point_select varying amount of threads

sysbench oltp_read_only --mysql-ssl=off --report-interval=1 --time=300 …

EverSQL is a platform that intelligently tunes your SQL queries by providing query optimization recommendations, and feedback on missing indexes. This is the second post of our EverSQL series, if you missed our introductory post take a look there first and then come back to this article.

We’ll use the Stackoverflow data set again as we did in our first post.

Diving into query optimization

We’ll grab the worst performing query in the list from PMM and optimize it. This query builds a list of the top 50 most recent posts which have a score greater than two, and involves joining two large tables – posts and comments. The original runtime of that query is …

For a very long time – since when multiple CPU cores were commonly available – I dreamed about MySQL having the ability to execute queries in parallel. This feature was lacking from MySQL, and I wrote a lots of posts on how to emulate parallel queries in MySQL using different methods: from simple parallel bash script to using Apache Spark to using ClickHouse together with MySQL. I have watched parallelism coming to PostgreSQL, to new databases like TiDB, to …

A common challenge with continuously deployed applications is that new and modified SQL queries are constantly being introduced to the application. Many companies choose to use a database monitoring system (such as PMM) to identify those slow queries. But identifying slow queries is only the start – what about actually optimizing them?

In this post we’ll demonstrate a new way to both identify and optimize slow queries, by utilizing the recent integration of Percona Monitoring and Management with EverSQL Query Optimizer via Chrome browser extension. This integration allows you to identify slow queries using PMM, and optimize them automatically using EverSQL Query Optimizer.

Optimizing queries with PMM & EverSQL

We’re using PMM to monitor our MySQL …