We have a Dell Poweredge 1750 with dual 3GHz Xeon's and 4GB of memory.� It is 
connected to a EMC CX600 through a Brocade Silkworm 2800 and QLogic HBA running 
qla2300 v6.07.� 

Anyways, we are attempting to migrate to the 4 tree from 3.23.56.� When we began doing 
so I started running some sql-bench tests for performance benchmarking.� I immediately 
noticed far worse performance on the mysql 4 tree in comparision to the 3 tree for 
specific tests primarily the create and insert.� 

We initially started troubleshooting by isolating the hardware.� We moved our datadir 
back to two internal scsi 10k drives in a hardware raid0 config.� I then tested 4.0.18 
on the internal storage and had no problems.� Performance was smoking fast.� I then 
reverted back to the san storage (raid5 btw) to verify again and received horrible 
performance.� Initially I thought it may be due to the additional overhead of writing 
parity to the raid5.� However, I then decided to downgrade to a mysql 4 version and 
test against the raid5 san storage.� I downgraded to 4.0.13 and tested.� Performance 
was again fast.� I then climbed the tree revision by revision.� I tested 4.0.14, 
4.0.15, 4.0.16 and they were all fast performance.� I then got to 4.0.17 and 
performance fell in the toilet again.� I have since tested 4.0.17 and 4.0.18 and both 
show a significant performance degradation over any versions prior to 4.0.17.� This 
ONLY occurs on the external raid5 storage for these versions.� All internal storage is 
lighting fast.� 

I have changed a few kernel parameters for the vm including:

# Modified the min readahead
vm.min-readahead = 128
# Modified the max readahead
vm.max-readahead = 512
# Modified bdflush parameters
vm.bdflush = 5� 2048��� 512���� 512���� 500���� 1000��� 20����� 10����� 0

I have also modified a few mysql parameters as seen below:

key_buffer = 1600M
sort_buffer_size = 8M

All parameters were identical through ALL tests and nothing else was running at the 
time of the testing.� The only thing that changed was the mysql version.� 

Can anyone offer some insight as to what may have changed between 4.0.16 and 4.0.17 
that would cause this performance issue on the external raid array?� I looked at the 
change log for 4.0.17 but didn't see anything that immediately stuck out as a 
possibility.

Here are the test results for create and here is some vmstat output:

MYSQL 4.0.16
[EMAIL PROTECTED] sql-bench]# /usr/bin/perl ./test-create --server=mysql -cmp=mysql 
--user=root --password='pa$$word' --log > 
"output/create-mysql-Linux_2.4.21_9.ELsmp_i686-cmp-mysql" 2>&1
[EMAIL PROTECTED] sql-bench]# more 
output/create-mysql-Linux_2.4.21_9.ELsmp_i686-cmp-mysql 
Testing server 'MySQL 4.0.16 log' at 2004-07-15 13:47:42

Testing the speed of creating and dropping tables
Testing with 10000 tables and 10000 loop count

Testing create of tables
Time for create_MANY_tables (10000): 46 wallclock secs ( 0.78 usr� 0.12 sys +� 0.00 
cusr� 0.00 csys =� 0.90 CPU)

Accessing tables
Time to select_group_when_MANY_tables (10000):� 6 wallclock secs ( 0.68 usr� 0.24 sys 
+� 0.00 cusr� 0.00 csys =� 0.92 CPU)

Testing drop
Time for drop_table_when_MANY_tables (10000):� 1 wallclock secs ( 0.17 usr� 0.16 sys 
+� 0.00 cusr� 0.00 csys =� 0.33 CPU)

Testing create+drop
Time for create+drop (10000):� 4 wallclock secs ( 0.69 usr� 0.28 sys +� 0.00 cusr� 
0.00 csys =� 0.97 CPU)
Time for create_key+drop (10000):� 5 wallclock secs ( 0.92 usr� 0.29 sys +� 0.00 cusr� 
0.00 csys =� 1.21 CPU)
Total time: 62 wallclock secs ( 3.24 usr� 1.09 sys +� 0.00 cusr� 0.00 csys =� 4.33 CPU)

[EMAIL PROTECTED] mysql-4.0.16]# vmstat 1
procs��������������������� memory����� swap��������� io���� system�������� cpu
�r� b�� swpd�� free�� buff� cache�� si�� so��� bi��� bo�� in��� cs us sy id wa
�0� 0����� 0 2708360� 35144 879168��� 0��� 0���� 0���� 0� 113��� 12� 0� 0 100� 0
�0� 0����� 0 2696128� 35148 889676��� 0��� 0���� 0���� 0� 116� 5376� 7� 7 86� 0
�1� 0����� 0 2679752� 35148 907548��� 0��� 0���� 0���� 0� 113� 8845� 7 21 72� 0
�1� 0� ����0 2669044� 35148 918256��� 0��� 0���� 0 16384� 217� 5395� 2 23 74� 0
�0� 0����� 0 2661044� 35148 926256��� 0��� 0���� 0� 8192� 166� 3998� 2 23 74� 0
�0� 0����� 0 2654380� 35148 932920��� 0��� 0���� 0���� 0� 114� 3294� 2 24 74� 0
�0� 0����� 0 2648748� 35148 938552��� 0��� 0���� 0� 8192� 176� 2856� 2 24 74� 0
�0� 0����� 0 2643888� 35148 943412��� 0��� 0���� 0� 8189� 207� 2490� 3 22 75� 0
�0� 0����� 0 2639304� 35148 947996��� 0��� 0���� 0���� 0� 115� 2281� 1 23 75� 0�
procs������� ��������������memory����� swap��������� io���� system�������� cpu
�r� b�� swpd�� free�� buff� cache�� si�� so��� bi��� bo�� in��� cs us sy id wa
�0� 0����� 0 2559228� 35148 1028072��� 0��� 0���� 0���� 0� 118�� 949� 2 24 74� 0
�0� 0����� 0 2557368� 35148 1029932��� 0��� 0���� 0���� 0� 117�� 933� 2 24 74� 0
�0� 0����� 0 2681956� 35160 888536��� 0��� 0���� 0���� 0� 115 12260� 5� 5 89� 0
�0� 0����� 0 2681956� 35160 888536��� 0��� 0���� 0���� 0� 113��� 12� 0� 0 100� 0
�0� 0����� 0 2681956� 35160 888536��� 0��� 0���� 0���� 0� 124��� 54� 0� 0 100� 0
MYSQL 4.0.17
[EMAIL PROTECTED] root]# more 
/usr/local/mysql/sql-bench/output/create-mysql-Linux_2.4.21_9.ELsmp_i686-cmp-mysql 
Testing server 'MySQL 4.0.17 log' at 2004-07-15 13:50:24

Testing the speed of creating and dropping tables
Testing with 10000 tables and 10000 loop count

Testing create of tables
Time for create_MANY_tables (10000): 400 wallclock secs ( 1.27 usr� 0.32 sys +� 0.00 
cusr� 0.00 csys =� 1.59 CPU)

Accessing tables
Time to select_group_when_MANY_tables (10000):� 6 wallclock secs ( 0.87 usr� 0.38 sys 
+ �0.00 cusr� 0.00 csys =� 1.25 CPU)

Testing drop
Time for drop_table_when_MANY_tables (10000):� 1 wallclock secs ( 0.23 usr� 0.14 sys 
+� 0.00 cusr� 0.00 csys =� 0.37 CPU)

Testing create+drop
Time for create+drop (10000): 400 wallclock secs ( 1.16 usr� 0.27 sys +� 0.00 cusr� 
0.00 csys =� 1.43 CPU)
Time for create_key+drop (10000): 400 wallclock secs ( 0.65 usr� 0.22 sys +� 0.00 
cusr� 0.00 csys =� 0.87 CPU)
Total time: 1207 wallclock secs ( 4.18 usr� 1.33 sys +� 0.00 cusr� 0.00 csys =� 5.51 
CPU)

1� 0����� 0 2666096� 35224 897208��� 0��� 0���� 0�� 412� 164�� 636� 0� 0 50 50
�0� 0����� 0 2666096� 35224 897220��� 0��� 0���� 0�� 400� 163�� 617� 0� 0 50 50
�0� 0����� 0 2666096� 35224 897232��� 0��� 0���� 0�� 404� 164�� 622� 0� 0 50 50
�0� 0����� 0 2666096� 35224 897244��� 0��� 0���� 0�� 400� 163�� 614� 0� 0 50 50
�0� 0����� 0 2666096� 35224 897256��� 0��� 0���� 0�� 412� 166�� 638� 0� 0 50 50
�0� 0����� 0 2666096� 35224 897268��� 0��� 0���� 0�� 400� 163�� 612� 0� 0 50 50
�0� 0����� 0 2669412� 35224 897368��� 0��� 0���� 0���� 0� 113��� 14� 0� 0 100� 0
�0� 0����� 0 2669412� 35224 897368��� 0��� 0���� 0���� 0� 113��� 18� 0� 0 100� 0
�0� 0����� 0 2669412� 35224 897368��� 0��� 0���� 0���� 0� 113��� 14� 0� 0 100� 0

As you can see resource patterns have changed drastically.� Our block activity, 
context switching and io wait time have all significantly changed between versions.� 
It's almost like 4.0.17 is performing direct io of some sort bypassing filesystem 
buffers.


I have tested throughput to the raid5 (4+1) array using both dd and tiobench running 
multiple threads.� In both cases the throughout to the array has been more than 
sufficient.

I have also strace'd the create script and the function calls are identical however 
the creates occur at a much faster rate in 4.0.16 compared to 4.0.17.� This is also 
displayed through the sql-bench results.

So the obvious question is what changed between 4.0.16 and 4.0.17 that would cause 
these results?� 

Thx,

Ian.
The information transmitted in this email is intended only for the person(s) or entity 
to which it is addressed and may contain confidential and/or privileged material. Any 
review, retransmission, dissemination or other use of, or taking of any action in 
reliance upon, this information by persons or entities other than the intended 
recipient is prohibited. If you received this email in error, please contact the 
sender and permanently delete the email from any computer.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to