sql-server - rendimiento - optimizar sql server
El mejor almacén de datos para miles de millones de filas (10)
Necesito poder almacenar pequeños bits de datos (aproximadamente 50-75 bytes) para miles de millones de registros (~ 3 mil millones / mes durante un año).
El único requisito son las inserciones rápidas y las búsquedas rápidas para todos los registros con el mismo GUID y la capacidad de acceder al almacén de datos desde .net.
Soy un tipo de servidor SQL y creo que SQL Server puede hacer esto, pero con todo lo que se dice sobre BigTable, CouchDB y otras soluciones nosql, suena cada vez más como una alternativa a un RDBS tradicional que puede ser mejor debido a las optimizaciones para consultas distribuidas y escalado. Intenté con Casandra y las bibliotecas .net actualmente no compilan o están todas sujetas a cambios (junto con la propia Casandra).
He examinado muchas tiendas de datos nosql disponibles, pero no puedo encontrar una que satisfaga mis necesidades como una plataforma sólida lista para producción.
Si tuviera que almacenar 36 mil millones de registros pequeños y planos para que sean accesibles desde .net, ¿qué elegiría y por qué?
"Necesito poder almacenar pequeños bits de datos (aproximadamente 50-75 bytes) para miles de millones de registros (~ 3 mil millones / mes durante un año).
El único requisito son las inserciones rápidas y las búsquedas rápidas para todos los registros con el mismo GUID y la capacidad de acceder al almacén de datos desde .net ".
Puedo decir por experiencia que esto es posible en SQL Server, porque lo hice a principios de 2009 ... y sigue funcionando hasta el día de hoy y bastante rápido.
La tabla fue particionada en 256 particiones, tenga en cuenta que esta era la versión SQL de 2005 ... e hicimos exactamente lo que usted dice, y eso es almacenar fragmentos de información por GUID y recuperarlos por GUID rápidamente.
Cuando me fui, teníamos entre 2 y 3 mil millones de registros, y la recuperación de datos aún era bastante buena (1 a 2 segundos si se pasaba a través de UI, o menos si estaba en RDBMS) aunque la política de retención de datos estaba a punto de crearse una instancia.
Así que, para abreviar, tomé el octavo char (es decir, en algún lugar del middle-ish) de la cadena GUID y SHA1 lo modificó como hash y lo fundé como tiny int (0-255) y lo almacené en la partición apropiada y usé la misma función call cuando recibí los datos de vuelta.
sígueme si necesitas más información ...
Almacenando ~ 3.5TB de datos e insertando aproximadamente 1K / seg 24x7, y también consultando a una velocidad no especificada, es posible con SQL Server, pero hay más preguntas:
- ¿Qué requisito de disponibilidad tienes para esto? 99.999% de tiempo de actividad o es 95% suficiente?
- ¿Qué requisito de confiabilidad tienes? ¿Falta una inserción cuesta $ 1M?
- ¿Qué requisito de capacidad de recuperación tiene? Si pierdes un día de datos, ¿importa?
- ¿Qué requisito de coherencia tienes? ¿Es necesario garantizar que una escritura sea visible en la próxima lectura?
Si necesita todos estos requisitos que destaqué, la carga que propone va a costar millones en hardware y licencias en un sistema relacional, cualquier sistema, sin importar qué trucos intente (fragmentación, creación de particiones, etc.). Un sistema nosql, por definición, no cumpliría todos estos requisitos.
Entonces, obviamente, ya ha relajado algunos de estos requisitos. Existe una buena guía visual que compara las ofertas de nosql basadas en el paradigma ''escoger 2 de 3'' en la Guía Visual de los Sistemas NoSQL :
Después de la actualización del comentario OP
Con SQL Server esto sería una implementación directa:
- una sola tabla agrupada (GUID, hora) clave. Sí, se va a fragmented , pero la fragmentación afecta a los cabezazos de lectura y lectura, solo son necesarios para escaneos de rango significativos. Como solo consulta un GUID específico y un intervalo de fechas, la fragmentación no importará demasiado. Sí, es una clave amplia, por lo que las páginas que no son hojas tendrán una densidad de clave baja. Sí, conducirá a un factor de llenado deficiente. Y sí, pueden producirse divisiones de página. A pesar de estos problemas, dados los requisitos, sigue siendo la mejor elección de clave agrupada.
- Partición de la tabla por tiempo para que pueda implementar la eliminación eficiente de los registros caducados, a través de una ventana deslizante automática . Aumente esto con una reconstrucción de partición de índice en línea del último mes para eliminar el factor de llenado deficiente y la fragmentación introducida por la agrupación de GUID.
- habilitar la compresión de página. Dado que los grupos de claves agrupadas por GUID primero, todos los registros de un GUID estarán uno al lado del otro, lo que brinda una buena oportunidad para que la compresión de diccionarios se implemente.
- Necesitará una ruta rápida de IO para el archivo de registro. Usted está interesado en un alto rendimiento, no en baja latencia para que un registro se mantenga al día con insertos de 1K / segundo, por lo que la stripping es imprescindible.
El particionamiento y la compresión de página requieren un servidor SQL Enterprise Edition, no funcionarán en Standard Edition y ambos son bastante importantes para cumplir con los requisitos.
Como nota al margen, si los registros provienen de una granja de servidores front-end web, pondría Express en cada servidor web y en lugar de INSERT en la parte de atrás, SEND la información a la parte de atrás, utilizando una conexión local / transacción en el Express compartido con el servidor web. Esto proporciona una historia de disponibilidad mucho mejor para la solución.
Así que así es como lo haría en SQL Server. La buena noticia es que los problemas que enfrentará se conocen bien y se conocen soluciones. eso no significa necesariamente que sea mejor de lo que podrías lograr con Cassandra, BigTable o Dynamo. Dejaré a alguien más conocedor en cosas que no sean sql-ish al argumento de su caso.
Tenga en cuenta que nunca mencioné el modelo de programación, el soporte de .Net y demás. Sinceramente, creo que son irrelevantes en implementaciones grandes. Marcan una gran diferencia en el proceso de desarrollo, pero una vez implementado no importa qué tan rápido fue el desarrollo, si la sobrecarga de ORM mata el rendimiento :)
Almacenar registros en archivos binarios simples, un archivo por GUID, no sería más rápido que eso.
Amazon Redshift es un gran servicio. No estaba disponible cuando la pregunta se publicó originalmente en 2010, pero ahora es un jugador importante en 2017. Es una base de datos en columna, bifurcada de Postgres, por lo que las bibliotecas de conectores estándar de SQL y Postgres trabajarán con ella.
Se utiliza mejor para fines de informes, especialmente la agregación. Los datos de una sola tabla se almacenan en diferentes servidores en la nube de Amazon, distribuidos por las asignaciones de tabla definidas, por lo que depende de la potencia de la CPU distribuida.
Entonces los SELECT y especialmente los SELECT agregados son muy rápidos. La carga de datos de gran tamaño debe realizarse preferiblemente con el comando COPY de los archivos csv de Amazon S3. Los inconvenientes son que los DELETE y UPDATE son más lentos de lo habitual, pero es por eso que Redshift no es principalmente una base de datos transnacional, sino más bien una plataforma de almacenamiento de datos.
Contrariamente a la creencia popular, NoSQL no se trata de rendimiento, o incluso de escalabilidad. Se trata principalmente de minimizar el desajuste de la impedancia llamada Objeto-Relacional, pero también se trata de la escalabilidad horizontal frente a la escalabilidad vertical más típica de un RDBMS.
Para el simple requerimiento de insertos de ayuno y búsquedas rápidas, casi cualquier producto de base de datos servirá. Si desea agregar datos relacionales, o uniones, o tiene alguna lógica transaccional compleja o restricciones que necesita aplicar, entonces desea una base de datos relacional. Ningún producto NoSQL puede comparar.
Si necesita datos sin esquema, querrá ir con una base de datos orientada a documentos como MongoDB o CouchDB. El esquema flojo es el principal atractivo de estos; Personalmente me gusta MongoDB y lo uso en algunos sistemas de informes personalizados. Lo encuentro muy útil cuando los requisitos de datos cambian constantemente.
La otra opción principal de NoSQL es la distribución de tiendas clave-valor como BigTable o Cassandra. Estos son especialmente útiles si desea escalar su base de datos en muchas máquinas que ejecutan hardware básico. También funcionan bien en los servidores, obviamente, pero no aprovechan el hardware de alta gama, SQL Server u Oracle u otra base de datos diseñada para escalamiento vertical , y obviamente no son relacionales y no sirven para imponer la normalización. o restricciones Además, como habrás notado, el soporte de .NET tiende a ser irregular en el mejor de los casos.
Todos los productos de bases de datos relacionales admiten partición de un tipo limitado. No son tan flexibles como BigTable u otros sistemas DKVS, no se dividen fácilmente en cientos de servidores, pero realmente no suena como eso es lo que estás buscando. Son bastante buenos en el manejo de recuentos de miles de millones, siempre y cuando indexe y normalice los datos correctamente, ejecute la base de datos en hardware potente (especialmente SSD si puede pagarlos) y particione en 2, 3 o 5 discos físicos si necesario.
Si cumple con los criterios anteriores, si está trabajando en un entorno corporativo y tiene dinero para gastar en hardware decente y optimización de bases de datos, me quedaría con SQL Server por ahora. Si está pizcando centavos y necesita ejecutar esto en hardware de computación en la nube Amazon EC2 de baja gama, probablemente prefiera optar por Cassandra o Voldemort (suponiendo que pueda trabajar con .NET).
El siguiente artículo analiza la importación y el uso de una tabla de 16 000 millones de filas en Microsoft SQL. http://sqlmag.com/t-sql/adventures-big-data-how-import-16-billion-rows-single-table .
Del artículo:
Aquí hay algunos consejos destilados de mi experiencia:
- Cuantos más datos tenga en una tabla con un índice agrupado definido, más lento se vuelve importar registros sin clasificar en él. En algún momento, se vuelve demasiado lento para ser práctico.
- Si desea exportar su tabla al archivo más pequeño posible, hágalo en formato nativo. Esto funciona mejor con tablas que contienen principalmente columnas numéricas porque están representadas más compactamente en campos binarios que los datos de caracteres. Si todos sus datos son alfanuméricos, no obtendrá mucho exportándolo en formato nativo. No permitir valores nulos en los campos numéricos puede compactar aún más los datos. Si permite que un campo sea nulo, la representación binaria del campo contendrá un prefijo de 1 byte que indica cuántos bytes de datos se seguirán.
- No puede usar BCP para más de 2,147,483,647 registros porque la variable de contador BCP es un entero de 4 bytes. No pude encontrar ninguna referencia a esto en MSDN o en Internet. Si su mesa consiste en
más de 2,147,483,647 registros, tendrás que exportarlo en pedazos
o escribe tu propia rutina de exportación.- La definición de un índice agrupado en una tabla prepoblada requiere mucho espacio en disco. En mi prueba, mi registro explotó a 10 veces el original
tamaño de la mesa antes de la finalización- Al importar una gran cantidad de registros usando la instrucción BULK INSERT, incluya el parámetro BATCHSIZE y especifique cuántos
registros para comprometerse a la vez. Si no incluye este parámetro,
su archivo completo se importa como una transacción única, que
requiere mucho espacio de registro.- La forma más rápida de obtener datos en una tabla con un índice agrupado es preseleccionar los datos primero. Luego puede importarlo usando BULK
Instrucción INSERT con el parámetro ORDER.
Hay un hecho inusual que parece pasarse por alto.
" Básicamente después de insertar filas de 30Mil en un día, necesito buscar todas las filas con el mismo GUID (quizás 20 filas) y estar razonablemente seguro de que las recuperaré todas ".
Necesitando solo 20 columnas, un índice no agrupado en el GUID funcionará perfectamente. Puede agrupar en otra columna para la dispersión de datos en las particiones.
Tengo una pregunta sobre la inserción de datos: ¿cómo se inserta?
- ¿Es esto una inserción masiva en un horario determinado (por minuto, por hora, etc.)?
- ¿De qué fuente se extraen estos datos (archivos planos, OLTP, etc.)?
Creo que estos deben ser respondidos para ayudar a entender un lado de la ecuación.
Muy pocas personas trabajan en el conjunto de filas de miles de millones de tamaño, y la mayoría de las veces que veo una solicitud como esta en el desbordamiento de la pila, los datos no están cerca del tamaño en el que se informa.
36 mil millones, 3 mil millones por mes, eso es aproximadamente 100 millones por día, 4.16 millones por hora, ~ 70k filas por minuto, 1.1k filas por segundo entrando al sistema, de manera sostenida durante 12 meses, suponiendo que no hay tiempo de inactividad.
Esas cifras no son imposibles por un largo margen, he hecho sistemas más grandes, pero querrás verificar que realmente se trata de las cantidades que quieres decir: muy pocas aplicaciones realmente tienen esta cantidad.
En términos de almacenamiento / recuperación y un aspecto bastante crítico que no has mencionado es el envejecimiento de los datos más antiguos: la eliminación no es gratuita.
La tecnología normal que se observa es la partición, sin embargo, la búsqueda / recuperación basada en GUID daría como resultado un bajo rendimiento, suponiendo que tiene que obtener cada valor coincidente en todo el período de 12 meses. Podría colocar un índice agrupado en la columna GUID para agrupar sus datos asociados para lectura / escritura, pero a esas cantidades y velocidad de inserción, la fragmentación será demasiado alta para admitir, y caerá en el piso.
También sugiero que va a necesitar un presupuesto de hardware muy decente si se trata de una aplicación seria con velocidades de respuesta de tipo OLTP, es decir, con algunas suposiciones aproximadas, asumiendo muy pocos gastos generales de indexación, unos 2,7 TB de datos.
En el campo de SQL Server, lo único que quizás desee ver es la nueva edición de almacén de datos de parrallel (madison) que está diseñada más para fragmentar datos y ejecutar consultas paralelas en su contra para proporcionar alta velocidad frente a grandes datamarts.
Puede usar MongoDB y usar el guid como clave de fragmentación, esto significa que puede distribuir sus datos en varias máquinas, pero los datos que desea seleccionar se encuentran solo en una máquina, ya que los selecciona mediante la tecla de fusión.
Sharding en MongoDb aún no está listo para producción.
Puedes intentar usar Cassandra o HBase, aunque necesitarías leer sobre cómo diseñar las familias de columnas según tu caso de uso. Cassandra proporciona su propio lenguaje de consulta, pero necesita usar las API de Java de HBase para acceder directamente a los datos. Si necesita utilizar Hbase, le recomiendo que consulte los datos con Apache Drill de Map-R, que es un proyecto de código abierto. El lenguaje de consulta de Drill cumple con SQL (las palabras clave en drill tienen el mismo significado que tendrían en SQL).