In order to verify and perhaps solve the SMP problems I and others
have reported to this list I did a series of sql-bench tests this
morning. As suggested by the MySQL people the system is configured with
glibc 2.2 and 2.4 kernel. I then switched kernels to the default 2.2.16
SMP and ran the tests again.
Setup:
Dual Pentium!!! 866MHz
512MB Ram, UW SCSI
RedHat Linux 7.0 v2(Respin)
MySQL 3.23.32
my.cnf:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=128M
set-variable = max_allowed_packet=1M
set-variable = table_cache=128
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
set-variable = thread_concurrency=4
set-variable = max_connections=250
log-update
[mysqldump]
quick
[mysql]
no-auto-rehash
[isamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
glibc-2.2-12 (.rpm)
gcc-2.96-69 (.rpm)
Kernels:
2.2.16SMP from default install
2.4SMP compiled from source
First test:
----------
sql-bench setup:
----------------
Benchmark DBD suite: 2.11a
Date of test: 2001-01-24 8:58:30
Running tests on: Linux 2.4.0 i686
Arguments:
Comments:
Limits from: mysql,mysql,pg,solid
Server version: MySQL 3.23.32 log
ATIS: Total time: 26 wallclock secs ( 4.84 usr 2.25 sys + 0.00 cusr 0.00 csys =
7.09 CPU)
alter-table: Total time: 143 wallclock secs ( 0.10 usr 0.03 sys + 0.00 cusr 0.00
csys = 0.13 CPU)
big-tables: Total time: 24 wallclock secs ( 5.66 usr 4.44 sys + 0.00 cusr 0.00 csys
= 10.10 CPU)
connect: Total time: 24 wallclock secs (12.47 usr 3.70 sys + 0.00 cusr 0.00 csys =
16.17 CPU)
insert: Total time: 1119 wallclock secs (254.38 usr 87.85 sys + 0.00 cusr 0.00 csys
= 342.23 CPU)
select: Total time: 739 wallclock secs (35.62 usr 7.38 sys + 0.00 cusr 0.00 csys =
43.00 CPU)
wisconsin: Total time: 11 wallclock secs ( 2.38 usr 1.09 sys + 0.00 cusr 0.00 csys
= 3.47 CPU)
------------------------------------------------------------------
seconds usr sys cpu tests
TOTALS 2124.00 315.43 106.74 422.17 1875011
------------------------------------------------------------------
Second test:
------------
sql-bench setup:
---------------
Benchmark DBD suite: 2.11a
Date of test: 2001-01-24 9:44:55
Running tests on: Linux 2.2.16-22smp i686
Arguments:
Comments:
Limits from: mysql,mysql,pg,solid
Server version: MySQL 3.23.32 log
ATIS: Total time: 26 wallclock secs ( 5.50 usr 2.29 sys + 0.00 cusr 0.00 csys =
7.79 CPU)
alter-table: Total time: 176 wallclock secs ( 0.21 usr 0.07 sys + 0.00 cusr 0.00
csys = 0.28 CPU)
big-tables: Total time: 25 wallclock secs ( 6.26 usr 4.13 sys + 0.00 cusr 0.00 csys
= 10.39 CPU)
connect: Total time: 29 wallclock secs (14.97 usr 4.12 sys + 0.00 cusr 0.00 csys =
19.09 CPU)
insert: Total time: 1274 wallclock secs (326.70 usr 102.33 sys + 0.00 cusr 0.00 csys
= 429.03 CPU)
select: Total time: 746 wallclock secs (41.49 usr 10.66 sys + 0.00 cusr 0.00 csys =
52.15 CPU)
wisconsin: Total time: 11 wallclock secs ( 2.91 usr 1.19 sys + 0.00 cusr 0.00 csys
= 4.10 CPU)
------------------------------------------------------------------
seconds usr sys cpu tests
TOTALS 2326.00 398.02 124.79 522.81 1875011
------------------------------------------------------------------
The conclusion is that the 2.4 kernel obviously helps. Even where the
tests total time are equal the 2.4 kernel generally consumes less
cpu. I did however not manage to "crash" MySQL during the tests with
the 2.2.16SMP kernel. This may be because the tests isn't as load
intensive as the load my production servers get, or it may be related to a
better performing glibc 2.2 (The production servers run RedHat 6.2 with
glibc 2.1 and kernel 2.2.16SMP). In other words it remains to be seen
if the 2.4 kernel paired with glibc-2.2 resolves my current problems
where mysql goes belly-up under load.
Any suggestions to further test will be appreciated.
regards
Rune Hansen
[EMAIL PROTECTED]
Viventus AS
Liaveien 11, 1411 Kolbotn
+4766812280/86
"It's a damn poor mind that can only think
of one way to spell a word."
--Andrew Jackson
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
