Ejecuto una construcción de base de datos MySQL muy simple. Solo tengo columnas como id, TimeStamp y OP_fs155e que considero que es una configuración extremadamente básica.

MariaDB [gadbdfm]> desc optical_power;
+-----------+------------------+------+-----+----------------------+-----------------------------+
| Field     | Type             | Null | Key | Default              | Extra                       |
+-----------+------------------+------+-----+----------------------+-----------------------------+
| id_record | int(10) unsigned | NO   | PRI | NULL                 | auto_increment              |
| TimeStamp | timestamp(6)     | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP |
| OP_fs155e | varchar(30)      | YES  | MUL | NULL                 |                             |
| data1     | varchar(30)      | YES  |     | NULL                 |                             |
| data2     | varchar(30)      | YES  |     | NULL                 |                             |
| data3     | varchar(30)      | YES  |     | NULL                 |                             |
| data4     | varchar(30)      | YES  |     | NULL                 |                             |
| data5     | varchar(30)      | YES  |     | NULL                 |                             |
+-----------+------------------+------+-----+----------------------+-----------------------------+
8 rows in set (0.00 sec)

Sin embargo, empiezo a notar que mis selecciones son terriblemente lentas primero en Raspberry Pi 3 (como maestro) y Centos7 (como esclavo - servidor muscular de 8GB). Aquí está mi selección de todo en el servidor esclavo que tomó varios minutos. Pensé que el problema es que Raspberry Pi 3 es demasiado lento, pero cuando descubrí que es lo mismo en un servidor esclavo real, definitivamente hay algo mal en él.

MariaDB [gadbdfm]> select TimeStamp,OP_fs155e from optical_power;

| 2017-01-01 17:41:03.697000 | -24       |
| 2017-01-01 17:42:03.666000 | -24       |
| 2017-01-01 17:43:03.701000 | -24       |
| 2017-01-01 17:44:03.675000 | -24       |
| 2017-01-01 17:45:03.676000 | -24       |
| 2017-01-01 17:46:03.692000 | -24       |
| 2017-01-01 17:47:03.686000 | -24       |
| 2017-01-01 17:48:03.539000 | -24       |
| 2017-01-01 17:49:03.581000 | -24       |
+----------------------------+-----------+
23044062 rows in set (37.24 sec)

Domina my.cnf

pi@rpi3jantoth - /opt/FlightStrata155E cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -v "#" | grep -v "^$"
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 0.0.0.0
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err

Esclavo my.cnf :

[root@fiber ~]# cat /etc/my.cnf | grep -v "#" | grep -v "^$"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id = 2
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

Sé que tengo optimización cero en mi esquema.

Un ejemplo más:

Esta selección en Raspberry Pi 3 toma como 7 minutos

mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;
0
user2156115 1 ene. 2017 a las 22:15
Muéstrenos el resultado de EXPLICAR seleccione TimeStamp, OP_fs155e de optical_power ORDER BY TimeStamp desc limit 15;
 – 
Bernd Buffen
1 ene. 2017 a las 22:36
Ok, lo que hace mi aplicación es lo siguiente: como cada minuto que recopilo el valor, llamo OP_fs155e (potencia óptica) del dispositivo Free Space Optics y lo guardo en mysql en frambuesa pi3 (MAESTRO).
 – 
user2156115
1 ene. 2017 a las 22:53

3 respuestas

La Raspberry Pi usa una tarjeta SD para el almacenamiento y, por lo tanto, es comprensible que el rendimiento de E / S sea mucho peor que el de un sistema de disco de servidor real.

Su primera consulta que tarda 37,24 segundos en devolver 23 millones de filas. Eso es bastante E / S. Calculo que podría caber alrededor de 500 filas por página de 16 KB (asumiendo que sus columnas de datos tienen aproximadamente 3 bytes cada una en promedio), por lo que necesitaría leer más de 46,000 páginas para leer 23 millones de filas, lo que equivale a 755 MB.

Apuesto a que es más o menos el data_length de su tabla, que puede verificar:

SHOW TABLE STATUS LIKE 'optical_power'\G

Pero la tasa de E / S para lecturas aleatorias en una tarjeta SD está entre 2,28 MB / seg y 8,10 MB / seg, por lo que tomaría entre 93 y 331 segundos leer 755 MB de la tarjeta SD. Eso es solo matemáticas.

Quizás algunas páginas de datos ya estaban almacenadas en caché en el grupo de búfer de MySQL, o InnoDB pudo hacer algunas optimizaciones de lectura anticipada para ayudar aquí.

Tu segunda consulta no está bien optimizada. Tiene que usar un ordenamiento de archivos porque no hay índice en su columna TimeStamp. El ordenamiento de archivos puede utilizar espacio de almacenamiento temporal, lo que genera E / S de escritura. Las escrituras son mucho más lentas que las lecturas en una tarjeta SD. Por lo tanto, no es de extrañar que se necesiten 7 minutos para realizar su consulta ORDER BY TimeStamp LIMIT 15.

Aparentemente, la marca de la tarjeta SD marca una gran diferencia. Consulte http://www.jeffgeerling.com/blogs/jeff -geerling / raspberry-pi-microsd-card para algunas comparaciones.

Pero incluso si obtiene una tarjeta SD que es más rápida, todavía la está desgastando al usar E / S de manera ineficiente. Será mejor evitar las E / S.

  • Cree un índice en su columna TimeStamp, para que ORDER BY TimeStamp pueda usarlo en lugar de hacer un ordenamiento de archivos. La indexación es muy importante para optimizar las consultas. Vea mi presentación Cómo diseñar índices, realmente.
  • Muchos usuarios de Raspberry Pi almacenan datos MySQL en el motor de almacenamiento MyISAM. MyISAM solo usa E / S con búfer y no tiene características de seguridad contra choques. Esto debería ayudar a mejorar el rendimiento de E / S y disminuir el desgaste de su tarjeta SD. Los índices y la replicación funcionan bien con las tablas MyISAM. MyISAM también tiende a almacenar datos en menos espacio que InnoDB.
  • Asegúrese de configurar sync_binlog=0 para permitir que el registro de replicación también use E / S asíncrona.

Si debe utilizar InnoDB, ajústelo para obtener el máximo almacenamiento en caché y la mínima durabilidad:

  • Aumente el innodb_buffer_pool_size tanto como pueda. Pero no lo haga tan grande que otros procesos no tengan suficiente memoria. Cuente con el grupo de búfer usando otro 10%, por lo que si lo establece en 512M, realmente tomará 563M.
  • Evite las E / S síncronas y utilice las E / S con búfer siempre que sea posible. Consulte https://dev.mysql.com/doc /refman/5.5/en/optimizing-innodb-diskio.html

    innodb_flush_log_at_trx_commit=2
    innodb_flush_method=O_DSYNC
    innodb_doublewrite=0
    

Re tu comentario:

Estoy usando una memoria USB Toshiba de 16GB como almacenamiento en lugar de la tarjeta SD, porque tengo muchos problemas con las tarjetas SD

El rendimiento de una unidad flash USB (lápiz) no es muy diferente al de una tarjeta SD. Ambos dispositivos están optimizados para lectura / escritura secuencial, no lectura / escritura aleatoria. Apestan terriblemente como sistemas de archivos o para el trabajo de bases de datos.

La conclusión es que si necesita el rendimiento de un servidor real, con datos a una escala que pertenece a un servidor, entonces no use una Raspberry Pi.

Esperaría que una solución Raspberry Pi para la recopilación de datos no almacene ningún dato en el Pi, sino que publique los datos inmediatamente en un servidor de su red. Una buena solución sería ejecutar un servidor de cola de mensajes, recopilando eventos publicados por sus diversos dispositivos Pi. Luego, escriba un script para consumir los datos de la cola de mensajes y publíquelo en la base de datos por lotes.

1
Bill Karwin 2 ene. 2017 a las 22:24

Bueno, en cuanto a tu primera sugerencia:

Database changed
mysql> SHOW TABLE STATUS LIKE 'optical_power'\G
*************************** 1. row ***************************
           Name: optical_power
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 22030014
 Avg_row_length: 38
    Data_length: 843055104
Max_data_length: 0
   Index_length: 0
      Data_free: 7340032
 Auto_increment: 34973978
    Create_time: 2016-12-30 16:10:49
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Estoy usando 16GB USB Toshiba stick como almacenamiento en lugar de la tarjeta SD, porque tengo muchos problemas con las tarjetas SD. Por ejemplo, uso todos los puertos USB raspberry pi3 porque es necesario recolectar datos de varios sensores. estoy intentando

ALTER TABLE optical_power ADD INDEX(TimeStamp,OP_fs155e);

Aprecio mucho tu respuesta, intentaré jugar con ella.

Muchas gracias

Por lo tanto, es simplemente una tabla de marca de tiempo y valor (OP_fs155e) simples.

code mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;
+----------------------------+-----------+
| TimeStamp                  | OP_fs155e |
+----------------------------+-----------+
| 2017-01-01 17:49:03.581000 | -24       |
| 2017-01-01 17:48:03.539000 | -24       |
| 2017-01-01 17:47:03.686000 | -24       |
| 2017-01-01 17:46:03.692000 | -24       |
| 2017-01-01 17:45:03.676000 | -24       |
| 2017-01-01 17:44:03.675000 | -24       |
| 2017-01-01 17:43:03.701000 | -24       |
| 2017-01-01 17:42:03.666000 | -24       |
| 2017-01-01 17:41:03.697000 | -24       |
| 2017-01-01 17:40:03.688000 | -24       |
| 2017-01-01 17:39:03.574000 | -24       |
| 2017-01-01 17:38:03.596000 | -24       |
| 2017-01-01 17:37:03.545000 | -24       |
| 2017-01-01 17:36:03.667000 | -24       |
| 2017-01-01 17:35:03.544000 | -24       |
+----------------------------+-----------+
15 rows in set (2 min 41.32 sec)
0
user2156115 1 ene. 2017 a las 23:48
Lo que estaba buscando es Data_length: 843055104 que no está muy lejos de mi estimación de 755 MB. El estado de la tabla solo puede mostrar estimaciones aproximadas de filas, data_length, index_length.
 – 
Bill Karwin
2 ene. 2017 a las 22:18

¡Hola @Bill Karwin y @Bernd Buffen! Aprecio mucho tu ¡Consejo! Agregué como este índice como se muestra y expliqué en la presentación aquí :

mysql> ALTER TABLE optical_power ADD INDEX(TimeStamp,OP_fs155e);

Y el resultado es 3,24 segundos en comparación con 2 minutos o 7 minutos a veces: ¡¡¡INCREÍBLE !!!

   mysql> use gadbdfm;
Database changed
mysql> select TimeStamp, OP_fs155e from optical_power ORDER BY TimeStamp desc limit 15;
+----------------------------+-----------+
| TimeStamp                  | OP_fs155e |
+----------------------------+-----------+
| 2017-01-01 17:49:03.581000 | -24       |
| 2017-01-01 17:48:03.539000 | -24       |
| 2017-01-01 17:47:03.686000 | -24       |
| 2017-01-01 17:46:03.692000 | -24       |
| 2017-01-01 17:45:03.676000 | -24       |
| 2017-01-01 17:44:03.675000 | -24       |
| 2017-01-01 17:43:03.701000 | -24       |
| 2017-01-01 17:42:03.666000 | -24       |
| 2017-01-01 17:41:03.697000 | -24       |
| 2017-01-01 17:40:03.688000 | -24       |
| 2017-01-01 17:39:03.574000 | -24       |
| 2017-01-01 17:38:03.596000 | -24       |
| 2017-01-01 17:37:03.545000 | -24       |
| 2017-01-01 17:36:03.667000 | -24       |
| 2017-01-01 17:35:03.544000 | -24       |
+----------------------------+-----------+
15 rows in set (3.24 sec)
0
user2156115 2 ene. 2017 a las 01:29
Esa consulta con ese índice debería haberse ejecutado mucho más rápido.
 – 
Rick James
3 ene. 2017 a las 06:35