DBA

According to my findings in Bug #31876, MySQL does not commit data to disk in Windows using the same method MS SQL Server and DB/2 are using. The method MySQL uses appears to be seven times slower in pathological scenarios.

The bug report contains a patch - thanks to the MySQL WTF (The Windows Task Force) and the lab provided by the customer for helping me to find that.

Does this work for you? I want to hear about your test results.

Lately, I have had opportunity to evaluate a very large Ruby installation that also was growing very quickly. A lot of the work performed on site has been specific to the site, but other observations are true for the platform no matter what is being done on it. This article is about Ruby On Rails and its interaction with MySQL in general.

One of the major contributing factors to the success of MySQL is the ease and simplicity of its replication. Read-slaves for scaleout and backup-slaves for noninterrupting backups are the norm in any MySQL installation I have seen in the last two years.

So how does replication work? And how shall it be expanded in the future?

What is available?

The binlog written by MySQL currently logs all statements changing the tablespace. It is a serialization of all tablespace changes. The binlog position, expressed as (binlog name, offset), is a database global timestamp - a timestamp expressed in seconds.fraction does not work for any precision at all, because on a multi-core machine multiple things can happen concurrently.

If you want to make a consistent full backup of the database, the database must not change during the backup. That is, it must be possible to associate one and exactly one binlog position with the backup.

In fact, if you have such a backup - one associated with a binlog position - and you happen to have the binlogs from that time until now, it is possible to do a point-in-time (PIT) recovery. You'd recover from the full backup and you'd then replay the binlog from the backups binlog position until now. That is why it is important to store the binlog in a filesystem that fails independently from the rest of your MySQL. That's also why you must not filter the binlog that is written by MySQL using binlog-do-db and binlog-ignore-db - if you do, you'll get an incomplete binlog that will fail to be useful in a PIT recovery scenario.

A slave in MySQL is now nothing but a binlog downloader and executor: The slave must be restored from a PIT-capable full backup. It is then being told the current binlog position and where to log in to get the missing binlog. The slaves IO_THREAD will then log into the master server and download the binlog to the local disk as fast as possible, storing it as the relay log. The slaves SQL_THREAD will then start to execute the relay log as fast as possible. Replication can thus be thought of as an ongoing live recovery.

In MyISAM, we do have LOAD INDEX INTO CACHE. In InnoDB this does not work. For benchmarking I often require a way to preload the innodb_buffer_pool with the primary key and data after a server restart to shorten warmup phases.

According to Blackhole Specialist Kai, the following should work: Another win for the unbreakable BLACKHOLE storage engine.

This article does not even contain the words database or MySQL. I still believe it is somewhat interesting.

Mail has, for some reason, always been playing a big role in my life. I have been running mail for two, my girlfriend and me, in 1988. I have been running mail for 20 and 200 people in 1992, setting up a citizens network. Later I designed and built mail systems for 2 000 and 20 000 person corporations, and planned mail server clusters for 200 000 and 2 million users. And just before I became a consultant at MySQL I was working for a shop that did mail for a living for 20 million users.

Mail is a very simple and well defined collection of services. You accept incoming messages to local users, you implement relaying for your local users with POP-before-SMTP and SMTP AUTH, you build POP, IMAP and webmail accesses, and you deploy spam filter systems and virus scanners for incoming and outgoing messages. This services collection does hardly change when you go from 2 to 20 million users – maybe the larger systems will also provide additional services such as portal services, a news server or other more directed stuff, but that is just fluff outside of the scope of the mail system. The solutions, though, are very different, and very much dependent on the scale of your operations.

These are the slides for my talk "Monitoring MySQL" at the MySQL Users Conference and Expo 2007 from yesterday.

I tried to reproduce my talk as a written text, but what I attach below is somewhat more and at the same time less than what I was actually saying yesterday.

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.

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).

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.

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)