MySQL Forums
Forum List  »  Performance

Need performance tuning tips for dual Opteron...
Posted by: Sergio Chong
Date: April 14, 2005 08:56AM

Hello:

I just installed MySQL 4.1.11 on an IBM eServer 326 gentoo loaded system. I ran the run_all_tests script included for benchmarking; the results seem reasonable except for the "create" section. Every benchmarking in that area throwed awful results, all of them were really slow specially the create+drop tests. The disks performanc
e does seem fine, hdparm reported a transfer rate of about 84MB/s and it's nothing weird with the rest of the tests.

I've included below the server architecture, ATIS-mysql-xxxx, RUN-mysql-xxx and the my.cnf file I'm using. MySQL was compiled static and dynamic (having almost no difference between both) and amd64 optimized. Notice the create tests took over 340 seconds each.

Sorry if this post is a little long, but I wanted to include as much information as posible to nail the problem.

Any performance tip would help and please let me know if extra information is needed.

Thanks in advance


Sergio Chong

================
SERVER:

IBM eServer 326
Dual Opteron 2.4GHz
512 RAM DDR
2 Ultra320 SCSI HD's
Integrated RAID-1 for mirroring

================
ATIS-mysql-Linux_2.6.11_gentoo_r6_x86_64

Testing server 'MySQL 4.1.11/' at 2005-04-12 21:08:30

ATIS table test

Creating tables
Time for create_table (28): 1 wallclock secs ( 0.00 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.01 CPU)

Inserting data
Time to insert (9768): 0 wallclock secs ( 0.08 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.15 CPU)

Retrieving data
Time for select_simple_join (500): 0 wallclock secs ( 0.10 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.15 CPU)
Time for select_join (100): 1 wallclock secs ( 0.07 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.12 CPU)
Time for select_key_prefix_join (100): 1 wallclock secs ( 0.65 usr 0.42 sys + 0.00 cusr 0.00 csys = 1.07 CPU)
Time for select_distinct (800): 2 wallclock secs ( 0.27 usr 0.16 sys + 0.00 cusr 0.00 csys = 0.43 CPU)
Time for select_group (2800): 2 wallclock secs ( 0.23 usr 0.09 sys + 0.00 cusr 0.00 csys = 0.32 CPU)

Removing tables
Time to drop_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU)
Total time: 7 wallclock secs ( 1.40 usr 0.85 sys + 0.00 cusr 0.00 csys = 2.25 CPU)

================
RUN-mysql-Linux_2.6.11_gentoo_r6_x86_64

Benchmark DBD suite: 2.15
Date of test: 2005-04-12 21:07:56
Running tests on: Linux 2.6.11-gentoo-r6 x86_64
Arguments: --threads=17 --random
Comments:
Limits from:
Server version: MySQL 4.1.11/
Optimization: None
Hardware:

alter-table: Total time: 34 wallclock secs ( 0.01 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.02 CPU)
ATIS: Total time: 7 wallclock secs ( 1.40 usr 0.85 sys + 0.00 cusr 0.00 csys = 2.25 CPU)
big-tables: Total time: 7 wallclock secs ( 2.03 usr 1.78 sys + 0.00 cusr 0.00 csys = 3.81 CPU)
connect: Total time: 35 wallclock secs ( 9.70 usr 8.60 sys + 0.00 cusr 0.00 csys = 18.30 CPU)
create: Total time: 1036 wallclock secs ( 0.87 usr 0.50 sys + 0.00 cusr 0.00 csys = 1.37 CPU)
insert: Total time: 385 wallclock secs (101.73 usr 56.06 sys + 0.00 cusr 0.00 csys = 157.79 CPU)
select: Total time: 170 wallclock secs ( 9.49 usr 3.95 sys + 0.00 cusr 0.00 csys = 13.44 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 3 wallclock secs ( 0.84 usr 0.45 sys + 0.00 cusr 0.00 csys = 1.29 CPU)

All 9 test executed successfully

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 15.00 0.00 0.00 0.00 100
alter_table_drop 15.00 0.01 0.00 0.01 91
connect 2.00 1.40 0.31 1.71 10000
connect+select_1_row 2.00 1.51 0.42 1.93 10000
connect+select_simple 2.00 1.47 0.40 1.87 10000
count 5.00 0.01 0.00 0.01 100
count_distinct 4.00 0.05 0.01 0.06 1000
count_distinct_2 7.00 0.05 0.01 0.06 1000
count_distinct_big 11.00 1.21 0.84 2.05 120
count_distinct_group 5.00 0.15 0.08 0.23 1000
count_distinct_group_on_key 5.00 0.06 0.02 0.08 1000
count_distinct_group_on_key_parts 6.00 0.17 0.11 0.28 1000
count_distinct_key_prefix 3.00 0.05 0.02 0.07 1000
count_group_on_key_parts 5.00 0.15 0.09 0.24 1000
count_on_key 53.00 2.18 0.62 2.80 50100
create+drop 338.00 0.21 0.17 0.38 10000
create_MANY_tables 354.00 0.21 0.08 0.29 10000
create_index 2.00 0.00 0.00 0.00 8
create_key+drop 341.00 0.34 0.13 0.47 10000
create_table 1.00 0.00 0.01 0.01 31
delete_all_many_keys 12.00 0.00 0.00 0.00 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 12.00 0.00 0.00 0.00 128
delete_key 1.00 0.05 0.09 0.14 10000
delete_range 1.00 0.00 0.00 0.00 12
drop_index 2.00 0.00 0.00 0.00 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 2.00 0.05 0.06 0.11 10000
insert 17.00 1.68 2.52 4.20 350768
insert_duplicates 4.00 0.64 0.86 1.50 100000
insert_key 19.00 1.40 0.93 2.33 100000
insert_many_fields 1.00 0.05 0.02 0.07 2000
insert_select_1_key 1.00 0.00 0.00 0.00 1
insert_select_2_keys 1.00 0.00 0.00 0.00 1
min_max 4.00 0.00 0.00 0.00 60
min_max_on_key 7.00 3.71 1.11 4.82 85000
multiple_value_insert 1.00 0.16 0.01 0.17 100000
once_prepared_select 8.00 1.63 2.19 3.82 100000
order_by_big 8.00 3.54 2.90 6.44 10
order_by_big_key 7.00 3.68 2.95 6.63 10
order_by_big_key2 6.00 3.52 2.96 6.48 10
order_by_big_key_desc 6.00 3.68 3.00 6.68 10
order_by_big_key_diff 8.00 3.52 2.92 6.44 10
order_by_big_key_prefix 7.00 3.51 2.94 6.45 10
order_by_key2_diff 0.00 0.30 0.24 0.54 500
order_by_key_prefix 1.00 0.16 0.15 0.31 500
order_by_range 1.00 0.19 0.12 0.31 500
outer_join 8.00 0.00 0.00 0.00 10
outer_join_found 9.00 0.00 0.00 0.00 10
outer_join_not_found 5.00 0.00 0.00 0.00 500
outer_join_on_key 6.00 0.00 0.00 0.00 10
prepared_select 11.00 4.51 1.90 6.41 100000
select_1_row 5.00 0.84 1.40 2.24 100000
select_1_row_cache 5.00 0.80 1.17 1.97 100000
select_2_rows 5.00 0.83 1.39 2.22 100000
select_big 6.00 3.57 2.92 6.49 80
select_big_str 3.00 1.10 0.52 1.62 10000
select_cache 21.00 0.42 0.12 0.54 10000
select_cache2 21.00 0.45 0.11 0.56 10000
select_column+column 5.00 0.61 1.08 1.69 100000
select_diff_key 26.00 0.02 0.00 0.02 500
select_distinct 2.00 0.27 0.16 0.43 800
select_group 7.00 0.24 0.09 0.33 2911
select_group_when_MANY_tables 1.00 0.06 0.06 0.12 10000
select_join 1.00 0.07 0.05 0.12 100
select_key 21.00 10.04 3.85 13.89 200000
select_key2 21.00 10.16 3.75 13.91 200000
select_key2_return_key 20.00 9.44 2.88 12.32 200000
select_key2_return_prim 20.00 9.48 3.22 12.70 200000
select_key_prefix 22.00 10.49 4.27 14.76 200000
select_key_prefix_join 1.00 0.65 0.42 1.07 100
select_key_return_key 20.00 9.72 3.11 12.83 200000
select_many_fields 6.00 1.98 1.76 3.74 2000
select_range 35.00 1.41 0.92 2.33 410
select_range_key2 3.00 0.90 0.40 1.30 25010
select_range_prefix 4.00 0.99 0.41 1.40 25010
select_simple 3.00 0.61 1.00 1.61 100000
select_simple_cache 3.00 0.53 0.90 1.43 100000
select_simple_join 0.00 0.10 0.05 0.15 500
update_big 6.00 0.00 0.00 0.00 10
update_of_key 3.00 0.21 0.41 0.62 50000
update_of_key_big 3.00 0.00 0.01 0.01 501
update_of_primary_key_many_keys 5.00 0.00 0.00 0.00 256
update_with_key 14.00 1.27 2.21 3.48 300000
update_with_key_prefix 6.00 1.20 0.96 2.16 100000
wisc_benchmark 1.00 0.71 0.23 0.94 114
TOTALS 1677.00 124.38 71.02 195.40 3425950
real 27m58.481s
user 2m6.993s
sys 1m12.372s

================
my.cnf

# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.0.14-r1,v 1.2 2004/07/18 02:47:43 dragonheart Exp $

[client]
character_set = latin1
#password = my_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

[safe_mysqld]
err-log = /var/log/mysql/mysql.err

[mysqld]
character-set-server = latin1
default-character-set = latin1
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
set-variable = thread_stack=1024K
language = /usr/share/mysql/english
# keep secure by default!
bind-address = 127.0.0.1
port = 3306
# this can make it even more secure:
skip-networking

# Point the following paths to different dedicated disks
tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

innodb_data_home_dir = /var/lib/iCDBs/iC_innodb
innodb_data_file_path = ibdata/ibdata1:512M:autoextend
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=50M
innodb_log_group_home_dir = /var/lib/iCDBs/iC_innodb/iblogs
innodb_log_arch_dir = /var/lib/iCDBs/iC_innodb/iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=72M
set-variable = innodb_log_buffer_size=24M
#innodb_force_recovery = 4
innodb_flush_log_at_trx_commit=1
#innodb_monitor=1
#innodb_status_file=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=120
#innodb_flush_method=fdatasync
innodb_fast_shutdown=1
set-variable = innodb_thread_concurrency=4

# you need debug use flag enabled to use this ones.
# if needed uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's appening in your server ;)

#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
#log-bin
#server-id=1
#log-bin = /var/masterlog/masterlog

[mysql]

Options: ReplyQuote


Subject
Views
Written By
Posted
Need performance tuning tips for dual Opteron...
2706
April 14, 2005 08:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.