PlanetMysql.ru – информация о СУБД MySQL

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 an example of a static chart; in this example, generated by Google:

Pros and cons of static charts

Pros

• Images can be viewed on any graphical platform. Browsers, email clients, cell phones, whatever.
• Self contained: chart image, legend, scales: all in one image.
• As such, easy to move around.
• Are safe to use.

Cons

• Images are fuzzy. Is the com_replace_psec really 0? Maybe it’s 0.1? A larger value can make lower values hard to tell.
• Images are inaccurate: the colors can lie. The red and green lines showing are hard to tell apart. The red is painted above the green. Data gets “lost”.
• They do not zoom (one needs to regenerate larger image)
• Unless encoded with base64, HTML pages which include images need to link outside.
• In the particular case of Google Charts, one is limited to 2K length URL. Trust me, it’s a big limitation! (PS, Google now support POST method to allow for up to 16K. But… it’s a POST method…)
• In the particular case of Google Charts, one must have an internet connection.
• In the particular case of Google Charts, one must submit data to Google.

Interactive charts

Interactive charts are those which react to your commands. These are either JavaScript or Flash based, mostly. They allow for really nice features. Take the following chart as an example: try and move over with your mouse; or select sections to zoom in.

DML
[graphDiv]
[labelsDiv]

The above chart is generated with dygraphs. Since it is embedded within my Wordpress page, the layout is affected by that of my theme. Take a look at this example page to see similar charts outside this blog site (Internet Explorer users: Maxmimize/minimize button will not work well for now. And, may I suggest Mozilla Firefox?)

Pros and cons of interactive charts

Pros

• Can present you with exact values. No more doubt about the com_replace_psec values.
• Can allow for zoom in, zoom out.

Cons

• Need supporting platform. The above cannot be viewed by non-JavaScript browsers (cell phones, etc.)
• Browser support is also an issue with JavaScript.
• Emailing such report will result in mail blocking in many companies: mail filters will not allow for JavaScript code to pass.
• Charts are not necessarily self-contained, in terms of the chart entity With Flash charts (e.g. Fusion Charts) this works. But in the above, the legend and scales are outside the image. As such, they cannot be just moved around.
• HTML pages which include such charts can be self contained. The HTML page can include all the JavaScript dependencies, in addition to the chart generating code. Flash based charts cannot be self contained.

Summary

Interactive charts are cool!

I’m now integrating dygraphs into mycheckpoint (How nice it is to work with BSD & MIT licenses!). Though I may later switch to flot, interactive charts will be the next standard charting way in mycheckpoint. I will continue supporting static Google Charts, as follows from the above pros and cons list.

PlanetMySQL Voting: Vote UP / Vote DOWN

I had a call from a new customer last week. They had issues with their MySQL server. Apparently, it was repeatingly crashing, every few hours. To have their production system kept alive, they used a script to periodically see if MySQL was still alive, and if not – start it.

I was first thinking in directions of old installations; wrong memory allocations (too little memory for large content; to large memory allocation for weak machine). When I reached the customer’s premises, I quickly reviewed general settings, while explaining some basic configuration guidelines. There were peculiarities (e.g. query_cache_limit being larger than query_cache_size), but nothing to obviously suggest a reason for crash.

I then observed the error log. Took some time to find it, since the log_error parameter appeared twice in the my.cnf file; first one appearing at the very beginning, the second (overwriting the first) was far into the file.

Sure enough, there were a lot of startup messages. And… shutdown messages. In fact, the log looked something like:

090923 17:38:15  mysqld started
090923 17:38:16  InnoDB: Started; log sequence number 0 3707779031
090923 17:38:16 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/tmp/mysql50.sock'  port: 3306  MySQL Community Server (GPL)
090923 19:53:41 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
090923 19:53:56  mysqld started
090923 19:53:56  InnoDB: Started; log sequence number 0 5288400927
090923 19:53:56 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/tmp/mysql50.sock'  port: 3306  MySQL Community Server (GPL)
090929 22:38:48 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
090923 22:38:58  mysqld started
090923 22:38:58  InnoDB: Started; log sequence number 0 7102832776
090923 22:38:58 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/tmp/mysql50.sock'  port: 3306  MySQL Community Server (GPL)
...

(The above is just a sample, not the original file).

Well, the log says there’s a lot of normal shutdowns.

Looking at the script

Next it was time to look at the script which was supposed to verify MySQL was up and running – else wise start it. And it went something like this:

while [ 1 ];
  if [`ps aux | grep mysqld | wc -l` -lt 2]
    then /etc/init.d/mysql restart
  ...
  sleep 60

The script was looking for all processes, then grepping for mysqld, counting number of lines in output. It expected 2 lines: one for the mysqld process, one for the grep mysqld process itself.

If you don’t know what’s wrong with this, a very brief explanation about how pipelines work in unix work it at place:

Pipelined processes do not execute one after another, or one before another. They are all executed at once. So, “ps aux | grep mysqld | wc -l” immediately spawns ps, grep, wc, then sets the standard output of one to the standard input of the other (most simplified description I could think of).

It is likely that grep will outrun ps in the race for starting up. So grep is started, already waiting for input from ps, which then notices both mysqld is alive, but also grep mysqld, lists them both along with other processes, to be filtered by grep, to be counted by wc (returning two lines count).

But this is just because ps is heavier than grep. It doesn’t have to be like that.

The less common scenario

Every once and again, ps outruns grep in the race for starting up. It would list the active processes, and no “grep” would appear in the listing. grep would then run, filtering the result of ps, then to be counted by wc — oh, there is only one line now! The script assumes this means mysqld is down (since it assumed grep is always there), and restarts mysqld.

So, the script which was supposed to take care of MySQL crashes, was actually causing them (though no crash occurred).

Better ways to test that MySQL is alive

Look for the pid file. This is the standard (that’s how the mysql service works). Look for the unix socket file. Both require that you parse the my.cnf file to learn where these files are.

If you’re reluctant to read the configuration files, other options are at hand. OK, look for the process; but use pgrep mysqld. No need to list all processes, then grep them.

And best way, that will set your mind at ease even if you’re worried that “mysql is running but not responding; it is stuck”: connect to MySQL and issue SELECT 1, SELECT NOW(), SELECT something. This would be the ultimate test that MySQL is up, listening, responding and valid.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.

It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel, apt-get install inotify-tools is all you need to get started.

 # inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/*
Establishing watches...
Setting up watch(es) on /var/lib/mysql/mysql/user.frm
OK, /var/lib/mysql/mysql/user.frm is now being watched.
[...]
Total of 212 watches.
Finished establishing watches, now collecting statistics.
Will listen for events for 60 seconds.
total  modify  filename
2371   2371    /var/lib/mysql/relay-log.info
2148   2148    /var/lib/mysql/master.info
1157   1157    /var/lib/mysql/ib_logfile0
24     24      /var/lib/mysql/zabbix/
24     24      /var/lib/mysql/zabbix/history.ibd
8      8       /var/lib/mysql/zabbix/trends_uint.ibd
6      6       /var/lib/mysql/zabbix/items.ibd
5      5       /var/lib/mysql/ibdata1

This is just a limited example from a dev box, but you can see the benefit. You can see which files have been accessed, in what way, and how many times over the specified period. Consequently this provides the most insight if you’re using innodb-file-per-table (or MyISAM) rather than a single InnoDB tablespace. But of course it depends a bit on what you’re looking for.