Featured
Percona Products
Percona Services
Use Cases
Sveta SmirnovaIn the second post in my series on open source databases on big machines, we’ll look at whether READ COMMITTED scales with multiple cores. The default transaction level for InnoDB is REPEATABLE READ. A more permissive level is READ COMMITTED, and is known to work well. While the REPEATABLE READ level maintains the transaction history up to the start of the transaction, […]
This blog compares how PostgreSQL and MySQL handle millions of queries per second. Anastasia: Can open source databases cope with millions of queries per second? Many open source advocates would answer “yes.” However, assertions aren’t enough for well-grounded proof. That’s why in this blog post, we share the benchmark testing results from Alexander Korotkov (CEO of […]
In this blog, I will provide answers to the Q & A for the WiredTiger B-Tree versus WiredTiger In-Memory webinar. First, I want to thank everybody for attending the October, 13 webinar. The recording and slides for the webinar are available here. Below is the list of questions that I wasn’t able to fully answer […]
In this blog, I will provide answers to the Q & A for the MySQL Replication Troubleshooting webinar. First, I want to thank everybody for attending the August 25 webinar. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses: Q: Hi Sveta. One question: how is […]
In this blog, I will provide answers to the Q & A for the InnoDB Troubleshooting webinar. First, I want to thank everybody for attending the August 11 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, […]
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 […]
In this blog, I will provide answers to the Q & A for the Troubleshooting configuration issues webinar. First, I want to thank you for attending the June, 9 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, […]
What is the difference between InnoDB locks and transaction isolation level? We’ll discuss it in this post. Recently I received a question from a user about one of my earlier blog posts. Since it wasn’t sent as a comment, I will answer it here. The question: > I am reading your article: > https://www.percona.com/resources/technical-presentations/troubleshooting-locking-issues-percona-mysql-webinar > Full table […]
In this blog, I provide answers to the Q & A for the Troubleshooting hardware resource usage webinar. First, I want to thank everybody who attended the May 26 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, […]
In this blog, I will provide answers to the Q & A for the Troubleshooting locking issues webinar. First, I want to thank you for attending the May, 12 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, […]
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 […]
In this blog, I will provide answers to the Webinar Q & A for Introduction to Troubleshooting Performance: What Affects Query Execution? First, I want to thank you for attending the April, 7 webinar. This webinar is the third in the “MySQL Troubleshooting” webinar series and last introductory webinar in the series. The recording and […]
This blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months. In this series, we’ve discussed everything unique to EXPLAIN FORMAT=JSON. I intentionally skipped a description of members such as table_name, access_type or select_id, which are not unique. In this series, I only mentioned in passing members that […]
In this blog, I will answer questions posed in my recent introduction to MySQL troubleshooting basic techniques webinar. Thank you for attending my March 10 MySQL troubleshooting webinar “Introduction to MySQL troubleshooting: basic techniques,” which is the second in the “MySQL Troubleshooting Webinar” series. The recording and slides for the webinar are available here. Here […]
Thank you for attending my March 2 MySQL troubleshooting webinar “Introduction to troubleshooting: How to create test setup,” which is the first in the “MySQL Troubleshooting Webinar” series. The recording and slides for the webinar are available here. Here is the list of your questions that I wasn’t able to answer during the webinar, with […]
Once again it’s time for another EXPLAIN FORMAT=JSON is cool! post. This post will discuss how EXPLAIN FORMAT=JSON allows the nested_loop command to make the JOIN operation hierarchy transparent. The regular EXPLAIN command lists each table that participates in a JOIN operation on a single row. This works perfectly for simple queries:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
mysql> explain select * from employees join titles join salariesG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 299379 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: titles partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 442724 filtered: 100.00 Extra: Using join buffer (Block Nested Loop) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2745434 filtered: 100.00 Extra: Using join buffer (Block Nested Loop) 3 rows in set, 1 warning (0.00 sec) |
You can see […]
Time for another entry in the EXPLAIN FORMAT=JSON is cool! series of blog posts. This time we’ll discuss how using EXPLAIN FORMAT=JSON allows you to see that cost_info knows why the optimizer prefers one index to another. Tables often have more than one index. Any of these indexes can be used to resolve query. The optimizer has […]
Time for another entry in the EXPLAIN FORMAT=JSON is cool! series. Today we’re going to look at how you can view the buffer result using JSON (instead of the regular EXPLAIN command. Regular EXPLAIN does not identify if SQL_BUFFER_RESULT was used at all. To demonstrate, let’s run this query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> explain select * from salariesG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2557022 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec) Note (Code 1003): /* select#1 */ select `employees`.`salaries`.`emp_no` AS `emp_no`,`employees`.`salaries`.`salary` AS `salary`,`employees`.`salaries`.`from_date` AS `from_date`,`employees`.`salaries`.`to_date` AS `to_date` from `employees`.`salaries` |
Now, let’s compare it to this query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> explain select sql_buffer_result * from salariesG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2557022 filtered: 100.00 Extra: Using temporary 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select sql_buffer_result `employees`.`salaries`.`emp_no` AS `emp_no`,`employees`.`salaries`.`salary` AS `salary`,`employees`.`salaries`.`from_date` AS `from_date`,`employees`.`salaries`.`to_date` AS `to_date` from `employees`.`salaries` |
Notice there […]
Ready for another post in the EXPLAIN FORMAT=JSON is Cool series! Great! This post will discuss how to see all the information that is contained in optimized queries with UNION using the union_result and query_specifications commands. When optimizing complicated queries with UNION, it is easy to get lost in the regular EXPLAIN output trying to identify which part […]
Over several previous blog posts, we’ve already discussed what information the EXPLAIN FORMAT=JSON output provides for some subqueries. You can review those discussions here, here and here. EXPLAIN FORMAT=JSON shows many details that you can’t get with other commands. Let’s now finish this topic and discuss the output for the rest of the subquery types. First, let’s look at the subquery […]