MySQL Forums
Forum List  »  Performance

5.0 cannot be that much worse than 4.1?
Posted by: Richard Mixon
Date: September 21, 2006 09:41PM

We just did a significant hardware upgrade, and also moved from MySQL 4.0.18 to MySQL 5.0.22. Seemed like a good idea at the time as 4.0 was getting a bit long in the tooth. After several tuning attempts the performance on the new hardware is still horrible, compared to the old server.

I have searched and read a number of articles that indicated in some cases performance on 4.1 was better than 5.0. However is it "much worse"? OK, I realize we need to better define what is "much".

We had been running a single server that ran everything for our application: Apache HTTPD 2.0, two instances of Apache Tomcat 5.5.17 and MySQL 4.0.18. The server ran Fedora Core 2, had dual Xeon 2.8Ghz CPUs (512KB cache), 4GB memory and a pair of IDE 7200 80GB drives (Linux RAID 1).

We needed more performance, as some of our analytics pages were too slow.

So we bought a new server to move MySQL to - with dual 3.2Ghz Xeons (2MB cache), 4GB of memory, hardware RAID and 3 x 15KB 74GB SCSI drives. We loaded 64-bit Fedora Core 5, MySQL 5.0.22 and moved our database there. It is connected to the original application server using a pair of trunked gigabit Ethernet lines.

At first performance was about 2.5 times slower on a set of queries that one of our pages runs. After a bit of tuning and rebuilding of indexes we have got that down to 1.45 times. Obviously this is not acceptable, we wanted better performance.

Our ISP is saying that we should move back to MySQL 4.1 to improve performance, and maybe even back to 32-bit Fedora Core 5. This seems like a step backward, but that's why I'm asking here.

I've included the following info:
- "uname -a"
- my.cnf (with comments removed)
- mysql bench runs

Any advice on what we might do or have done wrong would be appreciated.

I've got another web app (similar, but not the same) running on dual Opterons and SuSE SLES9 with similar setup and the database appears to run much, much faster.

Thank you - Richard

OLD SERVER:
Linux old.server.com 2.4.22-1.2115.nptlsmp #1 SMP Wed Oct 29 15:30:09 EST 2003 i686 i686 i386 GNU/Linux

NEW SERVER:
Linux new.server.com 2.6.16-1.2133_FC5 #1 SMP Tue Jun 6 00:51:53 EDT 2006 x86_64 x86_64 x86_64 GNU/Linux

OLD SERVER my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
key_buffer_size = 256M
max_allowed_packet = 1M
table_cache = 312
sort_buffer_size = 8M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
query_cache_size= 64M
thread_concurrency = 3
max_connections = 150
thread_cache_size = 32
log_slow_queries = slow_queries_log
log-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

NEW SERVER my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 64M
thread_concurrency = 8
log_slow_queries = slow_queries_log
log-bin=mysql-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

OLD SERVER benchmark:
Benchmark DBD suite: 2.14
Date of test: 2006-09-15 10:04:34
Running tests on: Linux 2.4.22-1.2115.nptlsmp i686
Arguments:
Comments:
Limits from:
Server version: MySQL 4.0.18 standard log

alter-table: Total time: 634 wallclock secs ( 0.18 usr 0.08 sys + 0.00 cusr 0.00 csys = 0.26 CPU)
ATIS: Total time: 9 wallclock secs ( 6.29 usr 2.03 sys + 0.00 cusr 0.00 csys = 8.32 CPU)
big-tables: Total time: 11 wallclock secs ( 3.64 usr 4.01 sys + 0.00 cusr 0.00 csys = 7.65 CPU)
connect: Total time: 28 wallclock secs (17.69 usr 5.01 sys + 0.00 cusr 0.00 csys = 22.70 CPU)
create: Total time: 190 wallclock secs ( 2.53 usr 1.24 sys + 0.00 cusr 0.00 csys = 3.77 CPU)
insert: Total time: 1327 wallclock secs (313.86 usr 97.84 sys + 0.00 cusr 0.00 csys = 411.70 CPU)
select: Total time: 86 wallclock secs (33.10 usr 8.08 sys + 0.00 cusr 0.00 csys = 41.18 CPU)
wisconsin: Total time: 7 wallclock secs ( 2.29 usr 1.23 sys + 0.00 cusr 0.00 csys = 3.52 CPU)

All 8 test executed successfully

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 355.00 0.10 0.06 0.16 992
alter_table_drop 266.00 0.06 0.00 0.06 496
connect 6.00 4.21 0.54 4.75 10000
connect+select_1_row 7.00 4.43 0.92 5.35 10000
connect+select_simple 8.00 4.48 0.89 5.37 10000
count 12.00 0.01 0.00 0.01 100
count_distinct 0.00 0.09 0.05 0.14 1000
count_distinct_2 1.00 0.18 0.01 0.19 1000
count_distinct_big 10.00 6.23 1.86 8.09 120
count_distinct_group 1.00 0.76 0.18 0.94 1000
count_distinct_group_on_key 0.00 0.20 0.01 0.21 1000
count_distinct_group_on_key_parts 1.00 0.77 0.21 0.98 1000
count_distinct_key_prefix 0.00 0.14 0.03 0.17 1000
count_group_on_key_parts 1.00 0.70 0.21 0.91 1000
count_on_key 23.00 6.06 1.16 7.22 50100
create+drop 40.00 0.60 0.33 0.93 10000
create_MANY_tables 94.00 0.66 0.17 0.83 10000
create_index 7.00 0.00 0.00 0.00 8
create_key+drop 50.00 0.94 0.34 1.28 10000
create_table 0.00 0.00 0.00 0.00 31
delete_all 9.00 0.00 0.00 0.00 12
delete_all_many_keys 207.00 0.01 0.00 0.01 1
delete_big 1.00 0.00 0.00 0.00 1
delete_big_many_keys 207.00 0.01 0.00 0.01 128
delete_key 1.00 0.14 0.18 0.32 10000
drop_index 6.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.13 0.10 0.23 10000
insert 43.00 5.07 5.12 10.19 350768
insert_duplicates 8.00 1.63 1.32 2.95 100000
insert_key 267.00 3.27 1.64 4.91 100000
insert_many_fields 3.00 0.15 0.02 0.17 2000
insert_select_1_key 1.00 0.00 0.00 0.00 1
insert_select_2_keys 3.00 0.00 0.00 0.00 1
min_max 5.00 0.02 0.00 0.02 60
min_max_on_key 14.00 10.42 1.95 12.37 85000
multiple_value_insert 1.00 0.26 0.00 0.26 100000
order_by_big 26.00 15.89 6.51 22.40 10
order_by_big_key 24.00 16.48 6.26 22.74 10
order_by_big_key2 23.00 15.89 6.30 22.19 10
order_by_big_key_desc 24.00 16.48 6.38 22.86 10
order_by_big_key_diff 26.00 15.97 6.53 22.50 10
order_by_big_key_prefix 23.00 16.06 6.43 22.49 10
order_by_key2_diff 3.00 1.38 0.52 1.90 500
order_by_key_prefix 1.00 0.78 0.17 0.95 500
order_by_range 3.00 0.65 0.27 0.92 500
outer_join 1.00 0.00 0.00 0.00 10
outer_join_found 2.00 0.01 0.00 0.01 10
outer_join_not_found 1.00 0.00 0.00 0.00 500
outer_join_on_key 2.00 0.00 0.00 0.00 10
select_1_row 0.00 0.21 0.30 0.51 10000
select_2_rows 1.00 0.18 0.29 0.47 10000
select_big 23.00 16.67 6.17 22.84 80
select_big_str 5.00 3.82 1.61 5.43 10000
select_column+column 1.00 0.18 0.28 0.46 10000
select_diff_key 58.00 0.09 0.01 0.10 500
select_distinct 2.00 1.27 0.31 1.58 800
select_group 3.00 0.81 0.23 1.04 2911
select_group_when_MANY_tables 4.00 0.20 0.30 0.50 10000
select_join 1.00 0.36 0.12 0.48 100
select_key 53.00 28.48 6.24 34.72 200000
select_key2 54.00 27.48 6.44 33.92 200000
select_key2_return_key 53.00 27.85 4.84 32.69 200000
select_key2_return_prim 55.00 27.57 5.61 33.18 200000
select_key_prefix 52.00 27.79 6.15 33.94 200000
select_key_prefix_join 4.00 3.22 1.12 4.34 100
select_key_return_key 52.00 27.31 5.02 32.33 200000
select_many_fields 8.00 3.49 3.99 7.48 2000
select_query_cache 41.00 1.33 0.29 1.62 10000
select_query_cache2 2.00 1.21 0.21 1.42 10000
select_range 10.00 6.69 2.02 8.71 410
select_range_key2 3.00 2.89 0.71 3.60 25010
select_range_prefix 4.00 3.13 0.58 3.71 25010
select_simple 0.00 0.18 0.18 0.36 10000
select_simple_join 0.00 0.43 0.14 0.57 500
update_big 29.00 0.00 0.00 0.00 10
update_of_key 19.00 0.96 0.78 1.74 50000
update_of_key_big 9.00 0.01 0.00 0.01 501
update_of_primary_key_many_keys 70.00 0.01 0.01 0.02 256
update_with_key 41.00 4.15 4.39 8.54 300000
update_with_key_prefix 8.00 1.26 1.34 2.60 100000
wisc_benchmark 2.00 1.90 0.82 2.72 114
TOTALS 2486.00 372.45 117.17 489.62 2667247


NEW SERVER benchmarks:
Benchmark DBD suite: 2.15
Date of test: 2006-09-18 7:34:56
Running tests on: Linux 2.6.16-1.2133_FC5 x86_64
Arguments:
Comments:
Limits from:
Server version: MySQL 5.0.22 log/
Optimization: None
Hardware:

alter-table: Total time: 40 wallclock secs ( 0.09 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.12 CPU)
ATIS: Total time: 6 wallclock secs ( 4.92 usr 0.17 sys + 0.00 cusr 0.00 csys = 5.09 CPU)
big-tables: Total time: 8 wallclock secs ( 4.35 usr 0.28 sys + 0.00 cusr 0.00 csys = 4.63 CPU)
connect: Total time: 77 wallclock secs (23.03 usr 12.28 sys + 0.00 cusr 0.00 csys = 35.31 CPU)
create: Total time: 582 wallclock secs ( 2.29 usr 1.16 sys + 0.00 cusr 0.00 csys = 3.45 CPU)
insert: Total time: 1180 wallclock secs (278.88 usr 42.57 sys + 0.00 cusr 0.00 csys = 321.45 CPU)
select: Total time: 72 wallclock secs (29.16 usr 3.58 sys + 0.00 cusr 0.00 csys = 32.74 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 5 wallclock secs ( 2.03 usr 0.48 sys + 0.00 cusr 0.00 csys = 2.51 CPU)

All 9 test executed successfully

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 18.00 0.04 0.01 0.05 100
alter_table_drop 17.00 0.03 0.01 0.04 91
connect 4.00 3.14 0.72 3.86 10000
connect+select_1_row 6.00 3.41 0.73 4.14 10000
connect+select_simple 6.00 3.22 0.77 3.99 10000
count 7.00 0.01 0.00 0.01 100
count_distinct 0.00 0.13 0.02 0.15 1000
count_distinct_2 0.00 0.12 0.02 0.14 1000
count_distinct_big 5.00 4.29 0.04 4.33 120
count_distinct_group 1.00 0.54 0.02 0.56 1000
count_distinct_group_on_key 1.00 0.14 0.03 0.17 1000
count_distinct_group_on_key_parts 0.00 0.55 0.01 0.56 1000
count_distinct_key_prefix 0.00 0.11 0.02 0.13 1000
count_group_on_key_parts 1.00 0.52 0.02 0.54 1000
count_on_key 18.00 5.59 0.96 6.55 50100
create+drop 195.00 0.71 0.41 1.12 10000
create_MANY_tables 188.00 0.55 0.20 0.75 10000
create_index 2.00 0.00 0.00 0.00 8
create_key+drop 195.00 0.70 0.38 1.08 10000
create_table 0.00 0.00 0.00 0.00 31
delete_all_many_keys 319.00 0.01 0.00 0.01 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 319.00 0.01 0.00 0.01 128
delete_key 3.00 0.25 0.13 0.38 10000
delete_range 6.00 0.00 0.00 0.00 12
drop_index 3.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.13 0.08 0.21 10000
insert 40.00 6.13 5.40 11.53 350768
insert_duplicates 9.00 1.87 1.57 3.44 100000
insert_key 125.00 2.97 1.66 4.63 100000
insert_many_fields 3.00 0.17 0.04 0.21 2000
insert_select_1_key 2.00 0.00 0.00 0.00 1
insert_select_2_keys 2.00 0.00 0.00 0.00 1
min_max 3.00 0.01 0.00 0.01 60
min_max_on_key 13.00 9.58 1.65 11.23 85000
multiple_value_insert 2.00 0.15 0.02 0.17 100000
once_prepared_select 20.00 5.84 1.68 7.52 100000
order_by_big 17.00 11.94 0.18 12.12 10
order_by_big_key 15.00 12.48 0.24 12.72 10
order_by_big_key2 14.00 11.95 0.18 12.13 10
order_by_big_key_desc 16.00 12.38 0.20 12.58 10
order_by_big_key_diff 16.00 12.03 0.15 12.18 10
order_by_big_key_prefix 14.00 11.99 0.15 12.14 10
order_by_key2_diff 2.00 1.04 0.00 1.04 500
order_by_key_prefix 1.00 0.58 0.04 0.62 500
order_by_range 2.00 0.53 0.00 0.53 500
outer_join 2.00 0.00 0.00 0.00 10
outer_join_found 2.00 0.00 0.00 0.00 10
outer_join_not_found 2.00 0.01 0.00 0.01 500
outer_join_on_key 2.00 0.00 0.00 0.00 10
prepared_select 28.00 11.80 1.82 13.62 100000
select_1_row 12.00 2.20 1.43 3.63 100000
select_1_row_cache 5.00 1.92 1.40 3.32 100000
select_2_rows 14.00 2.07 1.79 3.86 100000
select_big 15.00 12.01 0.19 12.20 80
select_big_str 5.00 1.65 1.18 2.83 10000
select_cache 2.00 1.06 0.22 1.28 10000
select_cache2 39.00 3.36 0.56 3.92 10000
select_column+column 13.00 1.73 1.54 3.27 100000
select_diff_key 0.00 0.06 0.03 0.09 500
select_distinct 1.00 0.94 0.02 0.96 800
select_group 2.00 0.68 0.04 0.72 2911
select_group_when_MANY_tables 2.00 0.20 0.09 0.29 10000
select_join 0.00 0.27 0.00 0.27 100
select_key 47.00 23.67 3.81 27.48 200000
select_key2 50.00 24.84 3.54 28.38 200000
select_key2_return_key 47.00 23.27 3.57 26.84 200000
select_key2_return_prim 48.00 23.61 3.68 27.29 200000
select_key_prefix 48.00 23.61 3.84 27.45 200000
select_key_prefix_join 2.00 2.39 0.00 2.39 100
select_key_return_key 46.00 23.56 3.60 27.16 200000
select_many_fields 4.00 4.18 0.24 4.42 2000
select_range 6.00 4.71 0.06 4.77 410
select_range_key2 3.00 2.44 0.31 2.75 25010
select_range_prefix 3.00 2.49 0.27 2.76 25010
select_simple 6.00 1.85 1.43 3.28 100000
select_simple_cache 6.00 1.84 1.29 3.13 100000
select_simple_join 1.00 0.34 0.01 0.35 500
update_big 10.00 0.00 0.00 0.00 10
update_of_key 16.00 1.14 0.74 1.88 50000
update_of_key_big 9.00 0.00 0.00 0.00 501
update_of_primary_key_many_keys 101.00 0.01 0.00 0.01 256
update_with_key 39.00 5.40 4.68 10.08 300000
update_with_key_prefix 12.00 2.88 1.31 4.19 100000
wisc_benchmark 1.00 1.29 0.04 1.33 114
TOTALS 2283.00 339.32 60.47 399.79 3425950

Options: ReplyQuote


Subject
Views
Written By
Posted
5.0 cannot be that much worse than 4.1?
2204
September 21, 2006 09:41PM
1126
September 25, 2006 03:20AM


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.