Don't tell me - you upgraded a PIII server to a PIV server? The cache on the PIV is les than half the size of a PIII.
We've hit this problem with our own apps that by the way they operate cache a lot of data. A PIII 1.4GHz will match a 2.4 GHz PIV. Kevin Cowley R&D Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -----Original Message----- From: Charles, Tony (Exchange) [mailto:[EMAIL PROTECTED] Sent: 21 May 2004 18:47 To: [EMAIL PROTECTED] Subject: upgraded hardware: new server is faster, but "GROUP BY" operations are slower??? Hi all, Just bought a new server, which was supposed to improve the performance of our app. The new machine has the same OS (Redhat 8), same MySQL (4.0.18), and same my.cnf. The problem is that this (frequently run) query, actually runs 41% slower! select * from LEG L, LEG_DETAIL D, DEAL_LEGS G where L.latest_id = D.latest_id and D.latest_id = G.latest_id group by L.dealid limit 750000; If I remove the "GROUP BY", then the new box returns the results 14% faster than the old server. (So what's so special about "GROUP BY"?) I ran the benchmark scripts on both boxes, and it seems the following operations are slower on the new server, for some reason? count_distinct (1000) count_distinct_2 (1000) count_distinct_group (1000) count_distinct_group_on_key (1000) count_distinct_group_on_key_parts (1 count_distinct_key_prefix (1000) count_group_on_key_parts (1000) count_on_key (50100) select_distinct (800) select_group (2911) Can anyone suggest why the new machine might be slower, ONLY IN THE ABOVE areas? Just in case these numbers help, here are lines from the RUN file, for two of the slow operations above.... Operation seconds usr sys cpu tests count_distinct_group 19.00 1.17 0.08 1.25 1000 count_distinct_group (new box) 26.00 0.39 0.26 0.65 1000 count_distinct 12.00 0.51 0.02 0.53 1000 count_distinct (new box) 15.00 0.10 0.01 0.11 1000 Any advice at all, would be very much appreciated! Thanks, Tony *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. *********************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ************************************************************************************** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000 Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ************************************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
