Year: 2009

SQL multi line chart

MySQL

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    | -------**#------------##-------------------------------**-------------*##-------- |
| 0.63    | ------##-*--------------##----------------------------*--------------##**-------- |
| 0.5     | -@@@@@@@@@@@@@@@----------#-----@@@@@@@@@@@@@@@@----**---------@@@@@@@@@@@@@@@@-- |
| 0.38    | ----#-------*--------------##----------------------*---------------#-------*----- |
| 0.25    | --##---------*---------------#--------------------*--------------##---------*---- |
| 0.13    | -#------------**--------------#-----------------**--------------#------------*--- |
| 0       | @---------------*--------------##--------------*---------------#--------------**- |
| -0.12   | -----------------*---------------#------------*--------------##-----------------* |
| -0.25   | ------------------*---------------#---------**--------------#-------------------- |
| -0.37   | -------------------**--------------#-------*---------------#--------------------- |
| -0.5    | ----------------@@@@@@@@@@@@@@@@----##----*-----@@@@@@@@@@@@@@@----------------@@ |
| -0.62   | ----------------------**--------------#-**--------------#------------------------ |
| -0.75   | ------------------------**------------**#-------------##------------------------- |
| -0.87   | --------------------------**-------***---###-------###--------------------------- |
| -1      | ----------------------------*******---------#######------------------------------ |
|         | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                               8 |
|         |     # sin(x)                                                                      |
|         |     * cos(x)                                                                      |
|         |     @ sign(tan(x))/2                                                              |
+---------+-----------------------------------------------------------------------------------+

Continue reading » “SQL multi line chart”

On free and open blog posts: this post is free

MySQL

I’d like to criticize and stress some opinions on free and open blog posts, including such appearing on planet MySQL.

Some rants follow; skip if you’re only after technical stuff

1. On free reading

This post is completely free. You don’t need to pay or register to read it, not will you require to pass personal details to comment.

If you happen to see this post on some website, which only provides you with 1st couple of sentences, then asks you to login in order to view the complete text — you should know you need no registration to read this.

Continue reading » “On free and open blog posts: this post is free”

How to calculate a good InnoDB log file size – recap

MySQL

Following Baron Schwartz’ post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB transaction log bytes that are expected to be written in the period of 1 hour.

Recap: this information can be useful if you’re looking for a good innodb_log_file_size value, such that will not pose too much I/O (smaller values will make for more frequent flushes), not will make for a too long recovery time (larger values mean more transactions to recover upon crash).

It is assumed that the 60 seconds period represents an average system load, not some activity spike period. Edit the sleep time and factors as you will to sample longer or shorter periods. Continue reading » “How to calculate a good InnoDB log file size – recap”

SQL: querying for status difference over time

MySQL

The InnoDB plugin has a nice INFORMATION_SCHEMA concept: resetting tables. For example, the INNODB_CMP table lists information about compression operation. A similar table, INNODB_CMP_RESET, provides the same information, but resets the values. The latter can be used to measure, for example, number of compression operations over time.

I wish to present a SQL trick which does the same, without need for resetting tables. Suppose you have some status table, and you wish to measure the change in status per second, per minute etc. The trick is to query for the value twice in the same query, with some pause in between, and make the difference calculation.

For sake of simplicity, I’ll demonstrate using 5.1’s INFORMATION_SCHEMA.GLOBAL_STATUS. Please refer to INFORMATION_SCHEMA.GLOBAL_STATUS: watch out for some discussion on this.

Continue reading » “SQL: querying for status difference over time”

How to export OpenOffice presentations to PDF

OpenOffice

OpenOffice 3.x has an improved PDF export mechanism. With File->Export as PDF…, one can export a presentation file (*.odp) to PDF format.

[Edit: see updated notes at the end of this post]

But there is a “feature” which is very annoying. One can export the presentation:

  • Without notes, in which case the presentation frames are exported one per page. This is the standard export, and what you would normally use to present in front of an audience.
  • With notes, which would normally be used for printing. The problem is, this kind of export first produces the pages without notes, exactly as in previous case, then followed by the slides (again!) with notes. It was argued that this is a feature, not a bug; I fail to see the reasoning behind this.

If I want to create a PDF for printing, why would I want the bare slides? Anyway, for those who wonder like me, here’s how to overcome this using OpenOffice:

  1. Install the PDF import extension. It will be used in following steps, but it’s a nice extension to have anyway.
  2. Export your presentation using File->Export as PDF…, make sure Export notes is checked. Say you export to “my_notes.pdf”.
  3. Open “my_notes.pdf” with OpenOffice (opens with Draw). The PDF extension allows for some PDF editing capabilities.
  4. Delete pages starting page #1 and until the last slide-PDF. 1st page should now be page #1 with slide+notes.
  5. Yet again, export with File->Export as PDF…, make sure Export notes is checked. Save as “my_notes_final.pdf”.

And there you have it. The “my_notes_final.pdf” file contains a printable version of slides+notes, without initial slides-only pages.

Of course, if you only want to produce slides in PDF, all you have to do is File->Export as PDF…, then make sure Export notes is unchecked.

Notes

On ubuntu, the best way to install the PDF import extension it to:

sudo apt-get install openoffice.org-pdfimport

Edit:

More than two years have passed. The “feature” is still there, but a couple more solutions are available:

  1. Just use cups-PDF on your Linux machine. However, at the time of writing this, the pdftops module on Ubuntu is CPU hogging, and doesn’t do the job. Argggh! I hate regressions.
  2. On the good side, you can just use pdfmod, a nice GUI application allowing you to remove or reorder PDF pages. So, just export to PDF with notes, then use pdfmod to remove slides, or to remove notes.

INFORMATION_SCHEMA.GLOBAL_STATUS: watch out

MySQL

MySQL 5.1 boasts some new and useful INFORMATION_SCHEMA tables. Among them is the GLOBAL_STATUS table.

At last, it is possible to ask questions like:

node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_os_log_written';
+-----------------------+----------------+
| VARIABLE_NAME         | VARIABLE_VALUE |
+-----------------------+----------------+
| INNODB_OS_LOG_WRITTEN | 512            |
+-----------------------+----------------+
1 row in set (0.00 sec)

node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'questions';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| QUESTIONS     | 28             |
+---------------+----------------+
1 row in set (0.00 sec)

Watch out #1

As with all INFORMATION_SCHEMA tables, to get a single row one needs to materialize the entire table. To ask the above two questions, the table will materialize twice. This means gathering all the information — twice. To get 20 values, we materialize the table 20 times. It not only takes time, but also increases some of the status variables themselves, like questions, select_scan, created_tmp_tables. Ironically, when we used SHOW GLOBAL STATUS and had to parse the results in our application code, we only issued the query once. But with the convenience of INFORMATION_SCHEMA, it’s much easier (and makes more sense!) to query per variable.

Continue reading » “INFORMATION_SCHEMA.GLOBAL_STATUS: watch out”

How NOT to test that mysqld is alive

MySQL

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: Continue reading » “How NOT to test that mysqld is alive”

High Performance MySQL – a book to re-read

MySQL

I first read High Performance MySQL, 2nd edition about a year ago, when it first came out. I since re-read a few pages on occasion.

In my previous posts I’ve suggested ways to improve upon the common ranking solution. Very innovative stuff! Or… so I thought.

I happened to browse through the book today, and a section on User Variables caught my eye. “Let’s see if I get get some insight“, I thought to myself. Imagine my surprise when I realized almost everything I’ve suggested is discussed in this modest section, black on white, sitting on my bookshelf for over a year!

I have read it a year back, have forgotten all about it, have re-invented stuff already solved and discussed… Oh, for more brain capacity…

To be honest, this has happened to me more than once in the past few months; I’m taking the habit of browsing the web when I’m looking for answers to my problems; I forget that this book contains the answers to so many common, practical MySQL problems, and does so in a very direct and helpful manner.

So, yet again, thumbs up to High Performance MySQL. Really a must book. Get it if you haven’t already!

SQL: ranking without self join, revisited

MySQL

This post follows SQL: Ranking without self join and On user variables evaluation order. I wish to share some insights with regard to user variables evaluation, as well as provide yet another ranking solution, which attempts to overcome the uncertainty factor with user variables.

There will be hand waving in this post (albeit empirical hand waving). Stop here if you don’t like hand waving. Continue if you feel curious or wish to contradict my assumptions.

Recap

The order of evaluation of user variables is undefined. The documentation has some contradicting example (bug 47514), but states that variables should not be read and assigned in different parts of the same statement (just what is a different part? Bug 47516).

Looking for a solution

There doesn’t seem to be a problem with reading and assigning variables in the very same part of the statement. For example, SELECT @a := @a+1… is exactly such a case.

Continue reading » “SQL: ranking without self join, revisited”

On user variables evaluation order

MySQL

There is something very unclear about what is defined and is undefined with regard to the order by which user variables are evaluated, even within the MySQL documentation itself.

I wish to present some examples and draw a conclusion. Since I will following state there’s missing information, I would greatly appreciate any educated comments.

The trivial “reordering problem” case

Let’s look at the following query: Continue reading » “On user variables evaluation order”