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 17

Displaying posts with tag: performance schema (reset)

Using MySQL 5.6 Performance Schema in multi-tenant environments
+0 Vote Up -0Vote Down

Hosting a shared MySQL instance for your internal or external clients (“multi-tenant”) was always a challenge. Multi-tenants approach or a “schema-per-customer” approach is pretty common nowadays to host multiple clients on the same MySQL sever. One of issues of this approach, however, is the lack of visibility: it is hard to tell how many resources (queries, disk, cpu, etc) each user will use.

Percona Server contains userstats Google patch, which will allow you to get the resource utilization per user. The new MySQL 5.6 performance_schema has even more instrumentation which can give you a better visibility on per-user or

  [Read more...]
MySQL Performance Schema : Prepared Statements Instrumentation
Employee_Team +3 Vote Up -0Vote Down

MySQL 5.7.4 has a new Performance Schema feature, namely instrumentation for prepared statements. This instrumentation gives details of PREPARE and EXECUTE statistics for a prepared statement.

New Table
New table added to display run time statistics of Prepared Statements is named as prepared_statements_instances.

mysql> describe performance_schema.prepared_statements_instances;
+———————–+——————————————————
| Field                        | Type
+———————–+——————————————————
|




  [Read more...]
Getting started with Performance Schema and MySQL Workbench 6.1.2 Beta
Employee +2 Vote Up -0Vote Down

MySQL’s Performance schema is a relatively new tool for measuring performance and MySQL Workbecn 6.1.2 is the latest beta of that software. I have not had a lot of time to play with performance schema but now I am taking my first steps with the help of Workbench. Startup Workbench and you will find under the Navigator an item labeled Performance Schema Setup. Flip the toggle from OFF to ON and then start exploring.

InnoDB Buffer stats by Schema are show here — one of more than twenty pre-established metrics available.

Now you can run queries and see what the costs are, where the server is waiting, or what indexes remain unused. You

  [Read more...]
ps_helper and its migration to sys
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
Performance Schema implementation Internals: Registering instruments
Employee_Team +0 Vote Up -0Vote Down

This is the very first post in the series of Performance Schema Implementation Internals. This series is for MySQL Developers to understand implementation of Performance Schema. For user point of view of performance Schema, please refer to mysql documentation link : http://dev.mysql.com/doc/refman/5.7/en/performance-schema-quick-start.html

This post talks about the mechanism to register instruments in Performance Schema (referred as P_S from hereon) so that their statistics could be collected at runtime.

To understand this, lets first understand different instrument classes in P_S.

Instrument Classes in P_S:

An enum PFS_class_type is there to keep track of all possible classes of

  [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...]
Thanks For Attending MySQL Connect
Employee_Team +2 Vote Up -0Vote Down

MySQL Connect 2013 was held this past Saturday through Monday, and I would like to extend a big thank you to everyone who attended my sessions, I talked to or otherwise took part in the conference.

I had two sessions as well as participated in a Birds of the Feather session with the Community and Support teams. The slides have been uploaded the the Content Catalog but they are not available for download from there yet. Until then you can download them from the the links below:

  [Read more...]
Troubleshooting High Memory Usage with MySQL on Windows
+2 Vote Up -0Vote Down

I was testing out the latest MySQL 5.6 on Windows (having used the auto-installer) and happened to notice my RAM usage (via Windows Task Manager) was reporting quite a high value, when I had very modest ram/buffer settings (should have been around 40M, but instead it was around 400M).

After double/triple-checking my settings to make sure I didn’t overlook something obvious, I searched the bugs database, and ran across bug #68287:

“High Memory Usage with MySQL 5.6.12 GA in ‘Development Machine’ mode”

Turns out, using the auto-installer set the value of table_definition_cache=1400, when the minimum value is 400. Reducing it to 400, and restarting MySQL immediately lowered the RAM usage, and is the “work-around”

  [Read more...]
Easier Overview of Current Performance Schema Setting
Employee_Team +2 Vote Up -0Vote Down

While I prepared for my Hands-On Lab about the Performance Schema at MySQL Connect last year, one of the things that occurred to me was how difficult it was quickly getting an overview of which consumers, instruments, actors, etc. are actually enabled. For the consumers things are made more complicated as the effective setting also depends on parents in the hierarchy. So my thought was: “How difficult can it be to write a stored procedure that outputs a tree of the hierarchies.” Well, simple enough in principle, but trying to be general ended up making it into a lengthy project and as it was a hobby project, it often ended up being put aside for more urgent tasks.

  [Read more...]
Webinar: MySQL 5.6 Performance Schema
+2 Vote Up -0Vote Down

This Wednesday, May 15 at 10 a.m. Pacific, I’ll be leading  a Webinar titled, “Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks.

In this Webinar I will offer an overview of Performance Schema, focusing on new features that have been added in MySQL 5.6, go over the configuration and spend most time showing how you can use the wealth of information Performance Schema gathers to understand some of the typical performance bottlenecks.

 

Other areas of focus

  [Read more...]
MySQL 5.6 : Performance Schema - Statement Digest
Employee +6 Vote Up -0Vote Down
Along with lots of new features in performance schema, a new summary table, event_statement_summary_by_digest, has been added to MySQL 5.6. This tables gives a consolidated view of different statements executed/executing on MySQL server.

WHAT IS DIGEST?
Digest is a hash calculated on normalized form of statement text which removes general property of a statement but keeps specific property. For ex. for two statements X and Y,

Removing general property means
normalized (X) == normalized (Y)
Preserving specific property after normalization means:
normalized (X) != normalized (Y)

On these normalized statement, a hash is calculated which is called DIGEST. So digest is a unique representation of statements having same structure.


Example :
SELECT 1 FROM t1; 
SELECT 2 FROM t1; 














  [Read more...]
MySQL Performance Schema - OSI-Days, 2012
Employee +0 Vote Up -0Vote Down
Here is the MySQL-Performance Schema presentation which I used at OSI, 2012.

http://www.slideshare.net/mayankmysql/osimysqlperformance-schema
MySQL Performance Schema - OSI-Days, 2012
Employee +1 Vote Up -0Vote Down
Here is the MySQL-Performance Schema presentation which I used at OSI, 2012.

http://www.slideshare.net/mayankmysql/osimysqlperformance-schema
Why I am So Excited About the MySQL Performance Schema
Employee_Team +5 Vote Up -0Vote Down

The improved Performance Schema in MySQL 5.6 provides a new way of investigating issues in the database. Many issues that previously required tools such as strace, dtrace, etc. can now be investigated directly from inside MySQL in a platform independent way using standard SQL statements.

The Performance Schema is enabled by default starting from the latest milestone release, 5.6.6. You have instruments which are the things you can measure, and consumers which are those that use the measurements. Not all instruments and consumers are enabled out of the box, however once the plugin is enabled, individual instruments and consumers can be switched on and off dynamically.

As an example take the case mentioned in What’s the innodb main

  [Read more...]
What is the MySQL Performance Schema and Why is It Needed?
Employee_Team +7 Vote Up -0Vote Down

When you have a non-trivial database installation, you will inevitably sooner or later encounter performance related issues ranging from a query not executing as fast as desirable to complete meltdowns where the database does not respond at all.

Until MySQL 5.5 the tools available to investigate what is going on inside MySQL were somewhat limited. Some of the tools were:

  • The slow and general query logs
  • The status counters available through SHOW [SESSION|GLOBAL] STATUS
  • Storage engine status, e.g. SHOW ENGINE INNODB STATUS
  • The EXPLAIN command to investigate the query plan of a SELECT statement
  • SHOW PROFILE to profile one or more queries
  • The MySQL error log

All of these tools are very useful, but also have their limitations, for example the SHOW STATUS mainly consists of counters

  [Read more...]
Attending MySQL Connect 2012
Employee_Team +1 Vote Up -0Vote Down

In the last weekend of September, MySQL Connect 2012 will take place in San Francisco as part of Oracle OpenWorld. MySQL Connect is a two day event that allows the attendees to focus on MySQL with the possibility to add on to Oracle OpenWorld or JavaOne.

I will be running the Hands-On Lab session Improving Performance with the MySQL Performance Schema (HOL10472) with the following

  [Read more...]
Damm you Peformance Schema
+0 Vote Up -2Vote Down

One significant new feature of MySQL 5.5 is the Performance Schema. I recently performed an upgrade from 5.0 to 5.5, however my check of differences in the MySQL variables via mysqladmin variables failed because we now have a new record long variable name “performance_schema_events_waits_history_long_size”.

The full list of new performance_schema variables are:

> | performance_schema_events_waits_history_long_size | 10000                                                                                                                  |
> | performance_schema_events_waits_history_size      | 10                                                                                                                     |
> | performance_schema_max_cond_classes               | 80
  [Read more...]
Showing entries 1 to 17

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.