[dba-SQLServer] VLDBs, the saga - Ramblings only

Shamil Salakhetdinov shamil at users.mns.ru
Mon Aug 30 00:41:49 CDT 2004


John,

May I ask you did you make any calculations in advance to see what to
expect with your data loading? (Almost nobody do I know - But I did
start to do that for some time now - it did happen to me that "brute
force" approach doesn't work well sometimes :) )

Why not make it normalized as a first step of your data loading
adventure?

I mean:

- you have 65 million records with 600 fields each and let's assume that
each field is 20 bytes (not Unicode).
Then you get 65millions*600*20 = 780GB (even if the average size of a
record is less than that - it for sure(?) should be more than 4KB and
therefore you get ONE record loaded on ONE page - MS SQL records can't
be longer than 8KB - this is ~520GB without indexes...)

If as the first step you go through all your source data and get them
normalized you get something like:

65millions*600*4 = 156GB - the latter looks manageable even with
ordinary modern IDE drive even connected through USB2 - that's a cheap
and quick enough solution for starters (I assume that some references
from normalized table will be 4 bytes long, some two bytes long, others
1 byte long, some like First- , Middle- and Last- name will be left
intact - so all that will probably give 4 bytes long field in average.
And four bytes are enough to get referenced even 65,000,000 data
dictionary/reference (65,000,000 =  03 DF D2 40 hexadecimal).

So as the first step - get all your source data unzipped on one HDD
(160MB)?

Second step - analyze the unzipped data and find what is the best way to
get it normalized (here is a simple utility reading source data, making
hash code of every field and calculating quantities of different hash
codes for every field should be not a bad idea - such a utility should
be very quick and reliable solution to get good approximation where you
can get with your huge volume of the source data, especially if you
write in on C++/C#/VB.NET - I'm getting in  this field now - so I can
help here just for fun to share this your challenge but spare time is
problem here so this help can be not as quick as it maybe needed for you
now...

Third step - make good (semi-) normalized data model (don't forget a
clustered primary key - Identity - you like them I know :)), calculate
well what size it will get when it will be implemented in a MS SQL
database...

Fourth step - load normalized data, maybe in several steps....
....
N-th step get all you data loaded and manageable - here is the point
where you can get it back denormalized if you will need that (I don't
think it will be needed/possible with your/your customer resources and
the OLAP tasks should work well on (semi-)normalized database metioned
above), maybe as a set of federated databases, linked databases,
partitioned views ...

I'd also advise you to read now  carefully the "SQL Server Architecture"
chapter from BOL...

Of course it easy to advice - and it's not that easy to go through the
challenge you have...

I'm not that often here this days but I'm every working day online on MS
Messenger (~ 8:00 - 22:00 (MT+3) Shamil at Work) - so you can get me there
if you'll need some of my help...

HTH &  I hope you'll get it up&running soon,
Shamil

----- Original Message ----- 
From: "John W. Colby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>; <dba-sqlserver at databaseadvisors.com>
Sent: Saturday, August 28, 2004 3:56 PM
Subject: [dba-SQLServer] VLDBs, the saga - Ramblings only


> I have been working on getting a rather large database into SQL
Server.
> This thing is ~65 million names plus demographics info and will be
used for
> bulk mailing analysis.  I have been struggling for weeks to make this
> happen.  Having no experience with a database this big, I had no idea
how
> big the final db would be.  I tried to get it in to a 160g drive but
it
> rapidly became obvious that wouldn't hold it.  I then purchased two
200g
> drives and used Raid0 to make a big 400 g drive.  I thought I turned
on
> compression but after getting well into the extraction process I
discovered
> this wasn't the case.  I then started trying to figure out how to get
the
> drive compressed.
>
> Long story short, a NTFS drive can be compressed, even a raid array
such as
> this, however...  There is a control that allows you to select the
sector
> size.  I had selected the "compress" check box but then selected a
sector
> size of 64K.  As I started investigating why the drive wasn't
compressed it
> turns out that only sector sizes of 512 to 4K bytes allow compression.
> Anything larger causes the "compress drive" check box to gray out and
the
> drive ends up uncompressed.
>
> By this time I had already spent several days extracting zipped files
of
> data and BCPing them into SQL Server so I had a MDF file of over 300gb
and
> no place to put it!
>
> Sigh.
>
> Out of desperation I decided to try zipping the database file.  I
started it
> PK zipping last night onto an empty 160g partition.  This morning I
had a
> 10gb zipped file that supposedly contains the MDF file!
>
> I then deleted the partition on the 400gb Raid array and started
playing
> with the compression / block size which is when I discovered the >4K
sector
> size gotcha.  I set the sector size to 4K and quick formatted, then
started
> unzipping the MDF file to the (compressed) 400gb raid array.
>
> We shall see.  The unzip is not finished, in fact has several hours to
go
> yet.  If this works I will celebrate.
>
> This whole project has been a challenge.  It looks like the database
will be
> around 600g for JUST the data, never mind any indexes.  I simply don't
have
> the money to build a raid 5 array to up the uncompressed drive size.
Even
> if I did, IDE drives larger than 250gb are expensive and AFAICT only
> available in 5200 RPM.  Plus the overhead of Raid5 is "One Drive"
which
> means I'd need (4) 300g drives to build a 900g usable space raid5
array.
> Raid1 (which I am currently using) tops out at 600g using (2) 300g
drives
> (uncompressed).  So far my (2) drive Raid1 array using 200g drives has
cost
> me $240 plus a controller I already had.  A Raid5 solution using 300g
drives
> would cost about $1200 just for the new controller and 4 drives!
>
> With any luck, given the massive compression PKZip managed to attain,
I will
> be able to shoehorn the 600g.
>
> Update 8-(
>
> As I write this I just got a "delayed write failed" message from
Windows
> saying it lost data trying to write to the disk.  I have tried to turn
off
> write caching but can't seem to find the magic button to cause Windows
to
> quit using "Delayed write".
>
> BIG sigh!
>
> If I can't get the db back out of the zip file I will be facing a
weekend of
> "starting from scratch" on getting the data out of the raw text files
and
> back in to SQL Server!
>
> And I thought this would be fairly easy.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>




More information about the dba-SQLServer mailing list