EmergencyEMERGENCY? Get 24/7 Help Now!

grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY

Latest MySQL Performance Blog posts - January 4, 2016 - 5:45pm

In the previous EXPLAIN FORMAT=JSON is Cool! series blog post, we discussed the  group_by_subqueries  member (which is child of grouping_operation). Let’s now focus on the grouping_operation  and other details of GROUP BY  processing.

grouping_operation simply shows the details of what happens when the GROUP BY clause is run:

mysql> explain format=json select dept_no from dept_emp group by dept_noG *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "14.40" }, "grouping_operation": { "using_filesort": false, "table": { "table_name": "dept_emp", "access_type": "range", "possible_keys": [ "PRIMARY", "emp_no", "dept_no" ], "key": "dept_no", "used_key_parts": [ "dept_no" ], "key_length": "4", "rows_examined_per_scan": 9, "rows_produced_per_join": 9, "filtered": "100.00", "using_index_for_group_by": true, "cost_info": { "read_cost": "12.60", "eval_cost": "1.80", "prefix_cost": "14.40", "data_read_per_join": "144" }, "used_columns": [ "emp_no", "dept_no" ] } } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`dept_emp`.`dept_no` AS `dept_no` from `employees`.`dept_emp` group by `employees`.`dept_emp`.`dept_no`

In the listing above, you can see which table was accessed by the GROUP BY operation, the access type, and if an index for GROUP BY was used.

In case of a simple JOIN  of two tables, grouping_operation is usually a parent for the nested_loop  object (which provides details on how the JOIN  proceeded):

mysql> explain format=json select de.dept_no, count(dm.emp_no) from dept_emp de join dept_manager dm using(emp_no) group by de.dept_noG *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "61.50" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "26.41" }, "nested_loop": [ { "table": { "table_name": "dm", "access_type": "index", "possible_keys": [ "PRIMARY", "emp_no" ], "key": "emp_no", "used_key_parts": [ "emp_no" ], "key_length": "4", "rows_examined_per_scan": 24, "rows_produced_per_join": 24, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "1.00", "eval_cost": "4.80", "prefix_cost": "5.80", "data_read_per_join": "384" }, "used_columns": [ "dept_no", "emp_no" ] } }, { "table": { "table_name": "de", "access_type": "ref", "possible_keys": [ "PRIMARY", "emp_no", "dept_no" ], "key": "emp_no", "used_key_parts": [ "emp_no" ], "key_length": "4", "ref": [ "employees.dm.emp_no" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 26, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "24.00", "eval_cost": "5.28", "prefix_cost": "35.09", "data_read_per_join": "422" }, "used_columns": [ "emp_no", "dept_no" ] } } ] } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`de`.`dept_no` AS `dept_no`,count(`employees`.`dm`.`emp_no`) AS `count(dm.emp_no)` from `employees`.`dept_emp` `de` join `employees`.`dept_manager` `dm` where (`employees`.`de`.`emp_no` = `employees`.`dm`.`emp_no`) group by `employees`.`de`.`dept_no`

Surprisingly, while many DISTINCT queries can be converted into equivalent queries with the GROUP BY clause, there is separate member (duplicates_removal) for processing it. Let’s see how it works with a simple query that performs the same job as the first one in this blog post:

mysql> explain format=json select distinct dept_no from dept_empG *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "14.40" }, "duplicates_removal": { "using_filesort": false, "table": { "table_name": "dept_emp", "access_type": "range", "possible_keys": [ "PRIMARY", "emp_no", "dept_no" ], "key": "dept_no", "used_key_parts": [ "dept_no" ], "key_length": "4", "rows_examined_per_scan": 9, "rows_produced_per_join": 9, "filtered": "100.00", "using_index_for_group_by": true, "cost_info": { "read_cost": "12.60", "eval_cost": "1.80", "prefix_cost": "14.40", "data_read_per_join": "144" }, "used_columns": [ "emp_no", "dept_no" ] } } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select distinct `employees`.`dept_emp`.`dept_no` AS `dept_no` from `employees`.`dept_emp`

You can see that the plan is almost same, but parent element for the plan is duplicates_removal.

The reason there are differences between these members can be seen if we change the second, more complicated query to use DISTINCT in place of GROUP BY:

mysql> explain format=json select distinct de.dept_no, count(dm.emp_no) from dept_emp de join dept_manager dm using(emp_no)G ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'employees.de.dept_no'; this is incompatible with sql_mode=only_full_group_by

This example shows that DISTINCT is not exactly same as GROUP BY, and can be used together  if we want to count the number of managers in each department (grouped by the year when the manager started working in the department). In this case, however, we are interested only in unique pairs of such dates and don’t want to see duplicates. Duplicates will appear if one person managed same department more than two years.

mysql> explain format=json select distinct de.dept_no, count(dm.emp_no) from dept_emp de join dept_manager dm using(emp_no) group by de.dept_no, year(de.from_date)G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "61.63" }, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "26.53" }, "nested_loop": [ { "table": { "table_name": "dm", "access_type": "index", "possible_keys": [ "PRIMARY", "emp_no" ], "key": "emp_no", "used_key_parts": [ "emp_no" ], "key_length": "4", "rows_examined_per_scan": 24, "rows_produced_per_join": 24, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "1.00", "eval_cost": "4.80", "prefix_cost": "5.80", "data_read_per_join": "384" }, "used_columns": [ "dept_no", "emp_no" ] } }, { "table": { "table_name": "de", "access_type": "ref", "possible_keys": [ "PRIMARY", "emp_no" ], "key": "PRIMARY", "used_key_parts": [ "emp_no" ], "key_length": "4", "ref": [ "employees.dm.emp_no" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 26, "filtered": "100.00", "cost_info": { "read_cost": "24.00", "eval_cost": "5.31", "prefix_cost": "35.11", "data_read_per_join": "424" }, "used_columns": [ "emp_no", "dept_no", "from_date" ] } } ] } } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select distinct `employees`.`de`.`dept_no` AS `dept_no`,count(`employees`.`dm`.`emp_no`) AS `count(dm.emp_no)` from `employees`.`dept_emp` `de` join `employees`.`dept_manager` `dm` where (`employees`.`de`.`emp_no` = `employees`.`dm`.`emp_no`) group by `employees`.`de`.`dept_no`,year(`employees`.`de`.`from_date`)

In this case, the member grouping_operation is a child of duplicates_removal and the temporary table used to store the result of GROUP BY  before removing the duplicates. A temporary table was also used to perform a filesort for the grouping operation itself.

Compare this with regular EXPLAIN output. EXPLAIN only shows that a temporary table was used, but does not provide insights on the operations for which it was used:

mysql> explain select distinct de.dept_no, count(dm.emp_no) from dept_emp de join dept_manager dm using(emp_no) group by de.dept_no, year(de.from_date)G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dm partitions: NULL type: index possible_keys: PRIMARY,emp_no key: emp_no key_len: 4 ref: NULL rows: 24 filtered: 100.00 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: de partitions: NULL type: ref possible_keys: PRIMARY,emp_no key: PRIMARY key_len: 4 ref: employees.dm.emp_no rows: 1 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.01 sec) Note (Code 1003): /* select#1 */ select distinct `employees`.`de`.`dept_no` AS `dept_no`,count(`employees`.`dm`.`emp_no`) AS `count(dm.emp_no)` from `employees`.`dept_emp` `de` join `employees`.`dept_manager` `dm` where (`employees`.`de`.`emp_no` = `employees`.`dm`.`emp_no`) group by `employees`.`de`.`dept_no`,year(`employees`.`de`.`from_date`)

Conclusion:  EXPLAIN FORMAT=JSON contains all the details about the GROUP BY and DISTINCT  optimizations.

The post grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY appeared first on MySQL Performance Blog.

Percona Server for MongoDB 3.0.8-1.2 is now available

Latest MySQL Performance Blog posts - January 4, 2016 - 11:37am

Percona is pleased to announce the release of Percona Server for MongoDB 3.0.8-1.2 on January 4, 2016. Download the latest version from the Percona web site or from the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.0 protocol and drivers. Based on MongoDB 3.0.8, it extends MongoDB with MongoRocks and PerconaFT storage engines, as well as features like external authentication and audit logging. Percona Server for MongoDB requires no changes to MongoDB applications or code.

 

New Features:

  • Added support for Ubuntu 15.10 (Wily Werewolf)
  • Contains all changes and fixes from MongoDB 3.0.8

Percona Server for MongoDB 3.0.8-1.2 release notes are available in the official documentation.

The post Percona Server for MongoDB 3.0.8-1.2 is now available appeared first on MySQL Performance Blog.

innobackupex does not seem to read datadir correctly

Lastest Forum Posts - January 4, 2016 - 11:21am
Hi,

I have a cluster setup with just 2 nodes, and I am using xtrabackup-v2 as the wsrep_sst_method. Version info is:

- mysql Ver 15.1 Distrib 10.1.10-MariaDB, for Linux (x86_64) using readline 5.1
- xtrabackup version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)

On node 1, I can bootstrap mysql successfully. On node 2, I am running into problem with the move-back step. From the mariadb.log, I can see that the innobackupex --move-back step is failing. Under the innobackup.move.log, I am seeing a strange error: - innobackupex version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)
- Error: Move file ib_logfile0 to ./ib_logfile0 failed: Destination file exists
I looked into the source code of xtrabackup, and it looks to me like the script didn't read the datadir conf var correctly. It's supposed to move the ib_logfile0 to /var/lib/mysql/ib_logfile0 but instead it seem to read datadir as "./".

The server.conf is located under my /etc/my.cnf.d directory. Here is the output of mysqld --print-defaults
mysqld would have been started with the following arguments:
--log-error=/var/log/mariadb.log --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --binlog_format=ROW --default_storage_engine=innodb --innodb_autoinc_lock_mode=2 --wsrep_on=ON --innodb_doublewrite=1 --wsrep_provider=/usr/lib64/galera/libgalera_smm.so --wsrep_provider_options=gmcast.listen_addr=tcp://0.0.0.0:4567; ist.recv_addr=172.20.222.62:4568 --wsrep_cluster_address=gcomm://172.20.222.61,172.20.222.62 --wsrep_sst_receive_address=172.20.222.62:4444 --wsrep_node_address=172.20.222.62:4567 --wsrep_cluster_name=satelliteDatabaseCluster --wsrep_node_name=dlo-lab62 --wsrep_sst_method=xtrabackup-v2 --wsrep_sst_auth=root:Test123 --wsrep_debug=ON --bind-address=0.0.0.0 --query_cache_size=0



=========== mariadb.log
WSREP_SST: [INFO] Streaming with xbstream (20160104 10:57:13.854)
WSREP_SST: [INFO] Using socat as streamer (20160104 10:57:13.855)
WSREP_SST: [INFO] Evaluating timeout -s9 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160104 10:57:13.879)
2016-01-04 10:57:14 140293202687744 [Note] WSREP: Prepared SST request: xtrabackup-v2|172.20.222.62:4444/xtrabackup_sst//1
2016-01-04 10:57:14 140293202687744 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2016-01-04 10:57:14 140293202687744 [Note] WSREP: REPL Protocols: 7 (3, 2)
2016-01-04 10:57:14 140292979345152 [Note] WSREP: Service thread queue flushed.
2016-01-04 10:57:14 140293202687744 [Note] WSREP: Assign initial position for certification: 352, protocol version: 3
2016-01-04 10:57:14 140292979345152 [Note] WSREP: Service thread queue flushed.
2016-01-04 10:57:14 140293202687744 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (805ae278-b2ac-11e5-8a4f-8b035dfc5817): 1 (Operation not permitted)
at galera/src/replicator_str.cpprepare_for_IST():456. IST will be unavailable.
2016-01-04 10:57:14 140292910999296 [Note] WSREP: Member 1.0 (dlo-lab62) requested state transfer from '*any*'. Selected 0.0 (dlo-lab61)(SYNCED) as donor.
2016-01-04 10:57:14 140292910999296 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 352)
2016-01-04 10:57:14 140293202687744 [Note] WSREP: Requesting state transfer: success, donor: 0
WSREP_SST: [INFO] Proceeding with SST (20160104 10:57:14.263)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160104 10:57:14.264)
WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20160104 10:57:14.266)
removed `/var/lib/mysql/mysql.sock'
removed `/var/lib/mysql/ib_logfile1'
removed `/var/lib/mysql/aria_log.00000001'
removed `/var/lib/mysql/aria_log_control'
removed `/var/lib/mysql/ibdata1'
removed `/var/lib/mysql/ib_logfile0'
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160104 10:57:14.298)
2016-01-04 10:57:16 140292921489152 [Note] WSREP: (f6dbe707, 'tcp://0.0.0.0:4567') turning message relay requesting off
2016-01-04 10:57:28 140292910999296 [Note] WSREP: 0.0 (dlo-lab61): State transfer to 1.0 (dlo-lab62) complete.
2016-01-04 10:57:28 140292910999296 [Note] WSREP: Member 0.0 (dlo-lab61) synced with group.
WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql//.sst (20160104 10:57:30.218)
WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20160104 10:57:30.224)
rm: cannot remove `/var/lib/mysql//innobackup.prepare.log': No such file or directory
rm: cannot remove `/var/lib/mysql//innobackup.move.log': No such file or directory
WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ (20160104 10:57:33.948)
WSREP_SST: [INFO] DATA is /var/lib/mysql//.sst (20160104 10:57:33.950)
WSREP_SST: [INFO] Evaluating innobackupex --defaults-file=/etc/my.cnf --no-version-check --move-back --force-non-empty-directories ${DATA} &> ${DATA}/innobackup.move.log (20160104 10:57:33.951)
WSREP_SST: [ERROR] Cleanup after exit with status:1 (20160104 10:57:33.958)
2016-01-04 10:57:33 140292879546112 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '172.20.222.62:4444' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --parent '3840' '' : 1 (Operation not permitted)
2016-01-04 10:57:33 140292879546112 [ERROR] WSREP: Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script.
2016-01-04 10:57:33 140293202995232 [ERROR] WSREP: SST failed: 1 (Operation not permitted)
2016-01-04 10:57:33 140293202995232 [ERROR] Aborting

Error in my_thread_global_end(): 1 threads didn't exit


=========== innobackup.move.log
160104 10:57:33 innobackupex: Starting the move-back operation

IMPORTANT: Please check that the move-back run completes successfully.
At the end of a successful move-back run innobackupex
prints "completed OK!".

innobackupex version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)
Error: Move file ib_logfile0 to ./ib_logfile0 failed: Destination file exists
18:57:33 UTC - xtrabackup got signal 11 ;
This could be because you hit a bug or data is corrupted.
This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x10000
innobackupex(my_print_stacktrace+0x2e) [0x93827e]
innobackupex(handle_fatal_signal+0x273) [0x81b483]
/lib64/libpthread.so.0(+0xf790) [0x7fded4a86790]
/lib64/libc.so.6(cfree+0x1c) [0x7fded30eb93c]
innobackupex(copy_back()+0x377) [0x5a1767]
innobackupex(main+0xacb) [0x591f9b]
/lib64/libc.so.6(__libc_start_main+0xfd) [0x7fded308ed5d]
innobackupex() [0x585ea9]


=============== server.conf
# Ansible managed: /Users/hvo/Workspaces/Eclipse_Helios/csw-cssm-engine/ha/deployment/ansible/roles/mariadb-prep/templates/mariadb_server.cnf modified on 2016-01-03 21:37:29 by hvo on HVO-M-M09J

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#

# this is read by the standalone daemon and embedded servers
[server]
log-error=/var/log/mariadb.log

# this is only for the mysqld standalone daemon
[mysqld]
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql

#
# * Galera-related settings
#
[galera]
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_on=ON
innodb_doublewrite=1
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_provider_options=gmcast.listen_addr=tcp://0.0.0.0:4567; ist.recv_addr=172.20.222.62:4568

# for IPv6 i need to insert a bracket around the address in the gcomm setting. look for it.
wsrep_cluster_address="gcomm://172.20.222.61,172.20.222.62"

wsrep_sst_receive_address=172.20.222.62:4444
wsrep_node_address=172.20.222.62:4567

wsrep_cluster_name=satelliteDatabaseCluster
wsrep_node_name=dlo-lab62
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:Test123
wsrep_debug=ON

bind-address=0.0.0.0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-10.1]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
query_cache_size=0

[mariadb-10.1]





Thank you for your help,

Huey

MongoDb Migration

Lastest Forum Posts - January 4, 2016 - 7:27am
Hey there
I wanted to know, if we have to do something to migrate data from engine MMAPv1 to perconaFT?

How can restore backup without doing Original data directory 'mysql' empty.

Lastest Forum Posts - January 1, 2016 - 4:08am
Hello All,

I used restore DB backup script : https://gist.github.com/DamianCaruso/931358?signup=true

As I am new to Xtrabackup I am facing some difficulties on how to restore an incremental backup. In more details:

Full backup:
/home/navman/../data/backups/full/2015-12-23_23-52-31/

Incremental backup:
/home/navman/../data/backups/incr/2015-12-23_23-52-31/2015-12-23_23-52-41/
/home/navman/../data/backups/incr/2015-12-23_23-52-31/2015-12-24_22-00-03/
/home/navman/../data/backups/incr/2015-12-23_23-52-31/2015-12-25_22-00-02/

After preparing my incremental backups

I am facing below error. In MySQL I have multiple databases running in MySQL. So without stop MySQL and without doing Original data directory '/var/lib/mysql' empty. How can restore db backup for single database?

innobackupex-1.5.1 failed:

---------- ERROR OUTPUT from innobackupex-1.5.1 ----------

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/ubuntu

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex-1.5.1
prints "completed OK!".

innobackupex-1.5.1: Error: Original data directory '/var/lib/mysql' is not empty! at /usr/b

Percona Toolkit Getting Started With CygWin and Windows

Lastest Forum Posts - December 31, 2015 - 11:39am
I'd like to use Percona Toolkit with a MySQL Windows install. I've read that it will work with CygWin. I have that installed as well. I have one mysql dev install on Win7 and a prod install on WindowsServer2013 so lets try to discuss for both. First, Where should I unzip the pt folder? I currently have it as a subfolder in my mysql directory. Assuming I navigate to it via CygWin, can you give me an example to call one of the tools via CygWin? Thanks!

Apache/nginx template, request for second?

Lastest Forum Posts - December 31, 2015 - 10:05am
Hello, first of all, thank you very much for the plugins, they are very useful

I have a question about monitoring templates for nginx and apache, does the data shown in the graphs on request correspond to requests for seconds?

Percona XtraBackup for Mysql 5.1.49-3

Lastest Forum Posts - December 31, 2015 - 2:34am
I'm trying to try out Percona XtraBackup on my server, but i have some doubts about:
what is the right version to download and specially for Debian6 architecture x86_64?
I'm told about before installing something i need to check dependencies, so installing whatever i need would it damage anything on my server or would update something like Mysql or Apache? (my server has Mysql and PHP), because would be terrible if XtraBackup update something.
Do i need any special configuration on my server after installing?

Database Performance Webinar: Tired of MySQL Making You Wait?

Latest MySQL Performance Blog posts - December 30, 2015 - 12:05pm

Too often developers and DBAs struggle to pinpoint the root cause of MySQL database performance issues, and then spend too much time in trying to fix them. Wouldn’t it be great to bypass wasted guesswork and get right to the issue?

In our upcoming webinar Tired of MySQL Making You Wait? we’re going to help you discover how to significantly increase the performance of your applications and reduce database response time.

In this webinar, Principal Architect Alexander Rubin and Database Evangelist Janis Griffin will provide the key steps needed to identify, prioritize, and improve query performance.

They will discuss the following topics:

  • Wait time analytics using Performance / Information schemas
  • Monitoring for performance using DPA
  • Explaining plan operations focusing on temporary tables and filesort
  • Using indexes to optimize your queries
  • Using loose and tight index scans in MySQL

WHEN:

Thursday, January 7, 2016 10:00am Pacific Standard Time (UTC – 8)

PRESENTERS:

Alexander RubinPrincipal Consultant, Percona

Janis GriffinDatabase Evangelist, SolarWinds

Register now!

Percona is the only company that delivers enterprise-class software, support, consulting and managed services solutions for both MySQL and MongoDB® across traditional and cloud-based platforms that maximize application performance while streamlining database efficiencies.

Percona’s industry-recognized performance experts can maximize your database, server and application performance, lower infrastructure costs, and provide capacity and scalability planning for future growth.

The post Database Performance Webinar: Tired of MySQL Making You Wait? appeared first on MySQL Performance Blog.

Percona Xtrabackup for MySQL 5.6

Lastest Forum Posts - December 30, 2015 - 7:59am
Hi,
We have MySQL 5.6 setup on Windows.
I understand that Xtrabackup is currently not supported for Windows. However, I see the Xtrabackup 1.6 version executables available online ( https://www.percona.com/downloads/Xt...traBackup-1.6/ ). Can you please share the Windows binaries for the latest version of Xtrabackup if possible. Else, can you please share the build scripts for Windows so that I can build it myself in our environment.



Appreciate your help!

TokuDB Development Gone Quiet

Lastest Forum Posts - December 29, 2015 - 4:14pm
Hiyas,

I'm concerned that very little news has been heard from the TokuDB development team. According to the tokudb-engine Github page, no commits have been made since the 1st of September 2015. Is it still being developed?

In MySQL 5.7, InnoDB has been enhanced to support very useful features including a JSON datatype, Spatial Indexing and Indexable Virtual Columns. However, InnoDB still does not support online addition/removal of columns - something that TokuDB handles brilliantly. This has greatly eased maintenance work in our production systems. InnoDB has also added support for page compression. Haven't had a change to test it yet, but I doubt it would perform as well as TokuDB's as InnoDB does not utilise TokuDB's fractal tree indexes.

I am hoping to find out the TokuDB team's plans for implementing the following features:

1. JSON Datatype

2. Indexable Virtual Columns

3. Spatial Indexing

4. Official Galera support

5. Fulltext Indexes


TokuDB and fractal tree indexes are such great technology and add so much value to MySQL. It would really suck if they were not allowed to reach their full potential.

2016 Percona Live Tutorials Schedule is UP!

Latest MySQL Performance Blog posts - December 29, 2015 - 1:24pm

We are excited to announce that the tutorial schedule for the Percona Live Data Performance Conference 2016 is up!

The schedule shows all the details for each of our informative and enlightening Percona Live tutorial sessions, including insights into InnoDB, MySQL 5.7, MongoDB 3.2 and RocksDB. These tutorials are a must for any data performance professional!

The Percona Live Data Performance Conference is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The sneak peek schedule for Percona Live 2016 has also been posted! The Conference will feature a variety of formal tracks and sessions related to MySQL, NoSQL and Data in the Cloud. With over 150 slots to fill, there will be no shortage of great content this year.

The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

Just a reminder to everyone out there: our Super Saver discount rate for the Percona Live Data Performance and Expo 2016 is only available ‘til December 31 11:30pm PST! This rate gets you all the excellent and amazing opportunities that Percona Live offers, at the lowest price possible!

Become a conference sponsor! We have sponsorship opportunities available for this annual MySQL, NoSQL and Data in the Cloud event. Sponsors become a part of a dynamic and growing ecosystem and interact with more than 1,000 DBAs, sysadmins, developers, CTOs, CEOs, business managers, technology evangelists, solutions vendors, and entrepreneurs who attend the event.

Click through to the tutorial link right now, look them over, and pick which sessions you want to attend!

The post 2016 Percona Live Tutorials Schedule is UP! appeared first on MySQL Performance Blog.

EXPLAIN FORMAT=JSON: order_by_subqueries, group_by_subqueries details on subqueries in ORDER BY and GROUP BY

Latest MySQL Performance Blog posts - December 29, 2015 - 12:39pm

Another post in the EXPLAIN FORMAT=JSON is Cool! series! In this post, we’ll discuss how the EXPLAIN FORMAT=JSON provides optimization details for ORDER BY and  GROUP BY operations in conjunction with order_by_subqueries and  group_by_subqueries. 

EXPLAIN FORMAT=JSON can print details on how a subquery in ORDER BY is optimized:

mysql> explain format=json select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select emp_no limit 1)G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "60833.60" }, "ordering_operation": { "using_filesort": true, "table": { "table_name": "employees", "access_type": "ALL", "rows_examined_per_scan": 299843, "rows_produced_per_join": 299843, "filtered": "100.00", "cost_info": { "read_cost": "865.00", "eval_cost": "59968.60", "prefix_cost": "60833.60", "data_read_per_join": "13M" }, "used_columns": [ "emp_no", "first_name", "last_name" ] }, "order_by_subqueries": [ { "dependent": true, "cacheable": false, "query_block": { "select_id": 2, "message": "No tables used" } } ] } } } 1 row in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'employees.employees.emp_no' of SELECT #2 was resolved in SELECT #1 Note (Code 1003): /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,concat(`employees`.`employees`.`first_name`,' ',`employees`.`employees`.`last_name`) AS `f2` from `employees`.`employees` order by (/* select#2 */ select `employees`.`employees`.`emp_no` limit 1)

The above code shows member ordering_operation of query_block  (which includes the order_by_subqueries array) with information on how the subquery in ORDER BY  was optimized.

This is a simple example. In real life you can have larger subqueries in the ORDER BY  clause. For example, take this more complicated and slightly crazy query:

select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select dept_no as c from salaries join dept_emp using (emp_no) group by dept_no)

Run a regular EXPLAIN on it. If we imagine this is a regular subquery, we won’t know if it can be cached or would be executed for each row sorted.

mysql> explain select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select dept_no as c from salaries join dept_emp using (emp_no) group by dept_no)G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: employees partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 299843 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: dept_emp partitions: NULL type: index possible_keys: PRIMARY,emp_no,dept_no key: dept_no key_len: 4 ref: NULL rows: 331215 filtered: 100.00 Extra: Using index *************************** 3. row *************************** id: 2 select_type: SUBQUERY table: salaries partitions: NULL type: ref possible_keys: PRIMARY,emp_no key: emp_no key_len: 4 ref: employees.dept_emp.emp_no rows: 10 filtered: 100.00 Extra: Using index 3 rows in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,concat(`employees`.`employees`.`first_name`,' ',`employees`.`employees`.`last_name`) AS `f2` from `employees`.`employees` order by (/* select#2 */ select `employees`.`dept_emp`.`dept_no` AS `c` from `employees`.`salaries` join `employees`.`dept_emp` where (`employees`.`salaries`.`emp_no` = `employees`.`dept_emp`.`emp_no`) group by `employees`.`dept_emp`.`dept_no`)

EXPLAIN FORMAT=JSON  provides a completely different picture:

mysql> explain format=json select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select dept_no as c from salaries join dept_emp using (emp_no) group by dept_no)G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "60833.60" }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "employees", "access_type": "ALL", "rows_examined_per_scan": 299843, "rows_produced_per_join": 299843, "filtered": "100.00", "cost_info": { "read_cost": "865.00", "eval_cost": "59968.60", "prefix_cost": "60833.60", "data_read_per_join": "13M" }, "used_columns": [ "emp_no", "first_name", "last_name" ] }, "optimized_away_subqueries": [ { "dependent": false, "cacheable": true, "query_block": { "select_id": 2, "cost_info": { "query_cost": "1082124.21" }, "grouping_operation": { "using_filesort": false, "nested_loop": [ { "table": { "table_name": "dept_emp", "access_type": "index", "possible_keys": [ "PRIMARY", "emp_no", "dept_no" ], "key": "dept_no", "used_key_parts": [ "dept_no" ], "key_length": "4", "rows_examined_per_scan": 331215, "rows_produced_per_join": 331215, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "673.00", "eval_cost": "66243.00", "prefix_cost": "66916.00", "data_read_per_join": "5M" }, "used_columns": [ "emp_no", "dept_no" ] } }, { "table": { "table_name": "salaries", "access_type": "ref", "possible_keys": [ "PRIMARY", "emp_no" ], "key": "emp_no", "used_key_parts": [ "emp_no" ], "key_length": "4", "ref": [ "employees.dept_emp.emp_no" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 3399374, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "335333.33", "eval_cost": "679874.87", "prefix_cost": "1082124.21", "data_read_per_join": "51M" }, "used_columns": [ "emp_no", "from_date" ] } } ] } } } ] } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,concat(`employees`.`employees`.`first_name`,' ',`employees`.`employees`.`last_name`) AS `f2` from `employees`.`employees` order by (/* select#2 */ select `employees`.`dept_emp`.`dept_no` AS `c` from `employees`.`salaries` join `employees`.`dept_emp` where (`employees`.`salaries`.`emp_no` = `employees`.`dept_emp`.`emp_no`) group by `employees`.`dept_emp`.`dept_no`)

We see that the subquery was optimized away: member optimized_away_subqueries exists, but there is no order_by_subqueries in the ordering_operation object. We can also see that the subquery was cached: "cacheable": true.

EXPLAIN FORMAT=JSON also provides information about subqueries in the GROUP BY clause. It uses the group_by_subqueries array in the grouping_operation  member for this purpose.

mysql> explain format=json select count(emp_no) from salaries group by salary > ALL (select s/c as avg_salary from (select dept_no, sum(salary) as s, count(emp_no) as c from salaries join dept_emp using (emp_no) group by dept_no) t)G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3412037.60" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "2838638.00" }, "table": { "table_name": "salaries", "access_type": "ALL", "rows_examined_per_scan": 2838638, "rows_produced_per_join": 2838638, "filtered": "100.00", "cost_info": { "read_cost": "5672.00", "eval_cost": "567727.60", "prefix_cost": "573399.60", "data_read_per_join": "43M" }, "used_columns": [ "emp_no", "salary", "from_date" ] }, "group_by_subqueries": [ { "dependent": true, "cacheable": false, "query_block": { "select_id": 2, "cost_info": { "query_cost": "881731.00" }, "table": { "table_name": "t", "access_type": "ALL", "rows_examined_per_scan": 3526884, "rows_produced_per_join": 3526884, "filtered": "100.00", "cost_info": { "read_cost": "176354.20", "eval_cost": "705376.80", "prefix_cost": "881731.00", "data_read_per_join": "134M" }, "used_columns": [ "dept_no", "s", "c" ], "attached_condition": "((<cache>(`employees`.`salaries`.`salary`) <= (`t`.`s` / `t`.`c`)) or isnull((`t`.`s` / `t`.`c`)))", "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 3, "cost_info": { "query_cost": "1106758.94" }, "grouping_operation": { "using_filesort": false, "nested_loop": [ { "table": { "table_name": "dept_emp", "access_type": "index", "possible_keys": [ "PRIMARY", "emp_no", "dept_no" ], "key": "dept_no", "used_key_parts": [ "dept_no" ], "key_length": "4", "rows_examined_per_scan": 331215, "rows_produced_per_join": 331215, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "673.00", "eval_cost": "66243.00", "prefix_cost": "66916.00", "data_read_per_join": "5M" }, "used_columns": [ "emp_no", "dept_no" ] } }, { "table": { "table_name": "salaries", "access_type": "ref", "possible_keys": [ "PRIMARY", "emp_no" ], "key": "PRIMARY", "used_key_parts": [ "emp_no" ], "key_length": "4", "ref": [ "employees.dept_emp.emp_no" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 3526884, "filtered": "100.00", "cost_info": { "read_cost": "334466.14", "eval_cost": "705376.80", "prefix_cost": "1106758.95", "data_read_per_join": "53M" }, "used_columns": [ "emp_no", "salary", "from_date" ] } } ] } } } } } } ] } } } 1 row in set, 1 warning (0.01 sec) Note (Code 1003): /* select#1 */ select count(`employees`.`salaries`.`emp_no`) AS `count(emp_no)` from `employees`.`salaries` group by <not>(<in_optimizer>(`employees`.`salaries`.`salary`,<exists>(/* select#2 */ select 1 from (/* select#3 */ select `employees`.`dept_emp`.`dept_no` AS `dept_no`,sum(`employees`.`salaries`.`salary`) AS `s`,count(`employees`.`salaries`.`emp_no`) AS `c` from `employees`.`salaries` join `employees`.`dept_emp` where (`employees`.`salaries`.`emp_no` = `employees`.`dept_emp`.`emp_no`) group by `employees`.`dept_emp`.`dept_no`) `t` where ((<cache>(`employees`.`salaries`.`salary`) <= (`t`.`s` / `t`.`c`)) or isnull((`t`.`s` / `t`.`c`))) having <is_not_null_test>((`t`.`s` / `t`.`c`)))))

Again, this output gives a clear view of query optimization: subquery in GROUP BY itself cannot be optimized, cached or converted into temporary table, but the subquery inside the subquery (select dept_no, sum(salary) as s, count(emp_no) as c from salaries join dept_emp using (emp_no) group by dept_no) could be materialized into a temporary table and cached.

A regular EXPLAIN command does not provide such details:

mysql> explain select count(emp_no) from salaries group by salary > ALL (select s/c as avg_salary from (select dept_no, sum(salary) as s, count(emp_no) as c from salaries join dept_emp using (emp_no) group by dept_no) t)G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2838638 filtered: 100.00 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: <derived3> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3526884 filtered: 100.00 Extra: Using where *************************** 3. row *************************** id: 3 select_type: DERIVED table: dept_emp partitions: NULL type: index possible_keys: PRIMARY,emp_no,dept_no key: dept_no key_len: 4 ref: NULL rows: 331215 filtered: 100.00 Extra: Using index *************************** 4. row *************************** id: 3 select_type: DERIVED table: salaries partitions: NULL type: ref possible_keys: PRIMARY,emp_no key: PRIMARY key_len: 4 ref: employees.dept_emp.emp_no rows: 10 filtered: 100.00 Extra: NULL 4 rows in set, 1 warning (0.01 sec) Note (Code 1003): /* select#1 */ select count(`employees`.`salaries`.`emp_no`) AS `count(emp_no)` from `employees`.`salaries` group by <not>(<in_optimizer>(`employees`.`salaries`.`salary`,<exists>(/* select#2 */ select 1 from (/* select#3 */ select `employees`.`dept_emp`.`dept_no` AS `dept_no`,sum(`employees`.`salaries`.`salary`) AS `s`,count(`employees`.`salaries`.`emp_no`) AS `c` from `employees`.`salaries` join `employees`.`dept_emp` where (`employees`.`salaries`.`emp_no` = `employees`.`dept_emp`.`emp_no`) group by `employees`.`dept_emp`.`dept_no`) `t` where ((<cache>(`employees`.`salaries`.`salary`) <= (`t`.`s` / `t`.`c`)) or isnull((`t`.`s` / `t`.`c`))) having <is_not_null_test>((`t`.`s` / `t`.`c`)))))

Most importantly, we cannot guess from the output if the DERIVED subquery can be cached.

Conlcusion: EXPLAIN FORMAT=JSON  provides details on how subqueries in ORDER BY and GROUP BY clauses are optimized.

The post EXPLAIN FORMAT=JSON: order_by_subqueries, group_by_subqueries details on subqueries in ORDER BY and GROUP BY appeared first on MySQL Performance Blog.

backup user used for mysqldump

Lastest Forum Posts - December 29, 2015 - 9:48am
I have created backup user with limited privileges which will be used by mysqldump.

mysql> show grants for 'backup'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, FILE, SHOW DATABASES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost'
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

However, I also want do dump mysql user grants and user and I have written simple sql statement to dump grants statements

SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants_usingcommand.sql

When we execute above SQL using backup user, the sql statements which will create users and grants, it does not have password information. Password is replaced by secret e.g IDENTIFIED BY PASSWORD <secret>

When I run above SQL using root, hashed password is getting displayed. Question is what extra permission do I need to give to backup user so that it can also provide password information required to create user

I have granted SUPER privilege and password is getting displayed for backup user. I want to give only minimum required privileges and not sure whether SUPER is good idea. Any suggestions.

Thanks a lot.

Joining second node without cluster lock

Lastest Forum Posts - December 29, 2015 - 7:09am
Is there a way, when recovering from failed node scenarios, to recover from being reduced to a single node without locking the cluster when the next node rejoins?

I have a 3 node cluster with the addition of an arbitrator, and a node had to be taken out for maintenance. When it went to rejoin, it synched from one of the two remaining nodes, hung, and crashed, and took the donor node with it. This has left me in a single node state - and I;d like to be abel to get back to multi-node without taking an outage.

I'm using xtrabackup and xbstream as my SST method, but I've noticed when two nodes synch this way, it locks the donor node. Is there a way around this? Given enough nodes (even having just two already in synch) adding a third node allows one node to remain active and serve requests. But what do you do when you're down to one node and need to recover without locking the whole cluster for an hour while the data synchs?

When will xtrabackup support MariaDB 10.1 encryption?

Lastest Forum Posts - December 28, 2015 - 7:54pm
Encryption of tables and tablespaces was added in MariaDB 10.1.3. Do we have a plan to support it?

innobackupex fails with 'no space on device' but completes with OK message

Lastest Forum Posts - December 28, 2015 - 3:05pm
Ran innobackupex with the command below. Device really did run out of space during rsync, but still completed with status of OK. This is not supposed to happen it should not end with OK message if something went wrong. My automation depends on not finding OK as indicated in the manual. It indicates it will only print OK if it really worked.

This seems like a bug, am I missing something here?


Command line: innobackupex --defaults-extra-file=/etc/mysql.cnf --backup --compress --parallel=4 --rsync --incremental --incremental-basedir=./full_backup_20151216/2015-12-27_01-10-11 ./full_backup_20151216

Log.....


151228 01:10:11 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

151228 01:10:11 version_check Connected to MySQL server
151228 01:10:11 version_check Executing a version check against the server...
151228 01:10:11 version_check Done.
Using server version 5.6.26-74.0-log
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
incremental backup from 14900595279201 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /srv/mysql
xtrabackup: open files limit requested 0, set to 16000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 2147483648
151228 01:10:11 >> log scanned up to (14900595284937)
.
. lots more log here removed for brevity...
.
151228 01:15:43 >> log scanned up to (14900595284977)
151228 01:15:44 >> log scanned up to (14900595284977)
rsync: writefd_unbuffered failed to write 4 bytes to socket [sender]: Broken pipe (32)
rsync: close failed on "/backup-data/marsdb/innobackupex/mysql/full_backup_20151216/2015-12-28_01-10-11/cosmic/.cosmic_variant.MYD.fHzcJi": No space left on device (28)
rsync error: error in file IO (code 11) at receiver.c(730) [receiver=3.0.6]
rsync: connection unexpectedly closed (62 bytes received so far) [sender]
rsync error: error in rsync protocol data stream (code 12) at io.c(600) [sender=3.0.6]
151228 01:15:45 Error: rsync failed with error code 1
151228 01:15:45 Executing LOCK BINLOG FOR BACKUP...
151228 01:15:45 [00] Compressing xtrabackup_binlog_info
151228 01:15:45 [00] ...done
151228 01:15:45 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '14900595284977'
xtrabackup: Stopping log copying thread.
.151228 01:15:45 >> log scanned up to (14900595284977)

151228 01:15:45 Executing UNLOCK BINLOG
151228 01:15:45 Executing UNLOCK TABLES
151228 01:15:45 All tables unlocked
151228 01:15:45 Backup created in directory '/backup-data/marsdb/innobackupex/mysql/full_backup_20151216/2015-12-28_01-10-11'
MySQL binlog position: filename 'marsdb-bin.000210', position '1043201980'
151228 01:15:45 [00] Compressing backup-my.cnf
151228 01:15:45 [00] ...done
151228 01:15:45 [00] Compressing xtrabackup_info
151228 01:15:45 [00] ...done
xtrabackup: Transaction log of lsn (14900595284937) to (14900595284977) was copied.
151228 01:15:45 completed OK!

real 5m33.759s
user 0m47.671s
sys 3m8.492s

EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

Latest MySQL Performance Blog posts - December 28, 2015 - 11:20am

The previous post in the EXPLAIN FORMAT=JSON is Cool! series showed an example of the query select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null), where the subquery was materialized into a temporary table and then joined with the outer query. This is known as a semi-join optimization. But what happens if we turn off this optimization?

EXPLAIN FORMAT=JSON can help us with this investigation too.

First lets look at the original output again:

mysql> explain format=json select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null)G *************************** 1. row *************************** EXPLAIN: {   "query_block": {     "select_id": 1,     "cost_info": {       "query_cost": "16.72"     },     "nested_loop": [       {         "table": {           "table_name": "departments",           <skipped>       },       {         "table": {           "table_name": "<subquery2>",           "access_type": "eq_ref",           "key": "<auto_key>",           "key_length": "4",           "ref": [             "employees.departments.dept_no"           ],           "rows_examined_per_scan": 1,           "materialized_from_subquery": {             "using_temporary_table": true,             "query_block": {               "table": {                 "table_name": "dept_manager",                 "access_type": "ALL",                 "possible_keys": [                   "dept_no"                 ],                 "rows_examined_per_scan": 24,                 "rows_produced_per_join": 21,                 "filtered": "90.00",                 "cost_info": {                   "read_cost": "1.48",                   "eval_cost": "4.32",                   "prefix_cost": "5.80",                   "data_read_per_join": "345"                 },                 "used_columns": [                   "dept_no",                   "to_date"                 ],                 "attached_condition": "(`employees`.`dept_manager`.`to_date` is not null)"               }             }           }         }       }     ]   } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`departments`.`dept_name` AS `dept_name` from `employees`.`departments` semi join (`employees`.`dept_manager`) where ((`<subquery2>`.`dept_no` = `employees`.`departments`.`dept_no`) and (`employees`.`dept_manager`.`to_date` is not null))

To repeat what happened here: the subquery was materialized into a  temporary table, then  joined with the departments table. Semi-join optimization is ON by default (as would be most likely without intervention).

What happens if we temporarily turn semi-join optimization OFF?

mysql> set optimizer_switch="semijoin=off"; Query OK, 0 rows affected (0.00 sec)

And then execute EXPLAIN one more time:

mysql> explain format=json select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null) G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2.80" }, "table": { "table_name": "departments", "access_type": "index", "key": "dept_name", "used_key_parts": [ "dept_name" ], "key_length": "42", "rows_examined_per_scan": 9, "rows_produced_per_join": 9, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "1.00", "eval_cost": "1.80", "prefix_cost": "2.80", "data_read_per_join": "432" }, "used_columns": [ "dept_no", "dept_name" ], "attached_condition": "<in_optimizer>(`employees`.`departments`.`dept_no`,`employees`.`departments`.`dept_no` in ( <materialize> (/* select#2 */ select `employees`.`dept_manager`.`dept_no` from `employees`.`dept_manager` where (`employees`.`dept_manager`.`to_date` is not null) ), <primary_index_lookup>(`employees`.`departments`.`dept_no` in <temporary table> on <auto_key> where ((`employees`.`departments`.`dept_no` = `materialized-subquery`.`dept_no`)))))", "attached_subqueries": [ { "table": { "table_name": "<materialized_subquery>", "access_type": "eq_ref", "key": "<auto_key>", "key_length": "4", "rows_examined_per_scan": 1, "materialized_from_subquery": { "using_temporary_table": true, "dependent": true, "cacheable": false, "query_block": { "select_id": 2, "cost_info": { "query_cost": "5.80" }, "table": { "table_name": "dept_manager", "access_type": "ALL", "possible_keys": [ "dept_no" ], "rows_examined_per_scan": 24, "rows_produced_per_join": 21, "filtered": "90.00", "cost_info": { "read_cost": "1.48", "eval_cost": "4.32", "prefix_cost": "5.80", "data_read_per_join": "345" }, "used_columns": [ "dept_no", "to_date" ], "attached_condition": "(`employees`.`dept_manager`.`to_date` is not null)" } } } } } ] } } } 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `employees`.`departments`.`dept_name` AS `dept_name` from `employees`.`departments` where <in_optimizer>(`employees`.`departments`.`dept_no`,`employees`.`departments`.`dept_no` in ( <materialize> (/* select#2 */ select `employees`.`dept_manager`.`dept_no` from `employees`.`dept_manager` where (`employees`.`dept_manager`.`to_date` is not null) ), <primary_index_lookup>(`employees`.`departments`.`dept_no` in <temporary table> on <auto_key> where ((`employees`.`departments`.`dept_no` = `materialized-subquery`.`dept_no`)))))

Now the picture is completely different. There is no nested_loop member, and instead there is an attached_subqueries array containing a single member: the temporary table materialized from the subquery select dept_no from dept_manager where to_date is not null (including all the details of this materialization).

Conclusion: We can experiment with the value of optimizer_switch and use EXPLAIN FORMAT=JSON to examine how a particular optimization affects our queries.

The post EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness appeared first on MySQL Performance Blog.

ERROR! MySQL (Percona XtraDB Cluster) is not running, but PID file exists

Lastest Forum Posts - December 23, 2015 - 3:11pm
I am trying to startup node two of a three node percona cluster. I am by no means a mysql database administrator, but maybe someone can identify what happened based on this error:
************************************************** ************************************************** ******************************************
Thread pointer: 0x7f483c000990
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f48523b8988 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7ee025]
/usr/sbin/mysqld(handle_fatal_signal+0x4b4)[0x6c0db4]
/lib64/libpthread.so.0(+0xf710)[0x7f4865b44710]
/lib64/libc.so.6(gsignal+0x35)[0x7f48641a0625]
/lib64/libc.so.6(abort+0x175)[0x7f48641a1e05]
/usr/sbin/mysqld[0x8f7239]
/usr/sbin/mysqld[0x8f85b9]
/usr/sbin/mysqld[0x8fc28b]
/usr/sbin/mysqld[0x8fc53b]
/usr/sbin/mysqld[0x82eaf3]
/usr/sbin/mysqld[0x831fe4]
/usr/sbin/mysqld[0x801c74]
/usr/sbin/mysqld(_ZN14Rows_log_event8find_rowEPK14Relay_log_ info+0x1e4)[0x766574]
/usr/sbin/mysqld(_ZN21Update_rows_log_event11do_exec_rowEPK1 4Relay_log_info+0xa5)[0x766a95]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Rel ay_log_info+0x267)[0x76d1c7]
/usr/sbin/mysqld(_Z14wsrep_apply_cbPvPKvmjPK14wsrep_trx_meta +0x6a5)[0x67d0b5]
/usr/lib64/libgalera_smm.so(+0x1a3699)[0x7f48613ce699]
/usr/lib64/libgalera_smm.so(_ZN6galera13ReplicatorSMM9apply_t rxEPvPNS_9TrxHandleE+0x273)[0x7f48613cfc13]
/usr/lib64/libgalera_smm.so(_ZN6galera13ReplicatorSMM11proces s_trxEPvPNS_9TrxHandleE+0x45)[0x7f48613d04e5]
/usr/lib64/libgalera_smm.so(_ZN6galera15GcsActionSource8dispa tchEPvRK10gcs_actionRb+0x2dc)[0x7f48613aa86c]
/usr/lib64/libgalera_smm.so(_ZN6galera15GcsActionSource7proce ssEPvRb+0x63)[0x7f48613aaf13]
/usr/lib64/libgalera_smm.so(_ZN6galera13ReplicatorSMM10async_ recvEPv+0x93)[0x7f48613ca1e3]
/usr/lib64/libgalera_smm.so(galera_recv+0x23)[0x7f48613dd5d3]
/usr/sbin/mysqld[0x67df11]
/usr/sbin/mysqld(start_wsrep_THD+0x2ee)[0x5215de]
/lib64/libpthread.so.0(+0x79d1)[0x7f4865b3c9d1]
/lib64/libc.so.6(clone+0x6d)[0x7f48642568fd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED

You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.
151116 01:03:18 mysqld_safe Number of processes running now: 0
151116 01:03:18 mysqld_safe WSREP: not restarting wsrep node automatically
151116 01:03:18 mysqld_safe mysqld from pid file /var/lib/mysql/web.web.com .pid ended
************************************************** ************************************************** ******************************************
Looking at the database service on node 2:

I looked at the service
[root@db subsys]# ps ax | grep mysql
25218 pts/0 S+ 0:00 grep mysql

************************************************** ************************************************** ******************************************
When I try to start the service back up, I get this error message:

[root@db2 mysql]# sudo /etc/init.d/mysql start
ERROR! MySQL (Percona XtraDB Cluster) is not running, but PID file exists
************************************************** ************************************************** ******************************************
I could not find the specific answer how to fix this issue online (only regarding locked PIDs). Does anyone know what I should do to fix this issue?

Thanks!




General Inquiries

For general inquiries, please send us your question and someone will contact you.