Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 76 10 Older Entries

Displaying posts with tag: performance_schema (reset)

When your query is blocked, but there is no blocking query
+1 Vote Up -0Vote Down
When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state.

Example:
mysql [information_schema] > select trx_id,trx_state 
    -> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974 | LOCK WAIT |
| 7962 | RUNNING |
+--------+-----------+
2 rows in set (0.00 sec)

Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful.

Let's try to recreate the situation and see exactly what happened. I'll use













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

I’ve just released the 1.1.0 version of the MySQL sys schema.

This release is hugely pleasing to me, in that I actually didn’t have to do too much work on it myself! There were a significant number of contributions from Jesper Wisborg Krogh and Arnaud Adant, both MySQL Support Engineers (at the time at least, Arnaud has moved on to pastures new now), as well as again from Joe Grasse.

Thank you all for your contributions!

Here’s a summary of the changes:

Improvements

  • Added host summary views, which have the same structure as the user summary views, but aggregated by host instead (Contributed by Arnaud Adant)
      [Read more...]
    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...]
    Showing entries 1 to 10 of 76 10 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.