Cómo ampliar el espacio de almacenamiento de PostgreSQL
Cstore_fdw implementa el almacenamiento de columnas para la base de datos PostgreSQL. El almacenamiento en columnas es ideal para escenarios de análisis de datos donde los datos se cargan en lotes.
Esta extensión utiliza el formato de almacenamiento de datos Optimized Row Row (ORC), que mejora el formato RCFile de Facebook y aporta los siguientes beneficios:
Compresión: convierte datos en memoria y disco. El tamaño es reducido de 2 a 4 veces su tamaño original. Se puede ampliar para admitir diferentes algoritmos de compresión.
Proyección de columnas: extrae únicamente datos de columnas relevantes para la consulta. Mejore el rendimiento de las consultas sensibles a IO.
Omitir índice: almacena las estadísticas máximas y mínimas de los grupos de filas y las utiliza para omitir filas irrelevantes.
Uso
La instalación y uso de cstore_fdw es muy simple, puedes consultar la información oficial.
thub.com/citusdata/cstore_fdw
Nota) Tenga en cuenta que cstore_fdw solo admite PostgreSQL9.3 y 9.4.
Hagamos algunas comparaciones simples de rendimiento para ver cuánta mejora de rendimiento puede aportar cstore_fdw.
2.1 Carga de datos
2.1.1 Tablas comunes
Crear tabla tb1
(
id int,
texto c1,
texto c2,
texto c3,
texto c4,
texto c5,
texto c6,
texto c7,
texto c8,
texto c9,
texto c10
);
Nota: En comparación con un escaneo completo de una tabla normal, las claves primarias y los índices no se crean.
[postgres@nodo 2 chenhj]$ tiempo psql -p 40382 -At -F, -c "seleccione id, id::texto, id::texto, id::texto, id::texto , id::texto, id::texto, id::texto, id::texto, id::texto, id::texto, id::texto, id::texto:texto de generate_series(1, 10000000 )id " | tiempo psql -p 40382-c " copiar TB 1 de
copiar 1000000
1.56 usuario 1.00 sistema 6: 42.39 usado 0 CPU(0 texto promedio 0 datos promedio 7632 residente máximo )k
776 entrada 0 salida (17 primaria 918 auxiliar) falta página 0 intercambio
Real 6m42.402s
Usuario 0m15.174s
sys 0m14.904s
postgres = # select pg_total_relation_size('TB 1'::regclass);
pg_total_relation_size
-
1161093120
(1 línea)
postgres = # \timing
El tiempo comienza
postgres =. # analizar TB 1;
Análisis
Tiempo: 11985,070 milisegundos
Insertar 65438 100 millones de registros, el tamaño de almacenamiento de datos es 1,16 G, la inserción tardó 6 minutos y 42 segundos y el análisis tomó 12 segundos.
Mesa de almacenamiento de 2,1,2 cm
$ mkdir-p/home/chenhj/data 94/c store
Crear cstore extendido_fdw
Crear servidor cstore_server contenedor de datos externo cstore_fdw
Crear tabla externa cstb1
(
id int,
c1 text,
texto c2,
texto c3,
texto c4,
texto c5,
texto c6,
texto c7,
texto c8,
texto c9,
texto c10
)
Servidor cstore_server
Opciones (nombre de archivo '/home/chenhj/data 94/cs store/cstb 1. cs store',
Compresión' pglz ');
[postgres@nodo 2 chenhj]$ tiempo psql -p 40382-At-F,-c "seleccione id,id::texto,id::texto,id::texto,id ::texto,www .hnnedu.com id::texto,id::texto,id::texto,id::texto,id::texto,id::texto,id::texto,id::texto de generate_series (1,10000000) id " | time psql -p 40382-c "Copiar cstb1
Copia 1000000
1.53 Usuario 0.78 Sistema 7: 35.15 Usado 0 CPU (0 texto promedio 0 datos promedio 7632 residente máximo) k
968 entrada 0 salida (20 principal 920 adjunto) falta página 0 intercambio
Real 7m35.520s
Usuario 0m14.809s
sys 0m14.170s
[postgres @ node 2 chenhj]$ ls -l/home/chenhj/data 94/c store/cstb 1 . postgres postgres 389583021 23 de junio 17:32/home/chenhj/data 94/c store/cstb 1 . c store
postgres = # \timing
inicio del tiempo.
postgres = # analizar cstb 1;
Análisis
Tiempo: 5946,476 milisegundos
Inserte 100.000 registros, el tamaño de almacenamiento de datos es 390 M , la inserción tarda 7 minutos y 35 segundos y el análisis tarda 6 segundos.
Después de usar el almacenamiento de columnas cstore, el tamaño de almacenamiento ocupado por los datos se reduce a 1, que es 3 veces mayor que el de una tabla normal. Cabe señalar que debido a que todas las columnas de texto están llenas de datos aleatorios, la relación de compresión no es alta y el efecto de compresión será mejor en algunos escenarios de aplicaciones prácticas.
2.2 Comparación de rendimiento de consultas similares de columnas de texto
2.2.1 Tablas de uso común
Borre la memoria caché del sistema de archivos y reinicie PostgreSQL.
[postgres @ node 2 chenhj]$ pg _ CTL -D /home/chenhj/data 94 -l archivo de registro 94 reiniciar
[root@node2 ~]# gratis< / p>
Número total de buffers compartidos libres en caché utilizados
Memoria: 2055508 771356 1284152 0 9900 452256
-/ buffers/cache: 309200 1746308
Intercambio: 4128760 387624 3741136
[root @ node 2 ~]# echo 1 gt;/proc/sys/VM/drop _ cache
[root@node2 ~ ]# gratis
Número total de buffers compartidos libres almacenados en caché
Memoria: 2055508 326788 1728720 0 228 17636
-/ buffer/cache: 308924 1746584
Exchange: 4128760 381912 3746848
Ejecute una consulta similar en la columna de texto.
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 . 23/ 14 _ x86 _ 64 _ (2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,80 0,00 0,38 3,42 0,00 95,40
Dispositivo : TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
Deutsche Mark-2 58,55 330,68 212,08 7351441 4714848
[postgres @ node 2 chenhj]$ tiempo psql -p 40382-c " seleccione recuento (*) de TB 1 donde c 1 como ' 66 ' "
Conteo
-
100000
(1 línea)
real 0m7.051s
Usuario 0m0.001s
sys 0m0.004s
[postgres @ nodo 2 chenhj]$ iostat -k DM-2
Linux 2 6 . 2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,80 0,00 0,38 3,43 0,00 95,39
Dispositivo: TPS kB _ lectura/ s kB _ wrtn/s kB _ read kB _ wrtn
Deutsche Mark-2 58,90 381,53 211,90 8489597 4714956
Se necesitan 7,1 segundos para leer 1,14 g de IO y escribir 108 K de IO.
La caché del sistema de archivos no está clara. No reinicie PostgreSQL y ejecútelo nuevamente. El tiempo de consumo se reduce a 1,6 segundos y casi no se genera IO.
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 . 23/ 14 _ x86 _ 64 _ (2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,80 0,00 0,38 3,43 0,00 95,39
Dispositivo : TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
Deutsche Mark-2 58,81 332,20 21 3,06 7350301 471 4364
[postgres @ node 2 chenhj ] $ tiempo psql -p 40382-c " seleccione recuento (*) de TB 1 donde c 1 como ' 66 ' "
Conteo
-
100000
(1 línea)
real 0m1.601s
Usuario 0m0.002s
sys 0m0.001s
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 32-71 . _(2 CPU)
CPU promedio: buen sistema de usuario, espera baja, robo inactivo
0,80 0,00 0,38 3,43 0,00 95,38
Dispositivo: TPS kB _ lectura/s kB _ wrtn/s kB _ read kB _ wrtn
Deutsche Mark-2 58.80 332.12 213.01 7350337 4714364
2.2.2 Tabla de almacenamiento
Borrar caché del sistema de archivos y reinicie PostgreSQL.
[postgres @ node 2 chenhj]$ pg _ CTL -D /home/chenhj/data 94 -l archivo de registro 94 reiniciar
[root @ node 2 ~]# echo 1 gt;/proc/sys/VM/drop_cache
Ejecutar consulta similar en columna de texto.
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 . 23/ 14 _ x86 _ 64 _ (2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,80 0,00 0,38 3,38 0,00 95,45
Dispositivo : TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
58.12 376.42 209.04 8492017 4716048
[postgres @ node 2 chenhj]$ tiempo psql -p 40382 -c " seleccione recuento (*) de cstb 1 donde c 1 me gusta ' 66 ' "
Conteo
-
100000
(1 línea)
Real 0m2.786s
Usuario 0m0.002s
sys 0m0.003s
[postgres @ node 2 chenhj ]$ iostat-k DM-2
Linux 2 6 . el6 x86 _ 64 (nodo 2) 23/06/14 _ x86 _ 64 _(2 CPU)<. /p>
CPU promedio: usuario bueno sistema baja espera robo inactivo
0.80 0.00 0.38 3.38 0.00 95.44
Dispositivo: TPS kB _ lectura/s kB _ wrtn/s kB _ read kB _ wrtn
58,12 378,75 208,89 8550761 4716048
Se necesitan 2,8 segundos y el resultado es 59 M, lectura IO, escritura IO 0K. Aunque no mucha gente optimiza el tiempo, IO se ha reducido considerablemente, lo que demuestra que la proyección de columnas juega un papel importante.
La caché del sistema de archivos no está clara. No reinicie PostgreSQL y ejecútelo nuevamente. El tiempo de consumo se reduce a 1,4 segundos y casi no se genera IO.
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 . 23/ 14 _ x86 _ 64 _ (2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,80 0,00 0,38 3,36 0,00 95,47
Dispositivo : TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
207.58 8550809 4716524
[postgres @ node 2 chenhj]$ time psql -p 40382- c " seleccione recuento (*) de cstb 1 donde c 1 me gusta ' 66 ' "
Conteo
-
100000
(1 línea)
real 0m1.424s
usuario 0m0.002s
sys 0m0.001s
[postgres @ nodo 2 chenhj ]$ iostat-k DM-2
Linux 2 6 . el6 . >
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,80 0,00 0,38 3,36 0,00 95,47
Dispositivo: TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
57.70 375.96 207.38 8550809 4716588
2.3 ejecutar = Consultar la columna Int
2.3.1 Tablas de uso común
Después borrar el caché del sistema de archivos y reiniciar PostgreSQL
[postgres @ node 2 chenhj]$ pg _ CTL -D /home/chenhj/data 94 -l log file 94 restart
[root @ node 2 ~]# echo 1 gt;/proc/sys/VM/drop_cache
Ejecutar = Consultar la columna Int.
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 . 23/ 14 _ x86 _ 64 _ (2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,79 0,00 0,37 3,33 0,00 95,50
Dispositivo : TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
Deutsche Mark 57,25 373,21 205,67 8560897 4717624
[postgres @ node 2 chenhj]$ tiempo psql - p 40382-c " seleccione recuento (*) de TB 1 donde id = 666666 "
Conteo
-
1
( 1 línea)
real 0m6.844s
usuario 0m0.002s
sys 0m0.006s
[postgres @ node 2 chenhj] $ iostat-k DM-2
Linux 2 6 32-71 . el6 . p>
CPU promedio: Usuario buen sistema, espera baja, robo inactivo
0.79 0.00 0.37 3.34 0.00 95.49
Dispositivo: TPS kB _ lectura/s kB _ wrtn/ s kB _ read kB _ wrtn
9699161 4717708
Se necesitan 6,8 segundos, lo que da como resultado una lectura de IO de 1,14 gy una escritura de IO de 84 K.
El caché no está claro. Ejecútalo de nuevo. El tiempo de consumo se reduce a 1,1 segundos y casi no se genera IO.
[postgres @ nodo 2 chenhj]$ iostat-k DM-2
Linux 2 6 . 23/ 14 _ x86 _ 64 _ (2 CPU)
CPU promedio: usuario bueno, sistema con baja espera, robo inactivo
0,79 0,00 0,37 3,33 0,00 95,50
Dispositivo : TPS kB _ lectura/s kB _ wrtn/s kB _ lectura kB _ wrtn
Deutsche Mark-2 57,44 421,37 204,97 9699177 4718032
[postgres @ node 2 chenhj]$ tiempo psql -p 40382-c " seleccione recuento (*) de TB 1 donde id = 666666 "
Conteo
-