每次都要去查 sysbench 的命令，很烦人，
这次做这个实验，留个底，等上了 好服务器后再来比对比对。。
又一次更新 sysbench 的命令，
纯insert 的方式测试。
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=192.168.1.215 --mysql-user=nextop --mysql-password=nextop --oltp-table-size=100000 --mysql-db=test --oltp-table-name=feedtest --num-threads=20 --max-requests=10000 --oltp-auto-inc=off --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=insert prepare
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=192.168.1.215 --mysql-user=nextop --mysql-password=nextop --oltp-table-size=100000 --mysql-db=test --oltp-table-name=feedtest --num-threads=20 --max-requests=10000 --oltp-auto-inc=off --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=insert prepare
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=192.168.1.215 --mysql-user=nextop --mysql-password=nextop --oltp-table-size=100000 --mysql-db=test --oltp-table-name=feedtest --num-threads=20 --max-requests=10000 --oltp-auto-inc=off --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=insert run
#sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=192.168.1.215 --mysql-user=nextop --mysql-password=nextop --oltp-table-size=10000000 --mysql-db=test --oltp-table-name=feedtest --num-threads=20 --max-requests=1000000 --oltp-auto-inc=off --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=insert run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Doing OLTP test.
Running non-transactional test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Not using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 1000000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 0
write: 1000206
other: 0
total: 1000206
transactions: 1000206 (19283.74 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1000206 (19283.74 per sec.)
other operations: 0 (0.00 per sec.)
Test execution summary:
total time: 51.8679s
total number of events: 1000206
total time taken by event execution: 1036.5332
per-request statistics:
min: 0.15ms
avg: 1.04ms
max: 785.77ms
approx. 95 percentile: 3.23ms
Threads fairness:
events (avg/stddev): 50010.3000/238.51
execution time (avg/stddev): 51.8267/0.00
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=192.168.1.215 --mysql-user=nextop --mysql-password=nextop --oltp-table-size=10000000 --mysql-db=test --oltp-table-name=feedtest --num-threads=20 --max-requests=1000000 --oltp-auto-inc=off --test=oltp --oltp-test-mode=nontrx --oltp-nontrx-mode=insert run
sysbench --mysql-table-engine=tokudb --debug=off --test=oltp --mysql-host=192.168.1.215 --mysql-user=nextop --mysql-password=nextop --oltp-table-size=10000000 --mysql-db=test --oltp-table-name=feedtest --num-threads=20 --max-requests=1000000 --oltp-auto-inc=off --test=oltp --mysql-engine-trx=yes --oltp-test-mode=nontrx --oltp-nontrx-mode=insert run
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.208 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=test --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.208 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=test --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
]# sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.208 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=test --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 100000 records in table 'sbtest'...
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.208 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=test --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 9 times)
Running the test with following options:
Number of threads: 10
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Not using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (146.10 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (2775.90 per sec.)
other operations: 20000 (292.20 per sec.)
Test execution summary:
total time: 68.4463s
total number of events: 10000
total time taken by event execution: 684.1316
per-request statistics:
min: 0.0254s
avg: 0.0684s
max: 0.8637s
approx. 95 percentile: 0.1011s
Threads fairness:
events (avg/stddev): 1000.0000/7.38
execution time (avg/stddev): 68.4132/0.01
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=test --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
FATAL: unable to connect to MySQL server, aborting...
FATAL: error 1049: Unknown database 'test'
FATAL: failed to connect to database server!
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.208 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=test --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 100000 records in table 'sbtest'...
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=100000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=10 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 9 times)
Running the test with following options:
Number of threads: 10
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Not using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (73.67 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (1399.71 per sec.)
other operations: 20000 (147.34 per sec.)
Test execution summary:
total time: 135.7422s
total number of events: 10000
total time taken by event execution: 1356.8151
per-request statistics:
min: 0.0797s
avg: 0.1357s
max: 0.6015s
approx. 95 percentile: 0.1627s
Threads fairness:
events (avg/stddev): 1000.0000/4.56
execution time (avg/stddev): 135.6815/0.03
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
ALERT: failed to execute MySQL query: `CREATE TABLE sbtest (id INTEGER UNSIGNED NOT NULL , k integer UNSIGNED DEFAULT '0' NOT NULL, c char(120) DEFAULT '' NOT NULL, pad char(60) DEFAULT '' NOT NULL, PRIMARY KEY (id) ) /*! ENGINE=NDB */`:
ALERT: Error 1050 Table 'sbtest' already exists
FATAL: failed to create test table
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex cleanup
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Dropping table 'sbtest'...
Done.
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 500000 records in table 'sbtest'...
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 1 times)
Running the test with following options:
Number of threads: 2
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Not using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (47.85 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (909.11 per sec.)
other operations: 20000 (95.70 per sec.)
Test execution summary:
total time: 208.9956s
total number of events: 10000
total time taken by event execution: 417.8435
per-request statistics:
min: 0.0266s
avg: 0.0418s
max: 0.2455s
approx. 95 percentile: 0.0511s
Threads fairness:
events (avg/stddev): 5000.0000/8.00
execution time (avg/stddev): 208.9218/0.01
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=ndbcluster --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex cleanup
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Dropping table 'sbtest'...
Done.
[root@fxstest ~]#
[root@fxstest ~]#
[root@fxstest ~]# sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex prepare
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 500000 records in table 'sbtest'...
[root@fxstest ~]# sysbench --mysql-table-engine=innodb --debug=off --test=oltp --mysql-host=10.4.6.226 --mysql-user=root --mysql-password=123456 --oltp-table-size=500000 --mysql-db=MAIN --oltp-table-name=sbtest --num-threads=2 --max-requests=10000 --oltp-auto-inc=off --oltp-test-mode=complex run
sysbench v0.4.8: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 1 times)
Running the test with following options:
Number of threads: 2
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Not using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (75.69 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (1438.04 per sec.)
other operations: 20000 (151.37 per sec.)
Test execution summary:
total time: 132.1247s
total number of events: 10000
total time taken by event execution: 264.1101
per-request statistics:
min: 0.0111s
avg: 0.0264s
max: 0.4670s
approx. 95 percentile: 0.0425s
Threads fairness:
events (avg/stddev): 5000.0000/72.00
execution time (avg/stddev): 132.0550/0.00
226:
[root@mfx226 ~]# cat /proc/c
cmdline cpuinfo crypto
[root@mfx226 ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 2.66GHz
stepping : 1
cpu MHz : 2661.101
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monitor ds_cpl tm2 cid xtpr
bogomips : 5323.17
[root@mfx226 ~]# cat /etc/my.cnf
[MYSQL_CLUSTER]
ndb-connectstring=10.4.6.224
[MYSQLD]
ndbcluster
ndb-connectstring=10.4.6.224
default-storage-engine=ndbcluster
max_connections=1024
default-character-set=cp932
ndb-log-update-as-write=0
#ndb-cluster-connection-pool=4
memlock
#added 2008 08 20 for ...
#BASE config.....
skip-name-resolve
back_log =350
log-slow-queries = slow.log
long_query_time = 1
table_cache =350
sort_buffer_size = 1M
query_cache_size=512M
query_cache_limit=2M
ndb_force_send=1
engine_condition_pushdown=1
ndb_use_exact_count=0
#1 -- means enable, 0 -- means disable
slave-allow-batching=1
#transaction-isolation = READ-COMMITTED
#add by wangxl ,for "converting HEAP to MyISAM "
max_heap_table_size=64M
tmp_table_size=64M
thread_cache_size=350
#add by liuyf for primary key repeat
auto_increment_increment=4
auto_increment_offset=3
#MySQL slave config ...........
log-bin=mfx226-bin
#binlog_format="STATEMENT"
binlog_format="ROW"
#binlog_format="MIXED"
server-id = 26
#replicate-do-db = MAIN
#master-host = 10.4.6.227
#master-user = root
#master-password = 123456
#replicate-ignore-table = MAIN.JHF_SYS_POSITION_INSERT
#replicate-ignore-table = mysql.%
#replicate-ignore-table = test.%
#relay-log=mfx226-relay-bin
#skip-slave-start
208:
[root@fxtest009 ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping : 10
cpu MHz : 2400.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monitor ds_cpl est cid xtpr
bogomips : 4003.23
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping : 10
cpu MHz : 3000.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monitor ds_cpl est cid xtpr
bogomips : 3999.48
[root@fxtest009 ~]#
[root@fxtest009 ~]# cat /etc/my.cnf |grep -Ev "^$|#"
[client]
password = 123456
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache =128
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
transaction-isolation = READ-COMMITTED
max_connections=1024
skip-name-resolve
back_log = 200
log-slow-queries = slow.log
long_query_time = 1
log-bin=main_mysql_bin
server-id = 1
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size =1024M
innodb_log_buffer_size = 16M
innodb_log_file_size =8M
innodb_file_per_table
innodb_status_file
innodb_thread_concurrency= 32
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@fxtest009 ~]#
