Proper MySQL Query Optimization starts with a proper Slow Query Logging session. And MySQL Query Optimization is where I spend 70-80% of my time when doing MySQL performance optimization. In this short series I will show you how to do Slow Query Logging the right way.

Here’s links to the other two parts:

• Advanced MySQL Slow Query Logging Part 2: pt-query-digest report
• Advanced MySQL Slow Query Logging Part 3: fine-tuning the logging process

The post Advanced MySQL Slow Query Logging appeared first on Speedemy.

Researching a performance issue, we came to a startling discovery:

MySQL 5.7 + binlogs is by default 37-45% slower than MySQL 5.6 + binlogs when otherwise using the default MySQL settings

Test server MySQL versions used:
i7, 8 threads, SSD, Centos 7.2.1511
mysql-5.6.30-linux-glibc2.5-x86_64
mysql-5.7.12-linux-glibc2.5-x86_64

mysqld –options:

--no-defaults --log-bin=mysql-bin --server-id=2

Run details:
Sysbench version 0.5, 4 threads, socket file connection

Sysbench Prepare: 

sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua --oltp-auto-inc=off --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=1000000 --oltp_tables_count=1 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/path_to_socket_file/your_socket_file.sock …

Sandisk (FusionIO) and Nexenta are working together to build this SDS solution.

Infiniflash is a very large SDS production, which manages for very large DW system who requires large storage space and also high IOPS.

We test infiniflash system ,read this Infiniflash_benchmark

In this blog, I will provide answers to the Q & A for the Troubleshooting Slow Queries webinar.

First, I want to thank you for attending the April 28 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: I’ve heard that is a bad idea to use

select *

; what do you recommend?

A: When I used

SELECT *

 in my slides, I wanted to underline the idea that sometimes you need to select all columns from …

We’re moving along on the first day at Percona Live Data Performance Conference 2016, and I was able to attend a lecture from Intel’s Ken LeTourneau on Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums. In this talk, Ken reviewed some benchmark testing he did using MySQL on various types of storage mediums. This talk looked at the performance of Percona Server for MySQL for Linux running on the same Intel system, but with three different storage configurations. We looked at and compared the performance of:

1. a RAID of HDD
2. a RAID of SATA SSD
3. a RAID of NVMe SSD

In the talk,  Ken covered the hardware and system …

When your database undergoes a restart, whether it's a clean reboot or a crash, it will typically create two negative effects your application will have to face: blackout (downtime) and brownout (period of degraded performance after startup).

In my previous post, I described how Aurora makes downtime less stressful for you, now it's time to investigate yet another feature that should come in handy in mission-critical environments: the survivable page cache.

Introduction As usual, let's throw in a tiny bit of theory before looking at the demonstrations.
The InnoDB Buffer Pool is an in-memory area for caching data and index pages. That said, buffer pool can also be called a "page cache". Both terms mean …

This blog discusses how TokuDB impacts InnoDB performance when the two run in the same environment.

You would think MySQL storage engines are fairly independent of each other, even in the same environment. Enabling one, or changing its configuration, logically should have no impact on the performance of other engines (such as InnoDB) when they are accessing tables. The reality, however, is more complicated than that!    

Now that we’ve shipped TokuDB, we’ve been getting feedback from our community and customers that enabling TokuDB might negatively affect performance – even for queries that don’t touch TokuDB tables (and in some cases, even when TokuDB is kept completely idle).

Following earlier posts on replication and query cache improvements, this article will focus on a feature that can make Aurora more suitable for mission critical enterprise applications: near-instantaneous crash recovery.

While real-life performance is tricky to measure and may be evaluated differently in the context of different applications, downtime is a fairly straightforward thing to define and you most certainly want to avoid it regardless of what kind of service you're running. Let's take Aurora for a spin and see how it can make your life easier in this area.

Introduction This article doesn't pretend to be even remotely close to a proper technical description of MySQL startup and crash …

Following the recent post on replication performance and efficiency, I'd like to talk about another MySQL feature that has received some treatment in Aurora: the Query Cache.
We'll first discuss the limitations of "traditional" Query Cache implementation, we will then watch a sysbench read-only test take off and go from 250K reads per second to nearly 650K reads per second, all thanks to the improved Query Cache. Have fun!
Query Cache The MySQL Query Cache is a hugely useful feature that caches the result sets of SELECT statements in memory. It makes perfect sense in typical OLTP applications (especially web apps), where queries are quite repeatable because the same piece of data is often selected multiple times by many clients.
The following performance issues are associated with query cache implementation in vanilla …

I've tried out Idera's MySQL Query Explorer, a free tool and I found it easy to use and simple to setup. The only improvement that can be suggested is to add, on the technical requirements page, that your MySQL instance (MySQL Server version 5.5 and newer) must be running with the performance_schema turned on. Otherwise the tool will just display an empty grid. After you've successfully set