数据库选型之MySQL(固态硬盘)

  刘勇    Email: lyssym@sina.com

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

简介

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

测试环境

       硬件环境

       10.1.120.34：Intel Core I5-4590, 主频：3.30G,  内存：16G， 有固态硬盘

       软件环境：

       10.1.120.34: Cent OS 6.5,  MySQL 5.6.26 (社区版)

       表结构：

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

       配置文件：即数据库配置文件，见/etc/my.cnf

 1 # read_rnd_buffer_size = 2M
 2 datadir=/var/lib/mysql
 3 socket=/var/lib/mysql/mysql.sock
 4 innodb_flush_log_at_trx_commit = 2
 5 innodb_autoinc_lock_mode = 2
 6 query_cache_type = 1
 7 query_cache_size = 20M
 8 innodb_buffer_pool_size = 2G
 9 innodb_flush_method = O_DSYNC
10 # Disabling symbolic-links is recommended to prevent assorted security risks
11 symbolic-links=0
my.cnf

性能测试

       事务处理

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

       本文采用写入多条数据执行事务处理。因为10.1.120.34上采用固态硬盘作为存储介质，其安装有MySQL，根据应用场景，第一种从远程访问，即测试主机10.1.25.50访问MySQL，第二种从本地访问10.1.120.34访问MySQL。以下分别从事务处理条目为1000、2000和3000下展开测试。

       以1000条数据为基础执行事务处理，结果见表-1。

表-1  1000条下事务处理MySQL测试结果

节点

数据库IP

数据量（K）

 平均写入速率（条/s）
远程节点 10.1.120.34 60 2328
远程节点 10.1.120.34 100 2366
远程节点 10.1.120.34 600 2372
本地节点 10.1.120.34 60 14936
本地节点 10.1.120.34 100 16526
本地节点 10.1.120.34 600 21169

       以2000条数据为基础执行事务处理，结果见表-2。

表-2  2000条下事务处理MySQL测试结果

节点

数据库IP

数据量（K）

 平均写入速率（条/s）
远程节点 10.1.120.34 60 2318
远程节点 10.1.120.34 100 2333
远程节点 10.1.120.34 600 2320
本地节点 10.1.120.34 60 12714
本地节点 10.1.120.34 100 17914
本地节点 10.1.120.34 600 20214

        以3000条数据为基础执行事务处理，结果见表-3。

表-3  3000条下事务处理MySQL测试结果

节点

数据库IP

数据量（K）

      平均写入速率     （条/s）
　　远程节点 　　10.1.120.34 　　      60 　　   2319
　　远程节点 　　10.1.120.34 　　    100 　　   2377
　　远程节点 　　10.1.120.34 　　    600 　　   2429
　　本地节点 　　10.1.120.34 　　     60 　　  11202
　　本地节点 　　10.1.120.34 　　    100 　　  17587
　　本地节点 　　10.1.120.34 　　    600 　　  18577

       小结

       从表1-3可知：1）在远程节点访问MySQL，即使采用固态硬盘，其速率还是较慢，无法满足需求；2）在本地节点，访问MySQL，则能够满足高频生产的需求。

       批处理

       为比较批处理方式和事务处理方式的性能，本文针对其进行比较测试。

       每1000条数据，执行一次批处理，测试结果如表-4。

表-4  1000条下批处理与事务处理MySQL测试结果

方式

数据库IP

数据量（K）

 平均写入速率（条/s）
批处理 10.1.120.34 60 15974
批处理 10.1.120.34 100 16328
批处理 10.1.120.34 600 17633
事务处理 10.1.120.34 60 14936
事务处理 10.1.120.34 100 16526
事务处理 10.1.120.34 600 21169

       每2000条数据，执行一次批处理，测试结果如表-5。

表-5  2000条下批处理与事务处理MySQL测试结果

方式

数据库IP

数据量（K）

 平均写入速率（条/s）
批处理 10.1.120.34 60 14840
批处理 10.1.120.34 100 16310
批处理 10.1.120.34 600 17364
事务处理 10.1.120.34 60 12714
事务处理 10.1.120.34 100 17914
事务处理 10.1.120.34 600 20214
       每3000条数据，执行一次批处理，测试结果如表-6。

表-6  3000条下批处理与事务处理MySQL测试结果

节点

数据库IP

数据量（K）

 平均写入速率（条/s）
批处理 10.1.120.34 60 14641
批处理 10.1.120.34 100 16371
批处理 10.1.120.34 600 17593
事务处理 10.1.120.34 60 11202
事务处理 10.1.120.34 100 17587
事务处理 10.1.120.34 600 18577

         小结

        从表4-6测试结果来看，在写入数据量少时，批处理方式较事务方式速率快一些，但是随着访问数据量增加，事务处理方式稍微快一些。

 

总结

       从上述测试结果来看，以固态硬盘作为存储介质，在本地访问MySQL可以满足高频生产的需求。此外，鉴于高频生产高负载的需求，优先选择事务处理方式。

 

附录：

测试部分源代码：

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

 

posted on 2015-09-20 09:52  志青云集  阅读(5582)  评论(0)  编辑  收藏  举报

导航