[Cialug] SDD drives (again)

[murraymckee at wellsfargo.com]

If the analyze description shows something odd, like scanning a table verses using an index, you might need to update the statistics of the DB.  And in some cases you might have to manually override the statistics that are automatically collected to 'encourage' the optimizer to use an index.

I was on a project creating a new system and my part was the first part of the conversion.  The 'empty' DB was tuned and statistics were collected, but since there were few to no records in most of the tables the DBMS decided a full table scan was the fastest way to find the needed data.  When the entire table could be read in 1 block, that was true, but as I converted data into the DB that quickly became not true.  So I had to alter "large" numbers into the row counts in some of the tables so the DBMS optimizer would be "encouraged" to use an index to access that table.  

There are indexes and then there are indexes.  I also discovered that several indexes were slowing the conversion down significantly, and I didn't need them.  I ended up dropping the indexes for the conversion and recreating them afterwards.  That was faster than maintaining them through the conversion.  If all you're doing is reading data, then this is probably not a problem, but if you're doing lots of updates it is something else to look into.

The result of all of the optimization efforts was that the conversion time dropped from 78 hours in the beginning to just under 3, and at the end when the run time was only 3 hours I was converting at least 40 times more data than when I had the 78 hour run.

Murray R. McKee

Operating Systems Analyst
Mainframe/Midrange Services

Wells Fargo Compute Platform Services  |  800 Walnut Street  |  Des Moines, IA 50309-3605
MAC N0001-037
Tel 515-557-6127 |  Cell 515-343-6630 |  Fax 515-557-6046 | Text pager: 5153436630 at vtext.com 

MurrayMcKee at WellsFargo.com

-----Original Message-----
From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On Behalf Of Matthew Nuzum
Sent: Wednesday, February 29, 2012 6:51 PM
To: Central Iowa Linux Users Group
Subject: Re: [Cialug] SDD drives (again)

On Wed, Feb 29, 2012 at 6:14 PM, Dave Hala Jr <dave at 58ghz.net> wrote:

> Its the seek time. Personally, I think its easier to manage the VM if
> everything is contained (for lack of a better word) in one container.
>

Writes or Reads?

Now days the answer is often SSD but there are two other options to keep on
your radar.

If the data is largely reads, can you fit the necessary tables into RAM? If
so, then there are a few ways to accomplish this, one is to warm up the
database by selecting every record before you do your reporting (and
getting it into the OS's disk cache) or by using whatever trick your
database supports to mirror the db into a ram disk.

If the data is a lot of writes then you can try adding two drives and doing
RAID 10. Considering the modest size requirements you need this may not be
cheaper than a 120G SSD.

Since you describe the problem as seek related I'd seriously look into the
first option, loading the data into RAM. If you have enough that will yield
far better performance than even SSDs. You may not need the whole db in
RAM. (Using postgresql as an example) you can add an "EXPLAIN ANALYZE"
before your slow select queries to see what is slowing things down. This
will tip you off to what data needs to be warmed up.

Other tricks I've used in the past were to partition the drive so that only
the outer edges of the disk were used. The data density at the outer edge
is higher so you have less head movement (and faster seek time). By density
I mean that more data travels under the read head with one rotation. Also,
in a RAID 10 setup, adjusting the block size of the stripes to be bigger
(say 256K).

Anyway, if you'd rather put your time into it than your money there are a
number of tricks available to you. Getting more of your data into RAM is
not too hard and for read-heavy loads there is nothing better.

-- 
Matthew Nuzum
newz2000 on freenode, skype, linkedin and twitter

♫ You're never fully dressed without a smile! ♫
_______________________________________________
Cialug mailing list
Cialug at cialug.org
http://cialug.org/mailman/listinfo/cialug