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 19

Displaying posts with tag: graphs (reset)

Measure the impact of MySQL configuration changes with Percona Cloud Tools
+1 Vote Up -0Vote Down

When you make a change to your MySQL configuration in production it would be great to know the impact (a “before and after” type of picture). Some changes are obvious. For many variables proper values can be determined beforehand, i.e. innodb_buffer_pool_size or innodb_log_file_size. However, there is 1 configuration variable which is much less obvious for many people working with MySQL: query_cache.

The idea of query cache is great, however, there are a lot of

  [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...]
Generating Google line charts with SQL, part II
+3 Vote Up -0Vote Down

This post continues Generating Google line charts with SQL, part I, in pursue of generating time series based image charts.

We ended last post with the following chart:

  [Read more...]
Generating Google line charts with SQL, part I
+1 Vote Up -0Vote Down

In this series of posts I wish to show how Google Charts can be generated via SQL. We discuss the Google Charts limitations which must be challenged, and work towards a simple chart.

I’m going to present the algorithm I use in mycheckpoint, a MySQL monitoring utility, which generates Google charts by raw data using views. An example of such chart follows:

  [Read more...]
mycheckpoint (rev. 170): improved custom queries; local charting; page/swap I/O monitoring; improved HTML reports
+2 Vote Up -0Vote Down

Revision 170 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

  • Improved custom queries: lifting of limitations from previous, introductory revision; better HTML presentation
  • Local, inline charting: no rendering of Google Charts, unless explicitly requested. All charts are now rendered locally using JavaScript. No data is now sent over the network.
  • Page/Swap I/O monitoring: now monitoring for page ins and outs, swap ins and outs (Linux only).
  • Improved HTML reports: several improvements on presentation (see
  [Read more...]
mycheckpoint (rev. 132): custom monitoring, custom charts, process list dump
+1 Vote Up -0Vote Down

Revision 132 of mycheckpoint has been released. New and updated in this revision:

  • Custom monitoring: monitoring & charting for user defined queries
  • HTML reports for custom monitoring
  • Process list dump upon alert notifications

Custom monitoring & charts

Custom monitoring allows the user to supply with a query, the results of which will be monitored.

That is, mycheckpoint monitors the status variables, replication status, OS metrics. But it cannot by itself monitor one’s application. Which is why a user may supply with such query as:

SELECT COUNT(*) FROM shopping_cart WHERE is_pending=1

Such a query will tell an online store how many

  [Read more...]
Kontrollbase – graph “no data to display” on new install has been fixed
+0 Vote Up -0Vote Down
If you have been wondering why the overview and graphs pages say “no data to display” on the graphs when you first install Kontrollbase, it’s because there’s no data in the database being returned from the queries that generate the graphs – this is because a new install has no data to graph. This has […]
Static charts vs. interactive charts
+1 Vote Up -1Vote Down

I’m having my usual fun with charts. Working on mycheckpoint, I’ve generated monitoring charts using the Google Chars API. But I’ve also had chance to experiment and deploy interactive charts, JavaScript based. In particular, I used and tweaked dygraphs.

I’d like to note some differences in using charts of both kinds. And I think it makes a very big difference.

Static charts

I’ll call any image-based chart by “static chart”. It’s just a static image. Example of such charts are those generated by Google Image Charts (they now also have new, interactive charts), or RRDtool. Show below is

  [Read more...]
mycheckpoint (rev. 88): mount points monitoring, improved charting, enhanced auto-deploy
+0 Vote Up -0Vote Down

Revision #88 of mycheckpoint is released. In this revision:

  • Disk space monitoring
  • Improved charting
  • Enhanced auto-deploy
  • And more…

Disk space monitoring

mycheckpoint now monitors (on Linux only) three mount points:

  • The “/” (root) mount point
  • The datadir mount point
  • The tmpdir mount point
  • It may well be the case that two of the above (or perhaps all three of them) share the same mount point. For example, if there isn’t any particular partition for “/tmp“, it is possible that the tmpdir (by default

      [Read more...]
    mycheckpoint rev. 76: OS monitoring, auto deploy, brief HTML and 24/7 reports
    +2 Vote Up -0Vote Down

    Revision 76 of mycheckpoint comes with quite a few improvements, including:

    • OS monitoring (CPU, load average, memory)
    • Auto-deploy
    • Improved charting
    • Brief HTML reports
    • 24/7 charts

    OS Monitoring

    When monitoring the local machine, mycheckpoint now monitors CPU utilization, load average, memory and swap space.

    This only applies to the Linux operating system; there is currently no plan to work this out for other operating systems.

    Examples:

    mysql> SELECT os_cpu_utilization_percent FROM sv_report_chart_sample;
    
    
      [Read more...]
    New and noteworthy in mycheckpoint (rev. 57)
    +1 Vote Up -0Vote Down

    Rev. 57 of mycheckpoint has been released and is available for download.

    New and updated in this revision:

    Remote host monitoring

    It is now possible to monitor one host, while writing into another. Either or both could be remote hosts:

    mycheckpoint
      [Read more...]
    OQGRAPH engine on MySQL University – 5 Nov 2009 10:00 UTC
    +0 Vote Up -0Vote Down

    Only a few weeks after Walter’s session on Multi-Master Replication with MMM and thanks to the great gang at MySQL Docs (my colleagues from long ago!) I’ll be doing a MySQL University session in a few days, about the GRAPH computation engine. From talks/demos I’ve done about it so far, I’ve learnt that people love it but there are lots of interesting questions. After all, it’s a pretty new and in a way exotic thing.

    MySQL University uses DimDim, an online presentation service. You’ll see slides, and hear my voice. You can also type questions in a live chat room. We actually even got desktop sharing working so a live demo is possible, we’ll see how that goes on the day (I’ll make sure to

      [Read more...]
    SQL multi line chart
    +6 Vote Up -0Vote Down

    Time for another charting SQL query. I wish to present a single-query generated multi-line/area chart. I’ll walk through some of the steps towards making this happen. By the end of this post I’ll present some real-data charts, area charts and colored charts.

    +---------+-----------------------------------------------------------------------------------+
    | y_scale | chart                                                                             |
    +---------+-----------------------------------------------------------------------------------+
    | 1       | ****---------#######----------------------------------------*******--------###### |
    | 0.88    | ----***---###-------##-----------------------------------***-------***---##------ |
    | 0.75    |
      [Read more...]
    Grouping by Arbitrary Time Ranges (Graphing What You Can See)
    Employee +0 Vote Up -0Vote Down

    First, the back story. One of the MEM (http://www.mysql.com/products/enterprise/monitor.html" target="_blank) developers asked me today about an interesting problem:

    We have graphs in MEM that can be generated against an arbitrary time range in the UI - even for a year or more if the data is not purged. Currently MEM does not do any kind of rolling up of the data (in an RRD style), and pulls graph data from each agent/instance on a 1 minute interval. So if you wanted to pull, for instance, the last 3 months worth of data in to a graph - the server back end basically goes back to the database and requests all of the rows - all ~43,829 of them, oh, and that’s for each series - and then calculate deltas on the server side if need be (we store raw values), and stuffs the data in to a graphing library to draw the graph.

    Further, graphs are only of a limited (but

      [Read more...]
    GRAPH engine – Mk.II
    +3 Vote Up -0Vote Down

    The GRAPH engine allows you to deal with hierarchies and graphs in a purely relational way. So, we can find all children of an item, path from an item to a root node, shortest path between two items, and so on, each with a simple basic query structure using standard SQL grammar.

    The engine is implemented as a MySQL/MariaDB 5.1 plugin (we’re working on a 5.0 backport for some clients) and thus runs with an unmodified server.

    Demo time! I’ll simplify/strip a little bit here for space reasons, but what’s here is plain cut/paste from a running server, no edits

    -- insert a few entries with connections (and multiple paths)
    insert into foo (origid, destid) values (1,2), (2,3), (2,4), (4,5), (3,6), (5,6);
    -- a regular table to join on to
    insert into people values (1,"pearce"),(2,"hunnicut"),(3,"potter"),
      [Read more...]
    SQL pie chart
    +11 Vote Up -0Vote Down

    My other half says I’m losing it. But I think that as an enthusiast kernel developer she doesn’t have the right to criticize people. (”I like user space better!” – she exclaims upon reading this).

    Shown below is a (single query) SQL-generated pie chart. I will walk through the steps towards making this happen, and conclude with what, I hope you’ll agree, are real-world, useful usage samples.

    +----------------------------------------------------------------------+
    | pie_chart                                                            |
    +----------------------------------------------------------------------+
    |                                                                      |
    |                         ;;;;;;;;;;;;;;;;;;;;;                        |
    |                  oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;                 |
    |
      [Read more...]
    Auto scaling, scaled SQL graphs concluded
    +1 Vote Up -0Vote Down

    I wasn’t sure I was to go this far. After catching breath the following have been added to Generic, auto scaling, scaled SQL graphs, and these will conclude my current hacks:

    • Displaying X-axis min/max values.
    • Support for Y-axis values precision.
    • Support for pre-defined scale range.

    The addition of the above makes for presentable, usable graphs. See also sample graphs at the end of this post.

    Step 8: adding X-axis values

    I add minimum/maximum X-scale values to the graph. What was just ordering_column before, now turns to be the x in the y = f(x) function.

    SELECT
      y_scale,
      horizontal_bar
      FROM
      (
      SELECT
        @row_number := @row_number+1,
        CASE
      [Read more...]
    Generic, auto scaling, scaled SQL graphs
    +3 Vote Up -0Vote Down

    In Rotating SQL graphs horizontally, I have shown how to rotate an ASCII SQL graph into horizontal position.

    I was dissatisfied with some parts of the solution, which I will show now how to fix:

    • I had to manually scale the graph values so as to fit nicely into screen.
    • I had to rely on hard coded scaling schemes.
    • I had to rely on hard coded column names.
    • I had no y-axis legend.

    I will now present an SQL query which allows for pluggable queries, which creates self, auto scaling graphs, along with y-axis scales.

    Using deeply nested subqueries, we will evolve a simple SELECT query into an elaborate graph. I will present the many steps required, followed by explanations and sample results. But in the end

      [Read more...]
    Showing entries 1 to 19

    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.