数据库选型之MySQL(普通硬盘)

本博客记录作者在工作与研究中所经历的点滴，一方面给自己的工作与生活留下印记，另一方面若是能对大家有所帮助，则幸甚至哉矣！

简介

鉴于高频中心库task部分(核心业务处理、存储逻辑)占用机器较多，为节省成本，调研数据库或缓存，以期满足高频生产的需求：1）峰值1w条/s；2）峰值60w条/m。本着节省成本的角度，本文对开源、免费的数据库MySQL和PostgreSQL从单一处理和批处理角度展开测试，测试目标平均写入速率达10000条/s 以上则能满足要求。

测试环境

硬件环境：

10.1.120.37：Intel Pentium, 主频：2.90G，内存：6G

localhost： Intel Core I5, 主频：3.10G, 内存：4G

软件环境：

10.1.120.37:Cent OS 6.5, MySQL 5.6.25 (社区版)

localhost: Win7，MySQL 5.6.26（社区版）

表结构：

 1 DROP TABLE IF EXISTS `transaction`;
 2 CREATE TABLE `transaction` (
 3   `tradedate` datetime DEFAULT NULL,
 4   `symbol` varchar(6) DEFAULT NULL,
 5   `symbolname` varchar(8) DEFAULT NULL,
 6   `trdmintime` varchar(6) DEFAULT NULL,
 7   `startprice` decimal(9,3) DEFAULT NULL,
 8   `highprice` decimal(9,3) DEFAULT NULL,
 9   `lowprice` decimal(9,3) DEFAULT NULL,
10   `endprice` decimal(9,3) DEFAULT NULL,
11   `change` decimal(9,3) DEFAULT NULL,
12   `changeratio` decimal(6,3) DEFAULT NULL,
13   `minvolume` decimal(10,0) DEFAULT NULL,
14   `minamout` decimal(16,3) DEFAULT NULL,
15   `unix` bigint(20) DEFAULT NULL,
16   `market` varchar(3) DEFAULT NULL
17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

table transaction

性能测试

MySQL测试

对高频生产的应用需求，本文构造高频中心库系统的数据结构，从单一处理和批处理角度，对远程节点和本地节点（对比性能所用）MySQL进行写入操作，分别存储数据量为60K、100K、600K条数据，对其速率进行测试。需要指出，由于常见I/O访问的瓶颈主要受限于写入测试，本文只针对写入操作进行测试，暂不考虑读取操作或者混合读写方式，若写入操作不满足要求，其它操作无需测试。

单一处理，即每次处理一条数据，对其性能进行测试，结果见表-1。

表-1 单一处理MySQL测试结果

数据库IP	数据量（K）	平均写入速率（条/s）
10.1.120.37	60	1533
10.1.120.37	100	1721
10.1.120.37	600	1772
localhost	60	4889
localhost	100	3875
localhost	600	5057

从表-1可知：1）随着处理数据量增加，其写入速率急剧下降；2）从10.1.120.37和localhost来看，采用单一处理方式，较难满足高频生产需求。

批处理方式，分别从批量处理1000、2000、3000条数据出发，对其性能展开测试，结果见表-2。

表-2批处理MySQL测试结果

		1000	2000	3000
数据库IP	数据量（K）	平均写入速率（条/s）	平均写入速率(条/s)	平均写入速率(条/s)
10.1.120.37	60	1533	1575	1546
10.1.120.37	100	1721	1654	1614
10.1.120.37	600	1772	1710	1725
localhost	60	2236	3033	3654
localhost	100	2252	3589	3936
localhost	600	2289	4717	4759

从表-2可知：1）增加批处理数据量，远程节点的写入速率变化不大，可能受限网络资源速率；2）采用批处理方式，很难满足高频生产系统需求，本地节点尚且很难满足，寄希望于远程节点则更加困难。

小结

从上述测试情况来看，单独采用MySQL数据库，其实是很难满足要求I/O要求，针对60w/m的峰值，即10次/ms，对于普通的物理介质存储方案（硬盘）来说，该要求是很难满足的。因此，若要考虑物理介质来存储，固态硬盘是一个选择方案，具体有待于测试。

PostgreSQL测试

由于PostgreSQL可以完全胜任任何中上规模的范围内的业务，因此，本文也针对PostgreSQL是否满足高频生产的应用需求展开性能测试。目前只针对本地节点从单一处理和批处理出发，分别存储上述数据量（见MySQL测试），若本地节点很难满足需求，远程节点也无需测试了。

每次处理一条数据，结果见表-3。

表-3单一处理PostgreSQL测试结果

数据库IP	数据量（K）	平均写入速率（条/s）
localhost	60	2055
localhost	100	2303
localhost	600	2102

分别从批量处理1000、2000、3000条数据出发，对其性能展开测试，结果见表-4。

表-4单一处理PostgreSQL测试结果

		1000	2000	3000
数据库IP	数据量（K）	平均写入速率（条/s）	平均写入速率(条/s)	平均写入速率(条/s)
localhost	60	2167	1755	2342
localhost	100	2200	2436	1423
localhost	600	1952	2184	2201

小结

从上述测试结果来看，PostgreSQL针对本文目标应用场景的需求，差距甚远。本地节点尚且无法胜任需求，同时其CPU的处理能力也较远程节点强一些，因此不再对远程节点展开测试。

总结

经上述测试，PostgreSQL相对MySQL的写入速率稍微缓慢一些。针对节省成本的考虑，拟采用单一服务器来满足应用需求，难度巨大，主要集中于，针对60w/m的峰值，即10次/ms，对于普通的物理介质存储方案（机械硬盘）来说，该I/O要求是很难满足的。因此，若要考虑物理介质来存储，固态硬盘是一个选择方案，具体有待于测试。

附录：

测试程序核心源代码：

  1 import java.sql.Date;
  2 import java.math.BigDecimal;
  3 
  4 public class Transaction {
  5     private Date tradedate; 
  6     private String symbol;
  7     private String symbolName;
  8     private String trdmintime;
  9     private BigDecimal startprice;
 10     private BigDecimal highprice;
 11     private BigDecimal lowprice;
 12     private BigDecimal endprice;
 13     private BigDecimal change;
 14     private BigDecimal changeratio;
 15     private BigDecimal minvolume;
 16     private BigDecimal minamout;
 17     private long unix;
 18     private String market;
 19     
 20     public Transaction(Date tradedate,
 21                         String symbol,
 22                         String symbolName,
 23                         String trdmintime,
 24                         BigDecimal startprice,
 25                         BigDecimal highprice,
 26                         BigDecimal lowprice,
 27                         BigDecimal endprice,
 28                         BigDecimal change,
 29                         BigDecimal changeratio,
 30                         BigDecimal minvolume,
 31                         BigDecimal minamout,
 32                         long unix,
 33                         String market)
 34     {
 35         this.symbol = symbol;
 36         this.symbolName = symbolName;
 37         this.trdmintime = trdmintime;
 38         this.startprice = startprice;
 39         this.highprice = highprice;
 40         this.lowprice = lowprice;
 41         this.endprice = endprice;
 42         this.change = change;
 43         this.changeratio = changeratio;
 44         this.minvolume = minvolume;
 45         this.minamout = minamout;
 46         this.unix = unix;
 47         this.market = market;
 48     }
 49 
 50     public void setTradedate(Date tradedate) {
 51         this.tradedate = tradedate;
 52     }
 53 
 54     public void setSymbol(String symbol) {
 55         this.symbol = symbol;
 56     }
 57 
 58     public void setSymbolName(String symbolName) {
 59         this.symbolName = symbolName;
 60     }
 61 
 62     public void setTrdmintime(String trdmintime) {
 63         this.trdmintime = trdmintime;
 64     }
 65 
 66     public void setStartprice(BigDecimal startprice) {
 67         this.startprice = startprice;
 68     }
 69 
 70     public void setHighprice(BigDecimal highprice) {
 71         this.highprice = highprice;
 72     }
 73 
 74     public void setLowprice(BigDecimal lowprice) {
 75         this.lowprice = lowprice;
 76     }
 77 
 78     public void setEndprice(BigDecimal endprice) {
 79         this.endprice = endprice;
 80     }
 81 
 82     public void setChange(BigDecimal change) {
 83         this.change = change;
 84     }
 85 
 86     public void setChangeratio(BigDecimal changeratio) {
 87         this.changeratio = changeratio;
 88     }
 89 
 90     public void setMinvolume(BigDecimal minvolume) {
 91         this.minvolume = minvolume;
 92     }
 93 
 94     public void setMinamout(BigDecimal minamout) {
 95         this.minamout = minamout;
 96     }
 97 
 98     public void setUnix(long unix) {
 99         this.unix = unix;
100     }
101 
102     public void setMarket(String market) {
103         this.market = market;
104     }
105 
106     public Date getTradedate() {
107         return tradedate;
108     }
109 
110     public String getSymbol() {
111         return symbol;
112     }
113 
114     public String getSymbolName() {
115         return symbolName;
116     }
117 
118     public String getTrdmintime() {
119         return trdmintime;
120     }
121 
122     public BigDecimal getStartprice() {
123         return startprice;
124     }
125 
126     public BigDecimal getHighprice() {
127         return highprice;
128     }
129 
130     public BigDecimal getLowprice() {
131         return lowprice;
132     }
133 
134     public BigDecimal getEndprice() {
135         return endprice;
136     }
137 
138     public BigDecimal getChange() {
139         return change;
140     }
141 
142     public BigDecimal getChangeratio() {
143         return changeratio;
144     }
145 
146     public BigDecimal getMinvolume() {
147         return minvolume;
148     }
149 
150     public BigDecimal getMinamout() {
151         return minamout;
152     }
153 
154     public long getUnix() {
155         return unix;
156     }
157 
158     public String getMarket() {
159         return market;
160     }
161 
162 }

Class Transaction

  1 import java.sql.*;
  2 import java.math.BigDecimal;
  3 import java.math.RoundingMode;
  4 
  5 public class Test {
  6     public static int PREFIX = 1000;        // 批处理量
  7     public static int FIX = 600;            // 操作数据 K
  8     private Connection conn;
  9     private PreparedStatement pstm;
 10     private String sql;
 11     private int count;
 12     
 13     public static void main(String[] args) {
 14         // TODO Auto-generated method stub
 15         
 16         Transaction ts = new Transaction(null,
 17                                         "",
 18                                         "",
 19                                         "010000",
 20                                         new BigDecimal(15.857).setScale(3, RoundingMode.HALF_UP),
 21                                         new BigDecimal(18.550).setScale(3, RoundingMode.HALF_UP),
 22                                         new BigDecimal(13.147).setScale(3, RoundingMode.HALF_UP),
 23                                         new BigDecimal(16.383).setScale(3, RoundingMode.HALF_UP),
 24                                         new BigDecimal(0.151).setScale(3, RoundingMode.HALF_UP),
 25                                         new BigDecimal(1.550).setScale(3, RoundingMode.HALF_UP),
 26                                         new BigDecimal(5000000).setScale(3, RoundingMode.HALF_UP),
 27                                         new BigDecimal(500000000).setScale(3, RoundingMode.HALF_UP),
 28                                         System.currentTimeMillis(),
 29                                         "SSE");
 30         
 31         Test test = new Test();
 32         int symbolData = 100000;
 33         test.initMySQL();
 34         
 35         long start = test.getRunTime();
 36         for(int i = 0; i < Test.FIX*1000; i++) {
 37             ts.setTradedate(new Date(System.currentTimeMillis()));
 38             ts.setSymbol(Integer.toString(symbolData));
 39             symbolData++ ;
 40             ts.setSymbolName("中国银行");
 41             ts.setUnix(ts.getUnix()+1);
 42             test.insertData(ts);
 43         }
 44         long end = test.getRunTime();
 45         System.out.println("写入速率为： " + Test.FIX*1000*1000/(end-start));
 46         
 47         test.down();
 48     }
 49     
 50     
 51     public void initMySQL()
 52     {
 53         String driver = "com.mysql.jdbc.Driver";
 54         String url = "jdbc:mysql://10.1.120.37:3306/hdfs";
 55         String user = "root";
 56         String password = "";
 57         
 58         try {
 59             Class.forName(driver);
 60             conn = DriverManager.getConnection(url, user, password);
 61             if (!conn.isClosed())
 62                 System.out.println("Start MySQL!");
 63         } catch (Exception e) {
 64             e.printStackTrace();
 65         }
 66         
 67         count = 0;
 68         sql = "insert into transaction" + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 69         try {
 70             pstm = conn.prepareStatement(sql);
 71             conn.setAutoCommit(false);
 72         } catch (SQLException e) {
 73             e.printStackTrace();
 74         }
 75     }
 76     
 77     
 78     public void insertData(Transaction ts)
 79     {
 80         try {
 81             pstm.setDate(1, ts.getTradedate());
 82             pstm.setString(2,  ts.getSymbol());
 83             pstm.setString(3, ts.getSymbolName());
 84             pstm.setString(4, ts.getTrdmintime());
 85             pstm.setBigDecimal(5, ts.getStartprice());
 86             pstm.setBigDecimal(6, ts.getHighprice());
 87             pstm.setBigDecimal(7, ts.getLowprice());
 88             pstm.setBigDecimal(8, ts.getEndprice());
 89             pstm.setBigDecimal(9, ts.getChange());
 90             pstm.setBigDecimal(10, ts.getChangeratio());
 91             pstm.setBigDecimal(11, ts.getMinvolume());
 92             pstm.setBigDecimal(12, ts.getMinamout());
 93             pstm.setLong(13, ts.getUnix());
 94             pstm.setString(14, ts.getMarket());
 95     
 96             pstm.executeUpdate();
 97             count++;
 98             if (count == Test.PREFIX) {
 99                 conn.commit();
100                 conn.setAutoCommit(false);
101                 count = 0;
102             }
103                         
104         } catch (SQLException e) {
105             try {
106                 conn.rollback();
107             } catch (SQLException e1) {
108                 // TODO Auto-generated catch block
109                 e1.printStackTrace();
110             }
111             e.printStackTrace();
112         }
113     }
114     
115     
116     public long getRunTime()
117     {
118         return System.currentTimeMillis();
119     }
120     
121     
122     public void down()
123     {
124         try {
125             if (!conn.isClosed()) {
126                 conn.close();
127                 System.out.println("Close MySQL!");
128             }
129         } catch (Exception e) {
130             e.printStackTrace();
131         }
132     }
133 
134 }

Class Test

　　作者：志青云集
　　出处：http://www.cnblogs.com/lyssym/p/4821490.html
　　如果，您认为阅读这篇博客让您有些收获，不妨点击一下右下角的【推荐】。
　　如果，您希望更容易地发现我的新博客，不妨点击一下左下角的【关注我】。
　　如果，您对我的博客所讲述的内容有兴趣，请继续关注我的后续博客，我是【志青云集】。
　　本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。