Showing entries 1 to 10 of 1009
10 Older Entries »
Displaying posts with tag: Performance (reset)
What are the MySQL Metrics That Really Make a Difference?

Author: Robert Agar

MySQL is one of the most popular relational database platforms in the world. As such, it is used as the backend of many mission-critical applications across all sectors of business and industry. If you are a DBA or database developer there is a high probability that you are working with MySQL now or will be in the near future.

One of the primary responsibilities of a DBA is to optimize the performance of their databases. There are many ways to accomplish this feat, and all of them have an important point in common. You need knowledge concerning the operation of your systems before you can expect to make intelligent modifications to them. All of the methods used to tune and optimize your databases are identified by studying metrics regarding their current performance and using this data to plan appropriate action.

The right tools are required to gather the information needed to …

[Read more]
MySQL 5.6/Maria 10.1 : How we got from 30k qps to 101k qps…..

Late one evening, I was staring at one of our large MySQL installations and noticed the database was hovering around 7-10 run queue length (48 cores, ~500 gigs memory, fusionIO cards). I had been scratching my head on how to get more throughput from the database. This blog records the changes I made to tune performance in order to achieve a 300% better throughput in MySQL. I tested my theories on MySQL 5.6/Maria 10.1. While with 5.7 DBAs would turn to performance_schema for the supporting metrics, I hope that you find the process interesting nevertheless.

View from an Oracle RDBMS DBA…

For context, I came to MySQL from a background as an Oracle RDBMS DBA, and this informs my expectations. For this exercise, unlike with Oracle RDBMS, I had no access to view wait events so that I could see where my database was struggling. At least, no access in MySQL 5.6/Maria 10.1 without taking a performance hit by using …

[Read more]
Webinar 8/7: Performance Analyses and Troubleshooting Technologies for Databases

Please join Percona CEO Peter Zaitsev as he presents “Performance Analyses and Troubleshooting Technologies for Databases” on Wednesday, August 7th, 2019 at 11:00 AM PDT (UTC-7).

Register Now

Have you heard about the USE Method (Utilization – Saturation – Errors), RED (Rate – Errors – Duration) or Golden Signals (Latency – Traffic – Errors – Saturations)?

In this presentation, we will talk briefly about these different-but-similar “focuses” and discuss how we can apply them to data infrastructure performance analysis troubleshooting and monitoring.

We will use MySQL as an example, but most of this talk applies to other database technologies as well.

[Read more]
MySQL Optimizer: Naughty Aberrations on Queries Combining WHERE, ORDER BY and LIMIT

Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes !  This is not a new problem: bugs about this can be traced back to 2014, and a blog post on this subject was published in 2015.  But even if this is old news, because this problem recently came yet again to my attention, and because this is still not fixed in MySQL 5.7 and 8.0, this is a subject worth writing about.

The MySQL Optimizer

Before looking at the problematic query, we have to say a few words about the optimizer.  The Query Optimizer is the part of query execution that chooses the query plan.  A Query Execution Plan is the way MySQL chooses to execute a specific query.  It …

[Read more]
Connector/Python C Extension Prepared Statement Support

Tweet

MySQL Connector/Python 8 made the C Extension the default for the platform/Python version combinations supporting it. One thing that was missing from the C Extension implementation (unless you used the _mysql_connector module) was support for prepared statements. That has been taken care of with the release of version 8.0.17.

The two main advantages of using prepared statements are security and performance. The security comes in as you can pass query parameters and have them applied server-side, so you are sure they are quoted and escaped correctly taking the data type into consideration. The performance benefit happens, when you execute the same query (except for the parameters) several times as MySQL will prepare it only …

[Read more]
The Best Way to Host MySQL on Azure Cloud

Are you looking to get started with the world’s most popular open-source database, and wondering how you should setup your MySQL hosting? So many default to Amazon RDS, when MySQL performs exceptionally well on Azure Cloud. While Microsoft Azure does offer a managed solution, Azure Database, the solution has some major limitations you should know about before migrating your MySQL deployments. In this post, we outline the best way to host MySQL on Azure, including managed solutions, instance types, high availability replication, backup, and disk types to use to optimize your cloud database performance.

MySQL DBaaS vs. Self-Managed MySQL

The first thing to consider when weighing between self-management and a MySQL Database-as-a-Service …

[Read more]
MySQL Functional Index and use cases.

MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).

Example: 

mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` …
[Read more]
TaskMax limit affects MySQL connections

Recently we had been bitten by a Systemd limitation at the “Tasks” created per-unit ie., process. This includes both the kernel threads and user-space threads, with each thread counting individually.

Am writing this blog as a reference for someone who might come across this limitation.

We have been actively working on migration DB instances, from one DC to the newly built DC .The instances on the newer DC were provisioned with the latest hardware and latest Debian OS. Below is the detailed spec of the system.

RAM             : 244G
Core             : 44Core
HardDisk.   : SSD
IOPS             : 120K
OS         …

[Read more]
My Slides about MySQL 8.0 Scalability & Benchmarks from #PerconaLIVE 2019 Austin, TX

Here are my slides about MySQL 8.0 Scalability & Benchmarks from Percona LIVE 2019 in Austin, TX. (and I also realized I forgot to publish my slides from MySQL pre-FOSDEM 2019 Day about MySQL Performance Tuning, so fixing it now ;-))

P.S. Percona LIVE was "just awesome" ! ;-))

Rgds,
-Dimitri

Constant-Folding Optimization in MySQL 8.0

In MySQL 8.0.16 the optimizer has improved again! Comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values. The goal is to speed up query execution.

Showing entries 1 to 10 of 1009
10 Older Entries »