]]>
]]>

You are here

Feed aggregator

Flow control state initiated by asynchronous traffic

Lastest Forum Posts - April 23, 2015 - 12:38pm
We are trying to get on a 5 node Percona cluster. We are replicating from our current environment which is 2 masters and 2 slaves. Of course we're reading the binary log from one master to a node in XtraDB cluster. We are running only a very light demo instance on the cluster currently. We are finding periods of time where the async replication causes a flow control state in XtraDB Cluster.

My theory is that there is a stored procedure being run that would be one transaction which modifies a large amount of data causing the flow control to back up. However, I'm having trouble proving that. I only know about the event after the event. I am looking for a needle in a hay farm. The stored procedure is my latest guess as to what is going on.

I am looking for advice on how to precisely capture the moment and transactions which are causing the flow control state, and fix that in my application.

I am guessing that any other writes, no matter how quickly they came in from async replication would be absorbed by galera as nothing.

Any advice on how to pinpoint the transaction/transactions causing the Flow Control state would be appreciated. We've seen up to 20 minute halt on the server due to this.

How to Restore MySQL backup to Percona XtraDB Cluster

Lastest Forum Posts - April 23, 2015 - 7:21am
All ,

I configured 3 node Percona XtraDB cluster in Amazon Ec2 , and it got successfully installed and running .

Below are the points which I want to achieve

1. Restore mysql DB backup on Percona XtraDB cluster.
2. Make one of the node as slave of existing master.

If my one of my Percona Xtradb Cluster node is slave of my current master will data get replicated across the cluster.

Plugins not compatabile with cacti 0.8.8c?

Lastest Forum Posts - April 23, 2015 - 3:56am
Hi Guy's.

I'm running cacti 0.8.8c on Centos and tried installing the latest plugins but was met with the following error:

rpm -i percona-cacti-templates-1.1.4-1.noarch.rpm
warning: percona-cacti-templates-1.1.4-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
cacti >= 0.8.6 is needed by percona-cacti-templates-1.1.4-1.noarch

Is there a compatible version around?

Thanks.

Percona XtraDB Cluster HandlerSocket

Lastest Forum Posts - April 22, 2015 - 9:58pm
Hi.

There was need to use HandlerSosket in Percona XtraDB Cluster 5.5. Unfortunately I not found information about plugin support in PXC 5.5. Does Percona XtraDB Cluster 5.5 support HandlerSocket?

Using OR, or IN with NULL

Lastest Forum Posts - April 22, 2015 - 9:35am
Is there any benefit to using one of these over the other?

1) AND (r.resolve_status = 0 OR ISNULL(r.resolve_status))

2) AND r.resolve_status IN(0,NULL)

I ran a query through an online "tuner" and the only recommendation was to use IN instead of OR. I can not find any documentation on this, but then again searching for variations of 'using or or in' may not have been the best approach.

Hello

Lastest Forum Posts - April 22, 2015 - 2:11am
Hello,

I've had a 3-node cluster online for a few days, and I tried to take the second node down to change the tmpdir in my.cnf (disk was getting full). When I start MySQL now, the node will not recieve an SST and fails with some frustrating error messages. /www.e-six-sigma.net

Percona - Metrics and perfs drop after migration.

Lastest Forum Posts - April 22, 2015 - 12:39am
Hi i'm pretty new to MySQL, and recently our company moved from the "regular" version of MySQL (5.1) to Percona.(5.6)

We have this machine for migration :

2 x 2690v3 Intel Processors (24 Cores 48 Threads)
256 GB 2133Mhz ECC memory
2xIntel SSD for System
4xMicron P420m RAID 10 for DATA (Making it 1,3TB EXT4)
10 Gbps Intel Cards (Base-T)
Ubuntu 14.04.02 LTS
5.6.23-72.1-log Percona Server (GPL), Release 72.1, Revision 0503478


We got something like :

Read 4K : 4,9GB/s
Read 16K : 6,6 GB/s

Network Troughput : 9,42Gbps


Sysbench shows something like :

OLTP test statistics: 48 threads
queries performed:
read: 22059002
write: 0
other: 3151286
total: 25210288
transactions: 1575643 (26260.09 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 22059002 (367641.29 per sec.)
other operations: 3151286 (52520.18 per sec.)

Test execution summary:
total time: 60.0014s
total number of events: 1575643
total time taken by event execution: 2872.7015
per-request statistics:
min: 1.13ms
avg: 1.82ms
max: 18.27ms
approx. 95 percentile: 2.57ms

Threads fairness:
events (avg/stddev): 32825.8958/149.43
execution time (avg/stddev): 59.8479/0.00

I don't know what your metric looks like, but, foor a noob like me, it looks solid.

Well, i guess we have some pretty good hardware down here, but so far, the App Team complains about really poor database performance compared to legacy hardware, which is strange. Old Xeons, 32GB of slower memory, only 2x10K 600Gigs Drives (Hardware raid with BBU and 256MB of cache).

We tuned the server accordingly to the parameters given by percona's tuner.

But it might be related to something else.

In fact i'm beggining to think that there is a link between the change of version has more to do than the server itself.

Any clues ?

Have a nice day.






















Considering Sharding with MySQL? Join my April 22 webinar. Questions welcome!

Latest MySQL Performance Blog posts - April 21, 2015 - 9:08am

MySQL sharding is one of the most used and surely the most abused MySQL scaling technology. My April 2 Dzone article, “To Shard, or Not to Shard,” proved there is indeed quite an interest in this topic.

As such, I’m hosting a live webinar tomorrow (April 22) that will shed light on questions about sharding with MySQL. It’s titled: To Shard or Not to Shard That is the Question!

I’ll be answering questions such as:

  • Is sharding right for your application or should you use other scaling technologies?
  • If you’re sharding, what things do you need to consider and which questions do you need to have answered?
  • What kind of specific technologies can assist you with sharding?

I hope you can make it for this April 22 webinar. It starts at 10 a.m. Pacific time. Please register now and bring your questions, as sharing them with me and the other attendees is half of the fun of live webinars.

Or if you prefer, share your questions about sharding with MySQL in the comments section below, and I’ll do my best to answer them. I’ll be writing a followup post that will include all questions and my answers soon. A recording of this webinar along with my slides will also be available here afterwards.

The post Considering Sharding with MySQL? Join my April 22 webinar. Questions welcome! appeared first on MySQL Performance Blog.

Considering Sharding with MySQL? Join my April 22 webinar. Questions welcome!

Latest MySQL Performance Blog posts - April 21, 2015 - 9:08am

MySQL sharding is one of the most used and surely the most abused MySQL scaling technology. My April 2 Dzone article, “To Shard, or Not to Shard,” proved there is indeed quite an interest in this topic.

As such, I’m hosting a live webinar tomorrow (April 22) that shed light on questions about sharding with MySQL. It’s titled: To Shard or Not to Shard That is the Question!

I’ll be answering question such as:

  • Is sharding right for your application or should you use other scaling technologies?
  • If you’re sharding, what things do you need to consider and which questions do you need to have answered?
  • What kind of specific technologies can assist you with sharding?

I hope you can make it for this April 22. Please register now and bring your questions, as sharing them with me and the other attendees is half of the fun of live webinars.

Or if you prefer, share your questions about sharding with MySQL in the comments section below, and I’ll do my best to answer them. I’ll be writing a followup post that will include all questions and my answers soon. A recording of this webinar along with my slides will also be available here afterwards.

The post Considering Sharding with MySQL? Join my April 22 webinar. Questions welcome! appeared first on MySQL Performance Blog.

Unable to install percona tool kit

Lastest Forum Posts - April 21, 2015 - 7:22am
Hi ,

I am trying to install "percona-toolkit-2.2.14-1.noarch.rpm" on the production box running MySQL 5.5.39 on Linux CentOS 6.6.

when i executed rpm -ivh percona-toolkit-2.2.14-1.noarch.rpm, i received the following dependencies but is failing on DBD::MySQL

[root@SVPROD mysql]# rpm -ivh percona-toolkit-2.2.14-1.noarch.rpm
warning: percona-toolkit-2.2.14-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-2.2.14-1.noarch
perl(IO::Socket::SSL) is needed by percona-toolkit-2.2.14-1.noarch
perl(Term::ReadKey) is needed by percona-toolkit-2.2.14-1.noarch
[root@SVPROD mysql]# rpm -ivh perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm
warning: perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing... ########################################### [100%]
1erl-TermReadKey ########################################### [100%]
[root@SVPROD mysql]# rpm -ivh perl-IO-Socket-SSL-1.44-1.el6.rfx.noarch.rpm
warning: perl-IO-Socket-SSL-1.44-1.el6.rfx.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
error: Failed dependencies:
perl(Net::SSLeay) >= 1.21 is needed by perl-IO-Socket-SSL-1.44-1.el6.rfx.noarch
[root@SVPROD mysql]# rpm -ivh perl-Net-SSLeay-1.36-1.el6.rfx.x86_64.rpm
warning: perl-Net-SSLeay-1.36-1.el6.rfx.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing... ########################################### [100%]
1erl-Net-SSLeay ########################################### [100%]
[root@SVPROD mysql]# rpm -ivh perl-IO-Socket-SSL-1.44-1.el6.rfx.noarch.rpm
warning: perl-IO-Socket-SSL-1.44-1.el6.rfx.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing... ########################################### [100%]
1erl-IO-Socket-SSL ########################################### [100%]
[root@SVPROD mysql]# rpm -ivh perl-DBD-MySQL-4.022-1.el6.rfx.x86_64.rpm
warning: perl-DBD-MySQL-4.022-1.el6.rfx.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
error: Failed dependencies:
mysql is needed by perl-DBD-MySQL-4.022-1.el6.rfx.x86_64

[root@SVPROD mysql]#

Kindly let me know how to fix this error

Thanks,
Rajesh

Order by using an Inner Join not working in Linux environment of Mysql

Lastest Forum Posts - April 21, 2015 - 4:08am
As i have to do an order by, on a column for a table having millions of records, I found an approcah which can do faster sort in the following way.

It is like doing an Inner Join with a result set which is ordered by the interested order by column, where in such column has to be indexed, so that it will use the created Index. So in this case, the time is not utilized at all for Order by and it is only Join time, so the result was faster.

SELECT distinct pd.message_id, pd.receipt_time_stamp
FROM fm_package_db.dla_package_details pd
INNER JOIN
(SELECT message_id FROM fm_package_db.dla_package_details
where receipt_time_stamp between '2015-03-30 00:00:00' AND '2015-03-30 23:59:59'
ORDER BY receipt_time_stamp desc) as temp ON temp.message_id = pd.message_id
WHERE pd.Site_Code = 'SITE_6' LIMIT 0 , 25

Now the problem is, this approach will do an Order by on any column in both Asc and Desc perfectly in Windows environment of Mysql. But it is always doing an 'asc' sort only in Linux environment of Mysql, but not doing Desc sorting.

Is there any such implementation differences with different Mysql instances in Linux and Windows, for the above query to not to work.

Pls help out.

Percona Server removes its pid and socket

Lastest Forum Posts - April 20, 2015 - 1:02pm
Faced with the fact that Percona Server on Centos 6 when re-start init script removes its pid and starts the second process. Checked at 51 and 55. A similar case is not found.

: [root@localhost ~]# ps auxw | grep mysql root 9826 0.0 0.0 103244 856 pts/1 S+ 15:35 0:00 grep mysql [root@localhost ~]# ls -l /var/lib/mysql/ total 28692 -rw-rw----. 1 mysql mysql 18874368 Apr 20 14:32 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Apr 20 15:15 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Apr 20 14:12 ib_logfile1 drwx------. 2 mysql mysql 4096 Apr 20 14:08 mysql drwx------. 2 mysql mysql 4096 Apr 20 14:08 performance_schema -rw-r--r--. 1 mysql mysql 117 Apr 20 14:08 RPM_UPGRADE_HISTORY -rw-r--r--. 1 mysql mysql 117 Apr 20 14:08 RPM_UPGRADE_MARKER-LAST drwx------. 2 mysql mysql 4096 Apr 20 14:08 test [root@localhost ~]# /etc/init.d/mysql start Starting MySQL (Percona Server). SUCCESS! [root@localhost ~]# ls -l /var/lib/mysql/ total 28696 -rw-rw----. 1 mysql mysql 18874368 Apr 20 15:35 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Apr 20 15:35 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Apr 20 14:12 ib_logfile1 -rw-rw----. 1 mysql mysql 5 Apr 20 15:35 localhost.localdomain.pid drwx------. 2 mysql mysql 4096 Apr 20 14:08 mysql srwxrwxrwx. 1 mysql mysql 0 Apr 20 15:35 mysql.sock drwx------. 2 mysql mysql 4096 Apr 20 14:08 performance_schema -rw-r--r--. 1 mysql mysql 117 Apr 20 14:08 RPM_UPGRADE_HISTORY -rw-r--r--. 1 mysql mysql 117 Apr 20 14:08 RPM_UPGRADE_MARKER-LAST drwx------. 2 mysql mysql 4096 Apr 20 14:08 test [root@localhost ~]# /etc/init.d/mysql start Starting MySQL (Percona Server) SUCCESS! //When recalling the init-script erased pid and socket [root@localhost ~]# ls -l /var/lib/mysql/ total 28692 -rw-rw----. 1 mysql mysql 18874368 Apr 20 15:35 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Apr 20 15:35 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Apr 20 14:12 ib_logfile1 drwx------. 2 mysql mysql 4096 Apr 20 14:08 mysql drwx------. 2 mysql mysql 4096 Apr 20 14:08 performance_schema -rw-r--r--. 1 mysql mysql 117 Apr 20 14:08 RPM_UPGRADE_HISTORY -rw-r--r--. 1 mysql mysql 117 Apr 20 14:08 RPM_UPGRADE_MARKER-LAST drwx------. 2 mysql mysql 4096 Apr 20 14:08 test //two process at one time [root@localhost ~]# ps auxw | grep mysql root 9843 0.0 0.1 106064 1484 pts/1 S 15:35 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid mysql 9932 0.0 4.0 597860 41592 pts/1 Sl 15:35 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock root 9969 0.0 0.1 106064 1484 pts/1 S 15:35 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid mysql 10060 0.1 2.9 389884 30456 pts/1 Sl 15:35 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock root 10074 0.0 0.0 103244 856 pts/1 S+ 15:35 0:00 grep mysql [root@localhost ~]#

Percona SSH based configuratin failing

Lastest Forum Posts - April 20, 2015 - 8:46am


HI

su - www-data -c 'env -i php /usr/share/cacti/site/scripts/ss_get_by_ssh.php --type memory --host 10.78.203.141 --items gu,gv'

gu:-1 gv:-1 (not returing proper values)


i am able to do
root@CACTI:/usr/share/cacti/site/scripts# ssh -i /etc/cacti/id_rsa cacti@10.78.203.141 echo "it works"
it works


i am not sure why i ama not getting the output




















handshake with remote endpoint failed, but endpoint is not part of config (any more)

Lastest Forum Posts - April 20, 2015 - 3:57am
I set up a cluster of 3 database nodes using a percona 5.6 cluster. Everything works, all nodes are replicated. I configured SSL for state transfer. The cluster recently changed its topography, because we had to migrate 2 nodes to new machines. The cluster was completely restarted recently with configuration files that only contain the 3 current cluster nodes node3, node8, nodeA (don't mind the naming). Earlier we also had node1 but this machine left the cluster and does not run any mysql any more.

Now the problem: In the log files of node3 and node8 I find every couple of seconds an error log that looks like this:

: 2015-04-20 11:06:24 22546 [ERROR] WSREP: handshake with remote endpoint ssl://<PUBLIC.node1.IP>:57953 failed: 1: 'End of file.' ( ) where ```<PUBLIC.node1.IP>``` is the address of the old node1, which is by no means any more part of the cluster. I looked through the my.cnf config files of all cluster nodes, but all traces of node1 are gone from it. How does it come the node3 and node8 try to contact the outdated node? Note, that this is after a complete bootstrapping of the cluster starting from nodeA, which does not show the strange error messages in the log.

I found an error description of a very similar error here: https://mariadb.com/kb/en/mariadb/st...-in-error-log/

There it was suggested, that this may be due to a broken network configuration where nodes could only ping from one node to the other, but not the reverse direction. But the given reason/solution does not fit in my case, since all nodes can ping each other fine. Also, it does not explain at all the fact, that my cluster nodes try to contact a node that is not part of the cluster any more.

Here is the relevant part of the congiguration file (from node8)


: ### PERCONA CLUSTER STUFF wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_address=gcomm://<PUBLIC.nodeA.IP>,<PUBLIC.node3.IP>,<PUBLIC.node 8. IP> #wsrep_cluster_address=gcomm:// wsrep_node_address=<PUBLIC.node8.IP> wsrep_slave_threads=8 wsrep_sst_method=xtrabackup-v2 binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_cluster_name=betdata_cluster wsrep_provider_options=gcache.size=3G;socket.ssl_cert=/etc/mysql/cert.pem;socket.ssl_key=/etc/mysql/key.pem wsrep_sst_donor=node3,nodeA wsrep_node_name=node8 wsrep_sst_auth=sstuser:blahblubb wsrep_sst_receive_address=<PUBLIC.node8.IP>:14444 query_cache_size=0 query_cache_type=0
Any Ideas?

Note. This is a cross posting from http://dba.stackexchange.com/questio...ndpoint-is-not

MySql Order by on a huge joined datasets is taking too long time.

Lastest Forum Posts - April 17, 2015 - 3:25am
I have to join many tables where in each table has huge volume of data and on such joined huge dataset, i have to apply Order by which is taking a very very longer time

MySql Order by on a huge joined datasets is taking too long time.

Lastest Forum Posts - April 17, 2015 - 3:25am
I have to join many tables where in each table has huge volume of data and on such joined huge dataset, i have to apply Order by which is taking a very very longer time

disconnecting clients

Lastest Forum Posts - April 17, 2015 - 1:14am
2015-04-17 10:15:15 1226 [Note] Forcefully disconnecting 5 remaining clients
2015-04-17 10:15:15 1226 [Warning] /usr/sbin/mysqld: Forcing close of thread 1229 user: 'b_user'
2015-04-17 10:15:15 1226 [Warning] /usr/sbin/mysqld: Forcing close of thread 1231 user: 'b_user'
2015-04-17 10:15:15 1226 [Warning] /usr/sbin/mysqld: Forcing close of thread 1228 user: 'b_user'
2015-04-17 10:15:15 1226 [Warning] /usr/sbin/mysqld: Forcing close of thread 1273 user: 'b_user'
2015-04-17 10:15:15 1226 [Warning] /usr/sbin/mysqld: Forcing close of thread 1270 user: 'b_user'

why is this happening?

Percona XtraDB Cluster crashes periodically

Lastest Forum Posts - April 16, 2015 - 2:23pm
Hello,


Running 3 nodes PXC, and am experimenting frequent crashes. I already reported a bug on Percona Launchpad.

https://bugs.launchpad.net/percona-server/+bug/1436320

Do you have any ideas of what could be the source of these frequent crashes ? Or any way to know what's happening.

Kind regards.

Profiling MySQL queries from Performance Schema

Latest MySQL Performance Blog posts - April 16, 2015 - 10:49am

When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries akaSET profiling = 1; . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative?

Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This means that you cannot capture profiling information for queries running from other connections. If you are using Percona Server, the profiling option for log_slow_verbosity is a nice alternative, unfortunately, not everyone is using Percona Server.

Now, for a quick demo: I execute a simple query and profile it below. Note that all of these commands are executed from a single session to my test instance.

mysql> SHOW PROFILES; +----------+------------+----------------------------------------+ | Query_ID | Duration   | Query                                  | +----------+------------+----------------------------------------+ |        1 | 0.00011150 | SELECT * FROM sysbench.sbtest1 LIMIT 1 | +----------+------------+----------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW PROFILE SOURCE FOR QUERY 1; +----------------------+----------+-----------------------+------------------+-------------+ | Status               | Duration | Source_function       | Source_file      | Source_line | +----------------------+----------+-----------------------+------------------+-------------+ | starting             | 0.000017 | NULL                  | NULL             |        NULL | | checking permissions | 0.000003 | check_access          | sql_parse.cc     |        5797 | | Opening tables       | 0.000021 | open_tables           | sql_base.cc      |        5156 | | init                 | 0.000009 | mysql_prepare_select  | sql_select.cc    |        1050 | | System lock          | 0.000005 | mysql_lock_tables     | lock.cc          |         306 | | optimizing           | 0.000002 | optimize              | sql_optimizer.cc |         138 | | statistics           | 0.000006 | optimize              | sql_optimizer.cc |         381 | | preparing            | 0.000005 | optimize              | sql_optimizer.cc |         504 | | executing            | 0.000001 | exec                  | sql_executor.cc  |         110 | | Sending data         | 0.000025 | exec                  | sql_executor.cc  |         190 | | end                  | 0.000002 | mysql_execute_select  | sql_select.cc    |        1105 | | query end            | 0.000003 | mysql_execute_command | sql_parse.cc     |        5465 | | closing tables       | 0.000004 | mysql_execute_command | sql_parse.cc     |        5544 | | freeing items        | 0.000005 | mysql_parse           | sql_parse.cc     |        6969 | | cleaning up          | 0.000006 | dispatch_command      | sql_parse.cc     |        1874 | +----------------------+----------+-----------------------+------------------+-------------+ 15 rows in set, 1 warning (0.00 sec)

To demonstrate how we can achieve the same with Performance Schema, we first identify our current connection id. In the real world, you might want to get the connection/processlist id of the thread you want to watch i.e. fromSHOW PROCESSLIST .

mysql> SELECT THREAD_ID INTO @my_thread_id     -> FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID(); Query OK, 1 row affected (0.00 sec)

Next, we identify the bounding EVENT_IDs for the statement stages. We will look for the statement we wanted to profile using the query below from theevents_statements_history_longtable. Your LIMIT clause may vary depending on how much queries the server might be getting.

mysql> SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, SQL_TEXT, NESTING_EVENT_ID     -> FROM events_statements_history_long     -> WHERE THREAD_ID = @my_thread_id     ->   AND EVENT_NAME = 'statement/sql/select'     -> ORDER BY EVENT_ID DESC LIMIT 3 G *************************** 1. row ***************************        THREAD_ID: 13848         EVENT_ID: 419     END_EVENT_ID: 434         SQL_TEXT: SELECT THREAD_ID INTO @my_thread_id FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID() NESTING_EVENT_ID: NULL *************************** 2. row ***************************        THREAD_ID: 13848         EVENT_ID: 374     END_EVENT_ID: 392         SQL_TEXT: SELECT * FROM sysbench.sbtest1 LIMIT 1 NESTING_EVENT_ID: NULL *************************** 3. row ***************************        THREAD_ID: 13848         EVENT_ID: 353     END_EVENT_ID: 364         SQL_TEXT: select @@version_comment limit 1 NESTING_EVENT_ID: NULL 3 rows in set (0.02 sec)

From the results above, we are mostly interested with the EVENT_ID and END_EVENT_ID values from the second row, this will give us the stage events of this particular query from theevents_stages_history_longtable.

mysql> SELECT EVENT_NAME, SOURCE, (TIMER_END-TIMER_START)/1000000000 as 'DURATION (ms)'     -> FROM events_stages_history_long     -> WHERE THREAD_ID = @my_thread_id AND EVENT_ID BETWEEN 374 AND 392; +--------------------------------+----------------------+---------------+ | EVENT_NAME                     | SOURCE               | DURATION (ms) | +--------------------------------+----------------------+---------------+ | stage/sql/init                 | mysqld.cc:998        |        0.0214 | | stage/sql/checking permissions | sql_parse.cc:5797    |        0.0023 | | stage/sql/Opening tables       | sql_base.cc:5156     |        0.0205 | | stage/sql/init                 | sql_select.cc:1050   |        0.0089 | | stage/sql/System lock          | lock.cc:306          |        0.0047 | | stage/sql/optimizing           | sql_optimizer.cc:138 |        0.0016 | | stage/sql/statistics           | sql_optimizer.cc:381 |        0.0058 | | stage/sql/preparing            | sql_optimizer.cc:504 |        0.0044 | | stage/sql/executing            | sql_executor.cc:110  |        0.0008 | | stage/sql/Sending data         | sql_executor.cc:190  |        0.0251 | | stage/sql/end                  | sql_select.cc:1105   |        0.0017 | | stage/sql/query end            | sql_parse.cc:5465    |        0.0031 | | stage/sql/closing tables       | sql_parse.cc:5544    |        0.0037 | | stage/sql/freeing items        | sql_parse.cc:6969    |        0.0056 | | stage/sql/cleaning up          | sql_parse.cc:1874    |        0.0006 | +--------------------------------+----------------------+---------------+ 15 rows in set (0.01 sec)

As you can see the results are pretty close, not exactly the same but close. SHOW PROFILE shows Duration in seconds, while the results above is in milliseconds.

Some limitations to this method though:

  • As we’ve seen it takes a few hoops to dish out the information we need. Because we have to identify the statement we have to profile manually, this procedure may not be easy to port into tools like the sys schema or pstop.
  • Only possible if Performance Schema is enabled (by default its enabled since MySQL 5.6.6, yay!)
  • Does not cover all metrics compared to the native profiling i.e. CONTEXT SWITCHES, BLOCK IO, SWAPS
  • Depending on how busy the server you are running the tests, the sizes of the history tables may be too small, as such you either have to increase or loose the history to early i.e.performance_schema_events_stages_history_long_sizevariable. Using ps_history might help in this case though with a little modification to the queries.
  • The resulting Duration per event may vary, I would think this may be due to the additional as described on performance_timers table. In any case we hope to get this cleared up as result when this bug is fixed.

The post Profiling MySQL queries from Performance Schema appeared first on MySQL Performance Blog.

Profiling MySQL queries from Performance Schema

Latest MySQL Performance Blog posts - April 16, 2015 - 10:49am

When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries akaSET profiling = 1; . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative?

Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This means that you cannot capture profiling information for queries running from other connections. If you are using Percona Server, the profiling option for log_slow_verbosity is a nice alternative, unfortunately, not everyone is using Percona Server.

Now, for a quick demo: I execute a simple query and profile it below. Note that all of these commands are executed from a single session to my test instance.

mysql> SHOW PROFILES; +----------+------------+----------------------------------------+ | Query_ID | Duration   | Query                                  | +----------+------------+----------------------------------------+ |        1 | 0.00011150 | SELECT * FROM sysbench.sbtest1 LIMIT 1 | +----------+------------+----------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW PROFILE SOURCE FOR QUERY 1; +----------------------+----------+-----------------------+------------------+-------------+ | Status               | Duration | Source_function       | Source_file      | Source_line | +----------------------+----------+-----------------------+------------------+-------------+ | starting             | 0.000017 | NULL                  | NULL             |        NULL | | checking permissions | 0.000003 | check_access          | sql_parse.cc     |        5797 | | Opening tables       | 0.000021 | open_tables           | sql_base.cc      |        5156 | | init                 | 0.000009 | mysql_prepare_select  | sql_select.cc    |        1050 | | System lock          | 0.000005 | mysql_lock_tables     | lock.cc          |         306 | | optimizing           | 0.000002 | optimize              | sql_optimizer.cc |         138 | | statistics           | 0.000006 | optimize              | sql_optimizer.cc |         381 | | preparing            | 0.000005 | optimize              | sql_optimizer.cc |         504 | | executing            | 0.000001 | exec                  | sql_executor.cc  |         110 | | Sending data         | 0.000025 | exec                  | sql_executor.cc  |         190 | | end                  | 0.000002 | mysql_execute_select  | sql_select.cc    |        1105 | | query end            | 0.000003 | mysql_execute_command | sql_parse.cc     |        5465 | | closing tables       | 0.000004 | mysql_execute_command | sql_parse.cc     |        5544 | | freeing items        | 0.000005 | mysql_parse           | sql_parse.cc     |        6969 | | cleaning up          | 0.000006 | dispatch_command      | sql_parse.cc     |        1874 | +----------------------+----------+-----------------------+------------------+-------------+ 15 rows in set, 1 warning (0.00 sec)

To demonstrate how we can achieve the same with Performance Schema, we first identify our current connection id. In the real world, you might want to get the connection/processlist id of the thread you want to watch i.e. fromSHOW PROCESSLIST .

mysql> SELECT THREAD_ID INTO @my_thread_id     -> FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID(); Query OK, 1 row affected (0.00 sec)

Next, we identify the bounding EVENT_IDs for the statement stages. We will look for the statement we wanted to profile using the query below from theevents_statements_history_longtable. Your LIMIT clause may vary depending on how much queries the server might be getting.

mysql> SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, SQL_TEXT, NESTING_EVENT_ID     -> FROM events_statements_history_long     -> WHERE THREAD_ID = @my_thread_id     ->   AND EVENT_NAME = 'statement/sql/select'     -> ORDER BY EVENT_ID DESC LIMIT 3 G *************************** 1. row ***************************        THREAD_ID: 13848         EVENT_ID: 419     END_EVENT_ID: 434         SQL_TEXT: SELECT THREAD_ID INTO @my_thread_id FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID() NESTING_EVENT_ID: NULL *************************** 2. row ***************************        THREAD_ID: 13848         EVENT_ID: 374     END_EVENT_ID: 392         SQL_TEXT: SELECT * FROM sysbench.sbtest1 LIMIT 1 NESTING_EVENT_ID: NULL *************************** 3. row ***************************        THREAD_ID: 13848         EVENT_ID: 353     END_EVENT_ID: 364         SQL_TEXT: select @@version_comment limit 1 NESTING_EVENT_ID: NULL 3 rows in set (0.02 sec)

From the results above, we are mostly interested with the EVENT_ID and END_EVENT_ID values from the second row, this will give us the stage events of this particular query from theevents_stages_history_longtable.

mysql> SELECT EVENT_NAME, SOURCE, (TIMER_END-TIMER_START)/1000000000 as 'DURATION (ms)'     -> FROM events_stages_history_long     -> WHERE THREAD_ID = @my_thread_id AND EVENT_ID BETWEEN 374 AND 392; +--------------------------------+----------------------+---------------+ | EVENT_NAME                     | SOURCE               | DURATION (ms) | +--------------------------------+----------------------+---------------+ | stage/sql/init                 | mysqld.cc:998        |        0.0214 | | stage/sql/checking permissions | sql_parse.cc:5797    |        0.0023 | | stage/sql/Opening tables       | sql_base.cc:5156     |        0.0205 | | stage/sql/init                 | sql_select.cc:1050   |        0.0089 | | stage/sql/System lock          | lock.cc:306          |        0.0047 | | stage/sql/optimizing           | sql_optimizer.cc:138 |        0.0016 | | stage/sql/statistics           | sql_optimizer.cc:381 |        0.0058 | | stage/sql/preparing            | sql_optimizer.cc:504 |        0.0044 | | stage/sql/executing            | sql_executor.cc:110  |        0.0008 | | stage/sql/Sending data         | sql_executor.cc:190  |        0.0251 | | stage/sql/end                  | sql_select.cc:1105   |        0.0017 | | stage/sql/query end            | sql_parse.cc:5465    |        0.0031 | | stage/sql/closing tables       | sql_parse.cc:5544    |        0.0037 | | stage/sql/freeing items        | sql_parse.cc:6969    |        0.0056 | | stage/sql/cleaning up          | sql_parse.cc:1874    |        0.0006 | +--------------------------------+----------------------+---------------+ 15 rows in set (0.01 sec)

As you can see the results are pretty close, not exactly the same but close. SHOW PROFILE shows Duration in seconds, while the results above is in milliseconds.

Some limitations to this method though:

  • As we’ve seen it takes a few hoops to dish out the information we need. Because we have to identify the statement we have to profile manually, this procedure may not be easy to port into tools like the sys schema or pstop.
  • Only possible if Performance Schema is enabled (by default its enabled since MySQL 5.6.6, yay!)
  • Does not cover all metrics compared to the native profiling i.e. CONTEXT SWITCHES, BLOCK IO, SWAPS
  • Depending on how busy the server you are running the tests, the sizes of the history tables may be too small, as such you either have to increase or loose the history to early i.e.performance_schema_events_stages_history_long_sizevariable. Using ps_history might help in this case though with a little modification to the queries.
  • The resulting Duration per event may vary, I would think this may be due to the additional as described on performance_timers table. In any case we hope to get this cleared up as result when this bug is fixed.

The post Profiling MySQL queries from Performance Schema appeared first on MySQL Performance Blog.

Pages

Subscribe to Percona aggregator
]]>