Page 1 of 1
mysql 5 optimisation
Posted: Tue Jul 03, 2007 5:48 pm
by faris
I'm sure there is a better forum to ask on but I trust the people who frequent this neck of the woods more than anywhere else.
I've just upgraded one of our servers. The old one was mysql4 and 1gig of ram. The new one is mysql 5 and 2gigs of ram, and about 4 times the processing power. It is running Plesk for hosting basically.
One of my hosting customers has a specialist site that does a lot of mysql stuff. It seems to be creating a lot of temp tables when I use show full processlist.
The problem is this: that site is running increadibly slowly. really slowly. It was far faster on the old system - at least 10 times faster. And it seems to be down to mysql from what I can see, as that is using 100% of one processor a lot of the time, while everything else barely registers in comparison. All other sites are fine. Blasting away in fact. It is just that one site that is so much slower when doing its mysql stuff.
my.cnf on the old system had basically no performance related configuration lines in it. It was pretty naked.
On the new system I'm using the stock my.cnf from centos 4.4 which is as follows (I added the max_packet_size because the default is lower and I could not import a particular databse from the old system)
Code: Select all
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-bdb
set-variable = max_allowed_packet=2M
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
1) I do not understand why I have two section each with set-variables in them -- mysqls_safe and mysqld - can someone enlighten me? When I had put stuff in my.cnf before I always put it under [mysqld].
2) You'll note there is no query caching. Is it safe to enable it? I have a memory of someone trying and things going horribly wrong.
3) Any suggestions for some typical values? What does scott's my.cnf in his RPM have?
I've tried my best to understand what mysqlreport is telling me but I'm failing to figure out exactly what I should be doing:
Code: Select all
MySQL 5.0.27 uptime 0 2:1:4 Tue Jul 3 22:42:31 2007
__ Key _________________________________________________________________
Buffer used 1.33M of 8.00M %Used: 16.56
Current 2.25M %Usage: 28.09
Write hit 52.34%
Read hit 98.37%
__ Questions ___________________________________________________________
Total 148.04k 20.4/s
DMS 81.21k 11.2/s %Total: 54.85
Com_ 47.37k 6.5/s 32.00
COM_QUIT 17.34k 2.4/s 11.71
+Unknown 2.12k 0.3/s 1.43
Slow 40 0.0/s 0.03 %DMS: 0.05
DMS 81.21k 11.2/s 54.85
SELECT 73.28k 10.1/s 49.50 90.24
INSERT 4.81k 0.7/s 3.25 5.92
UPDATE 1.68k 0.2/s 1.13 2.07
DELETE 1.44k 0.2/s 0.97 1.78
REPLACE 2 0.0/s 0.00 0.00
Com_ 47.37k 6.5/s 32.00
set_option 20.91k 2.9/s 14.12
change_db 9.81k 1.4/s 6.63
show_variab 9.28k 1.3/s 6.27
__ SELECT and Sort _____________________________________________________
Scan 26.93k 3.7/s %SELECT: 36.75
Range 2.99k 0.4/s 4.07
Full join 403 0.1/s 0.55
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1.91k 0.3/s
Sort range 2.60k 0.4/s
Sort mrg pass 0 0/s
__ Table Locks _________________________________________________________
Waited 110 0.0/s %Total: 0.13
Immediate 85.67k 11.8/s
__ Tables ______________________________________________________________
Open 64 of 64 %Cache: 100.00
Opened 7.22k 1.0/s
__ Connections _________________________________________________________
Max used 31 of 100 %Max: 31.00
Total 17.59k 2.4/s
__ Created Temp ________________________________________________________
Disk table 4.62k 0.6/s
Table 17.70k 2.4/s
File 5 0.0/s
__ Threads _____________________________________________________________
Running 1 of 25
Cached 0 of 0 %Hit: 0.01
Created 17.59k 2.4/s
Slow 0 0/s
Increase temp_table size? Increase table_cache? Do something about thread_cache?
Any suggestions would be appreciated.
Faris.
Posted: Thu Jul 05, 2007 11:36 am
by Highland
He could be using persistent connections. That opens up a connection and leaves it open. I had an older application I had written a long time ago and I used persistent connections. These connections weren't being closed in a timely manner and it bogged down the whole server.
Take a look at the mysql process list through the manual interface in SSH.
mysql> SHOW PROCESSLIST;
Look for processes with a high run time.
Posted: Thu Jul 05, 2007 3:07 pm
by faris
Thanks Highland.
No, not persistant connections. It is basically constantly "copying to tmp table" when people use the application. When they don't there's nothing in the processlist and mysql behaves perfectly.
I'm going to try to install Scott's Mysql on a test VPS to see what it uses as a default, if anything.
Posted: Sun Jul 08, 2007 5:59 am
by EvolutionCrazy
cache helps a lot in many cases... are the queries doing temp tables or are the temps results of a JOIN and such?
btw... for "standard" configs you should check my-huge/my-large.cnf...
( /usr/share/doc/mysql-server-5.0.27/my-large.cnf )
I run this on a 2gb / 2cpu box:
Code: Select all
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords = 1
safe-show-database
max_connections = 150
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
thread_concurrency=4
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
and mysql takes about 380mb virt with 240mb res memory
Code: Select all
MySQL 5.0.27 uptime 6 1:14:57 Sun Jul 8 12:09:55 2007
__ Key _________________________________________________________________
Buffer used 60.76M of 96.00M %Used: 63.30
Current 67.96M %Usage: 70.79
Write hit 19.95%
Read hit 99.92%
__ Questions ___________________________________________________________
Total 29.79M 57.0/s
QC Hits 13.84M 26.5/s %Total: 46.46
DMS 12.86M 24.6/s 43.18
Com_ 1.81M 3.5/s 6.07
COM_QUIT 1.27M 2.4/s 4.26
+Unknown 8.81k 0.0/s 0.03
Slow 25 0.0/s 0.00 %DMS: 0.00
DMS 12.86M 24.6/s 43.18
SELECT 7.52M 14.4/s 25.23 58.43
UPDATE 3.11M 6.0/s 10.46 24.22
INSERT 1.37M 2.6/s 4.59 10.64
DELETE 863.82k 1.7/s 2.90 6.72
REPLACE 146 0.0/s 0.00 0.00
Com_ 1.81M 3.5/s 6.07
change_db 1.32M 2.5/s 4.43
set_option 456.95k 0.9/s 1.53
show_fields 9.01k 0.0/s 0.03
__ SELECT and Sort _____________________________________________________
Scan 2.71M 5.2/s %SELECT: 36.11
Range 92.81k 0.2/s 1.24
Full join 809 0.0/s 0.01
Range check 116 0.0/s 0.00
Full rng join 1.52k 0.0/s 0.02
Sort scan 460.48k 0.9/s
Sort range 431.82k 0.8/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 50.35M of 96.00M %Used: 52.45
Block Fragmnt 18.97%
Hits 13.84M 26.5/s
Inserts 7.48M 14.3/s
Insrt:Prune 6.27:1 12.0/s
Hit:Insert 1.85:1
__ Table Locks _________________________________________________________
Waited 7.52k 0.0/s %Total: 0.05
Immediate 14.96M 28.6/s
__ Tables ______________________________________________________________
Open 1800 of 1800 %Cache: 100.00
Opened 19.25k 0.0/s
__ Connections _________________________________________________________
Max used 46 of 650 %Max: 7.08
Total 1.27M 2.4/s
__ Created Temp ________________________________________________________
Disk table 22.19k 0.0/s
Table 70.04k 0.1/s
File 5 0.0/s
__ Threads _____________________________________________________________
Running 1 of 3
Cached 43 of 384 %Hit: 100
Created 46 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 156 0.0/s
Connects 3 0.0/s
__ Bytes _______________________________________________________________
Sent 2.48G 4.8k/s
Received 3.55G 6.8k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 8.00M of 8.00M %Used: 100.00
Read ratio 0.000
Pages
Free 0 %Total: 0.00
Data 475 92.77 %Drty: 0.00
Misc 37 7.23
Latched 0 0.00
Reads 143.95M 275.3/s
From file 35.37k 0.1/s 0.02
Ahead Rnd 491 0.0/s
Ahead Sql 527 0.0/s
Writes 16.24k 0.0/s
Flushes 4.46k 0.0/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 37.88k 0.1/s
Writes 8.65k 0.0/s
fsync 5.38k 0.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 95 0.0/s
Read 44.92k 0.1/s
Written 4.46k 0.0/s
Rows
Deleted 127 0.0/s
Inserted 123 0.0/s
Read 39.39M 75.3/s
Updated 2.22k 0.0/s
Posted: Sun Jul 08, 2007 8:07 am
by faris
Thanks for that. Very interesting!
I've been playing with various settings and you are quite right. enabling qc has transformed things.
There are still some oddities but basically I've managed to get that particular site running as fast as it was before on the old system, but no faster.
My current my.cnf is as follows (but I will adjust upwards using your template now that I know it is unlikely to kill things too badly)
Code: Select all
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
max_allowed_packet = 16M
But as you can see from mysqlreport:
Code: Select all
MySQL 5.0.27 uptime 1 16:42:50 Sun Jul 8 12:50:29 2007
__ Key _________________________________________________________________
Buffer used 4.19M of 64.00M %Used: 6.55
Current 9.36M %Usage: 14.62
Write hit 54.76%
Read hit 99.51%
__ Questions ___________________________________________________________
Total 2.36M 16.1/s
QC Hits 997.22k 6.8/s %Total: 42.26
Com_ 781.99k 5.3/s 33.14
COM_QUIT 341.62k 2.3/s 14.48
DMS 235.33k 1.6/s 9.97
+Unknown 3.31k 0.0/s 0.14
Slow 0 0/s 0.00 %DMS: 0.00
DMS 235.33k 1.6/s 9.97
SELECT 120.64k 0.8/s 5.11 51.26
INSERT 60.22k 0.4/s 2.55 25.59
DELETE 28.52k 0.2/s 1.21 12.12
UPDATE 25.16k 0.2/s 1.07 10.69
REPLACE 794 0.0/s 0.03 0.34
Com_ 781.99k 5.3/s 33.14
set_option 381.79k 2.6/s 16.18
show_variab 186.90k 1.3/s 7.92
change_db 178.49k 1.2/s 7.56
__ SELECT and Sort _____________________________________________________
Scan 233.99k 1.6/s %SELECT: 193.96
Range 7.78k 0.1/s 6.45
Full join 1.58k 0.0/s 1.31
Range check 1 0.0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 7.64k 0.1/s
Sort range 5.57k 0.0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 27.65M of 32.00M %Used: 86.41
Block Fragmnt 10.55%
Hits 997.22k 6.8/s
Inserts 97.41k 0.7/s
Insrt:Prune 5.05:1 0.5/s
Hit:Insert 10.24:1
__ Table Locks _________________________________________________________
Waited 58 0.0/s %Total: 0.02
Immediate 258.69k 1.8/s
__ Tables ______________________________________________________________
Open 1024 of 1024 %Cache: 100.00
Opened 13.85k 0.1/s
__ Connections _________________________________________________________
Max used 32 of 100 %Max: 32.00
Total 344.49k 2.4/s
__ Created Temp ________________________________________________________
Disk table 11.68k 0.1/s
Table 209.60k 1.4/s
File 5 0.0/s
__ Threads _____________________________________________________________
Running 1 of 8
Cached 24 of 64 %Hit: 99.99
Created 32 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 5.73k 0.0/s
Connects 1 0.0/s
__ Bytes _______________________________________________________________
Sent 1.43G 9.8k/s
Received 155.42M 1.1k/s
There's more Com_ and COM_QUIT than DMS, which I think I need to work on, and there are 5.73k aborted clients. Comparing that with yours it indicates, I think, something is not quite right somewhere.
show full processlist gives me the following when this site is doing its stuff and being slow (it takes about 4 or 5 seconds to process this query)
Code: Select all
| 344963 | username | localhost | dbname | Query | 1 | Copying to tmp table | SELECT distinct rating,firstName,playerID,rating_max from games,players where ativo<>'0' and
oficial='1' AND (gameMessage <> 'playerInvited' AND gameMessage <> 'inviteDeclined' AND gameMessage <> '') AND
(blackPlayer=playerID OR whitePlayer=PlayerID) order by rating DESC,rating_month DESC |
You can see this (to me) is a complex query that might possibly be better if it was split. And you get stuff like that just when logging in to this particular site!
It is obviously very badly written. Unfortunately there is no new version of the particular script being used, apparently. (it is an online chess championship thing).
Faris.
Posted: Sun Jul 08, 2007 9:01 am
by scott
Whats odd here is I could have sworn the query cache defaulted to being on in 5.0. How about disks and whatnot on the new system? What were you using before? If you were using a faster disk on the old system, or not using RAID then the new one could be effected by that.
Posted: Sun Jul 08, 2007 9:35 am
by EvolutionCrazy
faris wrote:Code: Select all
| 344963 | username | localhost | dbname | Query | 1 | Copying to tmp table | SELECT distinct rating,firstName,playerID,rating_max from games,players where ativo<>'0' and
oficial='1' AND (gameMessage <> 'playerInvited' AND gameMessage <> 'inviteDeclined' AND gameMessage <> '') AND
(blackPlayer=playerID OR whitePlayer=PlayerID) order by rating DESC,rating_month DESC |
You can see this (to me) is a complex query that might possibly be better if it was split. And you get stuff like that just when logging in to this particular site!
It is obviously very badly written. Unfortunately there is no new version of the particular script being used, apparently. (it is an online chess championship thing).
Faris.
run that query in phpmyadmin using EXPLAIN, so you can see wich indexes are used and how many full range scan it have to do...
from the query it's hard to see wich column is from 1st table and wich one from teh 2nd...
dunno if doing a JOIN 2times with the same table (once for black user, another for white user) can help...
PS: is "distinct" really needed? does there's really a possibility that you will fetch 2 identical rows?
PS2: i would enable "slow queries" logging... so you can see wich queries are taking really too much time, and focus on optimizing those
Posted: Sun Jul 08, 2007 1:23 pm
by faris
Scott: QC is definitely not enabled by default on the Centos 4 RPM installation of Mysql at least. I say this purely because in my first mysqlreport, which I did before I edited my.cnf, there was no QC data to report.
The config of the drives on the new system is the same as the old one in most ways: hardware raid5 with 10,000rpm SCSI drives. Only the new ones are up to the second SAS while the old ones were "just" ultra-SCSI.
I note that a new bios uses write through by default, while the current ones don't. That will improve performance. But I don't think that's it really.
And generally I would say subjectively the new system is massively faster on all ways, including disk, from the way it reacts when doing general admin tasks and from the way that it doesn't seem to blink no matter what I throw at it -- other than this site
EC: I had no idea you could do that in phpmyadmin. I'll investigate. I know there are no slow queries from mysqlreport. But how slow is slow. I'd say these must border on slow. Before I enabled QC there were slow queries listed - I suspect one each time this script runs.
I'll enable logging and set slow queries to something like 3 or 4 seconds and see what happens.
Thanks for all these pointers - it is very much appreciated.
(Incidentally this particular client is a good bloke who does a lot for the community and for me, hence my desire to help him if I can)
Faris.
Posted: Sun Jul 08, 2007 1:30 pm
by faris
Well, here's the result of EXPLAIN but I'm afraid it means almost nothing to me:
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE players ALL PRIMARY NULL NULL NULL 346 Using where; Using temporary; Using filesort
1 SIMPLE games ALL gamemessageindex NULL NULL NULL 6553 Using where; Distinct
From the mysql manual I can see that it isn't using the indexes it thinks it could use (because there's a NULL in that column) and basically I think it is reading through everything each and every time. Is that about it?
Posted: Sun Jul 08, 2007 2:41 pm
by scott
One way to rule the disks out would be to use hdparm or one of the other disk performance checkers to see what your read/write speeds are. You are taking a performance hit with a RAID-5, if that is indeed the culprit, you should be able to detect that directly by watching load, or the amount of waits you'd see in vmstat.
Also, did you do anything with shared memory settings on the old system? Or any other kernel level changes you can think of? Oh, did you run optimize on the tables? Apologies if you mentioned that earlier in the thread.
Posted: Sun Jul 08, 2007 2:53 pm
by EvolutionCrazy
faris wrote:Well, here's the result of EXPLAIN but I'm afraid it means almost nothing to me:
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE players ALL PRIMARY NULL NULL NULL 346 Using where; Using temporary; Using filesort
1 SIMPLE games ALL gamemessageindex NULL NULL NULL 6553 Using where; Distinct
From the mysql manual I can see that it isn't using the indexes it thinks it could use (because there's a NULL in that column) and basically I think it is reading through everything each and every time. Is that about it?
yeah, looks like it's not using any index :S
you might want to try to force the indexes (i hope they are defined in the table structure) or rewrite the query (if that one is really the slowest one...)
eventually if you cannot find a way to get that query running faster you can try to post the table structure of the 2tables involved maybe someone can give it a look
Posted: Mon Jul 09, 2007 7:12 pm
by faris
Scott, load is minimal. It rarely gets over 1, and is usuall no higher than 0.5 - 0.2 on "idle".
This is a custom virtuozzo kernel - all this is running in one of several vpses I have set up on the machine. The others have pretty much 0 load.
I really don't think it is disk.
At one point I had installed and run bonnie++ and was getting excellent figures compared to the old machine. I'm not really sure I want to run it on a live server again but maybe if I'm up at 3am in the morning again I'll do it. I didn't save the old results unfortunately -- at least not that I remember.
I know the results when running bonnie under vmware server on the same machine were awful though.
Anyway, I'll keep looking. The tables have been optimised but it made no difference.
EC, I'll post something later this week or over the weekend. I'm pooped at the moment! I've had so many silly little panics from customers who have had X, Y or Z stop working after our server upgrade but in all but one minor case it has been coincidence and nothing to do with the upgrade. But they all have to be investigated in detail just in case
Faris.
Posted: Mon Jul 09, 2007 10:43 pm
by scott
Ah, was the old system a VPS server too? If not then try shutting down all the other virtual servers and re-test the performance. The other instances of mysql (and everything else) could definitely effect the performance on other systems. Even just sitting there at idle doing nothing.
Posted: Thu Jul 12, 2007 4:26 pm
by faris
No, the old system wasn't a VPS. Old fashioned bare metal job
Here are some bonnie++ results.
Code: Select all
asterisk (Ordinary Desktop Running RH9 and ext2 on 80Gb 7.2k UATA)
Version 1.03 ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
asterisk 1G 4883 91 41867 86 15148 34 3019 95 55846 68 153.0 0
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
16 645 99 +++++ +++ +++++ +++ 654 99 +++++ +++ 2542 99
harlech (Dell 1550 Server RH9 on 67Gb total RAID5, 10k SCSI)
Version 1.03 ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
harlech 2G 10050 96 34042 33 13579 11 8722 81 31854 11 437.5 2
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
16 683 97 +++++ +++ 30720 95 703 99 +++++ +++ 2561 99
VPS (Dell 1950 COS 4.5 with 253Gb total RAID5, 10k SAS)
Version 1.03 ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
vps 8G 20627 68 34119 31 28297 11 24884 72 84720 13 226.4 0
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
16 1419 87 +++++ +++ +++++ +++ 1636 98 +++++ +++ 3858 97
Ordinary desktop (asterisk) first, then old system (harlech) then new system (vps).
I do not know why bonnie used different sizes* for all three machines nor what effect it might have on the results. But the results for the VPS look fine to me, especially given that that bloody mysql query was run several times during the benchmark, and in fact the system was actually quite busy in general at the time I ran them. In fact I think it is safe to say it really flies!
It makes no difference to the mysql query when I shut down the other VPSes - it is still slow.
Faris.
Hmm.. The "size" reported is 2x the RAM on the machine in all cases. Interesting.
Posted: Sat Jul 14, 2007 9:26 pm
by jim0615
faris wrote:Scott: QC is definitely not enabled by default on the Centos 4 RPM installation of Mysql at least. I say this purely because in my first mysqlreport, which I did before I edited my.cnf, there was no QC data to report.
The config of the drives on the new system is the same as the old one in most ways: hardware raid5 with 10,000rpm SCSI drives. Only the new ones are up to the second SAS while the old ones were "just" ultra-SCSI.
I note that a new bios uses write through by default, while the current ones don't. That will improve performance. But I don't think that's it really.
And generally I would say subjectively the new system is massively faster on all ways, including disk, from the way it reacts when doing general admin tasks and from the way that it doesn't seem to blink no matter what I throw at it -- other than this site
EC: I had no idea you could do that in phpmyadmin. I'll investigate. I know there are no slow queries from mysqlreport. But how slow is slow. I'd say these must border on slow. Before I enabled QC there were slow queries listed - I suspect one each time this script runs.
I'll enable logging and set slow queries to something like 3 or 4 seconds and see what happens.
Thanks for all these pointers - it is very much appreciated.
(Incidentally this particular client is a good bloke who does a lot for the community and for me, hence my desire to help him if I can)
Faris.
If your using a dual Xeon setup, 5000 series chipset, with SAS than that can cause issues. A lot of people on 2cpu.com have performance problems with the sas controllers on these boards.
Also normally SCSI controllers for a raid 5 setup have hardware to perform the error checking. The block size you used to create the array can have a large impact on performance for a DB server as well.
Notice the older SCSI system performed almost twice as many random seeks per second.