Entries by Kristian Köhntopp

Dear Diary, today I ventured into one of the darkest realms of the sysadmin profession: I started playing with SNMP. My goal was very simple and quite clearly stated: Make the output of "SHOW GLOBAL STATUS" available to a SNMP client. One would think that this is a reasonable and easily fulfilled wish.

Little did I know of the madness and despair that linger in the depths which are guarded by the dread named ASN.1 and where the noxious fumes of the organisation no longer called CCITT can still strongly be smelled.

But let us begin this story at the beginning - with a clean install of Suse Linux 10.0 and my trusty apt4rpm and me. It was my thought that the perl support of net-snmp might me handy to get me where I wanted to me: perl is easily the more convenient language for prototyping that C or C++ and getting to the data source using perl-DBI would be pretty easy. So "apt install net-snmp net-snmp-devel perl-SNMP" it was. So with my spirits high and a song on my iPod I ventured out into the documentation...

Even when it is being repeated once more it is not true:

Stripping binaries using the ‘strip’ utility can also significantly reduce the memory footprint of the application

claims John Coggeshall.

While it is true that a file is smaller on disk after a strip, a quick run of "size" on a binary will show you that the actual binary part of the file is unchanged. Let's have a quick look at /proc/pid/maps to understand what happens.

Oprofile is a profiling tool that requires no instrumentation and can profile an entire system. Binaries with symbol tables (-g compiled binaries) and an uncompressed Kernel with symbol table are helpful.

Oprofile is capable of monitoring and analyzing a running -g compiled mysqld and can tell you which functions are using the most CPU time.

Sample output:

Ex-web.de Colleague Markus asked me: I have been writing a german language article on character sets in MySQL and this is the translation of it.

In Semi-Dynamic Data, Sheeri writes about Semi-Dynamic Data and content pregeneration. In her article, she suggests that for rarely changing data it is often adviseable to precompute the result pages and store them as static content. Sheeri is right: Nothing beats static content, not for speed and neither for reliability. But pregenerated pages can be a waste of system ressources when the number of possible pages is very large, or if most of the pregenerated pages are never hit.

An intermediate scenario may be a statification system and some clever caching logic.

Statification is the process of putting your content generation code into a 404 page handler and have that handler generate requested content. The idea is that on a second request the content will be there and thus a static file is being served with a 200 OK, using the fast path of the web server.

A typical example for this kind of task would be a script that generates its name as a PNG: By requesting http://vvv.k6p.de/statify/example.png, we are returning a PNG image that contains the text "example" - the number of potential PNGs is infinite, and we cannot possibly precalculate all of them. But a few PNGs are requested over and over - most likely because we are referencing them via some IMG tags in some HTML pages. It would be pointless to generate them again and again for each requester, because these images never change.

Here is how to do it, using PHP:

This is a translation of a german language article I wrote two weeks ago for my german language blog.

In 2004, when I was still working for web.de, I gave a little talk on Scaleout on Linuxtag. Even back then one major message of the talk was "Every read problem is a cache problem" and "Every write problem is a problem of distribution and batching":

To scale, you have to partition your application into smaller subsystems and replicate the data. The replication should be asynchronous, not using two phase commit (2pc), or the gain will be smaller to nothing. Writes must be delayed and batched, so they can be handled more efficiently. To avoid bottlenecks, data storage should be decentralized and a central database should be avoided.

That is often contradictory to what traditional database classes at university teach: It is insecure, you are working with false or outdated data and you don't even know at all time if everything worked out the way you imagined it should have.

It has an important advantage: It works. And it scales.

In Five Common PHP database problems, Jack Herrington writes about

five common database problems that occur in PHP applications -- including database schema design, database access, and the business logic code that uses the database -- as well as their solutions

My experience in the field differs slightly from his observations, and so I thought a few annotations may be in order.

This is a quick tour of DRBD and how it compares to local RAID and to MySQL replication. DRBD is short for "distributed raw block device", so what it does is essentially RAID-1 over a network cable. You will be able to have two copies of a block device on two different physical machines, one of them the primary, active node and the other one a secondary, passive node.

The DRBD tour in this blog post has been created on two vmware instances with a Suse 10.0 Professional installation on each which I am using to show the most essential features of DRBD. Each vmware has a bit of memory, a network card, a boot disk with a text only Suse 10 installation and a second simulated 1 GB SCSI disk besides the boot disk to demonstrate stuff. The two instances are connected on a simulated local vmnet instance and share the 10.99.99.x/24 network, they are called left
(10.99.99.128) and right (10.99.99.129).

Right now I am in Skien, which is somewhere in the middle of the dark forests of Norway. Skien, a bustling town of a whopping 45.000 people is the seventh largest municipiality in Norway, and also the home of the PHP Vikinger Unconference.

My part on this Unconference was to hold a Nontalk, a session where I asked the audience to come up with things that they think are typical for Everyday PHP use and that currently do not have a name.

The following stuff are the slightly edited and commented notes I made in front of the audience while moderating our session.

This is a quick tour of LVM and a demonstration how it is superior to static partitions. Basically, LVM provides you with a way to create dynamic partitions - you will be able to grow and shrink partitions on demand, move them between disks and snapshot them for backup, all while the filesystem and database on top of it are active and busy.

The LVM tour in this blog post has been created on a vmware instance with a Suse 10.0 Professional installation which I am using to show a combination of RAID and LVM configuration examples. The vmware has a bit of memory, a network card, a boot disk with a text only Suse 10 installation and 8 small simulated SCSI disks besides the boot disk to demonstrate stuff.

Here is the configuration for the basic system.

PHP is different. Unlike Java for example, there is no formal community, and no formal community process. PHP does not see itself as controlled by a company, or even large corporate players. PHP is not developed, it kind of grows. People using other languages see this as a weakness, but I actually think of it as a strength of the language, the platform and the community.

PHP is used differently than for example Java. Successful PHP projects use different strategies. If you have listened to what Rasmus has been telling you in his speeches during the last two years, you might get an idea of how PHP is different, and why. If you are comparing the approach MySQL has been using in the Dell DVD webshop benchmark uncontest with the other PHP approaches, you can see some of these principles applied.

Unfortunately, for many of these principles and methodologies no fancy names exist. So in my untalk on the PHP unconference at PHP Vikinger I invite you to describe the principles that you think make PHP different, and then we will try to find fancy names for them in order to be able to discuss them, and promote them.

I think this is important - nobody would have taken "put procedure call parameters into CGI parameters and just call them instead of building large XML requests to encapsulate everything" serious before it was called RESTful. And few people can understand Rasmus "scale by request and not by session, build lightweight and frameworkless pages and use the language and its modules as a framework instead of including tons of classes per request" before we can find a number of fancy acronyms for these things.

What else can you find that PHP does differently? Can you build cases for these patterns? And can you find good names for them?

Many maintenance procedures need to generate SQL as strings, and then need to execute that string as SQL. Use PREPARE for this.



(as requested on Freenode:#mysql)

Hmm. Planet MySQL was close to unreadable for me this morning. Also, I had a technical discussion on writing style with a colleague just a few weeks ago, so I might as well take what I explained to him and put it into a form suitable for Planet MySQL. Please note that this is how I see things. This may or may not coincide with Arjen's view or the view of MySQL AB.

---

If you write blog entries for a blog that is being picked up by a planet, you are writing for a larger audience that for your own blog, and that audience may have other goals and intentions than regular readers of your blog. Here are a few things to keep in mind:

Regular readers of your blog are coming specifically to you because they share a lot of interestes with you regarding the topics you cover. You can go into a lot of depth and you can assume a lot of context.

When your blog is being picked up by a planet, things are different - a lot different.

The planet that picks up your entries is for its readers an overview of an entire field, often aggregating the RSS feeds from more than a hundred blogs. For the readers of the planet, the planet is the morning news, starting their workday. The planet will contain a lot of articles, some of them of no interest at all to the reader, some of them of cursory interest ("interesting to know that this feature exists") and some of them right on target. The objective of the planet is to aggregate news on a subject or field of interest, and to provide a daily overview to the planets readers.

The planet will link to your articles, and that is where you can provide the depth.

This has a few implications on your writing style in your own blog:

• Make sure your blog has facilities for you to distinguish between teaser and article body.
• Make sure you feed only the teaser to the planet, and not the full articles body.
• Make sure you are writing the teaser in a way that it can be rendered "context free". That is, make sure the teaser markup is self-contained and makes no essential refereces to stylesheets or other layout objects only present on your blog. It should look good in your blogs template, but it must also be reasonably readable in the planets template.
• Make sure you are writing the teaser in a way that in can be understood "context free". That is, make sure you are writing the teaser for the casual reader. Make clear, what the article is about and why a planets reader should have a glance of the rest of the article. Who is your intended audience, when should they read your article and what do you expect them to know beforehand? What will they learn?
• Keep the teaser short. The planet can only work properly if you provide room for 2-3 teasers to be visible in the planets layout at the same time.

The planets mission is overview, and preselection. Give it a chance!

For a migration, I needed a latin1_german1_cs collation, that is, I needed German umlauts to sort as their base characters.

Here is what to do: Find your charsets directory with "SHOW VARIABLES LIKE 'char%dir%'".

Inside that directory will be a file Index.xml. Find a free id and add your new collation using an unused id. Add a description and a name.

Inside the same directory is a file latin1.xml. Inside that file, copy the block of the collation matching your new collation most closely, and adapt it as needed. In my case, I changed latin1_german1_ci to _cs, and modified the character values to match.

Patch attached after the break.

How to use:

After applying the patch, the server must be restarted. "SHOW COLLATION" will now show the new latin1_german1_cs collation. "ALTER TABLE t CHANGE COLUMN oldcol newcol varchar(80) CHARSET latin1 COLLATE latin1_german1_cs NOT NULL" will change any old column to the new collation and recreate indices as needed.

Because we changed a single byte character set, we do not need to recomple anything. Try sorting by latin1_bin, latin1_german1_ci and latin1_german1_cs to observe how things are different with each collation.

These are the slides I have shown at my security talk (Room F at 11.50am today).

Security and the real world (PDF, 1.4 MB):