List:General Discussion« Previous MessageNext Message »
From:Ady Wicaksono Date:March 7 2006 5:36am
Subject:Speed of InnoDB Insert with autocommit=1
View as plain text  
Speed of InnoDB Insert with autocommit=1;

Personal Test !

Linux : Fedora Core 4, kernel 2.6.11

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0

Connection id:          2349
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.18-max
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 41 days 13 hours 34 min 36 sec

Threads: 1  Questions: 3112491  Slow queries: 0  Opens: 1  Flush tables: 
1  Open tables: 1148  Queries per second avg: 0.867
--------------

CREATE TABLE `sbtest` (
  `pad` int(11) default NULL,
  `id` int(11) NOT NULL,
  `k` int(11) default NULL,
  `c` int(11) default NULL,
   t   timestamp not null default current_timestamp
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Use sysbencdh 0.4.4-sp

Edit  tests/oltp/sb_oltp.c, change

snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s values(?,?,?,?)",

to

snprintf(query, MAX_QUERY_LEN, "INSERT INTO %s (pad,id,k,c) 
values(?,?,?,?)",


Do

./sysbench --test=oltp --oltp-table-size=10  --oltp-table-name=sbtest 
--oltp-test-mode=nontrx
   --oltp-nontrx-mode=insert --max-requests=0 --num-threads=10 
--mysql-db=test run
   --mysql-password=sbtest --mysql-user=sbtest --mysql-host=127.0.0.1

10 Threads, could insert around 1200 rows/seconds (autocommit=1);

mysql> SELECT COUNT(*), t FROM sbtest GROUP by t;

+----------+---------------------+
| count(*) | t                   |
+----------+---------------------+
|      946 | 2006-03-07 12:08:16 |
|     1199 | 2006-03-07 12:08:17 |
|     1210 | 2006-03-07 12:08:18 |
|     1220 | 2006-03-07 12:08:19 |
|     1158 | 2006-03-07 12:08:20 |
|     1234 | 2006-03-07 12:08:21 |
|     1166 | 2006-03-07 12:08:22 |
|     1169 | 2006-03-07 12:08:23 |
|     1211 | 2006-03-07 12:08:24 |
|     1173 | 2006-03-07 12:08:25 |
|     1221 | 2006-03-07 12:08:26 |
|     1222 | 2006-03-07 12:08:27 |
|     1229 | 2006-03-07 12:08:28 |
|     1221 | 2006-03-07 12:08:29 |
|     1185 | 2006-03-07 12:08:30 |
|     1192 | 2006-03-07 12:08:31 |
|     1169 | 2006-03-07 12:08:32 |
|     1223 | 2006-03-07 12:08:33 |
|     1223 | 2006-03-07 12:08:34 |
|     1216 | 2006-03-07 12:08:35 |
|     1217 | 2006-03-07 12:08:36 |
+----------+---------------------+

ulimit -s 192;./sysbench --test=oltp --oltp-table-size=10  
--oltp-table-name=sbtest
   --oltp-test-mode=nontrx --oltp-nontrx-mode=insert --max-requests=0
   --num-threads=100 --mysql-db=test run --mysql-password=sbtest 
--mysql-user=sbtest --mysql-host=127.0.0.1

100 Threads, could insert around 2500 rows/seconds (autocommit=1);

mysql> SELECT COUNT(*), t FROM sbtest GROUP by t;
+----------+---------------------+
| COUNT(*) | t                   |
+----------+---------------------+
|      100 | 2006-03-07 12:13:58 |
|      319 | 2006-03-07 12:13:59 |
|     2302 | 2006-03-07 12:14:00 |
|     2256 | 2006-03-07 12:14:01 |
|     2494 | 2006-03-07 12:14:02 |
|     2804 | 2006-03-07 12:14:03 |
|     2970 | 2006-03-07 12:14:04 |
|     2686 | 2006-03-07 12:14:05 |
|     2949 | 2006-03-07 12:14:06 |
|     2954 | 2006-03-07 12:14:07 |
|     2835 | 2006-03-07 12:14:08 |
|     2829 | 2006-03-07 12:14:09 |
|     2785 | 2006-03-07 12:14:10 |
|     2946 | 2006-03-07 12:14:11 |
|     2350 | 2006-03-07 12:14:12 |
|     2555 | 2006-03-07 12:14:13 |
|     2611 | 2006-03-07 12:14:14 |
|     2753 | 2006-03-07 12:14:15 |
|     2984 | 2006-03-07 12:14:16 |
|     3098 | 2006-03-07 12:14:17 |
|     2911 | 2006-03-07 12:14:18 |
|     2788 | 2006-03-07 12:14:19 |
|     2754 | 2006-03-07 12:14:20 |
|     2712 | 2006-03-07 12:14:21 |
|     2798 | 2006-03-07 12:14:22 |
|     2974 | 2006-03-07 12:14:23 |
|     2451 | 2006-03-07 12:14:24 |
|     2901 | 2006-03-07 12:14:25 |
|     3144 | 2006-03-07 12:14:26 |
|     2447 | 2006-03-07 12:14:27 |
|     2471 | 2006-03-07 12:14:28 |
|     2106 | 2006-03-07 12:14:29 |
|     2906 | 2006-03-07 12:14:30 |
|     2929 | 2006-03-07 12:14:31 |
|     2853 | 2006-03-07 12:14:32 |
|     2675 | 2006-03-07 12:14:33 |
+----------+---------------------+


ulimit -s 192;

./sysbench --test=oltp --oltp-table-size=10  --oltp-table-name=sbtest 
--oltp-test-mode=nontrx
    --oltp-nontrx-mode=insert --max-requests=0 --num-threads=200 
--mysql-db=test run
    --mysql-password=sbtest --mysql-user=sbtest --mysql-host=127.0.0.1

mysql> SELECT COUNT(*), t FROM sbtest GROUP by t;
+----------+---------------------+
| COUNT(*) | t                   |
+----------+---------------------+
|      564 | 2006-03-07 12:23:26 |
|      294 | 2006-03-07 12:23:27 |
|      949 | 2006-03-07 12:23:28 |
|      815 | 2006-03-07 12:23:29 |
|     1152 | 2006-03-07 12:23:30 |
|     1120 | 2006-03-07 12:23:31 |
|     1201 | 2006-03-07 12:23:32 |
|      938 | 2006-03-07 12:23:33 |
|     1322 | 2006-03-07 12:23:34 |
|      672 | 2006-03-07 12:23:35 |
|      655 | 2006-03-07 12:23:36 |
|     1104 | 2006-03-07 12:23:37 |
|      924 | 2006-03-07 12:23:38 |
|      846 | 2006-03-07 12:23:39 |
|      381 | 2006-03-07 12:23:40 |
|     1062 | 2006-03-07 12:23:41 |
|      764 | 2006-03-07 12:23:42 |
|      537 | 2006-03-07 12:23:43 |
|     1660 | 2006-03-07 12:23:44 |
|      678 | 2006-03-07 12:23:45 |
|     1112 | 2006-03-07 12:23:46 |
|      836 | 2006-03-07 12:23:47 |
|      431 | 2006-03-07 12:23:48 |
|      682 | 2006-03-07 12:23:49 |
|     1197 | 2006-03-07 12:23:50 |
|      424 | 2006-03-07 12:23:51 |
|     1096 | 2006-03-07 12:23:52 |
|     1277 | 2006-03-07 12:23:53 |
|      895 | 2006-03-07 12:23:54 |
|      750 | 2006-03-07 12:23:55 |
|      881 | 2006-03-07 12:23:56 |
|      968 | 2006-03-07 12:23:57 |
+----------+---------------------+

ulimit -s 192;

./sysbench --test=oltp --oltp-table-size=10  --oltp-table-name=sbtest 
--oltp-test-mode=nontrx
    --oltp-nontrx-mode=insert --max-requests=0 --num-threads=500 
--mysql-db=test run
    --mysql-password=sbtest --mysql-user=sbtest --mysql-host=127.0.0.1

mysql> SELECT COUNT(*), t FROM sbtest GROUP by t;
+----------+---------------------+
| COUNT(*) | t                   |
+----------+---------------------+
|      506 | 2006-03-07 12:17:14 |
|      848 | 2006-03-07 12:17:15 |
|      195 | 2006-03-07 12:17:16 |
|       34 | 2006-03-07 12:17:17 |
|       30 | 2006-03-07 12:17:18 |
|       36 | 2006-03-07 12:17:19 |
|       53 | 2006-03-07 12:17:20 |
|       40 | 2006-03-07 12:17:21 |
|       72 | 2006-03-07 12:17:22 |
|       19 | 2006-03-07 12:17:23 |
|       47 | 2006-03-07 12:17:24 |
|       27 | 2006-03-07 12:17:25 |
|       18 | 2006-03-07 12:17:26 |
|       18 | 2006-03-07 12:17:27 |
|       15 | 2006-03-07 12:17:28 |
|       12 | 2006-03-07 12:17:29 |
|       94 | 2006-03-07 12:17:30 |
|       30 | 2006-03-07 12:17:31 |
|       14 | 2006-03-07 12:17:32 |
|       36 | 2006-03-07 12:17:33 |
|       35 | 2006-03-07 12:17:34 |
|       57 | 2006-03-07 12:17:35 |
|       23 | 2006-03-07 12:17:36 |
|       58 | 2006-03-07 12:17:37 |
|       76 | 2006-03-07 12:17:38 |
|      100 | 2006-03-07 12:17:39 |
|       50 | 2006-03-07 12:17:40 |
|       38 | 2006-03-07 12:17:41 |
|       68 | 2006-03-07 12:17:42 |
|       99 | 2006-03-07 12:17:43 |
|      126 | 2006-03-07 12:17:44 |
|      297 | 2006-03-07 12:17:45 |
|       52 | 2006-03-07 12:17:46 |
|      190 | 2006-03-07 12:17:47 |
|      319 | 2006-03-07 12:17:48 |
|      349 | 2006-03-07 12:17:49 |
|       90 | 2006-03-07 12:17:50 |
+----------+---------------------+

Thread
Speed of InnoDB Insert with autocommit=1Ady Wicaksono7 Mar