To continue the pid file theme I’ve found another slight issue. This was unrelated to the testing which I found the previous pid file issues. I was working on an unmonitored development mysql system. While working on it I ran it out of disk space in /. The box has it’s mysql datadir in a separate partition which had plenty of space. The pid file is in a dir on /. When I started mysqld_safe mysqld exited because it couldn’t create the pid file. mysqld_safe continued to restart mysqld until I saw the problem and killed it a few minutes later. I’m not sure exactly why, I didn’t spend very much time digging into a failure that I caused by filling up the disk. mysqld was exiting because it was trying to create a pid file in a full partition.

Note: This was a stock mysqld, not one running my pid file patch.

I can think of at least two major reasons why systems delay flushing changes to durable storage:

1. So they can do the work when it's more convenient.
2. So they can do less work in total.

Let's look at how the second property can be true.

A commenter on Deva's recent post on InnoDB adaptive flushing asked,

That’s really interesting stuff; am I reading it correctly though that adaptive flush actually increased the IOOPS? Looking at the IO graphs, it looks like both the peak IO rate and average IO rate were higher with adaptive flush nabled (assuming I’m reading properly).

Yes. Adaptive flushing actually increased the overall number of I/O operations performed. Smoothing out the workload can cause more work to be done. To see why, remember that InnoDB works in 16kb pages at a time. Suppose …

The best overall method of performance optimization is optimization for response time. Users care about response time, not load average or cache hit ratios. The job of a system is to accept some request and do the required work, and deliver a result. The time elapsed between the request and the result is the response time.

Methods of Response Time Optimization

Not all optimization methods are created equal. Here are a few I see commonly.

• No method. Most people simply have no method of performance optimization at all. They just look for things that look “bad” and try to make them look “better.” In the MySQL database world, the classic example is trying to improve a cache hit ratio. This is utter folly, and doesn’t become any less stupid no matter how many times it is taught and repeated.
• Server Load Reduction. One step up from that is to try …

I wanted to see how various filesystems in Linux stacked up to each other. So, I decided to benchmark them.
The filesystems I am benchmarking are: ext3, ext4, xfs, reiserfs, btrfs, and nilfs2.
The system I am using to do the benchmarking:
Gigabyte MA790FXT-UD5P MotherboardAMD Phenom II 955 Quad Core 3.2GHz4 x Mushkin Silverline PC3-10666 9-9-9-24 1333 2GB RamGigabyte GV-N210OC-512I Geforce 210 512MB 64-bit PCIE Video CardLG 22x DVD Sata Burner2 x WD Caviar Blue 320GB 7200RPM Sata Drives (OS/Other)4 x WD Caviar Blue 80GB 7200RPM Sata Drives (Data)4 x Patriot Memory 32GB Sata SSD (Database)
Gentoo Linux 10.1
The diskspace used is a software raid 0, comprised of 4 partition slices of 4864 cylinders (37.3GB) from the 80GB Hard Drives.
I used the fileio benchmarks in Sysbench 0.4.10 to do these tests.
I created a script that formats the filesystem, mounts it, runs the sysbench prepare statement, clears the …

The 10th edition of Fosdem is getting closer, yes .. I said 10 ,10 decimal that means ten, not two :)

It's kinda hard to imagine that this is actually going to be my 10th fosdem , it feels weird ..

Anyhow.. Lenz just announced a call for participation for the MySQL and Friends devroom.

I`m guessing a talk on .LolSQL might be a good fit for the "and Friends" part.

58 days to Fosdem ... !

Technorati Tags: devrooms fosdem mysql …

Last week Eben Moglen, founder and executive director of the Software Freedom Law Center (SFLC), submitted an independent opinion on the Oracle/Sun merger to the European Union (EU). Moglen summarized his submission as follows:

read more

According to the official lifecycle calendar at http://www.mysql.com/about/legal/lifecycle/#calendar, active support for MySQL 5.0 (including regular binary updates) will end on December 31st, 2009, which is about 3 weeks away.

Many folks are still using MySQL 5.0.45, as until October that was the package that came with RedHat. That was released in July 2007, over 2 years ago!

Upgrading to MySQL 5.1 is not difficult, though it requires more steps than just upgrading the packages.

There is a list with all the changes made that might affect the upgrade process at http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation/. This includes which variable names have been deprecated and changed, as well as how to upgrade stored …

FOSDEM 2010, the annual Free and Open Source Software Developers' European Meeting will take place again on February 6th and 7th in Brussels, Belgium. Next year they will celebrate their 10th anniversary (congratulations!) and we feel very honored and happy to announce that the organizers accepted our request for a developer room! They had to reject many proposals due to the great demand (there were 39 applications, but there are only 12 rooms available per day). Therefore we are glad to be among the excellent projects that will be present there.

Our devroom is titled "MySQL and Friends" and will be available for sessions on Sunday, 7th from 09:00 - 17:00. It will be located in room AW1.121 and provides 81 seats, a video projector with VGA cable, and wireless Internet. As for …

Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked relatively well. The row data was stored in table.MYD, indexes on top of it in table.MYI and information about the format of the row was
in table.FRM. Since MyISAM itself wasn’t crash safe, it didn’t really matter if creating/deleting the FRM file along with the table was either.

As more sophisticated engines were introduced (e.g. InnoDB) that had their own data dictionary, there started to be more of a problem. There were now two places storing information about a table: the FRM file and the data dictionary specific to the engine. Even if the data dictionary of the storage engine was crash safe, the FRM file was not plugged into that, so you …

The recently refactored "Table tools" dialog has a new tab now: "Bulk table editor". What is bulk editing?
* Move all tables of one or more databases to another database
* Change default collation of these tables
* Change table engine
* Convert data to a different charset
* Reset auto_increment value

See it in action: