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

Displaying posts with tag: performance_schema (reset)

Memory summary tables in Performance Schema in MySQL 5.7
Employee_Team +1 Vote Up -0Vote Down

One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and corresponding views in sys schema

And as troubleshooting freak I have huge reason to greet this feature.

Before version 5.7 we had very limited abilities to diagnose memory issues in MySQL. We could use operating system tools, such as vmstat, top, free, but they only showed what MySQL server uses memory, but do not show how. In version 5.7 things changed. …



  [Read more...]
Performance Schema memory tables and rightless users
Employee_Team +4 Vote Up -0Vote Down

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets …



  [Read more...]
pstop – a top-like program for MySQL (based on performance_schema)
+0 Vote Up -0Vote Down

I have been working with MySQL for some time and it has changed significantly from what I was using in 5.0 to what we have now in 5.6. One of the biggest handicap we’ve had in the past is to not be able to see what MySQL is doing or why.

MySQL 5.5 introduced us to performance_schema. It was a good start but quite crude. MySQL 5.6 gave us a significant increase in stuff that allows you to see what is going on inside MySQL. That’s great, except it’s hard to read, the documentation is good, but not oriented at the DBA but more at the MySQL developer (that’s what it seems like at least). So most of us have ignored it. Others …

  [Read more...]
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 …












  [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 …

  [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  …

  [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 …

  [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 …

  [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 …

  [Read more...]
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 …
  [Read more...]
Showing entries 1 to 10 of 79 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.