Re: partiioning option not worth it?

Hi Keith,

I liked the thread and your replies.

I am a dba aware of cost effectiveness. And I agree with you that we are fighting windmills when it comes to the decision of "it can technically be done" vs. "it should be done because of cost-effectiveness".

I have another point for you.

We decided against partitioning exactly because of that calculation: we have 800 million rows which belong to five days of business. So each day causes 160 million rows in this table.

We decided not to store the date within the table and partition against the date column because this would have meant (a) buying partitioning option (hence buying enterprise edition) and (b) storing 800 million dates (which break down to five different values) which makes 800,000,000 times 6 bytes = 4.8 GB that had to be moved around. Instead we chose to code the day inside the table name - so this was not an issue any more.

Later we got a decent discount on enterprise edition. We bought the enterprise edition but we stayed with the existing solution because a lot of bugs showed up on 8.1.6.x related to partitioning.

Storing the partitioning data itself in a separate column may also be undesirable from a technical and performance point of view.

Regards,

Martin

Keith Boulton wrote:
>
> It has been brought to my attention that much of what I've said is based on
> theory not practice.
>
> On reflection, much of what I argue about partitioning is based on theory.
>
> My starting point however is that last week I saved my employer £14000 at a
> cost of £300. I also have many years experience in the industry which leads
> me to believe that the statement "that's not possible" is actually just "I
> don't know how to do that and I can't be bothered to find out" and "that's
> how is has to be done" means either "that's how everyone does it" or "I
> can't be bothered to think beyond what the salesman/rep/consultant said"
>
> Now I could easily be wrong. But frankly, my own career is full of instances
> of doing things "can't be done". Additionally, I had the (mis)fortune in my
> first job to work for an organisation where "Can I do it cheaper" mattered.
>
> On my recent posts, (with the exception of one/two posts that were merely
> abusive and one of which I aplogised for) I cannot find anything
> unjustifiable.
>
> I will (and have) admitted that there are circumstances where the
> partitioning option is cost-effective - I just believe that in most
> circumstances, it is not, if you have an appropriate design. The big
> advantage of partitioning is that it allows you to architect the client side
> of a (very) big system as though it were a small(er) system. I believe I
> have explained the defficiences with the alternative approach I've proposed,
> and those defficiencies are not dependent on the size of the database, but
> are dependent on the complexity of the database.
>
> I have made disparaging remarks about the general quality of DBA's but my
> experience includes:
> a DBA who deleted one of the database files and then couldn't restore it
> because "the backup sometimes tries to go over more than one tape".
> a DBA who, when I pointed it that having system/manager was poor security on
> a test database which had a database link including password to a dba
> account which was present on all production databases, asked how I'd found
> out the password (it's not present in dba_db_links but is in user_db_links
> this implies that you merely need to look at the view definition to see how
> to find the password).
> a DBA who said "we need to consider scalability" about a database that had
> perfectly adequate performance and was only going to shrink as offices
> closed - I said "actually, we need to consider shrinkability"
> I was once asked to produce a backup/recovery plan and procedure for a
> database server. I said that it had to fit in with the disaster recovery
> plan for the site and who should I speak to. The respone was "what?" and a
> look of incomprehension. When I told the story to a DBA, he proudly said
> that they took two tape backups everynight and pointed to the cupboard
> adjacent to the servers where the tapes were stored. I did ask him what
> would happen if a fire broke out.
> a DBA team who all took a coffee break at the same time.
> a DBA team who introduced a six hour outage every weekend for backup with
> telling the users, the developers, or the application support team
> a DBA team who (somehow) managed to lose tables or views when asked to
> update a test system with production system data and then managed to have a
> batch job in a test system feed into a production system.
> a DBA who defined RAID5 as the standard disk configuration who insisted on
> database level redo log mirrors (ie to files per group, if I remember the
> terminology correctly, which I may not).
> Many, many DBAs who do not understand the difference between "that will be
> slower" and "that will be too slow to be usable".
> a DBA who said "a SAN is better performing than local disk". A statement
> which he'd never tested in general never mind wrt the particular project. I
> have asked for, and not seen, any evidence that this is true. If the cost
> difference was slight, you argue "who cares?" but it is not. There is an
> argument for the administrative advanges for a SAN, but certainly the
> administrative savings quoted in HP documents I've seen are ludicrous. I'm
> more than happy to change this view, but not without evidence.
> DBAs who have said we *must* move to a (much more expensive) UNIX
> environment because "it is more reliable". Seemingly without understanding
> the concept of "yes, but is it worth it", and when they had made no effort
> to address the causes of the unrelibiality in the NT environment ie "we
> should move to a UNIX environment because I can be arsed to perform a root
> cause analysis there". And, by the way, this is the same team that
> introduced a six hour outage every weekend.
>
> There is also the issue of motivation - DBAs are often motivated by purely
> technical issues e.g. "it could be faster if...", "my administrative effort
> is less if..." rather than "will it be fast enough" or "my extra 1 day's
> effort will save 2 months of developer effort". This is just human nature
> and is not the subject of blame, just understanding.
>
> Am I wrong, therefore to be suspicious of DBAs in general? (I won't even
> mention what some sysadmins have done)
>
> Other questions I've asked (without actually requesting any extra work,
> Howard!) are "has anyone actually tested the impact of changing redo log
> size - have I missed anything") - on that, Howard J. Rogers was most helpful
> in explaining something that I'd missed and I have in mind a test scenario.
> The question goes to the core of "don't believe what anyone tells you - test
> it yourself" and "never trust that what you tested yesterday is still true
> today"
>
> I've asserted that it's cheaper to retain data than to archive it - frankly
> the cost of tuning a larger system is nothing compared to the cost of
> building a bullet-proof archive and restore system. I've not heard any
> costing to change that opinion.
>
> I have stated that an inability to recover a database in noarchivelog mode
> because the backups hadn't actually worked was the result of
> incompetence/unprofessionalism wrt the backup/restore procedure, not the
> cost of archivelog storage. While it may be that the problem would not have
> happened if logs were available online, there would still have been a worse
> problem if the entire machine had been lost. This reflects my belief that an
> untested backup is the same as no backup, and the backup/restore procedure
> you tested last month will probably not work this month. Is this a misguided
> belief?
>
> I have been wrong about the implications of checkpoint timeout, but I think
> had a reasonable point about the difference between loss of data by volume
> and loss of data by human memory span i.e. it may be more important to limit
> data loss by elapsed time rather than quantity of data changed.
>
> So my question, am I misguided or not?
>
> Have a laugh
>
> Keith
Received on Tue Mar 05 2002 - 07:03:16 CST