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 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 70 Next 10 Older Entries

Displaying posts with tag: performance_schema (reset)

On configuring the Performance Schema
Employee +2 Vote Up -0Vote Down
On configuring the Performance Schema

This article is a user guide about MySQL 5.6 Performance Schema configuration. As with many things, the way to approach problems may vary a lot based on systems, user experiences, or just plain opinions, so the "Your Mileage May Vary" caution applies here.

It is easy to get lost in details, and yet starting with the big picture in mind helps to understand not only how, but also more importantly why, to do things ...

The magic recipe is as follows
  • Define your goals
  • Define what to instrument
  • Define how much detail to collect
  • Provide sizing data
  • Monitor sizing problems

Define your goals
Performance instrumentation in general can be used for many different things, ranging from casual monitoring in production to debugging in








  [Read more...]
A Visual Guide to the MySQL Performance Schema
Employee +1 Vote Up -0Vote Down

If you haven’t explored the MySQL Performance Schema yet, this is a good place to start.  This is Performance Schema 101, a basic introduction to the MySQL 5.6 performance_schema, which records runtime statistics from the MySQL database. The performance_schema is intended to provide access to useful information about server execution while having minimal impact on server performance.  Performance_schema is the name of both the storage engine and the database itself, and it was initially implemented  in MySQL 5.5. In MySQL 5.6 the engineers added quite a bit of new instrumentation.

The performance_schema database uses views or temporary tables that actually use little to no persistent disk storage .Memory allocation is all done at server startup, so there is no ongoing memory reallocation or

  [Read more...]
MyISAM in a MySQL 5.6 InnoDB Only instance
+1 Vote Up -0Vote Down
With MySQL 5.5 the default storage engine was changed to InnoDB. This was a good step as most users expected MySQL to support transactions, row level locking and all the other InnoDB features, but with 5.1 they sometimes forgot to choose the right storage engine. As most databases don't have multiple storage engines and many MySQL users changed the default storage engine to InnoDB this made the switch to MySQL easier and the default behaviour more in line with what people expect from a relational database.

Changing the storage engine can of course be done on a per table or per instance basis (default-storage-engine in my.cnf). The temporary tables created with 'CREATE TEMPORARY TABLE ...' should not be forgotten. The performance of InnoDB or MyISAM for temporary tables can have quite some impact, especially with slow storage, a buffer pool which is too

  [Read more...]
MySQL 5.6 Performance Schema is GA
Employee +9 Vote Up -0Vote Down
The PERFORMANCE SCHEMA was first introduced in MySQL 5.5, and provided some instrumentation.

With MySQL 5.6, the existing instrumentation has been improved a lot, and a lot of new instrumentation was added also.

Now is a good time to review the overall picture ...

The performance schema tables


In 5.5, the tables available are:

mysql> show tables;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| cond_instances                               |
|









  [Read more...]
On MySQL Memory Usage and Configuration
+1 Vote Up -0Vote Down

I saw a post on profiling memory usage today and this reminds me of several discussions I have had with different people.

Why would you want to profile the memory usage? Usually to see where memory is allocated and for what purposes, and usually you only care when memory usage is higher than expected. That is a DBA normally wants to use all available memory on a server for mysqld, whatever that size may be.

Configuration parameters may be inappropriate and need adjusting, so having a way to determine the range of memory usage based on those parameters would be most helpful.  However, the configuration parameters as a whole put no limit on memory used, so different workloads can quite easily lead to memory being

  [Read more...]
MySQL 5.6, GTID and performance_schema
+1 Vote Up -0Vote Down

Not much to add really to the bug I’ve filed here: bug#67159.

Again this GTID stuff looks good, but seems to prevent changes in the configuration of performance_schema, which I think is not appropriate, especially as P_S now has lots of extra goodies and after 5.6 will surely have even more.

Fiddle with the Performance Schema
Employee +2 Vote Up -0Vote Down

I recently found an interesting tool to play with databases, SQLFiddle.

The tool is used to share some fragments of code, running on a live database, to allow users to not only see (read) the code, but also actually execute it, to play with the code.

See this link for all the details.

The nice part is that MySQL 5.5 is part of the supported databases, try it here.

It gets better: MySQL 5.6 is also there, so you get to try new 5.6 features, or your favorite query, in a blink, without having to do an installation.

And icing on the 5.6 cake, the PERFORMANCE_SCHEMA is also enabled, with grants given to the user to query performance schema tables.












  [Read more...]
Fun with Performance Schema
+4 Vote Up -0Vote Down
I'm using a very small MariaDB instance as a datastore for my YouLess energy monitor, my own mail server (postfix, roundcube). It's a virtual machine from a commercial VPS provider.

All data fits in memory and the overhead of running with performance_schema on is not an issue.

While I was reading a blog post about performance_schema by Mark Leith I wanted to see what P_S could tell me about my own server.

This is the output from the first query:
mysql> select * from file_summary_by_event_name order by count_read desc,count_write desc limit 10;
+--------------------------------------+------------+-------------+--------------------------+---------------------------+
| EVENT_NAME








  [Read more...]
A MySQL Replication Load Average with Performance Schema
Employee +8 Vote Up -0Vote Down

Monitoring MySQL’s replication has always been a bit hit and miss, especially when trying to detect whether a slave is becoming overloaded or not. There’s been a few ways to do this in the past:

  • Monitor the Seconds_behind_master variable from SHOW SLAVE STATUS, which is the difference between the timestamp passed down from the master that the event was executed, to the time that the SQL thread started executing the event.
  • Use something like mk-heartbeat, which operates in the same way as Seconds_Behind_Master (trying to show you the actual time difference between the master and slave),
  [Read more...]
Monitoring Processes with Performance Schema in MySQL 5.6
Employee +8 Vote Up -0Vote Down

I’ve written before about how you can monitor the last wait per connection with Performance Schema in MySQL 5.5.

That solution joined the performance_schema.events_waits_current with the performance_schema.threads and INFORMATION_SCHEMA.PROCESSLIST tables.

Joining to INFORMATION_SCHEMA.PROCESSLIST is a necessary evil

  [Read more...]
Helper Functions for ps_helper
Employee +3 Vote Up -0Vote Down

I love our community.

Not long after posting my update on ps_helper, I had a couple of comments around the formatting of values within the output. Daniël van Eeden gave the suggestion that I could add a couple of Stored Functions, for formatting byte and time based values.

Of course, this was a great idea – not least for myself, because I no longer have to worry about how to format certain columns in the output.

I’ve added the following:

format_bytes()
format_time()


  [Read more...]
Who’s leaking prepared statements?
Employee +2 Vote Up -0Vote Down

In my last post, I described a specific problem with prepared statements into which PERFORMANCE_SCHEMA can give visibility.  That made me wonder whether PERFORMANCE_SCHEMA can also be used to identify other areas where prepared statements run into problems.  The most significant problem tends to be leakage of prepared statements.  This can inflate memory usage, both on the server and application side, and it’s not uncommon to find applications which fail to close prepared statements.

So the question is, what can PERFORMANCE_SCHEMA tell us about how connections close (or more importantly, fail to close) prepared statements?

At the most basic level, one can check the number of PREPARE statements executed compared to DEALLOCATE PREPARE, and you can do that using global status variables.  You’re shooting for general equality between

  [Read more...]
Prepared statement peculiarities (P_S to the rescue)
Employee +3 Vote Up -0Vote Down

Prepared statements have been with MySQL since version 4.1, including the protocol plumbing that helps support it.  What I didn’t realize – until a recent expedition through a general query log – is that the mysql command-line interface doesn’t implement the protocol commands that support this explicitly.  I came to this realization after observing a byproduct of this behavior.

The initial observation that triggered this exploration was noting that PREPARE and EXECUTE statements, when issued from the mysql command-line interface, result in two entries per command in the general query log:

6 Query    PREPARE stmt FROM 'SELECT RAND()'
6 Prepare    SELECT RAND()
6 Query    EXECUTE stmt
6 Execute    SELECT RAND()

Contrast this behavior with what is seen when a client sends COM_PREPARE and

  [Read more...]
A few hacks to simulate mysqldump --ignore-database
+4 Vote Up -0Vote Down

A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

As a workaround, he proposes:
mysqldump --databases `mysql --skip-column-names \
-e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
FROM information_schema.schemata WHERE schema_name \
NOT IN ('mysql','performance_schema','information_schema');" \
>` >/mysql/backup/rds2.sql

It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

There are two






  [Read more...]
Performance Schema, nailing the host cache coffin down
Employee +3 Vote Up -0Vote Down
Every old mansion has some old history, and the older it is, the scarier it gets ...

MySQL is no exception: there are some very old ghosts still lurking inside the server.

One of these ghosts is the 'host cache', which haunts the server main entrance halls, just right pass the TCP/IP front door.

How long has this ghost been there ? From the very beginning it seems, as is was first spotted on April 26, 2006 ... Yes, that was 6 years ago, back in the 4.1 old times.

This ghost has been haunting DBA's worst nightmares since then.

Public sightings include:
- Bug#22821 Adding "SHOW HOST_CACHE" patch from Jeremy Cole
-











  [Read more...]
MySQL 5.6.4 PERFORMANCE SCHEMA
Employee +2 Vote Up -0Vote Down
New performance schema features in 5.6.4
As development of the 5.6 serie continues, a new milestone (5.6.4) is now available.

The source code for mysql-trunk is on launchpad, and the MySQL 5.6 documentation is also public.

In this milestone, a few new performance schema features have been implemented, as can be seen in the changelog.

MY.CNF, or easier configuration


Until now, the performance schema used a very simple, or shall I say, crude, way to decide what needs to be instrumented or not: flip the master switch on (performance_schema), and you get absolutely everything available, with as much details







  [Read more...]
Performance schema or COM_ status counters ?
Employee +2 Vote Up -0Vote Down

In MySQL 5.6.3, that is, a while ago already, the performance schema added instrumentation for statements.

This major enhancement seem to have gone unnoticed, so a quick review of how it works, especially compared to the existing COM_ status counters that everyone is used to, can perhaps put some light here.

The MySQL server maintains 'COM_%' counters for statements executed.

These counters are available in both the session and global status.

Let's look at a few:

mysql> select variable_name from information_schema.global_status
where variable_name like "com\_%" order by variable_name limit 20;
+------------------------+
| variable_name          |
+------------------------+
| COM_ADMIN_COMMANDS     |
|











  [Read more...]
Monitoring Related OpenWorld Talks
Employee_Team +0 Vote Up -0Vote Down

I gave two monitoring related talks at OpenWorld, thanks to all that came along!

Both were monitoring related, the first an introduction to MySQL Enterprise Monitor, and the second a look at some of the new instrumentation that is getting developed, primarily within the MySQL 5.6 release. 

If you'd like to get a quick overview of how MySQL Enterprise Monitor works, then take a look through the "Getting to Know MySQL Enterprise Monitor" talk. This gives you a high level view of how the different pieces fit together, and then each of the important factors within the user interface:

Getting to Know MySQL Enterprise Monitor

And if you are interested in seeing how the instrumentation and monitoring

  [Read more...]
Monitoring Related OpenWorld Talks
Employee_Team +0 Vote Up -0Vote Down

I gave two monitoring related talks at OpenWorld, thanks to all that came along!

Both were monitoring related, the first an introduction to MySQL Enterprise Monitor, and the second a look at some of the new instrumentation that is getting developed, primarily within the MySQL 5.6 release. 

If you'd like to get a quick overview of how MySQL Enterprise Monitor works, then take a look through the "Getting to Know MySQL Enterprise Monitor" talk. This gives you a high level view of how the different pieces fit together, and then each of the important factors within the user interface:

Getting to Know MySQL Enterprise Monitor

And if you are interested in seeing how the instrumentation and monitoring

  [Read more...]
Explaining performance schema tables ... with pictures
Employee +2 Vote Up -0Vote Down
The problem For illustration, let's assume a MySQL server, with a couple of users (A, B, C, ...) who perform queries against some tables (T1, T2, etc) in the database.
Sooner of later, someone will want to know which user is performing which amount of table io against which table ... How can instrumentation help with that ?

T1 T2 T3 T4 T5 User A ??? ??? ??? ??? ??? User B ??? ??? ??? ??? ??? User C ??? ??? ??? ??? ???
Performance schema aggregates
The performance schema provides different "summaries" tables, which aggregate statistics collected using different criteria.

TABLE performance_schema.table_io_waits_summary_by_table


T1 T2 T3 T4 T5 User A ??? ??? ??? ??? ??? User B ??? ???






  [Read more...]
Performance schema, overhead tuning
Employee +6 Vote Up -0Vote Down
Performance schema performance tuning One of the most important question users ask before deciding to use the performance schema is: what is the overhead ? The underlying concern of course is to make sure deploying the performance schema does not negatively impact production ("First, do no harm").
The question is simple, and yet the answer is not so simple, as it depends on so many things.
As seen in some benchmarks done already, the overhead when using the performance schema can vary a lot, and in some cases really causes degradations.
A lot of time has been spent investigating the root causes (note the plural form) for the performance schema overhead in general, so I think it is a good time to share current findings.
Server workload Analysing performances, and testing the effects of different code or configuration changes, can hardly be done in



  [Read more...]
Monitoring MySQL IO Latency with performance_schema
Employee +4 Vote Up -0Vote Down

Baron pointed to two really good articles recently by Brendan Gregg on Filesystem Latency (part1 / part2), that I too would recommend everybody read.

They rightly point out that trying to correlate IO statistics from tools such as iostat to a databases workload, especially when that database is on a shared resource, is fraught with complications – and further to that, not the full picture anyway, even on a dedicated system. Brendan

  [Read more...]
Using MySQL 5.6 to find queries creating disk temporary tables
+2 Vote Up -0Vote Down

In my previous post, I’ve showed how to use Dtrace to find queries creating disk temporary tables (only available for OS with dtrace: solaris, freebsd, etc).

In MySQL 5.6 (which is not released yet, use “labs” version for now) we can use new performance_schema table events_statements_history or events_statements_history_long to find all performance metrics for all queries including created disk/memory tables, use of index, etc. WOW! This is what I have been waiting for a long time!

To illustrate, I have grabbed mysql-5.6.3-labs-performance-schema-linux2.6-x86_64.tar.gz from labs.mysql.com (this feature is only in labs version) and run sysbench readonly test (you need

  [Read more...]
Performance schema faq #2, what are all these server startup options ?
Employee +3 Vote Up -0Vote Down

One of the first thing to do to use the performance schema is to add "performance_schema" in the my.cnf file.

This typically prompts a long list of question from users, as there is a lot to know here.

This article attempts to cover more specifically this area.

It is long, but (I hope) also worth it, so keep reading ;)

Also, since some new performance schema features have been advertised in the mysql labs, this article also covers some never seen before parts of the performance schema.

Really, keep reading ;)

Q: Why do I even have to do anything ? Can the performance schema not be enabled by default ?

No. It could, but this is not a good choice.

Let's face it, some users do not even read the documentation.

If someone is not even aware that the performance schema feature exists in 5.5 (or 5.6), turning it on

  [Read more...]
Monitoring Table and Index IO with PERFORMANCE_SCHEMA
Employee +6 Vote Up -0Vote Down

Another new feature of performance_schema within 5.6 is the ability to monitor both table and index IO – this was added in two parts:

If you read my blog on graphing statement wait times – some of the data that WL#4895 exposes is seen within the examples there, they are within the “light coral” coloured blocks, such as the following from the

  [Read more...]
Performance schema FAQ #1, enable without a server restart
Employee +3 Vote Up -0Vote Down

Q: Is it possible to enable / disable the performance schema at runtime ?

A: Yes.

Configure, configure and configure

The performance schema is affected by three distinct set of configuration parameters, that take effect at compile time, server startup, or runtime.

Compile time options

When building from the source code, make sure the cmake flag 'WITH_PERFSCHEMA_STORAGE_ENGINE' is set to 'ON' (it is by default).

For packages built by Oracle, the performance schema is included by default.

Server startup options

Simply add the 'performance_schema' option to your my.cnf configuration file.

The effect of this option seems mis understood, leading people to think that the performance schema can only be enabled or disabled at server startup time, which is not the case.

The role of this startup option,

  [Read more...]
What a difference Atomics can make
Employee +4 Vote Up -0Vote Down

Following up to my previous blog on graphing statement execution in performance_schema, Sunny Bains on the InnoDB team pointed out that in looking at the INSERT graph, he didn’t think I had atomic operations enabled within my build.

Particularly here (from trunk):

225 /******************************************************************//**
226 Increments lock_word the specified amount and returns new value.
227 @return lock->lock_word after increment */
228 UNIV_INLINE
229 lint
230 rw_lock_lock_word_incr(
231 /*===================*/
232         rw_lock_t*      lock,
  [Read more...]
A Big Bag of Epic Awesomeness
Employee +9 Vote Up -0Vote Down

I tried to come up with a number of topics for this post, but none seemed to really convey what I really feel.. And really this blog is about all of them..

  • A look at PERFORMANCE_SCHEMA.STAGES and PERFORMANCE_SCHEMA.STATEMENTS
  • Graphing MySQL Statement Execution
  • A MySQL visual EXPLAIN plan with PERFORMANCE_SCHEMA
  • Tracing Sessions with PERFORMANCE_SCHEMA

If that doesn’t whet your appetite (and trust me, I need to, this post is long, but I feel is worth reading all the way to the end), then let me start out by asking the question:

Wouldn’t you like to be able to trace what a SQL statement did, either in the same or another session, on a production instance, after the fact? Wouldn’t you

  [Read more...]
The PERFORMANCE SCHEMA development continues
Employee +10 Vote Up -0Vote Down

Now that mysql 5.5 is GA, development for the performance schema continues in the mysql-trunk code base. A lot of projects have been completed already, which are worth mentioning.

New table io instrumentation

Table io is now instrumented. As the performance instrumentation is located in the MySQL layer and not inside the storage engine, this instrumentation is de facto available to all storage engines, with zero code change.

New table lock instrumentation

Table locks are also instrumented. It is a nice complement to table io, and can be enabled or disabled independently from table io. Not all table waits are io problems, some are also contention caused by locks ... and there is now a way to observe that.

New stages instrumentation

Major execution phases of a statement, called stages, are now instrumented. This instrumentation is

  [Read more...]
Evolution of MySQL metadata
+5 Vote Up -0Vote Down
I was looking at the latest MySQL versions, and I happened to notice that there has been a great increment in the number of metadata tables, both in the information_schema and performance_schema databases. So I made a simple count of both schemas in the various versions, and draw a graph. The advance looks straightforward.

versionInformation_schemaperformance_schema5.0.921705.1.542805.1.54 with innodb plugin3505.5.837175.6.24823


The difference between 5.0 and 5.6 is staggering. We came from 17 to 71 metadata total tables. A stacked bar chart helps visualize the changes.



I noticed, BTW, that








  [Read more...]
Previous 30 Newer Entries Showing entries 31 to 60 of 70 Next 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.