Sun Microsystems, Inc. today announced new benchmark results involving the performance of the open source MySQL database running online transaction processing (OLTP) workload on 8-way Sun Fire(TM) V40z servers. The testing, which measured the performance of both read/write and read-only operations, showed that MySQL 5.0.18 running on the Solaris(TM) 10 Operating System (OS) executed the same functions up to 64 percent faster in read/write mode and up to 91 percent faster in read-only mode than when it ran on the Red Hat Enterprise Linux 4 Advanced Server Edition OS.

This week, I?m teaching ?MySQL 5.0 for Database Adminstrators? near Philadelphia. Of course, we covered Backup and Recovery. With one exercise that I made up, we had to spend some more time as expected. The main reason for it was that I knew about a specific mysqldump feature, but I didn?t know the little details.

The exercise asked the students to have a per table backup of the world database and later restore a single table only.

Usually, mysqldump would create a single text file containing CREATE TABLE and INSERT statements. With the --tab option, it would create two files for every table being affected.

My first guess was to create a new directory and have all the files being created there.

h-67-102-186-164:~ root# mkdir /tmp/worldbackup h-67-102-186-164:~ …

The MySQL Users Conference 2006 is just a few days away. Everybody's preparing for the journey. So am I. We all might probably want to hack away on the local MySQL installations on our laptops while on the road, but there might not always be a wireless access point in reach...

So you better get yourself a fresh offline copy of the MySQL manuals to work with. That's what I just did. But it got a little bit tedious since the manual had been split into multiple versions: Download all the versions you need, unpack them, rename the directories to reflect the version of the manual, probably install a symbolic link to the index.hml and remove the tar.gz package.

I'm a lazy guy when it comes to tedious work. And when I have to do the same thing for the second time (I downloaded the manuals already when I went on another trip recently) it's definitely time to write a script that does the work for me. Enters the …

Let us talk about pread() and pwrite() for a moment.

So what are the differences between the two of these, and read() and write()?

The difference between pread() and read() is that pread() is an atomic action.

Let me explain why.

For the average application that is reading data you would normally be doing a seek and then do your call to read(). If you are reading sequentially in a file you will never do the seek call, all you will do is the read() call. The read call causes an update to the the file descriptors position.

Now lets look at the problem where you are reading different blocks of the file instead of reading all blocks sequentially. Calling read () means that you will be doing your call to seek and then updating file->f_pos for the operation. For a pread() the operation of seek is internalized and file->f_pos will not be updated.

So they are …

Come visit the DotOrg Pavilion at the MySQL Users Conference next week!

The DotOrg Pavilion is where you will find a wide range of open source projects that compose the larger FOSS ecosystem in which MySQL thrives. Regardless of your specific interests in MySQL topics, we’re sure you’ll find the DotOrg pavilion to be a mecca for OSS enthusiasts, writers, speakers, and advocates. Meet and greet with some of the world’s most recognized free software organizations, as well as discover new up and coming projects.

Exhibit Hall Hours are:

• Tuesday, April 25 - 10:00 a.m. - 4:30 p.m.
• Tuesday Night Reception - 6:00 p.m. - 7:30 p.m.
• Wednesday, April 26 - 10:00 a.m. - 5:00 p.m.

Register for an Exhibit Hall Pass …

I leave for the MySQL User Conference this Saturday. In the meantime, I’m working on final preparation. This involves things like tweaking my slides, configuring my laptop, packing, getting a haircut, and all the little things you need to do before attending a conference for a week.

Tweaking my Slides - Going over my slides, doing test deliveries to check time and content. Once I have them how I want them I push them online so attendees can follow along. Tweaking happens even this late into things because I can be a bit of a perfectionist with the slides (most slides even have a good number of notes).

Configuring my Laptop - Because I work from the desktop when at home, I take some time to make sure the laptop has all updates and a good defrag before I head out. I also ensure that my slides are on the laptop and that its desktop is clean so attendees don’t see too much clutter. Since the laptop serves as my wife’s email …

Here is a more detailed example of LOAD DATA INFILE syntax:

First of all create a table to be loaded:

mysql> create table 2beloaded (field_1 char(1), field_2 int, field_3 char(1), fi
eld_4 varchar(50));
Query OK, 0 rows affected (0.15 sec)


Then the text file:

c, 1, a
b, 2, d

(named 2beloaded.txt and placed in c:\, so it's a file created on Windows platform!!)

Now the query to load it, we want to load the file setting proper line termination and all, but also to load field_4 of the table with the file name, here it is:

mysql> load data infile 'c:\\2beloaded.txt' into table 2beloaded fields terminat
ed by ',' optionally enclosed by '"' lines terminated by '\r\n' (field_1, field_2, field_3)
set field_4 = …

OK, there are an absolute ton (24 if I'm not mistaken) Birds of a Feather sessions going on at the UC this coming week. For those of you who like pretty grids, I've made an online grid to go with the MySQL Users Conference list of BoF sessions.

I myself will be hosting the Performance Tuning BoF on Monday along with Peter Zaitsev, and on Tuesday both the All Things PHP and MySQL Forge BoFs. Finally, on Wednesday, I'll be floating in between a number of BoF sessions. Be sure to check out the FULLTEXT BoF on Wednesday night if you're doing FULLTEXT work. Andrew Aksoyonoff, creator of the Sphinx Fulltext Search Engine, will be hosting that BoF as well as being present in the DotOrg …

Each year my weblog gets bombarded with MySQL entries over the 5-day MySQL Users Conference. I've been posting a lot more about stuff happening with the MySQL database lately, but it doesn't compare to the 4-6 entries a day during the conference. Not only that, the entries will likely be more technical than other MySQL posts. My apologies to anyone who doesn't care about the MySQL stuff I post. You may want to put a filter on, or take a break for a week.

For planetmysql readers I'm just not sure how we're going to handle what I think is coming next week. The last year has been huge for the MySQL community. One year ago at this time Mike Hillyer was getting the conference aggregator ready and a few folks aggregated their posts, which seemed like a lot to follow.

Since then planetmysql has grown to over 100 syndicated …