Testing disk I/O with the O_DIRECT flag

Sometimes I find the need to test disk IO rates, especially in the context of one of those delightful intra-office debates where the DBAs are blaming the lousy hardware for bad database performance and the sysadmins are blaming the lousy database.

You can perform a simple test of disk IO using the dd command. First, create a 1 GB file full of zeros:

dd if=/dev/zero of=1G bs=1024k count=1024

Then scan and time it…

time dd if=1G of=/dev/null bs=1024k count=1024

…and then use the output from that to calculate your MB/s.

This technique is flawed because it does not account for OS-level caching, which can give a sigificant boost to IO. The Linux 2.4 kernel allows programs to disable this caching by setting the O_DIRECT flag. Starting in Oracle 9.2 you can use this feature by setting the FILESYSTEMIO_OPTIONS init parameter to DIRECTIO or SETALL. (Direct IO is a controversial subject in the Linux community but Oracle has built significant functionality around it).

Therefore, if Oracle does not use OS-level caching (i.e. FILESYSTEMIO_OPTIONS is set to DIRECTIO or SETALL) , the results you get from the dd test will not be representative for your Oracle database.

The standard dd command does not provide a way to set the O_DIRECT flag (I think this has changed in RHEL5), but Oracle provides a free replacement for the standard Linux coreutils package, which contains the dd and cp commands. The folks at Oracle so generously provided this replacement package to help us debug Clusterware problems, but it can be used for other tasks as well. The replacement can be found at http://oss.oracle.com/projects/coreutils/files/

Oracle has not updated their “official” coreutils package since RHEL3, but there is an updated unofficial version for RHEL4 here: http://oss.oracle.com/~smushran/.coreutils/RHEL4-i386/

I was told that the RHEL3 version would work fine with Centos 4 (and, presumably, RHEL4), but the package installer complained about missing dependencies when I tried to force-install it, so rather than risk breaking something on a production server I sought a different solution.

I downloaded Robin Miller’s dt utility http://www.scsifaq.org/RMiller_Tools/index.html and this provided O_DIRECT functionality. To use it, simply unzip the archive for your platform and then move the single dt executable to your system path (or execute it directly from the unzip folder; it requires no special installation).

dt allows you to create a big file similar to one created by dd; then it will scan that file and calculate the transfer rate for you.

When you run dt in output mode, it performs both a write and a read test:

$ dt of=1G limit=1g bs=1m dispose=keep flags=direct

Write Statistics:
Total records processed: 1024 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 1073741824 (1048576.000 Kbytes, 1024.000 Mbytes)
Average transfer rates: 29059319 bytes/sec, 28378.241 Kbytes/sec
Number I/O's per second: 27.713
Total passes completed: 0/1
Total errors detected: 0/1
Total elapsed time: 00m36.95s
Total system time: 00m00.19s
Total user time: 00m06.30s

Read Statistics:
Total records processed: 1024 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 1073741824 (1048576.000 Kbytes, 1024.000 Mbytes)
Average transfer rates: 50840048 bytes/sec, 49648.485 Kbytes/sec
Number I/O's per second: 48.485
Total passes completed: 1/1
Total errors detected: 0/1
Total elapsed time: 00m21.12s
Total system time: 00m00.13s
Total user time: 00m10.08s

Total Statistics:
Output device/file name: 1G (device type=regular)
Type of I/O's performed: sequential (forward)
Data pattern read/written: 0x39c39c39
Total records processed: 2048 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 2147483648 (2097152.000 Kbytes, 2048.000 Mbytes)
Average transfer rates: 36980948 bytes/sec, 36114.207 Kbytes/sec
Number I/O's per second: 35.268
Total passes completed: 1/1
Total errors detected: 0/1
Total elapsed time: 00m58.07s
Total system time: 00m00.32s
Total user time: 00m16.38s
Starting time: Fri Nov 14 15:09:12 2008
Ending time: Fri Nov 14 15:10:10 2008

When you run it in input mode, it performs just a read test:

$ dt if=1G limit=1g bs=1m dispose=keep flags=direct

Total Statistics:
Input device/file name: 1G (device type=regular)
Type of I/O's performed: sequential (forward)
Data pattern read: 0x39c39c39
Total records processed: 1024 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 1073741824 (1048576.000 Kbytes, 1024.000 Mbytes)
Average transfer rates: 55290516 bytes/sec, 53994.645 Kbytes/sec
Number I/O's per second: 52.729
Total passes completed: 1/1
Total errors detected: 0/1
Total elapsed time: 00m19.42s
Total system time: 00m00.09s
Total user time: 00m09.30s
Starting time: Fri Nov 14 15:10:38 2008
Ending time: Fri Nov 14 15:10:57 2008

Try running it without the flags=direct argument, and you will notice a significant difference in these stats.

See also:

Calculating the delta between two row values

In Oracle, the standard way to calculate the difference (delta) between two values is using the LAG() analytic function.

For example… Suppose you’ve got a table of numeric values–

create table t as
select rownum id, value from v$sesstat where value > 0
/

select * from t;

ID              VALUE
---------- ----------
1                   1
2                   1
3                 967
4                  10
5                   3
6                  71
7               14649
8                 175
9                4517
10                207
...

Here’s how you calculate the delta between one value in the VALUE column and the value in the previous row:

select id, value,
value - lag(value) over(order by id) delta
from t
/

ID              VALUE      DELTA
---------- ---------- ----------
1                   1
2                   1          0
3                 967        966
4                  10       -957
5                   3         -7
6                  71         68
7               14649      14578
8                 175     -14474
9                4517       4342
10                207      -4310
...

OK, that seems pretty straightforward. I bring this up because I was using subquery factoring clauses (“WITH clause subqueries”) before I found out about analytic functions, so this is how I used to approach the problem of calculating deltas:

with v as (
select * from t
)
select
b.id,
b.value,
b.value - a.value delta
from v a, v b
where a.id = b.id-1
/

ID              VALUE      DELTA
---------- ---------- ----------
2                   1          0
3                 967        966
4                  10       -957
5                   3         -7
6                  71         68
7               14649      14578
8                 175     -14474
9                4517       4342
10                207      -4310
...

That method (joining a table to itself) is fairly inefficient. To demonstrate, I gathered stats for t
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t', estimate_percent=>100, cascade=>true);

…and executed each query (the one using LAG, and the one using WITH) after setting
set autotrace traceonly

Here’s the execution plan for the method using LAG:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   826 |  6608 |     3  (34)| 00:00:01 |
|   1 |  WINDOW SORT       |      |   826 |  6608 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |   826 |  6608 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

…and here it is using the subquery factoring clause (WITH):

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   826 | 13216 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   826 | 13216 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |   826 |  6608 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |   826 |  6608 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

But the remarkable thing is the change in consistent gets:

using LAG:       4
using WITH:     62

This is cool because you can use LAG to retrieve a graphable table of wait events from the AWR that make it easier to spot problems. For instance, you can retrieve the time waited for some wait event such as enq: TX – row lock contention

select
to_char(a.begin_interval_time,'YYYY-MM-DD HH24:MI') day,
nvl(b.time_waited_micro,0) - nvl(lag(b.time_waited_micro) over(order by a.snap_id),0) time_waited_micro
from dba_hist_snapshot a, dba_hist_system_event b
where a.snap_id = b.snap_id(+)
and upper(b.event_name)=upper('&event_name')
and a.begin_interval_time between trunc(sysdate-&num_days) and sysdate
order by a.snap_id
/

and then graph it, helping you visualize how row locks have affected your database over time:

enq: TX - row lock waits

Back in the blog

After a long hiatus, I’m resuming my activity on this blog, devoting it mostly to Oracle and other database matters.

Removing a collar from a feral cat

I recently got a feral cat (Max) from a shelter in Brooklyn to be integrated into my local cat colony. Unfortunately Max came with a plastic ID collar that the shelter people had put on him during his incarceration at the pound, and obviously this collar had to come off before I let Max out into my colony (if he got it caught on a branch he could choke to death).

So the challenge was, How to remove a collar from a feral cat that is wild, terrified of humans, and cannot be touched except under sedation?

Here’s what I did (after getting some advice from Bryan Kortis at Neighborhood Cats):

First I raised Max’s cage up on blocks so that there was about a half-inch of space beneath it. This allowed the forks of a trap divider to go down through the floor of the trap, “locking” it in place.

Divider clearance

My plan was to confine Max in as small a space as possible on the “square” end of the trap (not the tapered end), so that I would have easier access to him.

Confinement space

With the first trap divider in place, I gently nudged and prodded him into the confinement space using the other divider. Fortunately he was more stubborn than violent so all I needed was a bit of patience.

Squished cat 1

Max was not very pleased with this at all.

Squished cat 2

Next I slid a quarter-inch diameter wooden dowel down into the cage, through Max’s collar, and through the bottom of the cage.

Dowel
This effectively “locked” him in place, making it easier to access the collar, and making it less likely to injure him in the process of cutting the collar, which came next:Scissors

Here I was able to reach a pair of scissors in between the bars of the trap and make the cut. This was why I chose the square end of the trap: depending on where Max had chosen to position his body, being at that end of the trap gave me more options for getting at the collar. If necessary, I could even have opened the trap’s front door and reached in between the bars of the divider, which are wider than the trap’s bars.

And here, the collar is cut.

Cut

Voilà.

Catfud Revisited

Since I started making my own catfood back in August, I have refined my procedure somewhat.

For one thing, I’ve become more skilled at carving up a chicken carcass. With a good heavy santoku knife and sharp kitchen scissors I can skin and dismember a 4.5 lb chicken in about 20 minutes. I got considerably faster when I discovered there are pressure points that “pop” out the shoulder and hip joints, allowing easier cutting and removal.

One of the big problems I had in my early attempts was that bones would jam the machine and bone chips got jammed between the round grinding plate and the housing. Thus not only did I have to stop and clear the bone jam every few minutes, but it was very difficult to remove the plate to clear the jam on account of the jammed-in bone chips.

I discovered the solution: by trickling water through the input tube during the bone-crushing phase, the mechanism stays lubricated and there are no more jams. This also helps to keep the holes in the plate clear, making clean-up much easier.

Cutting the bones into smaller chunks also helps a lot.

I try to buy chickens not larger than 4.5 lbs, since smaller birds have smaller bones and that makes it easier on the grinder.

So here is my process:

1. Wash the chicken in running water to remove surface bacteria.

2. Skin and dismember the chicken. Discard the skin and as much fat as is convenient.

3. Remove as much muscle meat as possible from the bones (this gets easier with practice). Separate the muscle meat and bones in separate bowls.

4. Chop the bones into lengths not longer than 1 inch. This is quite satisfying if you use a sharp, heavy knife. I always keep my unused hand behind my back while doing this so that I am not tempted to chop my fingers off.

5. Measure out the water that is needed for this weight of chicken (see chart below) and set it near the grinder.

6. Install the medium plate (5mm diameter holes) in the grinder.

7. Send the bones through. After every few bones, trickle a few tablespoons of water into the intake chute. Don’t stand directly in front of the grinder, as meaty water will sometimes spit out. Don’t wear your good tuxedo when doing this.

8. When you’re finished with the bones, put the ground-up bones aside and put an empty bowl in front of the grinder output. Flush the grinder with a good portion of the remaining water (but don’t use too much– at the end of the day, you don’t want to use more water than the recipe calls for). You now have a bowl with bony, meaty water in it.

9. Open the grinder and scoop the bone chips that didn’t go through into your bone-water bowl. Set it aside; we’ll deal with it later.

10. Now install the large plate (8mm diameter holes) in the grinder. Send the muscle meat through.

Note: The idea here is that you want fairly large chunks of muscle meat for your cats to chew on. This is very good for their teeth as it is a natural way of removing tartar. I have read some recipes where people chop up their muscle meat with a knife rather than grinding it; I tried that with my cats and the chunks were too big– they didn’t acknowledge that the chunks were actually food and they ate everything except the chunks. All my cats except Jill, who was born feral, did this; Jill (who is known to have eaten her own children– more about that another day) tore into the big chunks with relish.

11. Set the ground muscle meat aside and use the remaining water to flush the grinder; use the bowl of bone-water from earlier.

12. Open the grinder and scoop anything that didn’t grind into the bone-water bowl.

13. Pour the contents of the bone-water bowl, along with the unground bones, into a blender and run it on high for a few minutes. The result will be a nice pink mush of watery goop. If any large bone chips remain, discard them.
14. Add your nutritional supplements: egg yolks, vitamins, psyllium. Add psyllium last as it is a coagulant and if it sits too long you will be left with a firm stinky mass of jell-o that is hard to work with.

15. Mix the bone-meat, muscle-meat, and supplements in a big bowl. Decant into containers, and freeze.

That’s it. It takes me about 2 hours to process 9 lbs of chicken, including clean-up.

Here are the proportions I use:

Chicken (lbs) 4.0 4.5 5.0 5.5 6.0 6.5 7.0 7.5 8.0 8.5 9.0 9.5 10.0 10.5
water (cups) 1.8 2.0 2.2 2.4 2.7 2.9 3.1 3.3 3.6 3.8 4.0 4.2 4.4 4.7
taurine (mg) 3,555.6 4,000.0 4,444.4 4,888.9 5,333.3 5,777.8 6,222.2 6,666.7 7,111.1 7,555.6 8,000.0 8,444.4 8,888.9 9,333.3
Vitamin E (IU) 711.1 800.0 888.9 977.8 1,066.7 1,155.6 1,244.4 1,333.3 1,422.2 1,511.1 1,600.0 1,688.9 1,777.8 1,866.7
Vitamin B (mg) 177.8 200.0 222.2 244.4 266.7 288.9 311.1 333.3 355.6 377.8 400.0 422.2 444.4 466.7
salmon oil (mg) 1,777.8 2,000.0 2,222.2 2,444.4 2,666.7 2,888.9 3,111.1 3,333.3 3,555.6 3,777.8 4,000.0 4,222.2 4,444.4 4,666.7
psyllium husk (tsp) 3.6 4.0 4.4 4.9 5.3 5.8 6.2 6.7 7.1 7.6 8.0 8.4 8.9 9.3
egg yolks 3.6 4.0 4.4 4.9 5.3 5.8 6.2 6.7 7.1 7.6 8.0 8.4 8.9 9.3

Notes: I tried using the egg whites, but my cats couldn’t stand the taste. I’ve also read that they can’t digest them.

I haven’t been able to find the supplements in free-form, so I have to waste quite a bit of time opening up the gelcaps and pouring out the powder (or, in the case of the oils, piercing them and squeezing it out).

As for the Taste Test… my guys love it. I tried giving some to Sammy, a feral kitten I’m confining until I can get him fixed next week, and he scarfed it down and now won’t touch the canned food I give him. Picky. Sheesh.

Sammy In Jail

Oracle Error Messages

I’ve got a script that runs through the alert logs for all the Oracle databases I manage every 10 minutes or so and sends me a text message containing the error number if it finds an error. Sometimes, when the errors are of the more exotic variety, I am left scratching my head– Do I need to leap into the batmobile and race to the nearest terminal to avert the latest crisis? Or can I continue sunning myself on the roof of my Barcelona penthouse? It is always a dilemma.

I could look up the error on one of the scads of websites out there that list Oracle error messages using my Treo‘s (painfully slow) Internet connection, but that is, as I believe I said, painfully slow.

Makes me wish there was a lightweight web page out there, without images or advertising, where I could look up the error and get a quick synopsis of it.

Well now, thanks to me, there is such a web page: http://phlonx.com/oerr

It contains all the currently-documented ORA, PLS, and RMAN errors. I could add more if I find I need to add them or if someone requests it.

One nice feature of this page is that it accepts alphabetic input from a Treo keyboard. For instance, to look up ORA-16005 you don’t have to toggle your keyboard to numeric input, you can just type eg00f and it gets automatically translated.

Ironically, the Oracle error messages reside in a MySQL database. Massaging the raw data into valid INSERT statements was a bit of a chore in the beginning because I was using as source material a file that comes installed with every (Unix, seems to be lacking from Windows) Oracle installation: $ORACLE_HOME/rdbms/mesg/oraus.msg. This file is rather sloppily maintained, making it difficult to parse out the different fields in a consistent way. Took me a lot of trial and error.

This file only contains the ORA- errors, so for the others I had to turn to the official Oracle documentation itself: http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/toc.htm …and reformatting the raw data in those pages was a breeze using Textpad and regular expressions. This technique missed the fields that span multiple lines, but I didn’t worry about that since those are few and could be handled manually.

Catfud

Back when the tainted petfood scandal broke earlier this year, I resolved to make my own catfood. (Which is to say, I would continue to eat my own regular food, but I would make catfood for my cats).

Grinder of MeatIt wasn’t until I met someone who did it herself that I was inspired to take the plunge. My Northern Industrial Tools Electric Meat Grinder finally arrived, and I took it for a test drive with a whole chicken I bought.

I’m a squeamish city boy not used to handling wildlife. Never seen a whole chicken before (except at the petting zoo). I was surprised to find a little paper bag full of organs inside the chicken– quite a convenience! I had no idea chickens had evolved to the point where their vital organs grew in paper bags.

It was hard not to anthropmorphize the chicken. Peeling the skin off was a bit of a chore. It reminded me of trying to wrestle the dress off a… well never mind.

I also discovered the importance of having a good sharp knife (I don’t). After a few minutes of hacking the chicken ceased looking like a prom date and more like Alien.

I worried about the bones. This grinder is not rated for bone crushing, but my informant assured me it handles chicken bones without a problem. So it does. First I sent the bones through using the coarse filter, but found the bone fragments a little big for my comfort. So I sent it all though again using the fine filter, so that the resulting bone fragments had a consistency of fine sand.

My recipe was simple. I added water and taurine; there are other things I could (and should) add in like psyllum, once I get them.

But the proof of the pudding was in the eating… of which there has so far been little. All my indoor cats have so far turned up their noses, but the ferals outside love it.

The problem I think is that they’re addicted to dry food. I started feeding Purina One for Sensitive Systems to Squid, who has a sensitive tummy, and when everyone turned out to like it, I just fed that to everyone by default. So training them out of the dry food habit will be a chore.

Here are links to the instructions I followed:

http://www.catnutrition.org/pictorial.php

http://www.catinfo.org/makingcatfood.htm

Renaming your Windows XP user account

Someone recently asked me how to rename their C:\Documents and Settings\mysername folder but they didn’t leave a correct email address so my reply bounced. So here, for the benefit of all, is my answer.

Under the Documents and Settings is a folder saved in my name instead of my sons. When I had the new hard drive installed and XP the tech used my name instead of my son. Since it is a system folder I cant change it and really don’t want to pay another bill for trying to. Any other option?

Tosca

Tosca,

As far as I know, you cannot change the name of that folder since it is linked to your user name, and that cannot be changed. (You can change the user name alias, which appears on the login screen, but that does not change the name of the underlying folder in “Documents and Settings”).

You can, however, create a new account, transfer all your settings from the old account profile to the new one, and then delete the old one.

In detail:

Let’s say you have an account named “dad” and you want to change it to “son”.

Go to Control Panel, User accounts, Create a new account.

Type in the account name (“son”), click Next, make the account type “administrator”.

Restart windows and log in using the “son” account you just created.

Right-click on the “My Computer” icon on the desktop, choose Properties, and click the Advanced tab.

Under “User Profiles”, click Settings.

Select the “dad” profile from the list, then click “Copy to”.

Click the Browse button, and browse to the location of your new user profile (it should be in C:\Documents and Settings\son

Click OK, Yes (when it asks if you want to delete the contents of the directory), then OK out of all the dialog boxes.

Restart the computer and log in as “son” again.

Finally, you can delete the “dad” account from the Control Panel, User Accounts.
Hope this saves you a trip to the tech guy!

Three Normal Forms tutorial revised

After two months in the wilderness, my tutorial on the Three Normal Forms is back online.

I took it down because in the original, it had some serious flaws. While not factually inaccurate, the section pertaining to NF1 was vague and misleading, and actually belonged in the NF2 section. I have corrected and expanded the NF1 discussion, and I hope I have not obscured the simplicity of the original tutorial.

Thanks especially to Richard Moignard whose helpful feedback finally inspired me to make the changes.

Gigantic Sea Turtle on Staten Island

Saturday morning I was jogging down South Beach a little south of the Verrazanno Bridge when I came upon a stupefying sight: a huge sea turtle had crawled up on the beach and died. It was a little awe-inspiring, because the turtle’s great size implied great age. I measured it with my steps; it was 4′ 8″ from tail to beak. I stood there for a while, wondering why nobody else around seemed interested in this remarkable creature, then continued with my run.

After getting home I decided there must be someone who would be interested in such a thing. I called the Bronx Zoo but couldn’t get past the receptionist who insisted the zoo doesn’t make house calls and she said I should call the City to get it cleaned up.

That wasn’t quite what I had in mind, so next I called the New York Aquarium and was transferred to someone in “the lab”. The woman I spoke to seemed quite interested and took down whatever little information I could give her.

This afternoon Cancer-grrl and I went back to take a look. I was a little worried that I might have dreamt the whole thing. As we approached the spot on the beach the turtle was no longer where I had originally seen him, but he had come loose and was floating with the tide just a few yards offshore. He was being buffetted pretty badly by the waves and it will no doubt not be long before he starts to disintegrate. We were able to take these pictures. This was a majestic beast and it felt important to make some kind of record of its death.

I think it was a loggerhead turtle, which has been known to frequent Staten Island.

02.jpg

10.jpg

18.jpg

20.jpg

14.jpg 11.jpg

21.jpg

22.jpg