Showing entries 1 to 10 of 45
10 Older Entries »
Displaying posts with tag: Query Optimization (reset)
MySQL Marinate – So you want to learn MySQL! – START HERE

Want to learn or refresh yourself on MySQL? MySQL Marinate is the FREE virtual self-study group is for you!

MySQL Marinate quick links if you know what it is all about.

This is for beginners – If you have no experience with MySQL, or if you are a developer that wants to learn how to administer MySQL, or an administrator that wants to learn how to query MySQL, this course is what you want. If you are not a beginner, you will likely still learn some nuances, and it will be easy and fast to do. If you have absolutely zero experience with MySQL, this is perfect for you. The first few chapters walk you through getting and installing MySQL, so all you need is a computer and the book.

The format of a virtual self-study group is as follows:
Each participant acquires the same textbook ( …

[Read more]
Cost/Benefit Analysis of a MySQL Index

We all know that if we add a MySQL index to speed up a read, we end up making writes slower. How often do we do the analysis to look at how much more work is done?

Recently, a developer came to me and wanted to add an index to a very large table (hundreds of gigabytes) to speed up a query. We did some testing on a moderately used server:

Set long_query_time to 0 and turn slow query logging on
Turn slow query logging off after 30 minutes.

Add the index (was on a single field)

Repeat the slow query logging for 30 minutes at a similar time frame (in our case, we did middle of the day usage on a Tuesday and Wednesday, when the database is heavily used).

Then I looked at the write analysis – there were no DELETEs, no UPDATEs that updated the indexed field, and no UPDATEs that used the indexed field in the filtering. There were only INSERTs, and with the help of pt-query-digest, here’s what I found: …

[Read more]
Query Reviews (part 2): pt-query-digest

Query reviews (part 1): Overview

The 1st post in the series gave an overview of what a query review is and the value they can bring you. So now let’s talk about how one is done, specifically, how to do a query review using pt-query-digest.

The point of a query review is that it is a comprehensive review of queries. Imagine if you could get a list of all queries that run on your system, and then you systematically looked at each query to determine if it is optimized. That is the basic concept behind a query review.

So, how do you get a list of queries?

pt-query-digest can use a slow query log, binary log, general log or tcpdump. I usually use a slow query log with long_query_time set to 0, so I can capture all the …

[Read more]
Why does the MySQL optimizer not do what I think it should?

In May, I presented two talks – one called “Are you getting the best out of your indexes?” and “Optimizing Queries Using EXPLAIN”. I now have slides and video for both of them.

The first talk about indexing should probably be titled “Why is MySQL doing this?!!?!!?” It gives insight into why the MySQL optimizer chooses indexes that you do not expect; especially when it does not use an index you expect it to.

The talk has something for everyone – for beginners it explains B-trees and how they work, and for the more seasoned DBA it explains concepts like average value group size, and how the optimizer uses those concepts applied to metadata to make decisions.

Slides are at http://technocation.org/files/doc/2017_05_MySQLindexes.pdf.
Click the slide image below to go to the video at …

[Read more]
Introduction into storage engine troubleshooting: Q & A

In this blog, I will provide answers to the Q & A for the “Introduction into storage engine troubleshooting” webinar.

First, I want to thank everybody for attending the July 14 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: At which isolation level do 

pt-online-schema-change

 and 

pt-archive

  copy data from a table?

A: Both tools do not change the server’s default transaction isolation level. Use either

REPEATABLE READ

[Read more]
My Guidebook for Percona Live Amsterdam, 2015 - Part I

Unfortunately I am not going to Percona Live Amsterdam 2015 that starts next week. Somebody has to work and keep those customers happy who prefer to work as usual even during such a festival.

I am still asking myself: if I'd be able to go there, what tutorials and sessions I'd like to attend (besides those I'd present)? "All of them" is not a correct answer, as often great and useful sessions happens at the same time in different places. So, I decided to create a list of sessions for myself (as a reminder to check slides at least after they are published), and I'd like to share it.

I tried to pick up one session …

[Read more]
Efficient Use of Indexes in MySQL

The slides of “Efficient Use of Indexes in MySQL” talk we delivered on SFMySQL Meetup.

This is an introductory talk for developers on MySQL indexes. In my opinion it’s quite important to understand how InnoDB organizes data. If you know how MySQL accesses data it’s easier to write optimal queries.

When working with queries I imagine secondary indexes as a table with records sorted by secondary key fields. This is a powerful concept that helps to understand MySQL logic. It’s also easy to understand complex optimizations like loose index scan.

For example, for index (last_name, rank) the secondary index table looks like:

[Read more]
MySQL Query Profiling with Performance Schema

One of my favorite tools for query optimization is profiling. But recently I noticed this warning:

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1287 | '@@profiling' is deprecated and will be removed in a future release. |
+---------+------+----------------------------------------------------------------------+

After looking through certain documentation , I should indeed start using the Performance Schema to get this information.

Okay, so let’s give that a try.

I confirmed that I started MySQL 5.6.23 …

[Read more]
The MySQL Query Cache: How it works, plus workload impacts (good and bad)

Query caching is one of the prominent features in MySQL and a vital part of query optimization. It is important to know how it works as it has the potential to cause significant performance improvements – or a slowdown – of your workload.

The MySQL query cache is a global one shared among the sessions. It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory. It is important to have everything identical, no new comments, spaces, or most significantly differences in the WHERE clause. Basically when you trigger a select query, if it is available in the cache; it fetches from there or it considers the query as a new one and will go to the parser.

Even though it has some nice advantages, the MySQL query cache has its own downsides too. Well, let’s think about this: If you are frequently updating the table, you are then invalidating …

[Read more]
OurSQL Episode 182: Optimizing MariaDB

This week we discuss new optimizer improvements in MariaDB 10. Ear Candy is about the MariaDB threadpool, and At the Movies is Oracle's Tomas Ulin talking about raising the bar with MySQL.

Events
DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

Training
SkySQL Trainings

Tungsten University trainings

read more

Showing entries 1 to 10 of 45
10 Older Entries »