离线数据模拟实时数据的技术探索

背景

业务场景： 在测试环境中没有真实的数据，所以需要把离线数据模拟成实时数据，把以年月日为文件名的目录和以日期为字段的文本内容都改为当日或未来某一天。

查看原始数据格式为CSV，以纯文本（含字符序列，不含二进制）形式存储表格数据，以制表符为分隔符，每日大概500-600万笔。测试环境中，默认安装了MySQL5.1，其默认存储引擎为myisam。楼主接下去将简要对比下innodb与myisam的某些方面的性能差异。

CSV样例：

由于数据量较大，加载进内存排序，其检索性能不高。可借用一台数据库服务器持久化当日数据，并对表示时间的字段索引。把凌晨1点-6点之间的数据摒弃掉，为其入库时间，入库需要定时定点操作，可使用quartz框架。对于短时间内进行大数据量插入适合先插入后建索引，所以该临时表就设置为每日一删一建一插一索引。设置每X秒对远程服务器的realtime表做一次入库，则需要在X秒内完成两个步骤：①检索过去X秒~当前时间的数据；②对远程表做入库。

首先探究短时间内插入大数据量的问题，对单文本进行IO操作，其性能瓶颈在IO上，使用多线程读取文本，要根据我们服务器的磁盘结构：是单片双片还是三片，在磁头只有一个的情况下，多个线程于事无补因为共用一个磁头，线程切换上下文耗费的时间非但不能提高效率还会拖垮程序性能。所以我们会考虑使用单线程读，多线程写。一次写多少数据好，根据CVS格式数据，我们决定采用JDBC+C3P0做insert into table values(…),(…),….，每一万笔的时候插入一次。

public class BatchInsertThread implements Runnable{
   
    private String sql;
    private static Logger log = Logger.getLogger(BatchInsertThread.class);

    public BatchInsertThread(String sql) {
        super();
        this.sql = sql;
    }

    @Override
    public void run() {
        BaseDao.batchInsertData(sql);//异常的时候继续执行
    }
}

接下去我们尝试做了入库，发现时间把控较为困难，在第一笔数据还没有完全插入之前，第二笔数据的线程已经启动，紧接着第三笔数据的线程也启动…

mysql> show processlist;
+—-+——+—————–+———-+———+——+———————————+——————————————————————————————————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+—————–+———-+———+——+———————————+——————————————————————————————————+
| 14 | root | localhost | realtime | Sleep | 7715 | | NULL |
| 15 | root | localhost:47508 | realtime | Query | 5025 | Waiting for table metadata lock | ALTER TABLE realtemp2 ADD INDEX idx_realt_tradedate ( TRADE_DATE) USING BTREE |
| 16 | root | localhost:47510 | realtime | Sleep | 7072 | | NULL |
| 17 | root | localhost:47509 | realtime | Sleep | 7081 | | NULL |
| 18 | root | localhost:47512 | realtime | Sleep | 7473 | | NULL |
| 19 | root | localhost:47514 | realtime | Sleep | 7233 | | NULL |
| 20 | root | localhost:47515 | realtime | Sleep | 6262 | | NULL |
| 21 | root | localhost:47513 | realtime | Sleep | 6745 | | NULL |
| 22 | root | localhost:47516 | realtime | Sleep | 7273 | | NULL |
| 23 | root | localhost:47517 | realtime | Sleep | 7286 | | NULL |
| 24 | root | localhost:47518 | realtime | Sleep | 7344 | | NULL |
| 25 | root | localhost:47519 | realtime | Sleep | 7305 | | NULL |
| 26 | root | localhost:47520 | realtime | Sleep | 7032 | | NULL |
| 27 | root | localhost:47521 | realtime | Sleep | 6551 | | NULL |
| 28 | root | localhost:47522 | realtime | Sleep | 6276 | | NULL |
| 29 | root | localhost:47523 | realtime | Sleep | 6580 | | NULL |
| 30 | root | localhost:47524 | realtime | Sleep | 6117 | | NULL |
| 31 | root | localhost:47525 | realtime | Sleep | 6671 | | NULL |
| 32 | root | localhost:47526 | realtime | Sleep | 5981 | | NULL |
| 33 | root | localhost:47527 | realtime | Sleep | 5202 | | NULL |
| 34 | root | localhost:47528 | realtime | Sleep | 5514 | | NULL |
| 35 | root | localhost:47529 | realtime | Sleep | 2875 | | NULL |
| 36 | root | localhost:47531 | realtime | Query | 3151 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 37 | root | localhost:47530 | realtime | Sleep | 3030 | | NULL |
| 38 | root | localhost:47533 | realtime | Sleep | 6470 | | NULL |
| 39 | root | localhost:47534 | realtime | Sleep | 6510 | | NULL |
| 40 | root | localhost:47535 | realtime | Sleep | 6550 | | NULL |
| 41 | root | localhost:47537 | realtime | Sleep | 6229 | | NULL |
| 42 | root | localhost:47536 | realtime | Sleep | 6390 | | NULL |
| 43 | root | localhost:47538 | realtime | Query | 2900 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 44 | root | localhost:47539 | realtime | Query | 2927 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 45 | root | localhost:47540 | realtime | Sleep | 6350 | | NULL |
| 46 | root | localhost:47541 | realtime | Sleep | 6189 | | NULL |
| 47 | root | localhost:47542 | realtime | Sleep | 6028 | | NULL |
| 48 | root | localhost:47543 | realtime | Sleep | 6069 | | NULL |
| 49 | root | localhost:47544 | realtime | Query | 2882 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 50 | root | localhost:47545 | realtime | Sleep | 3971 | | NULL |
| 51 | root | localhost:47546 | realtime | Sleep | 5788 | | NULL |
| 52 | root | localhost:47547 | realtime | Query | 3401 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 53 | root | localhost:47548 | realtime | Query | 4761 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 54 | root | localhost:47549 | realtime | Sleep | 5908 | | NULL |
| 55 | root | localhost:47550 | realtime | Sleep | 5948 | | NULL |
| 56 | root | localhost:47551 | realtime | Sleep | 4568 | | NULL |
| 57 | root | localhost:47552 | realtime | Sleep | 3293 | | NULL |
| 58 | root | localhost:47553 | realtime | Sleep | 4817 | | NULL |
| 59 | root | localhost:47554 | realtime | Sleep | 5302 | | NULL |
| 60 | root | localhost:47555 | realtime | Sleep | 5707 | | NULL |
| 61 | root | localhost:47556 | realtime | Sleep | 5426 | | NULL |
| 62 | root | localhost:47557 | realtime | Query | 3114 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 63 | root | localhost:47558 | realtime | Query | 2836 | Waiting for table metadata lock | INSERT INTO realtemp2 (CARD_ID, TRADE_DATE, TRADE_ADDRESS,TRADE_TYPE,START_ADDRESS,DESTINATION) VALU |
| 64 | root | localhost:47559 | realtime | Sleep | 3167 | | NULL |
| 65 | root | localhost:47560 | realtime | Sleep | 3181 | | NULL |
| 66 | root | localhost | realtime | Query | 0 | init | show processlist |
+—-+——+—————–+———-+———+——+———————————+——————————————————————————————————+
53 rows in set (0.00 sec)

我们发现短时间内建立了大量连接，最终很容易出现几条Waiting for table metadata lock停滞而产生死锁。需要重启数据库才能解锁，或者kill掉进程号(有时kill行不通)。