I'll be using a recipe from my friend Andrew Keener:
Recipe
Ingredients:
1 gallon (12 lbs) of honey.
6lbs of fresh strawberries.
Red star wine yeast.
Pectic Enzyme to treat 5 gallons.
Yeast nutrient if you want to... not required.
Steps:
Bring about 3 gallons of water up to 160 degrees Fahrenheit in a 6+ gallon pot. Dump all the honey in and cover. Let it hang out for 20 minutes or so. While waiting, puree the strawberries or chop them finely. Put them in a pot and bring it up to 156 degree Fahrenheit stirring occasionally. Cover and let sit for 20 minutes.
Dump honey and strawberries into a 5 gallon fermenter and top off with water to 5 gallons. Pitch yeast when it comes down to an appropriate temperature (for wine yeasts this is usually 80 degress fahrenheit).
Let it ferment for roughly 2 weeks or until it bubbles once every 90 seconds. Then transfer to secondary. The strawberries will have mostly disintegrated so I try to suck up as few of them as possible when transferring.
Then let it sit for another month. If you take gravity reading throughout I usually finish when the gravity gets down to 1.010.
When I bottle I add 3/4 cup of corn sugar. Alternatively you can add 1 cup of honey but I would steep the honey at 156 degrees fahrenheit for 20 minutes in a little hot water before adding it.
My Changes to the Recipe
I'm excited that i have some good Wilmington suburban honey from a friend. I has hoping to make this during strawberry season and use hand picked local strawberries, but I've missed the season.
I have a couple of changes I'm going to make to the process/recipe:
I'm going to use Campden on the strawberries, rather than heat them. This is on the recommendation from John at Wilmington Homebrew Supply. The idea is that the fruit is more sensitive to heat, and it will change the flavor.
I'm going to try "Staggered Nutrient Additions" as described in this BrewingTV Recipe by Curt Stock. The idea here is to make sure the yeast is very healthy and vigorous.
I may also try some additional yeast pitchings. Can't remember where I saw this, but it seems that the yeast can fall to the bottom, or become inactive. Re-pitching makes sure that the desired yeast remains dominant.
I'm going to add two vanilla beans to the honey at the very beginning when I'm heating the honey. I love a little vanilla sugar on strawberries. I'll split the beans, scrape the seeds in, and add the whole beans. I've used vanilla beans in other recipes - they tolerate heat well, and their flavor is alcohol-soluble.
Going to do most of this today, and will try to keep detailed notes as I go.
Great book that helped me understand how a hard disk works, and how to organize my data in files and folders. Basic stuff that most kids know by age 10 now. But in 1988, people were still figuring that stuff out - this book was really helpful to me. It explained what was physically going on inside the hard drive.
It was the first time I'd heard about maintaining your Hard Drive (HD) performance by running a defragmentation utility.
Defragmentation (defragging) has become a standard maintenance operation on hard drives, and it's something I automatically set up on machines that I build. I'm sure I'm not alone.
But, I'm going to have to change.
I recently bought a Chromebook with 16GB of solid state (SS) storage, and no hard drive. (I love this machine, but that's another post). The operating system comes from SS, and I'm meant to keep my data files in the cloud. It boots extremely fast, and I am rarely waiting for data from the cloud. In other words, this Chromebook feels faster than any other machine I have.
The speedy feel is due to the solid state storage. It's not the CPU. It's not the amount of RAM. I have other machines that have more/better of both, and still feel slower. It's the hard drive.
It turns out that neither of these things makes any sense on an SSD, and can actually be harmful.
To understand why, let's look at how HDDs work.
HDDs have spinning platters in them (see video below). In other words, there are moving parts.
Here's a video showing clearly the multiple platters on an old-school drive:
In order to read or write from a platter, the read/write head must move to the right position on the spinning disk (access time), then wait until the appropriate spot on the disk spins to the position under the read/write head (rotational latency time). Then, all the magnetic spots on the platters have to travel under the read/write head.
These two physical operations take time. And because these are physical operations we can affect how long each one takes.
So what do we mean by "fast" parts of the disk? In short, the outside edges of the platters, and/or near the head park location.
Each platter is "lined" from center to edge, creating circular regions called tracks. Then each track is sliced into pieces that contain data called sectors. Because the outside track is longer than the inside track, there are typically more sectors on the outside track. The actuator arms (the arms that move the read/write heads across the platters) move the read/write head from track to track, then sectors are read as they spin under the read/write head.
To reduce access time we could put data in a spot on the platter that does not require the actuator arm to move very far. In other words, put data on tracks close together. In the videos above, notice that read/write heads begin "parked" near the spindle at the center of the platter(s). To reduce access time, we could position data near the center of platters. Then the actuator arms do not have to move as far, so the access time is reduced.
Once the read/write head is in the right spot, it must read the magnetic sectors on the disk as they pass under the read/write head. Because there are more sectors on the outside tracks, more data can be read/written in one revolution of the platter. So, outside edges are "faster".
And what do we mean by "consolidate file fragments".
Files commonly get split into multiple pieces and stored in fragments on a drive. This usually happens when a file is too big to fit into an open space on a disk. Over time, this can happen to many files, and each file can be split up into many pieces (tens, hundreds, or even thousands of pieces.)
So, if a user want to open a file that is actually split into two pieces on the hard drive, the activities are:
access the track where fragment 1 is located (access time)
wait for platter to rotate the first sector of fragment 1 under the read/write head (rotational latency)
allow all sectors of fragment 1 to pass under the read/write head (read time)
access the track where fragment 2 is located (access time)
wait for platter to rotate the first sector of fragment 2 under the read/write head (rotational latency)
allow all sectors of fragment 2 to pass under the read/write head (read time)
If the file was all in one piece, items 4 and 5 would not have to be done! Imagine a file in a thousand pieces - that's a lot of extra work!
Alright, so fragmentation on a HDD can make it take longer to read/write data files. And to speed things up, we can de-fragment, which puts all the file fragments together, and puts them on fast parts of the platters.
But why not do that with SSDs?
Well, SSDs do not have spinning platters, or moving actuator arms - they have no moving parts. They consist of solid-state chips accessed electronically. This means that access times are the same for any storage location in the SSD. There are no fast locations and there are no slow locations.
Also, because the time to get to a piece of data does not depend on the location, having file fragments physically next to each other means nothing. Said another way, accessing sectors 1 and 2 is no faster or slower than accessing locations 1 and 50000. Proximity means nothing.
OK, so consolidating files doesn't help on an SSD. And there are no fast/slow sectors in an SSD. But why not defragment anyway?
Well, solid state memory chips actually have a limited life cycle, meaning that they wear out. Writing to the sector actually wears it out a little. So moving data around in a defragmentation operation actually reduces the life of your SSD.
So:
defragment your HDDs - it will help performance
DON'T defragement your SDDs - it won't help, and it actually hurts
I've come to creating all my tables using an auto-number, integer primary key called "ID".
In other words:
CREATE TABLE Person
(
ID INT IDENTITY(1,1) PRIMARY KEY,
...
)
In another post, I talked about the good characteristics of a synthentic, non-meaningful key. In summary, meaningful keys are likely to need to change at some point, which can cause a problem because:
all foreign keys will need updated
some foreign keys may not be easily updatable, for instance older records that are archived, or off-line
So if you are going to synthesize, or completely invent, a field to be the primary key, what do you want out of that field?
In order to guarantee Entity Integrity, and satisfy the Guaranteed Access Rule (GAR), all you really need is that a key field be UNIQUE and NOT NULL. (The GAR requires that every value in a database be uniquely addressable. This is accomplished by tablename.fieldname WHERE primarykey=x.)
Another necessity is to have enough unique values for the table in question. Remember that data can live a long time (years, decades). Ask this question: How many records might be inserted in this table over the next ten years? That might seem hard, but you can start with how many per day, and do the math. Also consider that your traffic may increase over time, especially if your system is useful.
What else would be nice?
First, the field value for a record should not change. As pointed out above, a changing value in a primary key field will require cascading updates to all foreign keys. This can be difficult to accomplish, especially as data becomes older, and perhaps not online.
Second, the field should be small. In general, using the number of bytes necessary, but no more, keeps your records concise, and improves the number of records that fit on a page. In other words, when the database engine has to do an expensive disk IO to get a page, you'd like to get as many records on that page as possible. You are paying a disk IO, you'd like to get as many records as possible. Would you rather get 10 pencils for a dollar or 12?
Third, it might be nice to arrange it so that new records enter in an orderly fashion. This is where an "AutoNumber" field is helpful (IDENTITY in MS SQL Server).
Here's a layman's example. Think of a line of cars at a tollbooth. Usually, cars arrive at the tollbooth, and go to the end of the line. There's no question about where they go, and they don't affect any other cars in the line. But Suppose that as cars arrive at the tollbooth, that they must be arranged in license plate order. First, it must be decided where they should be in the line. Then which other cars have to move, then how to move them, etc. That's lot of work, and will take a lot of time.
In the database setting it is also convenient to have new records get placed "at the end". If your table uses an ascending "AutoNumber" field as the primary key, new records will get placed at the end. Furthermore, the database won't even have to ask the quesion "where does this record belong?", it just puts it at the end. No previously inserted records need to be relocated.
And the toll booth analogy goes further. Cars go through the tollbooth, leaving the line. The "oldest" cars go through the tollbooth first. Suppose we were making cars line up by licence plate number. Then we said, "The car that has been here longest gets to go through the tollbooth first!" We'd have to figure out which one was oldest, then make other cars move, etc. That's crazy!
In a database, records can leave the transactional table as they are archived. These are typically, the "oldest" records. If you are using an AutoNumber primary key, all the records that need to get archived are together, at the beginning of the list.
Finally, using an AutoNumber tends to keep "hot" records in RAM, rather on disk, improving performance. In a transactional database the newest records tend to accessed more often. The older records tend to get accessed less often. As an example, suppose I place an order at an e-tailer. I'm much more likely to change my mind and modify the order within 10 minutes of placing the order than after a week.
Using an AutoNumber means that the newest records are all together, on the same data pages. The database engine is smart enough to keep frequently accessed data pages in RAM to avoid the expensive disk access charges. As an example, I've noticed that my dentist office pulls all the files for the current day, and keeps them at the check in desk. So they are convenient and quick to access. Yesterday's files are filed in the back, because we won't need them for awhile.
To summarize, Autonumber fields are helpful because:
they get inserted in order, without affecting other records
they get archived in order, without affecting other records
they help the database keep current records in RAM
Of course, there are exceptions to every rule. The advice above would apply to transactional systems with a variety of operations (INSERT, UPDATE, DELETE, SELECT).
Why make up a primary key, when there's a perfectly good primary key already available?
Great question. I love it. It shows thought and a recognition that there might be a consequence to the choice.
For a field to be a primary key, all we need is to make sure that every record has a value in that field, and that there are no duplicates, i.e., UNIQUE and NOT NULL.
There is no requirement that a primary key field
have any meaning in the real world
be mnemonic (remind you of something)
be printed out on anything
be derivable from other information
be available or of interest to anyone other than a database professional
Each of the items above is helpful and useful, but is not strictly needed from a primary key. In fact, some of these helpful characteristics can actually cause practical problems in fields that are used as a primary key.
(There are significant performance implications for this choice, but I'm going to speak to more practical reasons why a completely made-up primary key has benefits.)
Consider the following table called Person, with the following fields:
firstName
lastName
SSN
username
For a field to be usable as a primary key, it must minimally be NOT NULL, and UNIQUE. This table, potentially, has fields that we might consider enforcing these requirements on:
SSN
username
Let's look first at SSN. This would normally be CHAR(9). As the database designer, here are some things to consider about SSN:
it means something in other systems
it requires that the Person exist in another system
it's not a strictly necessary attribute of a Person entity, i.e., People exist that don't have SSNs
there's no reasonable validity check on SSN values
Let's assume that our database designer has decided to use SSN as the primary key for the Person table. Somehow, the value '112456678' is listed as John Smith's SSN. How do we know that this is really John Smith's SSN? Suppose it isn't? John contacts us and states that the last character that is '8' should be '3'. So we make the change, and realize that all foreign key references must also be updated. OK, we cascade updates (manually or automatically) through all the foreign key references. What about last month's data from last month that is in the data warehouse, and not "cascadable"? What about the archive data from last year that's not online?
It's not that it's impossible to make all those changes, it's just expensive. Or, we can make the economically sensible decision to not update all the cascading data. Effectively, in our system, John Smith will have become two completely different people, because his primary key will have changed.
SSN is a great example of what I call an External Key. It's a value that's useful in identifying a person in someone else's system; in this case, the IRS. We have to give it to the IRS when we want to reference a specific Person in their database. However, because it's really the IRS's data, we cannot know, definitively, that it is accurate.
If it was our data, we could definitively verify that it was correct. Or, we could just decide that it is correct. For example, supposer that we were the IRS, and SSN was our data, and we found that John Smith's last SSN character was '8', but he thought it should be '3'. As long as '112456678' was unique and not null, we could just leave it as is.
So how about using username as a primary key? Let's assume that username is a value that we have complete control over - that we have some method for determining it, and no other system uses it.
Since we have complete control over it, how should we do it? What makes a good username? It's common to use some scheme like 'SmithJ', i.e., last name, concatenated with the first letter of the first name. People like that, mainly because of certain characteristics:
it has some meaning in the real world - someone's lastname...
it's mnemonic - it helps us remember which person goes with the username
it would be helpful useful information to print out on things
it's derivable from other data
it's useful for people in the real world (not db professionals)
However, all the things that make it useful also make it problematic as a primary key. We still have the problems that SSN had:
it means something in other systems (in this case, the social system - we call people by their name)
there's no reasonable validity check on names
What happens when someone changes their name? All the same things that happen when we have a mistake in a SSN. We don't control names - people control their own names.
So let's create usernames that don't mean something in the real world! Then it would make a good Primary Key!
Then we lose all the good attributes of a username.
My default recommendation is to have a primary key that is for the database professionals, and has no meaning to anyone else. Something completely non-meaningful; a completely Synthetic Key, that means nothing to anyone else but a db professional.
Why?
Data Integrity. When a key changes, we lose Referential Integrity. If we cannot control a key value, we cannot guarantee data integrity.
Database professionals need dependable (unchanging) primary keys so that they can be used to relate data.
Of course, we also want primary keys that will help with performance, but that's for another post...
