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

Hi,

On 23 August 2012 23:37, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
>
> 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. Other tricks work as
> well, such as having the application send an email any time a process
> takes more than 50% of the allowable maximum time.
>

I like to use APDEX (http://apdex.org/specs.html). You can change your
database to all time for all statements and then calculate APDEX score
based on last N log entries (> 10). APDEX score is weighted score
based on number of datapoints within three zones:
0...T Satisfied Zone
T..F (=4*T) Tolerating Zone
4T... Frustrated Zone

you can choose T (or F; then T = F/4) i.e. under normal circumstances
all queries should finish under 20ms (T = 20ms, F = 4T = 80ms). Apdex
score is:

score = (Satisfied count + Tolerating count / 2) / Total samples

You can get this number, for example, every minute and plot it using
Ganglia / MRTG / ...

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)