none
Partizionamento tabelle e scalabilità RRS feed

  • Domanda

  • Buongiorno a tutti!

    Ho un database con alcune tabelle che crescono molto durante le attività quotidiane e che poi diventano per lo più dati storici.

    Vorrei applicare a queste tabelle uno schema di partizionamento basato ad esempio sulla data di creazione del record e partizionare per anno.

    Non ho esperienza di partizionamento e vorrei per favore capire cosa succede man mano che cresce la tabella. In parole semplici, devo prevedere già al momento della creazione della funzione di partizione tutti gli anni a venire o posso aggiungere successivamente?

    Grazie e buona giornata!


    MCAD - MCSD.NET - MCTS CompTIA Security+

    mercoledì 14 gennaio 2015 08:11

Risposte


  • La tabella, nelle realtà in cui operiamo, cresce di circa 2 milioni di record all'anno (ogni record pesa circa 1,5KB). Abbiamo quindi tabelle in alcuni casi di 7-8 milioni di record. Possono essere considerate tabelle grandi o sono ancora gestibili?

    1.5KB * 8MB sono circa 12 Giga.. direi che è decisamente corposa. "Gestibile" è un termine relativo, nel senso che dipende anche dalla macchina su cui risiedono questi dati. Non è una tabella piccola, di certo. E di certo un piano di indici è necessario.

    Su questa tabelle ci sono ovviamente gli indici ma vorrei migliorare ulteriormente i tempi di risposta. Ho pensato quindi di partizionare la tabella in modo che le operazioni siano su tabelle più piccole (la tabella ha un indice cluster).

    Ma per le ricerche che fai su quella tabella, è sufficiente l'indice cluster? Hai fatto un'analisi controllando quanto l'istanza ti chiede per quel database? Una query che ti torni se mancano indici ad esempio.. Il partizionamento, se sai che le query non sono cross partizione, dovrebbe ridurti (ma dipende dal tipo di interrogazione che fai) di sicuro le IO, andando a leggere molti meno record. Ma se hai bisogno di tutti i dati sempre, quella volta che vai a leggere i dati su tutte le partizioni, il tempo potrebbe pure peggiorare. La partizione è efficacissima se interroghi "da sola". E poi serve l'enterprise edition (non so se tu la hai). Tutte queste cose dipendono tantissimo dal tipo di accesso che fai ai dati. Per quello ti parlavo della possibilità di isolarli. Se ti serve sempre tutto, io vedo solo un piano di indici potente (anche filtrati) dedicato alle singole chiamate, per dare il massimo della velocità in lettura.

    Come posso verificare se i tempi di risposta sono ottimali rispetto al dimensionamento del database e della dimensione delle tabelle?

    Il tempo di risposta non è un'entità assoluta. Dipende dai tuoi requisiti e da quelli del cliente che usa il tool. Quanto è "troppo" per le Vostre considerazioni? Di certo ci sono tempi sotto i quali non è possibile andare e sono quelli fisici dati dal sottosistema disco o dalla velocità della memoria. Al di là dell'hardware, dipende come vengono scritte le query per tornare i dati, dai piani di esecuzione, e da tante altre cose (tra cui anche le impostazioni di configurazione di istanza e database, non c'è veramente spazio qui per parlarne, servirebbero almeno un paio di giorni osservando la tua realtà). Diciamo che c'è il modo per capire quanto ad oggi stanno impiegando le tue query e quanto questo possa impattare sull'applicazione che le chiama. Poi devi determinare tu quanto ti puoi permettere.


    Alessandro Alpi - SQL Server MVP

    giovedì 15 gennaio 2015 14:12
    Moderatore

Tutte le risposte

  • Ciao,

    considera che una volta che applichi uno schema di partizionamento definisci una funzione che "decide", all'atto dell'inserimento o della modifica di un record, dove andare a scriverlo. La funzione si basa su una chiave, sulla quale definisci i boundary, quindi il range su cui la partizione si basa, nel tuo caso, l'anno. Ogni partizione scrive su un filegroup dedicato, che puoi quindi andare a spostare dove meglio credi. Chiaro è che se non definisci range ben precisi, ad esempio se imponi che la partizione storica è DOPO l'anno X, la soluzione tenderà a popolare la partizione in maniera progressiva, andando a rendere non scalabile l'accesso (proprio come succede per una tabella non partizionata).

    Potresti pensare, sempre che tu possa farlo, ad un livello "online" su cui tieni sempre il dato che ti serve in realtime e poi ad un livello di storico diviso per anni (una partizione per anno). Il primo livello potrebbe essere anche un set di database (o uno solo, dipende dalla tua situazione) dedicati per l'online. Il secondo livello sarebbe un set di database con tabelle partizionate solo per lo storico. In generale un layer snello per l'online è una soluzione che mi piace tantissimo.

    Oltre un certo numero di partizioni/anni (che puoi aggiungere anche dopo, andando ad aggiungere i range e i constraint), c'è da capire dove andare a mettere i record. Ma se non ti servono più, puoi anche tenerli salvati in un layer di backup (anche dati esportati e zippati) che richiameresti solo se ci fossero richieste. Queste ultime le gestirai on demand e ovviamente i dati non saranno navigabili e disponibili in real time.

    Evolvendolo quello potrebbe diventare la base per il tuo data warehouse e il conseguente cubo.

    Ho cercato di essere generico, perchè è una risposta che meriterebbe tanto tempo.


    Alessandro Alpi SQL Server MVP


    mercoledì 14 gennaio 2015 11:42
    Moderatore
  • il link qui sotto dovrebbe rispondere alla tua domanda

    http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/


    Edoardo Benussi
    Microsoft MVP - Directory Services
    edo[at]mvps[dot]org

    mercoledì 14 gennaio 2015 11:42
    Moderatore
  • Buongiorno Alessandro,

    grazie per la esauriente risposta. Il database contiene i dati di un gestionale documentale per cui non posso facilmente spostare i documenti più "vecchi" in livelli diversi perchè per le ricerche devono sempre essere disponibili.

    La tabella, nelle realtà in cui operiamo, cresce di circa 2 milioni di record all'anno (ogni record pesa circa 1,5KB). Abbiamo quindi tabelle in alcuni casi di 7-8 milioni di record. Possono essere considerate tabelle grandi o sono ancora gestibili? Su questa tabelle ci sono ovviamente gli indici ma vorrei migliorare ulteriormente i tempi di risposta. Ho pensato quindi di partizionare la tabella in modo che le operazioni siano su tabelle più piccole (la tabella ha un indice cluster).

    Come posso verificare se i tempi di risposta sono ottimali rispetto al dimensionamento del database e della dimensione delle tabelle?

    Chiedo scusa se ho fatto un po' un "risotto" di domande ma sto cercando di ottenere le migliori prestazioni possibili. Mi sai dare qualche indicazione/tutorial?

    Grazie e buona giornata!


    MCAD - MCSD.NET - MCTS CompTIA Security+

    giovedì 15 gennaio 2015 10:46

  • La tabella, nelle realtà in cui operiamo, cresce di circa 2 milioni di record all'anno (ogni record pesa circa 1,5KB). Abbiamo quindi tabelle in alcuni casi di 7-8 milioni di record. Possono essere considerate tabelle grandi o sono ancora gestibili?

    1.5KB * 8MB sono circa 12 Giga.. direi che è decisamente corposa. "Gestibile" è un termine relativo, nel senso che dipende anche dalla macchina su cui risiedono questi dati. Non è una tabella piccola, di certo. E di certo un piano di indici è necessario.

    Su questa tabelle ci sono ovviamente gli indici ma vorrei migliorare ulteriormente i tempi di risposta. Ho pensato quindi di partizionare la tabella in modo che le operazioni siano su tabelle più piccole (la tabella ha un indice cluster).

    Ma per le ricerche che fai su quella tabella, è sufficiente l'indice cluster? Hai fatto un'analisi controllando quanto l'istanza ti chiede per quel database? Una query che ti torni se mancano indici ad esempio.. Il partizionamento, se sai che le query non sono cross partizione, dovrebbe ridurti (ma dipende dal tipo di interrogazione che fai) di sicuro le IO, andando a leggere molti meno record. Ma se hai bisogno di tutti i dati sempre, quella volta che vai a leggere i dati su tutte le partizioni, il tempo potrebbe pure peggiorare. La partizione è efficacissima se interroghi "da sola". E poi serve l'enterprise edition (non so se tu la hai). Tutte queste cose dipendono tantissimo dal tipo di accesso che fai ai dati. Per quello ti parlavo della possibilità di isolarli. Se ti serve sempre tutto, io vedo solo un piano di indici potente (anche filtrati) dedicato alle singole chiamate, per dare il massimo della velocità in lettura.

    Come posso verificare se i tempi di risposta sono ottimali rispetto al dimensionamento del database e della dimensione delle tabelle?

    Il tempo di risposta non è un'entità assoluta. Dipende dai tuoi requisiti e da quelli del cliente che usa il tool. Quanto è "troppo" per le Vostre considerazioni? Di certo ci sono tempi sotto i quali non è possibile andare e sono quelli fisici dati dal sottosistema disco o dalla velocità della memoria. Al di là dell'hardware, dipende come vengono scritte le query per tornare i dati, dai piani di esecuzione, e da tante altre cose (tra cui anche le impostazioni di configurazione di istanza e database, non c'è veramente spazio qui per parlarne, servirebbero almeno un paio di giorni osservando la tua realtà). Diciamo che c'è il modo per capire quanto ad oggi stanno impiegando le tue query e quanto questo possa impattare sull'applicazione che le chiama. Poi devi determinare tu quanto ti puoi permettere.


    Alessandro Alpi - SQL Server MVP

    giovedì 15 gennaio 2015 14:12
    Moderatore