DB Benchmark

[codefollower]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Random;
import java.util.concurrent.CountDownLatch;

//运行这个例子要在MySQL和PostgreSQL中存在一个test数据库
public class Benchmark {
    static String url = "jdbc:mysql://localhost:3306/test?user=test&password=test";
    // static String url = "jdbc:postgresql://localhost/test?user=test&password=test";
    static Random random = new Random();
    static CountDownLatch latch;

    public static void main(String[] args) throws Exception {
        benchmark();
    }

    static Connection getConnection() throws Exception {
        return DriverManager.getConnection(url);
    }

    static class MyThread extends Thread {
        Statement stmt;
        Connection conn;
        long read_time;
        long randow_read_time;
        long write_time;
        int start;
        int end;

        MyThread(int start, int count) throws Exception {
            super("MyThread-" + start);
            conn = getConnection();
            stmt = conn.createStatement();
            this.start = start;
            this.end = start + count;
        }

        void write() throws Exception {
            long t1 = System.currentTimeMillis();
            for (int i = start; i < end; i++) {
                String sql = "INSERT INTO test(f1, f2) VALUES(" + i + "," + i * 10 + ")";
                stmt.executeUpdate(sql);
            }

            long t2 = System.currentTimeMillis();
            write_time = t2 - t1;
            System.out.println(getName() + " write end, time=" + write_time + " ms");
        }

        void read(boolean randow) throws Exception {
            long t1 = System.currentTimeMillis();
            for (int i = start; i < end; i++) {
                ResultSet rs;
                if (!randow)
                    rs = stmt.executeQuery("SELECT * FROM test where f1 = " + i);
                else
                    rs = stmt.executeQuery("SELECT * FROM test where f1 = " + random.nextInt(end));
                while (rs.next()) {
                    // System.out.println("f1=" + rs.getInt(1) + " f2=" + rs.getLong(2));
                }
            }

            long t2 = System.currentTimeMillis();

            if (randow)
                randow_read_time = t2 - t1;
            else
                read_time = t2 - t1;
            if (randow)
                System.out.println(getName() + " randow read end, time=" + randow_read_time + " ms");
            else
                System.out.println(getName() + "  read end, time=" + read_time + " ms");
        }

        @Override
        public void run() {
            try {
                write();
                read(false);
                read(true);
                stmt.close();
                conn.close();
                latch.countDown();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    static void benchmark() throws Exception {
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("DROP TABLE IF EXISTS test");
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS test (f1 int primary key, f2 real)");
        stmt.close();
        conn.close();

        int threadsCount = Runtime.getRuntime().availableProcessors();
        int loop = 1000;
        latch = new CountDownLatch(threadsCount);

        MyThread[] threads = new MyThread[threadsCount];
        for (int i = 0; i < threadsCount; i++) {
            threads[i] = new MyThread(i * loop, loop);
        }

        for (int i = 0; i < threadsCount; i++) {
            threads[i].start();
        }

        latch.await();

        long write_sum = 0;
        for (int i = 0; i < threadsCount; i++) {
            write_sum += threads[i].write_time;
        }

        long read_sum = 0;
        for (int i = 0; i < threadsCount; i++) {
            read_sum += threads[i].read_time;
        }
        long randow_read_sum = 0;
        for (int i = 0; i < threadsCount; i++) {
            randow_read_sum += threads[i].randow_read_time;
        }

        System.out.println();
        System.out.println("threads: " + threadsCount + ", loop: " + loop + ", rows: " + (threadsCount * loop));
        System.out.println("==========================================================");
        System.out.println("write_sum=" + write_sum + ", avg=" + (write_sum / threadsCount) + " ms");
        System.out.println("read_sum=" + read_sum + ", avg=" + (read_sum / threadsCount) + " ms");
        System.out.println("randow_read_sum=" + randow_read_sum + ", avg=" + (randow_read_sum / threadsCount) + " ms");
    }
}

[liuanxin]

$ cat /etc/issue
Debian GNU/Linux stretch/sid

$ psql --version
psql (PostgreSQL) 9.5.1
使用的 jdbc 包版本是 postgresql-9.4.1208.jre7.jar

$ mysql --version
mysql Ver 15.1 Distrib 10.0.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
使用的 jdbc 包版本是 mysql-connector-java-5.1.38.jar

$ java -version
java version "1.8.0_60"
Java(TM) SE Runtime Environment (build 1.8.0_60-b27)
Java HotSpot(TM) 64-Bit Server VM (build 25.60-b23, mixed mode)

mariaDB 的结果
MyThread-3000 write end, time=101805 ms
MyThread-1000 write end, time=101949 ms
MyThread-0 write end, time=102006 ms
MyThread-3000 read end, time=191 ms
MyThread-2000 write end, time=102041 ms
MyThread-1000 read end, time=257 ms
MyThread-3000 randow read end, time=234 ms
MyThread-0 read end, time=271 ms
MyThread-2000 read end, time=272 ms
MyThread-1000 randow read end, time=161 ms
MyThread-0 randow read end, time=133 ms
MyThread-2000 randow read end, time=137 ms

threads: 4, loop: 1000, rows: 4000

write_sum=407801, avg=101950 ms
read_sum=991, avg=247 ms
randow_read_sum=665, avg=166 ms

pg 的结果
MyThread-3000 write end, time=27189 ms
MyThread-2000 write end, time=27210 ms
MyThread-0 write end, time=27225 ms
MyThread-1000 write end, time=27236 ms
MyThread-2000 read end, time=349 ms
MyThread-3000 read end, time=390 ms
MyThread-0 read end, time=417 ms
MyThread-1000 read end, time=431 ms
MyThread-2000 randow read end, time=301 ms
MyThread-3000 randow read end, time=309 ms
MyThread-0 randow read end, time=331 ms
MyThread-1000 randow read end, time=311 ms

threads: 4, loop: 1000, rows: 4000

write_sum=108860, avg=27215 ms
read_sum=1587, avg=396 ms
randow_read_sum=1252, avg=313 ms

mariaDB 真心慢!!!

[codefollower]

@liuanxin
我昨天后来测了MariaDB10.1，修改一下data\my.ini，加入innodb_flush_log_at_trx_commit=2重新测就很快了。默认也是像MySQL一样，innodb_flush_log_at_trx_commit的值是1，每次insert一条新记录就会调fsync，所以会很慢。