Particionamiento de Tablas

Este documento explica el particionamientode tablas para ASE 12.5

El particionamiento de tablas es un procedimiento mediante el cual se crean múltiples cadenas de páginas para una misma tabla, las cuales son accesadas por los worker processes, que son sub-tareas que pueden tener acceso simultáneo a estas cadenas de páginas y su proposito es mejorar el rendimiento.

El particionamiento de tablas tiene los siguientes objetivos

• Proporciona múltiples puntos de inserción para una misma tabla.
• Potencialmente mejora el rendimiento haciendo posible que las operaciones de lectura/escritura sean distribuidas sobre múltiples dispositivos de datos.
• Mejora el rendimiento cuando el servidor está configurado para realizar procesamiento paralelo de consultas, ya que puede haber un worker processes por partición en una búsqueda basada en particiones.
• Ejecuta múltiples tareas bcp sobre una tabla particionada, permitiendo controlar a qué particiones se hace la copia de un bcp.

Múltiples Puntos de Inserción

Por defecto, el servidor almacena los datos de una tabla en una cadena de páginas doblemente encadenadas, llamada simplemente una cadena de páginas. Si la tabla no tienen un índice clustered, el servidor realiza todas las inserciones sobre la última página de la cadena de páginas.

Cuando una transacción inserta una nueva fila en un tabla tipo heap (por ejemplo, una tabla que almacena información histórica), el servidor mantiene un bloqueo exclusivo de página sobre la última página, mientras inserta la fila. Si la última página se llena, el servidor asigna y bloquea nueva página.

A medida que múltiples transacciones intentan insertar datos en la tabla al mismo tiempo, problemas de rendimiento pueden ocurrir. Sólo una transacción a la vez puede obtener el bloqueoo sobre la última página, así que otros insert concurrentes deben esperar su turno de ejecución.

Al particionar una tabla se crean múltiples cadenas de páginas (particiones) para la tabla y en consecuencia, múltiples últimas páginas para operaciones de insert. Una tabla particionada tiene tantas cadenas de páginas y últimas páginas, como particiones.

Contención Reducida de las Operaciones de Lectura/Escritura

El particionar una tabla puede disminuir la contención de las operaciones de lectura/escritura cuando el servidor escribe información del caché al disco. Si el segmento de una tabla cubre varios discos, el servidor distribuye las particiones de la tabla a través de los fragmentos de dispositivo cuando usted crea las particiones.

Cuando el servidor baja páginas al disco y sus fragmentos está repartidos a través de distintos discos, las operaciones de lectura/escritura asignadas a diferentes discos físicos pueden ocurrir en paralelo.

Para mejorar el rendimiento de las operaciones de lectura/escritura sobre tablas particionadas, usted debe asegurarse que el segmento que contenga la tabla particionada esté compuesto de múltiples fragmentos repartidos sobre múltiples dispositivos físicos. Para consultas en paralelo, la distribución de particiones a través de múltiples discos físicos no solamente reduce la contención de las operaciones lectura/escritura, sino que también aumenta la velocidad del procesamiento y logra un mejor nivel de paralelismo.

Puntos Importantes sobre la Contención de Lectura/Escritura

Cuando utilice particionamiento de tablas para balancear las operaciones de lectura/escritura, usted debe estar alerta del riesgo que se corre de empeorar el balance, al tratar de mejorarlo. Los siguientes escenarios pueden evitar que usted obtenga los beneficios deseados del balanceo de operaciones:

• Si usted parte una tabla frecuentemente actualizada y su índice clustered a través de dispositivos físicos, el balance de la asignación de disco puede cambiar en el tiempo, a no ser que usted borre y vuelva a crear la tabla.
• Tablas e índices muy utilizados son ubicados en un solo dispositivo físico en un área de espacio predeterminada.
• Sus fragmentos difieren en tamaño.
• Otros objetos utilizan fragmentos a los que les son asignadas particiones.
• Una tarea bcp inserta muchas filas en una sola transacción, sin la opción batch. Dado que una partición es asignada mientras dure la transacción bcp, una gran cantidad de datos pueden ir a una partición en particular, llenando así el fragmento al cual fue asignada la partición.

{mospagebreak}

Tablas que pueden ser particionadas

• Tablas del sistema.
• Tablas de trabajo. ASE puede particionar tablas de trabajo, pero usted no puede hacerlo directamente.

Columnas Tipo Text e Image

Es popsible parrticionar tablas que utilicen tipos de datos text e image. Sin embargo las columnas de estos tipos no son particionadas, ellas permanecen en una cadena única de páginas.

Que tablas particionar

A partir de la versión 11.5, el particionamiento de tablas le permite correr consultas en paralelo sobre tablas tipo heap y tablas con índices tipo clustered. Por ejemplo, se pueden particionar tablas heap que contengan grandes cantidades de inserciones concurrentes. Un tabla heap es una sin índices clustered, aunque puede tener un índice nonclustered, tal como:

• Tablas a las que se les agrega información "al final".
• Tablas que proporcionan información histórica o una lista de eventos auditados.
• Una nueva tabla a la que se cargan datos con bcp in.Para versiones anteriores a la 11.5, si usted desea crear una índice clustered después de cargar los datos, primero debe "desparticionar" la tabla.

Relacion de asiganción de particiones con las transacciones de insert

A un usuario se le asigna una partición por la duración de una transacción. La asignación de particiones se reinicia con el primer insert de una nueva transacción. El usuario mantiene un bloqueo, y en consecuencia la partición, hasta el final de la transacción.

Por esta razón, si usted está insertando una gran cantidad de datos, usted debe agruparlos en tareas separadas, cada una en su propia transacción.

Comandos no permitidos sobre una tabla particionada

Una vez usted ha particionado una tabla, no puede utilizar los siguientes comandos Transact-SQL sobre la tabla, hasta que ésta sea "desparticionada":

• sp_placeobject
• truncate table
• alter table table_name partition n

¿Pueden dos tareas ser asignadas a la misma particion?

Si. El servidor asigna particiones de manera aleatoria. Esto significa que siempre existe una posibilidad de que dos usuarios utilicen la misma partición al intentar insertar -- uno bloqueará temporalmente al otro.

Entre más particiones tenga una tabla, menor es la posibilidad de que varios usuarios intenten escribir a la misma partición a la vez.

Creación de una tabla particionada sobre varios dispositivos

La creación de una tabla particionada que cubra múltiples dispositivos es un procedimiento que involucra varios pasos.

En este ejemplo, asumimos los siguiente:

• Queremos crear un nuevo segmento, en vez de utilizar el segmento default.
• Queremos repartir la tabla particionada a través de tres dispositivos, data_dev1, data_dev2 y data_dev3.

1.Definir un segmento:

1> sp_addsegment newsegment, my_database, data_dev1
2> go

Extender el segmento sobre los tres dispositivos:

1> sp_extendsegment newsegment, my_database, data_dev2
2> go

1> sp_extendsegment newsegment, my_database, data_dev3
2> go

Crear la tabla sobre el segmento:

1> create table my_table

2> (names varchar(80) not null)

3> on newsegment

4> go

Particionar la tabla:

1> alter table my_table partition 6

2> go

PaginaDespués de haber completado estos pasos, usted tendrá 2 particiones en cada dispositivo. recuerde: un menor número de particiones implica menos recursos (y menos worker processes).

Uso del bcp en las tablas particionadas

Al utilizar el bcp paralelo para cargar datos en una tabla particionada es posible ubicar datos en una partición específica.

Usted puede tomar ventaja del particionamiento de tablas con el bcp paralelo siguiendo las siguientes recomendaciones:

1. Antes de una carga por bcp realice lo siguiente
   • Ubique la tabla en el segmento y particione la tabla
   • Borre todos los índices y triggers. Esto asegura un copiado rápido, con mínimo registro en el log
   • Active la opción trunc log on chkpt, si usted quiere evitar que el log se llene durante copiados grandes
2. La ejecución de tareas simultáneas aumenta las tasas de transferencia. Parta el archivo de datos en múltiples archivos y cargue simultáneamente estos archivos con tareas bcp separadas, sobre una tabla:
   •  Especifique el número de partición para cada tarea bcp; por ejemplo: bcp database_name.owner.table_name:slice_number in datafile ...

Nota:El uso de la opción -b ayuda a evitar que se agoten los bloqueos al correr varias sesiones bcp concurrentes. Otra alternativa es aumentar el parámetro de configuración number of locks.

Procesamiento paralelo de consultas

En ASE 11.5 (y versiones posteriores), el particionamiento es el fundamento del procesamiento paralelo de consultas. El particionamiento de datos se extiende para dar soporte a particiones sobre tablas con índices clustered. El particionamiento agrega un grado de paralelismo si su sistema está configurado para procesamiento paralelo de consultas, lo que le puede brindar mejoras notables de rendimiento.

Prácticas recomendadas para el particionamiento de tablas con paralelismo

Para un óptimo rendimiento en el procesamiento paralelo de consultas:

Utilice Espacio Dedicado

Ubique las particiones en segmentos dedicados sobre fragmentos de disco separados, sobre discos físicos con controladoras separadas.

{mospagebreak}

Asegure la Buena Distribución de Datos

Los pasos que aseguran distribución más equitativa de datos son:

1. Borre los índices de la tabla particionada.
2. Utilice bcp paralelo para copiar datos sobre particiones específicas. Si quiere crear un índice clustered al final, copie las filas en el orden del índice.
3. Cree el índice clustered utilizando la nueva cláusula with sorted_data del comando create clustered index de ASE 11.5, y versiones posteriores.

Una forma alterna de hacerlo:

1. Particione la tabla.
2. Defina number of worker processes y max parallel degree con un valor al menos tan grande como el número de particiones de la tabla más 1.
3. Ejecute sp_dboption base_de_datos, "select into/bulkcopy/pllsort", true y ejecute un checkpoint en la base de datos.

Una vez usted complete estos pasos preliminares, utilice el comando create clustered index.

Controle las Tablas Particionadas

Controle:

• La exactitud de las estadísticas. El optimizador utiliza estadísticas para seleccionar el plan óptimo de ejecución de consultas. Utilice regularmente el comando update partition statistics para asegurar el conteo exacto de páginas en la página de control de particiones de la tabla; utilice update all statistics para actualizar la página de distribución de cada índice y la página de control para cada partición.
• El balanceo de particiones. Si el número de páginas de datos en las particiones de una tabla es significativamente diferente, el rendimiento del procesamiento paralelo de consultas puede verse afectado. Usted puede ejecutar sp_help tabla para visualizar información sobre una tabla particionada, verificando que el balanceo de las particiones sea correcto y el ratio no sea mayor a 1.8

Indices clustered sobre tablas particionadas

Para crear un índice clustered sobre una tabla particionada, ASE debe estar configurado para paralelismo, de la siguiente manera:

• number of worker processes debe ser mayor o igual al número de particiones más 1.
• max parallel degree y parallel_degree, si es utilizado, debe ser mayor o igual al número de particiones.

Para detalles, vea el manual System Administration Guide.

Adicionalmente, number of sort buffers debe ser igual o menor al 90 por ciento del número de buffers en el banco de buffers de 2K, antes del marcador de lavado. Para detalles vea el manual Performance y Tuning Guide.

Crear un índice clustered sobre una tabla particionada con el comando create clustered index para redistribuir equitativamente los datos de la tabla sobre las particiones. Dependiendo de los rangos de la llave del índice, ASE puede distribuir las filas equitativamente en cada partición.

Usted sólo puede crear un índice clustered sobre una tabla particionada, en paralelo. La creación de índices en paralelos es una tarea de registro mínimo en el log, con éstas limitantes:

• Antes de crear un índice clustered, usted debe activar la opción pllsort de la base de datos, utilizando sp_dboption. De otra manera, la tarea falla con el error 1565, que le recuerda activar la opción pllsort.
• Después de la creación paralela del índice, para poder realizar un dump tran de la base de datos, primero se debe hace un dump database de la base de datos.
• Usted no puede utilizar create clustered index en la base de datos master.

Nota:
La creación de un índice clustered requiere al rededor de 120% del espacio que ocupa la tabla, ya que la tarea copia páginas de datos. Para más información sobre requerimientos de espacio para tablas particionadas, vea el manual Performance y Tuning Guide.

Determinando si un create index es ejecutado en paralelo

Es posible desplegar el entorno de ejecución a ser utilizado para la creación de un índice utilizando el comando set sort_resources on. Esto es muy recomendado en las etapas preliminares del proceso de diseño físico de la base de datos, para asegurar la creación eficiente de índices.

Si el comando create index es ejecutado en paralelo, la opción sort_resources muestra los dos tipos de worker processes, los dispositivos y los elementos de distribución.

La Creación del Índice

Antes de la creación del índice, desactive la opción sort_resources. A no ser que la tabla esté vacía, create index no funciona si sort_resources está activo. Para detalles del uso de sort_resources, vea el manual Performance y Tuning Guide.

Si el número de worker processes varía entre lo reportado por sort_resources y la ejecución del create index, puede que los dos entornos de ejecución no coincidan. Esto se puede prevenir ejecutando los dos comandos más cerca uno del otro o aumentando number of worker processes y max parallel degree.

Como se puede desbalancear una tabla

Los datos de una tabla particionada pueden tornarse no balanceados si:

• La tabla fue recientemente particionada y todas las páginas de datos están en la primera partición.
• Un número grande de filas es insertado/borrado de una partición.
• Un número grande de filas son actualizadas en una partición.
• Un número grande de filas es copiado sobre una o más particiones
• Las inserciones son frecuentemente canceladas.

Para balancear un atabla particionada, intente:

• Copiar todos los datos a un archivo, truncar la tabla y luego utilizar bcp paralelo para copiar cantidades iguales de datos sobre cada partición.
• Alternativamente, construya un índice clustered sobre la tabla. Si esto no balancea la tabla, utilice el método anterior.

Para verificar que el intento de re-balancear particiones tuvo éxito, ejecute sp_help tabla.

Nota:
Para ayuda en cómo manipular problemas de balanceo cuando los dispositivos están llenos, vea el manual Performance and Tuning Guide.

Comandos para verificar la distribucion de datos sobre tablas particionadas

Usted puede utilizar los siguientes procedimientos para evaluar cómo están distribuidos los datos:

• sp_help sobre una tabla particionada proporciona datos detallados sobre la tabla, tales como columnas, tipos de datos, índices y llaves foráneas, así como datos de particiones específicas.
• sp_helppartition permite visualizar el número de páginas en las particiones de una tabla. Este comando también le da la relación entre máximo y promedio de páginas, lo que indica si una consulta puede ser ejecutada en paralelo. La relación debe ser menor a 2 para que el optimizador seleccione un plan paralelo.
• sp_helpsegment lista las páginas libres para cada dispositivo de un segmento. El número de páginas de datos para una tabla particionada en los dispositivos se puede volver no balanceada, aunque el número de páginas de datos en una partición esté balanceado. Este "desbalanceo" ocurre cuando una partición se queda sin espacio en el dispositivo y obtiene espacio de otro dispositivo físico.