Re: At what point does a big table start becoming too big?

On Thu, Aug 23, 2012 at 6:37 AM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> In response to "Martin French" <Martin(dot)French(at)romaxtech(dot)com>:
>> >
>> > I have a table with 40 million rows and haven't had any performance
>> > issues yet.
>> >
>> > Are there any rules of thumb as to when a table starts getting too big?
>> >
>> > For example, maybe if the index size is 6x the amount of ram, if the
>> > table is 10% of total disk space, etc?
>>
>> My rule here is that a table is too big when performance starts degrading beyond an acceptable level.
>
> The challenge there is that if you wait until performance degrades
> beyond an acceptable level, you've allowed yourself to get into a
> situation where clients are upset and frustrated, and fixing the
> problem is difficult because there's so much data to manipulate to
> rearrange things.

Yes, I agree with that.

> And the advice I have along those lines is to establish now what
> constitutes unacceptable performance, and put some sort of monitoring
> and tracking in place to know what your performance degradation looks
> like and predict when you'll have to react. For example, a MRTG
> graph that runs an experimental query once a day during off hours and
> graphs the time it takes vs. the # of rows in the table will prove
> a valuable tool that can sometimes predict exactly when you'll have
> to change things before it becomes a problem.

This seems inconsistent with your previous advice. By the time your
experimental query shows a problem, you no longer have any maintenance
windows left large enough to fix it. Unless your experimental query
was a reindex or something non-production like that, in which case
running it on a production server, even off-hours, doesn't seem like a
good idea.

Cheers,

Jeff