MySQL5 becomes so slowly when bin log is open.
I used sysbench to test mysql.
MySQL version is mysql-standard-5.0.27-linux-i686-icc-glibc23.tar.gz
OS is RHEL 4.0
my.cnf is :
[mysqld]
#log-bin=icc
max_connections=3000
innodb_log_file_size=50M
max_prepared_stmt_count=32765
#log=Buddha.log
query_cache_size=16M
innodb_buffer_pool_size=800M
#key_buffer_size=512M
server-id=55
port=3309
I used sysbench to test mysql just like this:
sysbench --max-requests=10000 --mysql-host=172.20.23.28
--mysql-user=sbtest --mysql-password=sbtest --mysql-port=3309
--num-threads=50 --test=oltp --oltp-table-size=10000 run
It completed 396 transactions per second whithout bin log.
But when bin log is enabled, only 76 transactions per second.
I also tested MySQL5.0.33. Almost the same result.
But mysql4.1.22 didn't have that problem.
The whole sysbench's result is below:
When bin log is closed:
sysbench --max-requests=10000 --mysql-host=172.20.23.28 --
mysql-user=sbtest --mysql-password=sbtest --mysql-port=3309
--num-threads=50 --test=oltp -
-oltp-table-size=10000 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 49 times)
Running the test with following options:
Number of threads: 50
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
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: 140042
write: 50009
other: 20004
total: 210055
transactions: 10001 (396.49 per sec.)
deadlocks: 2 (0.08 per sec.)
read/write requests: 190051 (7534.67 per sec.)
other operations: 20004 (793.07 per sec.)
Test execution summary:
total time: 25.2235s
total number of events: 10001
total time taken by event execution: 1258.4014
per-request statistics:
min: 0.0301s
avg: 0.1258s
max: 0.4040s
approx. 95 percentile: 0.1926s
Threads fairness:
events (avg/stddev): 200.0200/3.52
execution time (avg/stddev): 25.1680/0.03
When bin log is open :
sysbench --max-requests=10000 --mysql-host=172.20.23.28 --
mysql-user=sbtest --mysql-password=sbtest --mysql-port=3309
--num-threads=50 --test=oltp -
-oltp-table-size=10000 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 49 times)
Running the test with following options:
Number of threads: 50
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
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: 144886
write: 50743
other: 20349
total: 215978
transactions: 10000 (76.92 per sec.)
deadlocks: 349 (2.68 per sec.)
read/write requests: 195629 (1504.68 per sec.)
other operations: 20349 (156.51 per sec.)
Test execution summary:
total time: 130.0134s
total number of events: 10000
total time taken by event execution: 6485.2686
per-request statistics:
min: 0.0353s
avg: 0.6485s
max: 6.0622s
approx. 95 percentile: 1.8840s
Threads fairness:
events (avg/stddev): 200.0000/14.86
execution time (avg/stddev): 129.7054/0.17
Are there any tips?
Or maybe I should use mysql4.1 ?
Thanks
Gu Lei
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
