Jet Profiler for MySQL

myterm - extensible mysql command line client

Bjorn Melinder — Thu, 05 Nov 2009 11:47:22 -0500

What if I type this:

myterm> SELECT engine, count(*) AS count FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC | chart pie | browser

and Firefox says:

What's that?

I just launched an open-source project on launchpad called myterm. Myterm is a crossover between the standard mysql command line client and the concept of pipes and filters in bash. You can use it to run queries and filter the produced result set in various ways using pipe chaining. This lends itself to quite a lot of different use cases, for instance graphical charts, md5 checksums and different presentation forms to name a few. It has browser integration using shell exec, which means it can render html result sets or charts in your browser. And since most stuff is written using plugins, it will work well to serve as a hub for hooking in more and more tools for data transfer, dumping, backup, simplify monitoring and so on. Sort of like inversed bash-scripting; you start inside the db going out.

The model is based on commands, filters, presenters and dests. The COMMAND (usually a query) produces a result set which is sent of to a chain of filters. Each FILTER can process the result set and reformat the data. A PRESENTER takes a result set and renders it in some form, e.g. plain text, tab separated values, html table or chart. Finally, a DEST is simply the destination of the rendered output, such as standard out, a file or the browser. A full command chain:

COMMAND --resultset--> FILTER --resultset--> PRESENTER --mime--> DEST

Some examples

First some standard output:

myterm> SHOW PROCESSLIST
+------+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------------+------+---------+------+-------+------------------+
| 4789 | root | 127.0.0.1:59047 | test | Query | 0 | | SHOW PROCESSLIST |
+------+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.000 sec)

Then, lets use the cols filter:

myterm> SHOW PROCESSLIST | cols 2-3
+------+-----------------+
| User | Host |
+------+-----------------+
| root | 127.0.0.1:59047 |
+------+-----------------+
1 row in set (0.000 sec)

which filters out some columns. Similarly, a basic grep filter exists. Lets hope people won't stop using indexes just because of this:

myterm> SHOW DATABASES | grep info
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)

For chart rendering, myterm uses libchart by Jean-Marc Tremeaux. Take a look at the biggest tables:

myterm> SELECT CONCAT(table_schema, '.', table_name) AS 'Table', data_length + index_length AS Bytes FROM information_schema.tables ORDER BY 2 DESC | other 7 | chart hbar

The other filter just reduces any rows after 6 into a total item named Other. Resulting bar chart:

The insertify plugin will reverse engineer a result set into a create statement and an insert statement. This is not as rock-solid or performant as CREATE...SELECT (it won't pick up indexes), but works for creating temporary snapshots or test data:

myterm> SHOW PROCESSLIST|insertify|tsv -N -E
CREATE TABLE 'some_table' (
'Id' bigint(11) NOT NULL DEFAULT '',
'User' varchar(16) NOT NULL DEFAULT '',
'Host' varchar(64) NOT NULL DEFAULT '',
'db' varchar(64) DEFAULT NULL,
'Command' varchar(16) NOT NULL DEFAULT '',
'Time' int(7) UNSIGNED NOT NULL DEFAULT '',
'State' varchar(30) DEFAULT NULL,
'Info' varchar(100) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO some_table (Id, User, Host, db, Command, Time, State, Info) VALUES ('4789', 'root', '127.0.0.1:59047', 'test', 'Query', '0', NULL, 'SHOW PROCESSLIST');
2 rows in set (0.000 sec)

Here are the plugins so far:

myterm> plugins list
15 plugins loaded:
Filters:
cols Filters columns and column ranges.
grep Filters lines containing the specified text in any column.
insertify Creates insert statements based on a result set.
other Reduces a result to max N rows, collapsing any extra rows to a row titled Other at the end.
Presenters:
chart Renders a chart using php Libchart.
html Formats the data to a html table.
md5 Calculates an md5 checksum of all rows and column values.
plain Formats the data to the default plain text table grid.
tsv Formats the data to tab-separated-values plain text.
vert Presents the data in a vertical plain-text fashion, similar to mysql \G output format.
vhtml Formats the data to a record by record, vertical html table.
Dests:
browser Sends the output to the browser.
file Sends output to a file with the given filename.
mailto Sends output to the registered email application using a mailto: link
std Sends output to standard out.

Current status

It's currently written in PHP, which is kind of bad, because of PHP:s limited console integration, signal handling (ctrl-c) and threading (think +asynchronous / multi-threaded queries). Maybe a rewrite in Python would fix these issues. Eric Day has initiated a similar project at Portland State University which is about to start... ...and they're thinking of Python! So I've contacted him about possibly combining these projects in some way later on, once both projects have gotten further.

Download

To download it, you need bazaar and php 5.2+ command line with mysqli extension. I've only tested it on Ubuntu Linux. Type:

bzr branch lp:myterm

then follow the README file.

Contribute

For now, myterm parsing and option handling is limited (don't do multi-line queries, comments or too much quoting), but most basic stuff works. There are probably tons of bugs in it and things that don't work, I know :) So... if you'd like to contribute, join the project and mailing list on launchpad. There, you can also look at the blueprints which are some ideas on additional features.

Feel free to leave comments and feature suggestions!

4 Comments

New license model

Bjorn, Jet Profiler team — Mon, 31 Aug 2009 09:15:10 -0400

We've received a lot of feedback from our customers regarding our licensing model. Many people felt annual subscriptions were limiting since they would have to renew the license every year. Another limitation was that the software would only run on one single computer.

We're now pleased to announce that the license model has changed:

No more subscriptions

We've removed the one year limitation of the subscription-based model. The new license allows you to use the software for as long as you like. There will be no more recurring fees, and the software will not stop working after one year of use.

More installations

Another improvement is that we now support two installations per license. This allows for much greater flexibility. You can install it on your laptop and desktop at the same time (or at work and at home). Or you can share the license with a colleague or give it to a friend.

Note to existing subscribers:
All customers with subscription-based licenses will be offered to convert to a normal license with the next renewal. So instead of paying $399 for a completely new license, you can pay $99 the next time you renew your subscription. You will then receive a normal license. An added benefit is that if you have multiple licenses, you can cut the number of licenses in half since each license is valid for two computers. A sales representative will contact you in time with more information.

Comments

Jet Profiler for MySQL 1.0 released

Bjorn, Jet Profiler team — Tue, 10 Feb 2009 08:31:19 -0500

This is the first public release. Features:

Top Queries - See which queries are being run the most on your server.
Top Users - See which users are using your server the most.
Top Tables - See which database tables are opened the most.
Top States - See which states your database is most busy doing, such as creating temp tables.
Replication Profiling - You can measure how much capacity you have left on the replication SQL thread on slaves. If you are using MyISAM a lot, a MyISAM lock analysis will help discover any locks associated with replication, as well as lock-prone queries in general.
Query Ratings and Visualization - You can get your queries rated using EXPLAIN and see which queries are most likely to cause load due to missing indices etc. The execution plan can be visualized in a diagram, which shows the table lookups involved, the join size and any bad indicators.
Zoomable GUI - You can easily zoom in on spikes in your load and see the corresponding queries for that time interval.
Low Overhead - Running the tool against MySQL typically costs around 1%. Recording granularity customizable 100ms - 10s.
Works on Windows, Linux and Mac

Download the free version, try it and let us know what you think is good and what isn't. It doesn't cost anything and isn't time limited. Read more...

Comments

Query profiling using SHOW PROCESSLIST

Bjorn, Jet Profiler team — Thu, 20 Nov 2008 12:22:41 -0500

The MySQL database server can display a lot of performance statistics. However, most of them relate to general server parameters, such as buffer sizes and query cache settings. Of course, such settings are important to get right, and can make a huge difference in performance. Once you've tuned them however, you need to start looking at the tables and queries. When a single bad query can cost 90 - 99% of the total performance, server tuning doesn't fix the problem. But obtaining query profiling information from MySQL can be tricky. Here are some of the options:

Slow query log

You can use the built-in slow_query_log from within the server. It will show you queries that take more than a second. Such queries typically don't use indices properly and are important to address. But only slow queries end up there, and many performance problems aren't caused by slow queries, but by queries that are run very often (for example, for-loops in the code instead of JOINs). You could use complete server query logging. This will log every statement to file, but it's not recommended, as it will take too much disk I/O.

SHOW PROFILES

SHOW PROFILES command by Jeremy Cole (available from MySQL 5.0.37) can help you a great deal. Once enabled, it will gather cpu execution times and other important information on up to 100 queries. You can compare the results and see wich query is most expensive. This article explains more.

SHOW USER / INDEX / TABLE STATISTICS

The SHOW USER / INDEX / TABLE STATISTICS patches from Google add functionality to trace cpu time and much more per user, index and table. No query information is shown though, you will have to figure that out based on the tables. The patches are not part of the main MySQL distribution, but Percona provides prebuilt MySQL versions.

MySQL Enterprise Monitor's Query Analyzer

MySQL Enterprise Monitor's Query Analyzer is great for finding the heaviest queries. Using MySQL Proxy (man-in-the-middle software), it collects all queries sent to the server and ranks them by execution time. You can also see number of times the query was run, and number of rows typically returned. Being the best tool of them all, it also costs, and it requires additional software/hardware setup for the MySQL Proxy.

SHOW PROCESSLIST

Another option is the SHOW PROCESSLIST command. It shows a list of currently running processes (queries), slightly similar to the Unix ps command or Windows Task Manager. For every query, you can see how long the query has run, its user, ip and state. If you run this command just once, you will get a snapshot of what queries the server is busy doing right now. Aggregating multiple snapshots over time can give you a good view of what queries the server typically is busy performing. An advantage of SHOW PROCESSLIST is that it works on all MySQL versions without modifications and requires no additional software / hardware. On the downside, you don't necessarily catch all queries, just the queries that happen to run during every snapshot. If you collect many snapshots over long time, this is less of a problem.

Issues

You might not have the option to go for MySQL Enterprise, or install MySQL Proxy in front of your database servers. Or you might not be able to swith to the MySQL server versions containing Google's patches or SHOW PROFILES. Another problem is, that even if you do get profiling information, it is typically presented in raw text form, and might not be easy to browse through. Good tools to visualize the profiling information and navigate through the data is just as important as getting the information in the first place.

A new profiling tool

We're currently in the process of developing a tool based on the SHOW PROCESSLIST above. The tool, called Jet Profiler for MySQL, collects process list information and stores it in an internal database where it is analyzed, ranked and presented. After normalizing the queries, top lists are created of:

most frequent queries
most frequent users
most frequent tables
most frequent schemas

By looking at the state of the processes, additional information can be extracted, such as lock-prone queries (if you're using MyISAM tables), or queries which create a lot of temp tables on disk. The tool is a client desktop app so no server changes are required. It works on all MySQL versions (3.23 - 6.0).

Better visualization and usability

To provide good visualization, the information is presented in a line chart over time. You can zoom in on spikes and other interesting time intervals, and see the top queries / users / etc for that particular time frame. Every top list is accompanied by a pie chart to make it easier to compare the impact of different items. An experimental EXPLAIN visualization feature is underway, helping you understand the queries better.

Beta testers are welcome

We still have lots of adjustments and features before a public release, but we are looking beta testers. If you are interested, click here.

More information

1 Comments

Best way to visualize EXPLAIN?

Bjorn, Jet Profiler team — Wed, 05 Nov 2008 11:37:34 -0500

Interpreting the output of the MySQL EXPLAIN command can be tricky. From all the information you get, some of the most important information is:

Full table scans
High join size product
Using filesorts
Using temporary tables

This can be hard to see in the raw output. Example query:

EXPLAIN SELECT b.item_id, b.bidder_id, u.username, MAX(price) AS price FROM bid b JOIN user u ON (b.bidder_id = u.user_id) WHERE b.item_id = '1' GROUP BY b.bidder_id ORDER BY price DESC

The explain outputs:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, b, ALL, null, null, 0, null, 22660, Using where; Using temporary; Using filesort
1, SIMPLE, u, eq_ref, PRIMARY, PRIMARY, 4, heavybidder.b.bidder_id, 1,

We've been experimenting how to visualize the output of the EXPLAIN command. Using boxes for tables, calculating the join size product and highlighting important information. What do you think of the result so far?

Screenshot:

Ideas, comments?

2 Comments