mysql

浏览数：33 / 时间：2015年06月12日

实验步骤
同一台电脑装了两个mysql，端口号不同，mysql5.6安装好之后只有一个my-default的文件，将其中添加一些错误内容，改mysql依然可以启动成功
，说明使用的不是该文件，将my-default.ini改名为my.ini，再向其中添加一些错误内容，则启动失败，则说明，mysql5.6启动时读取的配置文件
名称默认为my.ini
使用框架springmvc+spring+hibernate，但是插入数据时使用的JdbcDaoSupport中的jdbcTemplete，所以在这里hibernate也算是没有使用到，
由于要获取同一个connection对象，所以在这里封装了个方法

public static final ThreadLocal<Connection> cs = new ThreadLocal<Connection>();
    
    public static Connection getConnection(DataSource dataSource) throws SQLException{
        Connection c = cs.get();
        if(null==c){
            c=dataSource.getConnection();
            cs.set(c);
        }
        return c;
    }

dao插入数据代码

package com.h3c.itac.alarm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.h3c.itac.alarm.po.SyslogAlarm;
import com.h3c.itac.util.ConnectionUtil;

@Repository
public class SyslogAlarmDao extends JdbcDaoSupport{
    @Resource
    public void setDatasource(DataSource dataSource){
        this.setDataSource(dataSource);
    }
    
    private long count = 0L;
    String sql1="";
    String sql2="";
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;
    Connection con = null;
    @PostConstruct
    public void set() throws SQLException{
        sql1 = "insert into alarm(id,adesk_alarm_id,serial_no,level,source,type,device_id,create_time,upload_time,order_id,title,location,customer_id)"
                +"values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        sql2 = "insert into syslogalarm values(?,?,?,?,?,?,?,?)";
        con = ConnectionUtil.getConnection(this.getDataSource());
//        con.setAutoCommit(false);
        ps1 = con.prepareStatement(sql1);
        ps2 = con.prepareStatement(sql2);
        PreparedStatement ps3 = con.prepareStatement("select max(id) from alarm");
        ResultSet rs = ps3.executeQuery();
        while(rs.next()){
            count = rs.getLong(1);
        }
        System.out.println("alarm id最大值为 ："+count);
    }
    public void insert(SyslogAlarm sAlarm) throws SQLException{
        count++;
        ps1.setLong(1,count);
        ps1.setLong(2,sAlarm.getAlarm().getAdeskAlarmId());
        ps1.setString(3,sAlarm.getAlarm().getSerialNo());
        ps1.setInt(4, sAlarm.getAlarm().getLevel());
        ps1.setInt(5, sAlarm.getAlarm().getSource());
        ps1.setInt(6,sAlarm.getAlarm().getType());
        ps1.setLong(7, sAlarm.getAlarm().getDevice().getId());
        ps1.setLong(8, sAlarm.getAlarm().getCreateTime());
        ps1.setLong(9, sAlarm.getAlarm().getUploadTime());
        ps1.setLong(10,count);
        ps1.setString(11, sAlarm.getAlarm().getTitle());
        ps1.setString(12, sAlarm.getAlarm().getLocation());
        ps1.setLong(13,sAlarm.getAlarm().getDevice().getCustomer().getId());
        
        ps2.setLong(1,count);
        ps2.setString(2, sAlarm.getDescription());
        ps2.setString(4,sAlarm.getReason());
        ps2.setString(5,sAlarm.getAdvise());
        ps2.setString(3, sAlarm.getVariables());
        ps2.setString(6, sAlarm.getLogContent());
        ps2.setLong(7,sAlarm.getPriginalLogId());
        ps2.setLong(8, count);
        
        ps1.addBatch();
        ps2.addBatch();
        if(count%300==0){
            excuteRemainderBatchSQL();
        }
    }
    
    public void excuteRemainderBatchSQL() throws SQLException{
        ps1.executeBatch();
        ps1.clearBatch();
        ps2.executeBatch();
        ps2.clearBatch();
//        con.commit();
//        con.setAutoCommit(true);
    }
}

测试类

package com.h3c.itac.alarm.dao;

import java.sql.SQLException;
import java.util.Date;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.h3c.itac.alarm.po.Alarm;
import com.h3c.itac.alarm.po.SyslogAlarm;
import com.h3c.itac.customer.Customer;
import com.h3c.itac.device.Device;

public class TestSyslogAlarmDao {

    public SyslogAlarmDao getSyslogAlarmDao() {
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        return (SyslogAlarmDao) ac.getBean("syslogAlarmDao");
    }
    @Test
    public void init(){
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
    }
    /**每次清库1800
     * 2,自动提交（2.388,2.388,2.871,2.809,2.683）平均2.6278
     * 1，手动提交(2.825,2.717,2.294,2.076,2.169)平均2.4162
     * 不清库,每次1800
     * 2,自动提交（2.981,3.261,3.433,1.888,1.935）
     * 1，手动提交（2.091，2.293，3.354，1.95，2.371，）
     * 5000
     * 2，自动提交（6.397,5.43,6.584,6.35,4.853）                     平均5.9228
     * 1,手动提交(4.166，5.913，4.634，2.762，4.962) 平均4.4874
     */
    @Test
    public void testInsert() throws SQLException, ClassNotFoundException{
        double start = System.currentTimeMillis();
        SyslogAlarmDao sad = this.getSyslogAlarmDao();
        for(int i=1;i<5001;i++){
            Customer c = new Customer();
            c.setId(1L);
            Device d = new Device();
            d.setId(1L);
            d.setCustomer(c);
            
            Alarm alarm = new Alarm();
            alarm.setAdeskAlarmId((long) i);
            alarm.setCreateTime(new Date().getTime());
            alarm.setDevice(d);
            alarm.setLevel(1);
            alarm.setLocation("location "+i);
            alarm.setSerialNo("serialno "+i);
            alarm.setSource(1);
            alarm.setTitle("title "+i);
            alarm.setType(0);
            alarm.setUploadTime(new Date().getTime());
            
            SyslogAlarm sa = new SyslogAlarm();
            sa.setAdvise("advise "+i);
            sa.setAlarm(alarm);
            sa.setDescription("description "+i);
            sa.setLogContent("logcontent "+i);
            sa.setPriginalLogId(1);
            sa.setReason("reason "+i);
            sa.setVariables("variable "+1);
            
            sad.insert(sa);
        }
        sad.excuteRemainderBatchSQL();
        double end300 = System.currentTimeMillis();
        System.out.println((end300-start)/1000);
    }
    
}

如果只是将my-default.ini文件改名为my.ini文件，而不该内部配置，则在执行testInsert的时候速度超慢，即便是插入600条也需要将近二十多秒

然后找来其他版本的my.ini文件，替换掉改名后的my.ini文件，则速度比较快,可以想到问题就出在之前版本的my.ini和改名后的my.ini的文件配置内容上

然后用Compare对比改名前后的my.ini文件内容使用Compare辅助工具进行测试，（使用Compare对比在改名的my.ini文件中不存在的内容，将之前版本的my.ini文件内容逐行添加到改名后文件中，停止mysql服务，保存修改后文件，清空数据库，运行testInsert方法，观察运行时间，如果这条没有效果，继续添加下条配置）

后来就发现有一个配置起了作用

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 2

一、当该配置为2的时候，代码还是如上，自动提交事务，则插入数据速度明显提升，和使用之前版本的my.ini文件几乎是相同的，

二、当该配置为1的时候，代码依然如上，自动提交事务，则插入数据速度又变得明显超慢

三、当该配置依然为1的时候，自动提交事务，即将上述代码con.setAutoCommit(false);和con.commit();的注释去掉，则插入数据也有明显提升，和第一种情况差不多，当插入数据过多时比第一种要快多一些

四、当该配置为2的时候，自动提交事务，即将上述代码con.setAutoCommit(false);和con.commit();的注释去掉，插入速度也会有明显提升。

五、去掉innodb_flush_log_at_trx_commit = 2配置，设置为手动提交事务，插入速度也会明显提升

六、去掉innodb_flush_log_at_trx_commit = 2配置，自动提交事务，速度又是明显超慢。

综上，如果用的是mysql5.6版本，要更改默认配置，目前我们采用的是将my-default.ini文件名改成，my.ini文件名,这样就可以在其中进行文件配置修改

通过上面测试结果也可看出，不管是配置为1或者2，只要是手动提交事务，则都可一提高插入速度，这个是在jdbc中进行的测试，至于hibernate需要就不清楚了

my.ini

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]

# generic configuration options
port        = 3306
socket        = /tmp/mysql.sock
character-set-server=utf8

innodb_print_all_deadlocks=1

innodb_locks_unsafe_for_binlog=1

expire_logs_days=2

event_scheduler=1

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 2