Year: 2010

Static charts vs. interactive charts

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: Continue reading » “Static charts vs. interactive charts”

What I look forward to hear on “State of the Dolphin”, 2010

MySQL

Though most probably I won’t be there in person, here’s what I expect to hear from Edward Screven, Oracle, on the State of the Dolphin keynote, coming MySQL Conference & Expo.

I’m under the assumption that no shocking news are delivered. That is, that for the near future, it’s business as usual for MySQL.

Last year’s great message, delivered by Karen Padir, was “more community”. More community participation, more community patches. Looking back, I’m not sure I saw that coming true. The 5.4 version was announced at that same conference, and was criticized for being community-oriented yet community-hidden. The latest 5.5 milestones announcement took everyone by surprise again. Ideas from Google patches were incorporated into 5.5M2. but, to the best of my understanding, no community patch was delievered.

I have both congratulated and expressed my desire that community took greater part in this.

So what am I looking forward to hear?

  1. Like everyone else, the general plans Oracle holds for MySQL. Again, I’m not expecting shocking news here.
  2. The expected roadmap for MySQL, technically speaking. I don’t actually know if there is a roadmap right now.
  3. The intended role for the MySQL community. Frankly, it would be just fine with me if Oracle were to say: “we will not accept community patches”, and that would be the end of it. That’s fine, because it’s their right, and it would be an honest announcement. Naturally, I’ll be much happier to hear “we will incorporate the best 20 community patches withing the next three days”. Somewhere in between, I’ll be really satisfied with a clear explanation of how Oracle sees the community, and how it would like to cooperate with it. Will it share the development plan with the community? Will it allow the community to have a say about what goes in or not?

Continue reading » “What I look forward to hear on “State of the Dolphin”, 2010″

Defined your MySQL backup & recovery plan recently?

MySQL

Following up on Ronald Bradford‘s Checked your MySQL recovery process recently? post, I wish to add a prequel.

To see whether you have a clear definition of your backup requirements, ask yourself these questions:

  • Is there a backup/restore plan?
  • Is there a written backup/restore plan?
  • How fast do you need to recover a backup? What’s the longest downtime you will allow from the point of failure to the point of restored, functional database?
  • How much data are you willing to lose in case of crash? A second’s worth of data? An hour’s worth? A day’s worth? None?
  • Are you willing to allow that the database becomes read-only when taking the backup? Or completely down?
  • Are you willing to take the risk that the backup will not be 100% compatible with the data? (Backing up your slave holds this risk)
  • Is your manager willing to all that you are willing?
  • Is the backup plan known to the management team, or do they just know that “the database has backups“?

The above checklist is by no means complete.

I have a vivid memory of a very good sys admin who failed on the last two points. He had some very sour days when recovering a lost file from tape took much longer than was affordable to some contract.

I found that technical people rarely share the same views as marketing/management. Make sure to consult with the management team; they will have a clearer view on what the company can afford and what it cannot afford.

To not yum or to not apt-get?

MySQL

I’ve written shortly on this before. I like yum; I love apt-get; I prefer not to use them for MySQL installations. I consider a binary tarball to be the best MySQL installation format (source installations being a different case altogether).

Why?

I use yum and apt-get whenever I can and for almost all needs (sometimes preferring CPAN for Perl installations). But on a MySQL machine, I avoid doing so. The reason is either dependency hell or dependency mismatch.

Package managers are supposed to solve the dependency hell issue. But package managers will rarely have an up to date MySQL version.

I’ve had several experiences where a simple yum installation re-installed the MySQL version. I’ve had customers calling me up when, having installed something with yum, MySQL would not work anymore. Continue reading » “To not yum or to not apt-get?”

mycheckpoint (rev. 88): mount points monitoring, improved charting, enhanced auto-deploy

MySQL

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:

  1. The “/” (root) mount point
  2. The datadir mount point
  3. 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 “/tmp“) is on the same mount point as “/“. mycheckpoint does not care.

mycheckpoint monitors and reports the mount point’s used percent, in a similar algorithm df uses.

Disk space monitoring is only possible when monitoring the local machine (i.e. mycheckpoint runs on the same machine as the monitored MySQL server). In the future mycheckpoint may also monitor additional mount points, such as the various logs mount points.

Continue reading » “mycheckpoint (rev. 88): mount points monitoring, improved charting, enhanced auto-deploy”

Monotonic functions, SQL and MySQL

MySQL

In mathematics, a monotonic function (or monotone function) is a function which preserves the given order. [Wikipedia]

To be more precise, a function f is monotonic increasing, if for every x ≤ y it holds that f(x) ≤ f(y). f is said to be strictly monotonic increasing is for every x < y it holds that f(x) < f(y).

So, if we follow values in some order, we say that f is monotonic increasing if f‘s value never decreases (it either increases or stays the same), and we say that f is strictly increasing if f‘s value is always changes “upwards”.

Monotonic functions play an important role in SQL. To discuss monotonic functions in SQL we must first determine what the order is, and then, what the function is.

Well, they both change according to our point of view. Let’s look at some examples. Take a look at the following table: Continue reading » “Monotonic functions, SQL and MySQL”

Beware of implicit casting

MySQL

Ever so often a query provides a “bad” execution plan. Adding a missing index can many times solve the problem. However, not everything can be solved with an index. I wish to highlight the point of having an implicit cast, which negates the use of an index on MySQL.

I see this happening a lot on customers’ databases, and this begs for a short introduction.

MySQL doesn’t support index functions

Let’s assume the following table: Continue reading » “Beware of implicit casting”

Checking for string permutation

MySQL

A permutation is a change of places. Thus, ‘lolhe’ is a permuted ‘hello’ (commonly referred to as ‘scrambled text’).

I wish to present an SQL solution for checking if two strings are permutations of the same text.

About permutations

So, if ‘lolhe’ is a permutation of ‘hello’, then ‘hello’ is a permutation of ‘lolhe’, as well; and both are permutations of ‘elloh’. The REVERSE() of a text is an example of permutation. Mathematically, string permutation is an equivalence relation, and divides all strings to equivalence classes.

Use cases

  • We may be interested in permutations when a user chooses a password. We may disallow a password which is identical to the login name; but we may also disallow upper-lower-case-only transformations of the text. We may still disallow a permutation of the text.
  • On a slightly different scale, the two queries: SELECT * FROM City WHERE id IN (5, 21, 13) and SELECT * FROM City WHERE id IN (13, 5, 21) are identical. Here, the permutation is not with string characters, but with string tokens. While the solution discussed is targeted at string characters, it can be easily converted to work with string tokens.

Checking for permutation

The solution I’m suggesting checks for permutation between 2 strings by permuting both to a third, normal form. The two string are permutations of each other if both have the same normal form.

Continue reading » “Checking for string permutation”

Misimproving performance problems with INSERT DELAYED

MySQL

INSERT DELAYED may come in handy when using MyISAM tables. It may in particular be useful for log tables, where one is required to issue frequent INSERTs on one hand, but does not usually want or need to wait for DB response on the other hand.

It may even offer some performance boost, by aggregating such frequent INSERTs in a single thread.

But it is NOT a performance solution.

That is, in a case I’ve seen, database performance was poor. INSERTs were taking a very long time. Lot’s of locks were involved. The solution offered was to change all slow INSERTs to INSERT DELAYED. Voila! All INSERT queries now completed in no time.

But the database performance remained poor. Just as poor as before, with the additional headache: nobody knew what caused the low performance.

Using INSERT DELAYED to improve overall INSERT performance is like sweeping the dust under the carpet. It’s still there, only you can’t actually see it. When your queries are slow to return, you know which queries or which parts of your application are the immediate suspects. When everything happens in the background you lose that feeling.

The slow query log, fortunately, still provides with the necessary information, and all the other metrics are just as before. Good. But it now takes a deeper level of analysis to find a problem that was previously in plain sight.

So: use INSERT DELAYED carefully, don’t just throw it at your slow queries like a magic potion.

mycheckpoint rev. 76: OS monitoring, auto deploy, brief HTML and 24/7 reports

MySQL

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;

mycheckpoint-chart-cpu-sample
mysql> SELECT ts, os_loadavg FROM mycheckpoint.sv_report_sample;
+---------------------+------------+
| 2009-12-27 11:45:01 |       1.78 |
| 2009-12-27 11:50:01 |       2.48 |
| 2009-12-27 11:55:01 |       2.35 |
...
+---------------------+------------+
mysql> SELECT report FROM mycheckpoint.sv_report_human_sample ORDER BY id DESC LIMIT 1 \G
*************************** 1. row ***************************
report:
Report period: 2009-12-27 13:20:01 to 2009-12-27 13:25:01. Period is 5 minutes (0.08 hours)
Uptime: 100.0% (Up: 334 days, 06:37:28 hours)

OS:
 Load average: 1.67
 CPU utilization: 25.2%
 Memory: 7486.4MB used out of 7985.6484MB (Active: 6685.8906MB)
 Swap: 3835.2MB used out of 8189.3750MB
...

Auto-deploy

mycheckpoint now has a version recognition mechanism. There is no need to call mycheckpoint with the “deploy” argument on first install or after upgrade. mycheckpoint will recognize a change of version and will auto-deploy before moving on to monitoring your system.

Continue reading » “mycheckpoint rev. 76: OS monitoring, auto deploy, brief HTML and 24/7 reports”