Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.

To summarize, below are the different queries you can run:

Dataset Size

I the past I was using something like this :

But now with sys schema being installed by default, I encourage you to use some of the formatting functions provided with it. The query to calculate the dataset is now:

SELECT sys.format_bytes(sum(data_length)) DATA,
       sys.format_bytes(sum(index_length)) INDEXES,
       sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER  BY data_length + index_length;

Let’s see an example:

PS_history is a tool which collects historical snapshots of the PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values over time, for example, it is possible to look at the 95 th percentile response time for a query over time.

PS_history is stored procedure and event based, and thus it resides entirely inside of the database with no external dependencies. It uses a clever technique to capture all of the P_S data in one consistent snapshot. This ensures that all of the sys_history views (bundled now with PS_history) have a consistent set of data.

By default, as long as the event_schedule is enabled, PS_history will collect data every 30 seconds. If a snapshot takes 30 seconds, there will be a 30 second delay before the next snapshot starts. This value can be changed by calling the `ps_history`.`set_collection_interval`(N) where N is the number of seconds between samples.

The `sys_history` schema is …

PS_history is a tool which collects historical snapshots of the PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values over time, for example, it is possible to look at the 95 th percentile response time for a query over time.

PS_history is stored procedure and event based, and thus it resides entirely inside of the database with no external dependencies. It uses a clever technique to capture all of the P_S data in one consistent snapshot. This ensures that all of the sys_history views (bundled now with PS_history) have a consistent set of data.

By default, as long as the event_schedule is enabled, PS_history will collect data every 30 seconds. If a snapshot takes 30 seconds, there will be a 30 second delay before the next snapshot starts. This value can be changed by calling the `ps_history`.`set_collection_interval`(N) where N is the number of seconds between samples.

The `sys_history` schema is …

In this post I'd like to mention my colleagues from MySQL Support who had joined the team when Oracle already acquired Sun (and MySQL as a part of it), in 2010-2012. There were several really great engineers among them:

• Jesper Krogh - with his 31 public bug reports, numerous blog posts (check also this one), ps_tools and presentations, Jesper is well known to MySQL community. He had joined Oracle as Senior MySQL Support Engineer in March, 2011 and is a Senior Principal Technical Support Engineer at Oracle now. Check one of his …

Unfortunately I am not going to Percona Live Amsterdam 2015 that starts next week. Somebody has to work and keep those customers happy who prefer to work as usual even during such a festival.

I am still asking myself: if I'd be able to go there, what tutorials and sessions I'd like to attend (besides those I'd present)? "All of them" is not a correct answer, as often great and useful sessions happens at the same time in different places. So, I decided to create a list of sessions for myself (as a reminder to check slides at least after they are published), and I'd like to share it.

I tried to pick up one session …

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 two sessions for the transactions and a third to monitor the …

MySQL’s Performance Schema is an incredibly rich and versatile instrumentation engine, but apparently, with great power, comes increased complexity for the user to understand up front.

I think this is pretty natural:

• The more flexibility you provide to the user on what to monitor (and this is one of the major goals of Performance Schema) – the more there is to twiddle from a configuration perspective.
• The more data we provide in as generic a ways as possible – the more data there is.

Performance Schema is also only going to grow over time. There are many many missing bits of instrumentation within MySQL that users want us to add, and for the most part, most of those new things are now going in to Performance Schema. Things such as memory …