Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 74 Next 30 Older Entries

Displaying posts with tag: performance_schema (reset)

The Road to MySQL 5.6 -- A DBA Perspective
+4 Vote Up -0Vote Down
We've all heard the hype.  MySQL 5.6 is packed with amazing new features that address all our database problems.  5.6 deals with replication and HA and performance and monitoring and security and features.  It just may cure cancer.

In fact it's been out for ages.  It went GA 

  [Read more...]
MariaDB 10.0 upgrade goes smoothly
+0 Vote Up -0Vote Down

I have been meaning to update some systems to MariaDB 10.0 and finally had a bit of time to get around to that.  The documentation of specifics of what’s needed to go from MariaDB 5.5 to 10.0 can be found here and while it’s not very long it seems there’s little to actually do.

Having already upgraded some servers from MySQL 5.5 to 5.6 the process and appropriate configuration changes were very similar so all in all a rather non event.

One thing which is always a concern if systems can not be down for long is the time to do the upgrade. While you see many blog posts talking about taking a backup via mysqldump and then loading it all back this is not really an option on many systems I manage and a replacement

  [Read more...]
PERFORMANCE_SCHEMA disabled in MariaDB 10.0.12
+3 Vote Up -2Vote Down

Astute readers of the release notes for MariaDB 10.0.12 will notice that there is a line that reads: performance_schema is now disabled by default.

We didn’t come to this decision by accident. Recently at the SkySQL company meeting in Budapest, we did have some time to break out into our usual working teams to talk about our daily operations. Team MariaDB had a debate about PERFORMANCE_SCHEMA and how it was left on by mistake in 10.0 GA as there was a decision to turn it off. Personally, I don’t like introducing such changes in a GA release, and there was no archive of such a discussion, so the next best thing to do was to ask the MariaDB developers and users via a post to both

  [Read more...]
MySQL sys version 1.0.1 released
Employee +5 Vote Up -0Vote Down

I’ve just released the 1.0.1 version of the MySQL sys schema. It contains a few bug fixes (including a contribution from Joe Grasse, thanks Joe!), and a number of new helper procedures around viewing and altering configuration for consumers and instruments, contributed by the MySQL QA team, as they’ve started to incorporate more Performance Schema trace data in to their automated testing.

Next up is the 1.1.0 release, that also includes a bunch of new host summary views that were contributed by Arnaud Adant (of the MySQL Support team). I have a number of new things in development to add as well before then though.

Let me know if there are things you’d like to see as well, maybe I can find time to work on those too.

Enjoy.

Performance_schema success stories : host summary tables
Employee_Team +2 Vote Up -0Vote Down

This question was asked at support by a customer to solve a difficult issue.

How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?

If there are hundreds of hosts, it can be challenging, especially if the queries are fast. No chance for them to get logged in the famous slow query log !

Here is the solution using the performance_schema in MySQL 5.6 :

SELECT
host,
SUM(essbben.count_star) AS total_statements,
format_time(SUM(essbben.sum_timer_wait)) AS total_latency,
format_time(SUM(essbben.sum_timer_wait) / SUM(count_star))
AS avg_latency
FROM
performance_schema.events_statements_summary_by_host_by_event_name essbben
GROUP BY
host
ORDER BY
SUM(sum_timer_wait) DESC;

Here is the result

  [Read more...]
Fun with Bugs #30 - quick review of my reports in February, 2014
+0 Vote Up -0Vote Down
I've got only one comment to my previous post about deadlock, and it was more like a hint based on a different use case, not a real explanation. So far there is nobody who wants to get free beer... Maybe this is even good, as I do not go to the conference and BOF I've submitted will be supervised by my colleague Przemysław Malkowski. But you still have entire month till the conference to get a chance for a beer from him (we'll arrange this somehow).

In the meantime I'd like to review bug reports for MySQL server (few) and fine manual (many) that I've submitted in February, 2014. 22 in total, one

  [Read more...]
PERFORMANCE_SCHEMA vs Slow Query Log
+4 Vote Up -0Vote Down

A couple of weeks ago, shortly after Vadim wrote about Percona Cloud Tools and using Slow Query Log to capture the data, Mark Leith asked why don’t we just use Performance Schema instead? This is an interesting question and I think it deserves its own blog post to talk about.

First, I would say main reason for using Slow Query Log is compatibility. Basic Slow query log with microsecond query time precision is available starting in MySQL 5.1, while

  [Read more...]
MySQL 5.6 GA one year – What is next?
+3 Vote Up -0Vote Down

MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes.  Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job.  Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.

So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the

  [Read more...]
Exploring MySQL Metadata Lock Instrumentation in Closer Detail
+2 Vote Up -0Vote Down

I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.

First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES     | YES   |
+----------------------------+---------+-------+

“ENABLED” will report “YES” if it is enabled properly, and “NO” if not.

“TIMED” (referring to event timing) reports

  [Read more...]
Tracking Metadata Locks (MDL) in MariaDB 10.0
+1 Vote Up -1Vote Down

I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.

In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.

In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:

INSTALL SONAME 'metadata_lock_info';

Then, you will have the table.

To see it in action:

Connection #1:

mysql> create table t (id int) engine=myisam;
mysql> begin;
mysql> select * from t;

Connection #2:

mysql> alter table t
  [Read more...]
Tracking Metadata Locks (MDL) in MySQL 5.7
+3 Vote Up -1Vote Down

I’ve blogged about metadata locks (MDL) in the past (1 2 3) and in particular discussed how best to track them down and troubleshoot threads stuck waiting on metadata locks.

If you’ve had any experience with these, you’ll know finding them isn’t always the most straight-forward task.

So I was glad to see metadata lock instrumentation added to MySQL 5.7.3 as part of performance_schema, which makes tracking these down a breeze! (Note this is only in 5.7.3 currently, and therefore is some time from being GA as of today)!

To use these, performance_schema must be enabled (i.e., performance_schema=1 in your config file).

But, also, the metadata_locks instrument is disabled by default, so even if you enable the

  [Read more...]
SF MySQL Meetup Presentation: Changes in MySQL 5.7
Employee +4 Vote Up -0Vote Down

Last Wednesday, I spoke at the San Francisco MySQL Meetup on the topic of changes coming in MySQL 5.7 (and later).  We actually went through two different slide decks; the first on features being considered for deprecation in MySQL 5.7 (or later), and the second set providing a brief overview of the new features and benefits already introduced in MySQL 5.7 via the development milestone releases (DMRs) published to date.  A big thanks to the entire SF Meetup group, and in particular the organizers (Erin, Mike and Darren), for having me.  The event was streamed and recorded, and you can view the full presentation on YouTube.  The slide deck can be found here.

The discussion around proposed

  [Read more...]
Fun with Bugs #26 - MySQL bugs Oracle had not fixed for me (yet)
+0 Vote Up -0Vote Down
In the previous post in this series I've listed 15 MySQL bug reports, documentation and feature requests I've made in 2013 that got fixes or any other kind of solution. Now it's time to check what happened to the rest and try to think why.

First of all, no MySQL bug reporter is perfect (if only Domas), so some bug reports may be false alarms ("Not a bug"), to hard to fix at any foreseeable future ("To be fixed later") or

  [Read more...]
Fun with Bugs #25 - MySQL bugs Oracle fixed for me this year
+0 Vote Up -0Vote Down
I've checked recently and noted that I've sent 50 reports about MySQL bugs, features I'd like to see and unclear/missing manual pages this year. It all started with famous Bug #68079 (reported on January 14, 2013), that got a lot of attention, valuable workaround from Oracle and caused a lot of work that is going to improve MySQL scalability substantially in the future.

Oracle had also implemented this my (and not only mine!) feature request, Bug #69527, and in MySQL 5.7.3 PERFORMANCE_SCHEMA finally exposes metadata locks information. This is a great and long waited step forward in instrumentation.

Besides that, 12 of my documentation requests were satisfied:





  [Read more...]
What SQL is running in MySQL
+3 Vote Up -0Vote Down

Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.

The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.

use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest;
do sleep(60);
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from
  [Read more...]
Teaser on my upcoming Percona Live London 2013 session
+0 Vote Up -0Vote Down
As you probably know already, I have a session on PERFORMANCE_SCHEMA at the conference, scheduled at 12 November 4:00pm - 4:50pm @ Orchard 1. Presentation is mostly ready, but I had not decided yet when to publish it. In the meantime, for those really interested, here is a teaser.

Below I list one link for each slide (in order of presentation) having more than one of them mentioned or listed in my notes. Now try to guess what I am going to say there and why. Note that it's not a tutorial (my half a day tutorial on PERFORMANCE_SCHEMA was not accepted by the conference committee, and this is probably good decisions, as I am usually very good in explaining what's bad or what should never be done and much worse in "best practices"). So,

  [Read more...]
The second MySQL seminar: a summary
Employee +2 Vote Up -0Vote Down
Once again, Geir Høydalsvik and I had the pleasure of hosting a MySQL mini-seminar in Trondheim. 25+ attendants from at least 7 different companies and a few professors from the computer science dept. at NTNU showed up on yesterdays event. I recognized many of these from the first seminar but there were some new faces as well.

This time, Mark Leith came on a visit from the UK. He gave an introduction to Performance Schema and ps_helper. ps_helper is a really nice tool to make sense of the overwhelming amount of data collected by PS. He also gave a very convincing demo of MySQL Enterprise Monitor (MEM). More than a few attendants now plan to give MEM a try in their environment. You can too - there's a

  [Read more...]
New MySQL 5.6-Feature host_cache_size does not work
+1 Vote Up -0Vote Down

Today as i was learning for the new MySQL 5.6-certification (more about that in a later post) i stumbled again across the host_cache-Table that was added to MySQL in 5.6.5. Before that you had no chance to check which hosts are already known by the MySQL-server.

So thats a pretty good feature for us. And even better: you could resize the size of the host_cache now! Whoohoo, awesome! As we have a lot of servers that need to connect to one of our MySQL-server and we could not switch to ip-based-authentication we were really happy to tune the host_cache-size without recompiling MySQL.

Unfortunately i noticed that the performance_schema.host_cache table only holds 128 rows and that the content was changing everytime i checked. So i logged in to a server that wasn’t already in the host_cache-table, made a connection attempt to the mysql server

  [Read more...]
MySQL Connect and Oracle Open World Presentations Online
Employee +3 Vote Up -0Vote Down

After a slight delay (travel and catching up with “real work”), I’ve now uploaded the talks that I gave at MySQL Connect and Oracle Open World.

They are available on my Presentations Page, and inline below for convenience. The “Introduction to MySQL Enterprise Monitor” talk was actually a full demo, but there are some screenshots of MEM 3.0 in there if you’re interested in seeing a high level picture of what it looks like now.

Thanks to all that attended my talks, I got a lot of good questions and feedback!

Performance Schema and ps_helper   [Read more...]
Fun with Bugs #24 - PERFORMANCE_SCHEMA
+1 Vote Up -1Vote Down
It seems that one of my session proposals is accepted for Percona Live London 2013, so I have to prepare myself to speak about PERFORMANCE_SCHEMA new features and problems in MySQL 5.6. Bugs are going to be discussed, among other things. Let's check current active bugs (and some "Not a bug"s) related to PERFORMANCE_SCHEMA in this issue.

I'd like to start with Bug #68514 that got some attention this week again, in despite of its "Not a bug" formal status. Detailed instrumentation comes with a cost, and to reduce high CPU cost (reported as Bug #67736 by Domas at 5.6 RC stage) it was decided to

  [Read more...]
Understanding max_connect_errors
Employee +3 Vote Up -0Vote Down

To only slightly misquote one of the greatest movies of all times:

You keep using that option.  I do not think it means what you think it means.

 

Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice.  I’m writing this blog post to help others who hold similar misconceptions of what this option does.

Many, if not most, MySQL DBAs are familiar with “host blocked”

  [Read more...]
MySQL 5.6 users – prevent host blocked errors
Employee +2 Vote Up -0Vote Down

The much-improved PERFORMANCE_SCHEMA in MySQL 5.6 provides visibility into MySQL’s host cache, including the ability to monitor for impending blocked hosts.  You can do this with the following query:

mysql> SELECT
    ->  ip,
    ->  host,
    ->  host_validated,
    ->  sum_connect_errors
    -> FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                ip: 192.168.2.4
              host: TFARMER-MYSQL.wh.oracle.com
    host_validated: YES
sum_connect_errors: 3
1 row in set (0.02 sec)

That’s helpful information, and allows DBAs to identify problematic hosts before they are blocked.  Due to Bug#69807,

  [Read more...]
Spring Cleaning: Useless protocol commands
Employee +2 Vote Up -0Vote Down

In an earlier post, I commented on clients and utility programs which seem to no longer be useful, and opened (or referenced existing) public bug reports to deprecate and remove, where appropriate.  That effort came actually was the product of a different initiative:  I was looking for clients which might leverage the full spectrum of MySQL protocol commands in an effort to understand whether certain protocol commands are in use.  I thought I would share my observations, in the hope that we might also get feedback from others regarding usage of these commands. And even though it’s no longer spring (I started this post in April), I finally found time to finish this post.

The

  [Read more...]
Practical P_S: Fixing gaps in GLOBAL STATUS
Employee +1 Vote Up -0Vote Down

Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools.  Mark Leith even provided a patch, but it’s never been incorporated.  With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:

mysql> SELECT SUM(count_star) as pings
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
|    12 |
+-------+
1 row in set (0.02 sec)


Not only does PERFORMANCE_SCHEMA provide capabilities which mirror the STATUS

  [Read more...]
Fun with Bugs #21 - recently verified bugs in MySQL 5.6.13
+0 Vote Up -0Vote Down
Notable contribution of MySQL Community to MySQL 5.6.13 was explicitly recognized recently. But users and contributors still continue their efforts, as well as Oracle engineers. Even though MySQL 5.6.13 has been generally available just for few days, we already have several new bug reports and updates to known bugs at http://bugs.mysql.com. Let me present a short list with some comments.

  • Bug #69915 is a great example of a "new thinking" inside Oracle. Todd Farmer does not only write about new ways to use PERFORMANCE_SCHEMA in MySQL 5.6 in his blog, but also reports


  [Read more...]
Practical P_S: How idle are your connections?
Employee +2 Vote Up -0Vote Down

Idle connections can cause problems both at the application side, increasing the risk of connection timeouts for applications where persistent connections are used, and the server side, where resources remain allocated to idle connections.  Any application with persistent connections, such as a JDBC application using a connection pool, will have periods where connections are idle – but it’s good to know how much time is spent idle.  Too much idle time might mean connections pools configured to allow too many connections to sit idle in a connection pool, or not properly doing connection pool maintenance.

PERFORMANCE_SCHEMA in MySQL 5.6 makes it trivial to measure absolute time spent waiting.  This will show total, average

  [Read more...]
Practical P_S: How old are your connections?
Employee +3 Vote Up -0Vote Down

I’ve often wished that PROCESSLIST exposed when a connection was first established, and I find myself wishing for this information more now with MySQL 5.6.  Improvements to PERFORMANCE_SCHEMA make it trivial to see how much time is being spent in various operations for a given connection – but it would make some analysis (“what percentage of connection time is spent doing X?”) easier.

That said, it is possible to approximate connection age with PERFORMANCE_SCHEMA in MySQL 5.6.  I say “approximate” because results will vary based on what instrumentation exists, is enabled, and is collecting timing data.  That’s because we’re just doing a SUM() on the SUM_TIMER_WAIT column for all instrumented waits.  Here’s an example (FYI, I’m using

  [Read more...]
Practical P_S: Finding the KILLer
Employee +3 Vote Up -0Vote Down

In a previous post, I described how to leverage PERFORMANCE_SCHEMA in MySQL 5.6 to identify connections which had not been properly closed by the client.  One possible cause of connections being closed without explicit request from the client is when another process issues a KILL CONNECTION command:

mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 0     |
+-----------------+-------+
1 row in set (0.02 sec)

mysql> KILL CONNECTION 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'aborted_clients';
+-----------------+-------+
| Variable_name   | Value |
  [Read more...]
Practical P_S: From which hosts are connections being attempted?
Employee +2 Vote Up -0Vote Down

MySQL Server has an aborted_connect status counter which will show you the number of failed attempts to establish a new connection.  The manual describes potential causes as follows:

  [Read more...]
Practical P_S: Extending PROCESSLIST
Employee +2 Vote Up -0Vote Down

MySQL 5.6 introduced major advances to monitoring made via PERFORMANCE_SCHEMA, but also made a change in how it binds to the network by default.  In MySQL 5.5, the –bind-address configuration option defaulted to “0.0.0.0″, meaning only IPv4.  This changed to “*” in MySQL 5.6, accepting connections on both IPv6 and IPv4 interfaces.  Somehow (I’ve not looked into it yet), my (unsupported) WindowsXP installation now refuses to bind to IPv4,

  [Read more...]
Showing entries 1 to 30 of 74 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.