Personal blog of Yzmir Ramirez

Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but not on Linux.

However I follow the project SystemTap for couple of years (it was started in 2005), which is supposed to provide similar to DTrace functionality.

Why I am interested in this tool, because there is no simple way under Linux to profile not CPU-bound load (for CPU-bound there is OProfile, see for example
http://mysqlinsights.blogspot.com/2009/08/oprofile-for-io-bound-apps.html). I.e. for IO-bound or for mutex contention problems OProfile is not that useful.

SystemTap is included in RedHat 5 releases, but I was not able to get it running even in CentOS 5.3 (it crashed and hung every so often). Latest updated RedHat 5.4 promised some more fixes to SystemTap, so I decided to give it more try as soon as I got RedHat 5.4 on hands.

Surprising, but now it runs much more stable. I was able to get profiling of kernel and system calls.
Here is simple script to show IO activity per disk per process (well, it is similar to iotop, but iotop is not available in RedHat / CentOS)

with output like this

This example maybe is simple, but the point is that there is rich scripting language with tons
of probes you can intersect ( kernel functions, FS drivers functions, any other drives and modules)

What else I see very useful in SystemTap it can work in userspace. That is you can use it to profile your and any application that has -debuginfo packages ( all -debuginfo for standard RedHat RPMS you can download from RedHat FTP), but basically it is info you get compiling with gcc -g.

Well, there seems another war story going on. To profile userspace application with SystemTap your kernel should be patches with uprobes patch, which fortunately is included in RedHat based kernels, but not included in vanilla kernel yet. So I am not sure if you can get userspace profiling running in another distributives.

There is quite simple script that I tried to hack around MySQL ®

with output which I get running simple SELECT against InnoDB table:

Again, this case is maybe too simple, but basically you can intersect internal MySQL function and script (measure time, count of call, statistics) what you what. I did not figure out yet how to intersect C++ style function (i.e. ha_innobase::index_read), so there is area to investigate.

So I am going to play with it more and do some useful scripting to get profiling of MySQL.

And it seems SystemTap can re-use DTrace probes available in application, as you may know DTrace-probes were added into MySQL 5.4, so interesting how it works.

I should mention that there is second alternative of DTrace… It’s …. DTrace port. Looking on blog it seems one-man project and currently author is fighting with resolving userspace issues. I gave to this a try, but on my current RedHat 5.4 after several runs I got “Kernel panic”, so it’s enough for now.

Entry posted by Vadim |
2 comments

Add to: | | | |

If you ever wondered how big is that or another index in InnoDB … you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR – since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

We chose to put this into xtrabackup for a couple of reasons – the first is that running statistics on your backup database does not need to hurt production servers, and the second reason is that running statistic on a stopped database is more accurate than with online (although online is also supported, but you may have inexact results).

Let’s see how it works. I have one table with size 13Gb what was filled during about 2.5 years.
The table is:

And size of file is about 12.88 GB

-rw-r--r-- 1 root root 13832814592 Sep 10 14:41 link_out104.ibd


So to get statistics we run:

xtrabackup --stats --tables=art.link* --datadir=/mnt/data/mysql/

which will show something like this:

The output is intensive, let me highlight some points:

It says that PRIMARY key (which is the table by itself, as InnoDB is clustering data by primary key) takes 497839 pages ( 16KB each) and size of data 7492026403 bytes or (6.98 GB). And density ( fitting data into pages) is quite good – 91%. But it was expected, as table is really mostly inserted in, updates and deletes are rare).

And let’s take index domain_id

you can see the allocated pages (43255 pages or 708689920 bytes) are filled only by 76% ( data takes 545031333 bytes). And that means that 150MB are just waste of space. Which is really even worse for key revert_domain

leaf pages: recs=25839414, pages=133869, data=1566961607 bytes, data/pages=71%
.

For this key about 600MB is empty.

This needs a bit of explaining:
This does not have as good efficiency as the primary key, but a lot of this is to be expected. In a lot of cases we insert into the primary key in order which makes things very predictable, but the inserts into the secondary key index are random – which leads to a lot of page splits.

One helpful new feature to address this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should be quite good.

To check that, there is xtrabackup –stats for index domain_id created for table in Barracuda format with Fast creation method:

As you see this time it takes 34383 pages (compare to 43255 in previous statistics).

Though it would be interesting to see how it will grow with further inserts, and I also suspect random INSERTS into so dense space going to be slower than in previous case.

The –stats is not in xtrabackup release yet, only in source code repository, but should be released quite soon.

And the last point of the post – if you are badly missing some features in MySQL, InnoDB, InnoDB-plugin, XtraDB, XtraBackup – you know whom ask for!

Entry posted by Vadim |
5 comments

Add to: | | | |

So while I was at it made this PHP urlEncode urlDecode Tool because I keep forgetting to bookmark other sites’ urlencode/urldecoders.  Also because the Mac version of Hackbar in Firefox doesn’t have the same options as the Window’s version.

I needed to send an email with human readable string of the difference between two timestamps, so I made this little tool:

PHP TimeDiff Tool

I updated my tools page.  The php timestamp converter tool has been updated to use timezone_identifiers_list() (i.e. ‘America/New_York’), now instead of hard-coded offsets.

Also moved all the scripts into one directory here.

When I get bored again I’ll add some more.  I actually use these from work so it’s really just a convenience for me.

So in a recent symfony-zone article about “the best Symfony IDE: PHPEdit“, the author recommended using PHPEdit.   Problem is PHPEdit doesn’t have a Mac OS X version according to their requirements page as of this date.

So for now using Zend Studio or BBEdit is probably your best bet.  Haven’t tried Netbeans, but at least one developer I know still uses it.  Of course you could just use vi if all else fails.

One last thing, Zend Studio probably won’t ever have any first party support for symfony being that Zend Studio’s parent company has their own framework; so I would’t hold my breath for that.

Don’t download from these other sites tools that claim to remove probably the most prevalent virus in computer history.  Go to this site on Microsoft’s website:

http://www.microsoft.com/protect/computer/viruses/worms/conficker.mspx

Any web developer today is probably running Firefox because of some of the great add-ons they have (i.e. WebDeveloper, Colorzilla, Firebug, Hackbar, etc…). But you may also want to install some addons for security:

Security and privacy are some of the major concerns these days while choosing a web browser to use. So much so that all the major players in the “browser wars” are providing or developing a private browsing mode.

Firefox with the myraid of add-ons that it has to offer is never far from action. Here are some of the top Firefox addons that you should install for better privacy and security

Check out this article for the list of 10+ Best Firefox Addons for Security and Privacy.

I would argue the Firefox (currently #2), is probably the easiest of the browsers to develop for because of the great add-ons they have available. If you’re not already using any of the below, download them now:

1. Web Developer: Next to Firebug, this is a very useful tool for manipulating and inspecting web pages.  It gives you tools for Javascript, Cookies, CSS, Forms, Images, Validations, and other useful utilities (I use the on-screen Ruler alot as well to get dimensions).
2. Firebug: If you find yourself having to debug Javascript or integrate some of the popular frameworks, you’ll need this tool.  It has some overlap with Web Developer, but when you really need to inspect the DOM and debug Javascript, I can’t think of a better add-on.
3. Colorzilla: Ever wish you had an eyedropper inside Firefox?  Well here’s your tool.  A small add-on that allows you get the colors from a page quickly without having to open up your graphics program or search the style sheet for the color.
4. HackBar: When manipulating a URI is too cumbersome, you may want to invest in this little add-on.  Take a URI you’re on and split each parameter into a separate line in a small text editor in your toolbar.  Then you’ll have quick access to encode, encrypt, increment or decrement parameters easily for POST or GET calls.

So the adage, “Sometimes newer is not always better,” is true when it comes to software. I find myself getting used to older software and don’t necessarily need all the newer features.

Sometimes I buy a version or two older than the newest to save some money, but that’s another article.

Now obviously Windows has a built-in extraction tool, but I will harken back to WinZip days…that’s why I go here to download the older version of Winzip – Version 7 is my favorite.

You can goto Oldversion.com and download a whole-host of older applications.