Innodb (MYSQL 5.0) Poor Performance on many threads
Hi
I've done personal test with the capabilities of mysql innodb, and found
with 10 threads you could do around 1200-1300 rows/seconds on the same table.
with 100 threads you could do around 2500-2600 rows/seconds on the same table.
with 200 threads ....innodb started to decrease the performance
with 500 thread ....innodb is very bad
Is it a bug or what?
SUBJECT: 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 |
+----------+---------------------+