The Old Joel on Software Forum: Part 3 (of 5) - How big is this boards database?

How big is this boards database?

I'm still wondering how big the database for this message board has gotten, so I decided to ... contemplate heh (plus I'm bored atm).

Table Structure:

ixPost - 4 bytes
ixParent - 4 bytes
iReplies - 4 bytes
fDeleted - 4 bytes ' could be 1 bit/1byte or 2,4,8 bytes
dtDate - 4 bytes ' could also be 8 bytes
sIP - 15 bytes
sTitle - 50 bytes
sAuthor - 128 bytes
sEmail - 128 bytes
mMessage - 2000 bytes

Approx Total (i would guess on the low side) - 2341 bytes

According to:

http://www.usabilitymustdie.com/jos/WW_MonthlyStats.html

There are 78514 comments as of Monday (Updated every monday i think).

Size = 78514 * 2341 = 183801274 / 1048576 = 175.2865543

So the database is approximately 175MB.

Of course, this is probably not accurate either because something tells me that there are one or two more fields in the database that we don't know about and I don't know if this includes the deleted posts (some of which may actually be deleted)... but I would guess an extra 15 bytes per post at a minimum.

At the current monthly average of ~15 comments per item and 400 new items:

400 * 15 * 2341 = 14046000 / 1048576 = ~13 MB added per month

To fill 20 GB on a hard drive:

(20,000 MB - 175 MB) / 13MB per month = 1525 months / 12 months per year = ~127 years.

I'll be dead then... Well back to work.

Dave B.
Wednesday, November 19, 2003

It's 130 MB now.

Joel Spolsky
Wednesday, November 19, 2003

I'd bet ya that's because the message field is variable length and not always 2000 bytes (or what have you).

Dave B.
Wednesday, November 19, 2003

Yes, and I haven't seen Albert Kallal (sp?) post lately.

That alone could skew your estimate 45 meg. ;)

Dignified
Wednesday, November 19, 2003

Dave B.,

Truely bodacious back of the napkin estimate. And you got it well within the fabled one order of magnitude for great estimaters. I'm impressed.

Dennis Atkins
Wednesday, November 19, 2003

Dave:

Interesting estimate, but you forgot to include the fact that the number of posts per month seems to be increasing...actually taking a closer look at the stats it seems that we have hit a plateau.

Never mind.

BB

Billy Boy
Wednesday, November 19, 2003

>> Yes, and I haven't seen Albert Kallal (sp?) post lately.

LOL!!

Tim Lara
Wednesday, November 19, 2003

When you first wrote it you said it was an Access database, has this been migrated to SQL Server? When/why?

Ben Richardson
Wednesday, November 19, 2003

Dave B. should interview at Microsoft.

--
Wednesday, November 19, 2003

The Board

Could we have some date info beside the message listings?

And/or could we have them grouped by new messages, then most recent replies? As it stands it's hard to see which threads are being replied to and some of us nosey type like to read lots of them, so trawling through is a pain.

I'm sure some of us would write the board code if you are too busy Joel.

Marx
Saturday, November 22, 2003