Category: MySQL

MySQL related posts

Performance analysis with mycheckpoint

MySQL

mycheckpoint (see announcement) allows for both graph presentation and quick SQL access to monitored & analyzed data. I’d like to show the power of combining them both.

InnoDB performance

Taking a look at one of the most important InnoDB metrics: the read hit ratio (we could get the same graph by looking at the HTML report):

SELECT innodb_read_hit_percent FROM sv_report_chart_sample \G
*************************** 1. row ***************************
innodb_read_hit_percent: http://chart.apis.google.com/chart?cht=lc&chs=400x200&chts=303030,12&chtt=Nov+10,+11:40++-++Nov+11,+08:55+(0+days,+21+hours)&chdl=innodb_read_hit_percent&chdlp=b&chco=ff8c00&chd=s:400664366P6674y7176677677u467773y64ux166666764366646y616666666666644444434444s6u4S331444404433341334433646777666666074736777r1777767764776666F667777617777777777777777yaRi776776mlf667676xgx776766rou67767777u37797777x76676776u6A737464y67467761777666643u66446&chxt=x,y&chxr=1,99.60,100.00&chxl=0:||Nov+10,+15:55|Nov+10,+20:10|Nov+11,+00:25|Nov+11,+04:40|&chxs=0,505050,10

We see that read hit is usually high, but occasionally drops low, down to 99.7, or even 99.6. But it seems like most of the time we are above 99.95% read hit ratio. It’s hard to tell about 99.98%.

Can we know for sure?

We can stress our eyes, yet be certain of little. It’s best if we just query for the metrics! mycheckpoint provides with all data, accessible by simple SQL queries: Continue reading » “Performance analysis with mycheckpoint”

Replication analysis with mycheckpoint

MySQL

I would like to show how mycehckpoint (see announcement) can be put to use for analyzing various replication metrics.

Lagging slaves

A slave has been monitored. Monitoring started at a time when it was way behind master (about two days lag), but it has since caught up. This can be easily verified by the following chart:

The above chart can be obtained by viewing the HTML report:

SELECT html FROM sv_report_html

Or by directly issuing the query:

mysql> SELECT seconds_behind_master FROM sv_report_chart_hour\G
*************************** 1. row ***************************
seconds_behind_master: http://chart.apis.google.com/chart?cht=lc&chs=400x200&chts=303030,12&chtt=Nov+5,+10:00++-++Nov+10,+08:00+(4+days,+22+hours)&chdl=seconds_behind_master&chdlp=b&chco=ff8c00&chd=s:976431zzzywutrpnliiifdbZYXVTRRRPNLJHEBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA&chxt=x,y&chxr=1,0,169811&chxl=0:||Nov+6,+09:00|Nov+7,+09:00|Nov+8,+08:00|Nov+9,+08:00|&chxs=0,505050,10

This is all nice. But I’m also interested in the rate at which slave lag decreased. Many ignore this important metric: just how fast does your slave replicate?

Continue reading » “Replication analysis with mycheckpoint”

Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL

MySQL

I’m proud to announce mycheckpoint, a monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

mycheckpoint is a different kind of monitoring tool. It leaves the power in the user’s hand. It’s power is not with script-based calculations of recorded data. It’s with the creation of a view hierarchy, which allows the user to access computed metrics directly.

mycheckpoint is needed first, to deploy a monitoring schema. It may be needed next, so as to INSERT recorded data (GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS, SLAVE STATUS) — but this is just a simple INSERT; anyone can do that, even another monitoring tool.

It is then that you do not need it anymore: everything is laid at your fingertips. Consider:

SELECT innodb_read_hit_percent, DML FROM sv_report_chart_hour;

mycheckpoint provides the views which take raw data (just innodb_buffer_pool_read_requests, com_select, innodb_buffer_pool_size, table_open_cache, seconds_behind_master etc.) and generate Google Charts URLs, HTML reports, human readable reports, or otherwise easily accessible data.

Continue reading » “Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL”

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”

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!