使用sqlalchemy压缩连接

Question

我正在做一个项目，有多个远程设备上传数据到一个单一的MySQL数据库。（其中一些设备使用带数据帽的蜂窝调制解调器）

每台设备每2秒上传126列浮点数字的用电量数据，精度为4（xx.1234）。在

此外，208列平均每天上传2200次。每隔1、5和15分钟。我可能只需要从2秒的数据中计算这些数据，但是在python中对原始数据进行计算要容易得多，占用的cpu更少，这只是为了验证这个想法。在

我看到的最高数据使用量是130mib

单行的csv版本。在

# id, dtime, hz, min_v1, avg_v1, max_v1, min_v2, avg_v2, max_v2, min_v3, avg_v3, max_v3, min_i1, avg_i1, max_i1, min_i2, avg_i2, max_i2, min_i3, avg_i3, max_i3, i_n, l1_kw_pa, l2_kw_pb, l3_kw_pc, avg_kw_t, l1_kvar_qa, l2_kvar_qb, l3_kvar_qc, avg_kvar_t, l1_kva_sa, l2_kva_sb, l3_kva_sc, avg_kva_t, l1_pf_pfa, l2_pf_pfb, l3_pf_pfc, avg_pf_t, power_dmd, kvar_dmd, kva_dmd, kwh_imp, kvarh_imp, kwh_t, kvarh_t, kvah_t, v1_thd, v2_thd, v3_thd, i1_thd, i2_thd, i3_thd, p_seq_real_v, p_seq_comp_v, n_seq_real_v, n_seq_comp_v, z_seq_real_v, z_seq_comp_v, p_seq_real_i, p_seq_comp_i, n_seq_real_i, n_seq_comp_i, z_seq_real_i, z_seq_comp_i, v2_pa, v3_pa, i1_pa, i2_pa, i3_pa, vh1_2, vh1_3, vh1_5, vh1_7, vh1_9, vh1_11, vh1_13, vh1_odd, vh1_even, vh1_cf, vh2_2, vh2_3, vh2_5, vh2_7, vh2_9, vh2_11, vh2_13, vh2_odd, vh2_even, vh2_cf, vh3_2, vh3_3, vh3_5, vh3_7, vh3_9, vh3_11, vh3_13, vh3_odd, vh3_even, vh3_cf, ih1_3, ih1_5, ih1_7, ih1_9, ih1_11, ih1_13, ih1_odd, ih1_even, ih1_kf, ih2_3, ih2_5, ih2_7, ih2_9, ih2_11, ih2_13, ih2_odd, ih2_even, ih2_kf, ih3_3, ih3_5, ih3_7, ih3_9, ih3_11, ih3_13, ih3_odd, ih3_even, ih3_kf
1, 2015-03-09 20:12:05, 59.97, 123.1, 122.992, 123.1, 122.5, 122.381, 122.5, 121.8, 121.749, 121.9, 0, 1.91508, 0, 0, 13.4917, 0, 0, 7.38669, 0, 19.9551, -5.54378, 226.589, 127.961, 348.94, 235.676, 1631.89, -887.699, 978.145, 235.981, 1650.68, 899.93, 2785.75, -0.02348, 0.13701, 0.14203, 0.125, 47.335, 1299.89, 3203.01, 1272600, 863619, 1272850, 863720, 1846930, 0.0148, 0.0148, 0.0123, , , , 122.4, 0.2, 0.1, 0.1, 0.6, -0.1, 0, 0, 0, 0, 0, 0, 119.9, 240.2, 92, 203.8, 160.1, , , , , , , , , , 1.428, , , , , , , , , , 1.434, , , , , , , , , , 1.427, , , , , , , , , , , , , , , , , , , , , , , , , , , 

# id, dtime, min_hz, avg_hz, max_hz, min_min_v1, avg_avg_v1, max_max_v1, min_min_v2, avg_avg_v2, max_max_v2, min_min_v3, avg_avg_v3, max_max_v3, min_min_i1, avg_avg_i1, max_max_i1, min_min_i2, avg_avg_i2, max_max_i2, min_min_i3, avg_avg_i3, max_max_i3, min_i_n, avg_i_n, max_i_n, min_l1_kw_pa, avg_l1_kw_pa, max_l1_kw_pa, min_l2_kw_pb, avg_l2_kw_pb, max_l2_kw_pb, min_l3_kw_pc, avg_l3_kw_pc, max_l3_kw_pc, min_avg_kw_t, avg_avg_kw_t, max_avg_kw_t, min_l1_kvar_qa, avg_l1_kvar_qa, max_l1_kvar_qa, min_l2_kvar_qb, avg_l2_kvar_qb, max_l2_kvar_qb, min_l3_kvar_qc, avg_l3_kvar_qc, max_l3_kvar_qc, min_avg_kvar_t, avg_avg_kvar_t, max_avg_kvar_t, min_l1_kva_sa, avg_l1_kva_sa, max_l1_kva_sa, min_l2_kva_sb, avg_l2_kva_sb, max_l2_kva_sb, min_l3_kva_sc, avg_l3_kva_sc, max_l3_kva_sc, min_avg_kva_t, avg_avg_kva_t, max_avg_kva_t, min_l1_pf_pfa, avg_l1_pf_pfa, max_l1_pf_pfa, min_l2_pf_pfb, avg_l2_pf_pfb, max_l2_pf_pfb, min_l3_pf_pfc, avg_l3_pf_pfc, max_l3_pf_pfc, min_avg_pf_t, avg_avg_pf_t, max_avg_pf_t, max_power_dmd, max_kvar_dmd, max_kva_dmd, max_kwh_imp, max_kvarh_imp, max_kwh_t, max_kvarh_t, max_kvah_t, min_v1_thd, avg_v1_thd, max_v1_thd, min_v2_thd, avg_v2_thd, max_v2_thd, min_v3_thd, avg_v3_thd, max_v3_thd, min_i1_thd, avg_i1_thd, max_i1_thd, min_i2_thd, avg_i2_thd, max_i2_thd, min_i3_thd, avg_i3_thd, max_i3_thd, p_seq_real_v, p_seq_comp_v, n_seq_real_v, n_seq_comp_v, z_seq_real_v, z_seq_comp_v, p_seq_real_i, p_seq_comp_i, n_seq_real_i, n_seq_comp_i, z_seq_real_i, z_seq_comp_i, v2_pa, v3_pa, i1_pa, i2_pa, i3_pa, vh1_2, vh1_3, vh1_5, vh1_7, vh1_9, vh1_11, vh1_13, min_vh1_odd, avg_vh1_odd, max_vh1_odd, min_vh1_even, avg_vh1_even, max_vh1_even, min_vh1_cf, avg_vh1_cf, max_vh1_cf, vh2_2, vh2_3, vh2_5, vh2_7, vh2_9, vh2_11, vh2_13, min_vh2_odd, avg_vh2_odd, max_vh2_odd, min_vh2_even, avg_vh2_even, max_vh2_even, min_vh2_cf, avg_vh2_cf, max_vh2_cf, vh3_2, vh3_3, vh3_5, vh3_7, vh3_9, vh3_11, vh3_13, min_vh3_odd, avg_vh3_odd, max_vh3_odd, min_vh3_even, avg_vh3_even, max_vh3_even, min_vh3_cf, avg_vh3_cf, max_vh3_cf, ih1_3, ih1_5, ih1_7, ih1_9, ih1_11, ih1_13, min_ih1_odd, avg_ih1_odd, max_ih1_odd, min_ih1_even, avg_ih1_even, max_ih1_even, min_ih1_kf, avg_ih1_kf, max_ih1_kf, ih2_3, ih2_5, ih2_7, ih2_9, ih2_11, ih2_13, min_ih2_odd, avg_ih2_odd, max_ih2_odd, min_ih2_even, avg_ih2_even, max_ih2_even, min_ih2_kf, avg_ih2_kf, max_ih2_kf, ih3_3, ih3_5, ih3_7, ih3_9, ih3_11, ih3_13, min_ih3_odd, avg_ih3_odd, max_ih3_odd, min_ih3_even, avg_ih3_even, max_ih3_even, min_ih3_kf, avg_ih3_kf, max_ih3_kf
1, 2015-03-25 12:05:03, 59.9351, 59.9515, 59.9651, 123, 123.165, 123.5, 122.2, 122.379, 122.7, 121.9, 121.986, 122.3, 0, 0, 0, 0, 22.8891, 0, 0, 6.69319, 0, 0, 0, 0, 0, 0, 0, 2689.78, 2741.23, 2827.1, 761.323, 767.21, 775.285, 3455.01, 3509.49, 3597.13, 0, 0, 0, -47.318, -29.5382, -16.3021, 142.391, 147.547, 152.868, 97.1515, 117.985, 131.682, 0, 0, 0, 2744.95, 2800.07, 2883.35, 799.135, 818.643, 854.545, 3545.14, 3619.77, 3699.94, 0.99903, 0.99903, 0.99903, 0.97461, 0.978048, 0.98087, 0.8978, 0.936465, 0.95534, 0.95832, 0.968614, 0.97364, 3497.05, 154.864, 3610.55, 529.2, 46.8, 529.8, 47.3, 568.1, 0.0147, 0.0149417, 0.0153, 0.0155, 0.0158617, 0.0164, 0.0138, 0.0141883, 0.0149, 0, 0, 0, 0, 0, 0, 0, 0, 0, 122.452, -0.0783333, 0.241667, 0.103333, 0.308333, -0.0666667, 0, 0, 0, 0, 0, 0, 119.985, 240.133, 0, 121.318, 250.655, 0, 0.01061, 0, 0.003965, 0, 0, 0, 0.0131, 0.013455, 0.014, 0.0058, 0.006575, 0.007, 1.429, 1.43403, 1.435, 0, 0.0117267, 0, 0.00466333, 0, 0, 0, 0.0142, 0.0146267, 0.0152, 0.0055, 0.00624333, 0.0066, 1.435, 1.43673, 1.438, 0, 0.0110717, 0, 0, 0, 0, 0, 0.0128, 0.0132333, 0.0143, 0.0042, 0.00523333, 0.0059, 1.428, 1.42912, 1.43, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

我当前的设置使用Sqlalchemy和pythonmysqldb与数据库通信。我需要找到一种方法来减少数据的使用，如果可能的话，不要减少发送数据的频率

万一有关系的话，每个远程设备都是一台小树莓派电脑。在

作为参考，这是一个链接到我的开发web服务器，在那里你可以看到这个项目的意义。 http://104.131.181.35/live/voltsandamps 这项工作仍在进行中。在

在过去的几天里，我一直在研究MySQL压缩协议，但是还没有找到一种方法用sqlalchemy或任何其他python数据库连接器来实现它。在

我知道python_mysqldb有一个compress标志，但我不知道如何使用它。尤其是当它被用作sqlalchemy的驱动程序时，如果需要，我愿意放弃它。在

这可能吗？如果不是的话，有没有其他的解决方案能更好地解决这个问题。在

任何帮助都是值得的。在

编辑：

最后我写了一个类似@paidhima推荐的web服务。每台设备每1-30分钟发送一次数据。数据格式基本上是一个压缩的json字符串，具有版本、时间戳和值数组。然后，web服务器解压缩接收到的数据并将其插入数据库。当我第一次问这个问题时，我对数据库和web开发基本一无所知。一年回顾过去真有趣。web服务的最终结果：我实现了大约10:1的压缩比（介于88%和92%之间）。每个设备平均每天约10-15MB。在让远程设备直接连接到服务器后不久，我开始发现这不是一个适合于开发环境之外的解决方案。安全问题、防火墙问题、太多断开的连接以及一般性的研究导致我放弃了直接连接，并编写了一个简单的web服务。在

有了直接的数据库连接，我可以把它降低到每天每个设备70-80MB。这是有准备好的语句，并且启用了连接压缩。在

web服务是用python编写的，没有数据库交互代码，只有大约250行代码（用于客户机和服务器）。谢谢你给我的好建议。我完全是自学成才的，只有感谢像你们这样提供建议和回答问题的人，我才能走得更远。在

Answer 1

识别潜在改进

如果您在插入每个记录时检查传输到MySQL的数据包，这应该很容易用packet sniffer来完成，因为（除非您是connecting over SSL），通信既没有加密也没有压缩，您会注意到：

1. 每次都会传输SQLINSERT语句，其中包含完整的列名列表。

2. 浮点值以字符串形式传输，每个字符串最多需要36个字符。

这两种方法都会导致大量不必要的网络利用率，而使用MySQL's binary prepared statement protocol可以避免这种情况（SQL命令只能被发送到服务器一次，此后每次插入尝试都将以各自的存储格式传输数据值）。在

在SQLAlchemy支持的MySQL驱动程序中，只有Oracle的“官方”驱动程序（MySQL Connector/Python）提供了此功能的API（虽然oursql也使用该协议，但它不会重用重复发送的语句）。在

炼金术

不幸的是，SQLAlchemy的mysqlconnector dialect目前没有使用这些特性。在

虽然在SQLAlchemy中仍然有一些事情可以做，以减少网络利用率（例如，在核心中，您可以阻止完整的列名列表被传输），但实际情况是，您永远不会像使用二进制准备语句协议那样做得那么好。在

建议

因此，我建议：

• 扩展SQLAlchemy的mysqlconnector方言来支持这样的功能（更多的工作，但是对整个社区有相当大的价值）；或者

• 删除SQLAlchemy（至少对于这些插入操作），而是直接使用MySQL连接器/Python驱动程序。

在使用这两种方法时，您还可以同时启用packet compression。在

示例

import time
import mysql.connector

cnx = mysql.connector.connect(user='raspberryPi_1234',
                              password='foobar',
                              host='mysql.example.com',
                              database='voltsandamps',
                              autocommit=true,
                              compress=true)  # compress the connection

cursor = cnx.cursor(prepared=True)            # this is what SQLAlchemy is missing

stmt = "INSERT INTO power_readings VALUES (" + ",".join(126*["?"]) + ")"

while true:
    cursor.execute(stmt, getPowerReadings())
    time.sleep(2)

最后的想法

如果您需要进一步降低网络利用率，您可以考虑使用stored procedures来封装INSERT命令，这不仅是因为CALL myProc(...)命令几乎总是比底层的INSERT命令短，而且还因为它使人们能够采用一些非常激进的技术，包括：

• 重新调整数据基：如果值趋向于在某个范围内，您只需要从该范围的基部传输偏移量（这可能允许在传输过程中使用较小的数据类型）-然后可以在存储过程中执行将基值重定为实际值（并且可以使用user-defined variable来设置基值本身）；并且

• 在极端情况下，可以在客户机上压缩数据并将其打包为二进制字符串，然后在服务器上解压和解压缩，从而最大限度地利用最后一位（同样，单个字符串值比相同聚合长度的多个单独值产生的管理开销要少）。